Modeling and Querying Relations

Learn how to nest related rows in Kysely queries using JSON functions

Kysely is NOT an ORM. Kysely DOES NOT have the concept of relations. Kysely IS a query builder. Kysely DOES build the SQL you tell it to, nothing more, nothing less.

Having said that, there are ways to nest related rows in your queries. You just have to do it using the tools SQL and the underlying dialect (e.g. PostgreSQL, MySQL, or SQLite) provide.

Prerequisites

This recipe is supported on:

• PostgreSQL (all versions)
• MySQL versions 8.0.14 and higher
• SQLite (with ParseJSONResultsPlugin)

MySQL 8.0.14+ is required due to the way subqueries use outer references in this recipe. See the MySQL 8.0.14 changelog for details.

JSON data types and functions

PostgreSQL and MySQL have rich JSON support through their json data types and functions. The pg and mysql2 node drivers automatically parse returned json columns as JavaScript objects.

Parsing JSON in SQLite

The built-in SqliteDialect and some third-party dialects don’t parse returned JSON columns to objects automatically. If your JSON columns get returned as strings, use the ParseJSONResultsPlugin:

const db = new Kysely<DB>({
  // ...
  plugins: [new ParseJSONResultsPlugin()]
})

Basic example

Let’s fetch a list of people and nest each person’s pets and mother into the returned objects.

Here’s the raw PostgreSQL SQL we want to generate:

SELECT
  person.*,
  
  -- Select person's pets as a json array
  (
    SELECT
      COALESCE(JSON_AGG(pets), '[]')
    FROM
    (
      SELECT
        pet.id, pet.name
      FROM
        pet
      WHERE
        pet.owner_id = person.id
      ORDER BY
        pet.name
    ) pets
  ) pets,
  
  -- Select person's mother as a json object
  (
    SELECT
      TO_JSON(mother)
    FROM
    (
      SELECT
        mother.id, mother.first_name
      FROM
        person as mother
      WHERE
        mother.id = person.mother_id
    ) mother
  ) mother
FROM
  person

Using helper functions

Kysely provides helper functions to simplify this pattern:

function jsonArrayFrom<O>(expr: Expression<O>) {
  return sql<Simplify<O>[]>`(select coalesce(json_agg(agg), '[]') from ${expr} as agg)`
}

function jsonObjectFrom<O>(expr: Expression<O>) {
  return sql<Simplify<O>>`(select to_json(obj) from ${expr} as obj)`
}

Import built-in helpers

These helpers are included in Kysely. Import them based on your dialect:

PostgreSQL

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

import { jsonArrayFrom, jsonObjectFrom } from 'kysely/helpers/mysql'

import { jsonArrayFrom, jsonObjectFrom } from 'kysely/helpers/sqlite'

Type-safe queries with helpers

With these helpers, our query becomes much more readable:

const persons = await db
  .selectFrom('person')
  .selectAll('person')
  .select((eb) => [
    // pets
    jsonArrayFrom(
      eb.selectFrom('pet')
        .select(['pet.id', 'pet.name'])
        .whereRef('pet.owner_id', '=', 'person.id')
        .orderBy('pet.name')
    ).as('pets'),
    
    // mother
    jsonObjectFrom(
      eb.selectFrom('person as mother')
        .select(['mother.id', 'mother.first_name'])
        .whereRef('mother.id', '=', 'person.mother_id')
    ).as('mother')
  ])
  .execute()

console.log(persons[0].pets[0].name)
console.log(persons[0].mother?.first_name)

Creating reusable relation helpers

For repeated use across your codebase, create dedicated helper functions:

function pets(ownerId: Expression<string>) {
  return jsonArrayFrom(
    db.selectFrom('pet')
      .select(['pet.id', 'pet.name'])
      .where('pet.owner_id', '=', ownerId)
      .orderBy('pet.name')
  )
}

function mother(motherId: Expression<string>) {
  return jsonObjectFrom(
    db.selectFrom('person as mother')
      .select(['mother.id', 'mother.first_name'])
      .where('mother.id', '=', motherId)
  )
}

Now your queries become even cleaner:

const persons = await db
  .selectFrom('person')
  .selectAll('person')
  .select(({ ref }) => [
    pets(ref('person.id')).as('pets'),
    mother(ref('person.mother_id')).as('mother')
  ])
  .execute()

console.log(persons[0].pets[0].name)
console.log(persons[0].mother?.first_name)

Handling nullability

Kysely marks selections as nullable if it can’t determine that the related object always exists. Use $notNull() when you know a relation exists:

const persons = await db
  .selectFrom('person')
  .selectAll('person')
  .select(({ ref }) => [
    pets(ref('person.id')).as('pets'),
    mother(ref('person.mother_id')).$notNull().as('mother')
  ])
  .execute()

console.log(persons[0].pets[0].name)
console.log(persons[0].mother.first_name) // No optional chaining needed

Conditional relations

Use $if to select relations conditionally:

const persons = await db
  .selectFrom('person')
  .selectAll('person')
  .$if(includePets, (qb) => qb.select(
    (eb) => pets(eb.ref('person.id')).as('pets')
  ))
  .$if(includeMom, (qb) => qb.select(
    (eb) => mother(eb.ref('person.mother_id')).as('mother')
  ))
  .execute()

For better performance, make sure you have indices on foreign key columns like pet.owner_id and person.mother_id.