Adds a where expression to the query.

Calling this method multiple times will combine the expressions using and.

Also see whereRef

Examples

where method calls are combined with AND:

const person = await db
  .selectFrom('person')
  .selectAll()
  .where('first_name', '=', 'Jennifer')
  .where('age', '>', 40)
  .executeTakeFirst()

The generated SQL (PostgreSQL):

select * from "person" where "first_name" = $1 and "age" > $2

Operator can be any supported operator or if the typings don't support it you can always use:

sql`your operator`

Find multiple items using a list of identifiers:

const persons = await db
  .selectFrom('person')
  .selectAll()
  .where('id', 'in', ['1', '2', '3'])
  .execute()

The generated SQL (PostgreSQL):

select * from "person" where "id" in ($1, $2, $3)

You can use the and function to create a simple equality filter using an object

const persons = await db
  .selectFrom('person')
  .selectAll()
  .where((eb) => eb.and({
    first_name: 'Jennifer',
    last_name: eb.ref('first_name')
  }))
  .execute()

The generated SQL (PostgreSQL):

select *
from "person"
where (
  "first_name" = $1
  and "last_name" = "first_name"
)

To combine conditions using OR, you can use the expression builder. There are two ways to create OR expressions. Both are shown in this example:

const persons = await db
  .selectFrom('person')
  .selectAll()
  // 1. Using the `or` method on the expression builder:
  .where((eb) => eb.or([
    eb('first_name', '=', 'Jennifer'),
    eb('first_name', '=', 'Sylvester')
  ]))
  // 2. Chaining expressions using the `or` method on the
  // created expressions:
  .where((eb) =>
    eb('last_name', '=', 'Aniston').or('last_name', '=', 'Stallone')
  )
  .execute()

The generated SQL (PostgreSQL):

select *
from "person"
where (
  ("first_name" = $1 or "first_name" = $2)
  and
  ("last_name" = $3 or "last_name" = $4)
)

You can add expressions conditionally like this:

import { Expression, SqlBool } from 'kysely'

const firstName: string | undefined = 'Jennifer'
const lastName: string | undefined = 'Aniston'
const under18 = true
const over60 = true

let query = db
  .selectFrom('person')
  .selectAll()

if (firstName) {
  // The query builder is immutable. Remember to reassign
  // the result back to the query variable.
  query = query.where('first_name', '=', firstName)
}

if (lastName) {
  query = query.where('last_name', '=', lastName)
}

if (under18 || over60) {
  // Conditional OR expressions can be added like this.
  query = query.where((eb) => {
    const ors: Expression<SqlBool>[] = []

    if (under18) {
      ors.push(eb('age', '<', 18))
    }

    if (over60) {
      ors.push(eb('age', '>', 60))
    }

    return eb.or(ors)
  })
}

const persons = await query.execute()

Both the first and third argument can also be arbitrary expressions like subqueries. An expression can defined by passing a function and calling the methods of the ExpressionBuilder passed to the callback:

const persons = await db
  .selectFrom('person')
  .selectAll()
  .where(
    (qb) => qb.selectFrom('pet')
      .select('pet.name')
      .whereRef('pet.owner_id', '=', 'person.id')
      .limit(1),
    '=',
    'Fluffy'
  )
  .execute()

The generated SQL (PostgreSQL):

select *
from "person"
where (
  select "pet"."name"
  from "pet"
  where "pet"."owner_id" = "person"."id"
  limit $1
) = $2

A where in query can be built by using the in operator and an array of values. The values in the array can also be expressions:

const persons = await db
  .selectFrom('person')
  .selectAll()
  .where('person.id', 'in', [100, 200, 300])
  .execute()

The generated SQL (PostgreSQL):

select * from "person" where "id" in ($1, $2, $3)

For complex where expressions you can pass in a single callback and use the ExpressionBuilder to build your expression:

const firstName = 'Jennifer'
const maxAge = 60

const persons = await db
  .selectFrom('person')
  .selectAll('person')
  .where(({ eb, or, and, not, exists, selectFrom }) => and([
    or([
      eb('first_name', '=', firstName),
      eb('age', '<', maxAge)
    ]),
    not(exists(
      selectFrom('pet')
        .select('pet.id')
        .whereRef('pet.owner_id', '=', 'person.id')
    ))
  ]))
  .execute()

The generated SQL (PostgreSQL):

select "person".*
from "person"
where (
  (
    "first_name" = $1
    or "age" < $2
  )
  and not exists (
    select "pet"."id" from "pet" where "pet"."owner_id" = "person"."id"
  )
)

If everything else fails, you can always use the sql tag as any of the arguments, including the operator:

import { sql } from 'kysely'

const persons = await db
  .selectFrom('person')
  .selectAll()
  .where(
    sql`coalesce(first_name, last_name)`,
    'like',
    '%' + name + '%',
  )
  .execute()

The generated SQL (PostgreSQL):

select * from "person"
where coalesce(first_name, last_name) like $1

In all examples above the columns were known at compile time (except for the raw sql expressions). By default kysely only allows you to refer to columns that exist in the database and can be referred to in the current query and context.

Sometimes you may want to refer to columns that come from the user input and thus are not available at compile time.

You have two options, the sql tag or db.dynamic. The example below uses both:

import { sql } from 'kysely'
const { ref } = db.dynamic

const persons = await db
  .selectFrom('person')
  .selectAll()
  .where(ref(columnFromUserInput), '=', 1)
  .where(sql.id(columnFromUserInput), '=', 2)
  .execute()

Adds a where clause where both sides of the operator are references to columns.

The normal where method treats the right hand side argument as a value by default. whereRef treats it as a column reference. This method is expecially useful with joins and correlated subqueries.

Examples

Usage with a join:

db.selectFrom(['person', 'pet'])
  .selectAll()
  .whereRef('person.first_name', '=', 'pet.name')

The generated SQL (PostgreSQL):

select * from "person", "pet" where "person"."first_name" = "pet"."name"

Usage in a subquery:

const persons = await db
  .selectFrom('person')
  .selectAll('person')
  .select((eb) => eb
    .selectFrom('pet')
    .select('name')
    .whereRef('pet.owner_id', '=', 'person.id')
    .limit(1)
    .as('pet_name')
  )
  .execute()

The generated SQL (PostgreSQL):

select "person".*, (
  select "name"
  from "pet"
  where "pet"."owner_id" = "person"."id"
  limit $1
) as "pet_name"
from "person"

Just like WhereInterface.where | where but adds a having statement instead of a where statement.

Just like WhereInterface.whereRef | whereRef but adds a having statement instead of a where statement.

Adds a select statement to the query.

When a column (or any expression) is selected, Kysely adds its type to the return type of the query. Kysely is smart enough to parse the selection names and types from aliased columns, subqueries, raw expressions etc.

Kysely only allows you to select columns and expressions that exist and would produce valid SQL. However, Kysely is not perfect and there may be cases where the type inference doesn't work and you need to override it. You can always use the Kysely.dynamic | dynamic module and the sql tag to override the types.

Select calls are additive. Calling select('id').select('first_name') is the same as calling select(['id', 'first_name']).

To select all columns of the query or specific tables see the selectAll method.

See the $if method if you are looking for a way to add selections based on a runtime condition.

Examples

Select a single column:

const persons = await db
  .selectFrom('person')
  .select('id')
  .where('first_name', '=', 'Arnold')
  .execute()

The generated SQL (PostgreSQL):

select "id" from "person" where "first_name" = $1

Select a single column and specify a table:

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

The generated SQL (PostgreSQL):

select "person"."id" from "person", "pet"

Select multiple columns:

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

The generated SQL (PostgreSQL):

select "person"."id", "first_name" from "person"

You can give an alias for selections and tables by appending as the_alias to the name:

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

The generated SQL (PostgreSQL):

select
  "first_name" as "fn",
  "p"."last_name" as "ln"
from "person" as "p"

You can select arbitrary expression including subqueries and raw sql snippets. When you do that, you need to give a name for the selections using the as method:

import { sql } from 'kysely'

const persons = await db.selectFrom('person')
  .select(({ eb, selectFrom, or }) => [
    // Select a correlated subquery
    selectFrom('pet')
      .whereRef('person.id', '=', 'pet.owner_id')
      .select('pet.name')
      .orderBy('pet.name')
      .limit(1)
      .as('first_pet_name'),

    // Build and select an expression using
    // the expression builder
    or([
      eb('first_name', '=', 'Jennifer'),
      eb('first_name', '=', 'Arnold')
    ]).as('is_jennifer_or_arnold'),

    // Select a raw sql expression
    sql<string>`concat(first_name, ' ', last_name)`.as('full_name')
  ])
  .execute()

The generated SQL (PostgreSQL):

select
  (
    select "pet"."name"
    from "pet"
    where "person"."id" = "pet"."owner_id"
    order by "pet"."name"
    limit $1
  ) as "pet_name",
  ("first_name" = $2 or "first_name" = $3) as "jennifer_or_arnold",
  concat(first_name, ' ', last_name) as "full_name"
from "person"

In case you use the sql tag you need to specify the type of the expression (in this example string).

All the examples above assume you know the column names at compile time. While it's better to build your code like that (that way you also know the types) sometimes it's not possible or you just prefer to write more dynamic code. In this example, we use the dynamic module's methods to add selections dynamically:

const { ref } = db.dynamic

// Some column name provided by the user. Value not known at compile time.
const columnFromUserInput = req.query.select;

// A type that lists all possible values `columnFromUserInput` can have.
// You can use `keyof Person` if any column of an interface is allowed.
type PossibleColumns = 'last_name' | 'first_name' | 'birth_date'

const spersons = await db
  .selectFrom('person')
  .select([
    ref<PossibleColumns>(columnFromUserInput)
    'id'
  ])
  .execute()

// The resulting type contains all `PossibleColumns` as optional fields
// because we cannot know which field was actually selected before
// running the code.
const lastName: string | undefined = persons[0].last_name
const firstName: string | undefined = persons[0].first_name
const birthDate: string | undefined = persons[0].birth_date

// The result type also contains the compile time selection `id`.
persons[0].id

Adds distinct on expressions to the select clause.

Examples

const persons = await db.selectFrom('person')
  .innerJoin('pet', 'pet.owner_id', 'person.id')
  .where('pet.name', '=', 'Doggo')
  .distinctOn('person.id')
  .selectAll('person')
  .execute()

The generated SQL (PostgreSQL):

select distinct on ("person"."id") "person".*
from "person"
inner join "pet" on "pet"."owner_id" = "person"."id"
where "pet"."name" = $1

This can be used to add any additional SQL to the front of the query after the select keyword.

Examples

db.selectFrom('person')
  .modifyFront(sql`sql_no_cache`)
  .select('first_name')
  .execute()

The generated SQL (MySQL):

select sql_no_cache `first_name`
from `person`

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

Also see forUpdate, forShare, forKeyShare, forNoKeyUpdate skipLocked and noWait.

Examples

db.selectFrom('person')
  .select('first_name')
  .modifyEnd(sql`for update`)
  .execute()

The generated SQL (PostgreSQL):

select "first_name"
from "person"
for update

Makes the selection distinct.

Examples

const persons = await db.selectFrom('person')
  .select('first_name')
  .distinct()
  .execute()

The generated SQL (PostgreSQL):

select distinct "first_name" from "person"

Adds the for update modifier to a select query on supported databases.

Adds the for share modifier to a select query on supported databases.

Adds the for key share modifier to a select query on supported databases.

Adds the for no key update modifier to a select query on supported databases.

Adds the skip locked modifier to a select query on supported databases.

Adds the nowait modifier to a select query on supported databases.

Adds a select * or select table.* clause to the query.

Examples

The selectAll method generates SELECT *:

const persons = await db
  .selectFrom('person')
  .selectAll()
  .execute()

The generated SQL (PostgreSQL):

select * from "person"

Select all columns of a table:

const persons = await db
  .selectFrom('person')
  .selectAll('person')
  .execute()

The generated SQL (PostgreSQL):

select "person".* from "person"

Select all columns of multiple tables:

const personsPets = await db
  .selectFrom(['person', 'pet'])
  .selectAll(['person', 'pet'])
  .execute()

The generated SQL (PostgreSQL):

select "person".*, "pet".* from "person", "pet"

Joins another table to the query using an inner join.

Examples

Simple inner joins can be done 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 as pet_name'])
  .execute()

The generated SQL (PostgreSQL):

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

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

The generated SQL (PostgreSQL):

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

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

The generated SQL (PostgreSQL):

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

You can join a subquery by providing two callbacks:

const result = await db.selectFrom('person')
  .innerJoin(
    (eb) => eb
      .selectFrom('pet')
      .select(['owner_id as owner', 'name'])
      .where('name', '=', 'Doggo')
      .as('doggos'),
    (join) => join
      .onRef('doggos.owner', '=', 'person.id'),
  )
  .selectAll('doggos')
  .execute()

The generated SQL (PostgreSQL):

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

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

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

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

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

Examples

db.selectFrom('person')
  .innerJoinLateral(
    (eb) =>
      eb.selectFrom('pet')
        .select('name')
        .whereRef('pet.owner_id', '=', 'person.id')
        .as('p'),
    (join) => join.onTrue()
  )
  .select(['first_name', 'p.name'])
  .orderBy('first_name')

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

Examples

db.selectFrom('person')
  .leftJoinLateral(
    (eb) =>
      eb.selectFrom('pet')
        .select('name')
        .whereRef('pet.owner_id', '=', 'person.id')
        .as('p'),
    (join) => join.onTrue()
  )
  .select(['first_name', 'p.name'])
  .orderBy('first_name')

Adds an order by clause to the query.

orderBy calls are additive. Meaning, additional orderBy calls append to the existing order by clause.

In a single call you can add a single column/expression or multiple columns/expressions.

Single column/expression calls can have 1-2 arguments. The first argument is the expression to order by (optionally including the direction) while the second optional argument is the direction (asc or desc).

Examples

Single column/expression per call:

await db
  .selectFrom('person')
  .select('person.first_name as fn')
  .orderBy('id')
  .orderBy('fn desc')
  .execute()

await db
  .selectFrom('person')
  .select('person.first_name as fn')
  .orderBy('id')
  .orderBy('fn', 'desc')
  .execute()

The generated SQL (PostgreSQL):

select "person"."first_name" as "fn"
from "person"
order by "id" asc, "fn" desc

Multiple columns/expressions per call:

await db
  .selectFrom('person')
  .select('person.first_name as fn')
  .orderBy(['id', 'fn desc'])
  .execute()

The order by expression can also be a raw sql expression or a subquery in addition to column references:

import { sql } from 'kysely'

await db
  .selectFrom('person')
  .selectAll()
  .orderBy((eb) => eb.selectFrom('pet')
    .select('pet.name')
    .whereRef('pet.owner_id', '=', 'person.id')
    .limit(1)
  )
  .orderBy(
    sql`concat(first_name, last_name)`
  )
  .execute()

The generated SQL (PostgreSQL):

select *
from "person"
order by
  ( select "pet"."name"
    from "pet"
    where "pet"."owner_id" = "person"."id"
    limit 1
  ) asc,
  concat(first_name, last_name) asc

dynamic.ref can be used to refer to columns not known at compile time:

async function someQuery(orderBy: string) {
  const { ref } = db.dynamic

  return await db
    .selectFrom('person')
    .select('person.first_name as fn')
    .orderBy(ref(orderBy))
    .execute()
}

someQuery('fn')

The generated SQL (PostgreSQL):

select "person"."first_name" as "fn"
from "person"
order by "fn" asc

Adds a group by clause to the query.

Examples

import { sql } from 'kysely'

await db
  .selectFrom('person')
  .select([
    'first_name',
    sql`max(id)`.as('max_id')
  ])
  .groupBy('first_name')
  .execute()

The generated SQL (PostgreSQL):

select "first_name", max(id)
from "person"
group by "first_name"

groupBy also accepts an array:

import { sql } from 'kysely'

await db
  .selectFrom('person')
  .select([
    'first_name',
    'last_name',
    sql`max(id)`.as('max_id')
  ])
  .groupBy([
    'first_name',
    'last_name'
  ])
  .execute()

The generated SQL (PostgreSQL):

select "first_name", "last_name", max(id)
from "person"
group by "first_name", "last_name"

The group by expressions can also be subqueries or raw sql expressions:

import { sql } from 'kysely'

await db
  .selectFrom('person')
  .select([
    'first_name',
    'last_name',
    sql`max(id)`.as('max_id')
  ])
  .groupBy([
    sql`concat(first_name, last_name)`,
    (qb) => qb.selectFrom('pet').select('id').limit(1)
  ])
  .execute()

dynamic.ref can be used to refer to columns not known at compile time:

async function someQuery(groupBy: string) {
  const { ref } = db.dynamic

  return await db
    .selectFrom('person')
    .select('first_name')
    .groupBy(ref(groupBy))
    .execute()
}

someQuery('first_name')

The generated SQL (PostgreSQL):

select "first_name"
from "person"
group by "first_name"

Adds a limit clause to the query.

Examples

Select the first 10 rows of the result:

return await db
  .selectFrom('person')
  .select('first_name')
  .limit(10)

Select rows from index 10 to index 19 of the result:

return await db
  .selectFrom('person')
  .select('first_name')
  .offset(10)
  .limit(10)

Adds an offset clause to the query.

Examples

Select rows from index 10 to index 19 of the result:

return await db
  .selectFrom('person')
  .select('first_name')
  .offset(10)
  .limit(10)

Combines another select query or raw expression to this query using union.

The output row type of the combined query must match this query.

Examples

db.selectFrom('person')
  .select(['id', 'first_name as name'])
  .union(db.selectFrom('pet').select(['id', 'name']))
  .orderBy('name')

You can provide a callback to get an expression builder. In the following example, this allows us to wrap the query in parentheses:

db.selectFrom('person')
  .select(['id', 'first_name as name'])
  .union((eb) => eb.parens(
    eb.selectFrom('pet').select(['id', 'name'])
  ))
  .orderBy('name')

Combines another select query or raw expression to this query using union all.

The output row type of the combined query must match this query.

Examples

db.selectFrom('person')
  .select(['id', 'first_name as name'])
  .unionAll(db.selectFrom('pet').select(['id', 'name']))
  .orderBy('name')

You can provide a callback to get an expression builder. In the following example, this allows us to wrap the query in parentheses:

db.selectFrom('person')
  .select(['id', 'first_name as name'])
  .unionAll((eb) => eb.parens(
    eb.selectFrom('pet').select(['id', 'name'])
  ))
  .orderBy('name')

Combines another select query or raw expression to this query using intersect.

The output row type of the combined query must match this query.

Examples

db.selectFrom('person')
  .select(['id', 'first_name as name'])
  .intersect(db.selectFrom('pet').select(['id', 'name']))
  .orderBy('name')

You can provide a callback to get an expression builder. In the following example, this allows us to wrap the query in parentheses:

db.selectFrom('person')
  .select(['id', 'first_name as name'])
  .intersect((eb) => eb.parens(
    eb.selectFrom('pet').select(['id', 'name'])
  ))
  .orderBy('name')

Combines another select query or raw expression to this query using intersect all.

The output row type of the combined query must match this query.

Examples

db.selectFrom('person')
  .select(['id', 'first_name as name'])
  .intersectAll(db.selectFrom('pet').select(['id', 'name']))
  .orderBy('name')

You can provide a callback to get an expression builder. In the following example, this allows us to wrap the query in parentheses:

db.selectFrom('person')
  .select(['id', 'first_name as name'])
  .intersectAll((eb) => eb.parens(
    eb.selectFrom('pet').select(['id', 'name'])
  ))
  .orderBy('name')

Combines another select query or raw expression to this query using except.

The output row type of the combined query must match this query.

Examples

db.selectFrom('person')
  .select(['id', 'first_name as name'])
  .except(db.selectFrom('pet').select(['id', 'name']))
  .orderBy('name')

You can provide a callback to get an expression builder. In the following example, this allows us to wrap the query in parentheses:

db.selectFrom('person')
  .select(['id', 'first_name as name'])
  .except((eb) => eb.parens(
    eb.selectFrom('pet').select(['id', 'name'])
  ))
  .orderBy('name')

Combines another select query or raw expression to this query using except all.

The output row type of the combined query must match this query.

Examples

db.selectFrom('person')
  .select(['id', 'first_name as name'])
  .exceptAll(db.selectFrom('pet').select(['id', 'name']))
  .orderBy('name')

You can provide a callback to get an expression builder. In the following example, this allows us to wrap the query in parentheses:

db.selectFrom('person')
  .select(['id', 'first_name as name'])
  .exceptAll((eb) => eb.parens(
    eb.selectFrom('pet').select(['id', 'name'])
  ))
  .orderBy('name')

Gives an alias for the query. This method is only useful for sub queries.

Examples

const pets = await db.selectFrom('pet')
  .selectAll('pet')
  .select(
    (qb) => qb.selectFrom('person')
      .select('first_name')
      .whereRef('pet.owner_id', '=', 'person.id')
      .as('owner_first_name')
  )
  .execute()

pets[0].owner_first_name

Clears all select clauses from the query.

Examples

db.selectFrom('person')
  .select(['id', 'first_name'])
  .clearSelect()
  .select(['id', 'gender'])

The generated SQL(PostgreSQL):

select "id", "gender" from "person"

Clears all where expressions from the query.

Examples

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

The generated SQL(PostgreSQL):

select * from "person"

Clears limit clause from the query.

Examples

db.selectFrom('person')
  .selectAll()
  .limit(10)
  .clearLimit()

The generated SQL(PostgreSQL):

select * from "person"

Clears offset clause from the query.

Examples

db.selectFrom('person')
  .selectAll()
  .limit(10)
  .offset(20)
  .clearOffset()

The generated SQL(PostgreSQL):

select * from "person" limit 10

Clears all order by clauses from the query.

Examples

db.selectFrom('person')
  .selectAll()
  .orderBy('id')
  .clearOrderBy()

The generated SQL(PostgreSQL):

select * from "person"

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:

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

db.selectFrom('person')
  .selectAll()
  .$call(log)
  .execute()

Call func(this) if condition is true.

NOTE: This method has an impact on typescript performance and it should only be used when necessary. Remember that you can call most methods like where conditionally like this:

let query = db.selectFrom('person').selectAll()

if (firstName) {
  query = query.where('first_name', '=', firstName)
}

if (lastName) {
  query = query.where('last_name', '=', lastName)
}

const result = await query.execute()

This method is mainly useful with optional selects. Any select or selectAll method called inside the callback add optional fields to the result type. This is because we can't know if those selections were actually made before running the code.

Also see this recipe

Examples

async function getPerson(id: number, withLastName: boolean) {
  return await db
    .selectFrom('person')
    .select(['id', 'first_name'])
    .$if(withLastName, (qb) => qb.select('last_name'))
    .where('id', '=', id)
    .executeTakeFirstOrThrow()
}

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 getPerson function is:

{
  id: number
  first_name: string
  last_name?: string
}

You can also call any other methods inside the callback:

db.selectFrom('person')
  .select('person.id')
  .$if(filterByFirstName, (qb) => qb.where('first_name', '=', firstName))
  .$if(filterByPetCount, (qb) => qb
    .innerJoin('pet', 'pet.owner_id', 'person.id')
    .having((eb) => eb.fn.count('pet.id'), '>', petCountLimit)
    .groupBy('person.id')
  )

Change the output type of the query.

You should only use this method as the last resort if the types don't support your use case.

Changes the output type from an object to a tuple.

This doesn't affect the generated SQL in any way. This function is just a necessary evil when you need to convert a query's output record type to a tuple type. Typescript doesn't currently offer tools to do this automatically (without insane hackery).

The returned object can no longer be executed. It can only be used as a subquery.

Examples

const result = await db
  .selectFrom('person')
  .selectAll('person')
  .where(({ eb, refTuple, selectFrom }) => eb(
    refTuple('first_name', 'last_name'),
    'in',
    selectFrom('pet')
      .select(['name', 'species'])
      .where('pet.species', '!=', 'cat')
      .$asTuple('name', 'species')
  ))

The generated SQL(PostgreSQL):

select
  "person".*
from
  "person"
where
  ("first_name", "last_name")
  in
  (
    select "name", "species"
    from "pet"
    where "pet"."species" != $1
  )

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 when using where, having or JoinQueryBuilder.on.

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:

const person = await db.selectFrom('person')
  .where('nullable_column', 'is not', null)
  .selectAll()
  .executeTakeFirstOrThrow()

if (person.nullable_column) {
  functionThatExpectsPersonWithNonNullValue(person)
}

Into this:

const person = await db.selectFrom('person')
  .where('nullable_column', 'is not', null)
  .selectAll()
  .$narrowType<{ nullable_column: string }>()
  .executeTakeFirstOrThrow()

functionThatExpectsPersonWithNonNullValue(person)

Giving the explicit narrowed type (string in the example above) works fine for simple types. If the type is complex, for example a JSON column or a subquery, you can use the special NotNull type to make the column not null.

import { NotNull } from 'kysely'

const person = await db.selectFrom('person')
  .where('nullable_column', 'is not', null)
  .selectAll()
  .$narrowType<{ nullable_column: NotNull }>()
  .executeTakeFirstOrThrow()

functionThatExpectsPersonWithNonNullValue(person)

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.

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

const result = await db
  .with('first_and_last', (qb) => qb
    .selectFrom('person')
    .select(['first_name', 'last_name'])
    .$assertType<{ first_name: string, last_name: string }>()
  )
  .with('age', (qb) => qb
    .selectFrom('person')
    .select('age')
    .$assertType<{ age: number }>()
  )
  .selectFrom(['first_and_last', 'age'])
  .selectAll()
  .executeTakeFirstOrThrow()

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

Creates the OperationNode that describes how to compile this expression into SQL.

If you are creating a custom expression, it's often easiest to use the sql template tag to build the node:

class SomeExpression<T> implements Expression<T> {
  toOperationNode(): OperationNode {
    return sql`some sql here`.toOperationNode()
  }
}

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 to throw a different error.

Executes the query and streams the rows.

The optional argument chunkSize defines how many rows to fetch from the database at a time. It only affects some dialects like PostgreSQL that support it.

Examples

const stream = db.
  .selectFrom('person')
  .select(['first_name', 'last_name'])
  .where('gender', '=', 'other')
  .stream()

for await (const person of stream) {
  console.log(person.first_name)

  if (person.last_name === 'Something') {
    // Breaking or returning before the stream has ended will release
    // the database connection and invalidate the stream.
    break
  }
}

Executes query with explain statement before the main query.

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

The generated SQL (MySQL):

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

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`)

The generated SQL (PostgreSQL):

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