Class SelectQueryBuilder<DB, 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.

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.

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

Simply calls the given function passing this as the only argument.

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

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

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

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

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:

const { count } = db.fn

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(count('pet.id'), '>', petCountLimit)
    .groupBy('person.id')
  )

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

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

Deprecated

Use $assertType instead.

Deprecated

Use $call instead

Deprecated

Use $castTo instead.

Clears limit clause from the query.

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

The generated SQL(PostgreSQL):

select * from "person"

Clears offset clause from the query.

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.

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

The generated SQL(PostgreSQL):

select * from "person"

Clears all select clauses from the query.

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

The generated SQL(PostgreSQL):

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

Clears all where clauses from the query.

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

The generated SQL(PostgreSQL):

select * from "person"

Makes the selection distinct.

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

The generated SQL (PostgreSQL):

select distinct "first_name" from "person"

Adds distinct on expressions to the select clause.

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

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

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

db.selectFrom('person')
  .select(['id', 'first_name as name'])
  .except(db.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.

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

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 as the only argument to throw a different error.

Executes query with explain statement before select keyword.

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

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.

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"

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

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

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

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

Deprecated

Use $if instead

Joins another table to the query using an inner join.

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

The generated SQL (PostgreSQL):

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

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

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"

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

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

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

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

db.selectFrom('person')
  .select(['id', 'first_name as name'])
  .intersect(db.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.

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

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.

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 a limit clause to the query.

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)

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

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

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

The generated SQL (PostgreSQL):

select "first_name"
from "person"
for update

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

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

The generated SQL (MySQL):

select sql_no_cache `first_name`
from `person`

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

Adds an offset clause to the query.

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

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

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

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

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

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

Adds an or where clause to the query. Otherwise works just like where.

It's often necessary to wrap or where clauses in parentheses to control precendence. You can use the one argument version of the where method for that. See the examples.

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

The generated SQL (PostgreSQL):

select * from "person" where "id" = 1 or "id" = 2

Grouping with parentheses:

const persons = await db
  .selectFrom('person')
  .selectAll()
  .where((qb) => qb
    .where('id', '=', 1)
    .orWhere('id', '=', 2)
  )
  .execute()

The generated SQL (PostgreSQL):

select * from "person" (where "id" = 1 or "id" = 2)

Even the first where can be an orWhere. This is useful if you are looping through a set of conditions:

const persons = await db
  .selectFrom('person')
  .selectAll()
  .where((qb) => qb
    .orWhere('id', '=', 1)
    .orWhere('id', '=', 2)
  )
  .execute()

The generated SQL (PostgreSQL):

select * from "person" (where "id" = 1 or "id" = 2)

Just like whereExists but creates an or exists clause.

Just like whereExists but creates an or not exists clause.

Adds an or where clause to the query. Otherwise works just like whereRef.

Also see orWhere and where.

Adds an order by clause to the query.

orderBy calls are additive. To order by multiple columns, call orderBy multiple times.

The first argument is the expression to order by and the second is the order (asc or desc).

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

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((qb) => qb.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')