Skip to main content

Documentation Index

Fetch the complete documentation index at: https://mintlify.com/kysely-org/kysely/llms.txt

Use this file to discover all available pages before exploring further.

​
SelectQueryBuilder

Builder for constructing SELECT queries with type-safe column selection, joins, filtering, and more.

​
Type Parameters

  • DB - The database schema type
  • TB - Union of table names available in the FROM clause
  • O - The output row type (columns selected)

​
Methods

​
select

select<SE extends SelectExpression<DB, TB>>(
  selections: ReadonlyArray<SE>
): SelectQueryBuilder<DB, TB, O & Selection<DB, TB, SE>>

select<SE extends SelectExpression<DB, TB>>(
  selection: SE
): SelectQueryBuilder<DB, TB, O & Selection<DB, TB, SE>>

select<CB extends SelectCallback<DB, TB>>(
  callback: CB
): SelectQueryBuilder<DB, TB, O & CallbackSelection<DB, TB, CB>>
Adds a select statement to the query. When a column is selected, Kysely adds its type to the return type. Select calls are additive. Calling select('id').select('first_name') is the same as select(['id', 'first_name']). Examples: Select a single column: 
const persons = await db
  .selectFrom('person')
  .select('id')
  .where('first_name', '=', 'Arnold')
  .execute()
Select multiple columns: 
const persons = await db
  .selectFrom('person')
  .select(['person.id', 'first_name'])
  .execute()
Select with aliases: 
const persons = await db
  .selectFrom('person as p')
  .select([
    'first_name as fn',
    'p.last_name as ln'
  ])
  .execute()
Select complex expressions: 
import { sql } from 'kysely'

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

    // Expression builder
    or([
      eb('first_name', '=', 'Jennifer'),
      eb('first_name', '=', 'Arnold')
    ]).as('is_jennifer_or_arnold'),

    // Raw SQL
    sql<string>`concat(first_name, ' ', last_name)`.as('full_name'),
  ])
  .execute()

​
selectAll

selectAll(): SelectQueryBuilder<DB, TB, O & AllSelection<DB, TB>>

selectAll<T extends TB>(
  table: T
): SelectQueryBuilder<DB, TB, O & Selectable<DB[T]>>

selectAll<T extends TB>(
  table: ReadonlyArray<T>
): SelectQueryBuilder<DB, TB, O & AllSelection<DB, T>>
Adds a select * or select table.* clause to the query. Examples: Select all columns: 
const persons = await db
  .selectFrom('person')
  .selectAll()
  .execute()
Select all columns from a specific table: 
const persons = await db
  .selectFrom('person')
  .selectAll('person')
  .execute()
Select all columns from multiple tables: 
const personsPets = await db
  .selectFrom(['person', 'pet'])
  .selectAll(['person', 'pet'])
  .execute()

​
where

where<RE extends ReferenceExpression<DB, TB>>(
  lhs: RE,
  op: ComparisonOperatorExpression,
  rhs: OperandValueExpressionOrList<DB, TB, RE>
): SelectQueryBuilder<DB, TB, O>

where<E extends ExpressionOrFactory<DB, TB, SqlBool>>(
  expression: E
): SelectQueryBuilder<DB, TB, O>
Adds a WHERE clause to the query. Multiple where calls are combined with AND. See WhereInterface documentation for more examples.

​
whereRef

whereRef<LRE extends ReferenceExpression<DB, TB>, RRE extends ReferenceExpression<DB, TB>>(
  lhs: LRE,
  op: ComparisonOperatorExpression,
  rhs: RRE
): SelectQueryBuilder<DB, TB, O>
Adds a WHERE clause that compares two column references.

​
innerJoin

innerJoin<TE extends TableExpression<DB, TB>>(
  table: TE,
  k1: JoinReferenceExpression<DB, TB, TE>,
  k2: JoinReferenceExpression<DB, TB, TE>
): SelectQueryBuilderWithInnerJoin<DB, TB, O, TE>

innerJoin<TE extends TableExpression<DB, TB>>(
  table: TE,
  callback: JoinCallbackExpression<DB, TB, TE>
): SelectQueryBuilderWithInnerJoin<DB, TB, O, TE>
Joins another table using an inner join. Examples: Simple join: 
const result = await db
  .selectFrom('person')
  .innerJoin('pet', 'pet.owner_id', 'person.id')
  .select(['person.id', 'pet.name as pet_name'])
  .execute()
Join with callback: 
await db.selectFrom('person')
  .innerJoin(
    'pet',
    (join) => join
      .onRef('pet.owner_id', '=', 'person.id')
      .on('pet.name', '=', 'Doggo')
      .on((eb) => eb.or([
        eb('person.age', '>', 18),
        eb('person.age', '<', 100)
      ]))
  )
  .selectAll()
  .execute()
Subquery join: 
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()

​
leftJoin

leftJoin<TE extends TableExpression<DB, TB>>(
  table: TE,
  ...
): SelectQueryBuilderWithLeftJoin<DB, TB, O, TE>
Just like innerJoin but adds a left join instead of an inner join.

​
rightJoin

rightJoin<TE extends TableExpression<DB, TB>>(
  table: TE,
  ...
): SelectQueryBuilderWithRightJoin<DB, TB, O, TE>
Just like innerJoin but adds a right join instead of an inner join.

​
fullJoin

fullJoin<TE extends TableExpression<DB, TB>>(
  table: TE,
  ...
): SelectQueryBuilderWithFullJoin<DB, TB, O, TE>
Just like innerJoin but adds a full join instead of an inner join. Only supported by some dialects like PostgreSQL, MS SQL Server and SQLite.

​
orderBy

orderBy<OE extends OrderByExpression<DB, TB, O>>(
  expr: OE,
  modifiers?: OrderByModifiers
): SelectQueryBuilder<DB, TB, O>
Adds an ORDER BY clause to the query. See OrderByInterface documentation for more examples.

​
groupBy

groupBy<GE extends GroupByArg<DB, TB, O>>(
  groupBy: GE
): SelectQueryBuilder<DB, TB, O>
Adds a GROUP BY clause to the query. Example: 
import { sql } from 'kysely'

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

​
having

having<RE extends ReferenceExpression<DB, TB>>(
  lhs: RE,
  op: ComparisonOperatorExpression,
  rhs: OperandValueExpressionOrList<DB, TB, RE>
): SelectQueryBuilder<DB, TB, O>

having<E extends ExpressionOrFactory<DB, TB, SqlBool>>(
  expression: E
): SelectQueryBuilder<DB, TB, O>
Adds a HAVING clause to the query. See HavingInterface documentation for more examples.

​
limit

limit(
  limit: ValueExpression<DB, TB, number | bigint | null>
): SelectQueryBuilder<DB, TB, O>
Adds a LIMIT clause to the query. Example: 
await db
  .selectFrom('person')
  .select('first_name')
  .limit(10)
  .execute()

​
offset

offset(
  offset: ValueExpression<DB, TB, number | bigint>
): SelectQueryBuilder<DB, TB, O>
Adds an OFFSET clause to the query. Example: 
await db
  .selectFrom('person')
  .select('first_name')
  .limit(10)
  .offset(10)
  .execute()

​
distinct

distinct(): SelectQueryBuilder<DB, TB, O>
Makes the selection distinct. Example: 
const persons = await db.selectFrom('person')
  .select('first_name')
  .distinct()
  .execute()

​
distinctOn

distinctOn<RE extends ReferenceExpression<DB, TB>>(
  selection: RE
): SelectQueryBuilder<DB, TB, O>

distinctOn<RE extends ReferenceExpression<DB, TB>>(
  selections: ReadonlyArray<RE>
): SelectQueryBuilder<DB, TB, O>
Adds DISTINCT ON expressions to the select clause. Example: 
const persons = await db.selectFrom('person')
  .innerJoin('pet', 'pet.owner_id', 'person.id')
  .where('pet.name', '=', 'Doggo')
  .distinctOn('person.id')
  .selectAll('person')
  .execute()

​
union

union<E extends SetOperandExpression<DB, O>>(
  expression: E
): SelectQueryBuilder<DB, TB, O>
Combines another select query to this query using UNION. Example: 
await db.selectFrom('person')
  .select(['id', 'first_name as name'])
  .union(db.selectFrom('pet').select(['id', 'name']))
  .orderBy('name')
  .execute()

​
unionAll

unionAll<E extends SetOperandExpression<DB, O>>(
  expression: E
): SelectQueryBuilder<DB, TB, O>
Combines another select query using UNION ALL.

​
intersect

intersect<E extends SetOperandExpression<DB, O>>(
  expression: E
): SelectQueryBuilder<DB, TB, O>
Combines another select query using INTERSECT.

​
intersectAll

intersectAll<E extends SetOperandExpression<DB, O>>(
  expression: E
): SelectQueryBuilder<DB, TB, O>
Combines another select query using INTERSECT ALL.

​
except

except<E extends SetOperandExpression<DB, O>>(
  expression: E
): SelectQueryBuilder<DB, TB, O>
Combines another select query using EXCEPT.

​
exceptAll

exceptAll<E extends SetOperandExpression<DB, O>>(
  expression: E
): SelectQueryBuilder<DB, TB, O>
Combines another select query using EXCEPT ALL.

​
as

as<A extends string>(alias: A): AliasedSelectQueryBuilder<O, A>
Gives an alias for the query. Only useful for sub queries. Example: 
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

​
$call

$call<T>(func: (qb: this) => T): T
Simply calls the provided function passing this as the only argument. Example: 
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()

​
$if

$if<O2>(
  condition: boolean,
  func: (qb: this) => SelectQueryBuilder<any, any, O & O2>
): SelectQueryBuilder<DB, TB, O & Partial<Omit<O2, keyof O>>>
Call func(this) if condition is true. This method is especially handy with optional selects. Selections made inside the callback add optional fields to the result type. Example: 
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()
}

// Return type: { id: number, first_name: string, last_name?: string }

​
$castTo

$castTo<C>(): SelectQueryBuilder<DB, TB, C>
Change the output type of the query. This method doesn’t change the SQL. It simply returns a copy with a new output type.

​
$narrowType

$narrowType<T>(): SelectQueryBuilder<DB, TB, NarrowPartial<O, T>>
Narrows (parts of) the output type of the query.

​
execute

async execute(): Promise<SimplifyResult<O>[]>
Executes the query and returns an array of rows.

​
executeTakeFirst

async executeTakeFirst(): Promise<SimplifySingleResult<O>>
Executes the query and returns the first result or undefined if the query returned no result.

​
executeTakeFirstOrThrow

async executeTakeFirstOrThrow(
  errorConstructor?: NoResultErrorConstructor | ((node: QueryNode) => Error)
): Promise<SimplifyResult<O>>
Executes the query and returns the first result or throws if the query returned no result. By default an instance of NoResultError is thrown.

​
stream

async *stream(chunkSize?: number): AsyncIterableIterator<O>
Streams the query results.

​
compile

compile(): CompiledQuery<O>
Compiles the query to SQL without executing it.

​
explain

async explain<ER extends Record<string, any> = Record<string, any>>(
  format?: ExplainFormat,
  options?: Expression<any>
): Promise<ER[]>
Executes an EXPLAIN query for this query.

Build docs developers (and LLMs) love

Get started for freeTalk to us