Database

PostgreSQL with Drizzle ORM. Type-safe queries, automatic migrations, and a shared connection pool.

Key files

• db/schema.ts - All table definitions
• db/migrations/ - Generated SQL migrations
• lib/db.ts - Database client singleton
• lib/queries/*.ts - Reusable query helpers
• drizzle.config.ts - Drizzle configuration

Environment variables

DATABASE_URL=postgres://user:password@host:5432/database

Scripts

• pnpm db:generate - Generate migrations from schema changes
• pnpm db:migrate - Apply pending migrations
• pnpm db:push - Push schema directly (dev only)
• pnpm db:studio - Open Drizzle Studio

Schema conventions

• Table names: snake_case plural (e.g., projects)
• Column names: snake_case in DB, camelCase in TypeScript
• Always use UUID primary keys with defaultRandom()
• Always include createdAt and updatedAt timestamps

export const projects = pgTable("projects", {
  id: uuid("id").primaryKey().defaultRandom(),
  title: text("title").notNull(),
  userId: uuid("user_id")
    .notNull()
    .references(() => users.id, { onDelete: "cascade" }),
  createdAt: timestamp("created_at", { mode: "date" }).notNull().defaultNow(),
  updatedAt: timestamp("updated_at", { mode: "date" }).notNull().defaultNow(),
});

export type Project = typeof projects.$inferSelect;
export type NewProject = typeof projects.$inferInsert;

Query helpers

Place reusable queries in lib/queries/:

// lib/queries/projects.ts
import { db } from "@/lib/db";
import { projects } from "@/db/schema";
import { eq } from "drizzle-orm";

export async function getProjectById(id: string) {
  const result = await db
    .select()
    .from(projects)
    .where(eq(projects.id, id))
    .limit(1);
  return result[0] ?? null;
}

Adding a new table

Use the /add-model Cursor command, or manually:

1. Define table in db/schema.ts
2. Export inferred types
3. Run pnpm db:generate
4. Review generated SQL
5. Run pnpm db:migrate
6. Create query helpers in lib/queries/

Auth tables

Tables for users, accounts, sessions, and verification_tokens are managed by NextAuth. Don't modify their structure without understanding the adapter requirements.