D1 Database

Aero2 uses Cloudflare D1 as its primary database. D1 is a SQLite-compatible database that runs at the edge alongside Workers.

What is D1

D1 provides a familiar SQL interface (SQLite dialect) with automatic replication and low-latency reads from Cloudflare's global network. It is accessed through the DB binding in the Worker.

Query Patterns

All database queries use prepared statements with parameter binding to prevent SQL injection.

Single Row

const user = await env.DB.prepare(
  "SELECT * FROM users WHERE id = ? AND app_id = ?"
).bind(userId, appId).first();

Multiple Rows

const { results } = await env.DB.prepare(
  "SELECT * FROM users WHERE app_id = ? ORDER BY created_at DESC"
).bind(appId).all();

Insert / Update / Delete

const result = await env.DB.prepare(
  "INSERT INTO users (id, email, app_id) VALUES (?, ?, ?)"
).bind(id, email, appId).run();
// result.meta.changes = number of rows affected

Atomic Consumption (DELETE ... RETURNING)

Used for one-time-use tokens like authorization codes and OAuth state:

DELETE FROM oauth_state
WHERE state = ? AND idp_name = ? AND expires_at > datetime('now')
RETURNING redirect_uri

Batch Operations

D1 supports batch operations for executing multiple statements in a single round trip. This is the closest equivalent to transactions:

const results = await env.DB.batch([
  env.DB.prepare("INSERT INTO users (id, email, app_id) VALUES (?, ?, ?)").bind(id, email, appId),
  env.DB.prepare("INSERT INTO user_roles (user_id, role_id) VALUES (?, ?)").bind(id, roleId),
]);

All statements in a batch execute atomically -- if any statement fails, all are rolled back.

Local Development

During local development, Wrangler creates a local SQLite database that mirrors the D1 API:

# Create local database and apply schema
npx wrangler d1 execute aero2 --local --file=./migrations/0001_initial_schema.sql
 
# Run ad-hoc queries locally
npx wrangler d1 execute aero2 --local --command="SELECT COUNT(*) FROM users"

The local database is stored in .wrangler/state/ and is not committed to version control.

Remote Database Operations

# Apply migrations to QA
npx wrangler d1 execute aero2 --env qa --file=./migrations/0002_multi_tenancy.sql
 
# Apply migrations to production
npx wrangler d1 execute aero2 --env production --file=./migrations/0002_multi_tenancy.sql
 
# Query remote database (use sparingly)
npx wrangler d1 execute aero2 --env production --command="SELECT COUNT(*) FROM users"

Limitations

Limitation	Details
No `ALTER TABLE DROP COLUMN`	SQLite does not support dropping columns. Use the expand-contract pattern: add a new column, migrate data, stop using the old column.
Row size	Maximum 1 MB per row (including all columns)
Database size	10 GB per database (paid plan)
Reads per second	Designed for moderate read throughput; not a replacement for dedicated databases
No JOINs across databases	Each D1 database is independent
No stored procedures	All logic lives in the Worker code

Migration Strategy

Database schema changes are managed through numbered SQL migration files in the migrations/ directory:

migrations/
  0001_initial_schema.sql
  0002_multi_tenancy.sql
  0003_account_lockout.sql
  ...

Migrations are applied manually using npm run migrate:qa and npm run migrate:prod. See Database Migrations for the full workflow.

Schema Conventions

All primary keys use TEXT type with UUID values (generated via crypto.randomUUID())
Timestamps use DATETIME type with DEFAULT (datetime('now')) for auto-population
JSON data is stored as TEXT columns (D1/SQLite does not have a native JSON type, but supports JSON functions)
Boolean columns use BOOLEAN (stored as INTEGER 0/1 in SQLite)
Foreign keys include ON DELETE CASCADE where appropriate
All tenant-scoped tables include app_id TEXT NOT NULL REFERENCES applications(id)