Select Queries

Learn how to query data with Drizzle ORM’s type-safe select query builder, including filtering, ordering, limiting, and advanced select operations.

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:

Basic Filtering

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:

Basic Distinct

// 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:

Union

// 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