Database

App data lives in Cloudflare D1, a SQLite database, and you work with it through Drizzle ORM. The client in src/db/index.ts connects to the DB binding, so it only runs inside the Worker or the local dev server.

Schema

Tables are defined in src/db/schema.ts with sqliteTable. Copy the shape of the notes table: an integer id, timestamps from the createdAt() and updatedAt() helpers, and an organizationId so every row belongs to a team.

export const projects = sqliteTable('projects', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  createdAt: createdAt(),
  updatedAt: updatedAt(),
  name: text('name').notNull(),
  meta: text('meta', { mode: 'json' }).$type<Record<string, unknown>>(),
  organizationId: text('organization_id').notNull().references(() => organization.id, { onDelete: 'cascade' }),
  userId: integer('user_id').references(() => users.id, { onDelete: 'set null' }),
}, (t) => [index('idx_projects_org_updated').on(t.organizationId, t.updatedAt)]);

SQLite has no varchar, jsonb or serial. Use text, text({ mode: 'json' }) and integer({ autoIncrement: true }). User ids are integers; team, member and session ids are text.

Migrations

1. 1

   Change the schema

   Edit src/db/schema.ts.

2. 2

   Generate the migration

   Run bun run db:generate. Drizzle writes a numbered SQL file to migrations/.

3. 3

   Read the SQL

   SQLite rebuilds a table for many column changes. Check the file for drops or copies you didn't intend.

4. 4

   Apply it locally

   Run bun run db:migrate:local, then build and try the change.

5. 5

   Apply it to production

   Run bun run db:migrate before you deploy code that reads the new columns.

Wrangler records applied files in the d1_migrations table. Never edit a migration that's been applied. Add a new one.

Querying

Every read and write filters by team. Middleware puts the current team in Astro.locals.organizationId, already checked against the user's memberships.

import { and, desc, eq } from 'drizzle-orm';
import { db } from '@/db';
import { projects } from '@/db/schema';

const orgId = Astro.locals.organizationId;
const rows = await db.select().from(projects)
  .where(eq(projects.organizationId, orgId))
  .orderBy(desc(projects.updatedAt));

await db.update(projects).set({ name })
  .where(and(eq(projects.id, Number(id)), eq(projects.organizationId, orgId)));

Never trust an id from a URL or form on its own. Always pair it with the team id, as in the update above.

D1 limits

• There are no interactive transactions. db.transaction() doesn't work; use db.batch([...]), which runs its statements atomically.
• A statement can bind at most 100 parameters, so chunk large inserts and inArray() lists.
• like is already case-insensitive for ASCII text, and there's no ilike.

Inspecting data

scripts/db-query.ts runs one read-only statement and prints JSON:

echo "SELECT id, email FROM users LIMIT 5" | node scripts/db-query.ts
echo "SELECT count(*) FROM notes" | node scripts/db-query.ts --local --format table

It refuses writes unless you pass --write, and writes to the remote database also need --confirm "write remote". bun run db:studio opens Drizzle Studio if you'd rather browse.