8 ms·

Kysely: TypeScript SQL Query Builder

236 points by cal85 4y ago

MuffinFlavored 4y ago
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://knexjs.org/ https://www.npmjs.com/package/sql-template-strings https://www.npmjs.com/package/sql-template-strings ("out of date" since like 2016? https://www.npmjs.com/package/sql-template-tag 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?)
- nullwarp 4y ago
  I'm a big fan of them over traditional ORMs and use knex in quite a few projects at this point. I find them a really good balance between full on ORM and just writing out pure SQL queries. Definitely going to give Kysely a try on my next project
- deleted 4y ago
  [deleted]
- crdrost 4y ago
  There's nothing wrong with query builders, no antipattern. In many ways it's similar to preferring C where you can really feel the assembly-language underneath you even if you're not writing it. If you have written enough raw SQL you do a similar thing by convention... So for example if you look at my raw SQL queries you will notice that I usually only select specific columns with one-letter or two-letter table prefixes for disambiguation (because I hate “adding a column” being a breaking change! ... I am flexible on the name size but I like the freedom to make my table names long and expressive if feasible, group them with prefixes that relate related tables, etc). Then in the FROM, I only use JOIN and LEFT JOIN unless there's really no other way, and all my inner JOINs come before my left ones. All of those have AS statements renaming them to one-character prefixes too, and they have a clear ON condition that connects them to the above blob (so always "AS s WHERE s.whatever = ...") even though that makes the queries longer to refactor when you want to rearrange the joins (you often have to shift all the OFs down by one and reverse which side of the equality comes first or some nonsense). Subqueries should move up to a WITH or should be rewritten as LEFT JOINs if feasible. You want to use structure to guide a reader through this thing that could be complicated... That structure could be lexical structure in the SQL itself or it could be syntactic structure from a wrapping language, I don't care so much. The real problem is having one source-of-truth for the database schema, and that problem is just barely tractable with current languages, but I don't see anybody who does it right.
  - hot_gril 4y ago
    > In many ways it's similar to preferring C where you can really feel the assembly-language underneath you even if you're not writing it. When you write C, the ASM below it is much more complex. When use a query builder, the SQL beneath is about the same length and complexity. It's more like transpiling Swift to Java.
- giraffe_lady 4y ago
  I think you'll see the whole range of responses, even some very reasonable defenses of ORMs for some cases. I've come to prefer a yesql type approach that parses your sql into functions and provides a mechanism for applying functions to the bound data before running and the returned data after. Keeps things nicely separated and you can unit test your sql functions as application code.
- hot_gril 4y ago
  I don't like query builders because SQL is actually easier to write and read even when you're not using advanced features, but I don't consider them a serious problem like ORMs.
- zzzeek 4y ago
  > how does HN receive SQL builders in general? I feel like most of us agree ORMs are typically a bad idea. I haven't observed that at all, my anecdotal impression is quite the opposite. Have you taken a poll?
- hn_throwaway_99 4y ago
  Glad to see this posted. I totally agree - I think Kysely is a cool project but I do think query builders ARE generally an anti-pattern. I'm a huge fan of slonik, https://github.com/gajus/slonik#readme https://github.com/gajus/slonik#readme, which uses template strings for SQL and has recently come a long way with its strong typing support. This blog post by the author of slonik outlines why he thinks query builders are anti-pattern, https://gajus.medium.com/stop-using-knex-js-and-earn-30-bf410349856c https://gajus.medium.com/stop-using-knex-js-and-earn-30-bf41.... No, I'm not him, but I strongly agree with most of his opinions here.
- dns_snek 4y ago
  Query builders can be amazing if they make it easy to logically compose highly dynamic queries, just like you would do with any other code. I haven't tried Kysely or Knex but I really enjoy working with Ecto in Elixir. https://hexdocs.pm/ecto/dynamic-queries.html https://hexdocs.pm/ecto/dynamic-queries.html
zerace 4y ago
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).
- guntars 4y ago
  I would prefer 1. if it had full type safety. I’m imagining some sort of a build process that looks for any .sql files in the project and spits out .sql.d.ts in the same directory with the input and output types according to the current schema of the database. Another nice thing about a setup like this imo would be that the .sql files would have the full support of the editor for completions and execution, unlike sql fragments in a Typescript file.
  - cheeseface 4y ago
    Slonik comes pretty close https://github.com/gajus/slonik https://github.com/gajus/slonik. It does not inspect your DB schema, but has worked very well in our SQL-heavy architecture.
  - csnweb 4y ago
    For Postgres there is https://github.com/adelsz/pgtyped https://github.com/adelsz/pgtyped, sounds pretty much like what you describe?
crdrost 4y ago
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 4y ago
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.
jakewins 4y ago
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/select-parser.ts#L77 https://github.com/koskimas/kysely/blob/master/src/parser/se...
- kamilafsar 4y ago
  The relevant TS feature is called template literal types: https://www.typescriptlang.org/docs/handbook/2/template-literal-types.html https://www.typescriptlang.org/docs/handbook/2/template-lite...
- nikeee 4y ago
  You can use these template literal types + infer to build an entire SQL parser. I did a POC that infers SQL query types by parsing the SQL query on a type level: https://github.com/nikeee/sequelts https://github.com/nikeee/sequelts Building this parser is pretty cumbersome and supporting multiple SQL dialects would be lots of pain. While I'm not a fan of query builders per se, Kysely pretty much covers everything that my POC tried to cover (except that 0 runtime overhead). However, you get the option to use different DBMs in tests than in production (pg in prod, sqlite in tests), which is a huge benefit for a lot of people. sequelts was designed to work with sqlite only. And it's a hack.
- eddsh1994 4y ago
  The typescript type system is insane, I solved quite a few project Euler tasks with it in the type system itself (From peano numbers up). I learned to never question the TS community
skrebbel 4y ago
Also check out https://jawj.github.io/zapatos/ https://jawj.github.io/zapatos/ which has a similar non-ORM yet fully typesafe approach.
- CuriouslyC 4y ago
  Notably, in Zapatos you write SQL, whereas with this you use a query builder. Personally I think staying close to SQL is a feature, but YMMV.
  - gmac 4y ago
    Yes — though there are also shortcut functions for basic CRUD (and also for lateral joins) which give you automatic typing.
- lf-non 4y ago
  I started using zapatos in a project but eventually moved away from it. Their shortcuts and lateral join support is nice, but you very often need to write raw sql and manually provide types and ensure they are correct for all these cases. It is also possible but awkward to use interpolations to construct very dynamic queries where based on filter conditions you need different joins or unions etc. I looked at some of the solutions that infer ts types from sql queries but eventually felt it was more maintenable to keep the dynamic query generation on the ts ide I found ts-sql-query [1] to be much better suited for my use cases. It is very feature rich and has very good support for various dialect specific features in all mainstream databases. Also Juan (the author) is very helpful with queries and suggestions. [1] https://ts-sql-query.readthedocs.io/en/stable/ https://ts-sql-query.readthedocs.io/en/stable/
bastardoperator 4y ago
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.
- seer 4y ago
  you can try https://github.com/ivank/potygen https://github.com/ivank/potygen - type gen from sql itself supports all kinds of complex stuff like views, ctes json objects etc.
- chetanbhasin 4y ago
  I think that is particularly what I like about this. With Prisma optimising your code is very hard because you cannot just customise joins like here. From the example, it seems that you can create joins and it also shows you in the documentation what the actual SQL for that will look like. I still have Prisma running on my projects, so it will be a bit hard to move now particularly because it has TS native migrations, which is another issue. If I wanted to use these outside of TypeScript (let's say another service or middleware), then it would be very hard.
- dglass 4y ago
  Prisma is good for writing simple selects, updates, and deletes. But the moment you need to write any kind of advanced query with joins, nested queries, unions, etc. it gets frustrating very quickly. Kysely and Knex are far more flexible for writing complex queries and don't get in your way.
- LewisJEllis 4y ago
  I see a kysely + kysely-codegen (generates types from DB schema) setup as comparable to Prisma in TS integration, with the added flexibility/closeness-to-SQL of the querybuilder. If you: - have used/liked Knex (or similar querybuilders) before - like the TS integration + type safety of Prisma - but find Prisma to be a bit too magic/heavy with its query engine and schema management - and/or just want to be closer to SQL then Kysely is what you're looking for.
HatchedLake721 4y ago
Koskimas, awesome work! Will we ever see a lightweight ORM like Objection.js on top of Kysely?
- LewisJEllis 4y ago
  No, he's stated somewhere in the issue tracker that he has no plans to ever do this. edit: here - https://github.com/koskimas/kysely/issues/162#issuecomment-1249356092 https://github.com/koskimas/kysely/issues/162#issuecomment-1...
yasserf 4y ago
This is really cool, will look into using it in future projects! I also made a tool (https://github.com/vramework/schemats 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 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
deleted 4y ago
[deleted]
deleted 4y ago
[deleted]
jerryu 4y ago
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 https://www.youtube.com/watch?v=9VaBRPAtX08 What do you think about creating diagrams using the simple markup language in my tool?
haywirez 4y ago
Objection is an incredible SQL ORM library from the same author, I learned a lot of concepts from it over the years.
bottlepalm 4y ago
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.
Sharlin 4y ago
The word kysely (/ˈkysely/) is Finnish for "query", btw :)
- frosted-flakes 4y ago
  I was wondering what it meant. It's too bad English has a different pronunciation for "y", because the specified pronunciation Key-Seh-Lee means something totally different in Finnish (kiisseli, which is a type of dessert, a thick fruit soup that was a common tradition in my family).
  - Sharlin 4y ago
    Heh, indeed. English doesn't really have the /y/ phoneme at all, so a word like "kysely" is pretty tricky to pronounce for a native English speaker.
lofaszvanitt 4y ago
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.
- crooked-v 4y ago
  One key thing is that a variety of query builders and ORMs - I'd point to TypeORM for JS/TS, for example - allow in-IDE verification against a set schema and type inference of the results in whatever language you're working in.
  - lofaszvanitt 4y ago
    Ah, too much oo and type magic. 90% of things do not need those, it just increases development time many times fold.
    - hot_gril 4y ago
      Query builders are a slight annoyance at worst, while ORMs can easily screw up the foundation of your system. You get extra compile-time checks with a query builder if you're using TS (a big piece of bloat on its own) instead of JS, which can be nice, but that's also what tests are for.
      - lofaszvanitt 4y ago
        :DDDD
- deleted 4y ago
  [deleted]
- valzam 4y ago
  I think the main problem is getting compile time errors and autocomplete for your queries. Something like Kysely doesn't fully solve this but it avoids dumb errors like typos in sql commands or hanging commas. That being said, writing pure sql with Jetbrains IDEs is pretty amazing since you can get autocomplete straight from your production db schema. We use sqlx in Go + pure SQL and it works pretty well. Very simple things take slightly longer but writing complex queries is more straightforward than trying to get the builder to do the right thing.
hn_throwaway_99 4y ago
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-bf410349856c 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 https://github.com/gajus/slonik#readme
- hbrn 4y ago
  I tend to agree. Query builders are a "cool" solution looking for a problem. That said, dynamic query building example looks way better in knex than in slonik. Proposed approach has it's benefits, but the code is much harder to read. And it gets exponentially worse with more complicated queries. The caveat, however, is that dynamic queries themselves are almost an anti-pattern, and should be avoided when possible.
iddan 4y ago
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)
FireInsight 4y ago
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 https://en.m.wikipedia.org/wiki/Kissel).
- bayesian_horse 4y ago
  I remember it faintly from Finnishing the Finnish course on duolingo. I made it half-way through legendary, but I think I forgot most of it. Also it's unlikely to get more advanced content since it was a community contribution and Duolingo now switched to paid contributors only.
- pandem 4y ago
  A more relevant meaning is probably a query as in SQL-kysely means an SQL query. Feel bad for Finnish students though trying to google something about SQL queries now in Finnish as most results seem to be about this project.
moomoo11 4y ago
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.
throwayyy479087 4y ago
Reminds me of pgtyped
T3RMINATED 4y ago
[dead]
jokull 4y ago
Love this library and use it with kysely-codegen and D1

236 points by cal85 4y ago

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://knexjs.org/ https://www.npmjs.com/package/sql-template-strings https://www.npmjs.com/package/sql-template-strings ("out of date" since like 2016? https://www.npmjs.com/package/sql-template-tag 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?)

nullwarp 4y ago
I'm a big fan of them over traditional ORMs and use knex in quite a few projects at this point. I find them a really good balance between full on ORM and just writing out pure SQL queries. Definitely going to give Kysely a try on my next project
deleted 4y ago
[deleted]
crdrost 4y ago
There's nothing wrong with query builders, no antipattern. In many ways it's similar to preferring C where you can really feel the assembly-language underneath you even if you're not writing it. If you have written enough raw SQL you do a similar thing by convention... So for example if you look at my raw SQL queries you will notice that I usually only select specific columns with one-letter or two-letter table prefixes for disambiguation (because I hate “adding a column” being a breaking change! ... I am flexible on the name size but I like the freedom to make my table names long and expressive if feasible, group them with prefixes that relate related tables, etc). Then in the FROM, I only use JOIN and LEFT JOIN unless there's really no other way, and all my inner JOINs come before my left ones. All of those have AS statements renaming them to one-character prefixes too, and they have a clear ON condition that connects them to the above blob (so always "AS s WHERE s.whatever = ...") even though that makes the queries longer to refactor when you want to rearrange the joins (you often have to shift all the OFs down by one and reverse which side of the equality comes first or some nonsense). Subqueries should move up to a WITH or should be rewritten as LEFT JOINs if feasible. You want to use structure to guide a reader through this thing that could be complicated... That structure could be lexical structure in the SQL itself or it could be syntactic structure from a wrapping language, I don't care so much. The real problem is having one source-of-truth for the database schema, and that problem is just barely tractable with current languages, but I don't see anybody who does it right.
- hot_gril 4y ago
  > In many ways it's similar to preferring C where you can really feel the assembly-language underneath you even if you're not writing it. When you write C, the ASM below it is much more complex. When use a query builder, the SQL beneath is about the same length and complexity. It's more like transpiling Swift to Java.
giraffe_lady 4y ago
I think you'll see the whole range of responses, even some very reasonable defenses of ORMs for some cases. I've come to prefer a yesql type approach that parses your sql into functions and provides a mechanism for applying functions to the bound data before running and the returned data after. Keeps things nicely separated and you can unit test your sql functions as application code.
hot_gril 4y ago
I don't like query builders because SQL is actually easier to write and read even when you're not using advanced features, but I don't consider them a serious problem like ORMs.
zzzeek 4y ago
> how does HN receive SQL builders in general? I feel like most of us agree ORMs are typically a bad idea. I haven't observed that at all, my anecdotal impression is quite the opposite. Have you taken a poll?
hn_throwaway_99 4y ago
Glad to see this posted. I totally agree - I think Kysely is a cool project but I do think query builders ARE generally an anti-pattern. I'm a huge fan of slonik, https://github.com/gajus/slonik#readme https://github.com/gajus/slonik#readme, which uses template strings for SQL and has recently come a long way with its strong typing support. This blog post by the author of slonik outlines why he thinks query builders are anti-pattern, https://gajus.medium.com/stop-using-knex-js-and-earn-30-bf410349856c https://gajus.medium.com/stop-using-knex-js-and-earn-30-bf41.... No, I'm not him, but I strongly agree with most of his opinions here.
dns_snek 4y ago
Query builders can be amazing if they make it easy to logically compose highly dynamic queries, just like you would do with any other code. I haven't tried Kysely or Knex but I really enjoy working with Ecto in Elixir. https://hexdocs.pm/ecto/dynamic-queries.html https://hexdocs.pm/ecto/dynamic-queries.html

zerace 4y ago

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

guntars 4y ago
I would prefer 1. if it had full type safety. I’m imagining some sort of a build process that looks for any .sql files in the project and spits out .sql.d.ts in the same directory with the input and output types according to the current schema of the database. Another nice thing about a setup like this imo would be that the .sql files would have the full support of the editor for completions and execution, unlike sql fragments in a Typescript file.
- cheeseface 4y ago
  Slonik comes pretty close https://github.com/gajus/slonik https://github.com/gajus/slonik. It does not inspect your DB schema, but has worked very well in our SQL-heavy architecture.
- csnweb 4y ago
  For Postgres there is https://github.com/adelsz/pgtyped https://github.com/adelsz/pgtyped, sounds pretty much like what you describe?

crdrost 4y ago

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 4y ago

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.

jakewins 4y ago

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/select-parser.ts#L77 https://github.com/koskimas/kysely/blob/master/src/parser/se...

kamilafsar 4y ago
The relevant TS feature is called template literal types: https://www.typescriptlang.org/docs/handbook/2/template-literal-types.html https://www.typescriptlang.org/docs/handbook/2/template-lite...
nikeee 4y ago
You can use these template literal types + infer to build an entire SQL parser. I did a POC that infers SQL query types by parsing the SQL query on a type level: https://github.com/nikeee/sequelts https://github.com/nikeee/sequelts Building this parser is pretty cumbersome and supporting multiple SQL dialects would be lots of pain. While I'm not a fan of query builders per se, Kysely pretty much covers everything that my POC tried to cover (except that 0 runtime overhead). However, you get the option to use different DBMs in tests than in production (pg in prod, sqlite in tests), which is a huge benefit for a lot of people. sequelts was designed to work with sqlite only. And it's a hack.
eddsh1994 4y ago
The typescript type system is insane, I solved quite a few project Euler tasks with it in the type system itself (From peano numbers up). I learned to never question the TS community

skrebbel 4y ago

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

CuriouslyC 4y ago
Notably, in Zapatos you write SQL, whereas with this you use a query builder. Personally I think staying close to SQL is a feature, but YMMV.
- gmac 4y ago
  Yes — though there are also shortcut functions for basic CRUD (and also for lateral joins) which give you automatic typing.
lf-non 4y ago
I started using zapatos in a project but eventually moved away from it. Their shortcuts and lateral join support is nice, but you very often need to write raw sql and manually provide types and ensure they are correct for all these cases. It is also possible but awkward to use interpolations to construct very dynamic queries where based on filter conditions you need different joins or unions etc. I looked at some of the solutions that infer ts types from sql queries but eventually felt it was more maintenable to keep the dynamic query generation on the ts ide I found ts-sql-query [1] to be much better suited for my use cases. It is very feature rich and has very good support for various dialect specific features in all mainstream databases. Also Juan (the author) is very helpful with queries and suggestions. [1] https://ts-sql-query.readthedocs.io/en/stable/ https://ts-sql-query.readthedocs.io/en/stable/

bastardoperator 4y ago

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.

seer 4y ago
you can try https://github.com/ivank/potygen https://github.com/ivank/potygen - type gen from sql itself supports all kinds of complex stuff like views, ctes json objects etc.
chetanbhasin 4y ago
I think that is particularly what I like about this. With Prisma optimising your code is very hard because you cannot just customise joins like here. From the example, it seems that you can create joins and it also shows you in the documentation what the actual SQL for that will look like. I still have Prisma running on my projects, so it will be a bit hard to move now particularly because it has TS native migrations, which is another issue. If I wanted to use these outside of TypeScript (let's say another service or middleware), then it would be very hard.
dglass 4y ago
Prisma is good for writing simple selects, updates, and deletes. But the moment you need to write any kind of advanced query with joins, nested queries, unions, etc. it gets frustrating very quickly. Kysely and Knex are far more flexible for writing complex queries and don't get in your way.
LewisJEllis 4y ago
I see a kysely + kysely-codegen (generates types from DB schema) setup as comparable to Prisma in TS integration, with the added flexibility/closeness-to-SQL of the querybuilder. If you: - have used/liked Knex (or similar querybuilders) before - like the TS integration + type safety of Prisma - but find Prisma to be a bit too magic/heavy with its query engine and schema management - and/or just want to be closer to SQL then Kysely is what you're looking for.

HatchedLake721 4y ago

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

LewisJEllis 4y ago
No, he's stated somewhere in the issue tracker that he has no plans to ever do this. edit: here - https://github.com/koskimas/kysely/issues/162#issuecomment-1249356092 https://github.com/koskimas/kysely/issues/162#issuecomment-1...

yasserf 4y ago

This is really cool, will look into using it in future projects! I also made a tool (https://github.com/vramework/schemats 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 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

deleted 4y ago

[deleted]

deleted 4y ago

[deleted]

jerryu 4y ago

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 https://www.youtube.com/watch?v=9VaBRPAtX08 What do you think about creating diagrams using the simple markup language in my tool?

haywirez 4y ago

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

bottlepalm 4y ago

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.

Sharlin 4y ago

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

frosted-flakes 4y ago
I was wondering what it meant. It's too bad English has a different pronunciation for "y", because the specified pronunciation Key-Seh-Lee means something totally different in Finnish (kiisseli, which is a type of dessert, a thick fruit soup that was a common tradition in my family).
- Sharlin 4y ago
  Heh, indeed. English doesn't really have the /y/ phoneme at all, so a word like "kysely" is pretty tricky to pronounce for a native English speaker.

lofaszvanitt 4y ago

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.

crooked-v 4y ago
One key thing is that a variety of query builders and ORMs - I'd point to TypeORM for JS/TS, for example - allow in-IDE verification against a set schema and type inference of the results in whatever language you're working in.
- lofaszvanitt 4y ago
  Ah, too much oo and type magic. 90% of things do not need those, it just increases development time many times fold.
  - hot_gril 4y ago
    Query builders are a slight annoyance at worst, while ORMs can easily screw up the foundation of your system. You get extra compile-time checks with a query builder if you're using TS (a big piece of bloat on its own) instead of JS, which can be nice, but that's also what tests are for.
    - lofaszvanitt 4y ago
      :DDDD
deleted 4y ago
[deleted]
valzam 4y ago
I think the main problem is getting compile time errors and autocomplete for your queries. Something like Kysely doesn't fully solve this but it avoids dumb errors like typos in sql commands or hanging commas. That being said, writing pure sql with Jetbrains IDEs is pretty amazing since you can get autocomplete straight from your production db schema. We use sqlx in Go + pure SQL and it works pretty well. Very simple things take slightly longer but writing complex queries is more straightforward than trying to get the builder to do the right thing.

hn_throwaway_99 4y ago

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-bf410349856c 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 https://github.com/gajus/slonik#readme

hbrn 4y ago
I tend to agree. Query builders are a "cool" solution looking for a problem. That said, dynamic query building example looks way better in knex than in slonik. Proposed approach has it's benefits, but the code is much harder to read. And it gets exponentially worse with more complicated queries. The caveat, however, is that dynamic queries themselves are almost an anti-pattern, and should be avoided when possible.

iddan 4y ago

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)

FireInsight 4y ago

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 https://en.m.wikipedia.org/wiki/Kissel).

bayesian_horse 4y ago
I remember it faintly from Finnishing the Finnish course on duolingo. I made it half-way through legendary, but I think I forgot most of it. Also it's unlikely to get more advanced content since it was a community contribution and Duolingo now switched to paid contributors only.
pandem 4y ago
A more relevant meaning is probably a query as in SQL-kysely means an SQL query. Feel bad for Finnish students though trying to google something about SQL queries now in Finnish as most results seem to be about this project.

moomoo11 4y ago

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.

throwayyy479087 4y ago

Reminds me of pgtyped

T3RMINATED 4y ago

[dead]

jokull 4y ago

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