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