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();