Creating Reusable Helper Functions

Build composable, type-safe helper functions for common SQL operations

View this recipe in the playground

Kysely doesn’t have built-in functions for every SQL operation, but it provides the tools to create your own composable, type-safe helpers.

The problem

Let’s say you want to write this query:

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

Kysely doesn’t have a built-in upper function. You have several options:

const lastName = 'STALLONE'

const persons = await db
  .selectFrom('person')
  .select(['id', 'first_name'])
  // 1. `sql` template tag - least type-safe
  // No type-checking for column names, plugins won't affect it
  .where(
    sql<string>`upper(last_name)`, '=', lastName
  )
  // 2. `sql` with `ref` - better type safety
  // Column names are type-checked against query context
  .where(({ eb, ref }) => eb(
    sql<string>`upper(${ref('last_name')})`, '=', lastName
  ))
  // 3. `fn` function - best option
  // Avoids syntax errors, uses refs as first-class arguments
  .where(({ eb, fn }) => eb(
    fn<string>('upper', ['last_name']), '=', lastName
  ))
  .execute()

Each option has tradeoffs in readability and type safety.

Creating helper functions

Fortunately, Kysely allows you to create reusable, type-safe helpers:

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`||`)
}

Now our query is much cleaner:

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

The pattern for helper functions is simple:

1. Accept expressions as input

   Take inputs as Expression<T> where T is the type of the expression.

   • upper takes Expression<string> (transforms strings)
   • round would take Expression<number> (only rounds numbers)
2. Return an expression

   Use the inputs to create an output that’s also an Expression. Everything in Kysely is an expression:

   • Expression builder outputs
   • sql template tag results
   • SelectQueryBuilder instances
   • Pretty much everything else

See the expressions recipe to learn more about how expressions work.

Composing helpers

Since everything is an expression, helpers are composable:

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()

Using helpers anywhere

Helpers work in any part of a query:

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',
    // In select, always provide an explicit name with `as`
    concat(ref('person.first_name'), val(' '), ref('pet.name')).as('name_with_pet')
  ])
  .orderBy(({ ref }) => lower(ref('first_name')))
  .execute()

When using a helper in select, you must always provide an explicit name using the as method.

Helpers using ExpressionBuilder

You can create helpers that use the expression builder instead of raw SQL:

import { Expression, expressionBuilder } from 'kysely'

function idsOfPersonsThatHaveDogNamed(name: Expression<string>) {
  const eb = expressionBuilder<DB>()
  
  // Returns a subquery
  return eb
    .selectFrom('pet')
    .select('pet.owner_id')
    .where('pet.species', '=', 'dog')
    .where('pet.name', '=', name)
}

Usage:

const dogName = 'Doggo'

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

This generates a single query with a subquery:

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

Boolean expressions

You can create helpers that return boolean expressions:

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()

When using eb as a function like eb(left, operator, right), it creates a binary expression. All binary expressions with comparison operators are Expression<SqlBool>. You can return any Expression<SqlBool> from the callback.

Handling nullable expressions

To support nullable expressions, use conditional types:

import { Expression } from 'kysely'

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

Converting subquery types

SQL allows single-column subqueries to be used as scalars. Use $asScalar() to convert the type:

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() // Converts Expression<{ name: string }> to Expression<string>
        .$notNull()
    ).as('pet_name')
  ])
  .execute()

$asScalar() has no effect on the generated SQL — it’s purely a type-level helper.

Key takeaways

Everything is an expression

All Kysely constructs are composable expressions

Type-safe inputs

Use Expression<T> for inputs to maintain type safety

Reusable and composable

Helpers can be used together and anywhere in queries

No runtime overhead

Helpers are just TypeScript — they compile away