Kysely: TypeScript SQL Query Builder

zerace

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).

jakewins

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...

yasserf

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

crdrost

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.

satvikpendem

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.

bastardoperator

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.

skrebbel

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

Sharlin

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

hn_throwaway_99

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

jerryu

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?

bottlepalm

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.

FireInsight

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).

iddan

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)

haywirez

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

HatchedLake721

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

moomoo11

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.

jokull

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

throwayyy479087

Reminds me of pgtyped

MuffinFlavored

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?)

lofaszvanitt

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.

T3RMINATED

[dead]