So, we set out to find a replacement to better fit our needs. Having worked with Knex before, we were very impressed with Kysely. It's a modern query builder with a great TypeScript focus. As we'll see here, it also performs a lot better than Prisma.

To set the context a bit, the project we're working on uses Nest.js and it has a database with around 30 tables. It has support for localization and several many-to-many relations. It's a backend for iOS and Android apps. We'll use a sample database with a similar size since we cannot share the database schema here.

We spent a couple of days getting acquainted with Kysely before deciding on our approach. Our setup allowed us to refactor from Prisma piece by piece. This is my attempt at documenting my findings and thoughts on why Prisma didn't cut it for us. Fair warning: opinions ahead, your miles may vary, etc.

High-level comparison - Prisma vs Kysely

Prisma sits somewhere between a traditional ORM and a query builder. Prisma uses a single schema file for defining the model. It then uses that to generate database migrations in SQL files. This alone makes Prisma's DX one of the best among ORMs and query builders. Prisma still lacks some common features though, such as compatibility with views, custom column constraints, and handling nullable unique indexes.

Prisma also heavily abstracts SQL, so it's pretty much impossible to optimize the SQL it runs. For example, Prisma's generated SQL prefers executing several queries over using joins. Depending on your use case, this might be a good or bad thing.

In contrast, Kysely is a query builder. The downside (upside) is that you don't get as many batteries included as you would with Prisma. It does have some migration support but is not as well-defined as Prisma's. You also don't get the TypeScript types generated for you out of the box, but there are tools to help you there. For example, you can use kysely-codegen to generate TypeScript definitions from your database. Or use prisma-kysely to reuse a Prisma schema file. Because it's a lot closer to writing raw SQL, it can also be a lot more powerful.

Not having everything included can be a good thing. With Kysely you can pick whichever database driver you need ("dialect" in Kysely's terms). You can use the official pg driver, or you can use the newer postgres.js alternative. We did not notice a considerable performance difference between the two though. Likely because Kysely already does the heavy lifting.

Drizzle deserves an honorable mention here. It is another query builder with features like Kysely. One upside with Drizzle is that their migrations, like Prisma, are only SQL. Also, you write Drizzle's schemas in TypeScript but don't use the up/down approach of Kysely. This post won't cover it in detail, but we'll reference it in some places as an extra point of comparison.

Performance - fetching 1,000 films

This is using the DVD Rental sample database from PostgreSQL Tutorial. I've set up a sample repository for you to run the queries below and play around further with each setup. As a bonus, there's also a Drizzle test included.

The idea is this: fetch the most recent 1,000 films with their categories and actors included. Order films alphabetically if they have the same release year. Also, order categories and actors alphabetically.

Note: Prisma's prisma db pull command does not let you map names to camel-case. Kysely's CamelCasePlugin can do this for you though. If we wanted to achieve the same result in Prisma we'd have to manually add @map(...) and @@map(...) as needed. To save some time, the generated Prisma schema uses snake_case.

const latestFilms = await db
  .selectFrom("film")
  .limit(1000)
  .orderBy(["film.releaseYear desc", "film.title asc"])
  .selectAll("film")
  .groupBy("film.filmId")
  .select((eb) => [
    jsonArrayFrom(
      eb
        .selectFrom("filmCategory")
        .innerJoin(
          "category",
          "category.categoryId",
          "filmCategory.categoryId"
        )
        .where("filmCategory.filmId", "=", eb.ref("film.filmId"))
        .orderBy(["category.name asc"])
        .selectAll("category")
    ).as("categories"),
    jsonArrayFrom(
      eb
        .selectFrom("filmActor")
        .innerJoin("actor", "actor.actorId", "filmActor.actorId")
        .where("filmActor.filmId", "=", eb.ref("film.filmId"))
        .orderBy(["actor.lastName asc", "actor.firstName asc"])
        .selectAll("actor")
    ).as("actors"),
  ]);

const latestFilmsQuery = await prisma.film.findMany({
  include: {
    film_actor: {
      include: {
        actor: true,
      },
      orderBy: [
        { actor: { last_name: "asc" } },
        { actor: { first_name: "asc" } },
      ],
    },
    film_category: {
      include: {
        category: true,
      },
      orderBy: [{ category: { name: "asc" } }],
    },
  },
  take: 1000,
  orderBy: [{ release_year: "desc" }, { title: "asc" }],
});

At first glance, Prisma wins because the "query" looks so simple. But when we consider how Prisma executes the queries, it's not as simple anymore. Yes, it's a bit of a contrived example, but it does highlight some interesting points. I've run into similar queries in various projects, so it's not an unlikely scenario.

The queries are not a perfect apples-to-apples comparison. As mentioned, out of the box, Prisma doesn't do inner joins as our Kysely query does. It runs a separate query for each table it encounters. Prisma will only rely on joins if needed for filters. This is likely fine for smaller sets of data and can even outperform Kysely's query. But once you start fetching many rows, Kysely performs a lot better.

On my machine, I usually see Kysely averages around 50 ms for the above, while Prisma averages 110 ms. Again, the queries differ, but they do give the same core data back.

We also tried Prisma's preview feature relationJoins. While it's closer to what our Kysely query does, it's a lot slower at 240 ms. Finally, Drizzle runs that same query at around 75 ms using the pg driver. Surprisingly, its postgres.js driver was a bit slower.

One can also argue that Kysely's result is easier to reason about. With Prisma, you get all the nested many-to-many types in there too. If we were working on a backend API, then our front-end developers would not be happy if we returned that as-is. With Kysely you just get the data you requested and nothing more.

Migrations - a few options

You could keep using Prisma's migration style and generate types via prisma-kysely. This works well as long as you don't need to do something the Prisma schema does not support. Support for views is coming soon, but based on the preview feature they will be a bit awkward to manage.

Prisma's schema does not work with custom check constraints or multi-column unique indexes. If you customize the generated SQL, Prisma may not do what you might expect.

Another option is to use Kysely's built-in migrations. If you're familiar with Knex this should be trivial and a good approach. I never went this route so I can't say whether it comes with any challenges. One upside is that you just write TypeScript and don't need to learn Prisma's schema oddities.

A third option is to use a third-party CLI for forward-only SQL migrations. You can also write some custom scripts for that. The upside of this approach is that it forces you to understand the underlying schema. And then rely on kysely-codegen to generate the type definitions.

Finally, you could use Drizzle Kit to manage your schema but still use Kysely to write your queries. The Drizzle ORM includes support for that out of the box.

Compatibility - more than you bargained for

Prisma is very heavy to install at over 15 MB. Kysely, in comparison, takes a bit over 2 MB. This makes Prisma not the ideal choice when running code in a serverless context. As we understand it, Prisma relies on a 4 MB WASM file for its query engine, written in Rust. There's nothing wrong with Rust, but Kysely is simply a lot leaner.

Prisma's query engine needs to support PostgreSQL, SQLite, MongoDB, and more. With Kysely, like many other Node.js database libraries out there, you only need to install a driver.

Summary - wrong batteries

We're not the first to sing high praise of Kysely and feel let down by Prisma. Kysely will be a main contender for us to interact with SQL from now on. One takeaway from all this is that you should consider what you need out of a library, but also what you don't need. If not, you will fight against it and lose productivity.