Short and simple examples of how to use Kysely Select to achieve common tasks.
A single column
Schema
import { Generated } from ' kysely '
created_at : Generated < Date >
Querying
. where ( ' first_name ' , ' = ' , ' Arnold ' )
Result
Column with a table
Schema
import { Generated } from ' kysely '
created_at : Generated < Date >
Querying
. selectFrom ([ ' person ' , ' pet ' ])
Result
Multiple columns
Schema
import { Generated } from ' kysely '
created_at : Generated < Date >
Querying
. select ([ ' person.id ' , ' first_name ' ])
Result
Aliases
Schema
import { Generated } from ' kysely '
created_at : Generated < Date >
Querying
. selectFrom ( ' person as p ' )
Result
Complex selections
Schema
import { Generated } from ' kysely '
created_at : Generated < Date >
Querying
import { sql } from ' kysely '
const persons = await db . selectFrom ( ' person ' )
. select ( ( { eb , selectFrom , or } ) => [
// Select a correlated subquery
. whereRef ( ' person.id ' , ' = ' , ' pet.owner_id ' )
// Build and select an expression using
// the expression builder
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 ' )
Result
" person " . " id " = " pet " . " owner_id "
) AS " is_jennifer_or_arnold " ,
CONCAT (first_name, ' ' , last_name) AS " full_name "
` person ` . ` id ` = ` pet ` . ` owner_id `
) AS ` is_jennifer_or_arnold ` ,
CONCAT (first_name, ' ' , last_name) AS ` full_name `
" person " . " id " = " pet " . " owner_id "
) AS " is_jennifer_or_arnold " ,
concat (first_name, ' ' , last_name) AS " full_name "
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 '
created_at : Generated < Date >
Querying
import { sql } from ' kysely '
const result = await db . selectFrom ( ' person ' )
. innerJoin ( ' pet ' , ' pet.owner_id ' , ' person.id ' )
. select ( ( { fn , val , ref } ) => [
// The `fn` module contains the most common
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.
]) . as ( ' full_name_with_title ' ),
// You can call any aggregate function using the
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`
. having ( ( eb ) => eb . fn . count ( ' pet.id ' ), ' > ' , 10 )
Result
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 "
INNER JOIN " pet " ON " pet " . " owner_id " = " 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 `
INNER JOIN ` pet ` ON ` pet ` . ` owner_id ` = ` 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 "
INNER JOIN " pet " ON " pet " . " owner_id " = " person " . " id "
Distinct
Schema
import { Generated } from ' kysely '
created_at : Generated < Date >
Querying
const persons = await db . selectFrom ( ' person ' )
Result
Distinct on
Schema
import { Generated } from ' kysely '
created_at : Generated < Date >
Querying
const persons = await db . selectFrom ( ' person ' )
. innerJoin ( ' pet ' , ' pet.owner_id ' , ' person.id ' )
. where ( ' pet.name ' , ' = ' , ' Doggo ' )
Result
ON ( " person " . " id " ) " person " . *
INNER JOIN " pet " ON " pet " . " owner_id " = " person " . " id "
ON ( ` person ` . ` id ` ) ` person ` . *
INNER JOIN ` pet ` ON ` pet ` . ` owner_id ` = ` person ` . ` id `
ON ( " person " . " id " ) " person " . *
INNER JOIN " pet " ON " pet " . " owner_id " = " person " . " id "
All columns
Schema
import { Generated } from ' kysely '
created_at : Generated < Date >
Querying
Result
All columns of a table
Schema
import { Generated } from ' kysely '
created_at : Generated < Date >
Querying
Result
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 '
created_at : Generated < Date >
Querying
import { jsonArrayFrom } from ' kysely/helpers/postgres '
. select ([ ' pet.id as pet_id ' , ' pet.name ' ])
. whereRef ( ' pet.owner_id ' , ' = ' , ' person.id ' )
Result
COALESCE (JSON_AGG(agg), ' [] ' )
" pet " . " owner_id " = " person " . " id "
COALESCE (json_agg (agg), ' [] ' )
` pet ` . ` owner_id ` = ` person ` . ` id `
COALESCE (json_agg (agg), ' [] ' )
" pet " . " owner_id " = " person " . " id "
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 '
created_at : Generated < Date >
Querying
import { jsonObjectFrom } from ' kysely/helpers/postgres '
. select ([ ' pet.id as pet_id ' , ' pet.name ' ])
. whereRef ( ' pet.owner_id ' , ' = ' , ' person.id ' )
. where ( ' pet.is_favorite ' , ' = ' , true )
Result
" pet " . " owner_id " = " person " . " id "
AND " pet " . " is_favorite " = $ 1
` pet ` . ` owner_id ` = ` person ` . ` id `
AND ` pet ` . ` is_favorite ` = ?
" pet " . " owner_id " = " person " . " id "
AND " pet " . " is_favorite " = ?
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