ORM and Migrations (Drizzle)
Relevant source files
The following files were used as context for generating this wiki page:
The project utilizes Drizzle ORM as its primary tool for database interaction, providing a type-safe SQL-like query builder. It is configured to work with a PostgreSQL database. The core database logic, including the client, schema definitions, and migration tooling, is encapsulated within the @repo/db monorepo package. This setup ensures a clear separation of concerns and allows for consistent database management across the application.
Schema changes are managed by drizzle-kit, a command-line tool that generates and applies SQL migration files. This declarative approach, where the schema is defined in TypeScript files, allows developers to version-control the database structure alongside the application code, ensuring consistency and simplifying the process of evolving the data model.
Architecture and Configuration
The database architecture is centered around the @repo/db package, which contains all necessary configurations, the database client, and migration scripts.
Core Components
| Component | Location | Description |
|---|---|---|
| Drizzle ORM | packages/db/package.json | The core ORM library (drizzle-orm) used for building and executing type-safe SQL queries. |
| Drizzle Kit | packages/db/package.json | The CLI tool (drizzle-kit) for generating SQL migrations from TypeScript schema definitions. |
| PostgreSQL Driver | packages/db/package.json | The pg package, which is the Node.js driver used to connect to the PostgreSQL database. |
| DB Client | packages/db/src/client.ts | Initializes and exports the main Drizzle client instance, configured with a connection pool. |
| Drizzle Configuration | packages/db/drizzle.config.ts | Configuration for drizzle-kit, specifying schema locations, output directory for migrations, and database connection credentials. |
| Schema Definitions | packages/auth/src/schema/auth.table.ts, packages/db/src/schema/ | TypeScript files where database tables, columns, and relations are defined using Drizzle's pgTable syntax. |
| Migration Files | packages/db/drizzle/ | The output directory containing generated SQL migration files (*.sql) and their corresponding metadata snapshots (meta/*.json). |
Sources: packages/db/package.json, packages/db/src/client.ts, packages/db/drizzle.config.ts, packages/auth/src/schema/auth.table.ts, packages/db/drizzle/0002_amused_wasp.sql
Configuration Flow
The following diagram illustrates how the different configuration and client files work together to establish a database connection.
This diagram shows that both drizzle-kit and the runtime client rely on the DATABASE_URL environment variable. drizzle-kit uses drizzle.config.ts to find schema files and generate migrations, while client.ts creates a connection pool and exports a ready-to-use Drizzle instance.
Sources: packages/db/drizzle.config.ts, packages/db/src/client.ts
Drizzle Client Initialization
The database client is a singleton instance created by connecting drizzle-orm with the pg driver. It uses a connection pool for efficient handling of database connections.
// packages/db/src/client.ts
import { drizzle } from "drizzle-orm/node-postgres";
import { Pool } from "pg";
import { env } from "@repo/env";
import * as schema from "./schema";
const pool = new Pool({
connectionString: env.DATABASE_URL,
});
export const db = drizzle(pool, { schema });
Sources: packages/db/src/client.ts:1-15
Drizzle Kit Configuration
The drizzle.config.ts file is central to the migration process. It tells drizzle-kit where to find the schema definitions and where to output the generated migration files. Notably, it pulls schemas from both the local @repo/db package and the shared @repo/auth package.
// packages/db/drizzle.config.ts
import "dotenv/config";
import { defineConfig } from "drizzle-kit";
export default defineConfig({
schema: ["./src/schema/index.ts", "../auth/src/schema/auth.table.ts"],
out: "./drizzle",
dialect: "postgresql",
dbCredentials: {
url: process.env.DATABASE_URL!,
},
});
Sources: packages/db/drizzle.config.ts:1-11
Database Schema
The database schema is defined programmatically using TypeScript. This approach provides strong typing and allows the schema to be version-controlled with the rest of the codebase. The main schema definitions are located in packages/auth/src/schema/auth.table.ts.
Entity Relationship Diagram
The following ER diagram shows the relationships between the core authentication and authorization tables defined in the schema.
Table Definitions
Tables are defined using the pgTable function from drizzle-orm/pg-core. Below is an example of the organization and member tables, showcasing column types, constraints, and foreign key references.
// packages/auth/src/schema/auth.table.ts
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().defaultNow(),
metadata: text("metadata"),
createdBy: text("created_by").notNull(),
});
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").defaultNow().notNull(),
},
(table) => [
index("member_organizationId_idx").on(table.organizationId),
index("member_userId_idx").on(table.userId),
]
);
Sources: packages/auth/src/schema/auth.table.ts:70-97
Relations between tables are explicitly defined using the relations helper from Drizzle, which enables joining and querying related data.
// packages/auth/src/schema/auth.table.ts
export const organizationRelations = relations(organization, ({ many }) => ({
members: many(member),
invitations: many(invitation),
}));
export const memberRelations = relations(member, ({ one }) => ({
organization: one(organization, {
fields: [member.organizationId],
references: [organization.id],
}),
user: one(user, {
fields: [member.userId],
references: [user.id],
}),
}));
Sources: packages/auth/src/schema/auth.table.ts:133-147
Migrations
Database migrations are managed via drizzle-kit and a set of npm scripts defined in package.json. This provides a standardized workflow for evolving the database schema.
Migration Workflow
The process of applying a schema change follows these steps:
- A developer modifies a schema definition file (e.g.,
auth.table.ts). - The developer runs the
db:generatescript. drizzle-kitcompares the current schema in code with the last migration snapshot.- A new SQL migration file is generated in the
packages/db/drizzle/directory. - The developer reviews the SQL file and commits it.
- The
db:migratescript is run (typically in a CI/CD pipeline or manually) to apply the SQL changes to the database.
Migration Scripts
The following scripts are available in the @repo/db package and can be run from the monorepo root.
| Script | Command | Description |
|---|---|---|
db:generate | drizzle-kit generate | Generates a new SQL migration file based on changes detected in the TypeScript schema files. |
db:migrate | drizzle-kit migrate | Applies all pending migrations to the database. This script is not present in the files. |
db:push | drizzle-kit push | Pushes schema changes directly to the database without creating a migration file. Best for development. |
db:studio | drizzle-kit studio | Launches a local web UI to browse the database schema and data. |
Note: The db:migrate script is defined in the root package.json but not in the @repo/db package itself, which is an unusual pattern. The db:push command is an alternative for development.
Sources: packages/db/package.json:14-18, package.json:14
Example Migration File
This is an excerpt from a migration file generated by drizzle-kit. It shows standard SQL commands for creating tables and adding foreign key constraints.
-- packages/db/drizzle/0002_amused_wasp.sql
CREATE TABLE "member" (
"id" varchar(36) PRIMARY KEY NOT NULL,
"organization_id" varchar(36) NOT NULL,
"user_id" varchar(36) NOT NULL,
"role" varchar(50) NOT NULL,
"created_at" timestamp DEFAULT now() NOT NULL,
"updated_at" timestamp DEFAULT now() NOT NULL,
CONSTRAINT "member_organization_id_user_id_unique" UNIQUE("organization_id","user_id")
);
--> statement-breakpoint
ALTER TABLE "member" ADD CONSTRAINT "member_organization_id_organization_id_fk" FOREIGN KEY ("organization_id") REFERENCES "public"."organization"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "member" ADD CONSTRAINT "member_user_id_user_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."user"("id") ON DELETE cascade ON UPDATE no action;
Sources: packages/db/drizzle/0002_amused_wasp.sql:9-24
Summary
The project's database layer is built on a solid foundation using Drizzle ORM and drizzle-kit. By defining the schema in TypeScript, the application benefits from full type-safety from the database to the frontend. The migration workflow, managed through npm scripts, provides a repeatable and version-controlled process for evolving the database schema. This architecture, centered in the @repo/db package, creates a robust and maintainable system for all data persistence needs.