setup-database

Set up D1 SQLite database for your Cloudflare Workers application.

Overview

The setup-database command configures Cloudflare D1, a serverless SQL database, with migrations, type-safe queries, and database utilities for your edge application.

Usage

/template setup-database <database-name> [options]

Parameters

• <database-name> - Name of the D1 database (e.g., app-db, user-data)

Options

• --with-migrations - Include migration system (default: true)
• --with-seed - Create seed data script
• --with-drizzle - Use Drizzle ORM (recommended)
• --tables - Comma-separated list of initial tables to create

Examples

Basic Database Setup

/template setup-database app-db

With Initial Tables

/template setup-database user-data --tables "users,posts,comments"

Full Setup with ORM

/template setup-database main-db --with-drizzle --with-seed

What It Creates

Database Configuration

• D1 binding in wrangler.toml
• Migration system setup
• Database utilities
• Type-safe query builders

Generated Structure

src/
├── db/
│   ├── schema/
│   │   ├── users.ts       # User table schema
│   │   ├── posts.ts       # Posts table schema
│   │   └── index.ts       # Combined schema
│   ├── migrations/
│   │   └── 0001_initial.sql
│   ├── queries/
│   │   ├── users.ts       # User queries
│   │   └── posts.ts       # Post queries
│   ├── client.ts          # Database client
│   └── seed.ts            # Seed data script

Example Schema (Drizzle)

// src/db/schema/users.ts
import { sqliteTable, text, integer } from 'drizzle-orm/sqlite-core';

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

Example Queries

// src/db/queries/users.ts
import { db } from '../client';
import { users } from '../schema';

export async function createUser(data: NewUser) {
  return await db.insert(users).values(data).returning();
}

export async function getUserByEmail(email: string) {
  return await db.select().from(users).where(eq(users.email, email)).get();
}

Migration System

Creating Migrations

npm run db:generate -- create_posts_table

Running Migrations

# Local development
npm run db:migrate:local

# Production
npm run db:migrate:prod

Migration Example

-- migrations/0002_create_posts.sql
CREATE TABLE posts (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id INTEGER NOT NULL,
  title TEXT NOT NULL,
  content TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users (id)
);

CREATE INDEX idx_posts_user_id ON posts (user_id);

Best Practices

1. Use Transactions: Wrap multiple operations in transactions
2. Index Strategy: Add indexes for frequently queried columns
3. Data Types: Use appropriate SQLite data types
4. Migrations: Never modify existing migrations
5. Backup: Regular backups for production data

Performance Optimization

• Prepared statements for repeated queries
• Connection pooling
• Query result caching
• Batch operations
• Proper indexing

Common Patterns

User Authentication

// With KV for sessions
export async function createSession(userId: number, env: Env) {
  const session = crypto.randomUUID();
  await env.SESSIONS.put(session, userId.toString(), {
    expirationTtl: 86400, // 24 hours
  });
  return session;
}

Full-Text Search

-- Enable FTS5
CREATE VIRTUAL TABLE posts_fts USING fts5(
  title, content, content=posts
);

Soft Deletes

export const posts = sqliteTable('posts', {
  // ... other fields
  deletedAt: integer('deleted_at', { mode: 'timestamp' }),
});

Integration Points

• Workers KV: For caching
• Queues: For async operations
• R2: For file references
• Analytics: For query performance

Limitations

• 10GB storage limit per database
• 1000 databases per account
• SQLite limitations apply
• No direct TCP connections

Advanced Features

Read Replicas

// Use read replicas for queries
const result = await db
  .select()
  .from(users)
  .limit(10)
  .all({ consistency: 'eventual' });

Database Metrics

// Track query performance
const start = Date.now();
const result = await query();
await env.ANALYTICS.track('db_query', {
  duration: Date.now() - start,
  table: 'users',
});

Related Commands

• add-api-route - Create database API endpoints
• setup-auth - Add authentication with D1
• add-middleware - Add database middleware