Kysely Select

Short and simple examples of how to use Kysely Select to achieve common tasks.

A single column

Example

Select a single column

Schema

import { Generated } from 'kysely'

declare global {
  interface DB {
    person: PersonTable
    pet: PetTable
  }

  interface PersonTable {
    id: Generated<string>
    first_name: string
    last_name: string | null
    created_at: Generated<Date>
    age: number
  }

  interface PetTable {
    id: Generated<string>
    name: string
    owner_id: string
    species: 'cat' | 'dog'
    is_favorite: boolean
  }
}

Querying

const persons = await db
  .selectFrom('person')
  .select('id')
  .where('first_name', '=', 'Arnold')
  .execute()

Result

postgres

SELECT
  "id"
FROM
  "person"
WHERE
  "first_name" = $1

-- Parameters
-- [1] Arnold

mysql

SELECT
  `id`
FROM
  `person`
WHERE
  `first_name` = ?

-- Parameters
-- [1] Arnold

sqlite

SELECT
  "id"
FROM
  "person"
WHERE
  "first_name" = ?

-- Parameters
-- [1] Arnold

Column with a table

Example

Select a single column and specify a table

Schema

import { Generated } from 'kysely'

declare global {
  interface DB {
    person: PersonTable
    pet: PetTable
  }

  interface PersonTable {
    id: Generated<string>
    first_name: string
    last_name: string | null
    created_at: Generated<Date>
    age: number
  }

  interface PetTable {
    id: Generated<string>
    name: string
    owner_id: string
    species: 'cat' | 'dog'
    is_favorite: boolean
  }
}

Querying

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

Result

postgres

SELECT
  "person"."id"
FROM
  "person",
  "pet"

mysql

SELECT
  `person`.`id`
FROM
  `person`,
  `pet`

sqlite

SELECT
  "person"."id"
FROM
  "person",
  "pet"

Multiple columns

Example

Select multiple columns

Schema

import { Generated } from 'kysely'

declare global {
  interface DB {
    person: PersonTable
    pet: PetTable
  }

  interface PersonTable {
    id: Generated<string>
    first_name: string
    last_name: string | null
    created_at: Generated<Date>
    age: number
  }

  interface PetTable {
    id: Generated<string>
    name: string
    owner_id: string
    species: 'cat' | 'dog'
    is_favorite: boolean
  }
}

Querying

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

Result

postgres

SELECT
  "person"."id",
  "first_name"
FROM
  "person"

mysql

SELECT
  `person`.`id`,
  `first_name`
FROM
  `person`

sqlite

SELECT
  "person"."id",
  "first_name"
FROM
  "person"

Aliases

Example

You can give an alias for selections and tables by appending as the_alias to the name

Schema

import { Generated } from 'kysely'

declare global {
  interface DB {
    person: PersonTable
    pet: PetTable
  }

  interface PersonTable {
    id: Generated<string>
    first_name: string
    last_name: string | null
    created_at: Generated<Date>
    age: number
  }

  interface PetTable {
    id: Generated<string>
    name: string
    owner_id: string
    species: 'cat' | 'dog'
    is_favorite: boolean
  }
}

Querying

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

Result

postgres

SELECT
  "first_name" AS "fn",
  "p"."last_name" AS "ln"
FROM
  "person" AS "p"

mysql

SELECT
  `first_name` AS `fn`,
  `p`.`last_name` AS `ln`
FROM
  `person` AS `p`

sqlite

SELECT
  "first_name" AS "fn",
  "p"."last_name" AS "ln"
FROM
  "person" AS "p"

Complex selections

Example

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

Schema

import { Generated } from 'kysely'

declare global {
  interface DB {
    person: PersonTable
    pet: PetTable
  }

  interface PersonTable {
    id: Generated<string>
    first_name: string
    last_name: string | null
    created_at: Generated<Date>
    age: number
  }

  interface PetTable {
    id: Generated<string>
    name: string
    owner_id: string
    species: 'cat' | 'dog'
    is_favorite: boolean
  }
}

Querying

import { sql } from 'kysely'

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

    // Build and select an expression using
    // the expression builder
    or([
      eb('first_name', '=', 'Jennifer'),
      eb('first_name', '=', 'Arnold')
    ]).as('is_jennifer_or_arnold'),

    // Select a raw sql expression
    sql<string>`concat(first_name, ' ', last_name)`.as('full_name')
  ])
  .execute()

Result

postgres

SELECT
  (
    SELECT
      "pet"."name"
    FROM
      "pet"
    WHERE
      "person"."id" = "pet"."owner_id"
    ORDER BY
      "pet"."name"
    LIMIT
      $1
  ) AS "first_pet_name",
  (
    "first_name" = $2
    OR "first_name" = $3
  ) AS "is_jennifer_or_arnold",
  CONCAT(first_name, ' ', last_name) AS "full_name"
FROM
  "person"

-- Parameters
-- [1] 1
-- [2] Jennifer
-- [3] Arnold

mysql

SELECT
  (
    SELECT
      `pet`.`name`
    FROM
      `pet`
    WHERE
      `person`.`id` = `pet`.`owner_id`
    ORDER BY
      `pet`.`name`
    LIMIT
      ?
  ) AS `first_pet_name`,
  (
    `first_name` = ?
    OR `first_name` = ?
  ) AS `is_jennifer_or_arnold`,
  CONCAT(first_name, ' ', last_name) AS `full_name`
FROM
  `person`

-- Parameters
-- [1] 1
-- [2] Jennifer
-- [3] Arnold

sqlite

SELECT
  (
    SELECT
      "pet"."name"
    FROM
      "pet"
    WHERE
      "person"."id" = "pet"."owner_id"
    ORDER BY
      "pet"."name"
    LIMIT
      ?
  ) AS "first_pet_name",
  (
    "first_name" = ?
    OR "first_name" = ?
  ) AS "is_jennifer_or_arnold",
  concat (first_name, ' ', last_name) AS "full_name"
FROM
  "person"

-- Parameters
-- [1] 1
-- [2] Jennifer
-- [3] Arnold

Function calls

Example

This example shows how to create function calls. These examples also work in any other place (where calls, updates, inserts etc.). The only difference is that you leave out the alias (the as call) if you use these in any other place than select .

Schema

import { Generated } from 'kysely'

declare global {
  interface DB {
    person: PersonTable
    pet: PetTable
  }

  interface PersonTable {
    id: Generated<string>
    first_name: string
    last_name: string | null
    created_at: Generated<Date>
    age: number
  }

  interface PetTable {
    id: Generated<string>
    name: string
    owner_id: string
    species: 'cat' | 'dog'
    is_favorite: boolean
  }
}

Querying

import { sql } from 'kysely'

const result = await db.selectFrom('person')
  .innerJoin('pet', 'pet.owner_id', 'person.id')
  .select(({ fn, val, ref }) => [
    'person.id',

    // The `fn` module contains the most common
    // functions.
    fn.count<number>('pet.id').as('pet_count'),

    // You can call any function by calling `fn`
    // directly. The arguments are treated as column
    // references by default. If you want  to pass in
    // values, use the `val` function.
    fn<string>('concat', [
      val('Ms. '),
      'first_name',
      val(' '),
      'last_name'
    ]).as('full_name_with_title'),

    // You can call any aggregate function using the
    // `fn.agg` function.
    fn.agg<string[]>('array_agg', ['pet.name']).as('pet_names'),

    // And once again, you can use the `sql`
    // template tag. The template tag substitutions
    // are treated as values by default. If you want
    // to reference columns, you can use the `ref`
    // function.
    sql<string>`concat(
      ${ref('first_name')},
      ' ',
      ${ref('last_name')}
    )`.as('full_name')
  ])
  .groupBy('person.id')
  .having((eb) => eb.fn.count('pet.id'), '>', 10)
  .execute()

Result

postgres

SELECT
  "person"."id",
  COUNT("pet"."id") AS "pet_count",
  CONCAT($1, "first_name", $2, "last_name") AS "full_name_with_title",
  ARRAY_AGG("pet"."name") AS "pet_names",
  CONCAT("first_name", ' ', "last_name") AS "full_name"
FROM
  "person"
  INNER JOIN "pet" ON "pet"."owner_id" = "person"."id"
GROUP BY
  "person"."id"
HAVING
  COUNT("pet"."id") > $3

-- Parameters
-- [1] Ms.
-- [2]
-- [3] 10

mysql

SELECT
  `person`.`id`,
  COUNT(`pet`.`id`) AS `pet_count`,
  CONCAT(?, `first_name`, ?, `last_name`) AS `full_name_with_title`,
  array_agg (`pet`.`name`) AS `pet_names`,
  CONCAT(`first_name`, ' ', `last_name`) AS `full_name`
FROM
  `person`
  INNER JOIN `pet` ON `pet`.`owner_id` = `person`.`id`
GROUP BY
  `person`.`id`
HAVING
  COUNT(`pet`.`id`) > ?

-- Parameters
-- [1] Ms.
-- [2]
-- [3] 10

sqlite

SELECT
  "person"."id",
  COUNT("pet"."id") AS "pet_count",
  concat (?, "first_name", ?, "last_name") AS "full_name_with_title",
  array_agg ("pet"."name") AS "pet_names",
  concat ("first_name", ' ', "last_name") AS "full_name"
FROM
  "person"
  INNER JOIN "pet" ON "pet"."owner_id" = "person"."id"
GROUP BY
  "person"."id"
HAVING
  COUNT("pet"."id") > ?

-- Parameters
-- [1] Ms.
-- [2]
-- [3] 10

Distinct

Schema

import { Generated } from 'kysely'

declare global {
  interface DB {
    person: PersonTable
    pet: PetTable
  }

  interface PersonTable {
    id: Generated<string>
    first_name: string
    last_name: string | null
    created_at: Generated<Date>
    age: number
  }

  interface PetTable {
    id: Generated<string>
    name: string
    owner_id: string
    species: 'cat' | 'dog'
    is_favorite: boolean
  }
}

Querying

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

Result

postgres

SELECT DISTINCT
  "first_name"
FROM
  "person"

mysql

SELECT DISTINCT
  `first_name`
FROM
  `person`

sqlite

SELECT DISTINCT
  "first_name"
FROM
  "person"

Distinct on

Schema

import { Generated } from 'kysely'

declare global {
  interface DB {
    person: PersonTable
    pet: PetTable
  }

  interface PersonTable {
    id: Generated<string>
    first_name: string
    last_name: string | null
    created_at: Generated<Date>
    age: number
  }

  interface PetTable {
    id: Generated<string>
    name: string
    owner_id: string
    species: 'cat' | 'dog'
    is_favorite: boolean
  }
}

Querying

const persons = await db.selectFrom('person')
  .innerJoin('pet', 'pet.owner_id', 'person.id')
  .where('pet.name', '=', 'Doggo')
  .distinctOn('person.id')
  .selectAll('person')
  .execute()

Result

postgres

SELECT DISTINCT
  ON ("person"."id") "person".*
FROM
  "person"
  INNER JOIN "pet" ON "pet"."owner_id" = "person"."id"
WHERE
  "pet"."name" = $1

-- Parameters
-- [1] Doggo

mysql

SELECT DISTINCT
  ON (`person`.`id`) `person`.*
FROM
  `person`
  INNER JOIN `pet` ON `pet`.`owner_id` = `person`.`id`
WHERE
  `pet`.`name` = ?

-- Parameters
-- [1] Doggo

sqlite

SELECT DISTINCT
  ON ("person"."id") "person".*
FROM
  "person"
  INNER JOIN "pet" ON "pet"."owner_id" = "person"."id"
WHERE
  "pet"."name" = ?

-- Parameters
-- [1] Doggo

All columns

Example

The selectAll method generates SELECT *

Schema

import { Generated } from 'kysely'

declare global {
  interface DB {
    person: PersonTable
    pet: PetTable
  }

  interface PersonTable {
    id: Generated<string>
    first_name: string
    last_name: string | null
    created_at: Generated<Date>
    age: number
  }

  interface PetTable {
    id: Generated<string>
    name: string
    owner_id: string
    species: 'cat' | 'dog'
    is_favorite: boolean
  }
}

Querying

const persons = await db
  .selectFrom('person')
  .selectAll()
  .execute()

Result

postgres

SELECT
  *
FROM
  "person"

mysql

SELECT
  *
FROM
  `person`

sqlite

SELECT
  *
FROM
  "person"

All columns of a table

Example

Select all columns of a table

Schema

import { Generated } from 'kysely'

declare global {
  interface DB {
    person: PersonTable
    pet: PetTable
  }

  interface PersonTable {
    id: Generated<string>
    first_name: string
    last_name: string | null
    created_at: Generated<Date>
    age: number
  }

  interface PetTable {
    id: Generated<string>
    name: string
    owner_id: string
    species: 'cat' | 'dog'
    is_favorite: boolean
  }
}

Querying

const persons = await db
  .selectFrom('person')
  .selectAll('person')
  .execute()

Result

postgres

SELECT
  "person".*
FROM
  "person"

mysql

SELECT
  `person`.*
FROM
  `person`

sqlite

SELECT
  "person".*
FROM
  "person"

Nested array

While kysely is not an ORM and it doesn’t have the concept of relations, we do provide helpers for fetching nested objects and arrays in a single query. In this example we use the jsonArrayFrom helper to fetch person’s pets along with the person’s id.

Please keep in mind that the helpers under the kysely/helpers folder, including jsonArrayFrom, are not guaranteed to work with third party dialects. In order for them to work, the dialect must automatically parse the json data type into javascript JSON values like objects and arrays. Some dialects might simply return the data as a JSON string. In these cases you can use the built in ParseJSONResultsPlugin to parse the results.

Schema

import { Generated } from 'kysely'

declare global {
  interface DB {
    person: PersonTable
    pet: PetTable
  }

  interface PersonTable {
    id: Generated<string>
    first_name: string
    last_name: string | null
    created_at: Generated<Date>
    age: number
  }

  interface PetTable {
    id: Generated<string>
    name: string
    owner_id: string
    species: 'cat' | 'dog'
    is_favorite: boolean
  }
}

Querying

import { jsonArrayFrom } from 'kysely/helpers/postgres'

const result = await db
  .selectFrom('person')
  .select((eb) => [
    'id',
    jsonArrayFrom(
      eb.selectFrom('pet')
        .select(['pet.id as pet_id', 'pet.name'])
        .whereRef('pet.owner_id', '=', 'person.id')
        .orderBy('pet.name')
    ).as('pets')
  ])
  .execute()

Result

postgres

SELECT
  "id",
  (
    SELECT
      COALESCE(JSON_AGG(agg), '[]')
    FROM
      (
        SELECT
          "pet"."id" AS "pet_id",
          "pet"."name"
        FROM
          "pet"
        WHERE
          "pet"."owner_id" = "person"."id"
        ORDER BY
          "pet"."name"
      ) AS agg
  ) AS "pets"
FROM
  "person"

mysql

SELECT
  `id`,
  (
    SELECT
      COALESCE(json_agg (agg), '[]')
    FROM
      (
        SELECT
          `pet`.`id` AS `pet_id`,
          `pet`.`name`
        FROM
          `pet`
        WHERE
          `pet`.`owner_id` = `person`.`id`
        ORDER BY
          `pet`.`name`
      ) AS agg
  ) AS `pets`
FROM
  `person`

sqlite

SELECT
  "id",
  (
    SELECT
      COALESCE(json_agg (agg), '[]')
    FROM
      (
        SELECT
          "pet"."id" AS "pet_id",
          "pet"."name"
        FROM
          "pet"
        WHERE
          "pet"."owner_id" = "person"."id"
        ORDER BY
          "pet"."name"
      ) AS agg
  ) AS "pets"
FROM
  "person"

Nested object

While kysely is not an ORM and it doesn’t have the concept of relations, we do provide helpers for fetching nested objects and arrays in a single query. In this example we use the jsonObjectFrom helper to fetch person’s favorite pet along with the person’s id.

Please keep in mind that the helpers under the kysely/helpers folder, including jsonObjectFrom, are not guaranteed to work with 3rd party dialects. In order for them to work, the dialect must automatically parse the json data type into javascript JSON values like objects and arrays. Some dialects might simply return the data as a JSON string. In these cases you can use the built in ParseJSONResultsPlugin to parse the results.

Schema

import { Generated } from 'kysely'

declare global {
  interface DB {
    person: PersonTable
    pet: PetTable
  }

  interface PersonTable {
    id: Generated<string>
    first_name: string
    last_name: string | null
    created_at: Generated<Date>
    age: number
  }

  interface PetTable {
    id: Generated<string>
    name: string
    owner_id: string
    species: 'cat' | 'dog'
    is_favorite: boolean
  }
}

Querying

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

const result = await db
  .selectFrom('person')
  .select((eb) => [
    'id',
    jsonObjectFrom(
      eb.selectFrom('pet')
        .select(['pet.id as pet_id', 'pet.name'])
        .whereRef('pet.owner_id', '=', 'person.id')
        .where('pet.is_favorite', '=', true)
    ).as('favorite_pet')
  ])
  .execute()

Result

postgres

SELECT
  "id",
  (
    SELECT
      TO_JSON(obj)
    FROM
      (
        SELECT
          "pet"."id" AS "pet_id",
          "pet"."name"
        FROM
          "pet"
        WHERE
          "pet"."owner_id" = "person"."id"
          AND "pet"."is_favorite" = $1
      ) AS obj
  ) AS "favorite_pet"
FROM
  "person"

-- Parameters
-- [1] true

mysql

SELECT
  `id`,
  (
    SELECT
      to_json (obj)
    FROM
      (
        SELECT
          `pet`.`id` AS `pet_id`,
          `pet`.`name`
        FROM
          `pet`
        WHERE
          `pet`.`owner_id` = `person`.`id`
          AND `pet`.`is_favorite` = ?
      ) AS obj
  ) AS `favorite_pet`
FROM
  `person`

-- Parameters
-- [1] true

sqlite

SELECT
  "id",
  (
    SELECT
      to_json (obj)
    FROM
      (
        SELECT
          "pet"."id" AS "pet_id",
          "pet"."name"
        FROM
          "pet"
        WHERE
          "pet"."owner_id" = "person"."id"
          AND "pet"."is_favorite" = ?
      ) AS obj
  ) AS "favorite_pet"
FROM
  "person"

-- Parameters
-- [1] true

MORE EXAMPLES

The API documentation is packed with examples. The API docs are hosted here but you can access the same documentation by hovering over functions/methods/classes in your IDE. The examples are always just one hover away!

For example, check out these sections:

select method
selectAll method
selectFrom method