bunx wrangler d1 create my-database

{
  "name": "my-worker",
  "main": "src/index.ts",
  "compatibility_date": "2025-10-11",
  "d1_databases": [
    {
      "binding": "DB",                    // env.DB
      "database_name": "my-database",
      "database_id": "<UUID>",
      "preview_database_id": "local-db"
    }
  ]
}

bunx wrangler d1 migrations create my-database create_users

CREATE TABLE IF NOT EXISTS users (
  user_id INTEGER PRIMARY KEY AUTOINCREMENT,
  email TEXT NOT NULL UNIQUE,
  username TEXT NOT NULL,
  created_at INTEGER NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);

PRAGMA optimize;

# Local
bunx wrangler d1 migrations apply my-database --local

# Production
bunx wrangler d1 migrations apply my-database --remote

import { Hono } from 'hono';

type Bindings = {
  DB: D1Database;
};

const app = new Hono<{ Bindings: Bindings }>();

app.get('/users/:email', async (c) => {
  const { results } = await c.env.DB.prepare(
    'SELECT * FROM users WHERE email = ?'
  )
    .bind(c.req.param('email'))
    .all();

  return c.json(results);
});

export default app;

// Single result
const { results } = await env.DB.prepare(
  'SELECT * FROM users WHERE email = ?'
)
  .bind(email)
  .all();

// First result only
const user = await env.DB.prepare(
  'SELECT * FROM users WHERE user_id = ?'
)
  .bind(userId)
  .first();

// Raw results (faster)
const { results } = await env.DB.prepare(
  'SELECT username FROM users'
)
  .raw();  // Returns arrays instead of objects

const results = await env.DB.batch([
  env.DB.prepare('INSERT INTO users (email, username, created_at) VALUES (?, ?, ?)')
    .bind('user1@example.com', 'user1', Date.now()),
  env.DB.prepare('INSERT INTO users (email, username, created_at) VALUES (?, ?, ?)')
    .bind('user2@example.com', 'user2', Date.now()),
  env.DB.prepare('SELECT COUNT(*) as count FROM users')
]);

console.log('Users count:', results[2].results[0].count);

// For migrations/setup only
await env.DB.exec(`
  CREATE TABLE IF NOT EXISTS users (
    user_id INTEGER PRIMARY KEY,
    email TEXT NOT NULL
  );
  CREATE INDEX idx_email ON users(email);
`);

// Create
app.post('/users', async (c) => {
  const { email, username } = await c.req.json();

  const { results } = await c.env.DB.prepare(
    'INSERT INTO users (email, username, created_at) VALUES (?, ?, ?) RETURNING *'
  )
    .bind(email, username, Date.now())
    .all();

  return c.json(results[0]);
});

// Read
app.get('/users/:id', async (c) => {
  const user = await c.env.DB.prepare(
    'SELECT * FROM users WHERE user_id = ?'
  )
    .bind(c.req.param('id'))
    .first();

  if (!user) {
    return c.json({ error: 'Not found' }, 404);
  }

  return c.json(user);
});

// Update
app.patch('/users/:id', async (c) => {
  const { username } = await c.req.json();

  await c.env.DB.prepare(
    'UPDATE users SET username = ?, updated_at = ? WHERE user_id = ?'
  )
    .bind(username, Date.now(), c.req.param('id'))
    .run();

  return c.json({ success: true });
});

// Delete
app.delete('/users/:id', async (c) => {
  await c.env.DB.prepare(
    'DELETE FROM users WHERE user_id = ?'
  )
    .bind(c.req.param('id'))
    .run();

  return c.json({ success: true });
});

app.post('/users/bulk', async (c) => {
  const users = await c.req.json();  // Array of users

  const statements = users.map(user =>
    c.env.DB.prepare(
      'INSERT INTO users (email, username, created_at) VALUES (?, ?, ?)'
    ).bind(user.email, user.username, Date.now())
  );

  const results = await c.env.DB.batch(statements);

  return c.json({ inserted: results.length });
});

// Configure read replica (any region)
const session = c.env.DB.withSession({
  preferredRegion: 'auto'  // or 'weur', 'wnam', 'enam', 'apac'
});

// Read from nearest replica
const { results } = await session.prepare(
  'SELECT * FROM users WHERE email = ?'
)
  .bind(email)
  .all();

// Check which region served request
console.log('Served by:', results[0].served_by_region);

// Transfer credits between users (atomic)
const results = await c.env.DB.batch([
  c.env.DB.prepare(
    'UPDATE users SET credits = credits - ? WHERE user_id = ?'
  ).bind(amount, fromUserId),
  c.env.DB.prepare(
    'UPDATE users SET credits = credits + ? WHERE user_id = ?'
  ).bind(amount, toUserId),
  c.env.DB.prepare(
    'INSERT INTO transactions (from_user, to_user, amount, created_at) VALUES (?, ?, ?, ?)'
  ).bind(fromUserId, toUserId, amount, Date.now())
]);

// All succeed or all fail (transaction)

app.get('/users', async (c) => {
  const page = parseInt(c.req.query('page') || '1');
  const limit = 20;
  const offset = (page - 1) * limit;

  const { results } = await c.env.DB.prepare(
    'SELECT * FROM users ORDER BY created_at DESC LIMIT ? OFFSET ?'
  )
    .bind(limit, offset)
    .all();

  return c.json({
    users: results,
    page,
    limit
  });
});

bunx wrangler d1 migrations create my-database add_users_avatar

-- ✅ GOOD: Idempotent
CREATE TABLE IF NOT EXISTS users (...);
CREATE INDEX IF NOT EXISTS idx_email ON users(email);
DROP TABLE IF EXISTS old_table;

-- ❌ BAD: Fails on re-run
CREATE TABLE users (...);
CREATE INDEX idx_email ON users(email);

bunx wrangler d1 migrations apply my-database --local
bunx wrangler d1 execute my-database --local --command "SELECT * FROM users"

-- End of migration
PRAGMA optimize;

try {
  const { results } = await env.DB.prepare(
    'SELECT * FROM users WHERE email = ?'
  )
    .bind(email)
    .all();

  return c.json(results);
} catch (error) {
  console.error('D1 Error:', error);
  return c.json({ error: 'Database error' }, 500);
}

// Returns arrays instead of objects (faster)
const { results } = await env.DB.prepare(
  'SELECT user_id, email FROM users'
)
  .raw();

// results = [[1, 'user1@example.com'], [2, 'user2@example.com']]

const count = await env.DB.prepare(
  'SELECT COUNT(*) as count FROM users'
)
  .first('count');  // Get single column value

console.log('Total users:', count);