Build with D1 serverless SQLite database on Cloudflare's edge. Use when: creating databases, writing SQL migrations, querying D1 from Workers, handling relational data, or troubleshooting D1_ERROR, statement too long, migration failures, or query performance issues.
Build with D1 serverless SQLite database on Cloudflare's edge. Use when: creating databases, writing SQL migrations, querying D1 from Workers, handling relational data, or troubleshooting D1_ERROR, statement too long, migration failures, or query performance issues.
license
MIT
Cloudflare D1 Database
Status: Production Ready ✅
Last Updated: 2025-11-23
Dependencies: cloudflare-worker-base (for Worker setup)
Latest Versions: wrangler@4.50.0, @cloudflare/workers-types@4.20251121.0
Recent Updates (2025):
Nov 2025: Jurisdiction support (data localization compliance), remote bindings GA (wrangler@4.37.0+), automatic resource provisioning
Sept 2025: Automatic read-only query retries (up to 2 attempts), remote bindings public beta
July 2025: Storage limits increased (250GB → 1TB), alpha backup access removed, REST API 50-500ms faster
May 2025: HTTP API permissions security fix (D1:Edit required for writes)
April 2025: Read replication public beta (read-only replicas across regions)
Feb 2025: PRAGMA optimize support, read-only access permission bug fix
Jan 2025: Free tier limits enforcement (Feb 10 start), Worker API 40-60% faster queries
Quick Start (5 Minutes)
1. Create D1 Database
# Create a new D1 database
npx wrangler d1 create my-database
# Output includes database_id - save this!
# ✅ Successfully created DB 'my-database'
#
# [[d1_databases]]
# binding = "DB"
# database_name = "my-database"
# database_id = "<UUID>"
2. Configure Bindings
Add to your wrangler.jsonc:
{"name":"my-worker","main":"src/index.ts","compatibility_date":"2025-10-11","d1_databases":[{"binding":"DB",// Available as env.DB in your Worker"database_name":"my-database",// Name from wrangler d1 create"database_id":"<UUID>",// ID from wrangler d1 create"preview_database_id":"local-db"// For local development}]}
CRITICAL:
binding is how you access the database in code (env.DB)
database_id is the production database UUID
preview_database_id is for local dev (can be any string)
Never commit real database_id values to public repos - use environment variables or secrets
-- migrations/0001_create_users_table.sqlDROPTABLE IF EXISTS users;
CREATE TABLE IF NOTEXISTS users (
user_id INTEGERPRIMARY KEY AUTOINCREMENT,
email TEXT NOT NULLUNIQUE,
username TEXT NOT NULL,
created_at INTEGERNOT NULL,
updated_at INTEGER
);
-- Create index for common queriesCREATE INDEX IF NOTEXISTS idx_users_email ON users(email);
-- Optimize database
PRAGMA optimize;
4. Apply Migration
# Apply locally first (for testing)
npx wrangler d1 migrations apply my-database --local# Apply to production when ready
npx wrangler d1 migrations apply my-database --remote
-- Use IF NOT EXISTS to make migrations idempotentCREATE TABLE IF NOTEXISTS users (...);
CREATE INDEX IF NOTEXISTS idx_users_email ON users(email);
-- Run PRAGMA optimize after schema changes
PRAGMA optimize;
-- Use transactions for data migrationsBEGIN TRANSACTION;
UPDATE users SET updated_at = unixepoch() WHERE updated_at ISNULL;
COMMIT;
❌ Never Do:
-- DON'T include BEGIN TRANSACTION at start (D1 handles this)BEGIN TRANSACTION; -- ❌ Remove this-- DON'T use MySQL/PostgreSQL syntaxALTER TABLE users MODIFY COLUMN email VARCHAR(255); -- ❌ Not SQLite-- DON'T create tables without IF NOT EXISTSCREATE TABLE users (...); -- ❌ Fails if table exists
Handling Foreign Keys in Migrations
-- Temporarily disable foreign key checks during schema changes
PRAGMA defer_foreign_keys =true;
-- Make schema changes that would violate foreign keysALTER TABLE posts DROPCOLUMN author_id;
ALTER TABLE posts ADDCOLUMN user_id INTEGERREFERENCES users(user_id);
-- Foreign keys re-enabled automatically at end of migration
.first('column') → value - Get single column value (e.g., COUNT)
.run() → { success, meta } - Execute INSERT/UPDATE/DELETE (no results)
batch() - CRITICAL FOR PERFORMANCE:
const results = await env.DB.batch([
env.DB.prepare('SELECT * FROM users WHERE user_id = ?').bind(1),
env.DB.prepare('SELECT * FROM posts WHERE user_id = ?').bind(1)
]);
Executes sequentially, single network round trip
If one fails, remaining statements don't execute
Use for: bulk inserts, fetching related data
exec() - AVOID IN PRODUCTION:
await env.DB.exec('SELECT * FROM users;'); // Only for migrations/maintenance
❌ Never use with user input (SQL injection risk)
✅ Only use for: migration files, one-off tasks
Query Patterns
Basic CRUD Operations
// CREATEconst { meta } = await env.DB.prepare(
'INSERT INTO users (email, username, created_at) VALUES (?, ?, ?)'
).bind(email, username, Date.now()).run();
const newUserId = meta.last_row_id;
// READ (single)const user = await env.DB.prepare('SELECT * FROM users WHERE user_id = ?')
.bind(userId).first();
// READ (multiple)const { results } = await env.DB.prepare('SELECT * FROM users LIMIT ?')
.bind(10).all();
// UPDATEconst { meta } = await env.DB.prepare('UPDATE users SET username = ? WHERE user_id = ?')
.bind(newUsername, userId).run();
const rowsAffected = meta.rows_written;
// DELETEawait env.DB.prepare('DELETE FROM users WHERE user_id = ?').bind(userId).run();
// COUNTconst count = await env.DB.prepare('SELECT COUNT(*) as total FROM users').first('total');
// EXISTS checkconst exists = await env.DB.prepare('SELECT 1 FROM users WHERE email = ? LIMIT 1')
.bind(email).first();
Pagination Pattern
const page = parseInt(c.req.query('page') || '1');
const limit = 20;
const offset = (page - 1) * limit;
const [countResult, usersResult] = await c.env.DB.batch([
c.env.DB.prepare('SELECT COUNT(*) as total FROM users'),
c.env.DB.prepare('SELECT * FROM users ORDER BY created_at DESC LIMIT ? OFFSET ?')
.bind(limit, offset)
]);
return c.json({
users: usersResult.results,
pagination: { page, limit, total: countResult.results[0].total }
});
Batch Pattern (Pseudo-Transactions)
// D1 doesn't support multi-statement transactions, but batch() provides sequential executionawait env.DB.batch([
env.DB.prepare('UPDATE users SET credits = credits - ? WHERE user_id = ?').bind(amount, fromUserId),
env.DB.prepare('UPDATE users SET credits = credits + ? WHERE user_id = ?').bind(amount, toUserId),
env.DB.prepare('INSERT INTO transactions (from_user, to_user, amount) VALUES (?, ?, ?)').bind(fromUserId, toUserId, amount)
]);
// If any statement fails, batch stops (transaction-like behavior)
Error Handling
Common Error Types:
D1_ERROR - General D1 error
D1_EXEC_ERROR - SQL syntax error
D1_TYPE_ERROR - Type mismatch (undefined instead of null)
D1_COLUMN_NOTFOUND - Column doesn't exist
Common Errors and Fixes:
Error
Cause
Solution
Statement too long
Large INSERT with 1000+ rows
Break into batches of 100-250 using batch()
Too many requests queued
Individual queries in loop
Use batch() instead of loop
D1_TYPE_ERROR
Using undefined in bind
Use null for optional values: .bind(email, bio || null)
Transaction conflicts
BEGIN TRANSACTION in migration
Remove BEGIN/COMMIT (D1 handles automatically)
Foreign key violations
Schema changes break constraints
Use PRAGMA defer_foreign_keys = true
Automatic Retries (Sept 2025):
D1 automatically retries read-only queries (SELECT, EXPLAIN, WITH) up to 2 times on retryable errors. Check meta.total_attempts in response for retry count.
Performance Optimization
Index Best Practices:
✅ Index columns in WHERE clauses: CREATE INDEX idx_users_email ON users(email)
✅ Index foreign keys: CREATE INDEX idx_posts_user_id ON posts(user_id)
✅ Index columns for sorting: CREATE INDEX idx_posts_created_at ON posts(created_at DESC)
✅ Multi-column indexes: CREATE INDEX idx_posts_user_published ON posts(user_id, published)
✅ Partial indexes: CREATE INDEX idx_users_active ON users(email) WHERE deleted = 0
✅ Test with: EXPLAIN QUERY PLAN SELECT ...
PRAGMA optimize (Feb 2025):
CREATE INDEX idx_users_email ON users(email);
PRAGMA optimize; -- Run after schema changes
Query Optimization:
✅ Use specific columns (not SELECT *)
✅ Always include LIMIT on large result sets
✅ Use indexes for WHERE conditions
❌ Avoid functions in WHERE (can't use indexes): WHERE LOWER(email) → store lowercase instead
Local Development
Local vs Remote (Nov 2025 - Remote Bindings GA):
# Local database (automatic creation)
npx wrangler d1 migrations apply my-database --local
npx wrangler d1 execute my-database --local --command"SELECT * FROM users"# Remote database
npx wrangler d1 execute my-database --remote --command"SELECT * FROM users"# Remote bindings (wrangler@4.37.0+) - connect local Worker to deployed D1# Add to wrangler.jsonc: { "binding": "DB", "remote": true }
Local Database Location:.wrangler/state/v3/d1/miniflare-D1DatabaseObject/<database_id>.sqlite
