Add a Database Table

Add a Database Table

This fork stores all data in a single shared Postgres database, scoped by user_id on every user-owned table , see The Postgres port for the full architecture. Adding a new table takes three steps: define the schema, generate + apply a migration, then use it in a server function.

Step 1: Add the table to the schema

Open src/lib/db/schema.ts and add a new pgTable:

export const notes = pgTable(
  'notes',
  {
    id: text('id').primaryKey(),
    userId: text('user_id')
      .notNull()
      .references(() => users.id, { onDelete: 'cascade' }),
    content: text('content').notNull().default(''),
    createdAt: timestamp('created_at').notNull()
  },
  table => [index('notes_user_id_idx').on(table.userId)]
);

Rules:

• Every user-owned table needs a userId column with .references(() => users.id, { onDelete: 'cascade' }) , the cascade is what makes account deletion clean up this table automatically, with no manual cleanup step.
• Index userId , every query on this table will filter by it.

Step 2: Generate and apply the migration

bun run db:generate   # drizzle-kit generate , writes a new drizzle/*.sql file from the schema diff
bun run db:migrate    # scripts/migrate.ts , applies pending migrations against DATABASE_URL

Commit the generated drizzle/*.sql file , it's the actual migration, not just a schema description. Never hand-edit a migration that's already been applied in any environment; add a new one instead.

Step 3: Query the table

import { eq } from 'drizzle-orm';
import { uuidv7 } from 'uuidv7';
import { db } from '@/lib/db';
import { notes } from '@/lib/db/schema';
import { withUserTransaction } from '@/lib/db/user-db';

// Read: query the shared pool directly, always filter by userId
const rows = await db.select().from(notes).where(eq(notes.userId, user.id));

// Write: inside withUserTransaction, using the tx handle for every query
return withUserTransaction(user.id, async (tx, userId) => {
  await tx.insert(notes).values({
    id: uuidv7(),
    userId,
    content: data.content,
    createdAt: new Date()
  });
  return ok({ created: true });
});

The closure passed to withUserTransaction must use the tx handle it receives for every query inside , calling a helper with the outer db instead breaks atomicity silently. withUserTransaction is not a mutex like the base template's withWriteLock: it's a real Postgres transaction at read-committed isolation, so a concurrent check-then-write on the same row can still race , see src/lib/db/user-db.ts's header comment before assuming otherwise.

Step 4: Test it

import { describe, expect, it } from 'bun:test';
import { eq } from 'drizzle-orm';
import { notes } from '@/lib/db/schema';
import { users } from '@/lib/db/schema';
import { withTestDb } from '@/test/db';
import { makeUser } from '@/test/fixtures';

describe('notes', () => {
  it('creates a note', () =>
    withTestDb(async tx => {
      const [user] = await tx.insert(users).values(makeUser()).returning();
      await tx.insert(notes).values({
        id: '1',
        userId: user?.id as string,
        content: 'hello',
        createdAt: new Date()
      });
      const [row] = await tx.select().from(notes).where(eq(notes.id, '1'));
      expect(row?.content).toBe('hello');
    }));
});

withTestDb runs the whole test body inside a real transaction against the dockerized test Postgres, then always rolls it back , no mocks, no in-memory substitute (Postgres has no :memory: mode). Pass the tx handle to every DB call inside the test, not the ambient db export, or writes escape the rollback and leak into the next test.