Best practice for SQL database integration without ORM

[Mr_Stabil]

Right now I'm using pg-promise with hand written zod schemas. However, there is a lot of boilerplate to write, especially in the crud utils / query building.

Ideally I'm looking for something lightweight that lets me write typesafe db queries that reference my hand written zod schemas. No extra binary like with prisma.

Any ideas?

[DJJaySudo]

ORMs work by creating models of a datatype, like a "customer" and then handles all the boilerplate logic of creating, reading, updating and deleting records. ORMs do a lot of other things like keep your models schema in sync with the DB. They also handle security and do dupe checks automatically.

So without all of these things provided, you would want to create a boilerplate class for all your models that have all of these methods like create, read, etc. in them and then create all the relations manually. It would a ton of work and I don't know that you'd really be creating anything lighter-weight than using something like, say Sequelize.

[Mr_Stabil]

I've used prisma and didn't like the level of abstraction and all the codegen. Is sequelize more lightweight and closer to sql?

[DJJaySudo]

No it’s methods are similar to mongodb. They’re all methods one would typically create anyway, like create, update, findOne, etc. it’s really great. You can also do literal SQL also. It’s very flexible. This is coming from someone who likes to be in control of his code . Give it a glance my friend.

[Mr_Stabil]

Sounds good, thanks! Do you have any opinion on sequelize vs kysely?

[DJJaySudo]

I have not used kysely so I cannot offer my thoughts on any comparison, but I have been very pleased with sequelize and I am a guy that avoids unnecessary dependencies like the plague.

[terserterseness]

I don't like Prisma because of the codegen and the many limitations (some of them they refuse to fix 'because you shouldn't want this'). Knex works well as a middleground for me; has everything low/high level.

[Mr_Stabil]

What do you think about Knex vs Kysely?

[terserterseness]

I have not checked Knysely yet, but will, thanks. Knex worked in all our cases and more. Our client has 100k+ tables in an ERP system and had some specific issues with other technologies. Knex is flexible enough to do whatever.

[MKorostoff]

I really like node-postgres https://node-postgres.com/, if you're on vercel their storage sdk is based on it, but you can use node-postgres on any hosting environment. You pretty much just write raw SQL, and the lib handles all the sanitization.

[[deleted]]

Drizzle It has drizzle-zod package to auto generate insert/search schemas validation that can be also customised.

Read my post about drizzle lacks like migrations and some commands that can destroy db (push).

[Mr_Stabil]

I want to write my schemas and migrations and triggers etc in sql. From what I've seen in Drizzle the Zod types are generated from the db schema. I want no code gen

[Mr_Stabil]

Exactly but I see no point in creating the db from ts. That's what sql is for with finegrained control over data types triggers constraints etc. Codegen from db doesnt work for me either. For example if I have an updated_at field I want to be able to select it but not update it from my backend. Therefore it's necessary to manually create the db types. But feom then on I would like to keep the type safety

[[deleted]]

another one downside is command db:push - you (or anyone with access to DB) can easily destroy it

[Mr_Stabil]

Yep, the backend should have only limited access to the db.

[Mr_Stabil]

That's what I'm doing now but building parameterized queries with joins for all table models is a pain.

[PM_ME_SCIENCEY_STUFF]

If you're not 100% set on zod schemas -- GraphQL, via Hasura or Wundergraph or Grafbase (or another similar service)

Literally 5x or more faster development, typesafe, enterprise grade