[HN Gopher] Kysely: TypeScript SQL Query Builder
___________________________________________________________________
Kysely: TypeScript SQL Query Builder
Author : cal85
Score : 138 points
Date : 2023-01-24 17:17 UTC (5 hours ago)
(HTM) web link (github.com)
(TXT) w3m dump (github.com)
| zerace wrote:
| 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 wrote:
| 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.
| csnweb wrote:
| For Postgres there is https://github.com/adelsz/pgtyped,
| sounds pretty much like what you describe?
| cheeseface wrote:
| Slonik comes pretty close https://github.com/gajus/slonik. It
| does not inspect your DB schema, but has worked very well in
| our SQL-heavy architecture.
| kristiandupont wrote:
| I made a tool that generates Typescript types out of a live
| Postgres database. I've had a request for Kysely support
| (https://github.com/kristiandupont/kanel/issues/273), but I
| more or less forgot about it. I would love to hear if that
| would be helpful.
| LewisJEllis wrote:
| I think https://github.com/RobinBlomberg/kysely-codegen does
| roughly the same thing?
| kristiandupont wrote:
| Looks like it, yes. Do you use it?
| mirekrusin wrote:
| We use in prod variant of no 1. [0]. Why? Because:
|
| * it's sql
|
| * it's extremely lightweight (built on pure, functional
| combinators)
|
| * it allows us to use more complex patterns ie. convention
| where every json field ends with Json which is automatically
| parsed; which, unlike datatype alone, allows us to create
| composable query to fetch arbitrarily nested graphs and
| promoting single [$] key ie. to return list of emails as
| `string[]` not `{ email: string }[]` with `select email as [$]
| from Users` etc.
|
| * has convenience combinators for things like constructing
| where clauses from monodb like queries
|
| * all usual queries like CRUD, exists etc. and some more
| complex sql-wise but simple function-api-wise ie. insertIgnore
| list of objects, merge1n, upsert etc all have convenient
| function apis and allow for composing whatever more is needed
| for the project
|
| We resort to runtime type assertions [1] which works well for
| this and all other i/o; runtime type assertions are necessary
| for cases when your running service is incorrectly attached to
| old or future remote schema/api (there are other protections
| against it but still happens).
|
| [0] https://github.com/appliedblockchain/tsql
|
| [1] https://github.com/appliedblockchain/assert-combinators
| robertn702 wrote:
| You might also want to consider pgTyped
| (https://github.com/adelsz/pgtyped). It's supposed to make SQL
| and TS work together. I haven't gotten around to using it yet
| but I hear good things.
| drpotato wrote:
| Can weigh in here, we use pgtyped heavily at work and it's
| really good. There are some constraints with the type
| inference on complex queries, but it's a decent trade off
| IMO.
| HatchedLake721 wrote:
| Koskimas, awesome work! Will we ever see a lightweight ORM like
| Objection.js on top of Kysely?
| LewisJEllis wrote:
| 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-1...
| satvikpendem wrote:
| 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.
| skrebbel wrote:
| Also check out https://jawj.github.io/zapatos/ which has a
| similar non-ORM yet fully typesafe approach.
| CuriouslyC wrote:
| 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 wrote:
| Yes -- though there are also shortcut functions for basic
| CRUD (and also for lateral joins) which give you automatic
| typing.
| yasserf wrote:
| This is really cool, will look into using it in future projects!
|
| I also made a tool (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). 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
| crdrost wrote:
| 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.
| jakewins wrote:
| 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/se...
| nikeee wrote:
| 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
|
| 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.
| kamilafsar wrote:
| The relevant TS feature is called template literal types:
|
| https://www.typescriptlang.org/docs/handbook/2/template-lite...
| haywirez wrote:
| Objection is an incredible SQL ORM library from the same author,
| I learned a lot of concepts from it over the years.
| bottlepalm wrote:
| 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.
| [deleted]
| [deleted]
| MuffinFlavored wrote:
| 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://www.npmjs.com/package/sql-template-strings ("out of date"
| since like 2016? 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?)
| giraffe_lady wrote:
| 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.
| nullwarp wrote:
| 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]
| crdrost wrote:
| 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.
| jerryu wrote:
| 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
|
| What do you think about creating diagrams using the simple markup
| language in my tool?
| bastardoperator wrote:
| 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.
| cypress66 wrote:
| As someone who uses both prisma and knex (so something like
| kysely):
|
| Prisma is very very nice, it has maybe the best DX out there.
| My issue with it is performance. It is much slower than query
| builders or raw sql. It's also a huge black box, although I
| haven't had any issues, you're dealing with a complex beast.
|
| Knex (and other query builders) is nicer than raw sql, has good
| performance, and it's fairly transparent (it's not a 800 pound
| gorilla like Prisma)
| LewisJEllis wrote:
| 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.
| chetanbhasin wrote:
| 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.
| seer wrote:
| you can try https://github.com/ivank/potygen - type gen _from_
| sql itself supports all kinds of complex stuff like views, ctes
| json objects etc.
| dglass wrote:
| 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.
___________________________________________________________________
(page generated 2023-01-24 23:00 UTC)