Typed Query Builders: Kysely vs. Drizzle

Guillaume Billey
Guillaume BilleyJune 26, 2025
#js#node-js#database#typescript

At Marmelab, we believe a pleasant development environment leads to better customer satisfaction. Modern tools significantly boost DX, and in the JavaScript ecosystem, static typing is a prime example. For our current project, we wanted to add typing to our Knex.js-based query builder. This led us to compare Kysely and Drizzle, with a focus on schema generation, querying, and migrations, from a DX standpoint.

Our Testing Ground: The E-commerce Schema

To provide concrete examples, we'll use a simplified e-commerce schema with Categories, Products, Customers, Orders, and OrderItems tables. This setup allows us to illustrate various query complexities and how each library handles them. Since our real-world project uses Knex, we'll assume the sample project also leverages Knex.

E-commerce Schema

You can access the sample project on GitHub: https://github.com/marmelab/kysely-vs-drizzle.

The Challengers : Kysely and Drizzle

We chose two prominent libraries for our comparison:

  • Kysely: Think of Kysely as an augmented Knex.js. It maintains a similar migration mechanism and querying syntax to Knex.js but significantly enhances typing support. Kysely focuses on providing a powerful query builder with strong typing and intelligent autocompletion.
  • Drizzle: Drizzle is a relatively new ORM in the TypeScript world. It's a lightweight, SQL-first, type-safe ORM that lets you define your schema in TypeScript, generates migrations, and offers a convenient relational API for joins.

Get Your Schema On!

Our first step was to generate a schema from our existing application. Let's see how each tool handles this.

Kysely

Kysely doesn't have a native schema generation tool, but it suggests several community-contributed options. We chose kysely-codegen

From the root of our sample project, we ran:

    npx kysely-codegen --env-file .env.local

Kysely-codegen connects to the DATABASE_URL (provided via .env.local in our case) and generates the schema.

/**
 * This file was generated by kysely-codegen.
 * Please do not edit it manually.
 */

import type { ColumnType } from "kysely";

export type Generated<T> = T extends ColumnType<infer S, infer I, infer U>
  ? ColumnType<S, I | undefined, U>
  : ColumnType<T, T | undefined, T>;

export type Numeric = ColumnType<string, number | string, number | string>;

export type Timestamp = ColumnType<Date, Date | string, Date | string>;

export interface Categories {
  category_id: Generated<number>;
  name: string;
}

export interface Customers {
  created_at: Generated<Timestamp | null>;
  customer_id: Generated<number>;
  email: string;
  first_name: string;
  last_name: string;
}

export interface OrderItems {
  order_id: number | null;
  order_item_id: Generated<number>;
  product_id: number | null;
  quantity: number;
  unit_price: Numeric;
}

export interface Orders {
  customer_id: number | null;
  order_date: Generated<Timestamp | null>;
  order_id: Generated<number>;
  total_amount: Numeric;
}

export interface Products {
  category_id: number | null;
  created_at: Generated<Timestamp | null>;
  description: string | null;
  name: string;
  price: Numeric;
  product_id: Generated<number>;
}

export interface DB {
  categories: Categories;
  customers: Customers;
  order_items: OrderItems;
  orders: Orders;
  products: Products;
}

We then used this schema to initialize Kysely:

import { DB } from './schema' // this is the Database interface we defined earlier
import { Kysely, PostgresDialect } from 'kysely'
import { pgPool } from '../../pgPool'

const dialect = new PostgresDialect({
  pool: pgPool
})

export const db = new Kysely<DB>({
  dialect,
})

Drizzle

Drizzle includes its own tool for generating schemas from an existing database: drizzle-kit. This CLI helps manage migrations.

We ran the following command:

DATABASE_URL=postgresql://user:password@localhost:5432/mydb npx drizzle-kit pull

[...]

[✓] Your SQL migration file ➜ src/drizzle/db/0000_cool_red_wolf.sql 🚀
[✓] Your schema file is ready ➜ src/drizzle/db/schema.ts 🚀
[✓] Your relations file is ready ➜ src/drizzle/db/relations.ts 🚀

Drizzle-kit inspects the database and generates schema.ts, relations.ts, and an initial migration file.

The schema.ts file defines the entities and their typings, appearing a bit more complex than the Kysely schema:

import { pgTable, unique, serial, varchar, foreignKey, text, numeric, integer, timestamp } from "drizzle-orm/pg-core"
import { sql } from "drizzle-orm"



export const categories = pgTable("categories", {
	categoryId: serial("category_id").primaryKey().notNull(),
	name: varchar({ length: 50 }).notNull(),
}, (table) => [
	unique("categories_name_key").on(table.name),
]);

export const products = pgTable("products", {
	productId: serial("product_id").primaryKey().notNull(),
	name: varchar({ length: 100 }).notNull(),
	description: text(),
	price: numeric({ precision: 10, scale:  2 }).notNull(),
	categoryId: integer("category_id"),
}, (table) => [
	foreignKey({
			columns: [table.categoryId],
			foreignColumns: [categories.categoryId],
			name: "products_category_id_fkey"
		}),
]);

export const customers = pgTable("customers", {
	customerId: serial("customer_id").primaryKey().notNull(),
	firstName: varchar("first_name", { length: 50 }).notNull(),
	lastName: varchar("last_name", { length: 50 }).notNull(),
	email: varchar({ length: 100 }).notNull(),
}, (table) => [
	unique("customers_email_key").on(table.email),
]);

export const orders = pgTable("orders", {
	orderId: serial("order_id").primaryKey().notNull(),
	customerId: integer("customer_id"),
	orderDate: timestamp("order_date", { withTimezone: true, mode: 'string' }).default(sql`CURRENT_TIMESTAMP`),
	totalAmount: numeric("total_amount", { precision: 10, scale:  2 }).notNull(),
}, (table) => [
	foreignKey({
			columns: [table.customerId],
			foreignColumns: [customers.customerId],
			name: "orders_customer_id_fkey"
		}),
]);

export const orderItems = pgTable("order_items", {
	orderItemId: serial("order_item_id").primaryKey().notNull(),
	orderId: integer("order_id"),
	productId: integer("product_id"),
	quantity: integer().notNull(),
	unitPrice: numeric("unit_price", { precision: 10, scale:  2 }).notNull(),
}, (table) => [
	foreignKey({
			columns: [table.orderId],
			foreignColumns: [orders.orderId],
			name: "order_items_order_id_fkey"
		}),
	foreignKey({
			columns: [table.productId],
			foreignColumns: [products.productId],
			name: "order_items_product_id_fkey"
		}),
	unique("unique_order_product").on(table.orderId, table.productId),
]);

The relations.ts file defines all the relationships:

import { relations } from "drizzle-orm/relations";
import { categories, products, customers, orders, orderItems } from "./schema";

export const productsRelations = relations(products, ({one, many}) => ({
	category: one(categories, {
		fields: [products.categoryId],
		references: [categories.categoryId]
	}),
	orderItems: many(orderItems),
}));

export const categoriesRelations = relations(categories, ({many}) => ({
	products: many(products),
}));

export const ordersRelations = relations(orders, ({one, many}) => ({
	customer: one(customers, {
		fields: [orders.customerId],
		references: [customers.customerId]
	}),
	orderItems: many(orderItems),
}));

export const customersRelations = relations(customers, ({many}) => ({
	orders: many(orders),
}));

export const orderItemsRelations = relations(orderItems, ({one}) => ({
	order: one(orders, {
		fields: [orderItems.orderId],
		references: [orders.orderId]
	}),
	product: one(products, {
		fields: [orderItems.productId],
		references: [products.productId]
	}),
}));

We can already observe a key difference: Kysely acts as a query builder focused on that aspect, while Drizzle functions as an ORM, handling more than just query building. This explains the more complex schema Drizzle generates, as it manages a broader range of database interactions.

Querying the Database

With our schemas in place, let's explore how we query the database using these two libraries.

Kysely

Kysely offers a fluent API for building queries, reminiscent of Knex.js. Here’s how we retrieve all categories:

import { db } from './db' // this is the Kysely instance we defined earlier
// Get all products with their categories
export const getAllCategories = async () => {
    const categories = await db
        .selectFrom('categories')
        .selectAll()
        .execute();
    return categories;
}

Observe the autocompletion and type validation in action:

For a more complex query, like fetching all orders with their customers and order items, we wrote:

export const getOrderDetail = async (orderId: number) => {
    const query = db
    .selectFrom('orders')
    .where('orders.order_id', '=', orderId)
    .leftJoin('customers', 'orders.customer_id', 'customers.customer_id')
    .select((eb) => [
        'customers.first_name',
        'customers.last_name',
        jsonArrayFrom(eb.selectFrom('products')
            .leftJoin('order_items', 'order_items.product_id', 'products.product_id')
            .select([
                'products.name', 'products.price'
            ])
            .where('order_items.order_id', '=', orderId)
            .whereRef('products.product_id', '=', 'order_items.product_id'))
            .as('products')
    ]);
    const order = await query.execute();
    return order;
}

The inferred type of the returned value is impressively accurate:

const order: {
    first_name: string | null;
    last_name: string | null;
    products: {
        name: string;
        price: string;
    }[];
}[]

We used Kysely's jsonArrayFrom helper function to retrieve products as an array of JSON objects. This feature is powerful for constructing complex queries with nested structures.

Drizzle

Drizzle also provides a fluent API, but it tends to have a more SQL-like syntax. Here's how we performed a similar query to get all categories:

import { db } from './db' // this is the Drizzle instance we defined earlier
// Get all products with their categories
export const getAllCategories = async () => {
    const categories = await db.select().from(categories);
    return categories;
}

The syntax is quite similar, but Drizzle uses a more SQL-like select().from() method.

Let's see the autocompletion and typing validation:

For the more complex query fetching orders with customers and order items, we wrote:

export const getOrderDetail = async (orderId: number) => {
    const order = await db.select({
        firstName: customers.firstName,
        lastName: customers.lastName,
        products: products.name,
        price: products.price
    })
    .from(orders)
    .leftJoin(customers, eq(orders.customerId, customers.customerId))
    .leftJoin(orderItems, eq(orderItems.orderId, orders.orderId))
    .leftJoin(products, eq(orderItems.productId, products.productId))
    .where(eq(orders.orderId, orderId))
    .execute();
    return order;
}

The inferred type of the returned value is:

const order: {
    firstName: string | null;
    lastName: string | null;
    products: string | null;
    price: string | null;
}[]

You might notice that we don't have the products as an array of objects, but as a string. Drizzle lacks a built-in function like Kysely's jsonArrayFrom for handling nested arrays. While you can achieve this by manually grouping products, it requires more code.

Another difference lies in how you interact with entities. With Kysely, you reference them as strings, like in selectFrom('orders'). We found that importing only the db object and relying on autocompletion to explore available entities and attributes was convenient. In contrast, with Drizzle, you must explicitly import each entity and use it in your query:

import { categories } from './db/schema';

db.select().from(categories); // I cannot explore the entities from the db object

From a DX perspective, I prefer having the list of available entities as I type, rather than needing to import them explicitly. This is a matter of preference, but I find it more convenient to have everything readily available in autocompletion.

Migrations

As projects evolve, schema modifications are common. Migration tools help us track these changes alongside our code. Let's examine how each library handles migrations.

Kysely

Kysely offers a very simple approach, giving you control over your migrations. A migration file is a simple export of two functions, up and down, which are executed when applying or reverting the migration. We recommend using a timestamp in the file name to maintain migration order.

import { Kysely } from 'kysely';
export async function up(db: Kysely<any>): Promise<void> {
    await db.schema
        .createTable('new_table')
        .addColumn('id', 'integer', (col) => col.primaryKey().notNull())
        .addColumn('name', 'varchar', (col) => col.notNull())
        .execute();
}
export async function down(db: Kysely<any>): Promise<void> {
    await db.schema.dropTable('new_table').execute();
}

Kysely provides a CLI to create, apply, and revert migrations. We can create a new migration file:

npx kysely migrate:make add_new_table

This command creates a new migration file (e.g., add_new_table.ts) in the migrations directory, ready for us to add our migration code.

To apply migrations, we use:

npx kysely migrate:up

To revert the last migration:

npx kysely migrate:down

This approach is flexible, but it means you'll write your migrations manually. Kysely doesn't automatically generate migrations from schema changes.

Drizzle

Drizzle provides a more comprehensive migration toolset, allowing you to choose your preferred migration workflow:

  • Database first? Make changes directly in your database, then use drizzle-kit pull to update your schema. You'll handle rollbacks manually.
  • Codebase first approach?
    • If you don't need migration files, make changes in your code and push them to the database with drizzle-kit push.
    • If you want automatically generated migration files, modify your code, generate the migration file with drizzle-kit generate, and then apply them manually or with drizzle-kit migrate.

For example, if we add a stock column to the products table:

export const products = pgTable("products", {
    productId: serial("product_id").primaryKey().notNull(),
    name: varchar({ length: 100 }).notNull(),
    description: text(),
    price: numeric({ precision: 10, scale:  2 }).notNull(),
+   stock: integer("stock").notNull().default(0), // new column
});

We can generate a migration file using:

npx drizzle-kit generate

This creates a new migration file, such as 0002_uneven_rachel_grey.sql:

ALTER TABLE "products" ADD COLUMN "stock" integer DEFAULT 0 NOT NULL;

So, Kysely or Drizzle?

On the schema generation front, both tools are capable, but Drizzle holds a slight advantage by creating the initial migration file and offering a powerful drizzle-kit tool.

For querying, I lean towards Kysely due to its schema discoverability, autocompletion, and nested array support.

Regarding migrations, Drizzle provides more complete tooling. However, Kysely offers greater flexibility, allowing you to manage migrations as you see fit.

In the context of our project—an active project with an existing Knex.js database—switching to Kysely proved more straightforward than Drizzle. The migration mechanism is similar, as is the querying, and we primarily needed a powerful typed query builder. Migrating to Drizzle would have required significant rework of all existing migrations and queries, which would have been a massive undertaking.

Final Thoughts

Both Kysely and Drizzle are excellent TypeScript tools for database interaction. The choice between them should be pragmatic, not driven by hype.

While Drizzle touts itself as "headless" and dependency-free, Kysely is arguably even more so, offering a pure query builder experience without ORM abstractions. This makes Kysely ideal if you need robust typing and an enhanced query builder, especially when transitioning from Knex.js.

Kysely excels in query building and typing; Drizzle offers stronger ORM features and migration tooling.

Choose Kysely if:

  • You prioritize a powerful, type-safe query builder with excellent autocompletion.
  • You're already using Knex.js and want seamless, typed query support with minimal disruption.

Choose Drizzle if:

  • You're starting a new project and need a comprehensive ORM with full migration tooling.

Ultimately, select the tool that best fits your project's existing architecture, team's expertise, and specific technical needs, rather than chasing the latest trend.

Did you like this article? Share it!