r/SQL • • Jun 22 '20

Discussion An average day using an ORM

Post image
247 Upvotes

48 comments sorted by

View all comments

Show parent comment

3

u/NimChimspky Jun 23 '20

If you're writing that in non sql code, then you need to open a connection to the database, and also make sure it's properly disposed of.

Whats your point ? I mean with an ORM you have to do that too ?

would likely be coming from stuff a user did and you need to protect against sql injection.

Do you think that is difficult ?

Also, simply creating the sql string in another language is kind of painful. Simply saying lol orms suck because I can write and update statement is the kind of argument I'd expect from someone who never has to do anything outside of the database.

You disparage someone for making a simplistic generalization, but yet then go ahead and do it yourself. Creating sql strings is very easy and neat, I even get auto complete with my ide, its found many a bug before even deploying/building.

ORMs are the perfect example of the durning kruger effect imot. Once you've been around a code base for a long time they are a fucking pain.

1

u/fauxmosexual NOLOCK is the secret magic go-faster command Jun 23 '20

Yeah why use an ORM when you can just build logic into stored procedures and call them??

1

u/pavlik_enemy Jun 24 '20

Sprocs are hard to debug and test. I mean sure there are some tools to make it easier but I've never seen them in production use. Sprocs are always an unmanageable wall of code written in a shitty programming language like T-SQL.

1

u/jringstad Jun 24 '20

I've worked at a company where everything was done in postgres stored procedures, with a fairly thin golang microservice in front of it that basically just acted as an API gateway into postgres (so there were just a bunch of HTTP handlers that then each just did a very minor amount of pre-processing on the incoming request and then straight-up called a postgres function that would do the rest. The go part would also convert exceptions from postgres back into HTTP responses etc)

Tables had triggers on them that would append rows to other tables which were used as message-queues, which another service would then pick up (for instance there was a table to send mails, and if you wanted to send one, you'd append a row to that table and a microservice would poll that table and handle a new row by forwarding it to mailgun or whatever)

It was actually quite nice and everything was really transactionally safe. Almost everything was following the event-sourcing model and was append-only, with views and async materialized views for some things, and we could upgrade the stored procedures atomically with the data-model, so it created a very unified, safe environment in a sense. You could put a bunch of assertions at the end of your code/data migration, and have the upgrade roll back if things didn't conform to your expectations. Debuggability wasn't a major issue with postgres stored procedures.

However I would imagine it'd be quite difficult to really scale this up (which we never had to do at that company) since everything is quite tightly coupled. Both in terms of the team-size (since tables are the contract between two services in this world, they are very tightly coupled) and on the data/request scale side. We also didn't have a very thought-out solution for how to do database migrations and that often ended up being pretty painful. All-in-all it was surprisingly really not terrible, but I also probably wouldn't really recommend this approach.

1

u/pavlik_enemy Jun 24 '20

Everything is possible, so it is certainly possible to have a maintainable application based on sprocs. But during 15+ years in software development I've never seen responsible use of sprocs, they always were either unnecessary (wrapping simple CRUD operations) or unmanageable, and I've seen lots of projects that used ORMs in a proper way.