db

What this skill does

# Database Setup with Drizzle ORM

Sets up a type-safe PostgreSQL database layer using Drizzle ORM with the `postgres` driver. Uses Docker for local development with a production-ready migration workflow.

## Prerequisites

- Next.js app with `src/` directory and App Router
- Docker setup (for local PostgreSQL -- see docker-compose section)
- Environment configuration (dependency: `env-config` skill)

## Installation

```bash
bun add drizzle-orm postgres
bun add -D drizzle-kit
```

## Environment Variables

Add to `.env.local`:

```env
DATABASE_URL=postgresql://app:password@localhost:5432/appdb
```

If using the `env-config` skill, add to your `env.ts`:

```typescript
// In env.ts, add to server object:
server: {
  // ... existing variables
  DATABASE_URL: z.string().url(),
},

// Add to runtimeEnv:
runtimeEnv: {
  // ... existing variables
  DATABASE_URL: process.env.DATABASE_URL,
},
```

## Docker Setup

Add the PostgreSQL service to `docker-compose.yml`. Uses `pgvector/pgvector:pg16` for vector search support (compatible with standard Postgres usage):

```yaml
services:
  postgres:
    image: pgvector/pgvector:pg16
    ports:
      - "5432:5432"
    environment:
      POSTGRES_USER: ${POSTGRES_USER:-app}
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD:-password}
      POSTGRES_DB: ${POSTGRES_DB:-appdb}
    volumes:
      - postgres_data:/var/lib/postgresql/data
    healthcheck:
      test: ["CMD", "pg_isready", "-U", "${POSTGRES_USER:-app}"]
      interval: 5s
      timeout: 5s
      retries: 5
      start_period: 60s
    restart: unless-stopped

volumes:
  postgres_data:
```

> **Note:** Credentials here match the `docker` skill defaults (`app/password/appdb`). If your `docker-compose.yml` already defines a `postgres` service (from the `docker` skill), skip this section — the database is already configured.

Start the database:

```bash
docker compose up -d db
```

## What Gets Created

```
src/
├── lib/
│   └── db/
│       ├── index.ts           # Database client factory
│       └── schema/
│           └── index.ts       # Schema barrel export (add tables here)
├── lib/
│   └── db/
│       └── migrate.ts         # Migration runner
drizzle.config.ts              # Drizzle Kit config (project root)
```

## Setup Steps

### Step 1: Create Database Client (`src/lib/db/index.ts`)

This is the main database client factory. It uses the `postgres` package (NOT `pg`) with `drizzle-orm/postgres-js`.

```typescript
import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";
import * as schema from "./schema";

const connectionString = process.env.DATABASE_URL;
if (!connectionString) {
  throw new Error("DATABASE_URL is not set");
}

const client = postgres(connectionString);
export const db = drizzle(client, { schema });
export type Database = typeof db;
```

### Step 2: Create Schema Barrel Export (`src/lib/db/schema/index.ts`)

This is the barrel export for all schema tables. Add your own tables here as you build features.

```typescript
import { pgTable, text, timestamp, uuid } from "drizzle-orm/pg-core";

// Example table -- replace with your schema
export const example = pgTable("example", {
  id: uuid("id").primaryKey().defaultRandom(),
  name: text("name").notNull(),
  createdAt: timestamp("created_at", { withTimezone: true })
    .notNull()
    .defaultNow(),
  updatedAt: timestamp("updated_at", { withTimezone: true })
    .notNull()
    .defaultNow(),
});
```

### Step 3: Create Migration Runner (`src/lib/db/migrate.ts`)

A standalone script that runs migrations against the database. Uses a single connection (`max: 1`) and exits cleanly after completion.

```typescript
import { drizzle } from "drizzle-orm/postgres-js";
import { migrate } from "drizzle-orm/postgres-js/migrator";
import postgres from "postgres";

const connectionString = process.env.DATABASE_URL;
if (!connectionString) {
  throw new Error("DATABASE_URL is not set");
}

const client = postgres(connectionString, { max: 1 });
const db = drizzle(client);

async function main() {
  console.log("Running migrations...");
  await migrate(db, { migrationsFolder: "./drizzle" });
  console.log("Migrations complete!");
  await client.end();
}

main().catch((err) => {
  console.error("Migration failed:", err);
  process.exit(1);
});
```

### Step 4: Create Drizzle Kit Config (`drizzle.config.ts`)

Create this file at the project root. It tells Drizzle Kit where to find the schema and where to output migration files.

```typescript
import { defineConfig } from "drizzle-kit";

const url = process.env.DATABASE_URL;
if (!url) throw new Error("DATABASE_URL is required");

export default defineConfig({
  schema: "./src/lib/db/schema/index.ts",
  out: "./drizzle",
  dialect: "postgresql",
  dbCredentials: {
    url,
  },
});
```

### Step 5: Add Package Scripts

Add these scripts to `package.json`:

```json
{
  "scripts": {
    "db:generate": "drizzle-kit generate",
    "db:migrate": "bun src/lib/db/migrate.ts",
    "db:push": "drizzle-kit push",
    "db:studio": "drizzle-kit studio"
  }
}
```

### Step 6: Initialize the Database

```bash
# Start PostgreSQL (if not already running)
docker compose up -d db

# Push the schema directly (development)
bun run db:push

# Or generate and run migrations (production workflow)
bun run db:generate
bun run db:migrate
```

## Usage

### Importing the Database Client

```typescript
import { db } from "@/lib/db";
```

> **Path convention:** The canonical import path is `@/lib/db` (not `@/db`). All downstream skills that depend on this skill should use `@/lib/db` and `@/lib/db/schema` for imports.

### Insert Records

```typescript
import { db } from "@/lib/db";
import { example } from "@/lib/db/schema";

const newRecord = await db
  .insert(example)
  .values({
    name: "My Item",
  })
  .returning();
```

### Select Records

```typescript
import { db } from "@/lib/db";
import { example } from "@/lib/db/schema";
import { eq } from "drizzle-orm";

// Select all
const allRecords = await db.select().from(example);

// Select with filter
const filtered = await db
  .select()
  .from(example)
  .where(eq(example.name, "My Item"));

// Select single record by ID
const record = await db.query.example.findFirst({
  where: eq(example.id, "some-uuid"),
});
```

### Update Records

```typescript
import { db } from "@/lib/db";
import { example } from "@/lib/db/schema";
import { eq } from "drizzle-orm";

const updated = await db
  .update(example)
  .set({
    name: "Updated Name",
    updatedAt: new Date(),
  })
  .where(eq(example.id, "some-uuid"))
  .returning();
```

### Delete Records

```typescript
import { db } from "@/lib/db";
import { example } from "@/lib/db/schema";
import { eq } from "drizzle-orm";

await db.delete(example).where(eq(example.id, "some-uuid"));
```

### Transactions

```typescript
import { db } from "@/lib/db";
import { example } from "@/lib/db/schema";

await db.transaction(async (tx) => {
  const inserted = await tx
    .insert(example)
    .values({ name: "First" })
    .returning();

  await tx
    .update(example)
    .set({ name: "Updated" })
    .where(eq(example.id, inserted[0].id));
});
```

### Relations Example

When adding related tables, define relations for the query API:

```typescript
import { pgTable, text, timestamp, uuid } from "drizzle-orm/pg-core";
import { relations } from "drizzle-orm";

export const posts = pgTable("posts", {
  id: uuid("id").primaryKey().defaultRandom(),
  title: text("title").notNull(),
  content: text("content"),
  authorId: uuid("author_id")
    .notNull()
    .references(() => users.id, { onDelete: "cascade" }),
  createdAt: timestamp("created_at", { withTimezone: true })
    .notNull()
    .defaultNow(),
  updatedAt: timestamp("updated_at", { withTimezone: true })
    .notNull()
    .defaultNow(),
});

export const users = pgTable("users", {
  id: uuid("id").primaryKey().defaultRandom(),
  name: text("name").notNull(),
  email: text("email").notNull().unique(),
  createdAt: timestam