# Core Database Examples

Essential patterns for Drizzle ORM setup and schema definition. These are foundational patterns needed for any database work.

---

## Database Connection

### Neon HTTP Setup (Edge-Compatible)

```typescript
// Good Example - Proper Neon HTTP setup
import { neon } from "@neondatabase/serverless";
import { drizzle } from "drizzle-orm/neon-http";
import * as schema from "./db/schema";

if (!process.env.DATABASE_URL) {
  throw new Error("DATABASE_URL environment variable is not set");
}

// Use neon() for HTTP-based queries (edge-compatible)
export const sql = neon(process.env.DATABASE_URL);

// Initialize Drizzle with schema and snake_case mapping
export const db = drizzle(sql, {
  schema,
  casing: "snake_case", // Maps camelCase JS to snake_case SQL
});

// Export tables for direct query access
export const { jobs, companies, companyLocations, skills, jobSkills } = schema;
```

**Why good:** Environment variable validation prevents runtime errors, `casing: "snake_case"` ensures JS camelCase maps correctly to SQL snake_case preventing field name mismatches, HTTP connection works in all serverless environments

```typescript
// Bad Example - Missing critical configuration
import { neon } from "@neondatabase/serverless";
import { drizzle } from "drizzle-orm/neon-http";

const sql = neon(process.env.DATABASE_URL!); // No validation
const db = drizzle(sql); // Missing schema and casing

export default db; // Default export
```

**Why bad:** No env validation = runtime crashes, missing `casing` config = field name mismatches between JS and SQL, missing schema = no relational queries available, default export violates project conventions

---

### WebSocket Connection (Long-Running Queries)

For queries taking > 30 seconds or LISTEN/NOTIFY.

```typescript
// Good Example - WebSocket for long queries
import { Pool, neonConfig } from "@neondatabase/serverless";
import ws from "ws";

// Enable WebSocket support (Node.js only, not edge)
neonConfig.webSocketConstructor = ws;

const QUERY_TIMEOUT_MS = 60000;
const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  connectionTimeoutMillis: QUERY_TIMEOUT_MS,
});

async function longRunningQuery() {
  const client = await pool.connect();
  try {
    const result = await client.query("SELECT * FROM large_table WHERE ...");
    return result.rows;
  } finally {
    client.release(); // Always release connection
  }
}

export { longRunningQuery };
```

**Why good:** WebSocket enables queries > 30 seconds without timeout, connection pooling reuses connections efficiently, `finally` ensures connections are released preventing memory leaks, named constant for timeout improves maintainability

```typescript
// Bad Example - Missing connection cleanup
import { Pool } from "@neondatabase/serverless";

const pool = new Pool({ connectionString: process.env.DATABASE_URL });

async function query() {
  const client = await pool.connect();
  const result = await client.query("SELECT * FROM table");
  return result.rows; // Missing client.release()
}
```

**Why bad:** Missing `client.release()` causes connection leaks, connection pool exhaustion, memory leaks in serverless environments

---

### Drizzle Kit Configuration

```typescript
// drizzle.config.ts
import { defineConfig } from "drizzle-kit";

export default defineConfig({
  schema: "./lib/db/schema.ts",
  out: "./drizzle", // Migration files output
  dialect: "postgresql",
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
});
```

---

## Schema Definition

### Complete Table Definitions

```typescript
// Good Example - Well-structured table with constraints
export const companies = pgTable("companies", {
  id: uuid("id").primaryKey().defaultRandom(),
  name: varchar("name", { length: 255 }).notNull(),
  slug: varchar("slug", { length: 255 }).unique(),
  description: text("description"),
  logoUrl: text("logo_url"),
  websiteUrl: text("website_url"),
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),
  deletedAt: timestamp("deleted_at"), // Soft delete
});

export const companyLocations = pgTable("company_locations", {
  id: uuid("id").primaryKey().defaultRandom(),
  companyId: uuid("company_id")
    .references(() => companies.id, { onDelete: "cascade" })
    .notNull(),
  name: varchar("name", { length: 255 }),
  country: varchar("country", { length: 100 }).notNull(),
  city: varchar("city", { length: 100 }),
  isHeadquarters: boolean("is_headquarters").default(false),
  createdAt: timestamp("created_at").defaultNow(),
});

export const jobs = pgTable("jobs", {
  id: uuid("id").primaryKey().defaultRandom(),
  companyId: uuid("company_id")
    .references(() => companies.id, { onDelete: "cascade" })
    .notNull(),
  title: varchar("title", { length: 255 }).notNull(),
  description: text("description").notNull(),
  externalUrl: text("external_url").notNull(),
  employmentType: employmentTypeEnum("employment_type"),
  seniorityLevel: seniorityLevelEnum("seniority_level"),
  salaryMin: integer("salary_min"),
  salaryMax: integer("salary_max"),
  salaryCurrency: varchar("salary_currency", { length: 3 }).default("EUR"),
  showSalary: boolean("show_salary").default(false),
  isActive: boolean("is_active").default(true),
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),
  deletedAt: timestamp("deleted_at"),
});

export const skills = pgTable("skills", {
  id: uuid("id").primaryKey().defaultRandom(),
  name: varchar("name", { length: 100 }).notNull().unique(),
  slug: varchar("slug", { length: 100 }).unique(),
  popularityScore: integer("popularity_score").notNull().default(0),
  createdAt: timestamp("created_at").defaultNow(),
});

// Junction table for many-to-many
export const jobSkills = pgTable("job_skills", {
  id: uuid("id").primaryKey().defaultRandom(),
  jobId: uuid("job_id")
    .references(() => jobs.id, { onDelete: "cascade" })
    .notNull(),
  skillId: uuid("skill_id")
    .references(() => skills.id, { onDelete: "cascade" })
    .notNull(),
  isRequired: boolean("is_required").default(true),
  createdAt: timestamp("created_at").defaultNow(),
});
```

**Why good:** `uuid().defaultRandom()` generates secure unique IDs, `.notNull()` enforces required fields preventing null errors, enums constrain values to valid options, `onDelete: 'cascade'` prevents orphaned records, `deletedAt` enables soft deletes preserving data history, `createdAt`/`updatedAt` track record lifecycle

```typescript
// Bad Example - Missing constraints and best practices
export const companies = pgTable("companies", {
  id: uuid("id").primaryKey(), // Missing defaultRandom()
  name: varchar("name", { length: 255 }), // Missing .notNull()
  // Missing timestamps
});

export const jobs = pgTable("jobs", {
  id: uuid("id").primaryKey(),
  companyId: uuid("company_id").references(() => companies.id), // Missing onDelete
  status: varchar("status", { length: 50 }), // Should use enum
});
```

**Why bad:** Missing `defaultRandom()` requires manual ID generation, missing `.notNull()` allows nulls in required fields causing runtime errors, missing `onDelete` leaves orphaned records on deletion, no timestamps prevents tracking creation/updates, varchar for status instead of enum allows invalid values

---

### Identity Columns (Alternative to UUID)

PostgreSQL recommends identity columns over serial for auto-incrementing integer primary keys. Use when you prefer integer IDs over UUIDs.

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

// Good Example - Identity column for auto-increment
export const products = pgTable("products", {
  // GENERATED ALWAYS - database always generates value
  id: integer("id").primaryKey().generatedAlwaysAsIdentity({ startWith: 1000 }),
  name: text("name").notNull(),
  description: text("description"),
  createdAt: timestamp("created_at").defaultNow(),
});

// Alternative: GENERATED BY DEFAULT - allows manual override
export const categories = pgTable("categories", {
  id: integer("id").primaryKey().generatedByDefaultAsIdentity(),
  name: text("name").notNull(),
});
```

**Why use identity columns:** PostgreSQL recommended over deprecated `serial` type, customizable sequence options (startWith, increment), allows multiple identity columns per table, standard SQL compliance

**When to use UUID vs Identity:**

- **UUID** (`uuid().defaultRandom()`): Distributed systems, client-generated IDs, prevent ID enumeration
- **Identity** (`integer().generatedAlwaysAsIdentity()`): Simple auto-increment, smaller storage, faster joins

---

### Relations Definition

Use `defineRelations()` (RQB v2) for centralized relation definitions. See [relations-v2.md](relations-v2.md) for full examples.

```typescript
// RQB v2 - Centralized relation definitions
import { defineRelations } from "drizzle-orm";
import * as schema from "./schema";

export const relations = defineRelations(schema, (r) => ({
  companies: {
    jobs: r.many.jobs({ from: r.companies.id, to: r.jobs.companyId }),
    locations: r.many.companyLocations({
      from: r.companies.id,
      to: r.companyLocations.companyId,
    }),
  },
  jobs: {
    company: r.one.companies({ from: r.jobs.companyId, to: r.companies.id }),
    jobSkills: r.many.jobSkills({ from: r.jobs.id, to: r.jobSkills.jobId }),
  },
  jobSkills: {
    job: r.one.jobs({ from: r.jobSkills.jobId, to: r.jobs.id }),
    skill: r.one.skills({ from: r.jobSkills.skillId, to: r.skills.id }),
  },
  skills: {
    jobSkills: r.many.jobSkills({ from: r.skills.id, to: r.jobSkills.skillId }),
  },
}));
```

---

## See Also

- [queries.md](queries.md) - Relational queries with `.with()` and query builder patterns
- [transactions.md](transactions.md) - Atomic operations and error handling
- [migrations.md](migrations.md) - Drizzle Kit workflow and best practices
- [seeding.md](seeding.md) - Development data population

# Migration Examples

Patterns for managing database schema changes with Drizzle Kit.

> **Version Note:** Drizzle Kit v1.0.0-beta.2 restructured migrations - journal.json removed, SQL files and snapshots now in separate folders per migration. Run `drizzle-kit up` to upgrade existing migrations.

---

## Migration Workflow

```bash
# 1. Make schema changes in your schema file
# 2. Generate migration
bun run drizzle-kit generate

# 3. Review generated SQL in drizzle/ directory
# 4. Apply migration (development)
bun run drizzle-kit migrate

# 5. For serverless/production, use push for direct schema sync
bun run drizzle-kit push

# 6. (v1.0.0-beta.2+) Upgrade existing migrations to new folder structure
bun run drizzle-kit up
```

---

## Package.json Scripts

```json
{
  "scripts": {
    "db:generate": "drizzle-kit generate",
    "db:migrate": "drizzle-kit migrate",
    "db:push": "drizzle-kit push",
    "db:pull": "drizzle-kit pull",
    "db:up": "drizzle-kit up",
    "db:studio": "drizzle-kit studio"
  }
}
```

---

## When to Use `generate` vs `push`

| Command                | Use Case          | Notes                                     |
| ---------------------- | ----------------- | ----------------------------------------- |
| `generate` + `migrate` | Development       | Trackable migration files                 |
| `push`                 | Serverless/CI     | Direct schema sync                        |
| `pull`                 | Existing DB       | Introspect schema from database           |
| `up`                   | Migration upgrade | Upgrade to v1.0.0-beta.2 folder structure |

**Critical:** Never use `push` in production with existing data without backup

**Note:** `drizzle-kit drop` was removed in v1.0.0-beta.2. Delete migration folders manually if needed.

---

## Migration Naming Convention

Follow this pattern for migration file names (auto-generated use timestamp prefix):

- `20242409125510_init.sql` - Timestamp-prefixed (default)
- Custom naming with `--name` flag: `drizzle-kit generate --name=add_users_table`

---

## Custom Migrations

For data migrations or unsupported DDL:

```bash
# Generate empty migration file for custom SQL
bun run drizzle-kit generate --custom --name=seed-initial-data
```

Then edit the generated file with your custom SQL.

---

## Migration Table Configuration

Configure where Drizzle stores migration history:

```typescript
// drizzle.config.ts
import { defineConfig } from "drizzle-kit";

export default defineConfig({
  schema: "./lib/db/schema.ts",
  out: "./drizzle",
  dialect: "postgresql",
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
  migrations: {
    table: "__drizzle_migrations", // Custom table name (default)
    schema: "public", // PostgreSQL only - schema for migrations table
  },
});
```

---

## v1.0.0-beta.2 Migration Structure

New folder structure (eliminates Git conflicts with journal.json):

```
drizzle/
  0001_migration_name/
    migration.sql      # SQL statements
    snapshot.json      # Schema snapshot
  0002_another_migration/
    migration.sql
    snapshot.json
```

**Upgrading from older structure:**

```bash
# Run once to upgrade existing migrations
bun run drizzle-kit up
```

---

## Production Migration Patterns

### Runtime Migrations (Monolith)

Apply migrations during application startup:

```typescript
import { migrate } from "drizzle-orm/neon-http/migrator";
import { db } from "./db";

// Run during deployment/startup
await migrate(db, { migrationsFolder: "./drizzle" });
```

### Serverless Migrations

For serverless deployments, apply migrations as a separate step:

```bash
# In CI/CD pipeline
bun run drizzle-kit migrate

# Or use push for direct sync
bun run drizzle-kit push
```

---

## See Also

- [core.md](core.md) - Drizzle Kit configuration
- [seeding.md](seeding.md) - Populating development data after migrations
- [Drizzle Kit Docs](https://orm.drizzle.team/docs/kit-overview) - Official documentation
- [v1.0.0-beta.2 Release](https://orm.drizzle.team/docs/latest-releases/drizzle-orm-v1beta2) - Migration structure changes

# Query Examples

Patterns for relational queries with `.with()` and the Drizzle query builder for complex filtering.

---

## Relational Queries

### Efficient Relational Query with `.with()`

```typescript
// Good Example - Efficient relational query
import { and, eq, desc, asc, isNull } from "drizzle-orm";

const job = await db.query.jobs.findFirst({
  where: and(
    eq(jobs.id, jobId),
    eq(jobs.isActive, true),
    isNull(jobs.deletedAt),
  ),
  with: {
    company: {
      with: {
        locations: {
          orderBy: [
            desc(companyLocations.isHeadquarters),
            asc(companyLocations.name),
          ],
        },
      },
    },
    jobSkills: {
      where: eq(jobSkills.isRequired, true),
      with: {
        skill: true,
      },
    },
  },
});

// Result is fully typed and nested
if (job) {
  console.log(job.title); // string
  console.log(job.company.name); // string
  console.log(job.company.locations); // CompanyLocation[]
  console.log(job.jobSkills[0].skill.name); // string
}
```

**Why good:** Single SQL query eliminates N+1 problem, nested `.with()` loads deep relations efficiently, soft delete check prevents returning deleted records, ordering nested results provides predictable output, fully typed results catch errors at compile time

```typescript
// Bad Example - N+1 query problem
const job = await db.query.jobs.findFirst({
  where: eq(jobs.id, jobId), // Missing soft delete check
});

// Separate queries cause N+1 problem
const company = await db.query.companies.findFirst({
  where: eq(companies.id, job.companyId),
});

const locations = await db.query.companyLocations.findMany({
  where: eq(companyLocations.companyId, company.id),
});

const jobSkills = await db.query.jobSkills.findMany({
  where: eq(jobSkills.jobId, job.id),
});
```

**Why bad:** Multiple queries create N+1 problem degrading performance, missing soft delete check returns deleted records, manual relation loading is error-prone, no ordering means unpredictable results, more database round-trips = slower response times

---

## Query Builder

### Basic Query Builder

```typescript
// Good Example - Complex filtered query
import { and, eq, desc, isNull, sql } from "drizzle-orm";

const MAX_RESULTS = 100;

const results = await db
  .select({
    id: jobs.id,
    title: jobs.title,
    companyName: companies.name,
    companyLogo: companies.logoUrl,
  })
  .from(jobs)
  .leftJoin(companies, eq(jobs.companyId, companies.id))
  .where(
    and(
      eq(jobs.isActive, true),
      isNull(jobs.deletedAt),
      sql`LOWER(${jobs.country}) = ${country.toLowerCase()}`,
    ),
  )
  .orderBy(desc(jobs.createdAt))
  .limit(MAX_RESULTS);
```

**Why good:** Custom column selection reduces data transfer, named constant for limit improves maintainability, soft delete filter prevents returning deleted records, `LOWER()` makes country comparison case-insensitive, explicit ordering provides predictable results

```typescript
// Bad Example - Missing filters and magic numbers
const results = await db
  .select()
  .from(jobs)
  .leftJoin(companies, eq(jobs.companyId, companies.id))
  .where(sql`country = ${country}`) // Case-sensitive, no soft delete
  .limit(1000); // Magic number
```

**Why bad:** Missing soft delete check returns deleted records, case-sensitive comparison misses matches, magic number makes limit unclear and hard to change, selecting all columns wastes bandwidth, no ordering = unpredictable results

---

### Dynamic Filtering

```typescript
// Good Example - Dynamic filters with proper handling
import { inArray } from "drizzle-orm";

const conditions = [eq(jobs.isActive, true), isNull(jobs.deletedAt)];

// Country filter: support comma-separated values
if (country) {
  const countries = country.split(",").map((c) => c.trim().toLowerCase());
  if (countries.length === 1) {
    conditions.push(sql`LOWER(${jobs.country}) = ${countries[0]}`);
  } else {
    conditions.push(
      sql`LOWER(${jobs.country}) IN (${sql.join(
        countries.map((c) => sql`${c}`),
        sql`, `,
      )})`,
    );
  }
}

// Single value filter
if (employment_type) {
  conditions.push(eq(jobs.employmentType, employment_type as any));
}

// Multiple value filter with enum
if (seniority_level) {
  const seniorities = seniority_level.split(",");
  if (seniorities.length === 1) {
    conditions.push(eq(jobs.seniorityLevel, seniorities[0] as any));
  } else {
    conditions.push(inArray(jobs.seniorityLevel, seniorities as any));
  }
}

const results = await db
  .select()
  .from(jobs)
  .where(and(...conditions))
  .limit(MAX_RESULTS);
```

**Why good:** Conditions array allows dynamic filter building, handles both single and multiple values correctly, `trim()` cleans whitespace from input, case-insensitive country matching prevents missed results, `inArray()` optimizes multiple value queries

---

## See Also

- [core.md](core.md) - Database connection and schema definition
- [transactions.md](transactions.md) - Atomic operations for complex queries

# Relational Queries v2 (RQB v2) Examples

RQB v2 introduces `defineRelations()` for centralized relation definitions and object-based `where`/`orderBy` syntax. Available in Drizzle ORM v1.0.0-beta.1+.

---

## Defining Relations with defineRelations()

The v2 API consolidates all relations into a single location using `defineRelations()`:

```typescript
// Good Example - RQB v2 with defineRelations()
import { defineRelations } from "drizzle-orm";
import { users, posts, comments, groups, usersToGroups } from "./schema";

// Define ALL relations in one centralized location
export const relations = defineRelations(
  { users, posts, comments, groups, usersToGroups },
  (r) => ({
    // Relations for the 'users' table
    users: {
      posts: r.many.posts({
        from: r.users.id,
        to: r.posts.authorId,
      }),
      comments: r.many.comments({
        from: r.users.id,
        to: r.comments.userId,
      }),
      // Many-to-many with .through() - eliminates junction table boilerplate
      groups: r.many.groups({
        from: r.users.id.through(r.usersToGroups.userId),
        to: r.groups.id.through(r.usersToGroups.groupId),
      }),
    },
    // Relations for the 'posts' table
    posts: {
      author: r.one.users({
        from: r.posts.authorId,
        to: r.users.id,
      }),
      comments: r.many.comments({
        from: r.posts.id,
        to: r.comments.postId,
      }),
    },
    // Relations for the 'comments' table
    comments: {
      user: r.one.users({
        from: r.comments.userId,
        to: r.users.id,
      }),
      post: r.one.posts({
        from: r.comments.postId,
        to: r.posts.id,
      }),
    },
    // Relations for the 'groups' table
    groups: {
      members: r.many.users({
        from: r.groups.id.through(r.usersToGroups.groupId),
        to: r.users.id.through(r.usersToGroups.userId),
      }),
    },
  }),
);
```

**Why good:** Single location for all relations enables full autocomplete through `r` parameter, `through()` handles many-to-many junction tables automatically, centralized definition prevents inconsistencies

```typescript
// Bad Example - Old v1 syntax (DEPRECATED)
import { relations } from "drizzle-orm";

// ❌ Per-table relations() calls are deprecated in RQB v2
export const usersRelations = relations(users, ({ many }) => ({
  posts: many(posts),
}));

export const postsRelations = relations(posts, ({ one }) => ({
  author: one(users, {
    fields: [posts.authorId],
    references: [users.id],
  }),
}));
```

**Why bad:** Scattered relation definitions across multiple files, harder to maintain, `fields`/`references` syntax replaced by `from`/`to` in v2

---

## Database Initialization (v2)

```typescript
// Good Example - v2 initialization with relations object
import { drizzle } from "drizzle-orm/neon-http";
import { neon } from "@neondatabase/serverless";
import { relations } from "./relations";

const sql = neon(process.env.DATABASE_URL!);

// Pass relations object (NOT schema)
export const db = drizzle(sql, {
  relations,
  casing: "snake_case",
});
```

**Why good:** v2 uses `relations` object instead of `schema`, enables RQB v2 query syntax

```typescript
// Bad Example - v1 initialization (still works but outdated)
import { drizzle } from "drizzle-orm/neon-http";
import * as schema from "./schema";

// ❌ v1 syntax - passing schema with mode
const db = drizzle(sql, { schema, mode: "default" });
```

**Why bad:** v1 syntax doesn't enable RQB v2 features, `mode` parameter removed for most dialects

---

## Object-Based Where Syntax

RQB v2 replaces callback-based `where` with simpler object syntax:

```typescript
// Good Example - v2 object-based where (simple equality)
const activeUsers = await db.query.users.findMany({
  where: {
    isActive: true,
    role: "admin",
  },
  with: {
    posts: true,
  },
});

// Good Example - v2 object-based where with operators
const filteredUsers = await db.query.users.findMany({
  where: {
    isActive: true,
    age: { gt: 18 }, // Greater than
    email: { like: "%@company.com" }, // LIKE pattern
    status: { in: ["active", "pending"] }, // IN array
    deletedAt: { isNull: true }, // NULL check
  },
  orderBy: { createdAt: "desc" },
});

// Available operators: eq, ne, gt, gte, lt, lte, in, notIn, like, ilike, isNull, isNotNull

// Complex conditions with logical operators
const complexQuery = await db.query.users.findMany({
  where: {
    OR: [{ role: "admin" }, { AND: [{ role: "user" }, { age: { gte: 21 } }] }],
    NOT: { status: "banned" },
  },
});
```

**Why good:** Object syntax is cleaner and more readable, still supports full operator set (AND, OR, NOT, gt, lt, like, etc.), orderBy uses object notation

```typescript
// Bad Example - v1 callback-based where (DEPRECATED)
const users = await db.query.users.findMany({
  // ❌ Callback syntax deprecated in RQB v2
  where: (users, { eq }) => eq(users.isActive, true),
  orderBy: (users, { desc }) => [desc(users.createdAt)],
});
```

**Why bad:** Callback syntax is verbose and deprecated, object syntax is cleaner and better typed

---

## Many-to-Many with through()

The `through()` method eliminates manual junction table mapping:

```typescript
// Good Example - Direct many-to-many access
// Define the relation with through()
groups: r.many.groups({
  from: r.users.id.through(r.usersToGroups.userId),
  to: r.groups.id.through(r.usersToGroups.groupId),
}),

// Query directly - no junction table in results
const userWithGroups = await db.query.users.findFirst({
  where: { id: userId },
  with: {
    groups: true, // Returns Group[] directly, not UsersToGroups[]
  },
});

console.log(userWithGroups.groups); // Group[] - junction table is abstracted away
```

**Why good:** `through()` abstracts junction table completely, query returns `Group[]` directly instead of `{ groupId, userId, group: Group }[]`

```typescript
// Bad Example - v1 manual junction table mapping
// ❌ v1 required selecting through junction table
const userWithGroups = await db.query.users.findFirst({
  where: eq(users.id, userId),
  with: {
    usersToGroups: {
      with: {
        group: true,
      },
    },
  },
});

// Then manually map the results
const groups = userWithGroups.usersToGroups.map((utg) => utg.group);
```

**Why bad:** Requires manual mapping, junction table pollutes type, more verbose queries

---

## Filtering on Relations

Filter parent records based on related entities:

```typescript
// Good Example - Filter by related entity
const usersWithRecentPosts = await db.query.users.findMany({
  where: {
    posts: {
      // Filter users who have posts created in last 7 days
      createdAt: gt(new Date(Date.now() - 7 * 24 * 60 * 60 * 1000)),
    },
  },
  with: {
    posts: {
      limit: 5,
      orderBy: { createdAt: "desc" },
    },
  },
});
```

**Why good:** Can filter parent by related entity attributes, nested limit/orderBy on relations

---

## Predefined Where in Relations

Add default filters directly in relation definitions:

```typescript
// Good Example - Predefined filter in relation definition
const relations = defineRelations({ users, posts }, (r) => ({
  users: {
    // Only return published posts by default
    publishedPosts: r.many.posts({
      from: r.users.id,
      to: r.posts.authorId,
      where: {
        status: "published",
        deletedAt: null,
      },
    }),
    // Separate relation for drafts
    draftPosts: r.many.posts({
      from: r.users.id,
      to: r.posts.authorId,
      where: {
        status: "draft",
      },
    }),
  },
}));

// Query uses the predefined filter automatically
const user = await db.query.users.findFirst({
  where: { id: userId },
  with: {
    publishedPosts: true, // Already filtered to status='published'
  },
});
```

**Why good:** Encapsulates common filtering logic, prevents accidental exposure of unpublished/deleted content

**Note:** Predefined `where` can only filter on the target (`to`) table, not the source table.

---

## Optional Relations

Control nullability of one-to-one relations:

```typescript
// Good Example - Required vs optional relations
const relations = defineRelations({ users, profiles, settings }, (r) => ({
  users: {
    // Optional (default) - user may not have a profile
    profile: r.one.profiles({
      from: r.users.id,
      to: r.profiles.userId,
      optional: true, // This is the default
    }),
    // Required - throws if user doesn't have settings
    settings: r.one.settings({
      from: r.users.id,
      to: r.settings.userId,
      optional: false, // TypeScript type is non-nullable
    }),
  },
}));
```

**Why good:** `optional: false` provides non-nullable types when relation is guaranteed to exist

---

## Splitting Relations with defineRelationsPart

For large schemas, split relations across files:

```typescript
// Good Example - Split relations across files
// relations/users.ts
import { defineRelationsPart } from "drizzle-orm";
import * as schema from "../schema";

export const userRelations = defineRelationsPart(schema, (r) => ({
  users: {
    posts: r.many.posts({ from: r.users.id, to: r.posts.authorId }),
  },
}));

// relations/posts.ts
export const postRelations = defineRelationsPart(schema, (r) => ({
  posts: {
    author: r.one.users({ from: r.posts.authorId, to: r.users.id }),
  },
}));

// relations/index.ts - Combine all parts
import { defineRelations } from "drizzle-orm";
import * as schema from "../schema";
import { userRelations } from "./users";
import { postRelations } from "./posts";

// CRITICAL: Main relations must spread FIRST for TypeScript inference
export const relations = defineRelations(schema, (r) => ({
  ...userRelations,
  ...postRelations,
}));
```

**Why good:** Keeps large codebases organized, each domain owns its relations

**Critical:** Main `defineRelations()` must spread first to ensure proper TypeScript inference.

---

## Migration from v1 to v2

Quick migration steps:

1. Run `npx drizzle-kit pull` to auto-generate v2 relations (optional)
2. Create `relations.ts` with `defineRelations()` consolidating all relations
3. Replace `drizzle(url, { schema })` with `drizzle(url, { relations })`
4. Update queries: convert callback `where`/`orderBy` to object syntax
5. Update field references: `fields` → `from`, `references` → `to`
6. Update relation names: `relationName` → `alias`

| v1 Syntax                                 | v2 Syntax                                      |
| ----------------------------------------- | ---------------------------------------------- |
| `relations(table, ...)` per table         | `defineRelations({ tables }, ...)` centralized |
| `fields: [table.column]`                  | `from: r.table.column`                         |
| `references: [other.id]`                  | `to: r.other.id`                               |
| `relationName: "author"`                  | `alias: "author"`                              |
| `where: (t, { eq }) => eq(t.col, val)`    | `where: { col: val }`                          |
| `orderBy: (t, { desc }) => [desc(t.col)]` | `orderBy: { col: "desc" }`                     |
| Manual junction table mapping             | `through()` for many-to-many                   |

---

## See Also

- [core.md](core.md) - Database connection and schema definition
- [queries.md](queries.md) - Query builder patterns (still valid for complex queries)
- [Drizzle Relations v2 Docs](https://orm.drizzle.team/docs/relations-v2)
- [RQB v2 Docs](https://orm.drizzle.team/docs/rqb-v2)
- [v1 to v2 Migration](https://orm.drizzle.team/docs/relations-v1-v2)

# Seeding Examples

Patterns for populating development databases with test data. Includes manual seeding and the official `drizzle-seed` package for deterministic fake data generation.

---

## drizzle-seed Package (Recommended)

The official `drizzle-seed` package generates deterministic, realistic fake data. Requires `drizzle-orm@0.36.4+`.

### Installation

```bash
bun add drizzle-seed
```

### Basic Usage

```typescript
// Good Example - drizzle-seed basic usage
import { seed, reset } from "drizzle-seed";
import { drizzle } from "drizzle-orm/neon-http";
import * as schema from "./db/schema";

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

const DEFAULT_SEED_COUNT = 10;
const SEED_VALUE = 42; // Deterministic seed for reproducibility

async function seedDatabase() {
  // Clear all tables (respects foreign keys)
  await reset(db, schema);

  // Seed with default options (10 rows per table)
  await seed(db, schema);

  // Or with custom count and deterministic seed
  await seed(db, schema, {
    count: DEFAULT_SEED_COUNT,
    seed: SEED_VALUE, // Same seed = same data every run
    version: 2, // Generator version (default: 2) - pin for stability
  });

  console.log("Database seeded");
}

seedDatabase().catch(console.error);
```

**Why good:** Deterministic seeding ensures reproducible data across test runs, `reset()` handles foreign key constraints automatically, respects schema relationships

### Refinements API

Customize data generation per table:

```typescript
// Good Example - drizzle-seed with refinements
import { seed } from "drizzle-seed";

const USERS_COUNT = 20;
const POSTS_PER_USER = 5;

await seed(db, schema).refine((f) => ({
  users: {
    columns: {
      name: f.fullName(),
      email: f.email(),
      avatar: f.valuesFromArray({ values: ["avatar1.png", "avatar2.png"] }),
    },
    count: USERS_COUNT,
    with: {
      posts: POSTS_PER_USER, // Create 5 posts per user
    },
  },
  posts: {
    columns: {
      title: f.loremIpsum({ sentencesCount: 1 }),
      content: f.loremIpsum({ sentencesCount: 10 }),
      publishedAt: f.date({ minDate: "2024-01-01", maxDate: "2024-12-31" }),
    },
  },
}));
```

**Why good:** Fine-grained control per column, built-in generators (`fullName`, `email`, `loremIpsum`, `date`), `with` creates related records automatically

### Available Generators

| Generator                                     | Description           |
| --------------------------------------------- | --------------------- |
| `f.fullName()`                                | Realistic full names  |
| `f.email()`                                   | Valid email addresses |
| `f.companyName()`                             | Company names         |
| `f.city()`                                    | City names            |
| `f.date({ minDate, maxDate })`                | Date range            |
| `f.int({ minValue, maxValue })`               | Integer range         |
| `f.number({ minValue, maxValue, precision })` | Decimal numbers       |
| `f.loremIpsum({ sentencesCount })`            | Lorem ipsum text      |
| `f.valuesFromArray({ values })`               | Pick from array       |
| `f.weightedRandom([{ weight, value }])`       | Weighted distribution |

### Reset Behavior

The `reset()` function uses database-specific strategies:

- **PostgreSQL/CockroachDB:** `TRUNCATE ... CASCADE`
- **MySQL:** `DELETE` with foreign key check disable
- **SQLite:** `DELETE` with pragma foreign_keys off

```typescript
// Reset specific tables only
await reset(db, { users: schema.users, posts: schema.posts });
```

---

## Manual Seeding (Simple Cases)

For simple cases or when you need full control over seed data.

### Safe Seeding with Cleanup

```typescript
// Good Example - Safe seeding with cleanup
import { db } from "./db";
import { companies, jobs, skills } from "./db/schema";

async function seed() {
  console.log("Seeding database...");

  // Clear existing data (development only!)
  await db.delete(jobs);
  await db.delete(companies);

  // Seed companies
  const [acme] = await db
    .insert(companies)
    .values({
      name: "Acme Corp",
      slug: "acme-corp",
      websiteUrl: "https://acme.com",
    })
    .returning();

  // Seed jobs
  await db.insert(jobs).values([
    {
      companyId: acme.id,
      title: "Senior Engineer",
      description: "Build amazing products",
      externalUrl: "https://acme.com/jobs/1",
      employmentType: "full_time",
    },
    {
      companyId: acme.id,
      title: "Product Designer",
      description: "Design user experiences",
      externalUrl: "https://acme.com/jobs/2",
      employmentType: "full_time",
    },
  ]);

  console.log("Database seeded");
}

seed().catch(console.error);
```

**Why good:** Cleanup ensures clean slate, `.returning()` gets IDs for dependent records, error handling with `.catch()` surfaces failures, uses enum values for type safety

```typescript
// Bad Example - Unsafe seeding
async function seed() {
  // No cleanup - causes duplicate errors on re-run
  await db.insert(companies).values({
    name: "Acme Corp",
  }); // Missing .returning()

  // Hard-coded UUID - fragile
  await db.insert(jobs).values({
    companyId: "12345",
  });
}
```

**Why bad:** No cleanup causes errors on re-run, missing `.returning()` prevents getting generated IDs, hard-coded UUIDs are fragile and break easily, no error handling hides failures

---

## Package.json Script

```json
{
  "scripts": {
    "db:seed": "bun run scripts/seed.ts"
  }
}
```

---

## See Also

- [core.md](core.md) - Schema definition for seeding
- [migrations.md](migrations.md) - Apply migrations before seeding
- [transactions.md](transactions.md) - Wrap seeding in transaction for atomicity
- [drizzle-seed Docs](https://orm.drizzle.team/docs/seed-overview) - Official documentation
- [drizzle-seed Generators](https://orm.drizzle.team/docs/seed-functions) - All available generators

# Transaction Examples

Patterns for atomic database operations with proper error handling and rollback.

---

## Basic Transaction

```typescript
// Good Example - Proper transaction usage
await db.transaction(async (tx) => {
  // Insert parent
  const [company] = await tx
    .insert(companies)
    .values({
      name: "Acme Corp",
      slug: "acme-corp",
      websiteUrl: "https://acme.com",
    })
    .returning({ id: companies.id });

  // Insert children
  await tx.insert(jobs).values([
    {
      companyId: company.id,
      title: "Senior Engineer",
      description: "Build things",
      externalUrl: "https://acme.com/jobs/1",
    },
    {
      companyId: company.id,
      title: "Junior Designer",
      description: "Design things",
      externalUrl: "https://acme.com/jobs/2",
    },
  ]);

  // All succeed or all fail together
});
```

**Why good:** Uses `tx` parameter (not `db`) ensuring atomicity, `.returning()` gets inserted ID for child records, all operations succeed or fail together preventing partial data, transaction keeps related data consistent

```typescript
// Bad Example - Not using transaction parameter
await db.transaction(async (tx) => {
  const [company] = await db.insert(companies).values({...}); // Using db instead of tx
  await db.insert(jobs).values({...}); // Using db instead of tx
});
```

**Why bad:** Using `db` instead of `tx` bypasses transaction context breaking atomicity, operations can succeed/fail independently leaving inconsistent data, defeats entire purpose of transaction

---

## Transaction with Error Handling

```typescript
// Good Example - Transaction with proper error handling
try {
  await db.transaction(async (tx) => {
    const [job] = await tx
      .update(jobs)
      .set({ isActive: false })
      .where(eq(jobs.id, jobId))
      .returning();

    if (!job) {
      throw new Error("Job not found");
    }

    await tx.insert(auditLogs).values({
      entityType: "job",
      entityId: job.id,
      action: "deactivated",
      userId: currentUserId,
    });
  });
} catch (error) {
  console.error("Transaction failed:", error);
  // Nothing was changed in the database
}
```

**Why good:** Try-catch handles transaction failures, throwing error inside transaction rolls back all changes, validation ensures data exists before proceeding, audit log and update are atomic

---

## See Also

- [core.md](core.md) - Database connection setup
- [queries.md](queries.md) - Query patterns for reading data