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).
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...
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
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.
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.
Template strings in TypeScript allow you to safely write SQL directly, still with type checking: https://github.com/gajus/slonik#readme
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?
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.
I've also tried:
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?)