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.

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.

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.

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.

Zapatos offers something similar here: https://jawj.github.io/zapatos/

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)

The main difference to me is that with Prisma I don't need to do any SQL to get a project up and running. I can define the tables and relations from my code or from Prisma Studio, and in one swoop I get the db structure done and the types defined.

I know my way around a database, but I'd rather not leave my code editor whenever I need to add a new column to a table.

With Kysely you have to create the DB schema, and then write the types; with every change you need, you gotta do both again.

(At least this seems true by default; as the project's readme mentions, there is a code generator[1] to generate the types from the DB schema; not quite the same but at least it's better than nothing.)

Commenters say that writing complex queries with Kysely is easier, which makes me wonder if I could use Prisma except for those, since Kysely should be able to just generate the SQL query for me to handle to Prisma...

[1]: https://github.com/RobinBlomberg/kysely-codegen

Safeql[1] can also be a good middleground. Adds type checking to prisma raw queries.

[1] https://safeql.dev/compatibility/prisma.html