I’m well versed in SQL and priority #1 is performance. Can Kysely still make sense for me?
The TLDR is, I am impressed by Kysely and it seems like a great library… but I also don’t want to use it just because it looks wonderful 😉 I am brand new to the ORM / query builder world. We are migrating a project to SQLite and my decision is coming down to raw SQL (which I am very comfortable with and is my natural inclination) vs Kysely. Here are some questions I have:
1. Could / should I use Kysely and just use the sql template literal tags which is comfortable for me given my raw SQL experience?
2. Are there performance considerations? I know with full blown ORM the answer is obviously yes but wondering with Kysely if there are many performance trade offs in comparison to writing raw sql?
3. If I use just sql template tags does that defeat most the propose of Kysely or is that still a valid use case?
4. Outside of building queries (and the obvious TS type safety that comes with it), are there other benefits that come from Kysely?
I mean this as a compliment and not a bad thing that the Kysely library looks so nice and intriguing to use that I may not “need” it but am trying to justify a reason to 😉 BUT at the same time, it’s the classic JavaScript problem of need vs want and how much value do you get from a library.
Thanks in advance!!
Solution:Jump to solution
1. If you only use raw SQL, Kysely offers very little to no type-safety for you.
2. With other dialects, not really. With sqlite, Kysely wraps the better-sqlite synchronous API to an async API that can decrease performance. I haven't measured it, but there could be an impact.
3. Yes.
4. You can think of Kysely just as a type-safe way to write raw SQL. That's all it tries to do and that's pretty much the only benefit....
8 Replies
Solution
1. If you only use raw SQL, Kysely offers very little to no type-safety for you.
2. With other dialects, not really. With sqlite, Kysely wraps the better-sqlite synchronous API to an async API that can decrease performance. I haven't measured it, but there could be an impact.
3. Yes.
4. You can think of Kysely just as a type-safe way to write raw SQL. That's all it tries to do and that's pretty much the only benefit.
Hey 👋
Kysely is for SQL lovers. One of its core design principles is "What You See Is What You Get" - we're trying to provide the thinest abstraction possible over SQL, so reading/writing Kysely code can feel somewhat at home for SQL lovers. You trade raw SQL for sturcutred Javascript API with a lot of thought put into ergonomics and naming, and most importantly - type-safety.
Type-safety will make your codebase a joy to work with and have better maintainability.
This is weird. I thought maybe my post got lost or was inappropriate so got deleted 🤣🤦🏻♂️ but some reason I just had to scroll way down the list to find it 🙏
Thank you @koskimas and @igalk for your responses! Super helpful.
A few more questions im curious about:
1. How does migration work? Not really asking actually how it works cuz I can read the docs, but more about the value Kysely may bring from that perspective?
2. Does Kysely do much with regards to my actually SQLite database? Meaning adding extra tables, columns, changes pragma options etc? One goal is if we use an ORM / query builder that we keep as much control as possible over the DB so that if we moved away from Kysely for example, we wouldn’t have to like refresh our database / clear it out for lack of better word? Hopefully that question makes sense.
3. I presume when using Kysely we eliminate sql injection risk? That your abstractions take care of that for us naturally? What about if we use sql template literal tags with raw sql? Will that still handle sql injection risks for us?
4. @koskimas RE: better SQLite and sync vs async. Am I correct though that under the hood you are still interacting with better-SQLite3 synchronously (the way it recommends)? And that the asynchronous behavior is really just you wrapping promises around your abstractions? If that is the case it feels like that really should NOT have performance implications (from SQLite perspective)? Am I understanding correctly? Would you agree? Or are my assumptions mistaken?
Thanks so much!!
We use an extension called answer overflow that basically makes posts impossible to find once they are answered. I don't know if that's always been the case or if it just started happening. I've been wondering a long time why people don't interact with answered posts anymore. The reason is probably that they don't find them.
I think we should stop using answer overflow if there's no way to fix this.
Or alternatively only mark posts answered weeks after the answer
If the OP doesn't mark it
One potential is that it seems like it moves the topic back to the top for any messages sent after being marked as solved. So you could mark it as solved and then get in the habit of just responding right after that saying something like “If you have any other questions, let us know”. That seems like it would bump it back to the top as expected 🤔 kind of annoying for you but I think that could work.
4. RE: better SQLite and sync vs async. Am I correct though that under the hood you are still interacting with better-SQLite3 synchronously (the way it recommends)? And that the asynchronous behavior is really just you wrapping promises around your abstractions? If that is the case it feels like that really should NOT have performance implications (from SQLite perspective)? Am I understanding correctly? Would you agree? Or are my assumptions mistaken?@koskimas no pressure and just whenever you have a chance to respond, but would love to here your thoughts on my follow-up questions... #4 specifically (also quotes here to be clear) to see if I understood your original response about better-sqlite3 and async vs sync? Thanks!! @igalk @Igal @koskimas sorry for the re-ping but wondering if you could kindly provide your thoughts on my follow-up questions here in the replied to message? Thanks much!
1. Kysely provides a
schema module you can use to build DDL queries with. It provides primitives you can use to automate migration runs and keep state. kysely-ctl provides CLI tooling using those primitives.
2. Kysely adds tables when you use its migrations primitives to keep state. Nothing else.
3. When using raw SQL, there are no guarantees. You should always validate user input when passed in the query string itself.
4. If there is a performance hit that is not acceptable for you, you can use Kysely to build queries and than execute directly with better-sqlite3.@Igal Ahhhh, very cool. So I assume you are referring to this? https://kysely.dev/docs/recipes/splitting-query-building-and-execution#compile-a-query
And so we could use Kysely to help manage migrations (your response in #1 and #2) and then compile queries to create type safety, but then have full control over our better-SQLite database query execution by running the Kysely compiled query strings as if it we were just writing our own raw sql without Kysely?!
If I understand this correctly then this is amazing and basically exactly what I wanted!
Thanks so much for your help. You have gained a new user 🙂
Yes.
Yes.