@apisr/drizzle-model

Development Status: This package is in active development and not recommended for production use yet. APIs may change between minor versions. Semantic versioning will be enforced after v1.0.0 stable release.

Requires Drizzle ORM Beta: Built for drizzle-orm@^1.0.0-beta.2-86f844e with relations v2.

Overview

@apisr/drizzle-model adds a powerful model abstraction layer on top of Drizzle ORM, providing:

Reusable model abstraction per table — encapsulate table logic in models
Progressive query pipeline — build queries step-by-step with clear intent
Unified result-shaping layer — consistent formatting and transformation
Safe execution flows — error handling without try-catch boilerplate
Reusable business logic extensions — custom methods and model composition

Installation

npm install @apisr/drizzle-model drizzle-orm@beta

drizzle-orm is a peer dependency. You must install a beta version (≥ 1.0.0-beta.2).

Quick Start

Create Your First Model

import { modelBuilder, esc } from "@apisr/drizzle-model";
import { drizzle } from "drizzle-orm/node-postgres";
import * as schema from "./schema";
import { relations } from "./relations";

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

const model = modelBuilder({
  db,
  schema,
  relations, // requires DrizzleORM relations v2
  dialect: "PostgreSQL",
});

const userModel = model("user", {});

Basic Queries

// Find one user
const user = await userModel.findFirst();

// Find with filter (use esc() for literal values)
const users = await userModel
  .where({ name: esc("Alex") })
  .findMany();

// Count records
const total = await userModel.count();
const verified = await userModel
  .where({ isVerified: esc(true) })
  .count();

Philosophy

Why not just use Drizzle directly?

Without drizzle-model:

import { eq } from "drizzle-orm";

await db
  .select()
  .from(schema.user)
  .where(eq(schema.user.id, 1));

With drizzle-model:

await userModel.where({ id: esc(1) }).findFirst();

The difference becomes more apparent with:

Consistent formatting across queries
Reusable where conditions
Nested relation loading
Custom business logic methods

Progressive Query Pipeline

Queries flow through four stages:

Intent Stage — declare what you want (where, insert, update, …)
Execution Stage — choose execution (findMany, findFirst, return, returnFirst)
Refinement Stage — shape the SQL query (select, exclude, with)
Programmatic Polishing — post-process the result (omit, raw, safe)

Core Features

Using `esc()` for Type Safety

The esc() function provides three ways to specify comparisons:

Implicit Equality
Chainable Methods
Drizzle Operators

where({ name: esc("Alex") })

where({ name: esc.like("%Alex%") })
where({ age: esc.gte(18) })
where({ status: esc.in(["active", "pending"]) })
where({ price: esc.between(10, 100) })

import { gte } from "drizzle-orm";
where({ age: esc(gte, 18) })

Available chainable methods:

esc.eq(value) — equality
esc.not(value) — inequality
esc.gt(value), esc.gte(value) — greater than
esc.lt(value), esc.lte(value) — less than
esc.like(pattern), esc.ilike(pattern) — pattern matching
esc.in(values), esc.nin(values) — array membership
esc.between(min, max), esc.notBetween(min, max) — range

Basic Writes

Insert
Update
Delete
Upsert

await userModel.insert({
  name: "New User",
  email: "[email protected]",
  age: 18,
});

const updated = await userModel
  .where({ id: esc(1) })
  .update({ name: "Updated" })
  .returnFirst();

await userModel.where({ id: esc(2) }).delete();

const row = await userModel
  .upsert({
    insert: { name: "Alex", email: "[email protected]", age: 20 },
    update: { name: "Alex Updated" },
    target: schema.user.email,
  })
  .returnFirst();

Loading Relations with `.with()`

// Load related posts for each user
const users = await userModel
  .findMany()
  .with({ posts: true });

// Nested relations
const users = await userModel
  .findMany()
  .with({
    posts: {
      comments: true,
    },
  });

// Query where relations
const users = await userModel
  .findMany()
  .with({
    posts: postModel.where({
      title: {
        like: "New%"
      }
    }),
  });

// Use .include() to add nested relations to filtered models
const users = await userModel.findMany().with({
  posts: postModel.where({
    title: { like: "New%" }
  }).include({
    comments: true
  })
});

No N+1 queries: .with() uses JOIN-based loading, not separate queries per row.

SQL Column Selection

// Only fetch specific columns
const users = await userModel
  .findMany()
  .select({ id: true, name: true });

// Exclude specific columns
const users = await userModel
  .findMany()
  .exclude({ email: true });

// Combine select and exclude
const users = await userModel
  .findMany()
  .select({ id: true, name: true, email: true })
  .exclude({ email: true });

Error-Safe Execution

Use .safe() for error-as-value patterns:

const result = await userModel.findMany().safe();

if (result.error) {
  console.error(result.error);
} else {
  console.log(result.data);
}

result

SafeResult<T>

type SafeResult<T> =
  | { data: T; error: undefined }
  | { data: undefined; error: unknown };

Transactions

Use .db() to bind a model to a transaction:

await db.transaction(async (tx) => {
  const txUser = userModel.db(tx);
  const txPost = postModel.db(tx);
  
  const user = await txUser.insert({
    name: "Alice",
    email: "[email protected]",
    age: 25,
  }).returnFirst();
  
  await txPost.insert({
    title: "First Post",
    content: "Hello world",
    authorId: user.id,
  });
});

Advanced Features

Format Function

Transform every row returned from queries:

const postModel = model("post", {
  format(row) {
    return {
      ...row,
      createdAt: new Date(row.createdAt),
      updatedAt: row.updatedAt ? new Date(row.updatedAt) : null,
    };
  },
});

// Use .raw() to bypass format when needed
const rawPost = await postModel.findFirst().raw();

Default Where Conditions

const activeUsers = model("user", {
  where: { isVerified: esc(true) },
});

Custom Methods

const userModel = model("user", {
  methods: {
    async byEmail(email: string) {
      return await userModel.where({ email: esc(email) }).findFirst();
    },
    async adults() {
      return await userModel.where({ age: esc.gte(18) }).findMany();
    },
  },
});

// Use custom methods
const user = await userModel.byEmail("[email protected]");
const adults = await userModel.adults();

Extending Models

const extended = userModel.extend({
  methods: {
    async findActive() {
      return await userModel.where({ status: esc("active") }).findMany();
    },
  },
});

When method names conflict during extend, existing runtime methods take precedence over newly passed ones.

Performance Considerations

Selective Loading

Use .select() to fetch only needed columns and reduce payload size.

Count Optimization

Use .count() instead of .findMany() when you only need the count.

Index Usage

Add database indexes on columns used in .where() conditions.

Skip Formatting

Use .raw() to skip formatting when performance is critical.

API Reference

Intent Stage

where

(value) => Model

Filter conditions for queries

insert

(value) => Model

Insert new rows

update

(value) => Model

Update existing rows

delete

() => Model

Delete rows

upsert

(value) => Model

Insert or update rows

Execution Stage

findMany

() => Promise<T[]>

Fetch multiple rows

findFirst

() => Promise<T | undefined>

Fetch first matching row

count

() => Promise<number>

Count matching rows

return

() => Promise<T[]>

Return all affected rows from mutations

returnFirst

() => Promise<T | undefined>

Return first affected row from mutations

with

(relations) => Promise<T>

Load related entities via JOINs

select

(fields) => Promise<T>

SQL SELECT whitelist

exclude

(fields) => Promise<T>

SQL SELECT blacklist

Programmatic Stage

omit

(fields) => Promise<T>

Remove fields from result after query (not SQL)

raw

() => Promise<T>

Skip format function

safe

() => Promise<SafeResult<T>>

Wrap result in { data, error }

debug

() => Model

Inspect query state

Model-level Utilities

include

(value) => Model

Specify nested relations for model instances in .with()

extend

(options) => Model

Create extended model with additional methods

(dbInstance) => Model

Bind model to different db/transaction instance

Compatibility

Drizzle Version	Supported	Notes
v1 beta (≥ 1.0.0-beta.2)	✅ Yes	Requires relations v2
v0.x stable	❌ No	Relations v1 not supported

Supported dialects:

PostgreSQL
MySQL
SQLite

Runtime requirements:

Node.js 18+
Bun 1.0+

Limitations

Requires Drizzle ORM relations v2 — v1 relations are not supported
Explicit esc() required — plain values in .where() are not allowed (by design for type safety)
No lazy loading — relations must be loaded eagerly with .with()
No middleware system — use format() for transformations
No automatic soft deletes — implement via default where conditions

Get Started

Core Packages

Utility Packages

Guides

@apisr/drizzle-model

Overview

Installation

Quick Start

Create Your First Model

Basic Queries

Philosophy

Why not just use Drizzle directly?

Progressive Query Pipeline

Core Features

Using `esc()` for Type Safety

Basic Writes

Result Refinement

Loading Relations with `.with()`

SQL Column Selection

Error-Safe Execution

Transactions

Advanced Features

Format Function

Default Where Conditions

Custom Methods

Extending Models

Performance Considerations

Selective Loading

Count Optimization

Index Usage

Skip Formatting

API Reference

Intent Stage

Execution Stage

Refinement Stage

Programmatic Stage

Model-level Utilities

Compatibility

Limitations

Build docs developers (and LLMs) love

Get Started

Core Packages

Utility Packages

Guides

​Overview

​Installation

​Quick Start

​Create Your First Model

​Basic Queries

​Philosophy

​Why not just use Drizzle directly?

​Progressive Query Pipeline

​Core Features

​Using esc() for Type Safety

​Basic Writes

​Result Refinement

​Loading Relations with .with()

​SQL Column Selection

​Error-Safe Execution

​Transactions

​Advanced Features

​Format Function

​Default Where Conditions

​Custom Methods

​Extending Models

​Performance Considerations

Selective Loading

Count Optimization

Index Usage

Skip Formatting

​API Reference

​Intent Stage

​Execution Stage

​Refinement Stage

​Programmatic Stage

​Model-level Utilities

​Compatibility

​Limitations

Build docs developers (and LLMs) love

Overview

Installation

Quick Start

Create Your First Model

Basic Queries

Philosophy

Why not just use Drizzle directly?

Progressive Query Pipeline

Core Features

Using `esc()` for Type Safety

Basic Writes

Result Refinement

Loading Relations with `.with()`

SQL Column Selection

Error-Safe Execution

Transactions

Advanced Features

Format Function

Default Where Conditions

Custom Methods

Extending Models

Performance Considerations

API Reference

Intent Stage

Execution Stage

Refinement Stage

Programmatic Stage

Model-level Utilities

Compatibility

Limitations