Interface SelectQueryBuilder<DB, TB, O>

Plucks the value type of the output record.

In SQL, any record type that only has one column can be used as a scalar. For example a query like this works:

select
  id,
  first_name
from
  person as p
where
  -- This is ok since the query only selects one row
  -- and one column.
 (select name from pet where pet.owner_id = p.id limit 1) = 'Doggo'
Copy

In many cases Kysely handles this automatically and picks the correct scalar type instead of the record type, but sometimes you need to give Kysely a hint.

One such case are custom helper functions that take Expression<T> instances as inputs:

import type { Expression } from 'kysely'

function doStuff(expr: Expression<string>) {
  // ...
}

// Error! This is not ok because the expression type is
// `{ first_name: string }` instead of `string`.
// doStuff(db.selectFrom('person').select('first_name'))

// Ok! This is ok since we've plucked the `string` type of the
// only column in the output type.
doStuff(db.selectFrom('person').select('first_name').$asScalar())
Copy

This function has absolutely no effect on the generated SQL. It's purely a type-level helper.

This method returns an ExpressionWrapper instead of a SelectQueryBuilder since the return value should only be used as a part of an expression and never executed as the main query.

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 | null }>()
  )
  .with('age', (qb) => qb
    .selectFrom('person')
    .select('age')
    .$assertType<{ age: number | null }>()
  )
  .selectFrom(['first_and_last', 'age'])
  .selectAll()
  .executeTakeFirstOrThrow()
Copy

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

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'

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

await db.selectFrom('person')
  .selectAll()
  .$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 SelectQueryBuilder with a new output type.

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:

async function getPeople(firstName?: string, lastName?: string) {
  let query = db.selectFrom('person').selectAll()

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

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

  return 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:

Promise<{
  id: number
  first_name: string
  last_name?: string
}>
Copy

You can also call any other methods inside the callback:

async function getPeople(firstName?: string, petCountLimit?: number) {
  return await db.selectFrom('person')
    .select('person.id')
    .$if(firstName != null, (qb) => qb.where('first_name', '=', firstName!))
    .$if(petCountLimit != null, (qb) => qb
      .innerJoin('pet', 'pet.owner_id', 'person.id')
      .having((eb) => eb.fn.count('pet.id'), '>', petCountLimit!)
      .groupBy('person.id')
    )
    .execute()
}
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:

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

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

functionThatExpectsPersonWithNonNullValue(person)
Copy

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

The generated SQL (PostgreSQL):

select "pet".*, (
  select "first_name"
  from "person"
  where "pet"."owner_id" = "person"."id"
) as "owner_first_name"
from "pet"
Copy

Clears group by clause from the query.

Examples

await db.selectFrom('person')
  .selectAll()
  .groupBy('id')
  .clearGroupBy()
  .execute()
Copy

The generated SQL(PostgreSQL):

select * from "person"
Copy

Clears limit clause from the query.

Examples

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

The generated SQL(PostgreSQL):

select * from "person"
Copy

Clears offset clause from the query.

Examples

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

The generated SQL(PostgreSQL):

select * from "person" limit 10
Copy

Clears all order by clauses from the query.

Examples

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

The generated SQL(PostgreSQL):

select * from "person"
Copy

Clears all select clauses from the query.

Examples

await db.selectFrom('person')
  .select(['id', 'first_name'])
  .clearSelect()
  .select(['id', 'gender'])
  .execute()
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

Joins another table to the query using a cross apply.

This is only supported by some dialects like MS SQL Server.

Examples

await db.selectFrom('person')
  .crossApply(
    (eb) =>
      eb.selectFrom('pet')
        .select('name')
        .whereRef('pet.owner_id', '=', 'person.id')
        .as('p')
  )
  .select(['first_name', 'p.name'])
  .orderBy('first_name')
  .execute()
Copy

The generated SQL (MS SQL Server):

select "person"."first_name", "p"."name"
from "person"
cross apply (
  select "name"
  from "pet"
  where "pet"."owner_id" = "person"."id"
) as "p"
order by "first_name"
Copy

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

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

This is only supported by some dialects like PostgreSQL.

Examples

await db.selectFrom('person')
  .crossJoinLateral(
    (eb) =>
      eb.selectFrom('pet')
        .select('name')
        .whereRef('pet.owner_id', '=', 'person.id')
        .as('p')
  )
  .select(['first_name', 'p.name'])
  .orderBy('first_name')
  .execute()
Copy

The generated SQL (PostgreSQL):

select "person"."first_name", "p"."name"
from "person"
cross join lateral (
  select "name"
  from "pet"
  where "pet"."owner_id" = "person"."id"
) as "p"
order by "first_name"
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

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