Interface SelectQueryBuilder<DB, TB, O>

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

The generated SQL(PostgreSQL):

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

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

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

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

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

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.

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

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

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

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')
  )
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 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)
}
Copy

Into this:

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

functionThatExpectsPersonWithNonNullValue(person)
Copy

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
Copy

Clears limit clause from the query.

Examples

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

The generated SQL(PostgreSQL):

select * from "person"
Copy

Clears offset clause from the query.

Examples

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

The generated SQL(PostgreSQL):

select * from "person" limit 10
Copy

Clears all order by clauses from the query.

Examples

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

The generated SQL(PostgreSQL):

select * from "person"
Copy

Clears all select clauses from the query.

Examples

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

The generated SQL(PostgreSQL):

select "id", "gender" from "person"
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

Makes the selection distinct.

Examples

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

The generated SQL (PostgreSQL):

select distinct "first_name" from "person"
Copy

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

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
Copy

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

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

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

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')
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 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 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 for share modifier to a select query on supported databases.

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

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

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

The generated SQL (PostgreSQL):

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

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

The generated SQL (PostgreSQL):

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

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

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

The generated SQL (PostgreSQL):

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

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

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

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

The generated SQL (PostgreSQL):

select "person"."id", "pet"."name" as "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 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 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()
Copy

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

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

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

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

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

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

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

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

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

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

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

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

The generated SQL (PostgreSQL):

select "first_name"
from "person"
for update
Copy

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

The generated SQL (MySQL):

select sql_no_cache `first_name`
from `person`
Copy

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

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

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

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

The generated SQL (PostgreSQL):

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

Multiple columns/expressions per call:

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

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

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
Copy

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

The generated SQL (PostgreSQL):

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

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

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

The generated SQL (PostgreSQL):

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

Select a single column and specify a table:

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

The generated SQL (PostgreSQL):

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

Select multiple columns:

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

The generated SQL (PostgreSQL):

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

You can provide an alias for the selections by appending as the_alias to the selection.

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

The generated SQL (PostgreSQL):

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

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

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

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
Copy