Skip to main content
Drizzle ORM provides a powerful and type-safe query builder for selecting data from your database. The select query builder supports a wide range of operations including filtering, joining, grouping, and ordering.

​
Basic Select

Select all columns from a table: 
import { db } from './db';
import { users } from './schema';

// Select all columns from users
const allUsers = await db.select().from(users);

​
Partial Select

Select specific columns from a table: 
// Select only id and name
const userNames = await db.select({
  id: users.id,
  name: users.name,
}).from(users);

​
Filtering with Where

Use the where() method to filter results: 
import { eq, gt, lt } from 'drizzle-orm';

// Select users with a specific id
const user = await db.select()
  .from(users)
  .where(eq(users.id, 1));

// Select users older than 18
const adults = await db.select()
  .from(users)
  .where(gt(users.age, 18));

​
Ordering Results

Use orderBy() to sort query results: 
import { asc, desc } from 'drizzle-orm';

// Order by name ascending
const users = await db.select()
  .from(users)
  .orderBy(asc(users.name));

// Order by created date descending
const recentUsers = await db.select()
  .from(users)
  .orderBy(desc(users.createdAt));

// Multiple order by clauses
const sortedUsers = await db.select()
  .from(users)
  .orderBy(asc(users.role), desc(users.createdAt));

​
Limiting and Offsetting

Control the number of results returned: 
// Get first 10 users
const firstTen = await db.select()
  .from(users)
  .limit(10);

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

​
Distinct

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

​
Group By

Group results by one or more columns: 
import { count } from 'drizzle-orm';

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

​
Having Clause

Filter grouped results: 
import { count, gt } from 'drizzle-orm';

// Get roles with more than 5 users
const popularRoles = await db.select({
  role: users.role,
  count: count(),
})
  .from(users)
  .groupBy(users.role)
  .having(({ count }) => gt(count, 5));

​
Set Operations

Combine multiple select queries: 
// Combine results and remove duplicates
const combined = await db.select({ name: users.name })
  .from(users)
  .union(
    db.select({ name: customers.name }).from(customers)
  );

​
Conditional Where

Build dynamic queries with conditional logic: 
const filters = {
  role: 'admin',
  minAge: 18,
};

const conditions = [];
if (filters.role) {
  conditions.push(eq(users.role, filters.role));
}
if (filters.minAge) {
  conditions.push(gt(users.age, filters.minAge));
}

const result = await db.select()
  .from(users)
  .where(and(...conditions));

​
With Clause (CTE)

Use Common Table Expressions: 
const activeUsers = db.$with('active_users').as(
  db.select().from(users).where(eq(users.active, true))
);

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

​
For Update (PostgreSQL)

Lock rows for update: 
// Lock selected rows
const user = await db.select()
  .from(users)
  .where(eq(users.id, 1))
  .for('update');

// Lock with NOWAIT
const user = await db.select()
  .from(users)
  .where(eq(users.id, 1))
  .for('update', { noWait: true });

// Lock specific tables
const result = await db.select()
  .from(users)
  .leftJoin(posts, eq(users.id, posts.userId))
  .for('update', { of: users });

​
Conditional Operators Reference

Comparison

  • eq() - Equal to
  • ne() - Not equal to
  • gt() - Greater than
  • gte() - Greater than or equal
  • lt() - Less than
  • lte() - Less than or equal

Logical

  • and() - Combine with AND
  • or() - Combine with OR
  • not() - Negate condition

Patterns

  • like() - Pattern matching
  • ilike() - Case-insensitive pattern
  • notLike() - Not matching pattern
  • notIlike() - Case-insensitive not matching

Ranges & Sets

  • between() - Between two values
  • notBetween() - Not between values
  • inArray() - In array of values
  • notInArray() - Not in array

Null Checks

  • isNull() - Is NULL
  • isNotNull() - Is NOT NULL

Subqueries

  • exists() - Subquery has results
  • notExists() - Subquery has no results

​
Type Safety

Drizzle ensures complete type safety: 
// TypeScript knows the exact shape of the result
const users = await db.select({
  id: users.id,
  name: users.name,
}).from(users);

// users is typed as: { id: number; name: string; }[]
All query methods are fully typed, providing autocomplete and compile-time type checking.

​
Next Steps

Joins

Learn about joining tables together

Aggregations

Use aggregate functions like count, sum, avg

Subqueries

Write complex nested queries

Insert

Insert data into tables

Build docs developers (and LLMs) love

Get started for freeTalk to us