Drizzle ORM Query Building

db.select()
  .from(table)
  .execute()

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

db.select({
  id: users.id,
  name: users.name
})
  .from(users)
  .execute()

const userNames = await db.select({
  id: users.id,
  name: users.name
}).from(users);

// PostgreSQL and MySQL
db.selectDistinct()
  .from(table)
  .execute()

// PostgreSQL only
db.selectDistinctOn([column1, column2], fields?)
  .from(table)
  .execute()

// Get distinct user roles
const roles = await db.selectDistinct({ role: users.role })
  .from(users);

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

db.select()
  .from(table)
  .where(condition)
  .execute()

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

// Single condition
const activeUsers = await db.select()
  .from(users)
  .where(eq(users.active, true));

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

// Inner Join
db.select()
  .from(table1)
  .innerJoin(table2, condition)
  .execute()

// Left Join
db.select()
  .from(table1)
  .leftJoin(table2, condition)
  .execute()

// Right Join
db.select()
  .from(table1)
  .rightJoin(table2, condition)
  .execute()

// Full Join
db.select()
  .from(table1)
  .fullJoin(table2, condition)
  .execute()

import { eq } from 'drizzle-orm';

// Inner join
const usersWithOrders = await db.select()
  .from(users)
  .innerJoin(orders, eq(users.id, orders.userId));

// Left join
const allUsersWithOrders = await db.select()
  .from(users)
  .leftJoin(orders, eq(users.id, orders.userId));

// Multiple joins
const fullData = await db.select()
  .from(users)
  .innerJoin(orders, eq(users.id, orders.userId))
  .innerJoin(products, eq(orders.productId, products.id));

db.select()
  .from(table)
  .groupBy(column1, column2, ...)
  .execute()

import { count } from 'drizzle-orm';

const usersByRole = await db.select({
  role: users.role,
  count: count()
})
  .from(users)
  .groupBy(users.role);

db.select()
  .from(table)
  .groupBy(column)
  .having(condition)
  .execute()

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

const popularRoles = await db.select({
  role: users.role,
  count: count()
})
  .from(users)
  .groupBy(users.role)
  .having(gt(count(), 10));

import { asc, desc } from 'drizzle-orm';

db.select()
  .from(table)
  .orderBy(asc(column1), desc(column2))
  .execute()

import { asc, desc } from 'drizzle-orm';

// Single column ascending
const users1 = await db.select()
  .from(users)
  .orderBy(asc(users.name));

// Multiple columns
const users2 = await db.select()
  .from(users)
  .orderBy(desc(users.createdAt), asc(users.name));

// With vector distance (pgvector)
import { l2Distance } from 'drizzle-orm';

const similar = await db.select()
  .from(documents)
  .orderBy(l2Distance(documents.embedding, [0.1, 0.2, 0.3]));

db.select()
  .from(table)
  .limit(count)
  .execute()

// Get top 10 users
const topUsers = await db.select()
  .from(users)
  .limit(10);

db.select()
  .from(table)
  .offset(count)
  .execute()

// Pagination: skip first 20, get next 10
const page3 = await db.select()
  .from(users)
  .limit(10)
  .offset(20);

db.select()
  .from(table)
  .for('update' | 'no key update' | 'share' | 'key share', {
    noWait?: boolean,
    skipLocked?: boolean
  })
  .execute()

// FOR UPDATE
const locked = await db.select()
  .from(accounts)
  .where(eq(accounts.id, 1))
  .for('update');

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

db.insert(table)
  .values({
    column1: value1,
    column2: value2
  })
  .execute()

await db.insert(users).values({
  name: 'John Doe',
  email: 'john@example.com',
  age: 30
});

db.insert(table)
  .values([
    { column1: value1, column2: value2 },
    { column1: value3, column2: value4 }
  ])
  .execute()

await db.insert(users).values([
  { name: 'John', email: 'john@example.com' },
  { name: 'Jane', email: 'jane@example.com' },
  { name: 'Bob', email: 'bob@example.com' }
]);

db.insert(table)
  .values(data)
  .returning()
  .execute()

// Return specific fields
db.insert(table)
  .values(data)
  .returning({
    id: table.id,
    name: table.name
  })
  .execute()

// Return all columns
const [newUser] = await db.insert(users)
  .values({ name: 'John', email: 'john@example.com' })
  .returning();

// Return specific columns
const [result] = await db.insert(users)
  .values({ name: 'Jane', email: 'jane@example.com' })
  .returning({ id: users.id, name: users.name });

// Ignore all conflicts
db.insert(table)
  .values(data)
  .onConflictDoNothing()
  .execute()

// Ignore conflicts on specific target
db.insert(table)
  .values(data)
  .onConflictDoNothing({
    target: column
  })
  .execute()

// Ignore all conflicts
await db.insert(users)
  .values({ id: 1, name: 'John', email: 'john@example.com' })
  .onConflictDoNothing();

// Ignore conflicts on email column
await db.insert(users)
  .values({ name: 'John', email: 'john@example.com' })
  .onConflictDoNothing({ target: users.email });

db.insert(table)
  .values(data)
  .onConflictDoUpdate({
    target: column,
    set: {
      column1: value1,
      column2: sql`excluded.column2`
    },
    where?: condition
  })
  .execute()

import { sql } from 'drizzle-orm';

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

db.insert(table)
  .values(data)
  .onDuplicateKeyUpdate({
    set: {
      column1: sql`VALUES(column1)`,
      column2: value2
    }
  })
  .execute()

import { sql } from 'drizzle-orm';

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()
    }
  });

db.insert(table)
  .values(data)
  .ignore()
  .execute()

await db.insert(users)
  .values({ id: 1, name: 'John', email: 'john@example.com' })
  .ignore();

db.update(table)
  .set({
    column1: value1,
    column2: value2
  })
  .execute()

await db.update(users)
  .set({ active: false });

db.update(table)
  .set({ column: value })
  .where(condition)
  .execute()

import { eq } from 'drizzle-orm';

await db.update(users)
  .set({
    name: 'John Updated',
    updatedAt: new Date()
  })
  .where(eq(users.id, 1));

db.update(table)
  .set({ column: value })
  .where(condition)
  .returning()
  .execute()

// Return specific fields
db.update(table)
  .set({ column: value })
  .where(condition)
  .returning({
    id: table.id,
    name: table.name
  })
  .execute()

import { eq } from 'drizzle-orm';

// Return all columns
const updated = await db.update(users)
  .set({ name: 'John Updated' })
  .where(eq(users.id, 1))
  .returning();

// Return specific columns
const result = await db.update(users)
  .set({ active: false })
  .where(eq(users.id, 1))
  .returning({ id: users.id, active: users.active });

db.update(table1)
  .set({ column: value })
  .from(table2)
  .where(condition)
  .execute()

import { eq } from 'drizzle-orm';

await db.update(users)
  .set({ planName: sql`${plans.name}` })
  .from(plans)
  .where(eq(users.planId, plans.id));

db.delete(table)
  .execute()

await db.delete(users);

db.delete(table)
  .where(condition)
  .execute()

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

// Delete specific user
await db.delete(users)
  .where(eq(users.id, 1));

// Delete old records
await db.delete(logs)
  .where(lt(logs.createdAt, new Date('2023-01-01')));

db.delete(table)
  .where(condition)
  .returning()
  .execute()

// Return specific fields
db.delete(table)
  .where(condition)
  .returning({
    id: table.id,
    name: table.name
  })
  .execute()

import { eq } from 'drizzle-orm';

// Return all columns
const deleted = await db.delete(users)
  .where(eq(users.id, 1))
  .returning();

// Return specific columns
const result = await db.delete(users)
  .where(eq(users.active, false))
  .returning({ id: users.id, email: users.email });

db.$count(table)
  .execute()

const count = await db.$count(users);

db.$count(table, {
  where: condition
})
  .execute()

import { eq } from 'drizzle-orm';

const activeCount = await db.$count(users, {
  where: eq(users.active, true)
});

import {
  eq,    // Equal
  ne,    // Not equal
  gt,    // Greater than
  gte,   // Greater than or equal
  lt,    // Less than
  lte    // Less than or equal
} from 'drizzle-orm';

/**
 * Test equality between column and value.
 *
 * @param left - Column or SQL expression
 * @param right - Value to compare
 * @returns SQL condition
 */
eq(left: Column, right: value): SQL

/**
 * Test inequality between column and value.
 *
 * @param left - Column or SQL expression
 * @param right - Value to compare
 * @returns SQL condition
 */
ne(left: Column, right: value): SQL

/**
 * Test if left is greater than right.
 *
 * @param left - Column or SQL expression
 * @param right - Value to compare
 * @returns SQL condition
 */
gt(left: Column, right: value): SQL

/**
 * Test if left is greater than or equal to right.
 *
 * @param left - Column or SQL expression
 * @param right - Value to compare
 * @returns SQL condition
 */
gte(left: Column, right: value): SQL

/**
 * Test if left is less than right.
 *
 * @param left - Column or SQL expression
 * @param right - Value to compare
 * @returns SQL condition
 */
lt(left: Column, right: value): SQL

/**
 * Test if left is less than or equal to right.
 *
 * @param left - Column or SQL expression
 * @param right - Value to compare
 * @returns SQL condition
 */
lte(left: Column, right: value): SQL

import { eq, ne, gt, gte, lt, lte } from 'drizzle-orm';

// Equal
await db.select().from(users).where(eq(users.role, 'admin'));

// Not equal
await db.select().from(users).where(ne(users.status, 'deleted'));

// Greater than
await db.select().from(products).where(gt(products.price, 100));

// Greater than or equal
await db.select().from(users).where(gte(users.age, 18));

// Less than
await db.select().from(orders).where(lt(orders.total, 50));

// Less than or equal
await db.select().from(users).where(lte(users.loginAttempts, 3));

import { and, or, not } from 'drizzle-orm';

/**
 * Combine conditions with AND logic.
 * Undefined conditions are automatically filtered out.
 *
 * @param conditions - Array of SQL conditions
 * @returns Combined SQL condition
 */
and(...conditions: (SQL | undefined)[]): SQL | undefined

/**
 * Combine conditions with OR logic.
 * Undefined conditions are automatically filtered out.
 *
 * @param conditions - Array of SQL conditions
 * @returns Combined SQL condition
 */
or(...conditions: (SQL | undefined)[]): SQL | undefined

/**
 * Negate a condition with NOT.
 *
 * @param condition - SQL condition to negate
 * @returns Negated SQL condition
 */
not(condition: SQL): SQL

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

// AND
await db.select().from(users).where(
  and(
    eq(users.active, true),
    gt(users.age, 18),
    eq(users.verified, true)
  )
);

// OR
await db.select().from(users).where(
  or(
    eq(users.role, 'admin'),
    eq(users.role, 'moderator')
  )
);

// NOT
await db.select().from(users).where(
  not(eq(users.status, 'banned'))
);

// Complex combinations
await db.select().from(users).where(
  and(
    eq(users.active, true),
    or(
      eq(users.role, 'admin'),
      and(
        eq(users.role, 'user'),
        gt(users.karma, 100)
      )
    )
  )
);

import { isNull, isNotNull } from 'drizzle-orm';

/**
 * Test if column is NULL.
 *
 * @param column - Column to test
 * @returns SQL condition
 */
isNull(column: Column): SQL

/**
 * Test if column is NOT NULL.
 *
 * @param column - Column to test
 * @returns SQL condition
 */
isNotNull(column: Column): SQL

import { isNull, isNotNull } from 'drizzle-orm';

// IS NULL
await db.select().from(users).where(isNull(users.deletedAt));

// IS NOT NULL
await db.select().from(users).where(isNotNull(users.email));

import { inArray, notInArray } from 'drizzle-orm';

/**
 * Test if column value is in array.
 *
 * @param column - Column to test
 * @param values - Array of values or subquery
 * @returns SQL condition
 */
inArray(column: Column, values: any[] | Subquery): SQL

/**
 * Test if column value is not in array.
 *
 * @param column - Column to test
 * @param values - Array of values or subquery
 * @returns SQL condition
 */
notInArray(column: Column, values: any[] | Subquery): SQL

import { inArray, notInArray } from 'drizzle-orm';

// IN array
await db.select().from(users).where(
  inArray(users.role, ['admin', 'moderator', 'editor'])
);

// NOT IN array
await db.select().from(users).where(
  notInArray(users.status, ['deleted', 'banned', 'suspended'])
);

// IN subquery
await db.select().from(posts).where(
  inArray(
    posts.authorId,
    db.select({ id: users.id }).from(users).where(eq(users.active, true))
  )
);

import { exists, notExists } from 'drizzle-orm';

/**
 * Test if subquery returns any rows.
 *
 * @param subquery - Subquery to test
 * @returns SQL condition
 */
exists(subquery: Subquery): SQL

/**
 * Test if subquery returns no rows.
 *
 * @param subquery - Subquery to test
 * @returns SQL condition
 */
notExists(subquery: Subquery): SQL

import { exists, notExists, eq } from 'drizzle-orm';

// EXISTS
await db.select().from(users).where(
  exists(
    db.select()
      .from(orders)
      .where(eq(orders.userId, users.id))
  )
);

// NOT EXISTS
await db.select().from(users).where(
  notExists(
    db.select()
      .from(orders)
      .where(eq(orders.userId, users.id))
  )
);

import { between, notBetween } from 'drizzle-orm';

/**
 * Test if column is between min and max (inclusive).
 *
 * @param column - Column to test
 * @param min - Minimum value
 * @param max - Maximum value
 * @returns SQL condition
 */
between(column: Column, min: value, max: value): SQL

/**
 * Test if column is not between min and max.
 *
 * @param column - Column to test
 * @param min - Minimum value
 * @param max - Maximum value
 * @returns SQL condition
 */
notBetween(column: Column, min: value, max: value): SQL

import { between, notBetween } from 'drizzle-orm';

// BETWEEN
await db.select().from(products).where(
  between(products.price, 10, 100)
);

// NOT BETWEEN
await db.select().from(users).where(
  notBetween(users.age, 13, 17)
);

import { like, notLike, ilike, notIlike } from 'drizzle-orm';

/**
 * Case-sensitive pattern matching with wildcards.
 * Use % for zero or more characters, _ for single character.
 *
 * @param column - Column to match
 * @param pattern - Pattern with wildcards
 * @returns SQL condition
 */
like(column: Column, pattern: string): SQL

/**
 * Case-sensitive negative pattern matching.
 *
 * @param column - Column to match
 * @param pattern - Pattern with wildcards
 * @returns SQL condition
 */
notLike(column: Column, pattern: string): SQL

/**
 * Case-insensitive pattern matching with wildcards.
 * PostgreSQL only.
 *
 * @param column - Column to match
 * @param pattern - Pattern with wildcards
 * @returns SQL condition
 */
ilike(column: Column, pattern: string): SQL

/**
 * Case-insensitive negative pattern matching.
 * PostgreSQL only.
 *
 * @param column - Column to match
 * @param pattern - Pattern with wildcards
 * @returns SQL condition
 */
notIlike(column: Column, pattern: string): SQL

import { like, notLike, ilike, notIlike } from 'drizzle-orm';

// LIKE (case-sensitive)
await db.select().from(users).where(
  like(users.email, '%@gmail.com')
);

// NOT LIKE
await db.select().from(products).where(
  notLike(products.name, '%discontinued%')
);

// ILIKE (case-insensitive, PostgreSQL)
await db.select().from(users).where(
  ilike(users.name, '%john%')
);

// NOT ILIKE
await db.select().from(posts).where(
  notIlike(posts.title, '%draft%')
);

import {
  arrayContains,
  arrayContained,
  arrayOverlaps
} from 'drizzle-orm';

/**
 * Test if array column contains all elements of value array.
 * PostgreSQL only.
 *
 * @param column - Array column
 * @param values - Array of values to check
 * @returns SQL condition
 */
arrayContains(column: Column, values: any[]): SQL

/**
 * Test if value array contains all elements of array column.
 * PostgreSQL only.
 *
 * @param column - Array column
 * @param values - Array of values to check
 * @returns SQL condition
 */
arrayContained(column: Column, values: any[]): SQL

/**
 * Test if array column has any common elements with value array.
 * PostgreSQL only.
 *
 * @param column - Array column
 * @param values - Array of values to check
 * @returns SQL condition
 */
arrayOverlaps(column: Column, values: any[]): SQL

import { arrayContains, arrayContained, arrayOverlaps } from 'drizzle-orm';

// Array contains (column @> value)
await db.select().from(posts).where(
  arrayContains(posts.tags, ['typescript', 'orm'])
);

// Array contained (column <@ value)
await db.select().from(posts).where(
  arrayContained(posts.tags, ['typescript', 'orm', 'database'])
);

// Array overlaps (column && value)
await db.select().from(posts).where(
  arrayOverlaps(posts.tags, ['typescript', 'javascript'])
);

import {
  count,
  countDistinct,
  avg,
  avgDistinct,
  sum,
  sumDistinct,
  min,
  max
} from 'drizzle-orm';

/**
 * Count rows or non-null values.
 *
 * @param expression - Column to count, or omit to count all rows
 * @returns SQL returning number
 */
count(expression?: Column): SQL<number>

/**
 * Count distinct non-null values.
 *
 * @param expression - Column to count distinct values
 * @returns SQL returning number
 */
countDistinct(expression: Column): SQL<number>

/**
 * Calculate average of non-null values.
 *
 * @param expression - Column to average
 * @returns SQL returning string or null
 */
avg(expression: Column): SQL<string | null>

/**
 * Calculate average of distinct non-null values.
 *
 * @param expression - Column to average
 * @returns SQL returning string or null
 */
avgDistinct(expression: Column): SQL<string | null>

/**
 * Calculate sum of non-null values.
 *
 * @param expression - Column to sum
 * @returns SQL returning string or null
 */
sum(expression: Column): SQL<string | null>

/**
 * Calculate sum of distinct non-null values.
 *
 * @param expression - Column to sum
 * @returns SQL returning string or null
 */
sumDistinct(expression: Column): SQL<string | null>

/**
 * Find minimum value.
 *
 * @param expression - Column to find minimum
 * @returns SQL returning column type or null
 */
min(expression: Column): SQL<ColumnType | null>

/**
 * Find maximum value.
 *
 * @param expression - Column to find maximum
 * @returns SQL returning column type or null
 */
max(expression: Column): SQL<ColumnType | null>

import { count, countDistinct, avg, sum, min, max } from 'drizzle-orm';

// Count all rows
const totalUsers = await db.select({ count: count() })
  .from(users);

// Count non-null values
const verified = await db.select({ count: count(users.email) })
  .from(users);

// Count distinct
const uniqueRoles = await db.select({ count: countDistinct(users.role) })
  .from(users);

// Average
const avgSalary = await db.select({ avg: avg(employees.salary) })
  .from(employees);

// Average distinct
const avgDistinctSalary = await db.select({ avg: avgDistinct(employees.salary) })
  .from(employees);

// Sum
const totalRevenue = await db.select({ sum: sum(orders.total) })
  .from(orders);

// Sum distinct
const sumDistinct = await db.select({ sum: sumDistinct(orders.amount) })
  .from(orders);

// Min and Max
const priceRange = await db.select({
  min: min(products.price),
  max: max(products.price)
})
  .from(products);

// Group by with aggregates
const ordersByUser = await db.select({
  userId: orders.userId,
  totalOrders: count(),
  totalSpent: sum(orders.total),
  avgOrderValue: avg(orders.total)
})
  .from(orders)
  .groupBy(orders.userId);

import {
  l2Distance,
  l1Distance,
  innerProduct,
  cosineDistance,
  hammingDistance,
  jaccardDistance
} from 'drizzle-orm';

/**
 * Calculate L2 (Euclidean) distance between vectors.
 * Used for pgvector extension.
 *
 * @param column - Vector column
 * @param value - Vector to compare (array, string, or subquery)
 * @returns SQL for distance calculation
 */
l2Distance(
  column: Column,
  value: number[] | string[] | string | Subquery
): SQL

/**
 * Calculate L1 (Manhattan) distance between vectors.
 *
 * @param column - Vector column
 * @param value - Vector to compare
 * @returns SQL for distance calculation
 */
l1Distance(
  column: Column,
  value: number[] | string[] | string | Subquery
): SQL

/**
 * Calculate inner product distance between vectors.
 *
 * @param column - Vector column
 * @param value - Vector to compare
 * @returns SQL for distance calculation
 */
innerProduct(
  column: Column,
  value: number[] | string[] | string | Subquery
): SQL

/**
 * Calculate cosine distance between vectors.
 *
 * @param column - Vector column
 * @param value - Vector to compare
 * @returns SQL for distance calculation
 */
cosineDistance(
  column: Column,
  value: number[] | string[] | string | Subquery
): SQL

/**
 * Calculate Hamming distance between vectors.
 *
 * @param column - Vector column
 * @param value - Vector to compare
 * @returns SQL for distance calculation
 */
hammingDistance(
  column: Column,
  value: number[] | string[] | string | Subquery
): SQL

/**
 * Calculate Jaccard distance between vectors.
 *
 * @param column - Vector column
 * @param value - Vector to compare
 * @returns SQL for distance calculation
 */
jaccardDistance(
  column: Column,
  value: number[] | string[] | string | Subquery
): SQL

import { l2Distance, cosineDistance } from 'drizzle-orm';

// Find similar documents by L2 distance
const embedding = [0.1, 0.2, 0.3, /* ... */];
const similar = await db.select({
  id: documents.id,
  title: documents.title,
  distance: l2Distance(documents.embedding, embedding)
})
  .from(documents)
  .orderBy(l2Distance(documents.embedding, embedding))
  .limit(10);

// Find similar using cosine distance
const semanticSearch = await db.select()
  .from(documents)
  .orderBy(cosineDistance(documents.embedding, embedding))
  .limit(5);

// Vector distance in WHERE clause
const threshold = 0.5;
const closeMatches = await db.select()
  .from(documents)
  .where(
    sql`${l2Distance(documents.embedding, embedding)} < ${threshold}`
  );

import { sql } from 'drizzle-orm';

/**
 * Create SQL query from template literal.
 * Automatically handles parameter binding and escaping.
 *
 * @param strings - Template strings
 * @param params - Parameters to interpolate
 * @returns SQL object
 */
sql<T = unknown>`template string`

import { sql } from 'drizzle-orm';

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

// SQL in WHERE clause
await db.select().from(users).where(
  sql`${users.age} > 18 AND ${users.active} = true`
);

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

// Complex expressions
await db.select({
  name: users.name,
  jsonData: sql<any>`json_build_object('name', ${users.name}, 'age', ${users.age})`
})
  .from(users);

import { sql } from 'drizzle-orm';

/**
 * Create SQL from raw string without parameter binding.
 * WARNING: Vulnerable to SQL injection - validate input first.
 *
 * @param str - Raw SQL string
 * @returns SQL object
 */
sql.raw(str: string): SQL

import { sql } from 'drizzle-orm';

// Raw SQL fragment (be careful with user input!)
const tableName = 'users'; // Must be trusted/validated
const result = await db.select().from(sql.raw(tableName));

// Raw SQL in expression
await db.select({
  currentTime: sql.raw('NOW()')
})
  .from(users);

import { sql } from 'drizzle-orm';

/**
 * Join SQL chunks with optional separator.
 *
 * @param chunks - Array of SQL chunks to join
 * @param separator - Optional separator between chunks
 * @returns Combined SQL
 */
sql.join(chunks: SQL[], separator?: SQL): SQL

import { sql } from 'drizzle-orm';

// Join with comma separator
const columns = [users.name, users.email, users.age];
const columnList = sql.join(columns, sql`, `);

// Join conditions with OR
const conditions = [
  eq(users.role, 'admin'),
  eq(users.role, 'moderator')
];
await db.select().from(users).where(
  sql.join(conditions, sql` OR `)
);

// Dynamic column selection
const selectColumns = ['name', 'email'];
const dynamicSelect = sql.join(
  selectColumns.map(col => sql.identifier(col)),
  sql`, `
);

import { sql } from 'drizzle-orm';

/**
 * Create escaped database identifier (table, column, index name).
 * WARNING: Does not protect against SQL injection - validate input.
 *
 * @param name - Identifier name
 * @returns SQL identifier
 */
sql.identifier(name: string): SQL

import { sql } from 'drizzle-orm';

// Dynamic table name (must be validated)
const tableName = 'users';
await db.select().from(sql.identifier(tableName));

// Dynamic column name
const columnName = 'email';
await db.select({
  value: sql`${sql.identifier(columnName)}`
})
  .from(users);

// Fully qualified name
const schema = 'public';
const table = 'users';
await db.select().from(
  sql`${sql.identifier(schema)}.${sql.identifier(table)}`
);

import { sql } from 'drizzle-orm';

/**
 * Create named placeholder for prepared statements.
 *
 * @param name - Placeholder name
 * @returns Placeholder object
 */
sql.placeholder<TName extends string>(name: TName): Placeholder<TName>

import { sql } from 'drizzle-orm';

// Named placeholder
const prepared = db.select()
  .from(users)
  .where(eq(users.id, sql.placeholder('userId')))
  .prepare('getUserById');

await prepared.execute({ userId: 1 });
await prepared.execute({ userId: 2 });

// Multiple placeholders
const query = db.select()
  .from(users)
  .where(
    and(
      eq(users.role, sql.placeholder('role')),
      gt(users.age, sql.placeholder('minAge'))
    )
  )
  .prepare();

await query.execute({ role: 'admin', minAge: 18 });

import { sql } from 'drizzle-orm';

/**
 * Create parameter with optional encoder.
 *
 * @param value - Parameter value
 * @param encoder - Optional value encoder
 * @returns Parameter object
 */
sql.param<TData, TDriver>(
  value: TData,
  encoder?: DriverValueEncoder<TData, TDriver>
): Param<TData, TDriver>

import { sql } from 'drizzle-orm';

// Explicit parameter
const value = 'test@example.com';
await db.select().from(users).where(
  eq(users.email, sql.param(value))
);

import { sql } from 'drizzle-orm';

/**
 * Create empty SQL object.
 *
 * @returns Empty SQL
 */
sql.empty(): SQL

import { sql } from 'drizzle-orm';

// Conditional SQL building
const whereClause = condition
  ? sql`WHERE ${users.active} = true`
  : sql.empty();

/**
 * Alias an SQL expression.
 *
 * @param alias - Alias name
 * @returns Aliased SQL
 */
sql`...`.as(alias: string): SQL.Aliased

/**
 * Map result with decoder or function.
 *
 * @param decoder - Decoder or mapping function
 * @returns Mapped SQL
 */
sql`...`.mapWith(decoder: Decoder | Function): SQL

/**
 * Mark parameters to be inlined in query.
 *
 * @returns SQL with inline params
 */
sql`...`.inlineParams(): SQL

/**
 * Conditionally include SQL fragment.
 *
 * @param condition - Condition to check
 * @returns SQL if condition is truthy, undefined otherwise
 */
sql`...`.if(condition: any): SQL | undefined

import { sql } from 'drizzle-orm';

// Alias SQL expression
const upperName = sql<string>`upper(${users.name})`.as('upperName');
await db.select({ upperName }).from(users);

// Map with custom decoder
const customValue = sql`some_function(${users.value})`
  .mapWith((value) => parseCustomFormat(value));

// Inline params (for debugging or special cases)
const inlined = sql`SELECT * FROM users WHERE id = ${1}`.inlineParams();

// Conditional SQL
const orderClause = sortBy
  ? sql`ORDER BY ${sql.identifier(sortBy)}`.if(sortBy)
  : undefined;

/**
 * Use any SELECT query as a subquery by calling .as(alias).
 */
db.select()
  .from(table)
  .where(condition)
  .as(alias: string)

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

// Subquery in FROM
const activeUsers = db.select()
  .from(users)
  .where(eq(users.active, true))
  .as('activeUsers');

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

// Subquery in JOIN
const latestOrders = db.select({
  userId: orders.userId,
  maxDate: max(orders.createdAt).as('maxDate')
})
  .from(orders)
  .groupBy(orders.userId)
  .as('latestOrders');

await db.select()
  .from(users)
  .leftJoin(
    latestOrders,
    eq(users.id, latestOrders.userId)
  );

// Subquery in WHERE (with EXISTS)
await db.select().from(users).where(
  exists(
    db.select().from(orders).where(eq(orders.userId, users.id))
  )
);

// Subquery in WHERE (with IN)
await db.select().from(posts).where(
  inArray(
    posts.authorId,
    db.select({ id: users.id }).from(users).where(eq(users.verified, true))
  )
);

// Scalar subquery in SELECT
await db.select({
  name: users.name,
  orderCount: db.select({ count: count() })
    .from(orders)
    .where(eq(orders.userId, users.id))
    .as('orderCount')
})
  .from(users);

/**
 * Create a CTE (Common Table Expression) with db.$with().
 *
 * @param name - CTE name
 * @returns CTE builder
 */
db.$with(name: string)
  .as(query: SelectQuery)

// Use CTE in main query
db.with(cte1, cte2, ...)
  .select()
  .from(cte)

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

// Basic CTE
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));

// Multiple CTEs
const userOrders = db.$with('userOrders').as(
  db.select({
    userId: orders.userId,
    orderCount: count().as('orderCount')
  })
    .from(orders)
    .groupBy(orders.userId)
);

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

await db.with(userOrders, activeUsers)
  .select({
    name: activeUsers.name,
    orderCount: userOrders.orderCount
  })
  .from(activeUsers)
  .leftJoin(userOrders, eq(activeUsers.id, userOrders.userId));

// Recursive CTE (PostgreSQL)
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);

/**
 * Combine results from two queries, removing duplicates.
 * All SELECT queries must have the same structure.
 *
 * @param query - Another SELECT query to union with
 * @returns Combined query results (distinct rows)
 */
db.select()
  .from(table1)
  .union(
    db.select().from(table2)
  )
  .execute()

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

// Method 1: Chainable syntax
const activeUsers = await db.select({ id: users.id, name: users.name })
  .from(users)
  .where(eq(users.status, 'active'))
  .union(
    db.select({ id: admins.id, name: admins.name })
      .from(admins)
  );

// Method 2: Function syntax
const allUsers = await union(
  db.select({ id: users.id, email: users.email }).from(users),
  db.select({ id: admins.id, email: admins.email }).from(admins)
);

/**
 * Combine results from two queries, including duplicates.
 *
 * @param query - Another SELECT query to union with
 * @returns Combined query results (including duplicates)
 */
db.select()
  .from(table1)
  .unionAll(
    db.select().from(table2)
  )
  .execute()

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

// Include duplicates (faster than UNION)
const allRecords = await db.select()
  .from(currentYearData)
  .unionAll(
    db.select().from(archiveData)
  );

// Function syntax
const combined = await unionAll(
  db.select().from(table1),
  db.select().from(table2),
  db.select().from(table3)
);

/**
 * Return only rows that appear in both queries (distinct).
 *
 * @param query - Another SELECT query to intersect with
 * @returns Rows present in both queries
 */
db.select()
  .from(table1)
  .intersect(
    db.select().from(table2)
  )
  .execute()

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

// Find users who are both customers and employees
const dualRole = await db.select({ id: customers.userId })
  .from(customers)
  .intersect(
    db.select({ id: employees.userId }).from(employees)
  );

// Function syntax
const common = await intersect(
  db.select({ email: users.email }).from(users).where(eq(users.verified, true)),
  db.select({ email: subscribers.email }).from(subscribers).where(eq(subscribers.active, true))
);

/**
 * Return rows that appear in both queries, including duplicates.
 *
 * @param query - Another SELECT query to intersect with
 * @returns Rows present in both queries (with duplicates)
 */
db.select()
  .from(table1)
  .intersectAll(
    db.select().from(table2)
  )
  .execute()

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

// Include duplicate matches
const results = await db.select()
  .from(orders2023)
  .intersectAll(
    db.select().from(refunds2023)
  );

/**
 * Return rows from the first query that are not in the second query (distinct).
 *
 * @param query - SELECT query whose results to exclude
 * @returns Rows from first query not in second query
 */
db.select()
  .from(table1)
  .except(
    db.select().from(table2)
  )
  .execute()

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

// Find users who have never made a purchase
const nonBuyers = await db.select({ id: users.id, email: users.email })
  .from(users)
  .except(
    db.select({ id: orders.userId, email: users.email })
      .from(orders)
      .innerJoin(users, eq(orders.userId, users.id))
  );

// Function syntax
const difference = await except(
  db.select({ id: allProducts.id }).from(allProducts),
  db.select({ id: soldProducts.productId }).from(soldProducts)
);

/**
 * Return rows from the first query that are not in the second query, including duplicates.
 *
 * @param query - SELECT query whose results to exclude
 * @returns Rows from first query not in second query (with duplicates)
 */
db.select()
  .from(table1)
  .exceptAll(
    db.select().from(table2)
  )
  .execute()

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

// Include duplicate differences
const results = await db.select()
  .from(inventory)
  .exceptAll(
    db.select().from(shipped)
  );

// Set operations can be combined with ORDER BY and LIMIT
const results = await db.select({ name: users.name })
  .from(users)
  .union(
    db.select({ name: admins.name }).from(admins)
  )
  .orderBy(asc(users.name))
  .limit(10);

// Chain multiple set operations
const results = await db.select()
  .from(table1)
  .union(
    db.select().from(table2)
  )
  .union(
    db.select().from(table3)
  )
  .except(
    db.select().from(excludeTable)
  );

import { aliasedTable } from 'drizzle-orm';

/**
 * Create an aliased reference to a table.
 *
 * @param table - Original table
 * @param alias - Alias name
 * @returns Aliased table reference
 */
aliasedTable<T extends Table>(
  table: T,
  alias: string
): T

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

// Self-join with aliases
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));

// Multiple joins to same table
const author = aliasedTable(users, 'author');
const editor = aliasedTable(users, 'editor');

await db.select({
  postTitle: posts.title,
  authorName: author.name,
  editorName: editor.name
})
  .from(posts)
  .leftJoin(author, eq(posts.authorId, author.id))
  .leftJoin(editor, eq(posts.editorId, editor.id));

import { aliasedRelation } from 'drizzle-orm';

/**
 * Create an aliased reference to a relation (for relational queries).
 *
 * @param relation - Original relation
 * @param alias - Alias name
 * @returns Aliased relation reference
 */
aliasedRelation<T extends Relation>(
  relation: T,
  alias: string
): T

import { aliasedRelation } from 'drizzle-orm';

// Used in relational queries when you need to join the same relation multiple times
const authorRelation = aliasedRelation(postsRelations.author, 'author');
const editorRelation = aliasedRelation(postsRelations.editor, 'editor');

import { aliasedTableColumn } from 'drizzle-orm';

/**
 * Create an aliased reference to a specific column.
 *
 * @param column - Original column
 * @param tableAlias - Table alias name
 * @returns Aliased column reference
 */
aliasedTableColumn<T extends Column>(
  column: T,
  tableAlias: string
): T

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

// Alias specific columns
const managerName = aliasedTableColumn(users.name, 'manager');
const employeeName = aliasedTableColumn(users.name, 'employee');

/**
 * Create a prepared statement from any query.
 * Prepared statements are parsed once and can be executed multiple times
 * with different parameters for better performance.
 *
 * @param name - Optional statement name
 * @returns Prepared statement
 */
db.select()
  .from(table)
  .where(condition)
  .prepare(name?: string)

/**
 * Execute a prepared statement.
 *
 * @param params - Parameter values (for placeholder statements)
 * @returns Query results
 */
preparedStatement.execute(params?: object)

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

// Simple prepared statement
const prepared = db.select()
  .from(users)
  .where(eq(users.id, sql.placeholder('id')))
  .prepare('getUserById');

await prepared.execute({ id: 1 });
await prepared.execute({ id: 2 });

// INSERT prepared statement
const insertPrepared = db.insert(users)
  .values({
    name: sql.placeholder('name'),
    email: sql.placeholder('email'),
    age: sql.placeholder('age')
  })
  .prepare();

await insertPrepared.execute({
  name: 'John',
  email: 'john@example.com',
  age: 30
});

// UPDATE prepared statement
const updatePrepared = db.update(users)
  .set({
    name: sql.placeholder('name'),
    updatedAt: sql.placeholder('updatedAt')
  })
  .where(eq(users.id, sql.placeholder('id')))
  .prepare();

await updatePrepared.execute({
  id: 1,
  name: 'John Updated',
  updatedAt: new Date()
});

// DELETE prepared statement
const deletePrepared = db.delete(users)
  .where(eq(users.id, sql.placeholder('id')))
  .prepare();

await deletePrepared.execute({ id: 1 });

// Complex prepared statement with multiple placeholders
const complexPrepared = db.select()
  .from(users)
  .where(
    and(
      eq(users.role, sql.placeholder('role')),
      gt(users.age, sql.placeholder('minAge')),
      lt(users.age, sql.placeholder('maxAge'))
    )
  )
  .limit(sql.placeholder('limit'))
  .prepare();

await complexPrepared.execute({
  role: 'admin',
  minAge: 18,
  maxAge: 65,
  limit: 10
});

/**
 * Execute raw SQL query.
 *
 * @param query - SQL query (string or sql template)
 * @returns Query results
 */
db.execute(query: string | SQL): Promise<any>

import { sql } from 'drizzle-orm';

// Execute raw SQL string
await db.execute('SELECT * FROM users WHERE active = true');

// Execute with sql template (safer, with parameter binding)
const role = 'admin';
await db.execute(sql`SELECT * FROM users WHERE role = ${role}`);

// Complex raw query
const result = await db.execute(sql`
  WITH ranked_users AS (
    SELECT
      *,
      ROW_NUMBER() OVER (PARTITION BY role ORDER BY created_at DESC) as rn
    FROM users
  )
  SELECT * FROM ranked_users WHERE rn = 1
`);

/**
 * Execute statement and return metadata (SQLite only).
 *
 * @param query - SQL query
 * @returns Object with changes and lastInsertRowid
 */
db.run(query: string | SQL): Promise<{
  changes: number;
  lastInsertRowid: number;
}>

/**
 * Fetch all rows (SQLite only).
 *
 * @param query - SQL query
 * @returns Array of rows
 */
db.all(query: string | SQL): Promise<any[]>

/**
 * Fetch single row (SQLite only).
 *
 * @param query - SQL query
 * @returns Single row or undefined
 */
db.get(query: string | SQL): Promise<any | undefined>

/**
 * Fetch raw values array (SQLite only).
 *
 * @param query - SQL query
 * @returns Array of value arrays
 */
db.values(query: string | SQL): Promise<any[][]>

import { sql } from 'drizzle-orm';

// Run (for INSERT, UPDATE, DELETE)
const result = await db.run(
  sql`INSERT INTO users (name, email) VALUES ('John', 'john@example.com')`
);
console.log('Changes:', result.changes);
console.log('Last ID:', result.lastInsertRowid);

// All
const allUsers = await db.all(sql`SELECT * FROM users WHERE active = 1`);

// Get single row
const user = await db.get(sql`SELECT * FROM users WHERE id = ${1}`);

// Values (raw arrays)
const values = await db.values(sql`SELECT name, email FROM users`);
// [[John', 'john@example.com'], ['Jane', 'jane@example.com']]

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;

import { eq } from 'drizzle-orm';

// Offset-based pagination
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);
}

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

/**
 * Execute multiple queries in a single batch (driver-specific).
 *
 * @param queries - Array of query builders
 * @returns Array of results
 */
db.batch([query1, query2, query3])

// 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))
]);

/**
 * Execute queries within a transaction.
 * Automatically commits on success, rolls back on error.
 *
 * @param callback - Transaction callback receiving transaction object
 * @param config - Optional transaction configuration
 * @returns Callback return value
 */
db.transaction<T>(
  callback: (tx) => Promise<T>,
  config?: {
    isolationLevel?: 'read uncommitted' | 'read committed'
                   | 'repeatable read' | 'serializable';
  }
): Promise<T>

import { eq } from 'drizzle-orm';

// Basic transaction
await db.transaction(async (tx) => {
  await tx.insert(users).values({ name: 'John' });
  await tx.insert(posts).values({ authorId: 1, title: 'Hello' });
});

// Transaction with rollback
try {
  await db.transaction(async (tx) => {
    await tx.update(accounts)
      .set({ balance: sql`balance - 100` })
      .where(eq(accounts.id, 1));

    await tx.update(accounts)
      .set({ balance: sql`balance + 100` })
      .where(eq(accounts.id, 2));
  });
} catch (error) {
  console.error('Transaction failed:', error);
}

// Explicit rollback
await db.transaction(async (tx) => {
  const user = await tx.select().from(users).where(eq(users.id, 1));

  if (!user) {
    tx.rollback(); // Throws TransactionRollbackError
  }

  await tx.update(users).set({ lastLogin: new Date() });
});