Allows you to return data from modified rows.
On supported databases like MS SQL Server (MSSQL), this method can be chained
to insert, update, delete and merge queries to return data.
Also see the outputAll method.
Return one column:
const { id } = await db
.insertInto('person')
.output('inserted.id')
.values({
first_name: 'Jennifer',
last_name: 'Aniston',
gender: 'female',
})
.executeTakeFirstOrThrow()
The generated SQL (MSSQL):
insert into "person" ("first_name", "last_name", "gender")
output "inserted"."id"
values (@1, @2, @3)
Return multiple columns:
const { old_first_name, old_last_name, new_first_name, new_last_name } = await db
.updateTable('person')
.set({ first_name: 'John', last_name: 'Doe' })
.output([
'deleted.first_name as old_first_name',
'deleted.last_name as old_last_name',
'inserted.first_name as new_first_name',
'inserted.last_name as new_last_name',
])
.where('created_at', '<', new Date())
.executeTakeFirstOrThrow()
The generated SQL (MSSQL):
update "person"
set "first_name" = @1, "last_name" = @2
output "deleted"."first_name" as "old_first_name",
"deleted"."last_name" as "old_last_name",
"inserted"."first_name" as "new_first_name",
"inserted"."last_name" as "new_last_name"
where "created_at" < @3
Return arbitrary expressions:
import { sql } from 'kysely'
const { full_name } = await db
.deleteFrom('person')
.output((eb) => sql<string>`concat(${eb.ref('deleted.first_name')}, ' ', ${eb.ref('deleted.last_name')})`.as('full_name'))
.where('created_at', '<', new Date())
.executeTakeFirstOrThrow()
The generated SQL (MSSQL):
delete from "person"
output concat("deleted"."first_name", ' ', "deleted"."last_name") as "full_name"
where "created_at" < @1
Return the action performed on the row:
await db
.mergeInto('person')
.using('pet', 'pet.owner_id', 'person.id')
.whenMatched()
.thenDelete()
.whenNotMatched()
.thenInsertValues({
first_name: 'John',
last_name: 'Doe',
gender: 'male'
})
.output([
'inserted.id as inserted_id',
'deleted.id as deleted_id',
])
.execute()
The generated SQL (MSSQL):
merge into "person"
using "pet" on "pet"."owner_id" = "person"."id"
when matched then delete
when not matched then
insert ("first_name", "last_name", "gender")
values (@1, @2, @3)
output "inserted"."id" as "inserted_id", "deleted"."id" as "deleted_id"
Adds an output {prefix}.* to an insert/update/delete/merge query on databases
that support output such as MS SQL Server (MSSQL).
Also see the output method.
Allows you to return data from modified rows.
On supported databases like PostgreSQL, this method can be chained to
insert, update, delete and merge queries to return data.
Note that on SQLite you need to give aliases for the expressions to avoid
this bug in SQLite.
For example .returning('id as id').
Also see the returningAll method.
Return one column:
const { id } = await db
.insertInto('person')
.values({
first_name: 'Jennifer',
last_name: 'Aniston'
})
.returning('id')
.executeTakeFirstOrThrow()
Return multiple columns:
const { id, last_name } = await db
.insertInto('person')
.values({
first_name: 'Jennifer',
last_name: 'Aniston'
})
.returning(['id', 'last_name'])
.executeTakeFirstOrThrow()
Return arbitrary expressions:
import { sql } from 'kysely'
const { id, full_name, first_pet_id } = await db
.insertInto('person')
.values({
first_name: 'Jennifer',
last_name: 'Aniston'
})
.returning((eb) => [
'id as id',
sql<string>`concat(first_name, ' ', last_name)`.as('full_name'),
eb.selectFrom('pet').select('pet.id').limit(1).as('first_pet_id')
])
.executeTakeFirstOrThrow()
Adds a returning * or returning table.* to an insert/update/delete/merge
query on databases that support returning such as PostgreSQL.
Also see the returning method.
Adds a returning * to an insert/update/delete/merge query on databases
that support returning such as PostgreSQL.
Also see the returning method.
Changes a merge into query to an merge top into query.
top clause is only supported by some dialects like MS SQL Server.
Affect 5 matched rows at most:
await db.mergeInto('person')
.top(5)
.using('pet', 'person.id', 'pet.owner_id')
.whenMatched()
.thenDelete()
.execute()
The generated SQL (MS SQL Server):
merge top(5) into "person"
using "pet" on "person"."id" = "pet"."owner_id"
when matched then
delete
Affect 50% of matched rows:
await db.mergeInto('person')
.top(50, 'percent')
.using('pet', 'person.id', 'pet.owner_id')
.whenMatched()
.thenDelete()
.execute()
The generated SQL (MS SQL Server):
merge top(50) percent into "person"
using "pet" on "person"."id" = "pet"."owner_id"
when matched then
delete
Optionalmodifiers: "percent"Adds the using clause to the query.
This method is similar to SelectQueryBuilder.innerJoin, so see the documentation for that method for more examples.
const result = await db.mergeInto('person')
.using('pet', 'person.id', 'pet.owner_id')
.whenMatched()
.thenDelete()
.execute()
The generated SQL (PostgreSQL):
merge into "person"
using "pet" on "person"."id" = "pet"."owner_id"
when matched then
delete
Adds the using clause to the query.
This method is similar to SelectQueryBuilder.innerJoin, so see the documentation for that method for more examples.
const result = await db.mergeInto('person')
.using('pet', 'person.id', 'pet.owner_id')
.whenMatched()
.thenDelete()
.execute()
The generated SQL (PostgreSQL):
merge into "person"
using "pet" on "person"."id" = "pet"."owner_id"
when matched then
delete
This can be used to add any additional SQL to the end of the query.
Examples
The generated SQL (PostgreSQL):