Why We Ditched Drizzle & Knex in favor of Kysely's Querybuilder

Building scalable applications often hinges on precise database interactions. While ORMs promise rapid development, they can obscure SQL and become bottlenecks as complexity grows. Even popular query builders like Knex and Drizzle present challenges. After navigating various projects, we found Kysely offered the best balance of control, maintainability, and crucially, end-to-end type safety. The Challenge: Balancing Abstraction and Control (The Pitfalls of Traditional ORMs) 1. The Illusion of Simplicity: Hidden Complexity ORMs abstract away SQL generation, which can be useful for simple operations. However, this abstraction conceals real database interactions, making it difficult to optimize queries or troubleshoot performance issues. Debugging queries often requires reverse engineering ORM-generated SQL, adding unnecessary complexity, this is very crucial when working with large scale complex apps that often require complex query logic. 2. Limited Customization Many ORMs enforce strict patterns and conventions. When query requirements deviate from standard use cases whether for performance optimizations or non standard joins ORM constraints become a bottleneck. Developers often resort to workarounds, leading to convoluted, less maintainable code.. 3. The Spaghetti Code Scenario: Complex Queries in ORM Syntax For simple CRUD operations, ORM syntax is manageable. However, for queries involving multiple joins, subqueries, or aggregations, ORM APIs become cumbersome and difficult to follow. This not only makes debugging more challenging but also increases the risk of subtle errors, especially in production-grade systems, a nightmare to work with for new devs joining to project. Type Safety: A Crucial Distinction A system is either type-safe or it isn't—there is no in-between. Partial type safety slows development by introducing misleading assurances that a query is correct when, in reality, it's not. A truly type-safe query builder should catch errors at compile time rather than runtime, preventing incorrect table references and typos. Drizzle, for example, gives the impression of type safety, but only applies type validation to query results not the queries themselves. This means you can still write invalid queries, much like with Knex. Kysely, however, aims for stricter, end-to-end type safety. Its typings are designed to understand the query's structure, including aliases and subqueries, making it much harder to formulate logically incorrect joins or references that would only fail at runtime. Kysely vs. Knex vs. Drizzle: A Practical Comparison To illustrate how Kysely improves query safety and maintainability, let's examine a real world SQL query involving subqueries and joins. The goal is to fetch a list of books along with the name of the last lender for each book. SQL Query Representation SELECT "books"."name", "u"."name" AS "username" FROM "books" LEFT JOIN ( SELECT MAX("id") AS "last_lending_id", "lendings"."book_id" FROM "lendings" GROUP BY "book_id" ) AS "l" ON "l"."book_id" = "books"."id" INNER JOIN "lendings" AS "l1" ON "l1"."id" = "l"."last_lending_id" INNER JOIN "users" AS "u" ON "u"."id" = "l1"."user_id"; Knex Implementation: SQL-Like but Error-Prone Knex provides a SQL-like API but lacks type safety, making it prone to runtime errors due to typos or incorrect references. knex("books") .select("books.name", "u.name as username") .leftJoin( (query) => { query .max("id as last_lending_id") .select("lendings.book_id") .from("lendings") .groupBy("book_id") .as("l"); }, function () { this.on("l.book_id", "books.id"); } ) .innerJoin("lendings as l1", "l1.id", "l.last_lending_id") .innerJoin("users as u", "u.id", "l1.user_id"); While the syntax is intuitive for SQL users, Knex does not provide static type validation. Mistakes such as referencing l.book_id incorrectly will only be caught at runtime. Drizzle Implementation: Improved Readability but Partial Type Safety Drizzle attempts to balance SQL-style query building with type safety but falls short in enforcing correctness during query construction. const l = db .select({ book_id: schema.lendings.book_id, last_lending_id: max(schema.lendings.id).as('last_lending_id'), }) .from(schema.lendings) .groupBy(schema.lendings.book_id).as('l'); const l1 = aliasedTable(schema.lendings, 'l1'); const u = aliasedTable(schema.users, 'u'); db .select({ name: schema.books.name, username: u.name, }) .from(schema.books) .leftJoin(l, eq(l.book_id, schema.books.id)) .innerJoin(l1, eq(l1.id, l.last_lending_id)) .innerJoin(u, eq(u.id, l1.user_id)) .execute(); Drizzle improves readability and provides type safety for results and basic query elements. However, notice how alias

Apr 5, 2025 - 09:30

Why We Ditched Drizzle & Knex in favor of Kysely's Querybuilder

Building scalable applications often hinges on precise database interactions. While ORMs promise rapid development, they can obscure SQL and become bottlenecks as complexity grows. Even popular query builders like Knex and Drizzle present challenges. After navigating various projects, we found Kysely offered the best balance of control, maintainability, and crucially, end-to-end type safety.

The Challenge: Balancing Abstraction and Control (The Pitfalls of Traditional ORMs)

1. The Illusion of Simplicity: Hidden Complexity

ORMs abstract away SQL generation, which can be useful for simple operations. However, this abstraction conceals real database interactions, making it difficult to optimize queries or troubleshoot performance issues. Debugging queries often requires reverse engineering ORM-generated SQL, adding unnecessary complexity,
this is very crucial when working with large scale complex apps that often require complex query logic.

2. Limited Customization

Many ORMs enforce strict patterns and conventions. When query requirements deviate from standard use cases whether for performance optimizations or non standard joins ORM constraints become a bottleneck. Developers often resort to workarounds, leading to convoluted, less maintainable code..

3. The Spaghetti Code Scenario: Complex Queries in ORM Syntax

For simple CRUD operations, ORM syntax is manageable. However, for queries involving multiple joins, subqueries, or aggregations, ORM APIs become cumbersome and difficult to follow. This not only makes debugging more challenging but also increases the risk of subtle errors, especially in production-grade systems, a nightmare to work with for new devs joining to project.

Type Safety: A Crucial Distinction

A system is either type-safe or it isn't—there is no in-between. Partial type safety slows development by introducing misleading assurances that a query is correct when, in reality, it's not. A truly type-safe query builder should catch errors at compile time rather than runtime, preventing incorrect table references and typos.

Drizzle, for example, gives the impression of type safety, but only applies type validation to query results not the queries themselves. This means you can still write invalid queries, much like with Knex.

Kysely, however, aims for stricter, end-to-end type safety. Its typings are designed to understand the query's structure, including aliases and subqueries, making it much harder to formulate logically incorrect joins or references that would only fail at runtime.

Kysely vs. Knex vs. Drizzle: A Practical Comparison

To illustrate how Kysely improves query safety and maintainability, let's examine a real world SQL query involving subqueries and joins. The goal is to fetch a list of books along with the name of the last lender for each book.

SQL Query Representation

SELECT
  "books"."name",
  "u"."name" AS "username"
FROM
  "books"
  LEFT JOIN (
    SELECT
      MAX("id") AS "last_lending_id",
      "lendings"."book_id"
    FROM
      "lendings"
    GROUP BY
      "book_id"
  ) AS "l" ON "l"."book_id" = "books"."id"
  INNER JOIN "lendings" AS "l1" ON "l1"."id" = "l"."last_lending_id"
  INNER JOIN "users" AS "u" ON "u"."id" = "l1"."user_id";

Knex Implementation: SQL-Like but Error-Prone

Knex provides a SQL-like API but lacks type safety, making it prone to runtime errors due to typos or incorrect references.

knex("books")
  .select("books.name", "u.name as username")
  .leftJoin(
    (query) => {
      query
        .max("id as last_lending_id")
        .select("lendings.book_id")
        .from("lendings")
        .groupBy("book_id")
        .as("l");
    },
    function () {
      this.on("l.book_id", "books.id");
    }
  )
  .innerJoin("lendings as l1", "l1.id", "l.last_lending_id")
  .innerJoin("users as u", "u.id", "l1.user_id");

While the syntax is intuitive for SQL users, Knex does not provide static type validation. Mistakes such as referencing l.book_id incorrectly will only be caught at runtime.

Drizzle Implementation: Improved Readability but Partial Type Safety

Drizzle attempts to balance SQL-style query building with type safety but falls short in enforcing correctness during query construction.

const l = db
  .select({
    book_id: schema.lendings.book_id,
    last_lending_id: max(schema.lendings.id).as('last_lending_id'),
  })
  .from(schema.lendings)
  .groupBy(schema.lendings.book_id).as('l');

const l1 = aliasedTable(schema.lendings, 'l1');
const u = aliasedTable(schema.users, 'u');

db
  .select({
    name: schema.books.name,
    username: u.name,
  })
  .from(schema.books)
  .leftJoin(l, eq(l.book_id, schema.books.id))
  .innerJoin(l1, eq(l1.id, l.last_lending_id))
  .innerJoin(u, eq(u.id, l1.user_id))
  .execute();

Drizzle improves readability and provides type safety for results and basic query elements. However, notice how aliases (l, l1, u) are defined. While Drizzle types the result correctly, it doesn't inherently prevent runtime errors if you mistakenly used schema.lendings.id instead of l.last_lending_id in the later joins within the query construction. The types guide you, but the logical connection isn't always enforced at compile time in these complex cases.

Kysely Implementation: Fully Type-Safe and SQL-Like

Kysely enforces strict type safety across the entire query-building process, preventing incorrect joins and typos at compile time.

db
  .selectFrom("books")
  .leftJoin(
    (eb) =>
      eb
        .selectFrom("lendings")
        .select((sl) => [
          sl.fn.max("id").as("last_lending_id"),
          "lendings.book_id",
        ])
        .groupBy("book_id")
        .as("l"),
    (join) => join.onRef("l.book_id", "=", "books.id")
  )
  .innerJoin("lendings as l1", "l1.id", "l.last_lending_id")
  .innerJoin("users as u", "u.id", "l1.user_id")
  .select(["books.name", "u.name as username"]);

Kysely's strict typing shines here. The use of functions like (eb) => eb.selectFrom(...) and (join) => join.onRef(...) allows Kysely's types to track the available tables and columns within the current scope, including aliases from subqueries (l). Referencing l.book_id is type-checked against the subquery's output. Attempting to reference an invalid column or alias here would result in a compile-time TypeScript error, preventing runtime failures.

Unlike Knex and Drizzle, Kysely ensures that every part of the query is type-checked, preventing errors before execution. Additionally, its API remains readable while enforcing correctness.

Key Takeaways: Why Kysely Stands Out

Strict Type Safety: Kysely prevents incorrect table references, column names, and joins at compile time, eliminating common runtime errors.
SQL-Like Query Construction: While different from traditional SQL in ordering, Kysely's API ensures logical query composition.
Dynamic Yet Maintainable: Kysely's API maps closely to SQL concepts while ensuring logical query construction.
Avoids the Pitfalls of ORMs: Unlike ORMs, Kysely doesn’t obscure SQL execution, giving developers full control over performance optimizations.

Conclusion: Embrace Type-Safe Query Building

While Knex offers flexibility and Drizzle provides a good step towards type safety, both can still allow subtle bugs in complex queries to reach runtime.
Drizzle improves upon Knex by adding type safety to query results but falls short in enforcing correctness during query construction.

Kysely, on the other hand, achieves full end-to-end type safety while maintaining a SQL-like, flexible API. If your system demands precise control over database interactions and robust type validation, Kysely provides the clarity and safety needed for scalable, high-performance applications.

For us, adopting Kysely was a game changer, and for anything beyond simple prototypes, it's now our go-to choice.

What are your experiences with these query builders? Share your thoughts in the comments!