1. Mental Model
D1 = SQLite running inside your Worker process
Not a separate database server - zero network latency
One logical database, replicated globally by Cloudflare
env.DB injected at runtime via binding system
2. Basic Setup
Wrangler Config
{
"d1_databases": [
{
"binding": "DB",
"database_name": "my-database",
"database_id": "<unique-ID>"
}
]
}
TypeScript Support
npx wrangler types
Basic Worker Structure
export interface Env {
DB: D1Database;
}
export default {
async fetch(request, env): Promise<Response> {
},
} satisfies ExportedHandler<Env>;
3. Core SQL Operations
The Prepare → Bind → Run Chain
const stmt = env.DB.prepare("SELECT * FROM users WHERE id = ?");
const result = await stmt.bind(123).run();
Multiple Placeholders
const stmt = env.DB.prepare("SELECT * FROM users WHERE age > ? AND city = ?");
const result = await stmt.bind(25, "New York").run();
const stmt = env.DB.prepare(
"SELECT * FROM logs WHERE user_id = ?1 AND created_at > ?2 OR updated_at > ?2"
);
const result = await stmt.bind(userId, yesterday).run();
Execution Methods
const result = await stmt.run();
const user = await stmt.first();
const rows = await stmt.raw();
Type Safety
type User = { id: number; name: string; email: string };
const user = await stmt.first<User>();
const users = await stmt.run<User>();
const rawRows = await stmt.raw<User>();
4. D1-Specific Patterns
const insertStmt = env.DB.prepare(
"INSERT INTO users (name, email) VALUES (?, ?)"
);
const results = await env.DB.batch([
insertStmt.bind("Alice", "alice@example.com"),
insertStmt.bind("Bob", "bob@example.com"),
insertStmt.bind("Charlie", "charlie@example.com"),
]);
Error Handling
try {
const result = await env.DB.prepare("SELECT * FROM users WHERE id = ?")
.bind(userId)
.run();
return Response.json(result.results);
} catch (error) {
console.error("Database error:", error);
return new Response("Database error", { status: 500 });
}
Type Conversion Gotchas
await stmt.bind(true).run();
const result = await stmt.first<{ active: boolean }>();
console.log(result.active);
const maybeEmail = undefined;
await stmt.bind(maybeEmail ?? null).run();
Read Replication Behavior
const result = await env.DB.prepare("SELECT * FROM users").run();
console.log(`Served by region: ${result.meta.served_by_region}`);
console.log(`Served by primary: ${result.meta.served_by_primary}`);
Sessions API (Read Your Own Writes)
await env.DB.prepare("INSERT INTO posts (title) VALUES (?)")
.bind("My Post")
.run();
const post = await env.DB.prepare("SELECT * FROM posts WHERE title = ?")
.bind("My Post")
.first();
const session = env.DB.withSession("first-primary");
await session
.prepare("INSERT INTO posts (title) VALUES (?)")
.bind("My Post")
.run();
const post = await session
.prepare("SELECT * FROM posts WHERE title = ?")
.bind("My Post")
.first();
const bookmark = session.getBookmark();
response.headers.set("x-d1-bookmark", bookmark);
6. Schema Management
Manual Migration Pattern
migrations/
001-initial-schema.sql
002-add-users-table.sql
003-add-indexes.sql
npx wrangler d1 execute my-db --local --file=./migrations/002-add-users-table.sql
npx wrangler d1 execute my-db --remote --file=./migrations/002-add-users-table.sql
Safe Schema Changes
ALTER TABLE users ADD COLUMN email TEXT;
CREATE TABLE posts (id INTEGER PRIMARY KEY, title TEXT);
ALTER TABLE users DROP COLUMN old_field;
ALTER TABLE users RENAME COLUMN name TO full_name;
7. Real-World Integration
D1 + KV Caching Pattern
async function getUser(userId: number) {
const cached = await env.CACHE.get(`user:${userId}`, "json");
if (cached) return cached;
const user = await env.DB.prepare("SELECT * FROM users WHERE id = ?")
.bind(userId)
.first();
if (user) {
await env.CACHE.put(`user:${userId}`, JSON.stringify(user), {
expirationTtl: 300,
});
}
return user;
}
Race Condition Prevention
const user = await env.DB.prepare("SELECT credits FROM users WHERE id = ?")
.bind(userId)
.first();
if (user.credits >= price) {
await env.DB.prepare("UPDATE users SET credits = ? WHERE id = ?")
.bind(user.credits - price, userId)
.run();
}
const result = await env.DB.prepare(
`
UPDATE users SET credits = credits - ? WHERE id = ? AND credits >= ?
`
)
.bind(price, userId, price)
.run();
if (result.meta.changes === 0) {
throw new Error("Insufficient credits");
}
Complete Worker Example
export default {
async fetch(request, env) {
const url = new URL(request.url);
try {
if (url.pathname === "/api/users" && request.method === "GET") {
const { results } = await env.DB.prepare(
"SELECT id, name, email FROM users"
).run();
return Response.json(results);
}
if (url.pathname === "/api/users" && request.method === "POST") {
const userData = await request.json();
const result = await env.DB.prepare(
"INSERT INTO users (name, email) VALUES (?, ?)"
)
.bind(userData.name, userData.email)
.run();
return Response.json({ id: result.meta.last_row_id }, { status: 201 });
}
return new Response("Not found", { status: 404 });
} catch (error) {
console.error(error);
return new Response("Internal error", { status: 500 });
}
},
};
Key Commands
npx wrangler d1 create my-database
npx wrangler d1 execute my-database --local --command="SELECT * FROM users"
npx wrangler d1 execute my-database --local --file=./schema.sql
npx wrangler d1 execute my-database --remote --command="SELECT * FROM users"
npx wrangler d1 execute my-database --remote --file=./schema.sql
npx wrangler types
npx wrangler deploy
Remember: D1 = SQLite at the edge with global replication magic. Master the basics, then consider Drizzle for serious projects!
