Readonly_Protected OptionalauthStatic Readonly[entityCreates a subquery that defines a temporary named result set as a CTE.
It is useful for breaking down complex queries into simpler parts and for reusing the result set in subsequent parts of the query.
The alias for the subquery.
Failure to provide an alias will result in a DrizzleTypeError, preventing the subquery from being referenced in other queries.
// Create a subquery with alias 'sq' and use it in the select query
const sq = db.$with('sq').as(db.select().from(users).where(eq(users.id, 42)));
const result = await db.with(sq).select().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:
// Select an arbitrary SQL value as a field in a CTE and reference it in the main query
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);
Creates a delete query.
Calling this method without .where() clause will delete all rows in a table. The .where() clause specifies which rows should be deleted.
See docs: https://orm.drizzle.team/docs/delete
The table to delete from.
Creates an insert query.
Calling this method will create new rows in a table. Use .values() method to specify which values to insert.
See docs: https://orm.drizzle.team/docs/insert
The table to insert into.
Creates a select query.
Calling this method with no arguments will select all columns from the table. Pass a selection object to specify the columns you want to select.
Use .from() method to specify which table to select from.
See docs: https://orm.drizzle.team/docs/select
// Select all columns and all rows from the 'cars' table
const allCars: Car[] = await db.select().from(cars);
// Select specific columns and all rows from the 'cars' table
const carsIdsAndBrands: { id: number; brand: string }[] = await db.select({
id: cars.id,
brand: cars.brand
})
.from(cars);
Like in SQL, you can use arbitrary expressions as selection fields, not just table columns:
// Select specific columns along with expression and all rows from the 'cars' table
const carsIdsAndLowerNames: { id: number; lowerBrand: string }[] = await db.select({
id: cars.id,
lowerBrand: sql<string>`lower(${cars.brand})`,
})
.from(cars);
Creates a select query.
Calling this method with no arguments will select all columns from the table. Pass a selection object to specify the columns you want to select.
Use .from() method to specify which table to select from.
See docs: https://orm.drizzle.team/docs/select
The selection object.
// Select all columns and all rows from the 'cars' table
const allCars: Car[] = await db.select().from(cars);
// Select specific columns and all rows from the 'cars' table
const carsIdsAndBrands: { id: number; brand: string }[] = await db.select({
id: cars.id,
brand: cars.brand
})
.from(cars);
Like in SQL, you can use arbitrary expressions as selection fields, not just table columns:
// Select specific columns along with expression and all rows from the 'cars' table
const carsIdsAndLowerNames: { id: number; lowerBrand: string }[] = await db.select({
id: cars.id,
lowerBrand: sql<string>`lower(${cars.brand})`,
})
.from(cars);
Adds distinct expression to the select query.
Calling this method will return only unique values. When multiple columns are selected, it returns rows with unique combinations of values in these columns.
Use .from() method to specify which table to select from.
Adds distinct expression to the select query.
Calling this method will return only unique values. When multiple columns are selected, it returns rows with unique combinations of values in these columns.
Use .from() method to specify which table to select from.
The selection object.
Adds distinct on expression to the select query.
Calling this method will specify how the unique rows are determined.
Use .from() method to specify which table to select from.
The expression defining uniqueness.
// Select the first row for each unique brand from the 'cars' table
await db.selectDistinctOn([cars.brand])
.from(cars)
.orderBy(cars.brand);
// Selects the first occurrence of each unique car brand along with its color from the 'cars' table
await db.selectDistinctOn([cars.brand], { brand: cars.brand, color: cars.color })
.from(cars)
.orderBy(cars.brand, cars.color);
Adds distinct on expression to the select query.
Calling this method will specify how the unique rows are determined.
Use .from() method to specify which table to select from.
The expression defining uniqueness.
The selection object.
// Select the first row for each unique brand from the 'cars' table
await db.selectDistinctOn([cars.brand])
.from(cars)
.orderBy(cars.brand);
// Selects the first occurrence of each unique car brand along with its color from the 'cars' table
await db.selectDistinctOn([cars.brand], { brand: cars.brand, color: cars.color })
.from(cars)
.orderBy(cars.brand, cars.color);
Creates an update query.
Calling this method without .where() clause will update all rows in a table. The .where() clause specifies which rows should be updated.
Use .set() method to specify which values to update.
See docs: https://orm.drizzle.team/docs/update
The table to update.
// Update all rows in the 'cars' table
await db.update(cars).set({ color: 'red' });
// Update rows with filters and conditions
await db.update(cars).set({ color: 'red' }).where(eq(cars.brand, 'BMW'));
// Update with returning clause
const updatedCar: Car[] = await db.update(cars)
.set({ color: 'red' })
.where(eq(cars.id, 1))
.returning();
Incorporates a previously defined CTE (using $with) into the main query.
This method allows the main query to reference a temporary named result set.
The CTEs to incorporate into the main query.
StaticconnectConnect to a database and return a slonik connection
Postgres Connection String
Optionalopts: {Options Object
OptionalmigrationsFolder?: stringOptionaloptions?: PostgresJsSessionOptionsOptionalretry?: numberNumber of times to retry an initial connection
Optionalssl?: { rejectUnauthorized?: boolean }
Param: connstr
Postgres Connection String
Param: schema
DrizzleORM Schema