Database Migrations

Relevant source files

The following files were used as context for generating this wiki page:

• packages/db/package.json
• packages/db/src/schema/auth/auth.ts
• packages/db/drizzle/meta/0000_snapshot.json
• README.md
• apps/api/src/lib/auth.ts
• packages/db/src/db.ts

The project utilizes Drizzle ORM to manage its PostgreSQL database schema. Migrations are handled through drizzle-kit, a companion CLI tool that synchronizes the database state with the schema defined in TypeScript. This approach provides a type-safe, schema-first workflow where the TypeScript code in the packages/db workspace is the single source of truth for the database structure.

The migration strategy employed is drizzle-kit push, which directly applies schema changes to the database without generating discrete, versioned migration files. This is suitable for development environments and simplifies the process of keeping the database schema in sync with the application's data models.

Sources: README.md:15, README.md:79-81, packages/db/package.json:14-16

Tooling and Scripts

The core of the database migration system relies on drizzle-kit. The packages/db workspace contains the necessary scripts and dependencies to manage the schema.

Sources: packages/db/package.json

Key Dependencies

Package	Role
drizzle-orm	The TypeScript ORM used to define the schema and query the DB.
drizzle-kit	The CLI tool for generating SQL and pushing schema changes.
pg	The Node.js PostgreSQL client driver.
dotenv-cli	Used to load environment variables from .env for the CLI scripts.

Sources: packages/db/package.json:18-27

Management Scripts

The following scripts are defined in packages/db/package.json to interact with the database schema. They are designed to be run from the monorepo root using pnpm -F @contract-based-monorepo/db <script_name>.

Script	Command	Description
push	pnpm with-env drizzle-kit push	Synchronizes the database schema with the definitions in src/schema.
studio	pnpm with-env drizzle-kit studio	Launches Drizzle Studio, a GUI for browsing and managing the database.
with-env	dotenv -e ../../.env --	A helper script that loads the root .env file before executing another command (e.g., drizzle-kit).

Sources: packages/db/package.json:12-16

Schema Definition

The database schema is defined programmatically using Drizzle ORM's functions within the packages/db/src/schema/ directory. Currently, the schema primarily supports authentication features via the better-auth library.

Sources: packages/db/src/schema/auth/auth.ts, apps/api/src/lib/auth.ts:1-2

Auth Schema Tables

The authentication schema consists of four main tables: user, session, account, and verification.

This diagram shows the relationships between the authentication tables. A user can have multiple session and account records.

Sources: packages/db/src/schema/auth/auth.ts:3-56, packages/db/drizzle/meta/0000_snapshot.json

Table Definition Example

Tables are defined using pgTable from drizzle-orm/pg-core. The following snippet shows the definition for the user table, including its columns, types, and constraints.

// packages/db/src/schema/auth/auth.ts
import { boolean, pgTable, text, timestamp } from "drizzle-orm/pg-core";

export const user = pgTable("user", {
  id: text("id").primaryKey(),
  name: text("name").notNull(),
  email: text("email").notNull().unique(),
  emailVerified: boolean("email_verified").notNull(),
  image: text("image"),
  createdAt: timestamp("created_at").notNull(),
  updatedAt: timestamp("updated_at").notNull(),
});

Sources: packages/db/src/schema/auth/auth.ts:1-12

The schema defined in TypeScript is then used by other parts of the application, such as the better-auth adapter in the API.

// apps/api/src/lib/auth.ts
export const auth = betterAuth({
  // ...
  database: drizzleAdapter(db, {
    provider: "pg",
    schema: {
      user: schema.user,
      session: schema.session,
      account: schema.account,
      verification: schema.verification,
    },
  }),
});

Sources: apps/api/src/lib/auth.ts:8-17

Migration Workflow

The project uses a "push" workflow to apply schema changes. This process is simpler than versioned migrations and is initiated via a single command.

The following diagram illustrates the developer workflow for updating the database schema.

Schema Snapshot

drizzle-kit generates a snapshot of the schema in packages/db/drizzle/meta/0000_snapshot.json. This file is a JSON representation of the database tables, columns, foreign keys, and indexes as defined in the TypeScript schema files. It is used internally by Drizzle to understand the current state and determine the necessary changes during a push operation.

Below is a summary of the tables captured in the snapshot:

Table Name	Primary Key	Description
public.user	id	Stores user profile information, including name and email.
public.session	id	Manages user session tokens for authentication.
public.account	id	Links user profiles to different authentication providers (e.g., OAuth).
public.verification	id	Stores tokens for processes like email verification.

Sources: packages/db/drizzle/meta/0000_snapshot.json

Summary

Database migrations in this project are managed by Drizzle ORM and its drizzle-kit CLI. The system is built around a schema-first principle, where TypeScript files in packages/db/src/schema are the definitive source of truth. Developers use the pnpm ... push command to synchronize the PostgreSQL database with the defined schema, a process streamlined for development environments. This provides a type-safe and centralized way to manage the database structure alongside application code.

Sources: README.md:15, packages/db/package.json:14, packages/db/src/schema/auth/auth.ts