AbstractOptionalnestedIndex: numberReadonly_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);
Protected ReadonlynestedProtectedschemaStatic Readonly[entityOptionalfilters: SQL<unknown>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
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
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.
Optionalconfig: SQLiteTransactionConfigCreates 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.
Creates 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.
See docs: https://orm.drizzle.team/docs/select#with-clause