D1 Database

Cloudflare D1 is a serverless SQLite database that runs at the edge. CruzJS wraps D1 with Drizzle ORM to provide type-safe queries, automatic migrations, and a seamless local development experience using SQLite.

How CruzJS Uses D1

CruzJS never accesses D1 directly. Instead, the DrizzleService initializes a Drizzle ORM instance from the D1 binding and makes it available through dependency injection:

import { injectable, inject } from 'inversify';
import {
  DRIZZLE,
  type DrizzleDatabase,
} from '@cruzjs/core/shared/database/drizzle.service';
import { projects } from '../database/schema';
import { eq } from 'drizzle-orm';

@injectable()
export class ProjectService {
  constructor(@inject(DRIZZLE) private readonly db: DrizzleDatabase) {}

  async getProject(id: string) {
    const [project] = await this.db
      .select()
      .from(projects)
      .where(eq(projects.id, id))
      .limit(1);
    return project ?? null;
  }
}

The DRIZZLE injection token resolves to a DrizzleD1Database instance in production and a BetterSQLite3Database instance in local development. Both are API-compatible — your service code works unchanged in both environments.

Database Initialization

The database is initialized per-request inside CloudflareContext.init():

// CloudflareContext.init() calls this internally:
await DrizzleService.initFromContext(env?.DB);

initFromContext follows this logic:

1. If a D1 binding is provided (production/wrangler), create a Drizzle instance wrapping D1
2. If no D1 binding (local dev without wrangler), create a local SQLite database at ./data/local.db
3. If already initialized, skip (avoids redundant setup)

Schema Registration

The schema is registered through createCruzApp(), which passes it to the Drizzle initialization:

server.cloudflare.ts

import { createCruzApp } from '@cruzjs/core';
import { CloudflareAdapter } from '@cruzjs/adapter-cloudflare';
import * as schema from './database/schema';

export default createCruzApp({
  schema,
  modules: [],
  adapter: new CloudflareAdapter(),
  pages: () => import('virtual:react-router/server-build'),
});

This enables Drizzle’s relational query builder and proper type inference.

Local SQLite Fallback

The LocalDb class manages the local SQLite database for development:

import Database from 'better-sqlite3';
import { drizzle } from 'drizzle-orm/better-sqlite3';

// LocalDb automatically:
// 1. Creates the directory and database file
// 2. Enables WAL mode for better concurrent access
// 3. Enables foreign key constraints
// 4. Runs pending migrations

The local database file defaults to ./data/local.db. Override with the LOCAL_DB_PATH environment variable.

Database Commands

# Generate migration files from schema changes
cruz db generate

# Apply migrations to local D1 / SQLite
cruz db migrate

# Apply migrations to remote D1 (production)
cruz db migrate --remote

# Execute raw SQL
cruz db query "SELECT * FROM AuthIdentity LIMIT 5"

# Open Drizzle Studio (visual database browser)
cruz db studio

# Seed the database with test data
cruz db seed

# Delete local database and re-migrate from scratch
cruz db hard-reset

D1 Schema Conventions

CruzJS uses SQLite-compatible Drizzle schemas. Since D1 is SQLite under the hood, the schema uses sqliteTable and stores complex types as text:

import { sqliteTable, text, integer, index } from 'drizzle-orm/sqlite-core';
import { createId } from '@paralleldrive/cuid2';

const generateId = () => createId();
const nowISO = () => new Date().toISOString();

export const projects = sqliteTable('Project', {
  id: text('id').primaryKey().$defaultFn(generateId),
  name: text('name').notNull(),
  orgId: text('orgId').notNull(),
  settings: text('settings').default('{}'),       // JSON as text
  isArchived: integer('isArchived', { mode: 'boolean' }).default(false),
  createdAt: text('createdAt').notNull().$defaultFn(nowISO),
  updatedAt: text('updatedAt').notNull().$defaultFn(nowISO),
}, (table) => ({
  orgIdIdx: index('Project_orgId_idx').on(table.orgId),
}));

Key conventions:

• IDs: CUID2 text primary keys (not auto-increment integers)
• Timestamps: ISO 8601 text strings (not native timestamps — D1/SQLite has limited datetime support)
• Booleans: integer with { mode: 'boolean' } (SQLite stores 0/1)
• JSON: text with default('{}'), parsed/stringified at the application level
• Enums: text with application-level validation (no native enum support in SQLite)

D1 Limitations

D1 is SQLite, which means some PostgreSQL features are unavailable:

Feature	D1/SQLite	Workaround
JSON columns	No native JSONB	Store as text, parse in app
Array columns	No arrays	Store as JSON text
Enum types	No enums	Use text + validation
Full-text search	Limited FTS5	Use Workers AI for search
Row count on delete/update	Not returned	Query count separately
Transactions	Supported	Use db.transaction()
Max DB size	10GB (paid plan)	Partition across databases
Max row size	1MB	Store large blobs in R2

Read Replication

D1 automatically replicates reads to edge locations closest to the user. Writes always go to the primary location. This means:

• Reads are fast globally (served from nearest replica)
• Writes have slightly higher latency (routed to primary)
• Consistency is eventual for reads after writes — if you need to read your own writes immediately, use the d1_read_after_write header or structure your code to use the returned data from the write operation

// Good: Use the returned data immediately after a write
const [newProject] = await this.db
  .insert(projects)
  .values({ name: 'My Project', orgId })
  .returning();

// The returned `newProject` has the inserted data
// No need to re-query

Connection Management

D1 connections are managed automatically per-request:

1. CloudflareContext.init() sets the D1 binding on DrizzleService
2. Services inject DRIZZLE and get the current request’s database instance
3. No connection pooling needed — D1 handles this internally
4. No cleanup needed — connections are scoped to the request lifecycle

In local development, a single SQLite connection is shared across requests (via DrizzleService.db static property) with WAL mode enabled for concurrent access.

Next Steps

• Database Patterns — Advanced query patterns with Drizzle
• Migrations — Managing schema changes
• KV Storage — Caching query results with KV