Database

ZeroStarter uses Drizzle ORM with PostgreSQL for type-safe database operations and schema management.

Architecture

The database layer is centralized in the @packages/db workspace:

packages/db/
├── src/
│   ├── index.ts          # Database client and exports
│   └── schema/
│       ├── index.ts      # Schema barrel export
│       └── auth.ts       # Better Auth schema
├── drizzle/              # Generated migrations
├── drizzle.config.ts     # Drizzle Kit configuration
└── package.json

Database Client

Client Setup

The database client uses Bun’s native PostgreSQL driver:

packages/db/src/index.ts

import { env } from "@packages/env/db"
import { SQL } from "bun"
import type { BunSQLDatabase } from "drizzle-orm/bun-sql"
import { drizzle } from "drizzle-orm/bun-sql"
import * as schema from "@/schema"

type Database = BunSQLDatabase<typeof schema>

declare global {
  var db: Database
}

let db: Database

if (env.NODE_ENV === "production") {
  const client = new SQL(env.POSTGRES_URL, {
    connectionTimeout: 10,
    idleTimeout: 30,
    maxLifetime: 0,
    tls: {
      rejectUnauthorized: true,
    },
  })
  db = drizzle({ client, schema })
} else {
  if (!global.db) {
    const client = new SQL(env.POSTGRES_URL, {
      connectionTimeout: 10,
      idleTimeout: 30,
      maxLifetime: 0,
    })
    global.db = drizzle({ client, schema })
  }
  db = global.db
}

export { db }
export * from "@/schema"

The global variable pattern prevents creating multiple connections during development hot-reloads.

Connection Configuration

Production:

TLS enabled with certificate verification
Short connection timeout (10s)
Auto-closes idle connections after 30s

Development:

Reuses global connection across hot-reloads
No TLS required for local databases
Same timeout configuration

Schema Definition

Auth Schema

Better Auth requires specific tables for authentication:

User
Session
Account
Organization

packages/db/src/schema/auth.ts

export const user = pgTable("user", {
  id: text("id").primaryKey(),
  name: text("name").notNull(),
  email: text("email").notNull().unique(),
  emailVerified: boolean("email_verified").default(false).notNull(),
  image: text("image"),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at")
    .defaultNow()
    .$onUpdate(() => new Date())
    .notNull(),
})

export const session = pgTable(
  "session",
  {
    id: text("id").primaryKey(),
    expiresAt: timestamp("expires_at").notNull(),
    token: text("token").notNull().unique(),
    createdAt: timestamp("created_at").defaultNow().notNull(),
    updatedAt: timestamp("updated_at")
      .$onUpdate(() => new Date())
      .notNull(),
    ipAddress: text("ip_address"),
    userAgent: text("user_agent"),
    userId: text("user_id")
      .notNull()
      .references(() => user.id, { onDelete: "cascade" }),
    activeOrganizationId: text("active_organization_id"),
    activeTeamId: text("active_team_id"),
  },
  (table) => [index("session_userId_idx").on(table.userId)]
)

export const account = pgTable(
  "account",
  {
    id: text("id").primaryKey(),
    accountId: text("account_id").notNull(),
    providerId: text("provider_id").notNull(),
    userId: text("user_id")
      .notNull()
      .references(() => user.id, { onDelete: "cascade" }),
    accessToken: text("access_token"),
    refreshToken: text("refresh_token"),
    idToken: text("id_token"),
    accessTokenExpiresAt: timestamp("access_token_expires_at"),
    refreshTokenExpiresAt: timestamp("refresh_token_expires_at"),
    scope: text("scope"),
    password: text("password"),
    createdAt: timestamp("created_at").defaultNow().notNull(),
    updatedAt: timestamp("updated_at")
      .$onUpdate(() => new Date())
      .notNull(),
  },
  (table) => [index("account_userId_idx").on(table.userId)]
)

export const organization = pgTable(
  "organization",
  {
    id: text("id").primaryKey(),
    name: text("name").notNull(),
    slug: text("slug").notNull().unique(),
    logo: text("logo"),
    createdAt: timestamp("created_at").notNull(),
    metadata: text("metadata"),
  },
  (table) => [uniqueIndex("organization_slug_uidx").on(table.slug)]
)

export const member = pgTable(
  "member",
  {
    id: text("id").primaryKey(),
    organizationId: text("organization_id")
      .notNull()
      .references(() => organization.id, { onDelete: "cascade" }),
    userId: text("user_id")
      .notNull()
      .references(() => user.id, { onDelete: "cascade" }),
    role: text("role").default("member").notNull(),
    createdAt: timestamp("created_at").notNull(),
  },
  (table) => [
    index("member_organizationId_idx").on(table.organizationId),
    index("member_userId_idx").on(table.userId),
  ]
)

Relations

Drizzle relations enable type-safe joins:

packages/db/src/schema/auth.ts

import { relations } from "drizzle-orm"

export const userRelations = relations(user, ({ many }) => ({
  sessions: many(session),
  accounts: many(account),
  teamMembers: many(teamMember),
  members: many(member),
  invitations: many(invitation),
}))

export const sessionRelations = relations(session, ({ one }) => ({
  user: one(user, {
    fields: [session.userId],
    references: [user.id],
  }),
}))

export const organizationRelations = relations(organization, ({ many }) => ({
  teams: many(team),
  members: many(member),
  invitations: many(invitation),
}))

Database Operations

Querying Data

import { db, user } from "@packages/db"
import { eq } from "drizzle-orm"

// Find user by email
const result = await db
  .select()
  .from(user)
  .where(eq(user.email, "admin@example.com"))
  .limit(1)

const foundUser = result[0]

Relational Queries

Query with relations:

import { db, user } from "@packages/db"
import { eq } from "drizzle-orm"

// Get user with sessions and accounts
const userWithRelations = await db.query.user.findFirst({
  where: eq(user.id, userId),
  with: {
    sessions: true,
    accounts: true,
    members: {
      with: {
        organization: true,
      },
    },
  },
})

Transactions

Ensure atomicity with transactions:

import { db, user, organization, member } from "@packages/db"

await db.transaction(async (tx) => {
  // Create organization
  const [org] = await tx
    .insert(organization)
    .values({
      id: generateId(),
      name: "Acme Inc",
      slug: "acme",
      createdAt: new Date(),
    })
    .returning()

  // Add user as owner
  await tx.insert(member).values({
    id: generateId(),
    organizationId: org.id,
    userId: userId,
    role: "owner",
    createdAt: new Date(),
  })
})

Migrations

Drizzle Kit Configuration

packages/db/drizzle.config.ts

import { env } from "@packages/env/db"
import { defineConfig } from "drizzle-kit"

export default defineConfig({
  dialect: "postgresql",
  dbCredentials: {
    url: env.POSTGRES_URL,
  },
  schema: "src/schema",
  out: "drizzle",
})

Generating Migrations

Create a migration from schema changes:

bun db:generate

This:

Builds @packages/env (required for database URL)
Compares current schema to database
Generates SQL migration in drizzle/ directory

Applying Migrations

Run pending migrations:

bun db:migrate

Drizzle Studio

Open the visual database browser:

bun db:studio

Access at https://local.drizzle.studio

Type Safety

Inferred Types

Drizzle automatically infers TypeScript types from schema:

import { type InferSelectModel, type InferInsertModel } from "drizzle-orm"
import { user } from "@packages/db"

// Type for selecting from database
type User = InferSelectModel<typeof user>
// { id: string, name: string, email: string, ... }

// Type for inserting to database
type NewUser = InferInsertModel<typeof user>
// { id: string, name: string, email: string, emailVerified?: boolean, ... }

Query Result Types

Query results are fully typed:

const users = await db.select().from(user)
// users: Array<User>

const userWithSessions = await db.query.user.findFirst({
  with: { sessions: true },
})
// userWithSessions: { id: string, name: string, ..., sessions: Array<Session> }

Best Practices

Use indexes for frequently queried columns

Add indexes to improve query performance:

export const session = pgTable(
  "session",
  { /* columns */ },
  (table) => [index("session_userId_idx").on(table.userId)]
)

Set onDelete behavior for foreign keys

Ensure data integrity with cascade deletes:

userId: text("user_id")
  .notNull()
  .references(() => user.id, { onDelete: "cascade" })

Use $onUpdate for automatic timestamps

Auto-update timestamps on row changes:

updatedAt: timestamp("updated_at")
  .$onUpdate(() => new Date())
  .notNull()

Prefer prepared statements for repeated queries

Cache query execution plans:

const getUserById = db
  .select()
  .from(user)
  .where(eq(user.id, placeholder("id")))
  .prepare("get_user_by_id")

const user = await getUserById.execute({ id: userId })

Environment Variables

Required database configuration:

.env

POSTGRES_URL=postgresql://user:password@localhost:5432/zerostarter

For production with connection pooling:

POSTGRES_URL=postgresql://user:password@db.example.com:5432/production?sslmode=require

Local Development

Docker Compose

Run PostgreSQL locally:

docker-compose.yml

services:
  postgres:
    image: postgres:16
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres
      POSTGRES_DB: zerostarter
    ports:
      - "5432:5432"
    volumes:
      - postgres_data:/var/lib/postgresql/data

volumes:
  postgres_data:

Start database:

docker-compose up -d

Schema Visualization

The auth schema relationships:

Advanced Usage

Custom Column Types

Create reusable column definitions:

import { customType } from "drizzle-orm/pg-core"

const jsonb = <T>() =>
  customType<{ data: T; driverData: string }>({
    dataType: () => "jsonb",
    toDriver: (value: T): string => JSON.stringify(value),
    fromDriver: (value: string): T => JSON.parse(value),
  })

export const metadata = pgTable("metadata", {
  id: text("id").primaryKey(),
  data: jsonb<{ theme: string; locale: string }>()("data"),
})

Query Logging

Enable query logging in development:

const db = drizzle({ client, schema, logger: true })

Overview

Getting Started

Core Concepts

Frontend

Backend

Features

Development

Database

Architecture

Database Client

Client Setup

Connection Configuration

Schema Definition

Auth Schema

Relations

Database Operations

Querying Data

Relational Queries

Transactions

Migrations

Drizzle Kit Configuration

Generating Migrations

Applying Migrations

Drizzle Studio

Type Safety

Inferred Types

Query Result Types

Best Practices

Environment Variables

Local Development

Docker Compose

Schema Visualization

Advanced Usage

Custom Column Types

Query Logging

Overview

Getting Started

Core Concepts

Frontend

Backend

Database

Features

Development

​Architecture

​Database Client

​Client Setup

​Connection Configuration

​Schema Definition

​Auth Schema

​Relations

​Database Operations

​Querying Data

​Relational Queries

​Transactions

​Migrations

​Drizzle Kit Configuration

​Generating Migrations

​Applying Migrations

​Drizzle Studio

​Type Safety

​Inferred Types

​Query Result Types

​Best Practices

​Environment Variables

​Local Development

​Docker Compose

​Schema Visualization

​Advanced Usage

​Custom Column Types

​Query Logging

Architecture

Database Client

Client Setup

Connection Configuration

Schema Definition

Auth Schema

Relations

Database Operations

Querying Data

Relational Queries

Transactions

Migrations

Drizzle Kit Configuration

Generating Migrations

Applying Migrations

Drizzle Studio

Type Safety

Inferred Types

Query Result Types

Best Practices

Environment Variables

Local Development

Docker Compose

Schema Visualization

Advanced Usage

Custom Column Types

Query Logging