Class WheneableMergeQueryBuilder<DB, TT, ST, O>

Simply calls the provided function passing this as the only argument. $call returns what the provided function returns.

If you want to conditionally call a method on this, see the $if method.

Examples

The next example uses a helper function log to log a query:

import type { Compilable } from 'kysely'

function log<T extends Compilable>(qb: T): T {
  console.log(qb.compile())
  return qb
}

await db.updateTable('person')
  .set({ first_name: 'John' })
  .$call(log)
  .execute()
Copy

Call func(this) if condition is true.

This method is especially handy with optional selects. Any returning or returningAll method calls add columns as optional fields to the output type when called inside the func callback. This is because we can't know if those selections were actually made before running the code.

You can also call any other methods inside the callback.

Examples

import type { PersonUpdate } from 'type-editor' // imaginary module

async function updatePerson(id: number, updates: PersonUpdate, returnLastName: boolean) {
  return await db
    .updateTable('person')
    .set(updates)
    .where('id', '=', id)
    .returning(['id', 'first_name'])
    .$if(returnLastName, (qb) => qb.returning('last_name'))
    .executeTakeFirstOrThrow()
}
Copy

Any selections added inside the if callback will be added as optional fields to the output type since we can't know if the selections were actually made before running the code. In the example above the return type of the updatePerson function is:

Promise<{
  id: number
  first_name: string
  last_name?: string
}>
Copy

Executes the query and returns an array of rows.

Also see the executeTakeFirst and executeTakeFirstOrThrow methods.

Executes the query and returns the first result or undefined if the query returned no result.

Executes the query and returns the first result or throws if the query returned no result.

By default an instance of NoResultError is thrown, but you can provide a custom error class, or callback as the only argument to throw a different error.

This can be used to add any additional SQL to the end of the query.

Examples

import { sql } from 'kysely'

await db
  .mergeInto('person')
  .using('pet', 'pet.owner_id', 'person.id')
  .whenMatched()
  .thenDelete()
  .modifyEnd(sql.raw('-- this is a comment'))
  .execute()
Copy

The generated SQL (PostgreSQL):

merge into "person" using "pet" on "pet"."owner_id" = "person"."id" when matched then delete -- this is a comment
Copy

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.

Examples

Return one column:

const { id } = await db
  .insertInto('person')
  .output('inserted.id')
  .values({
    first_name: 'Jennifer',
    last_name: 'Aniston',
    gender: 'female',
  })
  .executeTakeFirstOrThrow()
Copy

The generated SQL (MSSQL):

insert into "person" ("first_name", "last_name", "gender")
output "inserted"."id"
values (@1, @2, @3)
Copy

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

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
Copy

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

The generated SQL (MSSQL):

delete from "person"
output concat("deleted"."first_name", ' ', "deleted"."last_name") as "full_name"
where "created_at" < @1
Copy

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

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"
Copy

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.

Examples

Return one column:

const { id } = await db
  .insertInto('person')
  .values({
    first_name: 'Jennifer',
    last_name: 'Aniston'
  })
  .returning('id')
  .executeTakeFirstOrThrow()
Copy

Return multiple columns:

const { id, last_name } = await db
  .insertInto('person')
  .values({
    first_name: 'Jennifer',
    last_name: 'Aniston'
  })
  .returning(['id', 'last_name'])
  .executeTakeFirstOrThrow()
Copy

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

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.

See MergeQueryBuilder.top.

Adds a simple when matched clause to the query.

For a when matched clause with an and condition, see whenMatchedAnd.

For a simple when not matched clause, see whenNotMatched.

For a when not matched clause with an and condition, see whenNotMatchedAnd.

Examples

const result = await db.mergeInto('person')
  .using('pet', 'person.id', 'pet.owner_id')
  .whenMatched()
  .thenDelete()
  .execute()
Copy

The generated SQL (PostgreSQL):

merge into "person"
using "pet" on "person"."id" = "pet"."owner_id"
when matched then
  delete
Copy

Adds the when matched clause to the query with an and condition.

This method is similar to SelectQueryBuilder.where, so see the documentation for that method for more examples.

For a simple when matched clause (without an and condition) see whenMatched.

Examples

const result = await db.mergeInto('person')
  .using('pet', 'person.id', 'pet.owner_id')
  .whenMatchedAnd('person.first_name', '=', 'John')
  .thenDelete()
  .execute()
Copy

The generated SQL (PostgreSQL):

merge into "person"
using "pet" on "person"."id" = "pet"."owner_id"
when matched and "person"."first_name" = $1 then
  delete
Copy

Adds the when matched clause to the query with an and condition.

This method is similar to SelectQueryBuilder.where, so see the documentation for that method for more examples.

For a simple when matched clause (without an and condition) see whenMatched.

Examples

const result = await db.mergeInto('person')
  .using('pet', 'person.id', 'pet.owner_id')
  .whenMatchedAnd('person.first_name', '=', 'John')
  .thenDelete()
  .execute()
Copy

The generated SQL (PostgreSQL):

merge into "person"
using "pet" on "person"."id" = "pet"."owner_id"
when matched and "person"."first_name" = $1 then
  delete
Copy

Adds the when matched clause to the query with an and condition. But unlike whenMatchedAnd, this method accepts a column reference as the 3rd argument.

This method is similar to SelectQueryBuilder.whereRef, so see the documentation for that method for more examples.

Adds a simple when not matched clause to the query.

For a when not matched clause with an and condition, see whenNotMatchedAnd.

For a simple when matched clause, see whenMatched.

For a when matched clause with an and condition, see whenMatchedAnd.

Examples

const result = await db.mergeInto('person')
  .using('pet', 'person.id', 'pet.owner_id')
  .whenNotMatched()
  .thenInsertValues({
    first_name: 'John',
    last_name: 'Doe',
  })
  .execute()
Copy

The generated SQL (PostgreSQL):

merge into "person"
using "pet" on "person"."id" = "pet"."owner_id"
when not matched then
  insert ("first_name", "last_name") values ($1, $2)
Copy

Adds the when not matched clause to the query with an and condition.

This method is similar to SelectQueryBuilder.where, so see the documentation for that method for more examples.

For a simple when not matched clause (without an and condition) see whenNotMatched.

Unlike whenMatchedAnd, you cannot reference columns from the table merged into.

Examples

const result = await db.mergeInto('person')
  .using('pet', 'person.id', 'pet.owner_id')
  .whenNotMatchedAnd('pet.name', '=', 'Lucky')
  .thenInsertValues({
    first_name: 'John',
    last_name: 'Doe',
  })
  .execute()
Copy

The generated SQL (PostgreSQL):

merge into "person"
using "pet" on "person"."id" = "pet"."owner_id"
when not matched and "pet"."name" = $1 then
  insert ("first_name", "last_name") values ($2, $3)
Copy

Adds the when not matched clause to the query with an and condition.

This method is similar to SelectQueryBuilder.where, so see the documentation for that method for more examples.

For a simple when not matched clause (without an and condition) see whenNotMatched.

Unlike whenMatchedAnd, you cannot reference columns from the table merged into.

Examples

const result = await db.mergeInto('person')
  .using('pet', 'person.id', 'pet.owner_id')
  .whenNotMatchedAnd('pet.name', '=', 'Lucky')
  .thenInsertValues({
    first_name: 'John',
    last_name: 'Doe',
  })
  .execute()
Copy

The generated SQL (PostgreSQL):

merge into "person"
using "pet" on "person"."id" = "pet"."owner_id"
when not matched and "pet"."name" = $1 then
  insert ("first_name", "last_name") values ($2, $3)
Copy

Adds the when not matched clause to the query with an and condition. But unlike whenNotMatchedAnd, this method accepts a column reference as the 3rd argument.

Unlike whenMatchedAndRef, you cannot reference columns from the target table.

This method is similar to SelectQueryBuilder.whereRef, so see the documentation for that method for more examples.

Adds a simple when not matched by source clause to the query.

Supported in MS SQL Server.

Similar to whenNotMatched, but returns a MatchedThenableMergeQueryBuilder.

Adds the when not matched by source clause to the query with an and condition.

Supported in MS SQL Server.

Similar to whenNotMatchedAnd, but returns a MatchedThenableMergeQueryBuilder.

Adds the when not matched by source clause to the query with an and condition.

Supported in MS SQL Server.

Similar to whenNotMatchedAnd, but returns a MatchedThenableMergeQueryBuilder.

Adds the when not matched by source clause to the query with an and condition.

Similar to whenNotMatchedAndRef, but you can reference columns from the target table, and not from source table and returns a MatchedThenableMergeQueryBuilder.