Back to Articles
Drizzle ORM Performance: Queries, Pooling, Type Safety

Drizzle ORM Performance: Queries, Pooling, Type Safety

5 min read 1,019 words

High-performance database operations with Drizzle ORM

Table of Contents

  1. Basic Setup
  2. Prepared Statements
  3. Selective Field Loading
  4. Joins and Relations
  5. Batch Operations
  6. Transactions
  7. Raw SQL
  8. Connection Pool Optimization
  9. Type-Safe Queries
  10. Quick Reference

1. Basic Setup

import { drizzle } from 'drizzle-orm/node-postgres';
import { Pool } from 'pg';
import * as schema from './schema';

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 20,
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
});

export const db = drizzle(pool, { schema });

2. Prepared Statements

import { sql } from 'drizzle-orm';
import { eq } from 'drizzle-orm';

// Prepared statement (compiled once, executed many times)
const getUserByEmail = db
  .select()
  .from(users)
  .where(eq(users.email, sql.placeholder('email')))
  .prepare('getUserByEmail');

// Execute with parameters
const user = await getUserByEmail.execute({ email: 'user@example.com' });

// Multiple prepared statements
const getActiveUsers = db
  .select()
  .from(users)
  .where(eq(users.active, true))
  .limit(sql.placeholder('limit'))
  .offset(sql.placeholder('offset'))
  .prepare('getActiveUsers');

const users = await getActiveUsers.execute({ limit: 20, offset: 0 });

3. Selective Field Loading

// BAD: Loads all columns
const posts = await db.query.posts.findMany();

// GOOD: Only load needed columns (partial select)
const posts = await db.query.posts.findMany({
  columns: {
    id: true,
    title: true,
    // Excludes: content, createdAt, updatedAt, etc.
  },
  limit: 20,
});

// With relations
const posts = await db.query.posts.findMany({
  columns: {
    id: true,
    title: true,
  },
  with: {
    author: {
      columns: {
        id: true,
        name: true,
      },
    },
  },
  limit: 20,
});

4. Joins and Relations

Define Relations in Schema

import { relations } from 'drizzle-orm';

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

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

Query with Relations (Avoids N+1)

const usersWithPosts = await db.query.users.findMany({
  with: {
    posts: {
      limit: 5,
      orderBy: (posts, { desc }) => [desc(posts.createdAt)],
    },
  },
  limit: 10,
});

Explicit JOIN

import { eq } from 'drizzle-orm';

const result = await db
  .select({
    user: users,
    post: posts,
  })
  .from(users)
  .leftJoin(posts, eq(users.id, posts.authorId))
  .where(eq(users.active, true))
  .limit(100);

5. Batch Operations

Batch Insert

const newUsers = [
  { name: 'User1', email: 'u1@example.com' },
  { name: 'User2', email: 'u2@example.com' },
  { name: 'User3', email: 'u3@example.com' },
];

await db.insert(users).values(newUsers);

// Batch insert with returning
const inserted = await db
  .insert(users)
  .values(newUsers)
  .returning();

Upsert

await db
  .insert(users)
  .values({ email: 'user@example.com', name: 'Updated Name' })
  .onConflictDoUpdate({
    target: users.email,
    set: { name: 'Updated Name' },
  });

Batch Update

await db
  .update(users)
  .set({ active: false })
  .where(
    inArray(users.id, [1, 2, 3, 4, 5])
  );

6. Transactions

Basic Transaction

const result = await db.transaction(async (tx) => {
  const [user] = await tx
    .insert(users)
    .values({ name: 'New User', email: 'new@example.com' })
    .returning();

  await tx
    .insert(orders)
    .values({ userId: user.id, total: 100 });

  return user;
});

Nested Transactions (Savepoints)

await db.transaction(async (tx) => {
  await tx.insert(users).values({ name: 'User1' });

  await tx.transaction(async (nestedTx) => {
    await nestedTx.insert(orders).values({ userId: 1, total: 50 });
    // This can be rolled back independently
  });
});

7. Raw SQL

import { sql } from 'drizzle-orm';

// Raw query with type safety
const result = await db.execute<{
  id: number;
  name: string;
  orderCount: number;
}>(sql`
  SELECT u.id, u.name, COUNT(o.id) as order_count
  FROM users u
  LEFT JOIN orders o ON u.id = o.user_id
  GROUP BY u.id
  HAVING COUNT(o.id) > 5
`);

// Parameterized raw SQL
const minOrders = 5;
const result = await db.execute(
  sql`SELECT * FROM users WHERE order_count > ${minOrders}`
);

8. Connection Pool Optimization

PostgreSQL with pg

import { Pool } from 'pg';

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,

  // Pool configuration
  max: 20,                    // Maximum connections
  min: 5,                     // Minimum connections
  idleTimeoutMillis: 30000,   // Close idle connections after 30s
  connectionTimeoutMillis: 5000,  // Wait 5s for connection
  maxUses: 7500,              // Close connection after N uses

  // Statement timeout
  statement_timeout: 30000,   // 30s query timeout
});

// With pg-native for 10% performance boost
// npm install pg-native
import pg from 'pg';
pg.native;  // This line enables native bindings

For Bun with postgres.js (Faster)

import postgres from 'postgres';

const sql = postgres({
  host: 'localhost',
  database: 'mydb',
  max: 20,
  idle_timeout: 30,
  connect_timeout: 10,
});

9. Type-Safe Queries

Schema Definition

import { pgTable, serial, text, boolean, timestamp } from 'drizzle-orm/pg-core';

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
  email: text('email').notNull().unique(),
  active: boolean('active').default(true),
  createdAt: timestamp('created_at').defaultNow(),
});

Infer Types from Schema

import { InferModel } from 'drizzle-orm';

type User = InferModel<typeof users>;
type NewUser = InferModel<typeof users, 'insert'>;

// Type-safe operations
const newUser: NewUser = {
  name: 'John',
  email: 'john@example.com',
  // TypeScript error if required fields missing
};

await db.insert(users).values(newUser);

10. Quick Reference

N+1 Prevention

// With relations
db.query.users.findMany({
  with: {
    posts: true,
    profile: true,
  },
});

// Explicit JOIN
db.select().from(users).leftJoin(posts, eq(users.id, posts.authorId));

ORM Comparison

┌─────────────────┬─────────────────────────────────────────────────┐
│ Feature         │ Drizzle                                         │
├─────────────────┼─────────────────────────────────────────────────┤
│ N+1 Prevention  │ with: {}                                        │
│ Partial Select  │ columns: {}                                     │
│ Bulk Insert     │ .values([])                                     │
│ Bulk Update     │ .set().where()                                  │
│ Prepared Stmt   │ .prepare()                                      │
│ Raw SQL         │ sql``                                           │
│ Connection Pool │ Pool config                                     │
│ Type Safety     │ Full                                            │
│ Bundle Size     │ ~7.4KB                                          │
└─────────────────┴─────────────────────────────────────────────────┘

Optimization Checklist

DRIZZLE PERFORMANCE:
├── [ ] Use prepared statements for repeated queries
├── [ ] Select only needed columns
├── [ ] Define relations to avoid N+1
├── [ ] Use batch inserts for multiple rows
├── [ ] Configure connection pool properly
├── [ ] Use transactions for multi-step operations
├── [ ] Add indexes on filtered/joined columns
├── [ ] Monitor slow queries
└── [ ] Use postgres.js with Bun for best performance

Query Time Budget

TARGET: 5ms per query

BREAKDOWN:
├── Connection acquisition:  0.1ms (from pool)
├── Query parsing:           0.1ms
├── Planning:                0.2ms
├── Execution:               4.0ms
├── Result transfer:         0.6ms
└── TOTAL:                   5.0ms