Zum Inhalt springen

Kysely Select

Dieser Inhalt ist noch nicht in deiner Sprache verfügbar.

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

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

SELECT
"id"
FROM
"person"
WHERE
"first_name" = $1
-- Parameters
-- [1] Arnold

Column with 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

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

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

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

Aliases

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

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

Complex selections

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

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

Function calls

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

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

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

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

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

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

Result

SELECT
*
FROM
"person"

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

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

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

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