General

Database

Learn how to manage your database, schema, and migrations with Drizzle ORM.

The Pro Next.js Drizzle starter kit uses Drizzle ORM with PostgreSQL. Drizzle provides a lightweight, fully type-safe way to interact with your database.

Client Setup

The database client is initialized in lib/db/client.ts and exported from lib/db/index.ts. It uses the postgres driver for high performance.

lib/db/client.ts

import { drizzle } from 'drizzle-orm/node-postgres';

import { env } from '@/lib/env';

import * as schema from './schema';

export const db = drizzle(env.DATABASE_URL, {
  schema
});

The client is then exported from lib/db/index.ts:

lib/db/index.ts

export * from './client';
export * from './schema';

Schema Definition

Your database schema is defined in lib/db/schema/. We recommend splitting your schema into multiple files for better organization:

tables.ts: Table definitions.
enums.ts: Enum definitions.
relations.ts: Relation definitions.

Example Table Definition

lib/db/schema/tables.ts

import {
  boolean,
  index,
  pgTable,
  text,
  timestamp,
  uuid
} from 'drizzle-orm/pg-core';

import { organizationTable } from './organization';

export const leadTable = pgTable(
  'lead',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    organizationId: uuid('organization_id')
      .notNull()
      .references(() => organizationTable.id, { onDelete: 'cascade' }),
    firstName: text('first_name').notNull(),
    lastName: text('last_name').notNull(),
    email: text('email').notNull(),
    createdAt: timestamp('created_at', { withTimezone: true })
      .notNull()
      .defaultNow()
  },
  (table) => [index('lead_organization_id_idx').on(table.organizationId)]
);

Migrations

Commands

Command	Description
`npm run db:generate`	Generate migration from schema changes
`npm run db:migrate`	Apply pending migrations
`npm run db:studio`	Open Drizzle Studio GUI
`npm run db:push`	Push schema directly (dev only, no migration)

Migration Workflow

Edit schema in lib/db/schema/tables.ts.
Generate migration: npm run db:generate.
Review migration in lib/db/migrations/.
Apply migration: npm run db:migrate.

Multi-Tenancy

Critical: Always filter by organizationId for tenant data to ensure data isolation.

trpc/routers/lead-router.ts

const leads = await db.query.leadTable.findMany({
  where: eq(leadTable.organizationId, ctx.organization.id)
});

Transactions

Use transactions for related operations that must be atomic.

lib/actions/widget.ts

const result = await db.transaction(async (tx) => {
  await tx
    .delete(subscriptionItemTable)
    .where(eq(subscriptionItemTable.subscriptionId, subId));

  const items = await tx
    .insert(subscriptionItemTable)
    .values(newItems)
    .returning();

  return items;
});