Database guide

ShockStack talks to Postgres from the frontend via Drizzle ORM. The schema backs Better Auth and any application tables you add. Drizzle Kit handles migrations and the local studio.

Versions: drizzle-orm ^0.45, drizzle-kit ^0.31, postgres ^3.4 (postgres.js driver), Postgres 17.

Layout

frontend/
├── drizzle.config.ts          # schema + output paths
├── drizzle/                   # generated migrations (tracked)
└── src/lib/db/
    ├── client.ts              # drizzle() instance
    ├── schema.ts              # table definitions
    └── seed.ts                # `pnpm db:seed`

Local Postgres

You can stand up Postgres any way you like. The repo ships with Compose and Aspire flavours.

Start Postgres

bash

option 1 — docker compose (frontend-only mode)

$ docker compose -f docker/docker-compose.dev.yml up -d postgres

option 2 — .NET Aspire orchestrates postgres + api + frontend

$ ss dev

Connection URL for both:

DATABASE_URL=postgres://postgres:postgres@localhost:5432/shockstack

Schema

src/lib/db/schema.ts defines the tables. The built-in set covers authentication:

Table	Purpose
`users`	Profile (name, email, emailVerified, image).
`sessions`	Browser/API sessions with cascade delete to `users`.
`accounts`	OAuth provider tokens + password hash for the email flow.
`verifications`	Email / OTP verification records.

Tables use timestamp().defaultNow() and use FK cascade deletes so deleting a user cleans up their sessions and accounts.

Better Auth owns the auth tables

Don’t edit users, sessions, accounts, or verifications column-by-column without checking better-auth/adapters/drizzle first — their shape is dictated by the adapter. Add new app tables alongside them, with FKs to users.id if needed.

Generating migrations

Change schema.ts, then:

pnpm --filter frontend exec drizzle-kit generate

This emits a new SQL file in frontend/drizzle/. Commit it alongside the schema change — migrations are part of the review.

Applying migrations

pnpm ss db migrate

That wraps drizzle-kit migrate against the current DATABASE_URL. Use it locally and in CI before the app starts.

Resetting (local only)

pnpm ss db reset         # drops + migrates + seeds. Prompts first.
pnpm ss db reset --force # skip the prompt

The command refuses to run against a non-localhost URL.

Seeding

src/lib/db/seed.ts creates a demo user via Better Auth’s sign-up/email endpoint so password hashing stays consistent with live sign-ups.

pnpm ss db seed
# or: pnpm --filter frontend db:seed

Edit seed.ts to add your own fixtures.

Drizzle Studio

Visual browser for rows and schema:

pnpm ss db studio

Opens on local.drizzle.studio.

Querying from Astro

The db export is a typed Drizzle instance wired to the schema.

// src/pages/users/[id].astro
---
import { eq } from "drizzle-orm";
import { db } from "../../lib/db/client";
import { users } from "../../lib/db/schema";

const { id } = Astro.params;
const [user] = await db
  .select()
  .from(users)
  .where(eq(users.id, id!))
  .limit(1);

if (!user) return Astro.redirect("/404");
---
<h1>{user.name}</h1>

For the current session user, prefer Astro.locals.user — middleware already resolved the session.

Adding a table

Add it to schema.ts (use pgTable, FK to users.id with onDelete: "cascade" when relevant).
pnpm --filter frontend exec drizzle-kit generate.
Commit the new migration file in frontend/drizzle/.
Use pnpm ss db migrate locally; CI will run it on deploy.

Production

Provision Postgres on your platform (Neon, Supabase, RDS, self-hosted — all fine).
Set DATABASE_URL as a runtime secret.
Run pnpm ss db migrate as a release step before the app picks up the new image.
Back up before destructive migrations. Drizzle will happily drop columns if you tell it to.