Type-safety with TypeScript ORMs and query builders

[thetutlage]

Alright, so I maintain a JavaScript ORM called Lucid. Lucid is built on top of knex, which isn't type-safe, and hence, Lucid isn't type-safe either.

My gut feeling is that utilizing SQL to its full potential and still being able to keep the TypeScript API end-to-end type-safe is a very, very, very hard task. Having hooks, an extensible query builder, and integrating ORM/query-builder (in a generic manner) to other parts of the codebase makes things even more difficult.

I am very happy with the current state of Lucid, except its not being type-safe. So, I decided to try Kysely (it's not an ORM) and Drizzle (it's an ORM) and see how it feels to use them.

Before we start, let me say a few words about type safety.

Type-safe systems are based on trust. If the compiler tells you that your code is correct, then it should be correct. Otherwise, it beats the whole purpose of type safety.

Therefore, a system is either type-safe or not. There is nothing called partial type safety. In fact, partial type safety will slow you down.

The Test

I was reading the Ecto docs and found a query that involves sub-queries and a few joins. So, I picked that example and reproduced it using Knex, Kysely, and Drizzle. Here's what the SQL query looks like.

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"

We query lendings, books and the users table to get a list of books and the name of the last lender for the book.

Here's what the SQL tables look like.

CREATE TABLE "public"."books" (
    "id" int8 NOT NULL,
    "name" text NOT NULL,
    PRIMARY KEY ("id")
);

CREATE TABLE "public"."users" (
    "id" int8 NOT NULL,
    "name" text NOT NULL,
    PRIMARY KEY ("id")
);

CREATE TABLE "public"."lendings" (
    "id" int8 NOT NULL,
    "book_id" int8 NOT NULL,
    "user_id" int8 NOT NULL,
    PRIMARY KEY ("id")
);

The queries

I have written the following queries using Knex, Kysely, and Drizzle.

With Knex

I find Knex syntax is terse and reads more like SQL. Not only the function names but also the flow in which you construct the query are similar to writing SQL.

However, you are free to have typos and incorrect table references, and no one will stop you except for the runtime exceptions.

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", "li.id", "l.last_lending_id")
  .innerJoin("users as u", "u.id", "l1.user_id")

With Kysely

Kysely, on the other hand, re-orders the writing flow of the query because it strives to keep the query type-safe.

For example, in the following query, you cannot write db.select before you write all the joins. This is because the columns from the joins are not visible to the select method if it is invoked first.

Now, there is nothing wrong with this approach. It seems logical from the TypeScript point of view.

However, when I first started using Kysely, I struggled a bit because I am used to writing SELECT statements first out of habit.

db
  .selectFrom("books")
  .leftJoin(
    (eb) => {
      return eb
        .selectFrom("lendings")
        .select((sl) => [
          sl.fn.max("id").as("last_lending_id"),
          "lendings.book_id",
        ])
        .groupBy("book_id")
        .as("l");
    },
    (join) => {
      return 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"])

With Drizzle

Drizzle is Knex trying to be Kysely. First, there is no type-safety in Drizzle when constructing the query.

They use references everywhere to avoid typos and have a single source of truth for all the tables and their columns. However, you can still use the wrong references, and the compiler won't yell at you. ( We talk about more about Drizzle pitfalls in a second )

Since they are using references everywhere, aliasing tables is painful. I have to create a JavaScript variable and use that in the rest of the query. Aliasing tables is a common practice, so I suspect every code path in my application will have a few variable declarations just for that.

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 pitfalls

Initially, I thought the drill of reproducing a SQL query with Drizzle, Kysely, and Knex would take only a few minutes. However, I found some interesting bits about Drizzle and thought I would present them properly in this article.

I don't know where I got this impression, but I always thought Drizzle is type-safe. It turns out its results are type-safe, and you can still write invalid queries with it.

A simple example

Let's take the simplest example and see if Drizzle can statically validate it for us.

The following query tries to select the name column from the users table. However, I forgot to write the join in the first place. I expect a type-safe query builder to stop me from writing and running this code.

db
  .select({
    name: schema.books.name,
    username: schema.users.name,
  })
  .from(schema.books)

On the other hand, Kysely does precisely what I expected.

/** Fails with error
 * Argument of type 'string[]' is not assignable to parameter of type 'SelectExpression<DB, "books">'
 */
db
  .selectFrom("books")
  .select(['users.name'])

Easy to mess up with subqueries

Alright, the above example might be too simple for any sane developer to mess up with. However, with the following query, I myself caught the error at runtime.

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

db
  .select()
  .from(schema.books)
  .leftJoin(l, eq(schema.lendings.book_id, schema.books.id))

Did you spot the error?
On the last line, i.e., leftJoin, since I am joining on a sub-query aliased as l, the ON clause should reference l.book_id and not lendings.book_id.

The following is the correct version.

-   .leftJoin(l, eq(schema.lendings.book_id, schema.books.id))
+   .leftJoin(l, eq(l.book_id, schema.books.id))

Once again, Kysely catches the error.

Conclusion

• Knex is a battle-tested old wizard that allows you to use the full potential of SQL. This might not be a huge deal for simple CRUD apps, but I appreciate knex when writing complex SQL queries.

• Kysely is pretty cool. They have pushed the limits as much as they can. I have nothing else to say.

• Drizzle gives the impression of type-safety. However, only the query results have type information. You can write invalid queries with Drizzle, just like Knex.\

  The references approach has its upsides and downsides. The upside is that you do not have static strings in your codebase. However, the references make the aliasing of tables (as per my limited testing) verbose.

My idea was not to bash Drizzle at all. I wanted to experience both Kysely and Drizzle. It turns out I had different expectations from Drizzle.

If you ask me, I will still pick Knex over Drizzle as of today because the maturity and simplicity of Knex outweigh the typed results of Drizzle for me. For you, it might be the other way around.

[andersoncardoso]

Nice article. I wrote quite a bunch with drizzle and kysely over the past 10 months or so, and today I would always pick Kysely over Drizzle.
On a large codebase it just flows well, it does not get in the way, and I was able to create a handfull of helpers and utilties on top of it quite easily. It's a great piece of software.
I think that a version of Lucid powered by Kysely would be a pure joy to use, and could have the potential to become the best ORM for Node.

[thetutlage]

I think that a version of Lucid powered by Kysely would be a pure joy to use, and could have the potential to become the best ORM for Node.

I would love that too, but building any sort of decent wrapper on top of Kysely is close to impossible. For example, I tried building a Auth guard using Kysely and was unsuccessful. I asked the Kysely creators for the same and they also agreed that generic wrappers over Kysely is not a good approach.

https://www.answeroverflow.com/m/1179612569774870548

[andersoncardoso]

I agree, that can get pretty hard, specially to the level of abstraction you would need for a full ORM like Lucid.
For the wrapper functions that I'm using, I sometimes just try to ensure the inputs and return get properly infered, but then internally I throw an any and use some type assertions with as to induce the TS compiler.
This gives me 80% of the benefit with less effort. I tried to make this properly manipulating Kysely internal types, and also tried to handle joins and aliases in a generic way, but it was taking too long and gave up.

For example, on the function below I get correct type hints and errors on all the inputs (where, select and orderBy) and also get correct inference for the result, but I kind off fool the ts compiler with assertions for the return:

        /**
	 * Simple select for the **all entries** matching the query object or an expression builder.
	 * Example:
	 *    const admins = await db.$fns.filterBy("user", { select: ["name"], where:  { userType: "admin" } } );
	 *    const admins = await db.$fns.filterBy("user", { where: (eb) => eb("userType", "=", "admin") })
	 */
	async filterBy<T extends keyof DB, S extends keyof Selectable<DB[T]>, O extends OrderByDirectionExpression>(
		table: T,
		options?: {
			where?: ParamsOrExpression<T>;
			select?: S[];
			orderBy?: OrderByExpression<DB, T, O> | ReadonlyArray<OrderByExpression<DB, T, O>>;
		}
	) {
		const where = options?.where ?? {};
		const withAll = isNil(options?.select);

		let query = this.#db
			.selectFrom(table)
			.$if(withAll, (q) => q.selectAll())
			.$if(!withAll, (q) => q.select(options!.select! as any)) // it's ok since we check for withAll
			.where(this.paramsOrExpression<T>(where));
		if (options?.orderBy) query = query.orderBy(options.orderBy as any);

		return (await query.execute()) as Expand<Pick<Selectable<DB[T]>, S>>[];
	}

	type ParamsOrExpression<T extends keyof DB> = Partial<Selectable<DB[T]>> | ExpressionOrFactory<DB, T, SqlBool>;

	private paramsOrExpression<T extends keyof DB>(where: ParamsOrExpression<T>): any {
		return isFunction(where) ? where : (eb: ExpressionBuilder<DB, T>) => eb.and(where as any);
	}

it's no ideal, but gets the job done for us. For more complex cases we just use Kysely directly.

[gigantedocil]

@thetutlage, thanks for the write up. Interesting comparison. One thing I would like to see that I don't think Knex supports and I feel that Kysely does better is producing good JSON.

For example, from my experience with Knex it always produces a flat result JSON. So if you have two tables e.g., blogs and posts and you want to retrieve a blog with posts, Knex will return duplicated data from the result essentially mapping the query result table after the join to the json object whereas Kysely will allow you to easily configure the right object hierarchy, for example with Kysely:

{
  "blogTitle": "some blog",
  "posts": [
    {
      "title": "post title"
    },
    {
      "title": "second post title"
    }
  ]
}

with Knex:

[
  {
    "blogTitle": "some blog",
    "postTitle": "post title"
  },
  {
    "blogTitle": "some blog",
    "postTitle": "second post title"
  }
]

With Knex it seems that afterwards you need to map it yourself to the JSON object structure that you need either manually or by using a naming convention with dots like Sequelize does under the hood with https://github.com/mickhansen/dottie.js.

[muniter]

There's another library that is also a great typesafe query builder:

• https://ts-sql-query.readthedocs.io/en/stable/queries/select/#gsc.tab=0
• https://www.npmjs.com/package/ts-sql-query

But it's usually not known off. It uses references like drizzle.

[vincerubinetti]

Your link is malformed fyi

[lroal]

Thanks

[AlexRMU]

https://www.prisma.io/
https://ts-sql-query.readthedocs.io/en/stable/#gsc.tab=0
?

[jeppester]

https://www.prisma.io/

Prisma does not let you put raw SQL in your selects and wheres.
If your query is not supported by the prisma API - for instance advanced full text search - then you are completely on your own:
prisma/prisma#5560

In my opinion that is not acceptable for a query builder.

[AlexRMU]

Prisma is still in development, it does not have many necessary functions.
But Kysely can be easily integrated into it, including types and connections.
https://github.com/eoin-obrien/prisma-extension-kysely

[tresorama]

Prisma introduced typed raw sql queries.

It's intended for query that are better written as raw SQL code (rather than prisma API) with prisma generating correct ts types at the end.

https://www.prisma.io/blog/announcing-typedsql-make-your-raw-sql-queries-type-safe-with-prisma-orm
https://www.youtube.com/watch?v=ZwYcCti6CEs

[Malix-Labs]

Might as well use Drizzle or Kysely

[aleclarson]

I'd recommend checking out pg-nano if you're a Postgres user. It's a tool I'm building that generates TypeScript definitions for your Postgres UDFs. So you write your queries in PL/pgSQL or another supported language (i.e. PL/rust) as CREATE FUNCTION statements, and pg-nano helps you call them from your TypeScript application server in a type-safe manner. Its query driver is powered by libpq, the official C library for Postgres.

[alexanderniebuhr]

@aleclarson I find this interesting, because all other ORMs seem to miss type-safety for stored procedures. Is pg-nano meant to be a replacement for other ORMs or is it meant to be used alongside them?

[aleclarson]

@alexanderniebuhr Any ORM or query builder that doesn't require its own migration tooling can be used in the same codebase as pg-nano. Unfortunately, its production-time migration “story” isn't yet complete, so pg-nano remains experimental for now.

[alexanderniebuhr]

Thanks for the insight. I'm still not 100% sure what things pg-nano can replace, but I’ll keep an eye on it.

[alexanderniebuhr]

With stored procedures, query performance is improved (thanks to “execution plan” caching, reduced data transfer, minimized round-trips, and efficient complex data processing closer to the data source). This is especially true for frequently executed complex queries and high-volume data operations.

Also can you explain proof or validate this statement I found 🤔

[lroal]

Have you considered orange-orm
It is typesafe with powerful filter mechanisms.

const rows = await db.books.getAll({
    where: x => x.lendings.exists(),
    lendings: true 
});  

[mparpaillon]

I'm looking for an ORM that can support branded ids. They all talk about type-safety but none of them is preventing me from using a "InvoiceId" instead of "OrderId" in my queries.

[mparpaillon]

Zod does it with z.brand()
Typesaurus does it too

[ben-pr-p]

kanel-kysely (https://kristiandupont.github.io/kanel/kanel-kysely.html) does this perfectly

[mparpaillon]

In the end I think Drizzle does it too now ?? https://orm.drizzle.team/docs/column-types/pg#customizing-data-type

[benknab]

you can achieve this in Drizzle with custom types

[stephenh]

@mparpaillon fwiw we extended the notion of "branded ids" to the runtime id values themselves, i.e. the author row id=1 in the database returns "a:1" from the author.id getter.

This is "different" and probably controversial, but several years after adopting this approach we're still loving it; it's just really helpful to have a:... or b:... go by in the logs, toStrings, url params, etc.

It also prevents "the UI read an author id over in file A, but then is using it as a book id in file B, and nothing in the test suite or database FK constraints catches it b/c 1 was both a valid author row and a valid book row". I've had that same bug happen ~regularly in every CRUD app I've worked on. 😅

https://joist-orm.io/advanced/tagged-ids/

(We also brand AuthorId and BookId internally within the codebase as well, but our runtime "branding" is unique enough that I thought it warranted a comment.)

[alexanderniebuhr]

I'm in the process of finding the best ORM for my use case, however reading through everything here, it doesn't look like there is one. It sounds like I want to use Kysely for the best Type-safety, however it isn't an ORM and misses a lot of things, e.g. relations, migrations, etc.
It also sounds like I don't want anything based on Knex, since Knex is not type-safe enough.
So the only ORMs not built on top of Knex seem to be Drizzle and Prisma, but those also have their issues with type-safety.

Is there any fully type-safe ORM or any ORM built on top of Kysely which supports multiple database engines, e.g. Postgres, SQlite, etc.?

[igalklebanov]

Hey 👋

MikroORM v7 is going to be using Kysely instead of Knex.

[alexanderniebuhr]

That’s amazing news. I’ll check it out. Only D1 support missing in MikroORM now.

[MitchRivet]

misses a lot of things, e.g. relations, migrations, etc.

Kysely supports migrations it seems: https://kysely.dev/docs/migrations