Database (D1)

Cloudwerk provides integration with Cloudflare D1, a serverless SQLite database that runs at the edge.

Getting Started

Create a D1 Database

1. Create a new D1 database:

   wrangler d1 create my-database

2. Copy the database ID from the output and add it to wrangler.toml:

   [[d1_databases]]
   binding = "DB"
   database_name = "my-database"
   database_id = "your-database-id"

3. Your D1 database is now available via context.env.DB in loaders and getContext().env.DB in route handlers.

Database Migrations

Create migrations in the migrations/ directory:

-- migrations/0001_create_users.sql
CREATE TABLE IF NOT EXISTS users (
  id TEXT PRIMARY KEY,
  email TEXT UNIQUE NOT NULL,
  name TEXT NOT NULL,
  password_hash TEXT NOT NULL,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX idx_users_email ON users(email);

Run migrations:

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

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

Using D1 in Loaders

Access D1 via context.env.DB in loader functions:

// app/users/page.tsx
import type { PageProps, LoaderArgs } from '@cloudwerk/core';

export async function loader({ context }: LoaderArgs) {
  const db = context.env.DB;
  const { results: users } = await db.prepare('SELECT * FROM users').all();
  return { users };
}

export default function UsersPage({ users }: PageProps & { users: User[] }) {
  return (
    <ul>
      {users.map(user => (
        <li key={user.id}>{user.name}</li>
      ))}
    </ul>
  );
}

Using D1 in Route Handlers

Access D1 via getContext().env.DB in route handlers:

// app/api/users/route.ts
import type { CloudwerkHandlerContext } from '@cloudwerk/core';
import { json, getContext } from '@cloudwerk/core';

export async function GET(request: Request, { params }: CloudwerkHandlerContext) {
  const { env } = getContext();
  const { results: users } = await env.DB.prepare('SELECT * FROM users').all();
  return json(users);
}

Query Patterns

Select Queries

// Get all rows
const { results: users } = await db.prepare('SELECT * FROM users').all();

// Get single row by ID
const user = await db
  .prepare('SELECT * FROM users WHERE id = ?')
  .bind(userId)
  .first();

// Select specific columns
const { results: emails } = await db
  .prepare('SELECT id, email FROM users')
  .all();

// With conditions
const { results: activeUsers } = await db
  .prepare(`
    SELECT * FROM users
    WHERE status = ?
    AND created_at > ?
    ORDER BY created_at DESC
    LIMIT 10
  `)
  .bind('active', '2024-01-01')
  .all();

Insert Queries

// Insert single row
const id = crypto.randomUUID();
await db
  .prepare('INSERT INTO users (id, email, name, password_hash) VALUES (?, ?, ?, ?)')
  .bind(id, '[email protected]', 'John Doe', hashedPassword)
  .run();

// Insert and return the row (use a separate query)
await db
  .prepare('INSERT INTO users (id, email, name, password_hash) VALUES (?, ?, ?, ?)')
  .bind(id, email, name, passwordHash)
  .run();

const user = await db
  .prepare('SELECT id, email, name FROM users WHERE id = ?')
  .bind(id)
  .first();

Update Queries

// Update by ID
await db
  .prepare('UPDATE users SET name = ?, updated_at = ? WHERE id = ?')
  .bind('Jane Doe', new Date().toISOString(), userId)
  .run();

// Conditional update
await db
  .prepare(`
    UPDATE posts
    SET status = 'published'
    WHERE author_id = ?
    AND status = 'draft'
  `)
  .bind(userId)
  .run();

Delete Queries

// Delete by ID
await db
  .prepare('DELETE FROM users WHERE id = ?')
  .bind(userId)
  .run();

// Delete with conditions
await db
  .prepare('DELETE FROM sessions WHERE expires_at < ?')
  .bind(new Date().toISOString())
  .run();

Relationships

Joining Tables

// Inner join
const { results: postsWithAuthors } = await db
  .prepare(`
    SELECT
      posts.id,
      posts.title,
      posts.content,
      users.name as author_name,
      users.email as author_email
    FROM posts
    INNER JOIN users ON users.id = posts.author_id
  `)
  .all();

// Left join
const { results: usersWithPosts } = await db
  .prepare(`
    SELECT
      users.id,
      users.name,
      posts.title as post_title
    FROM users
    LEFT JOIN posts ON posts.author_id = users.id
  `)
  .all();

Subqueries

// Users with post count
const { results: usersWithCounts } = await db
  .prepare(`
    SELECT
      users.id,
      users.name,
      (SELECT COUNT(*) FROM posts WHERE posts.author_id = users.id) as post_count
    FROM users
  `)
  .all();

Transactions

Use batch() for atomic operations:

const userId = crypto.randomUUID();
const profileId = crypto.randomUUID();

// All statements execute atomically
await db.batch([
  db.prepare('INSERT INTO users (id, email, name, password_hash) VALUES (?, ?, ?, ?)')
    .bind(userId, email, name, hash),
  db.prepare('INSERT INTO profiles (id, user_id, bio, avatar_url) VALUES (?, ?, ?, ?)')
    .bind(profileId, userId, '', null),
]);

// If any statement fails, all changes are rolled back

Aggregations

// Count
const countResult = await db
  .prepare('SELECT COUNT(*) as count FROM users')
  .first();
const total = countResult?.count ?? 0;

// Group by with aggregation
const { results: monthlyStats } = await db
  .prepare(`
    SELECT
      strftime('%Y-%m', created_at) as month,
      COUNT(*) as count
    FROM posts
    WHERE author_id = ?
    GROUP BY month
    ORDER BY month DESC
  `)
  .bind(userId)
  .all();

Best Practices

Performance Tips

• Use indexes for frequently queried columns
• Use LIMIT and pagination for large result sets
• Select only needed columns instead of SELECT *
• Use batch() for multiple related operations
• Use prepared statements with .bind() to prevent SQL injection

Pagination

const PAGE_SIZE = 20;

export async function loader({ request, context }: LoaderArgs) {
  const url = new URL(request.url);
  const page = parseInt(url.searchParams.get('page') ?? '1');
  const offset = (page - 1) * PAGE_SIZE;

  const db = context.env.DB;

  const [usersResult, countResult] = await Promise.all([
    db.prepare(`
      SELECT * FROM users
      ORDER BY created_at DESC
      LIMIT ? OFFSET ?
    `)
      .bind(PAGE_SIZE, offset)
      .all(),
    db.prepare('SELECT COUNT(*) as total FROM users').first(),
  ]);

  return {
    users: usersResult.results,
    pagination: {
      page,
      pageSize: PAGE_SIZE,
      total: countResult?.total ?? 0,
      totalPages: Math.ceil((countResult?.total ?? 0) / PAGE_SIZE),
    },
  };
}

Error Handling

export async function loader({ params, context }: LoaderArgs) {
  const db = context.env.DB;

  try {
    const user = await db
      .prepare('SELECT * FROM users WHERE id = ?')
      .bind(params.id)
      .first();

    if (!user) {
      throw new NotFoundError('User not found');
    }

    return { user };
  } catch (error) {
    if (error instanceof NotFoundError) {
      throw error;
    }
    console.error('Database error:', error);
    throw new Error('Failed to load user');
  }
}

TypeScript Support

Define types for your database rows:

// lib/db/types.ts
export interface User {
  id: string;
  email: string;
  name: string;
  password_hash: string;
  created_at: string;
  updated_at: string;
}

export interface Post {
  id: string;
  title: string;
  content: string;
  author_id: string;
  status: 'draft' | 'published';
  created_at: string;
  updated_at: string;
}

Use generics with D1 queries:

import type { User, Post } from '../lib/db/types';

export async function loader({ params, context }: LoaderArgs) {
  const db = context.env.DB;

  const user = await db
    .prepare('SELECT * FROM users WHERE id = ?')
    .bind(params.id)
    .first<User>();

  const { results: posts } = await db
    .prepare('SELECT * FROM posts WHERE author_id = ?')
    .bind(params.id)
    .all<Post>();

  return { user, posts };
}

Next Steps

• Data Loading - Server-side data fetching patterns
• Forms and Actions - Handle form submissions
• Authentication - Protect your routes