Class UpdateQueryBuilder<DB, UT, TB, O>

Asserts that query's output row type equals the given type T.

This method can be used to simplify excessively complex types to make TypeScript happy and much faster.

Kysely uses complex type magic to achieve its type safety. This complexity is sometimes too much for TypeScript and you get errors like this:

error TS2589: Type instantiation is excessively deep and possibly infinite.
Copy

In these case you can often use this method to help TypeScript a little bit. When you use this method to assert the output type of a query, Kysely can drop the complex output type that consists of multiple nested helper types and replace it with the simple asserted type.

Using this method doesn't reduce type safety at all. You have to pass in a type that is structurally equal to the current type.

Examples

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

const person = {
  id: 1,
  gender: 'other',
} satisfies PersonUpdate

const pet = {
  name: 'Fluffy',
} satisfies PetUpdate

const result = await db
  .with('updated_person', (qb) => qb
    .updateTable('person')
    .set(person)
    .where('id', '=', person.id)
    .returning('first_name')
    .$assertType<{ first_name: string }>()
  )
  .with('updated_pet', (qb) => qb
    .updateTable('pet')
    .set(pet)
    .where('owner_id', '=', person.id)
    .returning(['name as pet_name', 'species'])
    .$assertType<{ pet_name: string, species: Species }>()
  )
  .selectFrom(['updated_person', 'updated_pet'])
  .selectAll()
  .executeTakeFirstOrThrow()
Copy

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'
import type { PersonUpdate } from 'type-editor' // imaginary module

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

const values = {
  first_name: 'John',
} satisfies PersonUpdate

db.updateTable('person')
  .set(values)
  .$call(log)
  .execute()
Copy

Change the output type of the query.

This method call doesn't change the SQL in any way. This methods simply returns a copy of this UpdateQueryBuilder with a new output type.

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

Narrows (parts of) the output type of the query.

Kysely tries to be as type-safe as possible, but in some cases we have to make compromises for better maintainability and compilation performance. At present, Kysely doesn't narrow the output type of the query based on set input when using where and/or returning or returningAll.

This utility method is very useful for these situations, as it removes unncessary runtime assertion/guard code. Its input type is limited to the output type of the query, so you can't add a column that doesn't exist, or change a column's type to something that doesn't exist in its union type.

Examples

Turn this code:

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

const id = 1
const now = new Date().toISOString()

const person = await db.updateTable('person')
  .set({ deleted_at: now })
  .where('id', '=', id)
  .where('nullable_column', 'is not', null)
  .returningAll()
  .executeTakeFirstOrThrow()

if (isWithNoNullValue(person)) {
  functionThatExpectsPersonWithNonNullValue(person)
}

function isWithNoNullValue(person: Person): person is Person & { nullable_column: string } {
  return person.nullable_column != null
}
Copy

Into this:

import type { NotNull } from 'kysely'

const id = 1
const now = new Date().toISOString()

const person = await db.updateTable('person')
  .set({ deleted_at: now })
  .where('id', '=', id)
  .where('nullable_column', 'is not', null)
  .returningAll()
  .$narrowType<{ deleted_at: Date; nullable_column: NotNull }>()
  .executeTakeFirstOrThrow()

functionThatExpectsPersonWithNonNullValue(person)
Copy

Clears the order by clause from the query.

See orderBy for adding an order by clause or item to a query.

Examples

const query = db
  .selectFrom('person')
  .selectAll()
  .orderBy('id', 'desc')

const results = await query
  .clearOrderBy()
  .execute()
Copy

The generated SQL (PostgreSQL):

select * from "person"
Copy

Clears all returning clauses from the query.

Examples

db.updateTable('person')
  .returningAll()
  .set({ age: 39 })
  .where('first_name', '=', 'John')
  .clearReturning()
Copy

The generated SQL(PostgreSQL):

update "person" set "age" = 39 where "first_name" = "John"
Copy

Clears all where expressions from the query.

Examples

db.selectFrom('person')
  .selectAll()
  .where('id','=',42)
  .clearWhere()
Copy

The generated SQL(PostgreSQL):

select * from "person"
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.

Executes query with explain statement before the main query.

const explained = await db
 .selectFrom('person')
 .where('gender', '=', 'female')
 .selectAll()
 .explain('json')
Copy

The generated SQL (MySQL):

explain format=json select * from `person` where `gender` = ?
Copy

You can also execute explain analyze statements.

import { sql } from 'kysely'

const explained = await db
 .selectFrom('person')
 .where('gender', '=', 'female')
 .selectAll()
 .explain('json', sql`analyze`)
Copy

The generated SQL (PostgreSQL):

explain (analyze, format json) select * from "person" where "gender" = $1
Copy

Adds a from clause to the update query.

This is supported only on some databases like PostgreSQL.

The API is the same as QueryCreator.selectFrom.

Examples

db.updateTable('person')
  .from('pet')
  .set((eb) => ({
    first_name: eb.ref('pet.name')
  }))
  .whereRef('pet.owner_id', '=', 'person.id')
Copy

The generated SQL (PostgreSQL):

update "person"
set "first_name" = "pet"."name"
from "pet"
where "pet"."owner_id" = "person"."id"
Copy

Adds a from clause to the update query.

This is supported only on some databases like PostgreSQL.

The API is the same as QueryCreator.selectFrom.

Examples

db.updateTable('person')
  .from('pet')
  .set((eb) => ({
    first_name: eb.ref('pet.name')
  }))
  .whereRef('pet.owner_id', '=', 'person.id')
Copy

The generated SQL (PostgreSQL):

update "person"
set "first_name" = "pet"."name"
from "pet"
where "pet"."owner_id" = "person"."id"
Copy

Just like innerJoin but adds a full join instead of an inner join.

Just like innerJoin but adds a full join instead of an inner join.

Joins another table to the query using an inner join.

Examples

Simple usage by providing a table name and two columns to join:

const result = await db
  .selectFrom('person')
  .innerJoin('pet', 'pet.owner_id', 'person.id')
  // `select` needs to come after the call to `innerJoin` so
  // that you can select from the joined table.
  .select(['person.id', 'pet.name'])
  .execute()

result[0].id
result[0].name
Copy

The generated SQL (PostgreSQL):

select "person"."id", "pet"."name"
from "person"
inner join "pet"
on "pet"."owner_id" = "person"."id"
Copy

You can give an alias for the joined table like this:

await db.selectFrom('person')
  .innerJoin('pet as p', 'p.owner_id', 'person.id')
  .where('p.name', '=', 'Doggo')
  .selectAll()
  .execute()
Copy

The generated SQL (PostgreSQL):

select *
from "person"
inner join "pet" as "p"
on "p"."owner_id" = "person"."id"
where "p".name" = $1
Copy

You can provide a function as the second argument to get a join builder for creating more complex joins. The join builder has a bunch of on* methods for building the on clause of the join. There's basically an equivalent for every where method (on, onRef, onExists etc.). You can do all the same things with the on method that you can with the corresponding where method. See the where method documentation for more examples.

await db.selectFrom('person')
  .innerJoin(
    'pet',
    (join) => join
      .onRef('pet.owner_id', '=', 'person.id')
      .on('pet.name', '=', 'Doggo')
  )
  .selectAll()
  .execute()
Copy

The generated SQL (PostgreSQL):

select *
from "person"
inner join "pet"
on "pet"."owner_id" = "person"."id"
and "pet"."name" = $1
Copy

You can join a subquery by providing a select query (or a callback) as the first argument:

await db.selectFrom('person')
  .innerJoin(
    db.selectFrom('pet')
      .select(['owner_id', 'name'])
      .where('name', '=', 'Doggo')
      .as('doggos'),
    'doggos.owner_id',
    'person.id',
  )
  .selectAll()
  .execute()
Copy

The generated SQL (PostgreSQL):

select *
from "person"
inner join (
  select "owner_id", "name"
  from "pet"
  where "name" = $1
) as "doggos"
on "doggos"."owner_id" = "person"."id"
Copy

Joins another table to the query using an inner join.

Examples

Simple usage by providing a table name and two columns to join:

const result = await db
  .selectFrom('person')
  .innerJoin('pet', 'pet.owner_id', 'person.id')
  // `select` needs to come after the call to `innerJoin` so
  // that you can select from the joined table.
  .select(['person.id', 'pet.name'])
  .execute()

result[0].id
result[0].name
Copy

The generated SQL (PostgreSQL):

select "person"."id", "pet"."name"
from "person"
inner join "pet"
on "pet"."owner_id" = "person"."id"
Copy

You can give an alias for the joined table like this:

await db.selectFrom('person')
  .innerJoin('pet as p', 'p.owner_id', 'person.id')
  .where('p.name', '=', 'Doggo')
  .selectAll()
  .execute()
Copy

The generated SQL (PostgreSQL):

select *
from "person"
inner join "pet" as "p"
on "p"."owner_id" = "person"."id"
where "p".name" = $1
Copy

You can provide a function as the second argument to get a join builder for creating more complex joins. The join builder has a bunch of on* methods for building the on clause of the join. There's basically an equivalent for every where method (on, onRef, onExists etc.). You can do all the same things with the on method that you can with the corresponding where method. See the where method documentation for more examples.

await db.selectFrom('person')
  .innerJoin(
    'pet',
    (join) => join
      .onRef('pet.owner_id', '=', 'person.id')
      .on('pet.name', '=', 'Doggo')
  )
  .selectAll()
  .execute()
Copy

The generated SQL (PostgreSQL):

select *
from "person"
inner join "pet"
on "pet"."owner_id" = "person"."id"
and "pet"."name" = $1
Copy

You can join a subquery by providing a select query (or a callback) as the first argument:

await db.selectFrom('person')
  .innerJoin(
    db.selectFrom('pet')
      .select(['owner_id', 'name'])
      .where('name', '=', 'Doggo')
      .as('doggos'),
    'doggos.owner_id',
    'person.id',
  )
  .selectAll()
  .execute()
Copy

The generated SQL (PostgreSQL):

select *
from "person"
inner join (
  select "owner_id", "name"
  from "pet"
  where "name" = $1
) as "doggos"
on "doggos"."owner_id" = "person"."id"
Copy

Just like innerJoin but adds a left join instead of an inner join.

Just like innerJoin but adds a left join instead of an inner join.

Adds a limit clause to the update query for supported databases, such as MySQL.

Examples

Update the first 2 rows in the 'person' table:

await db
  .updateTable('person')
  .set({ first_name: 'Foo' })
  .limit(2)
  .execute()
Copy

The generated SQL (MySQL):

update `person` set `first_name` = ? limit ?
Copy

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

Examples

import { sql } from 'kysely'

await db.updateTable('person')
  .set({ age: 39 })
  .where('first_name', '=', 'John')
  .modifyEnd(sql.raw('-- This is a comment'))
  .execute()
Copy

The generated SQL (MySQL):

update `person`
set `age` = 39
where `first_name` = "John" -- 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.

Adds a returning * to an insert/update/delete/merge query on databases that support returning such as PostgreSQL.

Also see the returning method.

Just like innerJoin but adds a right join instead of an inner join.

Just like innerJoin but adds a right join instead of an inner join.

Sets the values to update for an update query.

This method takes an object whose keys are column names and values are values to update. In addition to the column's type, the values can be any expressions such as raw sql snippets or select queries.

This method also accepts a callback that returns the update object. The callback takes an instance of ExpressionBuilder as its only argument. The expression builder can be used to create arbitrary update expressions.

The return value of an update query is an instance of UpdateResult. You can use the returning method on supported databases to get out the updated rows.

Examples

Update a row in person table:

const result = await db
  .updateTable('person')
  .set({
    first_name: 'Jennifer',
    last_name: 'Aniston'
  })
  .where('id', '=', 1)
  .executeTakeFirst()
Copy

The generated SQL (PostgreSQL):

update "person" set "first_name" = $1, "last_name" = $2 where "id" = $3
Copy

As always, you can provide a callback to the set method to get access to an expression builder:

const result = await db
  .updateTable('person')
  .set((eb) => ({
    age: eb('age', '+', 1),
    first_name: eb.selectFrom('pet').select('name').limit(1),
    last_name: 'updated',
  }))
  .where('id', '=', 1)
  .executeTakeFirst()
Copy

The generated SQL (PostgreSQL):

update "person"
set
  "first_name" = (select "name" from "pet" limit $1),
  "age" = "age" + $2,
  "last_name" = $3
where
  "id" = $4
Copy

If you provide two arguments the first one is interpreted as the column (or other target) and the second as the value:

import { sql } from 'kysely'

const result = await db
  .updateTable('person')
  .set('first_name', 'Foo')
  // As always, both arguments can be arbitrary expressions or
  // callbacks that give you access to an expression builder:
  .set(sql<string>`address['postalCode']`, (eb) => eb.val('61710'))
  .where('id', '=', 1)
  .executeTakeFirst()
Copy

On PostgreSQL you can chain returning to the query to get the updated rows' columns (or any other expression) as the return value:

const row = await db
  .updateTable('person')
  .set({
    first_name: 'Jennifer',
    last_name: 'Aniston'
  })
  .where('id', '=', 1)
  .returning('id')
  .executeTakeFirstOrThrow()

row.id
Copy

The generated SQL (PostgreSQL):

update "person" set "first_name" = $1, "last_name" = $2 where "id" = $3 returning "id"
Copy

In addition to primitives, the values can arbitrary expressions including raw sql snippets or subqueries:

import { sql } from 'kysely'

const result = await db
  .updateTable('person')
  .set(({ selectFrom, ref, fn, eb }) => ({
    first_name: selectFrom('person').select('first_name').limit(1),
    middle_name: ref('first_name'),
    age: eb('age', '+', 1),
    last_name: sql<string>`${'Ani'} || ${'ston'}`,
  }))
  .where('id', '=', 1)
  .executeTakeFirst()

console.log(result.numUpdatedRows)
Copy