kysely
    Preparing search index...

    Interface SelectQueryBuilder<DB, TB, O>

    interface SelectQueryBuilder<DB, TB extends keyof DB, O> {
        get expressionType(): undefined | T;
        get isSelectQueryBuilder(): true;
        $asScalar<K extends string | number | symbol = keyof O>(): ExpressionWrapper<
            DB,
            TB,
            O[K],
        >;
        $assertType<T>(): O extends T
            ? SelectQueryBuilder<DB, TB, T>
            : KyselyTypeError<
                "$assertType() call failed: The type passed in is not equal to the output type of the query.",
            >;
        $asTuple<
            K1 extends string
            | number
            | symbol,
            K2 extends string | number | symbol,
        >(
            key1: K1,
            key2: K2,
        ): keyof O extends K1
        | K2
            ? ExpressionWrapper<DB, TB, [O[K1], O[K2]]>
            : KyselyTypeError<
                "$asTuple() call failed: All selected columns must be provided as arguments",
            >;
        $asTuple<
            K1 extends string
            | number
            | symbol,
            K2 extends string | number | symbol,
            K3 extends string | number | symbol,
        >(
            key1: K1,
            key2: K2,
            key3: K3,
        ): keyof O extends K1
        | K2
        | K3
            ? ExpressionWrapper<DB, TB, [O[K1], O[K2], O[K3]]>
            : KyselyTypeError<
                "$asTuple() call failed: All selected columns must be provided as arguments",
            >;
        $asTuple<
            K1 extends string
            | number
            | symbol,
            K2 extends string | number | symbol,
            K3 extends string | number | symbol,
            K4 extends string | number | symbol,
        >(
            key1: K1,
            key2: K2,
            key3: K3,
            key4: K4,
        ): keyof O extends K1
        | K2
        | K3
        | K4
            ? ExpressionWrapper<DB, TB, [O[K1], O[K2], O[K3], O[K4]]>
            : KyselyTypeError<
                "$asTuple() call failed: All selected columns must be provided as arguments",
            >;
        $asTuple<
            K1 extends string
            | number
            | symbol,
            K2 extends string | number | symbol,
            K3 extends string | number | symbol,
            K4 extends string | number | symbol,
            K5 extends string | number | symbol,
        >(
            key1: K1,
            key2: K2,
            key3: K3,
            key4: K4,
            key5: K5,
        ): keyof O extends K1
        | K2
        | K3
        | K4
        | K5
            ? ExpressionWrapper<DB, TB, [O[K1], O[K2], O[K3], O[K4], O[K5]]>
            : KyselyTypeError<
                "$asTuple() call failed: All selected columns must be provided as arguments",
            >;
        $call<T>(func: (qb: this) => T): T;
        $castTo<C>(): SelectQueryBuilder<DB, TB, C>;
        $if<O2>(
            condition: boolean,
            func: (qb: this) => SelectQueryBuilder<any, any, O & O2>,
        ): SelectQueryBuilder<DB, TB, O & Partial<Omit<O2, keyof O>>>;
        $narrowType<T>(): SelectQueryBuilder<DB, TB, NarrowPartial<O, T>>;
        as<A extends string>(alias: A): AliasedSelectQueryBuilder<O, A>;
        clearGroupBy(): SelectQueryBuilder<DB, TB, O>;
        clearLimit(): SelectQueryBuilder<DB, TB, O>;
        clearOffset(): SelectQueryBuilder<DB, TB, O>;
        clearOrderBy(): SelectQueryBuilder<DB, TB, O>;
        clearSelect(): SelectQueryBuilder<DB, TB, {}>;
        clearWhere(): SelectQueryBuilder<DB, TB, O>;
        compile(): CompiledQuery<Simplify<O>>;
        crossApply<
            TE extends
                | string
                | AliasedExpression<any, any>
                | AliasedDynamicTableBuilder<any, any>
                | AliasedExpressionFactory<DB, TB>,
        >(
            table: TE,
        ): SelectQueryBuilderWithInnerJoin<DB, TB, O, TE>;
        crossJoin<
            TE extends
                | string
                | AliasedExpression<any, any>
                | AliasedDynamicTableBuilder<any, any>
                | AliasedExpressionFactory<DB, TB>,
        >(
            table: TE,
        ): SelectQueryBuilderWithInnerJoin<DB, TB, O, TE>;
        crossJoinLateral<
            TE extends
                | string
                | AliasedExpression<any, any>
                | AliasedDynamicTableBuilder<any, any>
                | AliasedExpressionFactory<DB, TB>,
        >(
            table: TE,
        ): SelectQueryBuilderWithInnerJoin<DB, TB, O, TE>;
        distinct(): SelectQueryBuilder<DB, TB, O>;
        distinctOn<
            RE extends
                | string
                | Expression<any>
                | DynamicReferenceBuilder<any>
                | SelectQueryBuilderExpression<Record<string, any>>
                | OperandExpressionFactory<DB, TB, any>,
        >(
            selections: readonly RE[],
        ): SelectQueryBuilder<DB, TB, O>;
        distinctOn<
            RE extends
                | string
                | Expression<any>
                | DynamicReferenceBuilder<any>
                | SelectQueryBuilderExpression<Record<string, any>>
                | OperandExpressionFactory<DB, TB, any>,
        >(
            selection: RE,
        ): SelectQueryBuilder<DB, TB, O>;
        except<E extends SetOperandExpression<DB, O>>(
            expression: E,
        ): SelectQueryBuilder<DB, TB, O>;
        exceptAll<E extends SetOperandExpression<DB, O>>(
            expression: E,
        ): SelectQueryBuilder<DB, TB, O>;
        execute(): Promise<Simplify<O>[]>;
        executeTakeFirst(): Promise<SimplifySingleResult<O>>;
        executeTakeFirstOrThrow(
            errorConstructor?:
                | NoResultErrorConstructor
                | ((node: QueryNode) => Error),
        ): Promise<Simplify<O>>;
        explain<ER extends Record<string, any> = Record<string, any>>(
            format?: ExplainFormat,
            options?: Expression<any>,
        ): Promise<ER[]>;
        fetch(
            rowCount: number | bigint,
            modifier?: FetchModifier,
        ): SelectQueryBuilder<DB, TB, O>;
        forKeyShare(of?: TableOrList<TB>): SelectQueryBuilder<DB, TB, O>;
        forNoKeyUpdate(of?: TableOrList<TB>): SelectQueryBuilder<DB, TB, O>;
        forShare(of?: TableOrList<TB>): SelectQueryBuilder<DB, TB, O>;
        forUpdate(of?: TableOrList<TB>): SelectQueryBuilder<DB, TB, O>;
        fullJoin<
            TE extends
                | string
                | AliasedExpression<any, any>
                | AliasedDynamicTableBuilder<any, any>
                | AliasedExpressionFactory<DB, TB>,
            K1 extends string,
            K2 extends string,
        >(
            table: TE,
            k1: K1,
            k2: K2,
        ): SelectQueryBuilderWithFullJoin<DB, TB, O, TE>;
        fullJoin<
            TE extends
                | string
                | AliasedExpression<any, any>
                | AliasedDynamicTableBuilder<any, any>
                | AliasedExpressionFactory<DB, TB>,
            FN extends JoinCallbackExpression<DB, TB, TE>,
        >(
            table: TE,
            callback: FN,
        ): SelectQueryBuilderWithFullJoin<DB, TB, O, TE>;
        groupBy<
            GE extends
                | string
                | Expression<any>
                | DynamicReferenceBuilder<any>
                | SelectQueryBuilderExpression<Record<string, any>>
                | OperandExpressionFactory<DB, TB, any>
                | readonly GroupByExpression<DB, TB, O>[]
                | (
                    (
                        eb: ExpressionBuilder<DB, TB>,
                    ) => readonly GroupByExpression<DB, TB, O>[]
                ),
        >(
            groupBy: GE,
        ): SelectQueryBuilder<DB, TB, O>;
        having<
            RE extends
                | string
                | Expression<any>
                | DynamicReferenceBuilder<any>
                | SelectQueryBuilderExpression<Record<string, any>>
                | OperandExpressionFactory<DB, TB, any>,
            VE extends any,
        >(
            lhs: RE,
            op: ComparisonOperatorExpression,
            rhs: VE,
        ): SelectQueryBuilder<DB, TB, O>;
        having<E extends ExpressionOrFactory<DB, TB, SqlBool>>(
            expression: E,
        ): SelectQueryBuilder<DB, TB, O>;
        havingRef<
            LRE extends
                | string
                | Expression<any>
                | DynamicReferenceBuilder<any>
                | SelectQueryBuilderExpression<Record<string, any>>
                | OperandExpressionFactory<DB, TB, any>,
            RRE extends
                | string
                | Expression<any>
                | DynamicReferenceBuilder<any>
                | SelectQueryBuilderExpression<Record<string, any>>
                | OperandExpressionFactory<DB, TB, any>,
        >(
            lhs: LRE,
            op: ComparisonOperatorExpression,
            rhs: RRE,
        ): SelectQueryBuilder<DB, TB, O>;
        innerJoin<
            TE extends
                | string
                | AliasedExpression<any, any>
                | AliasedDynamicTableBuilder<any, any>
                | AliasedExpressionFactory<DB, TB>,
            K1 extends string,
            K2 extends string,
        >(
            table: TE,
            k1: K1,
            k2: K2,
        ): SelectQueryBuilderWithInnerJoin<DB, TB, O, TE>;
        innerJoin<
            TE extends
                | string
                | AliasedExpression<any, any>
                | AliasedDynamicTableBuilder<any, any>
                | AliasedExpressionFactory<DB, TB>,
            FN extends JoinCallbackExpression<DB, TB, TE>,
        >(
            table: TE,
            callback: FN,
        ): SelectQueryBuilderWithInnerJoin<DB, TB, O, TE>;
        innerJoinLateral<
            TE extends
                | string
                | AliasedExpression<any, any>
                | AliasedDynamicTableBuilder<any, any>
                | AliasedExpressionFactory<DB, TB>,
            K1 extends string,
            K2 extends string,
        >(
            table: TE,
            k1: K1,
            k2: K2,
        ): SelectQueryBuilderWithInnerJoin<DB, TB, O, TE>;
        innerJoinLateral<
            TE extends
                | string
                | AliasedExpression<any, any>
                | AliasedDynamicTableBuilder<any, any>
                | AliasedExpressionFactory<DB, TB>,
            FN extends JoinCallbackExpression<DB, TB, TE>,
        >(
            table: TE,
            callback: FN,
        ): SelectQueryBuilderWithInnerJoin<DB, TB, O, TE>;
        intersect<E extends SetOperandExpression<DB, O>>(
            expression: E,
        ): SelectQueryBuilder<DB, TB, O>;
        intersectAll<E extends SetOperandExpression<DB, O>>(
            expression: E,
        ): SelectQueryBuilder<DB, TB, O>;
        leftJoin<
            TE extends
                | string
                | AliasedExpression<any, any>
                | AliasedDynamicTableBuilder<any, any>
                | AliasedExpressionFactory<DB, TB>,
            K1 extends string,
            K2 extends string,
        >(
            table: TE,
            k1: K1,
            k2: K2,
        ): SelectQueryBuilderWithLeftJoin<DB, TB, O, TE>;
        leftJoin<
            TE extends
                | string
                | AliasedExpression<any, any>
                | AliasedDynamicTableBuilder<any, any>
                | AliasedExpressionFactory<DB, TB>,
            FN extends JoinCallbackExpression<DB, TB, TE>,
        >(
            table: TE,
            callback: FN,
        ): SelectQueryBuilderWithLeftJoin<DB, TB, O, TE>;
        leftJoinLateral<
            TE extends
                | string
                | AliasedExpression<any, any>
                | AliasedDynamicTableBuilder<any, any>
                | AliasedExpressionFactory<DB, TB>,
            K1 extends string,
            K2 extends string,
        >(
            table: TE,
            k1: K1,
            k2: K2,
        ): SelectQueryBuilderWithLeftJoin<DB, TB, O, TE>;
        leftJoinLateral<
            TE extends
                | string
                | AliasedExpression<any, any>
                | AliasedDynamicTableBuilder<any, any>
                | AliasedExpressionFactory<DB, TB>,
            FN extends JoinCallbackExpression<DB, TB, TE>,
        >(
            table: TE,
            callback: FN,
        ): SelectQueryBuilderWithLeftJoin<DB, TB, O, TE>;
        limit(
            limit: ValueExpression<DB, TB, null | number | bigint>,
        ): SelectQueryBuilder<DB, TB, O>;
        modifyEnd(modifier: Expression<any>): SelectQueryBuilder<DB, TB, O>;
        modifyFront(modifier: Expression<any>): SelectQueryBuilder<DB, TB, O>;
        noWait(): SelectQueryBuilder<DB, TB, O>;
        offset(
            offset: ValueExpression<DB, TB, number | bigint>,
        ): SelectQueryBuilder<DB, TB, O>;
        orderBy<
            OE extends
                | string
                | Expression<any>
                | DynamicReferenceBuilder<any>
                | SelectQueryBuilderExpression<Record<string, any>>
                | OperandExpressionFactory<DB, TB, any>,
        >(
            expr: OE,
            modifiers?: OrderByModifiers,
        ): SelectQueryBuilder<DB, TB, O>;
        orderBy<
            OE extends
                | string
                | Expression<any>
                | DynamicReferenceBuilder<any>
                | SelectQueryBuilderExpression<Record<string, any>>
                | OperandExpressionFactory<DB, TB, any>,
        >(
            exprs: readonly OE[],
        ): SelectQueryBuilder<DB, TB, O>;
        orderBy<
            OE extends
                | `${string} desc`
                | `${string} asc`
                | `${string}.${string} desc`
                | `${string}.${string} asc`,
        >(
            expr: OE,
        ): SelectQueryBuilder<DB, TB, O>;
        orderBy<
            OE extends
                | string
                | Expression<any>
                | DynamicReferenceBuilder<any>
                | SelectQueryBuilderExpression<Record<string, any>>
                | OperandExpressionFactory<DB, TB, any>,
        >(
            expr: OE,
            modifiers: Expression<any>,
        ): SelectQueryBuilder<DB, TB, O>;
        outerApply<
            TE extends
                | string
                | AliasedExpression<any, any>
                | AliasedDynamicTableBuilder<any, any>
                | AliasedExpressionFactory<DB, TB>,
        >(
            table: TE,
        ): SelectQueryBuilderWithLeftJoin<DB, TB, O, TE>;
        rightJoin<
            TE extends
                | string
                | AliasedExpression<any, any>
                | AliasedDynamicTableBuilder<any, any>
                | AliasedExpressionFactory<DB, TB>,
            K1 extends string,
            K2 extends string,
        >(
            table: TE,
            k1: K1,
            k2: K2,
        ): SelectQueryBuilderWithRightJoin<DB, TB, O, TE>;
        rightJoin<
            TE extends
                | string
                | AliasedExpression<any, any>
                | AliasedDynamicTableBuilder<any, any>
                | AliasedExpressionFactory<DB, TB>,
            FN extends JoinCallbackExpression<DB, TB, TE>,
        >(
            table: TE,
            callback: FN,
        ): SelectQueryBuilderWithRightJoin<DB, TB, O, TE>;
        select<
            SE extends
                | string
                | AliasedExpression<any, any>
                | DynamicReferenceBuilder<any>
                | AliasedExpressionFactory<DB, TB>,
        >(
            selections: readonly 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>>;
        select<
            SE extends
                | string
                | AliasedExpression<any, any>
                | DynamicReferenceBuilder<any>
                | AliasedExpressionFactory<DB, TB>,
        >(
            selection: SE,
        ): SelectQueryBuilder<DB, TB, O & Selection<DB, TB, SE>>;
        selectAll<T extends string | number | symbol>(
            table: readonly T[],
        ): SelectQueryBuilder<DB, TB, O & AllSelection<DB, T>>;
        selectAll<T extends string | number | symbol>(
            table: T,
        ): SelectQueryBuilder<DB, TB, O & Selectable<DB[T]>>;
        selectAll(): SelectQueryBuilder<DB, TB, O & AllSelection<DB, TB>>;
        skipLocked(): SelectQueryBuilder<DB, TB, O>;
        stream(chunkSize?: number): AsyncIterableIterator<O>;
        toOperationNode(): SelectQueryNode;
        top(
            expression: number | bigint,
            modifiers?: TopModifier,
        ): SelectQueryBuilder<DB, TB, O>;
        union<E extends SetOperandExpression<DB, O>>(
            expression: E,
        ): SelectQueryBuilder<DB, TB, O>;
        unionAll<E extends SetOperandExpression<DB, O>>(
            expression: E,
        ): SelectQueryBuilder<DB, TB, O>;
        where<
            RE extends
                | string
                | Expression<any>
                | DynamicReferenceBuilder<any>
                | SelectQueryBuilderExpression<Record<string, any>>
                | OperandExpressionFactory<DB, TB, any>,
            VE extends any,
        >(
            lhs: RE,
            op: ComparisonOperatorExpression,
            rhs: VE,
        ): SelectQueryBuilder<DB, TB, O>;
        where<E extends ExpressionOrFactory<DB, TB, SqlBool>>(
            expression: E,
        ): SelectQueryBuilder<DB, TB, O>;
        whereRef<
            LRE extends
                | string
                | Expression<any>
                | DynamicReferenceBuilder<any>
                | SelectQueryBuilderExpression<Record<string, any>>
                | OperandExpressionFactory<DB, TB, any>,
            RRE extends
                | string
                | Expression<any>
                | DynamicReferenceBuilder<any>
                | SelectQueryBuilderExpression<Record<string, any>>
                | OperandExpressionFactory<DB, TB, any>,
        >(
            lhs: LRE,
            op: ComparisonOperatorExpression,
            rhs: RRE,
        ): SelectQueryBuilder<DB, TB, O>;
        withPlugin(plugin: KyselyPlugin): SelectQueryBuilder<DB, TB, O>;
    }

    Type Parameters

    • DB
    • TB extends keyof DB
    • O

    Hierarchy (View Summary)

    Index

    Accessors

    • get expressionType(): undefined | T

      All expressions need to have this getter for complicated type-related reasons. Simply add this getter for your expression and always return undefined from it:

      import { type Expression, type OperationNode, sql } from 'kysely'

      class SomeExpression<T> implements Expression<T> {
      get expressionType(): Tundefined {
      return undefined
      }

      toOperationNode(): OperationNode {
      return sql`some sql here`.toOperationNode()
      }
      }

      The getter is needed to make the expression assignable to another expression only if the types T are assignable. Without this property (or some other property that references T), you could assing Expression<string> to Expression<number>.

      Returns undefined | T

    Methods

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

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

      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.

      Type Parameters

      • K extends string | number | symbol = keyof O

      Returns ExpressionWrapper<DB, TB, O[K]>

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

      Type Parameters

      • T

      Returns O extends T
          ? SelectQueryBuilder<DB, TB, T>
          : KyselyTypeError<
              "$assertType() call failed: The type passed in is not equal to the output type of the query.",
          >

    • 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.

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

      The generated SQL(PostgreSQL):

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

      Type Parameters

      • K1 extends string | number | symbol
      • K2 extends string | number | symbol

      Parameters

      Returns keyof O extends K1
      | K2
          ? ExpressionWrapper<DB, TB, [O[K1], O[K2]]>
          : KyselyTypeError<
              "$asTuple() call failed: All selected columns must be provided as arguments",
          >

    • Type Parameters

      • K1 extends string | number | symbol
      • K2 extends string | number | symbol
      • K3 extends string | number | symbol

      Parameters

      Returns keyof O extends K1
      | K2
      | K3
          ? ExpressionWrapper<DB, TB, [O[K1], O[K2], O[K3]]>
          : KyselyTypeError<
              "$asTuple() call failed: All selected columns must be provided as arguments",
          >

    • Type Parameters

      • K1 extends string | number | symbol
      • K2 extends string | number | symbol
      • K3 extends string | number | symbol
      • K4 extends string | number | symbol

      Parameters

      Returns keyof O extends K1
      | K2
      | K3
      | K4
          ? ExpressionWrapper<DB, TB, [O[K1], O[K2], O[K3], O[K4]]>
          : KyselyTypeError<
              "$asTuple() call failed: All selected columns must be provided as arguments",
          >

    • Type Parameters

      • K1 extends string | number | symbol
      • K2 extends string | number | symbol
      • K3 extends string | number | symbol
      • K4 extends string | number | symbol
      • K5 extends string | number | symbol

      Parameters

      Returns keyof O extends K1
      | K2
      | K3
      | K4
      | K5
          ? ExpressionWrapper<DB, TB, [O[K1], O[K2], O[K3], O[K4], O[K5]]>
          : KyselyTypeError<
              "$asTuple() call failed: All selected columns must be provided as arguments",
          >

    • 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.

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

      Type Parameters

      • T

      Parameters

      • func: (qb: this) => T

      Returns T

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

      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:

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

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

      Type Parameters

      • O2

      Parameters

      Returns SelectQueryBuilder<DB, TB, O & Partial<Omit<O2, keyof O>>>

    • 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.

      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
      }

      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)

      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)

      Type Parameters

      • T

      Returns SelectQueryBuilder<DB, TB, NarrowPartial<O, T>>

    • 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

      The generated SQL (PostgreSQL):

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

      Type Parameters

      • A extends string

      Parameters

      • alias: A

      Returns AliasedSelectQueryBuilder<O, A>

    • Joins another table to the query using a cross apply.

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

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

      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"

      Type Parameters

      • TE extends
            | string
            | AliasedExpression<any, any>
            | AliasedDynamicTableBuilder<any, any>
            | AliasedExpressionFactory<DB, TB>

      Parameters

      Returns SelectQueryBuilderWithInnerJoin<DB, TB, O, TE>

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

      This is only supported by some dialects like PostgreSQL.

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

      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"

      Type Parameters

      • TE extends
            | string
            | AliasedExpression<any, any>
            | AliasedDynamicTableBuilder<any, any>
            | AliasedExpressionFactory<DB, TB>

      Parameters

      Returns SelectQueryBuilderWithInnerJoin<DB, TB, O, TE>

    • Adds distinct on expressions to the select clause.

      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

      Type Parameters

      • RE extends
            | string
            | Expression<any>
            | DynamicReferenceBuilder<any>
            | SelectQueryBuilderExpression<Record<string, any>>
            | OperandExpressionFactory<DB, TB, any>

      Parameters

      • selections: readonly RE[]

      Returns SelectQueryBuilder<DB, TB, O>

    • Type Parameters

      • RE extends
            | string
            | Expression<any>
            | DynamicReferenceBuilder<any>
            | SelectQueryBuilderExpression<Record<string, any>>
            | OperandExpressionFactory<DB, TB, any>

      Parameters

      • selection: RE

      Returns SelectQueryBuilder<DB, TB, O>

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

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

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

      The generated SQL (PostgreSQL):

      select "id", "first_name" as "name"
      from "person"
      except
      select "id", "name"
      from "pet"
      order by "name"

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

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

      The generated SQL (PostgreSQL):

      select "id", "first_name" as "name"
      from "person"
      except
      (
      select "id", "name"
      from "pet"
      )
      order by "name"

      Type Parameters

      • E extends SetOperandExpression<DB, O>

      Parameters

      • expression: E

      Returns SelectQueryBuilder<DB, TB, O>

    • 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.

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

      The generated SQL (PostgreSQL):

      select "id", "first_name" as "name"
      from "person"
      except all
      select "id", "name"
      from "pet"
      order by "name"

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

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

      The generated SQL (PostgreSQL):

      select "id", "first_name" as "name"
      from "person"
      except all
      (
      select "id", "name"
      from "pet"
      )
      order by "name"

      Type Parameters

      • E extends SetOperandExpression<DB, O>

      Parameters

      • expression: E

      Returns SelectQueryBuilder<DB, TB, O>

    • 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
      

      Type Parameters

      • ER extends Record<string, any> = Record<string, any>

      Parameters

      Returns Promise<ER[]>

    • Adds a fetch clause to the query.

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

      await db
      .selectFrom('person')
      .select('first_name')
      .orderBy('first_name')
      .offset(0)
      .fetch(10)
      .execute()

      The generated SQL (MS SQL Server):

      select "first_name"
      from "person"
      order by "first_name"
      offset 0 rows
      fetch next 10 rows only

      Parameters

      Returns SelectQueryBuilder<DB, TB, O>

    • Adds a group by clause to the query.

      importsql } from 'kysely'

      await db
      .selectFrom('person')
      .select([
      'first_name',
      sql<string>`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:

      importsql } from 'kysely'

      await db
      .selectFrom('person')
      .select([
      'first_name',
      'last_name',
      sql<string>`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<string>`max(id)`.as('max_id')
      ])
      .groupBy([
      sql<string>`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"

      Type Parameters

      • GE extends
            | string
            | Expression<any>
            | DynamicReferenceBuilder<any>
            | SelectQueryBuilderExpression<Record<string, any>>
            | OperandExpressionFactory<DB, TB, any>
            | readonly GroupByExpression<DB, TB, O>[]
            | (
                (
                    eb: ExpressionBuilder<DB, TB>,
                ) => readonly GroupByExpression<DB, TB, O>[]
            )

      Parameters

      • groupBy: GE

      Returns SelectQueryBuilder<DB, TB, O>

    • Joins another table to the query using an inner join.

      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 (like OR expressions for example). 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')
      .on((eb) => eb.or([
      eb('person.age', '>', 18),
      eb('person.age', '<', 100)
      ]))
      )
      .selectAll()
      .execute()

      The generated SQL (PostgreSQL):

      select *
      from "person"
      inner join "pet"
      on "pet"."owner_id" = "person"."id"
      and "pet"."name" = $1
      and (
      "person"."age" > $2
      OR "person"."age" < $3
      )

      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"

      Type Parameters

      • TE extends
            | string
            | AliasedExpression<any, any>
            | AliasedDynamicTableBuilder<any, any>
            | AliasedExpressionFactory<DB, TB>
      • K1 extends string
      • K2 extends string

      Parameters

      Returns SelectQueryBuilderWithInnerJoin<DB, TB, O, TE>

    • Type Parameters

      Parameters

      • table: TE
      • callback: FN

      Returns SelectQueryBuilderWithInnerJoin<DB, TB, O, TE>

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

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

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

      The generated SQL (PostgreSQL):

      select "id", "first_name" as "name"
      from "person"
      intersect
      select "id", "name"
      from "pet"
      order by "name"

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

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

      The generated SQL (PostgreSQL):

      select "id", "first_name" as "name"
      from "person"
      intersect
      (
      select "id", "name"
      from "pet"
      )
      order by "name"

      Type Parameters

      • E extends SetOperandExpression<DB, O>

      Parameters

      • expression: E

      Returns SelectQueryBuilder<DB, TB, O>

    • 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.

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

      The generated SQL (PostgreSQL):

      select "id", "first_name" as "name"
      from "person"
      intersect all
      select "id", "name"
      from "pet"
      order by "name"

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

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

      The generated SQL (PostgreSQL):

      select "id", "first_name" as "name"
      from "person"
      intersect all
      (
      select "id", "name"
      from "pet"
      )
      order by "name"

      Type Parameters

      • E extends SetOperandExpression<DB, O>

      Parameters

      • expression: E

      Returns SelectQueryBuilder<DB, TB, O>

    • Adds a limit clause to the query.

      Passing a null value is only supported by some dialects like PostgreSQL, and will result in a no-op limit clause.

      Select the first 10 rows of the result:

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

      The generated SQL (PostgreSQL):

      select "first_name" from "person" limit $1
      

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

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

      The generated SQL (PostgreSQL):

      select "first_name" from "person" limit $1 offset $2
      

      Parameters

      Returns SelectQueryBuilder<DB, TB, O>

    • Adds an order by clause to the query.

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

      orderBy is supported in select queries on all dialects. In MySQL, you can also use orderBy in update and delete queries.

      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, while the second optional argument is the direction (asc or desc), a callback that accepts and returns an OrderByItemBuilder or an expression.

      See clearOrderBy to remove the order by clause from a query.

      Single column/expression per call:

      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", "fn" desc

      Building advanced modifiers:

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

      The generated SQL (PostgreSQL):

      select "person"."first_name" as "fn"
      from "person"
      order by "id" desc nulls first

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

      importsql } 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<string>`concat(first_name, last_name) asc`
      )
      .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"

      Type Parameters

      • OE extends
            | string
            | Expression<any>
            | DynamicReferenceBuilder<any>
            | SelectQueryBuilderExpression<Record<string, any>>
            | OperandExpressionFactory<DB, TB, any>

      Parameters

      Returns SelectQueryBuilder<DB, TB, O>

    • Type Parameters

      • OE extends
            | string
            | Expression<any>
            | DynamicReferenceBuilder<any>
            | SelectQueryBuilderExpression<Record<string, any>>
            | OperandExpressionFactory<DB, TB, any>

      Parameters

      • exprs: readonly OE[]

      Returns SelectQueryBuilder<DB, TB, O>

      It does ~2-2.5x more compile-time instantiations than multiple orderBy(expr, modifiers?) calls, and has broken autocompletion.

    • Type Parameters

      • OE extends
            | `${string} desc`
            | `${string} asc`
            | `${string}.${string} desc`
            | `${string}.${string} asc`

      Parameters

      Returns SelectQueryBuilder<DB, TB, O>

      Use orderBy(expr, direction) instead.

    • Type Parameters

      • OE extends
            | string
            | Expression<any>
            | DynamicReferenceBuilder<any>
            | SelectQueryBuilderExpression<Record<string, any>>
            | OperandExpressionFactory<DB, TB, any>

      Parameters

      Returns SelectQueryBuilder<DB, TB, O>

      Use orderBy(expr, (ob) => ...) instead.

    • 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.

      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, val, lit }) => [
      // 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'),

      // Select a static string value
      val('Some value').as('string_value'),

      // Select a literal value
      lit(42).as('literal_value'),
      ])
      .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",
      $4 as "string_value",
      42 as "literal_value"
      from "person"

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

      Sometimes you can be sure something's not null, but Kysely isn't able to infer it. For example calling where('last_name', 'is not', null) doesn't make last_name not null in the result type, but unless you have other where statements you can be sure it's never null.

      Kysely has a couple of helpers for dealing with these cases: $notNull() and $narrowType. Both are used in the following example:

      import { NotNull } from 'kysely'
      import { jsonObjectFrom } from 'kysely/helpers/postgres'

      const persons = db
      .selectFrom('person')
      .select((eb) => [
      'last_name',
      // Let's assume we know the person has at least one
      // pet. We can use the `.$notNull()` method to make
      // the expression not null. You could just as well
      // add `pet` to the `$narrowType` call below.
      jsonObjectFrom(
      eb.selectFrom('pet')
      .selectAll()
      .limit(1)
      .whereRef('person.id', '=', 'pet.owner_id')
      ).$notNull().as('pet')
      ])
      .where('last_name', 'is not', null)
      // $narrowType can be used to narrow the output type.
      // The special `NotNull` type can be used to make a
      // selection not null. You could add `pet: NotNull`
      // here and omit the `$notNull()` call on it.
      // Use whichever way you prefer.
      .$narrowType<{ last_name: NotNull }>()
      .execute()

      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: string = 'first_name';

      // 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' | 'birthdate'

      const people = 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 | null | undefined = people[0].last_name
      const firstName: string | undefined = people[0].first_name
      const birthDate: Date | null | undefined = people[0].birthdate

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

      Type Parameters

      • SE extends
            | string
            | AliasedExpression<any, any>
            | DynamicReferenceBuilder<any>
            | AliasedExpressionFactory<DB, TB>

      Parameters

      • selections: readonly SE[]

      Returns SelectQueryBuilder<DB, TB, O & Selection<DB, TB, SE>>

    • Type Parameters

      Parameters

      • callback: CB

      Returns SelectQueryBuilder<DB, TB, O & CallbackSelection<DB, TB, CB>>

    • Type Parameters

      • SE extends
            | string
            | AliasedExpression<any, any>
            | DynamicReferenceBuilder<any>
            | AliasedExpressionFactory<DB, TB>

      Parameters

      • selection: SE

      Returns SelectQueryBuilder<DB, TB, O & Selection<DB, TB, SE>>

    • 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.

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

      Parameters

      • OptionalchunkSize: number

      Returns AsyncIterableIterator<O>

    • Adds a top clause to the query.

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

      Select 10 biggest ages:

      await db
      .selectFrom('person')
      .select('age')
      .top(10)
      .orderBy('age desc')
      .execute()

      The generated SQL (MS SQL Server):

      select top(10) "age" from "person" order by "age" desc
      

      Select 10% first rows:

      await db
      .selectFrom('person')
      .selectAll()
      .top(10, 'percent')
      .execute()

      The generated SQL (MS SQL Server):

      select top(10) percent * from "person"
      

      Parameters

      • expression: number | bigint
      • Optionalmodifiers: TopModifier

      Returns SelectQueryBuilder<DB, TB, O>

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

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

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

      The generated SQL (PostgreSQL):

      select "id", "first_name" as "name"
      from "person"
      union
      select "id", "name"
      from "pet"
      order by "name"

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

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

      The generated SQL (PostgreSQL):

      select "id", "first_name" as "name"
      from "person"
      union
      (
      select "id", "name"
      from "pet"
      )
      order by "name"

      Type Parameters

      • E extends SetOperandExpression<DB, O>

      Parameters

      • expression: E

      Returns SelectQueryBuilder<DB, TB, O>

    • 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.

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

      The generated SQL (PostgreSQL):

      select "id", "first_name" as "name"
      from "person"
      union all
      select "id", "name"
      from "pet"
      order by "name"

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

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

      The generated SQL (PostgreSQL):

      select "id", "first_name" as "name"
      from "person"
      union all
      (
      select "id", "name"
      from "pet"
      )
      order by "name"

      Type Parameters

      • E extends SetOperandExpression<DB, O>

      Parameters

      • expression: E

      Returns SelectQueryBuilder<DB, TB, O>

    • Adds a where expression to the query.

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

      Also see whereRef

      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:

      import { sql } from 'kysely'

      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:

      importsql } from 'kysely'

      const persons = await db
      .selectFrom('person')
      .selectAll()
      .where(
      sql<string>`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:

      importsql } from 'kysely'
      const { ref } = db.dynamic

      const columnFromUserInput: string = 'id'

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

      Type Parameters

      • RE extends
            | string
            | Expression<any>
            | DynamicReferenceBuilder<any>
            | SelectQueryBuilderExpression<Record<string, any>>
            | OperandExpressionFactory<DB, TB, any>
      • VE extends any

      Parameters

      Returns SelectQueryBuilder<DB, TB, O>

    • Adds a where expression to the query.

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

      Also see whereRef

      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:

      import { sql } from 'kysely'

      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:

      importsql } from 'kysely'

      const persons = await db
      .selectFrom('person')
      .selectAll()
      .where(
      sql<string>`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:

      importsql } from 'kysely'
      const { ref } = db.dynamic

      const columnFromUserInput: string = 'id'

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

      Type Parameters

      Parameters

      • expression: E

      Returns SelectQueryBuilder<DB, TB, O>

    • 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.

      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

      Type Parameters

      • LRE extends
            | string
            | Expression<any>
            | DynamicReferenceBuilder<any>
            | SelectQueryBuilderExpression<Record<string, any>>
            | OperandExpressionFactory<DB, TB, any>
      • RRE extends
            | string
            | Expression<any>
            | DynamicReferenceBuilder<any>
            | SelectQueryBuilderExpression<Record<string, any>>
            | OperandExpressionFactory<DB, TB, any>

      Parameters

      Returns SelectQueryBuilder<DB, TB, O>