Reusable helpers

info

Here's a playground link containing all the code in this recipe.

Let's say you want to write the following query:

SELECT id, first_name
FROM person
WHERE upper(last_name) = $1

Kysely doesn't have a built-in upper function but there are at least three ways you could write this:

const lastName = 'STALLONE'

const persons = await db
  .selectFrom('person')
  .select(['id', 'first_name'])
  // 1. `sql` template tag. This is the least type-safe option.
  // You're providing the column name without any type-checking,
  // and plugins won't affect it.
  .where(
    sql<string>`upper(last_name)`, '=', lastName
  )
  // 2. `sql` template tag with `ref`. Anything passed to `ref`
  // gets type-checked against the accumulated query context.
  .where(({ eb, ref }) => eb(
    sql<string>`upper(${ref('last_name')})`, '=', lastName
  ))
  // 3. The `fn` function helps you avoid missing parentheses/commas
  // errors and uses refs as 1st class arguments.
  .where(({ eb, fn }) => eb(
    fn<string>('upper', ['last_name']), '=', lastName
  ))
  .execute()

but each option could be more readable or type-safe.

Fortunately Kysely allows you to easily create composable, reusable and type-safe helper functions:

import { Expression, sql } from 'kysely'

function upper(expr: Expression<string>) {
  return sql<string>`upper(${expr})`
}

function lower(expr: Expression<string>) {
  return sql<string>`lower(${expr})`
}

function concat(...exprs: Expression<string>[]) {
  return sql.join<string>(exprs, sql`||`)
}

Using the upper helper, our query would look like this:

const lastName = 'STALLONE'

const persons = await db
  .selectFrom('person')
  .select(['id', 'first_name'])
  .where(({ eb, ref }) => eb(
    upper(ref('last_name')), '=', lastName
  ))
  .execute()

The recipe for helper functions is simple: take inputs as Expression<T> instances where T is the type of the expression. For example upper takes in any string expression since it transforms strings to upper case. If you implemented the round function, it'd take in Expression<number> since you can only round numbers.

The helper functions should then use the inputs to create an output that's also an Expression. Everything you can create using the expression builder is an instance of Expression. So is the output of the sql template tag and all methods under the sql object. Same goes for SelectQueryBuilder and pretty much everything else in Kysely. Everything's an expression.

See this recipe to learn more about expressions.

So we've learned that everything's an expression and that expressions are composable. Let's put this idea to use:

const persons = await db
  .selectFrom('person')
  .select(['id', 'first_name'])
  .where(({ eb, ref, val }) => eb(
    concat(
      lower(ref('first_name')),
      val(' '),
      upper(ref('last_name'))
    ),
    '=',
    'sylvester STALLONE'
  ))
  .execute()

So far we've only used our helper functions in the first argument of where but you can use them anywhere:

const persons = await db
  .selectFrom('person')
  .innerJoin('pet', (join) => join.on(eb => eb(
    'person.first_name', '=', lower(eb.ref('pet.name'))
  )))
  .select(({ ref, val }) => [
    'first_name',
    // If you use a helper in `select`, you need to always provide an explicit
    // name for it using the `as` method.
    concat(ref('person.first_name'), val(' '), ref('pet.name')).as('name_with_pet')
  ])
  .orderBy(({ ref }) => lower(ref('first_name')))
  .execute()

Reusable helpers using `ExpressionBuilder`

Here's an example of a helper function that uses the expression builder instead of raw SQL:

import { Expression, expressionBuilder } from 'kysely'

function idsOfPersonsThatHaveDogNamed(name: Expression<string>) {
  const eb = expressionBuilder<DB>()

  // A subquery that returns the identifiers of all persons
  // that have a dog named `name`.
  return eb
    .selectFrom('pet')
    .select('pet.owner_id')
    .where('pet.species', '=', 'dog')
    .where('pet.name', '=', name)
}

And here's how you could use it:

const dogName = 'Doggo'

const persons = await db
  .selectFrom('person')
  .selectAll('person')
  .where((eb) => eb(
    'person.id', 'in', idsOfPersonsThatHaveDogNamed(eb.val(dogName))
  ))
  .execute()

Note that idsOfPersonsThatHaveDogNamed doesn't execute a separate query but instead returns a subquery expression that's compiled as a part of the parent query:

select
  person.*
from
  person
where
  person.id in (
    select pet.owner_id
    from pet
    where pet.species = 'dog'
    and pet.name = ?
  )

In all our examples we've used the following syntax:

.where(eb => eb(left, operator, right))

When the expression builder eb is used as a function, it creates a binary expression. All binary expressions with a comparison operator are represented as a Expression<SqlBool>. You don't always need to return eb(left, operator, right) from the callback though. Since Expressions are composable and reusable, you can return any Expression<SqlBool>.

This means you can create helpers like this:

function isOlderThan(age: Expression<number>) {
  return sql<SqlBool>`age > ${age}`
}

const persons = await db
  .selectFrom('person')
  .select(['id', 'first_name'])
  .where(({ val }) => isOlderThan(val(60)))
  .execute()

Dealing with nullable expressions

If you want your helpers to work with nullable expressions (nullable columns etc.), you can do something like this:

import { Expression } from 'kysely'

// This function accepts both nullable and non-nullable string expressions.
function toInt<T extends string | null>(expr: Expression<T>) {
  // This returns `Expression<number | null>` if `expr` is nullable
  // and `Expression<number>` otherwise.
  return sql<T extends null ? (number | null) : number>`(${expr})::integer`
}

Passing select queries as expressions

Let's say we have the following query:

const expr: Expression<{ name: string }> = db
  .selectFrom('pet')
  .select('pet.name')

The expression type of our query is Expression<{ name: string }> but SQL allows you to use a query like that as an Expression<string>. In other words, SQL allows you to use single-column record types like scalars. Most of the time Kysely is able to automatically handle this case but with helper functions you need to use $asScalar() to convert the type. Here's an example:

const persons = await db
  .selectFrom('person')
  .select((eb) => [
    'id',
    'first_name',
    upper(
      eb.selectFrom('pet')
        .select('name')
        .whereRef('person.id', '=', 'pet.owner_id')
        .limit(1)
        .$asScalar() // <-- This is needed
        .$notNull()
    ).as('pet_name')
  ])

The subquery is an Expression<{ name: string }> but our upper function only accepts Expression<string>. That's why we need to call $asScalar(). $asScalar() has no effect on the generated SQL. It's simply a type-level helper.

We also used $notNull() in the example because our simple upper function doesn't support nullable expressions.

Reusable helpers using ExpressionBuilder​

Dealing with nullable expressions​

Passing select queries as expressions​

Reusable helpers using `ExpressionBuilder`

Dealing with nullable expressions

Passing select queries as expressions