livedash-node/docs/D1_CLI_ACCESS.md

D1 Database Command Line Access

This guide shows you how to access and manage your Cloudflare D1 database d1-notso-livedash from the command line.

Quick Reference

Using the Custom D1 CLI Script

# Simple and fast commands
pnpm d1 tables                           # List all tables
pnpm d1 info                             # Database information
pnpm d1 schema User                      # Show table schema
pnpm d1 query "SELECT COUNT(*) FROM User" # Execute SQL
pnpm d1 export backup.sql               # Export database

# Remote (production) commands
pnpm d1 --remote info                   # Production database info
pnpm d1 --remote query "SELECT * FROM Company LIMIT 5"

Using Package.json Scripts

# Database information
pnpm d1:list                # List all D1 databases
pnpm d1:info                # Local database info
pnpm d1:info:remote         # Remote database info

# Backup and export
pnpm d1:export              # Export local database
pnpm d1:export:remote       # Export remote database
pnpm d1:schema              # Export schema only

Direct Wrangler Commands

# Basic operations
npx wrangler d1 list
npx wrangler d1 info d1-notso-livedash
npx wrangler d1 execute d1-notso-livedash --command "SELECT * FROM User"

# Remote operations (add --remote flag)
npx wrangler d1 info d1-notso-livedash --remote
npx wrangler d1 execute d1-notso-livedash --remote --command "SELECT COUNT(*) FROM Company"

Database Schema

Your D1 database contains these tables:

Company Table

- id (TEXT, PRIMARY KEY)
- name (TEXT, NOT NULL)
- csvUrl (TEXT, NOT NULL)
- csvUsername (TEXT)
- csvPassword (TEXT)
- sentimentAlert (REAL)
- dashboardOpts (TEXT)
- createdAt (DATETIME, NOT NULL, DEFAULT CURRENT_TIMESTAMP)
- updatedAt (DATETIME, NOT NULL)

User Table

- id (TEXT, PRIMARY KEY)
- email (TEXT, NOT NULL)
- password (TEXT, NOT NULL)
- companyId (TEXT, NOT NULL)
- role (TEXT, NOT NULL)
- resetToken (TEXT)
- resetTokenExpiry (DATETIME)

Session Table

- id (TEXT, PRIMARY KEY)
- userId (TEXT, NOT NULL)
- expiresAt (DATETIME, NOT NULL)

Common SQL Queries

Data Exploration

-- Check table sizes
SELECT 'Company' as table_name, COUNT(*) as count FROM Company
UNION ALL
SELECT 'User' as table_name, COUNT(*) as count FROM User
UNION ALL
SELECT 'Session' as table_name, COUNT(*) as count FROM Session;

-- Show all table names
SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;

-- Get table schema
PRAGMA table_info(User);

Business Queries

-- List companies with user counts
SELECT c.name, c.id, COUNT(u.id) as user_count
FROM Company c
LEFT JOIN User u ON c.id = u.companyId
GROUP BY c.id, c.name;

-- Find admin users
SELECT u.email, c.name as company
FROM User u
JOIN Company c ON u.companyId = c.id
WHERE u.role = 'admin';

-- Active sessions
SELECT COUNT(*) as active_sessions
FROM Session
WHERE expiresAt > datetime('now');

Local vs Remote Databases

• Local Database: Located at .wrangler/state/v3/d1/ (for development)
• Remote Database: Cloudflare's production D1 database

When to Use Each:

• Local: Development, testing, safe experimentation
• Remote: Production data, deployment verification

Database Statistics

Current database info:

• Database ID: d4ee7efe-d37a-48e4-bed7-fdfaa5108131
• Region: WEUR (Western Europe)
• Size: ~53.2 kB
• Tables: 6 (including system tables)
• Read Queries (24h): 65
• Write Queries (24h): 8

Scripts Available

/scripts/d1.js (Recommended)

Simple, fast CLI for common operations:

node scripts/d1.js tables
node scripts/d1.js schema User
node scripts/d1.js query "SELECT * FROM Company"
node scripts/d1.js --remote info

/scripts/d1-query.js

Simple query executor:

node scripts/d1-query.js "SELECT COUNT(*) FROM User"
node scripts/d1-query.js --remote "SELECT * FROM Company"

/scripts/d1-manager.js

Comprehensive database management (if needed for advanced operations):

node scripts/d1-manager.js info
node scripts/d1-manager.js backup

Backup and Recovery

Create Backups

# Quick backup
pnpm d1 export backup_$(date +%Y%m%d).sql

# Automated backup with timestamp
npx wrangler d1 export d1-notso-livedash --output backups/backup_$(date +%Y%m%d_%H%M%S).sql

# Schema only backup
npx wrangler d1 export d1-notso-livedash --no-data --output schema.sql

Restore from Backup

# Apply SQL file to database
npx wrangler d1 execute d1-notso-livedash --file backup.sql

Troubleshooting

Common Issues

1. "wrangler not found": Use npx wrangler instead of wrangler
2. Permission denied: Ensure you're logged into Cloudflare: npx wrangler login
3. Database not found: Check wrangler.json for correct binding name

Debug Commands

# Check Wrangler authentication
npx wrangler whoami

# Verify database configuration
npx wrangler d1 list

# Test database connectivity
npx wrangler d1 execute d1-notso-livedash --command "SELECT 1"

Security Notes

• Local database is for development only
• Never expose production database credentials
• Use --remote flag carefully in production
• Regular backups are recommended for production data