# Typescript Drizzle Orm

> Type-safe SQL with Drizzle ORM in TypeScript. Use when defining database schemas, writing queries, setting up relations, running migrations, working with PostgreSQL/MySQL/SQLite/Cloudflare D1/Durable Objects data layers, or integrating drizzle-orm/effect-postgres with @effect/sql-pg and Effect Layers.

- Skill: `martinffx/typescript-drizzle-orm` (Agent Skill, multi-file: 6 files)
- Install (CLI): `npx skillmds@latest add martinffx/typescript-drizzle-orm`
- Raw SKILL.md: https://api.skillmd.com/api/skills/martinffx/typescript-drizzle-orm/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Data & Analytics
- Author: martinffx (https://skillmd.com/u/martinffx)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/martinffx/typescript-drizzle-orm

---


# Drizzle ORM

Inspect the owning package and existing implementation first. Reuse established
project types, helpers, errors, lifecycle behavior, and test utilities. The
patterns below are options, not an implementation checklist. Introduce one only
when the current task requires it.

## Project-specific rules

- Prefer PostgreSQL constraints and driver behavior over application machinery.
- Let established domain models own `fromRequest`, `fromRow`, and `toRow`.
  Import inferred request and Drizzle row types with type-only imports instead of
  creating mirror types.
- Keep queries, transactions, write predicates, and driver error classification
  in repositories. Services orchestrate use cases; routes validate and serialize
  HTTP data.
- Use the driver’s lifecycle methods before adding connection tracking or shutdown
  orchestration.
- Require a current query or operational need before adding indexes, migrations,
  retries, or pool customization.

Lightweight, type-safe ORM with SQL-like and relational query APIs for PostgreSQL, MySQL, SQLite, Cloudflare D1, and Durable Objects.

## Quick Start (PostgreSQL)

```typescript
import {
  pgTable,
  serial,
  text,
  integer,
  timestamp,
  boolean,
  varchar
} from 'drizzle-orm/pg-core'

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
  email: varchar('email', { length: 255 }).notNull().unique(),
  age: integer('age'),
  isActive: boolean('is_active').default(true),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').$onUpdate(() => new Date()),
})

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  title: text('title').notNull(),
  content: text('content'),
  authorId: integer('author_id')
    .notNull()
    .references(() => users.id, { onDelete: 'cascade' }),
  createdAt: timestamp('created_at').defaultNow().notNull(),
})
```

See [references/postgresql.md](./references/postgresql.md) for detailed PostgreSQL patterns.

## Quick Start (SQLite/D1)

```typescript
import { sqliteTable, text, integer } from 'drizzle-orm/sqlite-core'

export const users = sqliteTable('users', {
  id: text('id').primaryKey(),
  name: text('name').notNull(),
  email: text('email').notNull(),
  isActive: integer('is_active', { mode: 'boolean' }).default(true),
  createdAt: text('created_at').notNull(),
})

export const posts = sqliteTable('posts', {
  id: text('id').primaryKey(),
  title: text('title').notNull(),
  content: text('content'),
  authorId: text('author_id')
    .notNull()
    .references(() => users.id, { onDelete: 'cascade' }),
  createdAt: text('created_at').notNull(),
})
```

See [references/sqlite.md](./references/sqlite.md) for SQLite patterns and [references/cloudflare.md](./references/cloudflare.md) for D1 and Durable Objects.

## Type Inference

```typescript
// Infer types from schema - no manual interfaces needed
export type User = typeof users.$inferSelect
export type NewUser = typeof users.$inferInsert

export type Post = typeof posts.$inferSelect
export type NewPost = typeof posts.$inferInsert
```

## Relations

```typescript
import { relations } from 'drizzle-orm'

export const usersRelations = relations(users, ({ many }) => ({
  posts: many(posts),
}))

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

## SQL-like Queries

```typescript
import { eq, and, or, gt, like, isNull, desc, asc } from 'drizzle-orm'

// Select all
const allUsers = await db.select().from(users)

// Select specific columns
const names = await db.select({ name: users.name }).from(users)

// Where clause
const activeUsers = await db
  .select()
  .from(users)
  .where(eq(users.isActive, true))

// Multiple conditions
const filtered = await db
  .select()
  .from(users)
  .where(and(
    eq(users.isActive, true),
    gt(users.age, 18)
  ))

// Like/pattern matching
const matching = await db
  .select()
  .from(users)
  .where(like(users.email, '%@example.com'))

// Order and limit
const recent = await db
  .select()
  .from(posts)
  .orderBy(desc(posts.createdAt))
  .limit(10)

// Joins
const postsWithAuthors = await db
  .select({
    postTitle: posts.title,
    authorName: users.name,
  })
  .from(posts)
  .leftJoin(users, eq(posts.authorId, users.id))
```

## Relational Queries

```typescript
// Requires schema with relations passed to drizzle()
const db = drizzle(pool, { schema })

// Find many with relations
const usersWithPosts = await db.query.users.findMany({
  with: {
    posts: true,
  },
})

// Partial columns + nested relations
const partial = await db.query.users.findMany({
  columns: {
    id: true,
    name: true,
  },
  with: {
    posts: {
      columns: {
        title: true,
        createdAt: true,
      },
    },
  },
})

// Find first
const user = await db.query.users.findFirst({
  where: eq(users.id, 1),
  with: { posts: true },
})

// Exclude columns
const withoutEmail = await db.query.users.findMany({
  columns: {
    email: false, // exclude
  },
})
```

## Insert

```typescript
// Single insert
const [newUser] = await db
  .insert(users)
  .values({ name: 'Alice', email: 'alice@example.com' })
  .returning()

// Multiple insert
await db.insert(users).values([
  { name: 'Bob', email: 'bob@example.com' },
  { name: 'Carol', email: 'carol@example.com' },
])

// Upsert (on conflict)
await db
  .insert(users)
  .values({ name: 'Alice', email: 'alice@example.com' })
  .onConflictDoUpdate({
    target: users.email,
    set: { name: 'Alice Updated' },
  })
```

## Update

```typescript
await db
  .update(users)
  .set({ isActive: false })
  .where(eq(users.id, 1))

// Update with returning
const [updated] = await db
  .update(users)
  .set({ name: 'New Name' })
  .where(eq(users.id, 1))
  .returning()
```

## Delete

```typescript
await db.delete(users).where(eq(users.id, 1))

// Delete with returning
const [deleted] = await db
  .delete(users)
  .where(eq(users.id, 1))
  .returning()
```

## Transactions

```typescript
await db.transaction(async (tx) => {
  const [user] = await tx
    .insert(users)
    .values({ name: 'Alice', email: 'alice@example.com' })
    .returning()

  await tx.insert(posts).values({
    title: 'First Post',
    authorId: user.id,
  })
})
```

## Entity Pattern

Use an Entity pattern only when the owning package already uses it or the current
task needs a domain boundary that plain inferred records cannot provide.

```typescript
import type { InferInsertModel, InferSelectModel } from 'drizzle-orm'
import type { users } from './schema'
import type { CreateUserRequest } from './routes/users/schema'

type UserRow = InferSelectModel<typeof users>
type UserInsert = InferInsertModel<typeof users>

class UserEntity {
  public readonly id: string
  public readonly name: string
  public readonly email: string
  public readonly createdAt: Date

  private constructor(data: UserEntityData) {
    Object.assign(this, data)
  }

  // API request → Entity
  static fromRequest(rq: CreateUserRequest, id?: string): UserEntity {
    return new UserEntity({
      id: id ?? crypto.randomUUID(),
      name: rq.name,
      email: rq.email,
      createdAt: new Date(),
    })
  }

  // DB row → Entity
  static fromRow(row: UserRow): UserEntity {
    return new UserEntity({
      id: row.id,
      name: row.name,
      email: row.email,
      createdAt: row.createdAt,
    })
  }

  // Entity → DB row
  toRow(): UserInsert {
    return {
      id: this.id,
      name: this.name,
      email: this.email,
      createdAt: this.createdAt,
    }
  }
}
```

The route serializes `UserEntity` into the declared HTTP response schema.

See [references/entity-pattern.md](./references/entity-pattern.md) for detailed examples.

## Repository Pattern

Use a Repository only when the owning package already has that boundary or the
current task needs a stable abstraction over multiple queries. Direct Drizzle
queries are valid when they match the existing implementation.

```typescript
import { eq, and } from 'drizzle-orm'
import { users } from './schema'
import { UserEntity } from './entities/UserEntity'

class UserRepo {
  constructor(private db: DrizzleDB) {}

  async getById(id: string): Promise<UserEntity> {
    const record = await this.db.query.users.findFirst({
      where: eq(users.id, id),
    })
    if (!record) throw new NotFoundError('User not found')
    return UserEntity.fromRow(record)
  }

  async create(entity: UserEntity): Promise<UserEntity> {
    try {
      const [record] = await this.db
        .insert(users)
        .values(entity.toRow())
        .returning()
      return UserEntity.fromRow(record)
    } catch (error) {
      throw handleDBError(error, { userId: entity.id })
    }
  }

  async update(entity: UserEntity): Promise<UserEntity> {
    const [record] = await this.db
      .update(users)
      .set(entity.toRow())
      .where(eq(users.id, entity.id))
      .returning()
    if (!record) throw new NotFoundError('User not found')
    return UserEntity.fromRow(record)
  }
}
```

See [references/repository-pattern.md](./references/repository-pattern.md) for detailed examples.

## Database-Specific Guides

Load a database-specific guide only for the current database and task. Its
migration, index, retry, and connection examples remain conditional:

- **[PostgreSQL patterns](./references/postgresql.md)** - Connection, migrations, column types, error codes, optimistic locking
- **[SQLite patterns](./references/sqlite.md)** - Schema definition, type differences, better-sqlite3 testing
- **[Cloudflare D1 & Durable Objects](./references/cloudflare.md)** - D1 connection, DO SQLite, testing with vitest-pool-workers, D1 vs DO decision guide
- **[Effect PostgreSQL](https://orm.drizzle.team/docs/connect-effect-postgres)** - For
  `drizzle-orm/effect-postgres`, confirm compatible Effect v4 peer dependencies.
  When `typescript-effect-ts` is installed, also read its
  `references/v4/drizzle-effect-postgres.md`; otherwise use the official guide.
  Follow installed declarations when examples differ.

## Guidelines

1. Reuse the existing schema layout, inferred types, query style, and data-access boundaries.
2. Prefer database constraints and native driver behavior to duplicate application checks.
3. Add relations, repositories, or entities only when a current query or domain boundary needs them.
4. Keep `returning()`, tenant and optimistic-write predicates, and driver error
   classification in the repository.
5. Add optimistic locking only for a demonstrated concurrency requirement. Let
   the service decide whether to retry the use case.
6. Close owned connections with the driver's lifecycle API in the existing shutdown
   path. For `@effect/sql-pg`, let its `PgClient` Layer own the pool lifecycle
   instead of adding a second pool or shutdown path.