Database

TheShipStack uses Drizzle ORM with PostgreSQL (Neon in prod, Docker locally).

Schema location

All tables are defined in db/schema/. Each file exports one or more table definitions.

db/
  schema/
    users.ts       ← auth user table (managed by Better Auth)
    profiles.ts    ← user profile table
    uploads.ts     ← file upload records
    index.ts       ← re-exports all tables
  index.ts         ← exports the db client

Adding a table

Create a new file in db/schema/:

// db/schema/posts.ts
import { pgTable, text, timestamp, uuid } from 'drizzle-orm/pg-core'
import { users } from './users'

export const posts = pgTable('posts', {
  id: uuid('id').primaryKey().defaultRandom(),
  userId: uuid('user_id').notNull().references(() => users.id, { onDelete: 'cascade' }),
  title: text('title').notNull(),
  content: text('content').notNull(),
  createdAt: timestamp('created_at').notNull().defaultNow(),
})

Re-export it from db/schema/index.ts:

export * from './posts'

Push the schema to your database:

pnpm db:push

Querying

Import the db client and query:

import { db } from '@/db'
import { posts } from '@/db/schema'
import { eq } from 'drizzle-orm'

const userPosts = await db.select().from(posts).where(eq(posts.userId, userId))

Visualizing the database

See Local Development for Drizzle Studio and direct Postgres client connection details.

Conventions

Never use process.env.DATABASE_URL directly — it's validated and exported from lib/env.ts
Use uuid primary keys with defaultRandom()
Always add createdAt and updatedAt timestamps to tables
Cascade deletes from users to user-owned tables

Schema location

Adding a table

Querying

Visualizing the database

Conventions

On this page