I'm writing this down as it reflects a use case we had in a recent project. As it is often the case with blog posts, a positive side effect is that they may also prove useful to others. So, let's begin.

There are instances where using a full-fledged ORM like Prisma or Drizzle may not be desirable due to the need for more delicate control over performance and other factors. However, resorting to writing the SQL by yourself and risking security issues like SQL injection is not ideal either.

A balanced approach that sits between raw SQL queries and comprehensive ORMs is the utilization of a query builder like Kysely. It is lightweight, and its usage is remarkably similar to writing raw SQL statements, minus the potential for shooting yourself in the foot.

Despite which database access method you use, often you find yourself in a situation where you want to use some database-specific function, but you're not sure how to integrate it into your current approach.

It happened to me last week when I had to use a Postgres-specific function in my query using Kysely. This article outlines the approach I chose by utilizing the expression builder.

The Problem

Having a lightweight database abstraction is also a good thing when it comes to migrating from one database system to another. Yah, I know, this is a wonderful joke, because, let's be honest, it never happens. 🤡

Focusing on this portability is also often the reason why we shy away from using database-specific functions directly in the query. That said, why do we avoid using the powerful capabilities a database system like Postgres provides and make our lives harder in this regard?

Isn't it hard to use database-specific functionality when using a query builder?

Yes, kind of. Why? We're not used to it. In the following, we will create such a case, and I will showcase how to integrate Postgres-specific functions into the query.

The Use Case

I want to select all rows of a specific month.

In our scenario here, we have a table which contains user availabilities. An availability consists of a start and an end timestamp.

Now, we want to build a user interface where the user sees their availabilities based on a specific month. Imagine a date picker where you can jump between month. After jumping to a month, the availabilities of that month should be visible.

Luckily, we utilize Postgres so that we can use the handy date_trunc function that allows the truncation of a date to the specific month.

SELECT id, start
  FROM availabilities
WHERE date_trunc('MONTH', start) = date_trunc('MONTH', DATE('2024-01-02 08:00:00+00'));

What we basically do here is truncate the value of start in each row to the respective month and compare it with another timestamp that was also truncated to the respective month. The timestamp 2024-01-02 08:00:00+00 in this example is just a demo timestamp. After truncating, it can be seen as 2024-01.

The Solution

This chapter is meant for showcasing the solution. Due to the fact that we already have the solution defined from a query perspective. The open question is:

How to use Postgres-specific functions in Kysely?

Using database-specific functions in Kysely is pretty straightforward. Instead of using the .where clause by passing separate arguments, you can also define a function whose first argument is an expression builder. With that in place, you can go crazy and craft a structure like:

const currentDate = new Date().toISOString();

// Query all availabilities of the current month
await kysely
  .selectFrom("availabilities")
  .where((eb) => {
    const month = sql.lit("MONTH");

    const left = eb.fn("date_trunc", [month, eb.ref("start")]);
    const right = eb.fn("date_trunc", [month, eb.val(currentDate)]);

    return eb(left, "=", right);
  })
  .select(["id", "start"])
  .orderBy("start")

Conclusion

We learned how we can leverage database-specific capabilities without jumping back into a raw SQL statement while still using the mechanisms (and therefore security guarantees) of the query builder.

Kysely's expression builder proves to be quite powerful. This tool can confidently solve numerous scenarios before there's any need to resort to the escape hatch of engaging with raw SQL queries.