Building APIs with Drizzle ORM and NestJS: Complete Guide

Drizzle ORM has emerged as the fastest-growing TypeScript ORM, offering SQL-like syntax with full type safety and zero runtime overhead. Combined with NestJS, it creates a powerful stack for building production APIs that are both developer-friendly and performant.

Key Takeaways

• Drizzle provides SQL-like queries with full TypeScript inference -- no code generation step
• NestJS dependency injection integrates cleanly with Drizzle database connections
• Schema-driven development ensures database and TypeScript types stay synchronized
• Drizzle push and migration commands handle schema evolution without downtime

---

Project Setup

Installation

Start with a NestJS project and add Drizzle dependencies:

npm install drizzle-orm postgres
npm install -D drizzle-kit @types/pg

We recommend the postgres.js driver for its performance. For connection pooling in production, consider PgBouncer or serverless-optimized drivers.

Database Connection Module

Create a global database module providing the Drizzle instance via dependency injection. Define a DB_TOKEN provider using useFactory that creates a postgres client from DATABASE_URL and returns drizzle(client, { schema }). Export the token so any service can inject it with @Inject("DRIZZLE_DB").

---

Schema Definition

Define database tables using Drizzle table builders. Each table exports a const created with pgTable() containing column definitions using uuid, varchar, timestamp, boolean, and other column types. Columns support defaults (defaultRandom, defaultNow), constraints (notNull, unique), and relations.

Defining Relations

Drizzle relations provide type-safe eager loading. Define relations using the relations() function, specifying one-to-many with many() and many-to-one with one(). Reference columns map foreign keys to their parent table primary keys.

---

Query Building

Select Queries

Drizzle queries read like SQL while providing full type inference:

• Simple select: db.select().from(users)
• Filtered: db.select().from(users).where(and(eq(users.role, "admin"), eq(users.isActive, true)))
• With joins: db.select().from(users).leftJoin(orders, eq(users.id, orders.userId))
• Relation loading: db.query.users.findMany({ with: { orders, true }, limit: 20 })

Insert, Update, Delete

// Insert with returning
const [newUser] = await this.db
  .insert(users)
  .values(`{ email: "[email protected]", name: "New User" }`)
  .returning();

// Update
await this.db
  .update(users)
  .set(`{ role: "admin", updatedAt: new Date() }`)
  .where(eq(users.id, userId));

// Delete
await this.db.delete(users).where(eq(users.id, userId));

---

Transactions

Drizzle transactions ensure atomic operations. Call db.transaction() with an async callback receiving a transaction object (tx). All queries within the callback execute atomically -- if any fail, the entire transaction rolls back.

Common use cases include creating parent and child records together, updating inventory when processing orders, and transferring funds between accounts.

---

Migrations

Schema Push (Development)

During development, push schema changes directly with drizzle-kit push. This compares your schema files against the database and applies changes. Fast for development but not suitable for production.

Migration Files (Production)

For production, generate versioned SQL migration files with drizzle-kit generate, then apply them with drizzle-kit migrate. Migration files can be reviewed, tested in staging, and applied in CI/CD pipelines.

---

NestJS Integration Patterns

Service Pattern

Services inject the Drizzle database and build queries with dynamic filters:

@Injectable()
export class ProductsService {
  constructor(@Inject("DRIZZLE_DB") private db: DrizzleDB) {}

  async findAll(filters: ProductFilters) {
    const conditions = [];
    if (filters.category)
      conditions.push(eq(products.category, filters.category));
    if (filters.minPrice)
      conditions.push(gte(products.price, filters.minPrice));

    return this.db
      .select()
      .from(products)
      .where(conditions.length ? and(...conditions) : undefined)
      .orderBy(desc(products.createdAt))
      .limit(filters.limit || 20)
      .offset(filters.offset || 0);
  }
}

Multi-Tenancy

For SaaS applications, every query must filter by organizationId. Create a base service or middleware that ensures tenant isolation on all database operations. Never trust client-provided organization IDs -- validate against the authenticated session.

Parameterized Queries

Drizzle uses parameterized queries by default, preventing SQL injection. Never use sql.raw() with user input. Instead, use the sql template literal which automatically parameterizes interpolated values.

---

Performance Best Practices

1. Select only needed columns: Use .select({ id: users.id, name: users.name }) instead of selecting all columns
2. Use indexes: Ensure frequently filtered columns have database indexes
3. Limit results: Always use .limit() on list queries
4. Batch inserts: Use .values([...array]) for bulk inserts instead of loops
5. Connection pooling: Configure appropriate pool sizes for your workload
6. Lazy connections: Use a Proxy pattern to defer connection until first query

---

Frequently Asked Questions

Q: How does Drizzle compare to Prisma for NestJS?

Drizzle is more SQL-oriented and has zero runtime overhead (no query engine). Prisma uses its own query language and requires a generated client. Drizzle has smaller bundle size and faster cold starts, while Prisma has a larger ecosystem and more tooling.

Q: Can Drizzle handle complex raw SQL?

Yes. The sql template literal supports raw queries while maintaining parameterized safety. Drizzle never uses string concatenation for queries, preventing SQL injection.

Q: What about connection pooling in production?

Use PgBouncer external to the application or configure the postgres.js driver with max connection limits. For serverless environments, use serverless-optimized drivers with built-in pooling.

Q: How do we handle schema changes without downtime?

Generate migration files, review the SQL, test in staging, then apply in production. Most ALTER TABLE statements in PostgreSQL execute without exclusive locks. Avoid operations that require table rewrites during peak hours.

---

What Is Next

Drizzle ORM with NestJS provides a type-safe, performant foundation for building production APIs. The SQL-like API minimizes the abstraction gap while TypeScript inference eliminates runtime type errors.

Contact ECOSIRE for API development assistance, or explore our Odoo integration services for connecting custom APIs with your ERP.

---

Published by ECOSIRE -- helping businesses scale with enterprise software solutions.