Overview
Querying records allows you to select, filter, sort, join, and aggregate data from your database using a fluent, SQL-like syntax directly in TypeScript. Drizzle provides powerful query builders that give you full control over how you retrieve and shape your data while maintaining complete type safety.
Note
Casting expressions to specific types (such as numbers or dates) ensures your query results match your expected TypeScript types.
Selecting Data
You can fetch all columns from a table or specify exactly which columns and custom expressions you want to include in your result set.
// Select all columns and all rows from the 'cars' table
const allCars = await db.select().from(cars);
// Select specific columns from the 'cars' table
const carsIdsAndBrands = await db.select({
id: cars.id,
brand: cars.brand
}).from(cars);
// Select specific columns along with an expression
const carsIdsAndLowerNames = await db.select({
id: cars.id,
lowerBrand: sql<string>`lower(${cars.brand})`,
}).from(cars);Filtering Records
Basic Filtering
Use the .where() clause combined with conditional operators to retrieve only the rows that match specific criteria.
// Select all cars with a green color
await db.select().from(cars).where(eq(cars.color, 'green'));Combining Conditions
You can group multiple filters together using logical operators like and() or or():
// Select all BMW cars with a green color
await db.select().from(cars).where(and(eq(cars.color, 'green'), eq(cars.brand, 'BMW')));
// Select all cars with green or blue color
await db.select().from(cars).where(or(eq(cars.color, 'green'), eq(cars.color, 'blue')));Sorting and Pagination
You can organize your query results by ordering them, limiting the total count, or skipping rows with an offset.
// Select cars ordered by year in ascending order
await db.select().from(cars).orderBy(cars.year);
// Select cars ordered by year in descending order, then by price
await db.select().from(cars).orderBy(desc(cars.year), asc(cars.price));
// Limit results and apply an offset for pagination
await db.select().from(people).offset(10).limit(10);Joining Tables
Combine rows from multiple tables or queries to retrieve related data across datasets.
// Combine user records with city data
const usersAndCities = await db.select({
userId: users.id,
cityName: cities.name,
})
.from(users);When building relational queries, table relationships can also be traversed and loaded hierarchically using query configs with the with property.
Aggregations and Grouping
Group rows with shared values into summary rows and filter aggregated data using .groupBy() and .having().
// Group and count cars by their brand, keeping only brands with more than one car
await db.select({
brand: cars.brand,
count: sql<number>`cast(count(${cars.id}) as int)`,
})
.from(cars)
.groupBy(cars.brand)
.having(({ count }) => gt(count, 1));Tip
Use .having() to filter aggregated results (like counts or sums) just like you use .where() to filter individual table rows.