cloudflare-d1

name	cloudflare-d1
description	Cloudflare D1 SQLite database - prepared statements, batch operations, Drizzle ORM essentials. Trigger: When working with D1 database, SQLite queries, Drizzle ORM, database bindings.
license	Apache-2.0
metadata	{"author":"gentleman-programming","version":"1.0"}

Critical Patterns

Setup Binding

# wrangler.toml
[[d1_databases]]
binding = "DB"
database_name = "my-database"
database_id = "your-database-id"

export interface Env {
  DB: D1Database
}

Prepared Statements (REQUIRED)

// ✅ ALWAYS use prepared statements
const stmt = env.DB.prepare(
  "SELECT * FROM Users WHERE email = ?"
).bind(userEmail)

const result = await stmt.first()

// ❌ NEVER do this - SQL injection!
const result = await env.DB.prepare(
  `SELECT * FROM Users WHERE email = '${userEmail}'`
).run()

Return Methods

// INSERT/UPDATE/DELETE
const result = await env.DB.prepare(
  "INSERT INTO Users (name, email) VALUES (?, ?)"
).bind("John", "[e-mail]").run()

console.log(result.meta.last_row_id)   // New ID
console.log(result.meta.rows_written)  // Rows affected

// SELECT all rows
const { results } = await env.DB.prepare(
  "SELECT * FROM Users"
).all()

// SELECT single row
const user = await env.DB.prepare(
  "SELECT * FROM Users WHERE id = ?"
).bind(userId).first()

// SELECT single value
const count = await env.DB.prepare(
  "SELECT COUNT(*) as total FROM Users"
).first("total")

Batch Operations

// All succeed or all fail (atomic)
const [userResult, ordersResult] = await env.DB.batch([
  env.DB.prepare("SELECT * FROM Users WHERE id = ?").bind(1),
  env.DB.prepare("SELECT * FROM Orders WHERE user_id = ?").bind(1)
])

// Bulk inserts
await env.DB.batch([
  env.DB.prepare("INSERT INTO Users (name) VALUES (?)").bind("User 1"),
  env.DB.prepare("INSERT INTO Users (name) VALUES (?)").bind("User 2"),
  env.DB.prepare("INSERT INTO Users (name) VALUES (?)").bind("User 3")
])

Drizzle ORM

import { drizzle } from 'drizzle-orm/d1'
import { sqliteTable, text, integer } from 'drizzle-orm/sqlite-core'
import { eq } from 'drizzle-orm'

// Define schema
export const users = sqliteTable('users', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  name: text('name').notNull(),
  email: text('email').notNull().unique(),
  createdAt: integer('created_at', { mode: 'timestamp' })
})

// Use in worker
export default {
  async fetch(request: Request, env: Env) {
    const db = drizzle(env.DB)
    
    // SELECT
    const allUsers = await db.select().from(users).all()
     activeUsers =  db.().(users)
      .((users., ))
      .()
    
    
     newUser =  db.(users).({
      : ,
      : ,
      :  ()
    }).()
    
    
     db.(users)
      .({ :  })
      .((users., userId))
    
    
     db.(users).((users., userId))
    
     .(allUsers)
  }
}

Performance Tips

// ✅ Batch queries (single round trip)
const [user, orders] = await env.DB.batch([
  env.DB.prepare("SELECT * FROM Users WHERE id = ?").bind(1),
  env.DB.prepare("SELECT * FROM Orders WHERE user_id = ?").bind(1)
])

// ❌ Multiple queries (slow)
const user = await env.DB.prepare("SELECT * FROM Users WHERE id = ?").bind(1).first()
const orders = await env.DB.prepare("SELECT * FROM Orders WHERE user_id = ?").bind(1).all()

// ✅ Use .first() for single rows
const user = await env.DB.prepare("SELECT * FROM Users WHERE id = ?").bind(1).first()

// ❌ Wasteful
const users = await env.DB.prepare("SELECT * FROM Users WHERE id = ?").bind(1).all()
 user = users.[]


 users =  env..(
  
).()

Error Handling

try {
  const user = await env.DB.prepare(
    "SELECT * FROM Users WHERE id = ?"
  ).bind(userId).first()
  
  if (!user) {
    return new Response("User not found", { status: 404 })
  }
  
  return Response.json(user)
} catch (error) {
  console.error("Database error:", error)
  return new Response("Database error", { status: 500 })
}

Commands

# Create database
wrangler d1 create my-database

# Execute SQL
wrangler d1 execute my-database --local --command="SELECT * FROM Users"

# Drizzle migrations
pnpm drizzle-kit generate
pnpm drizzle-kit push
pnpm drizzle-kit studio

Resources

• Docs: developers.cloudflare.com/d1
• Drizzle: orm.drizzle.team