Kysely: TypeScript SQL Query Builder | Hacker News Reader

Kysely: TypeScript SQL Query Builder

github.com

236 pointscal8570 comments

I love Kysely! I use it for a couple of production applications and it works quite well. Type support is better than Knex, and Kysely is really lightweight so I can use it in my projects without being concerned about performance issues.

Really, when you look at options like this, you start to break them down into 3 distinct categories:

1. Raw adapter usage - writing SQL. Performant, but can get tedious at scale, and weird to add types to.

2. Knex/Kysely, lightweight query builders. Readable, composable, and support types well, but a step removed from the performance of (1). Some would argue (1) is more universally understandable, too, although query builders make things easy for those more-familiar with programming languages than query languages.

3. Full-fledged ORMs like TypeORM, Sequelize, Prisma. Often require much more ecosystem buy-in and come with their own performance issues and problems.

I usually choose 1/2 depending on the project to keep things simple.

I have pretty much had no issue with it so far. The only thing that I would call out is that you _must_ run a migration initially to set things up, or your queries will hang. This has stumped me a few times (despite being obvious after-the-fact). It also interfaces really well with postgres, and has nice support for certain features (like jsonb).

I would prefer 1. if it had full type safety. I’m imagining some sort of a build process that looks for any .sql files in the project and spits out .sql.d.ts in the same directory with the input and output types according to the current schema of the database. Another nice thing about a setup like this imo would be that the .sql files would have the full support of the editor for completions and execution, unlike sql fragments in a Typescript file.

Slonik comes pretty close https://github.com/gajus/slonik. It does not inspect your DB schema, but has worked very well in our SQL-heavy architecture.

For Postgres there is https://github.com/adelsz/pgtyped, sounds pretty much like what you describe?

I wrote a thing that connects to your live database and then dumps out d.ts files.

F# can do this, either as part of the compilation via type providers [1] or, in a more lightweight manner, via Roslyn analyzers embedded in the editor [2].

Unsurprisingly, Haskell can also do this via Template Haskell [3], but I haven't used it.

[1] https://github.com/demetrixbio/FSharp.Data.Npgsql

[2] https://github.com/Zaid-Ajaj/Npgsql.FSharp.Analyzer

[3] https://hackage.haskell.org/package/postgresql-typed

On a similar note, there's also sqlx for rust [1] - and I find it extra impressive that they manage to support not only postgres, but sqlite and and mysql too.

[1] https://github.com/launchbadge/sqlx

You might also want to consider pgTyped (https://github.com/adelsz/pgtyped). It's supposed to make SQL and TS work together. I haven't gotten around to using it yet but I hear good things.

Can weigh in here, we use pgtyped heavily at work and it’s really good. There are some constraints with the type inference on complex queries, but it’s a decent trade off IMO.

Do you have a bit more information regarding said limitation? I'm guessing that dynamic queries would of course not be suitable but any other edge cases you have encountered?

I made a tool that generates Typescript types out of a live Postgres database. I've had a request for Kysely support ([link redacted]), but I more or less forgot about it. I would love to hear if that would be helpful.

I think https://github.com/RobinBlomberg/kysely-codegen does roughly the same thing?

Looks like it, yes. Do you use it?

We use in prod variant of no 1. [0]. Why? Because:

* it's sql

* it's extremely lightweight (built on pure, functional combinators)

* it allows us to use more complex patterns ie. convention where every json field ends with Json which is automatically parsed; which, unlike datatype alone, allows us to create composable query to fetch arbitrarily nested graphs and promoting single [$] key ie. to return list of emails as `string[]` not `{ email: string }[]` with `select email as [$] from Users` etc.

* has convenience combinators for things like constructing where clauses from monodb like queries

* all usual queries like CRUD, exists etc. and some more complex sql-wise but simple function-api-wise ie. insertIgnore list of objects, merge1n, upsert etc all have convenient function apis and allow for composing whatever more is needed for the project

We resort to runtime type assertions [1] which works well for this and all other i/o; runtime type assertions are necessary for cases when your running service is incorrectly attached to old or future remote schema/api (there are other protections against it but still happens).

[0] https://github.com/appliedblockchain/tsql

[1] https://github.com/appliedblockchain/assert-combinators

I mostly do work on the Python side of things and SQLAlchemy is the de facto ORM there. I hate it. It’s heavy. Opinionated. The happy path seems to be doing things in sessions where joins foreign keys and things are evaluated in the app instead of at the DB level (one can define relationships in models for the ORM to understand but not define explicit foreign key relationships at the DB table level, wtf? Thereby doing joins based on common columns…) and yet I can’t fault ORMs for their ability to get you going fast and help you iterate very quickly.

I recently had the chance to start a new side project and trying to go the pure SQL route which I love was so slow in terms of productivity. When I could just model the tables via SQLAlchemy I was able to get to the meat of the thing I was making much quicker. What I didn’t like was all the additional cognitive overhead but I gained DB agnostic ways of querying my data and could use say SQLite for testing and then say Postgres for staging or production by just changing a config whereas if I write pure SQL I might get into issues where the dialects are different such that that flexibility is not there.

In the end I am very conflicted. Oh, some context I began my professional life as a DBA and my first love was SQL. I like writing queries and optimizing them and knowing exactly what they’re doing etc.

Why must you run a migration initially? What does it do?

Dang, how do they implement the “parse text strings and generate types immediately available in auto-complete” thing?? I can see how you could do that with Rust Macros, but how do they do it in TS?

Eg. this thing:

blah.select(['pet.name as pet_name'])

is inferred to return a type with a `pet_name` field, parsing the contents of the SQL expression?

[Update]: whatafak lol TS has way more juice in it than I realized: https://github.com/koskimas/kysely/blob/master/src/parser/se...

The relevant TS feature is called template literal types:

https://www.typescriptlang.org/docs/handbook/2/template-lite...

You can use these template literal types + infer to build an entire SQL parser. I did a POC that infers SQL query types by parsing the SQL query on a type level:

https://github.com/nikeee/sequelts

Building this parser is pretty cumbersome and supporting multiple SQL dialects would be lots of pain. While I'm not a fan of query builders per se, Kysely pretty much covers everything that my POC tried to cover (except that 0 runtime overhead). However, you get the option to use different DBMs in tests than in production (pg in prod, sqlite in tests), which is a huge benefit for a lot of people. sequelts was designed to work with sqlite only. And it's a hack.

The typescript type system is insane, I solved quite a few project Euler tasks with it in the type system itself (From peano numbers up). I learned to never question the TS community

This is really cool, will look into using it in future projects!

I also made a tool (https://github.com/vramework/schemats) that generates the types directly from the db, which means whenever you do a DB migration your database types automatically update. Was forked from the original schemats library a couple years ago.

I also created a lightweight library ontop of pg that is less of a query builder and more of a typed CRUD + SQL for non trivial queries (https://github.com/vramework/postgres-typed). Most queries I deal with in a day to day is usually crud so I find it a little easier, but it's much less powerful then Kysely! I fall more into the camp of writing complex queries in SQL with small helpers and writing simple ones with util functions and typescript.

Edit: Will be looking into cleaning up docs and tests next month. Right now everything is in the ReadMe and examples

What a beautiful api. As a Knex user I appreciate the design influence of Knex, which similarly does not try to give you a full orm but just a structured builder for SQL queries. But I agree that Knex was always a little weird in that it only would execute the query if it was being listened for (so `query.getSql()` or something would get you the stringified query, while `await query` would actually execute it) and had some other quirks (in particular different ways of specifying the root table’s name at the position in the FROM depending on what you were doing).

The TypeScript integration is nice too, I also have treated TS this way as “programmable autocomplete for VS Code.” I will say that doesn't make it super maintainable usually but that's not an issue for the 0.x.x releases of course.

Reminds me of Prisma in its type safety, any major differences? I see that it's a query builder but when I used them in the past, they honestly didn't feel that different to ORMs.

In Rust, there is sqlx which lets you write SQL but checks at compile time whether the SQL is valid for the database, by connecting to the database, performing the transaction then rolling back, picking up and displaying any errors along the way.

Now with Prisma, I like it since it provides one unified database schema that I can commit into git (which avoids the problem of overlapping migrations from team members simultaneously working on separate branches that then need to be merged back in; with a git compatible schema, we must handle merge conflicts) and be able to transport across languages. I recently ported a TypeScript project to Rust and the data layer at least was very easy due to this. I used Prisma Client Rust for it, which is the beauty of having a language agnostic API, you can generate your own clients in whatever language you want.

How does this compare to say Prisma? I want to write SQL more than I want to write Javascript. I got really hung up on writing joins with Prisma and don't want to use a raw query. How would this compare assuming they're comparable, thanks in advance.

I think that is particularly what I like about this. With Prisma optimising your code is very hard because you cannot just customise joins like here. From the example, it seems that you can create joins and it also shows you in the documentation what the actual SQL for that will look like.

I still have Prisma running on my projects, so it will be a bit hard to move now particularly because it has TS native migrations, which is another issue. If I wanted to use these outside of TypeScript (let's say another service or middleware), then it would be very hard.

you can try https://github.com/ivank/potygen - type gen from sql itself supports all kinds of complex stuff like views, ctes json objects etc.

Prisma is good for writing simple selects, updates, and deletes. But the moment you need to write any kind of advanced query with joins, nested queries, unions, etc. it gets frustrating very quickly.

Kysely and Knex are far more flexible for writing complex queries and don't get in your way.

I see a kysely + kysely-codegen (generates types from DB schema) setup as comparable to Prisma in TS integration, with the added flexibility/closeness-to-SQL of the querybuilder.

If you:

- have used/liked Knex (or similar querybuilders) before

- like the TS integration + type safety of Prisma

- but find Prisma to be a bit too magic/heavy with its query engine and schema management

- and/or just want to be closer to SQL

then Kysely is what you're looking for.

Zapatos offers something similar here: https://jawj.github.io/zapatos/

As someone who uses both prisma and knex (so something like kysely):

Prisma is very very nice, it has maybe the best DX out there. My issue with it is performance. It is much slower than query builders or raw sql. It's also a huge black box, although I haven't had any issues, you're dealing with a complex beast.

Knex (and other query builders) is nicer than raw sql, has good performance, and it's fairly transparent (it's not a 800 pound gorilla like Prisma)

The main difference to me is that with Prisma I don't need to do any SQL to get a project up and running. I can define the tables and relations from my code or from Prisma Studio, and in one swoop I get the db structure done and the types defined.

I know my way around a database, but I'd rather not leave my code editor whenever I need to add a new column to a table.

With Kysely you have to create the DB schema, and then write the types; with every change you need, you gotta do both again.

(At least this seems true by default; as the project's readme mentions, there is a code generator[1] to generate the types from the DB schema; not quite the same but at least it's better than nothing.)

Commenters say that writing complex queries with Kysely is easier, which makes me wonder if I could use Prisma except for those, since Kysely should be able to just generate the SQL query for me to handle to Prisma...

[1]: https://github.com/RobinBlomberg/kysely-codegen

Safeql[1] can also be a good middleground. Adds type checking to prisma raw queries.

[1] https://safeql.dev/compatibility/prisma.html

Also check out https://jawj.github.io/zapatos/ which has a similar non-ORM yet fully typesafe approach.

Notably, in Zapatos you write SQL, whereas with this you use a query builder. Personally I think staying close to SQL is a feature, but YMMV.

Yes — though there are also shortcut functions for basic CRUD (and also for lateral joins) which give you automatic typing.

I started using zapatos in a project but eventually moved away from it. Their shortcuts and lateral join support is nice, but you very often need to write raw sql and manually provide types and ensure they are correct for all these cases.

It is also possible but awkward to use interpolations to construct very dynamic queries where based on filter conditions you need different joins or unions etc. I looked at some of the solutions that infer ts types from sql queries but eventually felt it was more maintenable to keep the dynamic query generation on the ts ide

I found ts-sql-query [1] to be much better suited for my use cases. It is very feature rich and has very good support for various dialect specific features in all mainstream databases. Also Juan (the author) is very helpful with queries and suggestions.

[1] https://ts-sql-query.readthedocs.io/en/stable/

The word kysely (/ˈkysely/) is Finnish for "query", btw :)

I was wondering what it meant. It's too bad English has a different pronunciation for "y", because the specified pronunciation Key-Seh-Lee means something totally different in Finnish (kiisseli, which is a type of dessert, a thick fruit soup that was a common tradition in my family).

Heh, indeed. English doesn't really have the /y/ phoneme at all, so a word like "kysely" is pretty tricky to pronounce for a native English speaker.

I think this is a really cool project, but for most use cases (not all, but most) I strongly believe query builders are an anti-pattern. I could go over the reasons, but when I saw this blog post, https://gajus.medium.com/stop-using-knex-js-and-earn-30-bf41..., from the author of slonik years ago I had a eureka moment, and it explains the reasoning better than I could.

Template strings in TypeScript allow you to safely write SQL directly, still with type checking: https://github.com/gajus/slonik#readme

I tend to agree. Query builders are a "cool" solution looking for a problem.

That said, dynamic query building example looks way better in knex than in slonik. Proposed approach has it's benefits, but the code is much harder to read. And it gets exponentially worse with more complicated queries.

The caveat, however, is that dynamic queries themselves are almost an anti-pattern, and should be avoided when possible.

Very cool!

Not sure if you use a diagram tool to visualize your databases but I built ERD Lab specially for developers and would love to get your feedback.

If you are on desktop/laptop you can login as guest. No registration required.

Here is a 1 minute video of ERDLab in action. https://www.youtube.com/watch?v=9VaBRPAtX08

What do you think about creating diagrams using the simple markup language in my tool?

I’m still hoping for a real ORM for JavaScript like Entity Framework. I find that devs who haven’t used EF don’t really understand the true power of an ORM.

It's funny that "kysely" is a Finnish word (the author is a finn) meaning questionnair, pronounced more like kew-seh-lew, but the pronounciation instructions make it sound like "kiisseli" which is like a fruit-soup-dessert thing (https://en.m.wikipedia.org/wiki/Kissel).

I remember it faintly from Finnishing the Finnish course on duolingo. I made it half-way through legendary, but I think I forgot most of it. Also it's unlikely to get more advanced content since it was a community contribution and Duolingo now switched to paid contributors only.

A more relevant meaning is probably a query as in SQL-kysely means an SQL query. Feel bad for Finnish students though trying to google something about SQL queries now in Finnish as most results seem to be about this project.

This is really cool, could come very handy for analysis work. For application development my go-to is Prisma (which also features a type safe query builder)

Objection is an incredible SQL ORM library from the same author, I learned a lot of concepts from it over the years.

Koskimas, awesome work! Will we ever see a lightweight ORM like Objection.js on top of Kysely?

No, he's stated somewhere in the issue tracker that he has no plans to ever do this.

edit: here - https://github.com/koskimas/kysely/issues/162#issuecomment-1...

koskimas is my favorite person in the world.

Objection is amazing and I’m happy to see he is continuing with this. Whenever I had issues he was quick to help and fix issues. Amazing person, and deserves all our support.

Next time I use node I will def check it out.

Love this library and use it with kysely-codegen and D1

Reminds me of pgtyped

How does HN receive SQL builders in general? I feel like most of us agree ORMs are typically a bad idea. I feel like that almost instantly leaves the need for "something" to take its place. In my experience, it's typically been a query builder like this.

I've also tried:

https://knexjs.org/

https://www.npmjs.com/package/sql-template-strings ("out of date" since like 2016? https://www.npmjs.com/package/sql-template-tag might be better)

Are query builders an anti pattern? People who are doing serious/logic heavy stuff with SQL, how do you avoid a query builder (if at all?)

I'm a big fan of them over traditional ORMs and use knex in quite a few projects at this point. I find them a really good balance between full on ORM and just writing out pure SQL queries.

Definitely going to give Kysely a try on my next project

There's nothing wrong with query builders, no antipattern.

In many ways it's similar to preferring C where you can really feel the assembly-language underneath you even if you're not writing it.

If you have written enough raw SQL you do a similar thing by convention... So for example if you look at my raw SQL queries you will notice that I usually only select specific columns with one-letter or two-letter table prefixes for disambiguation (because I hate “adding a column” being a breaking change! ... I am flexible on the name size but I like the freedom to make my table names long and expressive if feasible, group them with prefixes that relate related tables, etc). Then in the FROM, I only use JOIN and LEFT JOIN unless there's really no other way, and all my inner JOINs come before my left ones. All of those have AS statements renaming them to one-character prefixes too, and they have a clear ON condition that connects them to the above blob (so always "AS s WHERE s.whatever = ...") even though that makes the queries longer to refactor when you want to rearrange the joins (you often have to shift all the OFs down by one and reverse which side of the equality comes first or some nonsense). Subqueries should move up to a WITH or should be rewritten as LEFT JOINs if feasible.

You want to use structure to guide a reader through this thing that could be complicated... That structure could be lexical structure in the SQL itself or it could be syntactic structure from a wrapping language, I don't care so much. The real problem is having one source-of-truth for the database schema, and that problem is just barely tractable with current languages, but I don't see anybody who does it right.

> In many ways it's similar to preferring C where you can really feel the assembly-language underneath you even if you're not writing it.

When you write C, the ASM below it is much more complex. When use a query builder, the SQL beneath is about the same length and complexity. It's more like transpiling Swift to Java.

I think you'll see the whole range of responses, even some very reasonable defenses of ORMs for some cases.

I've come to prefer a yesql type approach that parses your sql into functions and provides a mechanism for applying functions to the bound data before running and the returned data after. Keeps things nicely separated and you can unit test your sql functions as application code.

I don't like query builders because SQL is actually easier to write and read even when you're not using advanced features, but I don't consider them a serious problem like ORMs.

> how does HN receive SQL builders in general? I feel like most of us agree ORMs are typically a bad idea.

I haven't observed that at all, my anecdotal impression is quite the opposite. Have you taken a poll?

Glad to see this posted. I totally agree - I think Kysely is a cool project but I do think query builders ARE generally an anti-pattern.

I'm a huge fan of slonik, https://github.com/gajus/slonik#readme, which uses template strings for SQL and has recently come a long way with its strong typing support.

This blog post by the author of slonik outlines why he thinks query builders are anti-pattern, https://gajus.medium.com/stop-using-knex-js-and-earn-30-bf41.... No, I'm not him, but I strongly agree with most of his opinions here.

Query builders can be amazing if they make it easy to logically compose highly dynamic queries, just like you would do with any other code. I haven't tried Kysely or Knex but I really enjoy working with Ecto in Elixir.

https://hexdocs.pm/ecto/dynamic-queries.html

Never understood why people use abstractions on SQL. SQL in itself is plain, simple, easy to use. No need for any high level abstractions. If you ever get next to a mysql console you can shove your abstractions up your wahzoo. If you know the basic level thing you can do whatever you want, in whatever environment you are in.

One key thing is that a variety of query builders and ORMs - I'd point to TypeORM for JS/TS, for example - allow in-IDE verification against a set schema and type inference of the results in whatever language you're working in.

Ah, too much oo and type magic. 90% of things do not need those, it just increases development time many times fold.

Query builders are a slight annoyance at worst, while ORMs can easily screw up the foundation of your system. You get extra compile-time checks with a query builder if you're using TS (a big piece of bloat on its own) instead of JS, which can be nice, but that's also what tests are for.

:DDDD

I think the main problem is getting compile time errors and autocomplete for your queries. Something like Kysely doesn't fully solve this but it avoids dumb errors like typos in sql commands or hanging commas. That being said, writing pure sql with Jetbrains IDEs is pretty amazing since you can get autocomplete straight from your production db schema. We use sqlx in Go + pure SQL and it works pretty well. Very simple things take slightly longer but writing complex queries is more straightforward than trying to get the builder to do the right thing.

Oh man :DDD. You must be joking, right? :D

Can you elaborate? Writing SQL and having the library only take care of converting from/to classes (or structs or whatever) in your code is common in various communities. What's so weird about it?

Reply on news.ycombinator.com