Database

This project uses PostgreSQL with Drizzle ORM for database management.

Why Drizzle?

Drizzle ORM provides several advantages:

• Type-safe - Full TypeScript support with inferred types
• SQL-like - Write queries that feel like SQL but with type safety
• Performant - Zero overhead, generates efficient SQL
• Lightweight - Minimal bundle size compared to alternatives
• Migration-friendly - Built-in migration tools via drizzle-kit

Key Features

Schema Definition

Tables are defined in src/db/tables/ using Drizzle's schema builder:

• Organized structure - Each feature has its own table file
• Common columns - Standardized id, createdAt, updatedAt
• Type inference - Automatic TypeScript types from schema
• Relationships - Define relations for type-safe joins

Learn more about schema definition →

Operations Abstraction

The createDrizzleOperations helper provides standardized CRUD operations:

• Consistent API - Same methods across all tables
• Built-in caching - Automatic cache invalidation
• Type-safe - Full TypeScript support
• Pagination - Built-in table pagination support

Learn more about operations →

Migrations

Use drizzle-kit to manage schema changes:

• Development - npm run db:push for rapid iteration
• Production - npm run db:generate for versioned migrations
• Version control - Migration files in src/db/migrations/

Learn more about migrations →

Caching

Built-in caching with Next.js cache primitives:

• Table tags - Cache invalidation per table
• Automatic - Operations handle cache management
• Configurable - Control cache behavior per operation

Learn more about caching →

Row Level Security

Multi-tenant data isolation with RLS policies:

• User-scoped - Users only see their own data
• Organization-scoped - Team data isolation
• Policy-based - Fine-grained access control

[!TIP] Run npm run db:setup once before db:push to initialize required PostgreSQL roles. See Database Setup for details.

Quick Start

1. Define a Table

Create a new table in src/db/tables/:

import { createTable, commonColumns } from "@/db/table-utils";
import { text, boolean } from "drizzle-orm/pg-core";

export const tasks = createTable("tasks", {
  ...commonColumns,
  title: text().notNull(),
  completed: boolean().default(false),
  userId: uuid()
    .notNull()
    .references(() => users.id, { onDelete: "cascade" }),
});

2. Create Operations

Use the operations abstraction in functions.ts:

import { createDrizzleOperations } from "@/db/drizzle-operations";
import { tasks } from "@/db/tables/tasks";

const operations = createDrizzleOperations<typeof tasks, Task>({
  table: tasks,
});

export async function list() {
  return operations.listDocuments();
}

export async function create(
  data: Omit<Task, "id" | "createdAt" | "updatedAt">,
) {
  return operations.createDocument(data);
}

3. Push Schema

Update the database:

npm run db:push

Related Documentation

• Setup - Initialize database roles for RLS
• Schema Definition - Table structure and relationships
• Operations - CRUD operations and queries
• Migrations - Schema version control
• Caching - Cache strategies and invalidation
• Feature Modules - Feature organization patterns
• New Table Template - Step-by-step table creation guide