Edge Cases and Advanced Scenarios

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

// Build query conditionally
const filters: SQL[] = [];

if (nameFilter) {
  filters.push(eq(users.name, nameFilter));
}

if (ageFilter) {
  filters.push(sql`${users.age} >= ${ageFilter}`);
}

if (roleFilter) {
  filters.push(eq(users.role, roleFilter));
}

const query = db.select().from(users);

if (filters.length > 0) {
  query.where(and(...filters));
}

const results = await query;

async function getUsers(page: number, pageSize: number) {
  return await db.select()
    .from(users)
    .where(eq(users.active, true))
    .limit(pageSize)
    .offset(page * pageSize)
    .orderBy(users.createdAt);
}

async function getUsersAfterCursor(cursor: number, limit: number) {
  return await db.select()
    .from(users)
    .where(gt(users.id, cursor))
    .limit(limit)
    .orderBy(users.id);
}

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

  // Nested transaction (savepoint)
  await tx.transaction(async (tx2) => {
    await tx2.insert(posts).values({ title: 'Post 1', userId: 1 });
    await tx2.insert(posts).values({ title: 'Post 2', userId: 1 });
  });

  // If nested transaction fails, only nested operations are rolled back
  await tx.insert(comments).values({ text: 'Comment', postId: 1 });
});

// Serializable isolation for critical operations
await db.transaction(async (tx) => {
  // Your operations
}, {
  isolationLevel: 'serializable',
  accessMode: 'read write',
  deferrable: true
});

import { sql } from 'drizzle-orm';

// Prepare a query for repeated execution
const getUserById = db
  .select()
  .from(users)
  .where(eq(users.id, sql.placeholder('id')))
  .prepare('get_user_by_id');

// Execute multiple times efficiently
const user1 = await getUserById.execute({ id: 1 });
const user2 = await getUserById.execute({ id: 2 });
const user3 = await getUserById.execute({ id: 3 });

// Execute multiple queries efficiently
const [users, posts, comments] = await db.batch([
  db.select().from(users).where(eq(users.active, true)),
  db.select().from(posts).limit(10),
  db.select().from(comments).orderBy(desc(comments.createdAt))
]);

import { aliasedTable, eq } from 'drizzle-orm';

const managers = aliasedTable(users, 'managers');
const employees = aliasedTable(users, 'employees');

const result = await db.select({
  employeeName: employees.name,
  managerName: managers.name
})
.from(employees)
.leftJoin(managers, eq(employees.managerId, managers.id));

const result = await db.select()
  .from(users)
  .innerJoin(orders, eq(users.id, orders.userId))
  .innerJoin(products, eq(orders.productId, products.id))
  .where(and(
    eq(users.active, true),
    gt(orders.total, 100)
  ));

import { eq, gt } from 'drizzle-orm';

const activeUsers = db.$with('activeUsers').as(
  db.select().from(users).where(eq(users.active, true))
);

const result = await db.with(activeUsers)
  .select()
  .from(activeUsers)
  .where(gt(activeUsers.age, 18));

const subordinates = db.$with('subordinates').as(
  db.select({
    id: employees.id,
    name: employees.name,
    managerId: employees.managerId,
    level: sql<number>`1`.as('level')
  })
    .from(employees)
    .where(eq(employees.managerId, 1))
    .unionAll(
      db.select({
        id: employees.id,
        name: employees.name,
        managerId: employees.managerId,
        level: sql<number>`${subordinates.level} + 1`
      })
        .from(employees)
        .innerJoin(subordinates, eq(employees.managerId, subordinates.id))
    )
);

const result = await db.with(subordinates)
  .select()
  .from(subordinates);

import { sql } from 'drizzle-orm';

// Type-safe raw SQL
const result = await db.select({
  id: users.id,
  name: users.name,
  upperName: sql<string>`upper(${users.name})`
})
.from(users);

// Complex raw SQL with subqueries
const avgPrice = sql`(SELECT AVG(price) FROM ${products})`;
await db.select({
  name: products.name,
  priceVsAvg: sql`${products.price} - ${avgPrice}`
})
.from(products);

// Get first occurrence of each role (ordered)
const firstUsers = await db.selectDistinctOn([users.role])
  .from(users)
  .orderBy(users.role, users.createdAt);

// FOR UPDATE SKIP LOCKED - useful for job queues
const available = await db.select()
  .from(jobs)
  .where(eq(jobs.status, 'pending'))
  .limit(1)
  .for('update', { skipLocked: true });

await db.insert(users)
  .values({ email: 'john@example.com', name: 'John', loginCount: 1 })
  .onDuplicateKeyUpdate({
    set: {
      name: sql`VALUES(name)`,
      loginCount: sql`loginCount + 1`,
      updatedAt: new Date()
    }
  });

await db.insert(users)
  .values({ id: 1, name: 'John', email: 'john@example.com' })
  .onConflictDoUpdate({
    target: users.id,
    set: {
      name: sql`excluded.name`,
      updatedAt: new Date()
    }
  });

import { DrizzleQueryError, TransactionRollbackError } from 'drizzle-orm';

try {
  await db.transaction(async (tx) => {
    await tx.insert(users).values({ name: 'John', email: 'john@example.com' });
    await tx.insert(users).values({ name: 'Jane', email: 'john@example.com' }); // Duplicate
  });
} catch (error) {
  if (error instanceof TransactionRollbackError) {
    console.log('Transaction was explicitly rolled back');
  } else if (error instanceof DrizzleQueryError) {
    console.log('Query failed:', error.query);
    console.log('Params:', error.params);
    console.log('Cause:', error.cause);
  } else {
    console.log('Unexpected error:', error);
  }
}

import { withReplicas } from 'drizzle-orm/node-postgres';

const replica1 = new Pool({ connectionString: process.env.REPLICA1_URL });
const replica2 = new Pool({ connectionString: process.env.REPLICA2_URL });

const db = withReplicas(masterPool, [replica1, replica2]);

// Reads automatically go to replicas
const users = await db.select().from(users);

// Writes go to master
await db.insert(users).values({ name: 'John' });

import { UpstashCache } from 'drizzle-orm/upstash';

const cache = new UpstashCache({
  url: process.env.UPSTASH_REDIS_URL,
  token: process.env.UPSTASH_REDIS_TOKEN,
});

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

// Queries are automatically cached
const users = await db.select().from(users);

import { customType } from 'drizzle-orm/pg-core';

// Custom type for currency stored as integer (cents)
const currency = customType<{
  data: number;
  driverData: number;
}>({
  dataType() {
    return 'integer';
  },
  toDriver(value: number): number {
    return Math.round(value * 100);
  },
  fromDriver(value: number): number {
    return value / 100;
  },
});

export const products = pgTable('products', {
  id: integer('id').primaryKey(),
  price: currency('price').notNull(),
});

// Usage
await db.insert(products).values({ id: 1, price: 19.99 });  // Stores 1999
const product = await db.select().from(products).where(eq(products.id, 1));
console.log(product[0].price);  // Returns 19.99

import { sql } from 'drizzle-orm';

export const users = pgTable('users', {
  id: integer('id').primaryKey(),
  firstName: text('first_name').notNull(),
  lastName: text('last_name').notNull(),
  fullName: text('full_name').generatedAlwaysAs(
    sql`first_name || ' ' || last_name`
  ),
});

export const users = pgTable('users', {
  id: integer('id').generatedAlwaysAsIdentity({ startWith: 1000 }),
  name: text('name').notNull(),
});

export const posts = pgTable('posts', {
  id: integer('id').generatedByDefaultAsIdentity(),  // Can override on insert
  title: text('title').notNull(),
});