Show HN: Kysely, a type-safe SQL query builder for TypeScript
kysely.devWe've been using this in production for several months, and I'm a huge fan. Unopinionated but incredibly helpful. Big ups to the authors!
<3 from Kysely.
Have recently started using it, big fan, especially in concert with kysely-codegen (generate Kysely types from DB schema). The combo is similar to Prisma, but IMO better in practice - Prisma is too limited, too little support for any sort of custom/non-standard types. For example, using PostGIS with Prisma is a terrible experience, but fine with Kysely.
Also, a query builder with generated types that match the DB schema has many of the advantages of an ORM, with IMO few of the disadvantages. I’m not spending so much time trying to figure out how to translate what I want to do in SQL into the ORM language, as it’s basically a thin, more type safe and composable layer over SQL.
<3 from Kysely. kysely-codegen is really dope!
If you want to run SQL, write SQL.
The majority of your SQL will not require building dynamic where clauses or (dog forbid) dynamic joins.
Having your SQL as plain statements with simple placeholders (to create safe prepared statements) is the saner approach.
Not only can you pluck them into your favorite SQL tools and analyzers, but you will not be surprised by terribly performing queries, because you created them dynamically without understanding their complexity.
We've learned the hard lessons decades ago by misusing ORMs. While using alternative SQL syntax builders is avoiding many of those pitfalls, you will still inherit complexity by translating a SQL dialect to the builder pattern.
It is not worth it IMHO.
Kysely is not an ORM, its a query builder with strong emphasis on type-safety and 1:1* mapping ("What You See Is What You Get") to SQL.
Projects writing raw SQL eventually end up implementing their own query building functionality. Which adds another thing to maintain and understand - that's also lacking in features and is not as type-safe.
Just use a well adopted query builder.
What sort of platonic ideal project do you work on that the majority of SQL you write doesn't need dynamic WHERE clauses? You're positing a majority use case I haven't encountered in 20 years of professional and personal projects.
In NodeJS land this library is pretty close to this: https://github.com/porsager/postgres
postgres.js is dope! I'm maintaining a Kysely community dialect for it. https://github.com/igalklebanov/kysely-postgres-js
Because writing SQL is a drudge, you will write some helpers here and there. Before you know it, you have your own buggy non type safe SQL builder. This is vastly superior.
For me, Rusts SQLx has been a good option for this. Write plain SQL with a macro and have it verified against a dev database at build time, optionally to a typed anonymous struct to match your query results. The performance isn't incredible though.
I've been curious about sqlx for a while. About the non-incredible performance: Do you mean the performance of the verification in development, or the performance of sqlx execution in a live setting?
I can't think of a single project I've worked on that did not need dynamic where clauses almost immediately.
Any patterns you'd recommend? (pseudo code)
1) db.execute("select name, tel from person where id={?}",personId);
2) const sqlText="select name, tel from person where id={?}";
db.execute(sqlText, personId);
3) as 2), but put sqlText in another file,
Maybe not the majority, but the most critical, like filter-based searches, will require some dynamic SQL. Type safety is a big win.
no
continues to be productive in django
How does it compare to zapatos?
We moved away from zapatos because the generated types are good only when selecting from single table. The moment we start selecting some subset of columns from a join of multiple tables, it is upto the developer to provide the right combination of pick and intersection of generated types and type safety takes a hit.
The solution we use right now is ts-sql-query [1] which supports automatic type-safety for complex joins, CTEs, subselects etc. I evaluated Kysely as well but found the sql feature set coverage of ts-sql-query better at the time.
I maintain a code-generator [2] for this project that can generate the table mappers from database schema similar to how zapatos.
We don't have as good support for lateral joins and deriving json from database though, which zapatos does really well.
> which supports automatic type-safety for complex joins, CTEs, subselects etc. I evaluated Kysely as well but found the sql feature set coverage of ts-sql-query better at the time.
Kysely also provides "automatic type-safety for complex joins, CTEs, subselects etc.".
Gotta love how toxic some open-source maintainers are, bashing other libraries while self-promoting.
How does this compare with pgTyped[1]?
I like pgtyped - when the queries are mostly static it is a great solution.
Solutions like ts-sql-query are better when you need to dynamically generate complex sql. With ts-sql-query it is very easy to create sql select statements where multiple individual where clauses, or even joins are conditional based on the incoming filters.
You can choose to use stored procedures etc. for the more complex cases while keeping pgtyped for 80% of the less dynamic use cases. We decided not to go that route to keep most of the application in typescript which we are more comfortable with.
I'd suggest you try them both, and pick what you like better or what feels safer to bet on for your project.
I know some of our users use both, zapatos for codegen and kysely for querying.
Have used it for a smaller project for a bit now and I really like it as well. Concise code and I can still map it to actual SQL in my brain.
What has been a bit non-intuitive for me though is the expression builder since the latest major version of kysely. When writing queries with `OR` conditions it always takes me a while to wrap my head around it again. It is also challening to make this easily readable with lots of dynamic `OR` conditions and I usually end up with a wrapper function which returns the array for the statements passed into the `or(` block. Could be improved in my opinion, otherwise a great tool
<3 from Kysely.
Would love to have a discussion about your use case in our discord. We're constantly thinking about improving that part of our API as it's at the heart of many things.
Sure, I'll reach out
I am building squashql-js for a slightly different use case (database agnostic SQL-like Typescript query builder among others) but Kysely and pypika (for Python) have been a great source of inspiration.
https://github.com/squashql/squashql/blob/main/documentation...
Looks cool!
Kysely was inspired by Knex, but took a more predictable route instead of providing generic APIs that produce different SQL for different dialects. We believe that dialect "lock-in" should never be a real concern because migrating from let's say, MySQL to PostgreSQL is extremely rare in the real world.
> MySQL to PostgreSQL is extremely rare in the real world.
That's true, I agree.
But we are mainly targeting software editors that build data analytics platform and let their customers choose where they want to store their data and run their analysis (on premise, in the cloud). Having a common API avoid adapting developed applications for each customer.
This is only partially type-safe. Having a few SQL keywords abstracted into methods doesn't deliver a lot of value. I've found some value in type-safe mappings between database fields and language fields, at least you can directly see the problems in your code if some entity class has been changed. String-based field references will only fail at runtime so you need 100% unit tests to refactor with confidence.
If you use it with kysely-codegen, it generates the types from your DB schema, guaranteeing they match. And the strings are verified at compile time - you can’t typo a table name, field name, etc., it won’t compile. Plus, from an ergonomics POV, it integrates very well with auto-complete.
It’s very typesafe IMO, more so than most libs that interact with the the DB, where you hand-define the schemas and can more easily make typos.
The only nag I have with this (and it might just be a case of me doing things wrong) is that it seems you also have to create types for tables based on the operation you want to perform by wrapping them in the `Selectable`, `Insertable`, etc. Kysely types. Kinda wish kysely-codegen created all those types along with the base table types. Otherwise, it’s been working pretty well for me so far!
It was also requested in prisma-kysely recently.
Might be a good idea to check kysely-codegen's issues section and open an issue if it wasn't requested yet.
I didn't go deep into the docs, but the 'movie' on the homepage clearly shows string-based field name mappings so I instantly saw that as a red flag...
Nice to know it's implemented better though.
TypeScript allows strongly typed string constants. It's actually not a red flag, it's just idiomatic TypeScript.
These are literal string types. Its a feature of at least two languages that I know of:
Typescript: https://www.typescriptlang.org/docs/handbook/literal-types.h...
Scala: https://docs.scala-lang.org/sips/42.type.html
Typescript goes a step further has has things like template literal types: https://www.typescriptlang.org/docs/handbook/2/template-lite...
They are a little bit more than string mappings, and the mapping you do see is all codegen generated from the DB schema.
Yeah, TS has a very flexible/powerful type system. You can have a type like ‘full_name’, that can only be that exact string, or a type like ‘full_name’ | ‘id’ | ‘email’, that can only be those 3 strings, etc. Kysely takes advantage of those sorts of types.
It’s a string but TS is crazily powerful. The generated types would verify it’s the correct string says like table_name and the columns name also matches with table_name.
With generated database types (we use zapatos) this is the most type safe query builder, the typing is extensive and very correct
For Java, JOOQ is great and offers better type safety than this. Additionally, JOOQ generates data model classes from the DB schema.
JOOQ is cool!
What do you mean by "better type safety"?
Kysely's ecosystem has 2 codegen libraries:
kysely-codegen introspects the database directly.
prisma-kysely generates types based on Prisma schemas.
There’s a popular sister project, kysely-codegen, that lets you do the same (generate types from the DB schema).
See now this is what I thought that "Selectric Typeballs" post [1] was going to be. I think it would make for a better name than "Kysely" if I'm being honest.
Kysely is Finnish for query. Given that the author is Finnish it makes sense
Love Kysely, been rooting for it ever since it was first released. Paired with kysely-codegen, it's my favorite TS <> SQL interface. Does just enough, but not too much.
Had lots of good experiences working with Knex.js over the years, and Kysely is the TS-native spiritual successor to Knex.
<3 from Kysely. kysely-codegen is dope!
Has anyone used this and compared to the types provided by Knex? I’m largely very happy with Knex but when you start dealing with subqueries and the like the types fall apart. Suspect this kind of thing is pushing TS types to their limit, though.
You should try it. I switched from Knex to Kysely since I was early in a project and Knex lost its lead developer (IIRC) and the typing Kysely gives you is extremely effortless and works very well.
We love Knex, and are inspired by it. Sadly its not type-safe and reaching type-safety would require a rewrite.
We care a lot about our TypeScript compilation performance and Developer eXperience in general.
v0.25 introduced internal changes that doubled the possible complexity of CTEs, joins and conditional selects.
We also provide helper methods that "reset" the stack in a type-safe way.
I typed up a comment saying how much I’d love to drop this in alongside my Prisma code so I can keep using the ORM for simple things and have the option to drop into SQL as needed. It ended asking if anyone knew whether such a thing was possible. I decided to check the docs one more time and… here it is! https://github.com/valtyr/prisma-kysely
I’m excited. I enjoy Prisma but SQL can be so expressive. Looking forward to trying this.
<3 from Kysely. prisma-kysely is dope!
Doesn't seem like it performs result set nesting on joins? For example with the one to many of owner -> pet, I'd like the results to look something like `{ person: Person, pets: Pet[] }[]`. Knex doesn't do this either (afaict) - wrote a few "deep" queries with some convoluted lodash to group things up but mostly gave up and just live with raw resultsets.
I guess I still prefer that to a full on ORM, but that's really the one missing feature I want from these SQL query builder libraries
Have you read this recipe? https://kysely.dev/docs/recipes/relations
Oh sure, knex had a github issue with a similar recommendation. Interesting approach, and I suppose json -> ts is a smooth transformation, thanks for the tip
Another "almost" like SQL library that I've heard floating around:
https://github.com/drizzle-team/drizzle-orm
I think I've reached the limits of Prisma and embarrassingly I'm thinking about ripping it out. The benefits of not having to constantly reference your DB schema and having IDE guidance is not matching up to the idiosyncrasy and incomplete DB support of Prisma.
Been using Drizzle for the past couple of weeks and liking it a lot. Their relation feature is awesome and solves the n+1 problem very nicely.
Kysely looks interesting, although I really like being able to write the query directly so I'm able to test the raw query directly. PgTyped is an interesting library I've used in the past where queries can be written as regular template strings that gets checked and responses become types. https://pgtyped.dev/docs/ts-file
Fun fact: "Kysely" is "query" in Finnish.
Fun fact 2: the way it is instructed to pronounce (Key-seh-lee) it would be "kiisseli" in Finnish which means pudding.
Hey :wave:
Igal from Kysely here (I did not create it, Sami did).
Our site is a constant WIP. We've recently revamped "Getting Started" and added a lot of examples. If you can't find something there, check the API docs site or JS docs in your IDE - everything is documented.
We respond quite fast on discord if you've got any questions.
Feel free to ask me questions here too. :)
Currently using knex, but this looks like a nice improvement. Getting the types via codegen is brilliant.
Huge shoutout to our community, bloggers and youtubers!
for the db schema definition for this tool, where does the source of truth lives?
I'm trying to think what happens when a column gets deleted or added in the prod, ci, or dev db tier. Ideally those db schema changes should happen at the same time but real life doesn't work like that.
Kysely has community projects that offer Database interface auto-generation.
kysely-codegen can introspect all core dialects. prisma-kysely can generate straight from Prisma schemas.
We recommend using these in production apps. You could verify everything is aligned in your CICD workflows.