SQL Select

Drizzle ORM provide you the most SQL-like way to query your relational database.
We natively support mostly every query feature capability of every dialect and whatever we do not yet support - can be done with our powerful sql operator

Basic and partial select

Getting a list of all users and you will have a typed result set

const result: User[] = await db.select().from(users);
 
result[0].id;
result[0].name;

select * from 'users';

Whenever you have SQL table with many columns you might not wanna select all of them for either performance or security reasons.
You can omit them by using our partial query syntax which will generate partial SQL select and automatically map results

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

select "users"."id" as "field1", "users"."name" as "field2" from "users";

With partial select you can apply sql transformations with sql operator

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

select "users"."id", lower("users"."name") as "lowerName" from "users";

You can also select fields conditionally

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

Select filters

You can filter SQL results with our list of filter operators

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 * from 'users' where 'id' = 42;
select * from 'users' where 'id' < 42;
select * from 'users' where 'id' >= 42;
select * from 'users' where 'id' <> 42;

Any filter operator is a sql operator under the hood, for full SQL potential you can utilise it directly and build type safe and future safe queries
You can safely alter schema, rename tables and columns and it will automatically reflect in queries, as opposed to having regular string raw SQL queries

import { sql } from "drizzle-orm";
 
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`${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 * from 'users' where 'id' = 42;
select * from 'users' where 'id' < 42;
select * from 'users' where 'id' <> 42;
select * from 'users' where 'id' >= 42;
select * from 'users' where lower('name') = "aaron";

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 * from 'users' where not 'id' = 42;
select * from 'users' where not 'id' = 42;

Combining filters

You can logically combine filter operators with conditional 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 * from 'users' where 'id' = 42 and 'name' = "Dan";
select * 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 * from 'users' where 'id' = 42 or 'name' = "Dan";
select * from 'users' where 'id' = 42 or 'name' = "Dan";

Distinct

You can use the distinct keyword to retrieve unique or distinct values from a column or set of columns in a query result. It eliminates duplicate rows, returning only the unique values.

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 "users"."id", "users"."name";
 
select distinct "id" from "users" order by "users"."id";

Drizzle ORM supports DISTINCT ON PostgreSQL operator too

💡

DISTINCT ON is only available in PostgreSQL

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 ("users"."id") "id", "name" from "users" order by "users"."id";
 
select distinct on ("users"."name") "name" from "users" order by "users"."name";

Limit & Offset

You can apply limit and offset to the query

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

select * from "users" limit 10;
select * from "users" limit 10 offset 10;

Order By

You can sort results with orderBy operator

import { asc, desc } from "drizzle-orm";
 
await db.select().from(users).orderBy(users.name);
await db.select().from(users).orderBy(desc(users.name));
 
// you can pass multiple order args
await db.select().from(users).orderBy(users.name, users.name2);
await db.select().from(users).orderBy(asc(users.name), desc(users.name2));

select * from "users" order by "name";
select * from "users" order by "name" desc;
 
select * from "users" order by "name" "name2";
select * from "users" order by "name" asc "name2" desc;

WITH clause

SQL with clause - is a statement scoped view, helpful to organise complex queries

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 * from "users" where "users"."id" = 42)
select * from sq;

To select raw sql in a WITH subquery and reference that field in other queries, you must add an alias to it

 
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 - 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 reference 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 SQL queries into other SQL queries by using subquery API

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

select * from (select * from "user" where "user"."id" = 42) "sq";

You can also use subqueries in joins

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

Aggregations

With Drizzle ORM you can do aggregations with functions like sum, count, avg, etc. by grouping and filtering with groupBy and having respectfully, just like you do in SQL.

With our powerful sql operator you can infer aggregations functions return types using sql<number> syntax

import { pgTable, serial, text, doublePrecision } from 'drizzle-orm/pg-core';
import { gte } from 'drizzle-orm';
 
export const product = pgTable('product', {
  id: serial('id').primaryKey(),
  name: text('name'),
  unitPrice: doublePrecision("unit_price")
});
 
const result = await db.select({ count: sql<number>`count(*)` }).from(product);
result[0].count // will be number type
 
await db.select({ count: sql<number>`count(*)` }).from(product).where(gte(product.unitPrice, 4));

select count(*) from "product";
select count(*) from "product" where "unit_price" >= 4;

Lets have a quick look on how to group and filter grouped using a having

import { pgTable, serial, text } from 'drizzle-orm/pg-core';
 
export const user = pgTable('user', {
  id: serial('id').primaryKey(),
  name: text('name'),
  city: text("city"),
});
 
await db.select({ count: sql<number>`count(${user.id})`, city: user.city })
  .from(user)
  .groupBy(({ city }) => city)
 
await db.select({ count: sql<number>`count(${user.id})`, city: user.city })
  .from(user)
  .groupBy(({ city }) => city)
  .having(({ count }) => count)

select count("id"), "city" from "user" group by "user"."city";
select count("id"), "city" from "user" group by "user"."city" having count("user"."id");

Here's 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>`count(${details.productId})`,
    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();

Query Insert