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.
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.
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,
continues to be productive in django
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.
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 know some of our users use both, zapatos for codegen and kysely for querying.
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.
Nice to know it's implemented better though.
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.
https://github.com/squashql/squashql/blob/main/documentation...
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.
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.
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.
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 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
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.
Had lots of good experiences working with Knex.js over the years, and Kysely is the TS-native spiritual successor to Knex.
I’m excited. I enjoy Prisma but SQL can be so expressive. Looking forward to trying this.
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.
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-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.
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. :)
