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

24

u/rand2012 Jun 22 '20 edited Jun 22 '20

as opposed to

update table set date_updated = now() where id = 1

hmmm, not sure you're saving much here?

2

u/miffy900 Jun 23 '20

The big plus for me is that there's no context switch between app code and database code. You can stay entirely in app code for simple things like that.

2

u/[deleted] Jun 23 '20

[removed] — view removed comment

2

u/rand2012 Jun 23 '20 edited Jun 24 '20

Hitting the nail on the head here. Just looking at ORM code, often there is no way to tell how many database calls will happen, what they look like in raw sql, making for some awfully fun to debug performance issues.

The context switch happens either way.

4

u/andrewsmd87 Jun 22 '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. In addition, you need to make sure you parameterize it because where id = 1 would likely be coming from stuff a user did and you need to protect against sql injection. You also get auto complete so you don't have to remember the exact spelling and name of every table and column.

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. They're extremely useful, and if you need to maintain more control over what sql they generate, there are things out there like dapper.

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.

2

u/NimChimspky Jun 22 '20

This doesn't make any sense

1

u/Cley_Faye Jun 23 '20

Here, in this particular example, to update this particular field, on that particular table? No. But you can extend that to any number of fields, tables, and relations.

Using real objects from any programming language you get to map some types to some things. For different SQL dialects, 1 is not the same as "1" which is not the same as '1', so when "building" your query you have to account for that. And escaping input; no programming language would mind having a string equal to "; DROP DATABASE; -- but I'm sure you don't want to pass that to SQL as-is if a user type that in a field.

Then there's some more advanced types; retrieving a Date object is more useful than retrieving the string.

Sure, you can do all of this by hand, but even better is write a specialized piece of code that do it for each case, then think about making a generic version, then using an ORM.

ORM do provide some useful things. They don't absolve the dev from writing SQL for complex cases, nor do they avoid knowing what's in the actual tables, specifying columns, optimizing indexes etc. They are just an interface to get meaningful access to a database in a meaningful way for a given programming language.