references/d1/gotchas.md

# D1 Gotchas & Troubleshooting

## Common Errors

### "SQL Injection Vulnerability"

**Cause:** Using string interpolation instead of prepared statements with bind()  
**Solution:** ALWAYS use prepared statements: `env.DB.prepare('SELECT * FROM users WHERE id = ?').bind(userId).all()` instead of string interpolation which allows attackers to inject malicious SQL

### "no such table"

**Cause:** Table doesn't exist because migrations haven't been run, or using wrong database binding  
**Solution:** Run migrations using `wrangler d1 migrations apply <db-name> --remote` and verify binding name in wrangler.jsonc matches code

### "UNIQUE constraint failed"

**Cause:** Attempting to insert duplicate value in column with UNIQUE constraint  
**Solution:** Catch error and return 409 Conflict status code

### "Query Timeout (30s exceeded)"

**Cause:** Query execution exceeds 30 second timeout limit  
**Solution:** Break into smaller queries, add indexes to speed up queries, or reduce dataset size

### "N+1 Query Problem"

**Cause:** Making multiple individual queries in a loop instead of single optimized query  
**Solution:** Use JOIN to fetch related data in single query or use `batch()` method for multiple queries

### "Missing Indexes"

**Cause:** Queries performing full table scans without indexes  
**Solution:** Use `EXPLAIN QUERY PLAN` to check if index is used, then create index with `CREATE INDEX idx_users_email ON users(email)`

### "Boolean Type Issues"

**Cause:** SQLite uses INTEGER (0/1) not native boolean type  
**Solution:** Bind 1 or 0 instead of true/false when working with boolean values

### "Date/Time Type Issues"

**Cause:** SQLite doesn't have native DATE/TIME types  
**Solution:** Use TEXT (ISO 8601 format) or INTEGER (unix timestamp) for date/time values

## Plan Tier Limits

| Limit | Free Tier | Paid Plans | Notes |
|-------|-----------|------------|-------|
| Database size | 500 MB | 10 GB | Design for multiple DBs per tenant on paid |
| Row size | 1 MB | 1 MB | Store large files in R2, not D1 |
| Query timeout | 30s | 30s (900s with sessions) | Use sessions API for migrations |
| Batch size | 1,000 statements | 10,000 statements | Split large batches accordingly |
| Time Travel | 7 days | 30 days | Point-in-time recovery window |
| Read replicas | ❌ Not available | ✅ Available | Paid add-on for lower latency |
| Sessions API | ❌ Not available | ✅ Up to 15 min | For migrations and heavy operations |
| Concurrent requests | 10,000/min | Higher | Contact support for custom limits |

## Production Gotchas

### "Batch size exceeded"

**Cause:** Attempting to send >1,000 statements on free tier or >10,000 on paid  
**Solution:** Chunk batches: `for (let i = 0; i < stmts.length; i += MAX_BATCH) await env.DB.batch(stmts.slice(i, i + MAX_BATCH))`

### "Session not closed / resource leak"

**Cause:** Forgot to call `session.close()` after using sessions API  
**Solution:** Always use try/finally block: `try { await session.prepare(...) } finally { session.close() }`

### "Replication lag causing stale reads"

**Cause:** Reading from replica immediately after write - replication lag can be 100ms-2s  
**Solution:** Use primary for read-after-write: `await env.DB.prepare(...)` not `env.DB_REPLICA`

### "Migration applied to local but not remote"

**Cause:** Forgot `--remote` flag when applying migrations  
**Solution:** Always run `wrangler d1 migrations apply <db-name> --remote` for production

### "Foreign key constraint failed"

**Cause:** Inserting row with FK to non-existent parent, or deleting parent before children  
**Solution:** Enable FK enforcement: `PRAGMA foreign_keys = ON;` and use ON DELETE CASCADE in schema

### "BLOB data corrupted on export"

**Cause:** D1 export may not handle BLOB correctly  
**Solution:** Store binary files in R2, only store R2 URLs/keys in D1

### "Database size approaching limit"

**Cause:** Storing too much data in single database  
**Solution:** Horizontal scale-out: create per-tenant/per-user databases, archive old data, or upgrade to paid plan

### "Local dev vs production behavior differs"

**Cause:** Local uses SQLite file, production uses distributed D1 - different performance/limits  
**Solution:** Always test migrations on remote with `--remote` flag before production rollout