Database

The app's data lives in Postgres and is queried through Drizzle. The Worker reaches it through Cloudflare Hyperdrive, and on your machine it is PGlite, Postgres compiled to WebAssembly, so you need no database server. You work here when you add tables, write queries or ship a migration.

How it works

1. useDrizzle(event) returns a Drizzle client over the pg driver. useDrizzle, tables and the common query helpers are auto-imported in server/.
2. On the Worker it opens a pool of up to five connections per request to the HYPERDRIVE binding and closes it after the response. Under pnpm dev there is one pool for the whole process, to DATABASE_URL.
3. The template's tables are in server/database/schema.ts(Licensees only), yours in server/database/custom/schema.ts(Licensees only), and tables holds both.
4. Each file has its own migration chain. pnpm db:migrate applies both, the template's first.

Locally

pnpm dev opens a PGlite database in .data/pglite, applies pending migrations, and serves it on 127.0.0.1:5432 with DATABASE_URL pointing at it. If something else holds 5432, such as another project's dev server, PGlite takes the next free port up to 5441 and the dev server logs which one. When 5432 to 5441 are all taken, the dev server stops. While it runs, it keeps PGlite's URL in .data/pglite-server.json, where pnpm db:migrate, pnpm db:seed and pnpm db:studio read it. With the dev server stopped, the scripts open .data/pglite directly, except pnpm db:studio, which needs the dev server running.

Each project's data stays in its own .data/pglite, so several kit projects can run pnpm dev at once and the port never changes which data you see. For psql or another client that needs a fixed address, set NUXT_DEV_PGLITE_PORT in .env. The dev server then tries only that port and stops if it is taken, so pick one outside 5432 to 5441, where an unpinned project can land first.

To use a real Postgres, such as a container or a branch of production, set DATABASE_URL in .env. The dev server then starts no PGlite and migrates nothing, so run pnpm db:migrate yourself. It warns at startup when that host isn't your machine.

pnpm preview runs the built Worker under wrangler dev. It reaches the database through the Hyperdrive binding and ignores DATABASE_URL. It needs a real Postgres with a password in its URL, set in .env:

CLOUDFLARE_HYPERDRIVE_LOCAL_CONNECTION_STRING_HYPERDRIVE=postgres://postgres:postgres@127.0.0.1:5450/postgres

Migrate that database, then start the preview:

DATABASE_URL=postgres://postgres:postgres@127.0.0.1:5450/postgres pnpm db:migrate
pnpm preview

Without that variable it uses the binding's localConnectionString, 127.0.0.1:5432, which is usually a dev server's PGlite, this project's or another's. Before building, pnpm preview refuses PGlite, a URL with no password, and an address where nothing answers.

Tests skip the socket. Each test file opens its own PGlite from a migrated copy and truncates every table before each test (Testing).

Add a table

Put it in server/database/custom/schema.ts, which ships empty. Import a template table to reference it:

import { index, integer, pgTable, text, timestamp } from 'drizzle-orm/pg-core'

import { organizations } from '../schema'

export const menus = pgTable(
  'menus',
  {
    id: integer('id').primaryKey().generatedByDefaultAsIdentity(),
    orgId: integer('org_id')
      .notNull()
      .references(() => organizations.id, { onDelete: 'cascade' }),
    title: text('title').notNull(),
    createdAt: timestamp('created_at', { withTimezone: true })
      .notNull()
      .$defaultFn(() => new Date()),
  },
  (table) => [
    index('menus_org_created_idx').on(table.orgId, table.createdAt, table.id),
  ],
)

pnpm db:generate
pnpm db:migrate

The migration lands in server/database/migrations/custom/, and tables.menus works wherever tables.users does.

• To store more about a template row, add a table of your own keyed by its id, such as user_profiles whose primary key user_id references users.id. A column added to schema.ts belongs to the template's chain, and the next kit update forks it.
• Import template tables, never re-export them from custom/schema.ts, or drizzle-kit creates them a second time.
• Only custom/schema.ts is read. If you split your tables across files in custom/, re-export each from it: export * from './menus'.
• PGlite loads only the extensions in PGLITE_EXTENSIONS in scripts/db/lib/local-postgres.mjs(Licensees only), which is pg_trgm. A create extension of your own, such as pgvector, fails in pnpm dev and in tests until you add it there.

Query

server/api/menus/[org].get.ts:

import { requireOrgScope } from '#server/tenancy/scope'

export default defineEventHandler(async (event) => {
  const scope = await requireOrgScope(event, getRouterParam(event, 'org') ?? '')

  return useDrizzle(event)
    .select({ id: tables.menus.id, title: tables.menus.title })
    .from(tables.menus)
    .where(eq(tables.menus.orgId, scope.orgId))
    .orderBy(desc(tables.menus.createdAt), desc(tables.menus.id))
})

The auto-imported helpers are and, or, eq, ne, gte, lt, lte, inArray, notInArray, isNull, isNotNull, desc, sql, count, countDistinct, max and sum. Import anything else, such as asc or ilike, from drizzle-orm. isUniqueConstraintError(error) is auto-imported too and spots a unique violation anywhere in the error's cause chain.

Postgres refuses an integer above 2,147,483,647 bound to an integer column and any string holding U+0000, and the route answers 500. Validate ids with rowIdSchema() or rowIdParamSchema() and stored text with storableTextSchema(), all from #shared/schemas/storable. For ilike searches, containing(term) from #server/database/text-search builds the pattern with %, _ and \ escaped.

Transactions

Pick the shape from what the write depends on.

• Writes that only need to land together: Drizzle's own db.transaction(async (tx) => …), at read committed, Postgres's default.
• A write that depends on rows it reads, such as "remove this owner if another remains": serializable(db, async (tx) => …) from #server/database/transaction. Two of these racing can't both pass the check. Postgres aborts one, and serializable reruns the callback, up to five attempts, on a serialization failure or a deadlock.
• Reads that must agree, such as a page and its total: snapshot(db, async (tx) => …), one read-only transaction at repeatable read.

Deleting a menu only while the organization keeps another:

import { serializable } from '#server/database/transaction'

await serializable(useDrizzle(event), async (tx) => {
  const [row] = await tx
    .select({ total: count() })
    .from(tables.menus)
    .where(eq(tables.menus.orgId, orgId))

  if ((row?.total ?? 0) <= 1) {
    throw createError({ statusCode: 409, statusMessage: 'Keep one menu.' })
  }

  await tx
    .delete(tables.menus)
    .where(and(eq(tables.menus.id, menuId), eq(tables.menus.orgId, orgId)))
})

A serializable callback can run more than once, so it touches nothing but tx. Record activity, emit events and send mail after it returns.

A compare-and-set on the row you write needs no transaction: update … set consumed_at = now() where token_hash = $1 and consumed_at is null returning … gives the row to exactly one of two racing requests.

Migrations

The deploy applies migrations before it uploads the Worker (Deployment). A migration that drops or narrows something the live code still uses must be split across two releases, add first and drop later.

The migrator records each chain in its own table in the drizzle schema, __drizzle_migrations and __drizzle_migrations_custom, and applies a chain's pending migrations in one transaction. Kit updates bring the template's migrations in files you never edit, so they don't conflict with yours.

Once anyone has applied a migration, leave it alone and write a new one. test/unit/migration-freshness.test.ts(Licensees only) fails when a schema changed without its generate command, when an app table or export takes a template table's name, and when a chain forks.

Seed data

pnpm db:seed                          # 50 accounts, 5 of them admins
pnpm db:seed --count 200 --admins 12
pnpm db:seed --reset                  # delete every @example.com account first
pnpm db:seed --dry-run                # print the SQL, touch nothing

admin@example.com (superadmin), author@example.com (staff) and user@example.com come first. Every account shares the password seed-password-123 (--password changes it) and owns an organization, as a signup would. Accounts are upserted by address, so a rerun updates them in place without signing anyone out. pnpm db:seed:purchases adds purchases for the recent-purchase feed.

Both seeds write only to a database on your machine. They refuse a DATABASE_URL whose host is anything but localhost, a 127.x address, ::1 or a Unix socket.

Production database

Any Postgres provider Hyperdrive can reach works. The first deploy is where a missing piece shows up:

• Create the Hyperdrive with --caching-disabled. A cached read can miss a write made a moment before it, so a revoked session would still sign in. The deploy stops while caching is on.
• Give Hyperdrive the database's direct connection string, not the provider's pooler.
• The first migration runs CREATE EXTENSION IF NOT EXISTS pg_trgm, so the provider must offer it and the role needs CREATE on the database (Postgres 13 or later).
• The deploy migrates from a GitHub-hosted runner, which connects over public IPv4 only. Supabase's direct connection is IPv6-only without its IPv4 add-on.
• The migration checks the server certificate. For one no public authority signed, name its CA in DATABASE_URL with sslrootcert.
• Leave room in max_connections for Hyperdrive's pool, a migration and Studio. On Workers Free, Hyperdrive also stops at 100,000 queries a day.
• The kit takes no backups. Turn on point-in-time recovery at the provider.

pnpm db:studio:remote opens Drizzle Studio on production, reading DATABASE_URL from .env.production or the shell and never from .env.

Configuration

Good to know

• Inside a transaction callback, query through tx, never useDrizzle(event). On the Worker that query runs on another connection, outside the transaction, and in tests it waits on the open transaction forever.
• The request's pool closes after the response, so nothing can query through its event afterwards. Move work that outlives the response to a job (Background jobs).
• In a correlated subquery, write ${tables.organizations}.id and alias the inner table. Without a join, Drizzle writes select-list columns unqualified, and "id" inside the subquery binds to the inner table.
• Give every column that names a user a foreign key with cascade or set null, or account deletion leaves it behind. test/unit/schema-cascades.test.ts(Licensees only) checks columns named user_id, subject_user_id and actor_user_id in both schemas. A column with any other name goes unchecked.
• Hyperdrive shares connections between requests, so anything that keeps state on one connection, such as LISTEN/NOTIFY or session-level advisory locks, doesn't work through it.
• A statement Hyperdrive couldn't send to the database, for example while a Neon compute wakes from scale-to-zero, goes again, up to three attempts, and so does a transaction's begin. Nothing Postgres itself refused is resent.