Class QueryCreator<DB>

Creates a delete query.

See the where method for examples on how to specify a where clause for the delete operation.

The return value of the query is an instance of DeleteResult.

Examples

Delete a single row:

const result = await db
  .deleteFrom('person')
  .where('person.id', '=', '1')
  .executeTakeFirst()

console.log(result.numDeletedRows)
Copy

The generated SQL (PostgreSQL):

delete from "person" where "person"."id" = $1
Copy

Some databases such as MySQL support deleting from multiple tables:

const result = await db
  .deleteFrom(['person', 'pet'])
  .using('person')
  .innerJoin('pet', 'pet.owner_id', '=', 'person.id')
  .where('person.id', '=', 1)
  .executeTakeFirst()
Copy

The generated SQL (MySQL):

delete from `person`, `pet`
using `person`
inner join `pet` on `pet`.`owner_id` = `person`.`id`
where `person`.`id` = ?
Copy

Creates an insert query.

The return value of this query is an instance of InsertResult. InsertResult has the insertId field that holds the auto incremented id of the inserted row if the db returned one.

See the values method for more info and examples. Also see the returning method for a way to return columns on supported databases like PostgreSQL.

Examples

const result = await db
  .insertInto('person')
  .values({
    first_name: 'Jennifer',
    last_name: 'Aniston'
  })
  .executeTakeFirst()

console.log(result.insertId)
Copy

Some databases like PostgreSQL support the returning method:

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

Creates a merge query.

The return value of the query is a MergeResult.

See the using method for examples on how to specify the other table.

Examples

const result = await db
  .mergeInto('person')
  .using('pet', 'pet.owner_id', 'person.id')
  .whenMatched((and) => and('has_pets', '!=', 'Y'))
  .thenUpdateSet({ has_pets: 'Y' })
  .whenNotMatched()
  .thenDoNothing()
  .executeTakeFirstOrThrow()

console.log(result.numChangedRows)
Copy

The generated SQL (PostgreSQL):

merge into "person"
using "pet" on "pet"."owner_id" = "person"."id"
when matched and "has_pets" != $1 then
  update set "has_pets" = $2
when not matched then
  do nothing
Copy

Creates a replace query.

A MySQL-only statement similar to onDuplicateKeyUpdate that deletes and inserts values on collision instead of updating existing rows.

The return value of this query is an instance of InsertResult. InsertResult has the insertId field that holds the auto incremented id of the inserted row if the db returned one.

See the values method for more info and examples.

Examples

const result = await db
  .replaceInto('person')
  .values({
    first_name: 'Jennifer',
    last_name: 'Aniston'
  })
  .executeTakeFirst()

console.log(result.insertId)
Copy

Creates a select query builder for the given table or tables.

The tables passed to this method are built as the query's from clause.

Examples

Create a select query for one table:

db.selectFrom('person').selectAll()
Copy

The generated SQL (PostgreSQL):

select * from "person"
Copy

Create a select query for one table with an alias:

const persons = await db.selectFrom('person as p')
  .select(['p.id', 'first_name'])
  .execute()

console.log(persons[0].id)
Copy

The generated SQL (PostgreSQL):

select "p"."id", "first_name" from "person" as "p"
Copy

Create a select query from a subquery:

const persons = await db.selectFrom(
    (eb) => eb.selectFrom('person').select('person.id as identifier').as('p')
  )
  .select('p.identifier')
  .execute()

console.log(persons[0].identifier)
Copy

The generated SQL (PostgreSQL):

select "p"."identifier",
from (
  select "person"."id" as "identifier" from "person"
) as p
Copy

Create a select query from raw sql:

import { sql } from 'kysely'

const items = await db
  .selectFrom(sql<{ one: number }>`(select 1 as one)`.as('q'))
  .select('q.one')
  .execute()

console.log(items[0].one)
Copy

The generated SQL (PostgreSQL):

select "q"."one",
from (
  select 1 as one
) as q
Copy

When you use the sql tag you need to also provide the result type of the raw snippet / query so that Kysely can figure out what columns are available for the rest of the query.

The selectFrom method also accepts an array for multiple tables. All the above examples can also be used in an array.

import { sql } from 'kysely'

const items = await db.selectFrom([
    'person as p',
    db.selectFrom('pet').select('pet.species').as('a'),
    sql<{ one: number }>`(select 1 as one)`.as('q')
  ])
  .select(['p.id', 'a.species', 'q.one'])
  .execute()
Copy

The generated SQL (PostgreSQL):

select "p".id, "a"."species", "q"."one"
from
  "person" as "p",
  (select "pet"."species" from "pet") as a,
  (select 1 as one) as "q"
Copy

Creates a select query builder without a from clause.

If you want to create a select from query, use the selectFrom method instead. This one can be used to create a plain select statement without a from clause.

This method accepts the same inputs as select. See its documentation for more examples.

Examples

const result = db.selectNoFrom((eb) => [
  eb.selectFrom('person')
    .select('id')
    .where('first_name', '=', 'Jennifer')
    .limit(1)
    .as('jennifer_id'),

  eb.selectFrom('pet')
    .select('id')
    .where('name', '=', 'Doggo')
    .limit(1)
    .as('doggo_id')
  ])
  .executeTakeFirstOrThrow()

console.log(result.jennifer_id)
console.log(result.doggo_id)
Copy

The generated SQL (PostgreSQL):

select (
  select "id"
  from "person"
  where "first_name" = $1
  limit $2
) as "jennifer_id", (
  select "id"
  from "pet"
  where "name" = $3
  limit $4
) as "doggo_id"
Copy

Creates an update query.

See the where method for examples on how to specify a where clause for the update operation.

See the set method for examples on how to specify the updates.

The return value of the query is an UpdateResult.

Examples

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

console.log(result.numUpdatedRows)
Copy

Creates a with query (Common Table Expression).

Examples

await db
  .with('jennifers', (db) => db
    .selectFrom('person')
    .where('first_name', '=', 'Jennifer')
    .select(['id', 'age'])
  )
  .with('adult_jennifers', (db) => db
    .selectFrom('jennifers')
    .where('age', '>', 18)
    .select(['id', 'age'])
  )
  .selectFrom('adult_jennifers')
  .where('age', '<', 60)
  .selectAll()
  .execute()
Copy

The CTE name can optionally specify column names in addition to a name. In that case Kysely requires the expression to retun rows with the same columns.

await db
  .with('jennifers(id, age)', (db) => db
    .selectFrom('person')
    .where('first_name', '=', 'Jennifer')
    // This is ok since we return columns with the same
    // names as specified by `jennifers(id, age)`.
    .select(['id', 'age'])
  )
  .selectFrom('jennifers')
  .selectAll()
  .execute()
Copy

The first argument can also be a callback. The callback is passed a CTEBuilder instance that can be used to configure the CTE:

await db
  .with(
    (cte) => cte('jennifers').materialized(),
    (db) => db
      .selectFrom('person')
      .where('first_name', '=', 'Jennifer')
      .select(['id', 'age'])
  )
  .selectFrom('jennifers')
  .selectAll()
  .execute()
Copy

Returns a copy of this query creator instance with the given plugin installed.

Creates a recursive with query (Common Table Expression).

Note that recursiveness is a property of the whole with statement. You cannot have recursive and non-recursive CTEs in a same with statement. Therefore the recursiveness is determined by the first with or withRecusive call you make.

See the with method for examples and more documentation.

Sets the schema to be used for all table references that don't explicitly specify a schema.

This only affects the query created through the builder returned from this method and doesn't modify the db instance.

See this recipe for a more detailed explanation.

Examples

await db
  .withSchema('mammals')
  .selectFrom('pet')
  .selectAll()
  .innerJoin('public.person', 'public.person.id', 'pet.owner_id')
  .execute()
Copy

The generated SQL (PostgreSQL):

select * from "mammals"."pet"
inner join "public"."person"
on "public"."person"."id" = "mammals"."pet"."owner_id"
Copy

withSchema is smart enough to not add schema for aliases, common table expressions or other places where the schema doesn't belong to:

await db
  .withSchema('mammals')
  .selectFrom('pet as p')
  .select('p.name')
  .execute()
Copy

The generated SQL (PostgreSQL):

select "p"."name" from "mammals"."pet" as "p"
Copy

Returns a copy of this query creator instance without any plugins.