ORMs vs. Query Builders for your Typescript application
Comparing type-safe SQL libraries
Typed SQL libraries for Typescript are designed to provide a robust, maintainable, and developer-friendly interface between your code and the database. However, choosing the right library for your Typescript project can be difficult since they all offer similar functionalities.
This blog post will discuss choosing the right SQL library for your Typescript application by comparing prominent Typescript-based libraries designed to work with Postgres databases: Drizzle, Kysely, Prisma, and Zapatos.
Note that this article is not meant to be an in-depth comparison between the tools, and some of the examples are simplified. We highly encourage you to dig deeper into the libraries to decide which tool to use for your Typescript projects.
These four libraries typically fall under two different categories: Object-Relation Mappers and Query Builders. Let’s start by explaining what they are and the difference between them.
Object Relation Mappers (ORMs)
ORMs manage the synchronization between your objects or classes and the corresponding database tables. However, they can have limitations regarding complex queries and can lead to performance issues. Those abstractions and “magic under the hood” are some of the motivations behind alternative solutions such as Query Builders.
These libraries allow you to declare your tables in Typescript and write your queries in a way that closely mirrors raw SQL. They leverage the Typescript type system to infer the return types of any complex query. However, they come with limitations, especially when it comes to handling things like migrations.
We will also use the following criteria to compare the SQL libraries:
- Learning curve
- Support for complex queries
- SQL migrations
- Supported runtimes
- And, most importantly, their communities
What about performance, You ask?
After a small experiment comparing Drizzle, Kysely, Prisma, and Zapatos with Postgres databases, their average latencies were within 3ms, while their 95th percentile was within 2ms.
Latency is in the eye of the beholder and heavily depends on the circumstances in which the framework is used. We leave it as an exercise for the reader to measure their latencies.
We’ll pick two factors for the learning curve: schema definition and query syntax. In the first area, Kysely offers the lowest barrier to entry since it uses Typescript interfaces for a cleaner and more direct representation.
Drizzle and Zapatos also use Typescript but introduce new types and functions to create table schema.
In contrast, Prisma uses its own Schema Language (PSL) for defining the database schema, relations, and constraints. Although the syntax is not so different from a language such as Typescript, PSL uses attributes and functions such as
autoincrement to modify the behavior of a field.
Luckily for us, SQL is the only language you need to know to generate your Typescript types. Drizzle, Kysely, Prisma, and Zapatos provide commands to introspect the database and generate Typescript-type definitions based on your schema. Kysely does not include a built-in CLI but utilizes a third-party library for code generation,
Do you like the SQL syntax? or prefer a Typescript-centric or even a human-readable syntax? Conveniently, there is much to choose from regarding SQL libraries in your Typescript application.
Drizzle’s principle is “If you know SQL, you know Drizzle ORM. Drizzle uses a SQL-like syntax for running queries and focuses on a function-based approach.
Here is another example with Left JOIN:
The code above would generate the following SQL query:
Find out more about printing SQL queries in Drizzle ORM’ in their ‘s docs.
Kysely and Zapatos, on the other hand, use a Typescript-centric syntax focusing on type safety and interfaces.
However, it’s important to note that although Knex inspires Kysely, it doesn’t share its abstraction layer. Kysely’s design decisions are driven by the “what you see is what you get” principle. For example:
Will generate the following SQL:
You can find more examples and side-by-side comparisons between Kysely code and compiled SQL on Kysely’s community playground.
Syntax-wise, Prisma offers a human-readable approach because the ORM was designed with all kinds of data stores in mind, including SQL and NoSQL databases.
In summary, Drizzle is the most intuitive if you are familiar with SQL. Zapatos and Kysely SQL keywords are Typescript-friendly, with WYSIWYG driving design decisions in Kysely. Prisma supports databases other than relational ones, which is why they favor a human-readable syntax that works with all.
Support for complex SQL queries
In some cases, you need more complex SQL queries that are impossible using a standard ORM. That’s why support for raw SQL queries is important when choosing the right tool for your Typescript application.
Here, Drizzle, Kysely, Prisma, and Zapatos all support raw SQL queries using template tags, which make your queries SQL injection-proof. Below are examples of how to use raw SQL queries:
SQL Migrations and the single source of truth
Migrations are essential to working with databases, as they allow developers to update and manage their schema over time.
Prisma and Drizzle provide a similar approach to handling migrations using their respective CLIs. At the same time, Kysely offers flexibility and strong typing but lacks a built-in CLI and requires developers to handle Typescript compilation.
Note that Zapatos doesn’t handle migrations. However, its CLI speaks to your Postgres database to generate types, ensuring your schema and Typescript type definitions are always consistent.
Let’s have a closer look at both approaches to SQL migrations:
Prisma Migrate keeps track of the migration history in a dedicated _prisma_migrations table, ensuring that your database schema is always up-to-date and consistent.
To create and apply migrations, you can use the Prisma CLI commands:
prisma migrate dev command tracks database changes, automatically generates SQL migration files in the /prisma/migrations folder and applies them to the database. It also updates the _prisma_migrations table in the database.
prisma migrate deploy command syncs your migration history from the development environment to your staging or production database. It compares applied migrations with the migration history, applies pending migrations, and updates the _prisma_migrations table accordingly. This command is typically ran in a CI/CD pipeline.
In Drizzle, the
drizzle-kit CLI generates SQL migrations. The CLI traverses the schema folder, generates a schema snapshot, and compares it to the previous version if there’s one. Based on the difference, it will generate all needed SQL migrations, and if there are any automatically unresolvable cases like renames, it will prompt the user for input.
You can run migrations with
drizzle-kit using the following command:
For schema file:
It will generate:
Kysely does not provide a built-in CLI for running migrations. Instead, it offers a flexible approach, allowing developers to write migration files using Typescript. Developers can create “up” and “down” functions in the migration files, which are responsible for updating the database schema to the next version or rolling back to a previous version.
Migrations in Kysely can use the Kysely.schema module to modify the schema and run normal queries to modify data.
Here is a summary of the SQL migrations
|Prisma||– Built-in CLI tool for generating and running migrations.|
– Ensures a consistent and up-to-date database schema.The prisma migrate dev command automatically generates SQL migration files and applies them to the database.
– The prisma migrate deploy command syncs migration history between development and production environments.
|– Not flexible when handling specific migration scenarios.|
|Drizzle||– Built-in CLI tool for generating and running migrations.Automates the process of generating SQL migrations.|
– Prompts users for input in cases that cannot be resolved automatically, like renames.
|– May not cover all edge cases or complex scenarios.|
– May not provide as much control over migrations as other solutions.
|Kysely||– Flexible and customizable approach to migrations.|
– Strong typing for database schema changes.
|– No built-in CLI for running migrations.|
Ultimately, the choice between SQL migration solutions will depend on your project requirements, the complexity of your schema, and your preference for a more flexible or automated approach.
When comparing edge runtime compatibility, Drizzle, Kysely, and Zapatos have a clear advantage over Prisma.
Drizzle, Kysely, and Zapatos work in edge-runtimes and Postgres using the
@neondatabase/serverless driver or the
@vercel/kysely driver, which allows developers to deploy their applications on edge networks, taking advantage of lower latency and improved performance.
In contrast, Prisma does not support edge-runtimes (yet) with Postgres. This limitation and lack of support for edge-runtimes may limit its applicability in scenarios where low latency and high availability are crucial.
We are comparing community engagement using Stack Overflow questions and the number of weekly downloads on npm.
The data sheds light on the growth and community engagement of the four libraries. Among these, Prisma is the most popular with the highest weekly downloads, reaching 1,168,489 by 15 May 2023, marking a 58% increase from 1 January. Additionally, Prisma exhibits a robust community on Stack Overflow, with 2,819 associated tags, indicating a substantial user base and active discussion around this tool.
In conclusion, type-safe SQL libraries offer powerful tools to enhance the development experience when working with Typescript and databases.
Despite the differences, community engagement, syntax preference, SQL migrations, and runtimes are the key determining factors in selecting a library.
Prisma remains the most popular of the four, with the largest community and number of questions on Stack Overflow, which makes running into errors more solvable.
Kysely and Zapatos’ more direct representation of table schema, Typescript-centric syntax, and edge-runtime compatibility make it an attractive option for developers looking for a modern and efficient solution.
Meanwhile, Drizzle’s function-based approach to schema definition and built-in CLI tool for managing migrations may appeal to those who prefer a SQL-like syntax and an automated migration process.
Try Neon today for free, and let us know which SQL library you use for your Typescript applications and what other criteria you use to assess tools.