New Features
Support for UPDATE ... FROM in PostgreSQL and SQLite
As the SQLite documentation mentions:
Note
The UPDATE-FROM idea is an extension to SQL that allows an UPDATE statement to be driven by other tables in the database.
The "target" table is the specific table that is being updated. With UPDATE-FROM you can join the target table
against other tables in the database in order to help compute which rows need updating and what
the new values should be on those rows
Similarly, the PostgreSQL documentation states:
Note
A table expression allowing columns from other tables to appear in the WHERE condition and update expressions
Drizzle also supports this feature starting from this version
For example, current query:
await db
.update(users)
.set({ cityId: cities.id })
.from(cities)
.where(and(eq(cities.name, 'Seattle'), eq(users.name, 'John')))Will generate this sql
update "users" set "city_id" = "cities"."id"
from "cities"
where ("cities"."name" = $1 and "users"."name" = $2)
-- params: [ 'Seattle', 'John' ]You can also alias tables that are joined (in PG, you can also alias the updating table too).
const c = alias(cities, 'c');
await db
.update(users)
.set({ cityId: c.id })
.from(c);Will generate this sql
update "users" set "city_id" = "c"."id"
from "cities" "c"In PostgreSQL, you can also return columns from the joined tables.
const updatedUsers = await db
.update(users)
.set({ cityId: cities.id })
.from(cities)
.returning({ id: users.id, cityName: cities.name });Will generate this sql
update "users" set "city_id" = "cities"."id"
from "cities"
returning "users"."id", "cities"."name"Support for INSERT INTO ... SELECT in all dialects
As the SQLite documentation mentions:
Note
The second form of the INSERT statement contains a SELECT statement instead of a VALUES clause.
A new entry is inserted into the table for each row of data returned by executing the SELECT statement.
If a column-list is specified, the number of columns in the result of the SELECT must be the same as
the number of items in the column-list. Otherwise, if no column-list is specified, the number of
columns in the result of the SELECT must be the same as the number of columns in the table.
Any SELECT statement, including compound SELECTs and SELECT statements with ORDER BY and/or LIMIT clauses,
may be used in an INSERT statement of this form.
Caution
To avoid a parsing ambiguity, the SELECT statement should always contain a WHERE clause, even if that clause is simply "WHERE true", if the upsert-clause is present. Without the WHERE clause, the parser does not know if the token "ON" is part of a join constraint on the SELECT, or the beginning of the upsert-clause.
As the PostgreSQL documentation mentions:
Note
A query (SELECT statement) that supplies the rows to be inserted
And as the MySQL documentation mentions:
Note
With INSERT ... SELECT, you can quickly insert many rows into a table from the result of a SELECT statement, which can select from one or many tables
Drizzle supports the current syntax for all dialects, and all of them share the same syntax. Let's review some common scenarios and API usage.
There are several ways to use select inside insert statements, allowing you to choose your preferred approach:
- You can pass a query builder inside the select function.
- You can use a query builder inside a callback.
- You can pass an SQL template tag with any custom select query you want to use
Query Builder
const insertedEmployees = await db
.insert(employees)
.select(
db.select({ name: users.name }).from(users).where(eq(users.role, 'employee'))
)
.returning({
id: employees.id,
name: employees.name
});const qb = new QueryBuilder();
await db.insert(employees).select(
qb.select({ name: users.name }).from(users).where(eq(users.role, 'employee'))
);Callback
await db.insert(employees).select(
() => db.select({ name: users.name }).from(users).where(eq(users.role, 'employee'))
);await db.insert(employees).select(
(qb) => qb.select({ name: users.name }).from(users).where(eq(users.role, 'employee'))
);SQL template tag
await db.insert(employees).select(
sql`select "users"."name" as "name" from "users" where "users"."role" = 'employee'`
);await db.insert(employees).select(
() => sql`select "users"."name" as "name" from "users" where "users"."role" = 'employee'`
);