SQL Select

Drizzle provides you the most SQL-like way to fetch data from your database, while remaining type-safe and composable. It natively supports mostly every query feature and capability of every dialect, and whatever it doesn’t support yet, can be added by the user with the powerful sql operator.

For the following examples, let’s assume you have a users table defined like this:

import { pgTable, serial, text } from 'drizzle-orm/pg-core';

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
  age: integer('age'),
});

import { mysqlTable, serial, text } from 'drizzle-orm/mysql-core';

export const users = mysqlTable('users', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
  age: integer('age'),
});

import { sqliteTable, integer, text } from 'drizzle-orm/sqlite-core';

export const users = sqliteTable('users', {
  id: integer('id').primaryKey(),
  name: text('name').notNull(),
  age: integer('age'),
});

Basic and partial select

Select with all columns

Select all rows from a table including all columns:

const result = await db.select().from(users);
/*
  {
    id: number;
    name: string;
    age: number | null;
  }[]
*/

select "id", "name", "age" from "users";

Notice that the result type is inferred automatically based on the table definition, including columns nullability.

Partial select

In some cases, you might want to select only a subset of columns from a table. You can do that by providing a selection object to the .select() method:

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

const { field1, field2 } = result[0];

select "id", "name" from "users";

Like in SQL, you can use arbitrary expressions as selection fields, not just table columns:

const result = await db.select({
  id: users.id,
  lowerName: sql<string>`lower(${users.name})`,
}).from(users);

select "id", lower("name") from "users";

If you have an expression you use frequently, you can extract it into a function:

import type { Column } from 'drizzle-orm';
import { sql } from 'drizzle-orm';

function lower(col: Column) {
  return sql<string>`lower(${col})`;
}

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

Conditional select

You can have a dynamic selection object based on some condition:

async function selectUsers(withName: boolean) {
  return db
    .select({
      id: users.id,
      ...(withName ? { name: users.name } : {}),
    })
    .from(users);
}

const users = await selectUsers(true);

Filtering

You can filter the query results using the filter operators in the .where() method:

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

await db.select().from(users).where(eq(users.id, 42));
await db.select().from(users).where(lt(users.id, 42));
await db.select().from(users).where(gte(users.id, 42));
await db.select().from(users).where(ne(users.id, 42));
...

select "id", "name", "age" from "users" where "id" = 42;
select "id", "name", "age" from "users" where "id" < 42;
select "id", "name", "age" from "users" where "id" >= 42;
select "id", "name", "age" from "users" where "id" <> 42;

All filter operators are implemented using the sql function. You can use it yourself to write arbitrary SQL filters, or build your own operators. For inspiration, you can check how the operators provided by Drizzle are implemented.

import { sql } from 'drizzle-orm';

function equals42(col: Column) {
  return sql`${col} = 42`;
}

await db.select().from(users).where(sql`${users.id} < 42`);
await db.select().from(users).where(sql`${users.id} = 42`);
await db.select().from(users).where(equals42(users.id));
await db.select().from(users).where(sql`${users.id} >= 42`);
await db.select().from(users).where(sql`${users.id} <> 42`);
await db.select().from(users).where(sql`lower(${users.name}) = 'aaron'`);

select "id", "name", "age" from "users" where 'id' < 42;
select "id", "name", "age" from "users" where 'id' = 42;
select "id", "name", "age" from "users" where 'id' = 42;
select "id", "name", "age" from "users" where 'id' >= 42;
select "id", "name", "age" from "users" where 'id' <> 42;
select "id", "name", "age" from "users" where lower("name") = 'aaron';

await db.select().from(users).where(eq(users.id, 42));

select "id", "name", "age" from "users" where "id" = $1; -- params: [42]

Inverting condition with a not operator:

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

await db.select().from(users).where(not(eq(users.id, 42)));
await db.select().from(users).where(sql`not ${users.id} = 42`);

select "id", "name", "age" from "users" where not ("id" = 42);
select "id", "name", "age" from "users" where not ("id" = 42);

Combining filters

You can logically combine filter operators with and() and or() operators:

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

await db.select().from(users).where(
  and(
    eq(users.id, 42),
    eq(users.name, 'Dan')
  )
);
await db.select().from(users).where(sql`${users.id} = 42 and ${users.name} = 'Dan'`);

select "id", "name", "age" from "users" where "id" = 42 and "name" = 'Dan';
select "id", "name", "age" from "users" where "id" = 42 and "name" = 'Dan';

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

await db.select().from(users).where(
  or(
    eq(users.id, 42), 
    eq(users.name, 'Dan')
  )
);
await db.select().from(users).where(sql`${users.id} = 42 or ${users.name} = 'Dan'`);

select "id", "name", "age" from "users" where "id" = 42 or "name" = 'Dan';
select "id", "name", "age" from "users" where "id" = 42 or "name" = 'Dan';

Distinct

You can use .selectDistinct() instead of .select() to retrieve only unique rows from a dataset:

await db.selectDistinct().from(users).orderBy(usersTable.id, usersTable.name);

await db.selectDistinct({ id: users.id }).from(users).orderBy(usersTable.id);

select distinct "id", "name" from "users" order by "id", "name";

select distinct "id" from "users" order by "id";

In PostgreSQL, you can also use the distinct on clause to specify how the unique rows are determined:

await db.selectDistinctOn([users.id]).from(users).orderBy(users.id);
await db.selectDistinctOn([users.name], { name: users.name }).from(users).orderBy(users.name);

select distinct on ("id") "id", "name" from "users" order by "id";
select distinct on ("name") "name" from "users" order by "name";

Limit & offset

Use .limit() and .offset() to add limit and offset clauses to the query - for example, to implement pagination:

await db.select().from(users).limit(10);
await db.select().from(users).limit(10).offset(10);

select "id", "name", "age" from "users" limit 10;
select "id", "name", "age" from "users" limit 10 offset 10;

Order By

Use .orderBy() to add order by clause to the query, sorting the results by the specified fields:

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

await db.select().from(users).orderBy(users.name);
await db.select().from(users).orderBy(desc(users.name));

// order by multiple fields
await db.select().from(users).orderBy(users.name, users.name2);
await db.select().from(users).orderBy(asc(users.name), desc(users.name2));

select "id", "name", "age" from "users" order by "name";
select "id", "name", "age" from "users" order by "name" desc;

select "id", "name", "age" from "users" order by "name", "name2";
select "id", "name", "age" from "users" order by "name" asc, "name2" desc;

WITH clause

Using the with clause can help you simplify complex queries by splitting them into smaller subqueries called common table expressions (CTEs):

const sq = db.$with('sq').as(db.select().from(users).where(eq(users.id, 42)));

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

with sq as (select "id", "name", "age" from "users" where "id" = 42)
select "id", "name", "age" from sq;

To select arbitrary SQL values as fields in a CTE and reference them in other CTEs or in the main query, you need to add aliases to them:

const sq = db.$with('sq').as(db.select({ 
  name: sql<string>`upper(${users.name})`.as('name'),
})
.from(users));

const result = await db.with(sq).select({ name: sq.name }).from(sq);

If you don’t provide an alias, the field type will become DrizzleTypeError and you won’t be able to reference it in other queries. If you ignore the type error and still try to use the field, you will get a runtime error, since there’s no way to reference that field without an alias.

Select from subquery

Just like in SQL, you can embed queries into other queries by using the subquery API:

const sq = db.select().from(users).where(eq(users.id, 42)).as('sq');
const result = await db.select().from(sq);

select "id", "name", "age" from (select "id", "name", "age" from "users" where "id" = 42) "sq";

Subqueries can be used in any place where a table can be used, for example in joins:

const sq = db.select().from(users).where(eq(users.id, 42)).as('sq');
const result = await db.select().from(users).leftJoin(sq, eq(users.id, sq.id));

select "users"."id", "users"."name", "users"."age", "sq"."id", "sq"."name", "sq"."age" from "users"
  left join (select "id", "name", "age" from "users" where "id" = 42) "sq"
    on "users"."id" = "sq"."id";

Iterator

If you need to return a very large amount of rows from a query and you don’t want to load them all into memory, you can use .iterator() to convert the query into an async iterator:

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

for await (const row of iterator) {
  console.log(row);
}

It also works with prepared statements:

const query = await db.select().from(users).prepare();
const iterator = await query.iterator();

for await (const row of iterator) {
  console.log(row);
}

Aggregations

With Drizzle, you can do aggregations using functions like sum, count, avg, etc. by grouping and filtering with .groupBy() and .having() respectfully, same as you would do in raw SQL:

import { gt } from 'drizzle-orm';

await db.select({
  age: users.age,
  count: sql<number>`cast(count(${users.id}) as int)`,
})
  .from(users)
  .groupBy(users.age);

await db.select({
  age: users.age,
  count: sql<number>`cast(count(${users.id}) as int)`,
})
  .from(users)
  .groupBy(users.age)
  .having(({ count }) => gt(count, 1));

select "age", cast(count("id") as int)
  from "users"
  group by "age";

select "age", cast(count("id") as int)
  from "users"
  group by "age"
  having cast(count("id") as int) > 1;

Aggregations helpers

Drizzle has a set of wrapped sql functions, so you don’t need to write sql templates for common cases in your app

count

Returns the number of values in expression.

import { count } from 'drizzle-orm'

await db.select({ value: count() }).from(users);
await db.select({ value: count(users.id) }).from(users);

select count("*") from "users";
select count("id") from "users";

// It's equivalent to writing
await db.select({ 
  value: sql`count('*'))`.mapWith(Number) 
}).from(users);

await db.select({ 
  value: sql`count(${users.id})`.mapWith(Number) 
}).from(users);

countDistinct

Returns the number of non-duplicate values in expression.

import { countDistinct } from 'drizzle-orm'

await db.select({ value: countDistinct(users.id) }).from(users);

select count(distinct "id") from "users";

// It's equivalent to writing
await db.select({ 
  value: sql`count(${users.id})`.mapWith(Number) 
}).from(users);

avg

Returns the average (arithmetic mean) of all non-null values in expression.

import { avg } from 'drizzle-orm'

await db.select({ value: avg(users.id) }).from(users);

select avg("id") from "users";

// It's equivalent to writing
await db.select({ 
  value: sql`avg(${users.id})`.mapWith(String) 
}).from(users);

avgDistinct

Returns the average (arithmetic mean) of all non-null values in expression.

import { avgDistinct } from 'drizzle-orm'

await db.select({ value: avgDistinct(users.id) }).from(users);

select avg(distinct "id") from "users";

// It's equivalent to writing
await db.select({ 
  value: sql`avg(distinct ${users.id})`.mapWith(String) 
}).from(users);

sum

Returns the sum of all non-null values in expression.

import { sum } from 'drizzle-orm'

await db.select({ value: sum(users.id) }).from(users);

select sum("id") from "users";

// It's equivalent to writing
await db.select({ 
  value: sql`sum(${users.id})`.mapWith(String) 
}).from(users);

sumDistinct

Returns the sum of all non-null and non-duplicate values in expression.

import { sumDistinct } from 'drizzle-orm'

await db.select({ value: sumDistinct(users.id) }).from(users);

select sum(distinct "id") from "users";

// It's equivalent to writing
await db.select({ 
  value: sql`sum(distinct ${users.id})`.mapWith(String) 
}).from(users);

max

Returns the maximum value in expression.

import { max } from 'drizzle-orm'

await db.select({ value: max(users.id) }).from(users);

select max("id") from "users";

// It's equivalent to writing
await db.select({ 
  value: sql`max(${expression})`.mapWith(users.id) 
}).from(users);

min

Returns the minimum value in expression.

import { min } from 'drizzle-orm'

await db.select({ value: min(users.id) }).from(users);

select min("id") from "users";

// It's equivalent to writing
await db.select({ 
  value: sql`min(${users.id})`.mapWith(users.id) 
}).from(users);

A more advanced example:

const orders = sqliteTable('order', {
  id: integer('id').primaryKey(),
  orderDate: integer('order_date', { mode: 'timestamp' }).notNull(),
  requiredDate: integer('required_date', { mode: 'timestamp' }).notNull(),
  shippedDate: integer('shipped_date', { mode: 'timestamp' }),
  shipVia: integer('ship_via').notNull(),
  freight: numeric('freight').notNull(),
  shipName: text('ship_name').notNull(),
  shipCity: text('ship_city').notNull(),
  shipRegion: text('ship_region'),
  shipPostalCode: text('ship_postal_code'),
  shipCountry: text('ship_country').notNull(),
  customerId: text('customer_id').notNull(),
  employeeId: integer('employee_id').notNull(),
});

const details = sqliteTable('order_detail', {
  unitPrice: numeric('unit_price').notNull(),
  quantity: integer('quantity').notNull(),
  discount: numeric('discount').notNull(),
  orderId: integer('order_id').notNull(),
  productId: integer('product_id').notNull(),
});


db
  .select({
    id: orders.id,
    shippedDate: orders.shippedDate,
    shipName: orders.shipName,
    shipCity: orders.shipCity,
    shipCountry: orders.shipCountry,
    productsCount: sql<number>`cast(count(${details.productId}) as int)`,
    quantitySum: sql<number>`sum(${details.quantity})`,
    totalPrice: sql<number>`sum(${details.quantity} * ${details.unitPrice})`,
  })
  .from(orders)
  .leftJoin(details, eq(orders.id, details.orderId))
  .groupBy(orders.id)
  .orderBy(asc(orders.id))
  .all();