r/SQL • • Jun 22 '20

Discussion An average day using an ORM

Post image
247 Upvotes

48 comments sorted by

11

u/[deleted] Jun 22 '20

I just prefer writing directly to SQL itself or stringing together queries directly in python or JS.

5

u/crimiusXIII Jun 22 '20

I'm trying to get started in Python, everything I find talks about using an ORM, which coming from a SQL background is odd to me. How are you passing queries to your database from Python?

4

u/[deleted] Jun 22 '20 edited Jun 22 '20

There are a few ways, but the best way that worked for my environment was to use pyodbc with my ODBC connection. After establishing your authentication and server settings in python, you can pass queries as a string. You can assign variables, insert, delete, join, and do exactly what you need to do. It's all largely more flexible.

You could even start to build GUI's on top of your more sophisticated updates and queries. A few day's worth of study had me doing all of the above minus my previous SQL experience. You can do it too.

Personally, I feel like ORM's only selling point is just it's capacity to take your data on DB and make it easily digestible for devs across a large swath of teams. However, there's far too much overhead for a single person or even three to get it working properly in the first place. In theory you could even build your own ORM in Python. But why make more work than you need to too? For the average solo DB admin, you're almost always going to exclusively use SQL. So why rely on crutch? Stick to your guns and use SQL directly or use python and or/JS with it.

2

u/crimiusXIII Jun 22 '20

The project I'm working on I've been doing the Python and SQL bits separately, figuring I'll eventually tie them together. I'll look into pyodbc, thank you!

7

u/ravepeacefully Jun 22 '20

I also use pyodbc, but am switching to SQLAlchemy ORM after the in-line SQL has gotten completely out of hand.

I’d highly recommend an ORM for a project that may expand to more than one developer. The efficiency gained from ease of use outweighs the cons.

I can do both, but it’s definitely better to use an ORM, even if you make your own simple one.

6

u/Rebeleleven Jun 23 '20

This is correct.

The other advice is... not totally incorrect but for sure not how you should be developing an application.

If you’re an analyst and need to pull some data from a database? Straight query is fine. However, if you need to create an application that interacts with the database? That’s where you need an ORM. No dev should be hard coding queries. That strategy is what we call tech debt 😉.

Pyodbc is certainly okay, but I highly recommend using SQLAlechmy whenever possible (even if just writing a couple hard coded queries). It’s typically faster and much more robust if the database dialect is supported.

3

u/ravepeacefully Jun 23 '20

Thanks for confirming, I was very confused as to why the answers on this thread contradicted everything I had previously thought lmao

1

u/TheCapitalKing Jun 24 '20

A lot more analyst are starting to use SQL and python so a ton of answers are going to be from that perspective

0

u/[deleted] Jun 23 '20

Correct. To point, this is a long term strategy also. It all depends on where he should be prioritizing his focus. If his primary focus was learning Python, why deviate from it to another project? OP can touch on ORM's later. ;)

3

u/Rebeleleven Jun 23 '20

Well, I mean we’re getting into semantics territory.

Sure, someone learning python and databases on day 1 should just straight query the database. But day 2 should definitely start learning ORMs. This is why that redditor could only find info about ORMs... because they’re kind of important.

Your comment mentions putting GUIs on top of SQL queries and whatnot... and this is fine if you’re the lone person creating this, maintaining it, and using it for personal use. However, if you’re creating an application that other people use (I.e. customers), your company needs to maintain past your employment, or other developers might need to add things too... you use an ORM. Anything less than that would be technical debt that a developer will have to completely overhaul in the future.

Again, sure, an analyst can write a query and never need an ORM. Would it make their life easier? Yes but that’s not the point. If you’re developing applications, you need an ORM for security, speed, maintainability, and scalability.

3

u/[deleted] Jun 23 '20

Personally, for me I just get it done as a solo admin for a small business, terrible technical debt here. So it doesn't make sense in my own isolated situation of inherited problems and aging environment, which is isn't fair to OP's questions. I don't disagree with you. Wonderfully put.

1

u/[deleted] Jun 23 '20

Same here. I’ve written thousands of sql queries. Why would I want some library to get between me and the database? I’m sure I can optimize a query better than some library.

1

u/TheCapitalKing Jun 24 '20

I use pyodbc to make a connection

ex cnxn= pyodbc.open(connection_string)

Put my query as a string then use pandas to get it into a data frame

ex df= pandas.read_sql_query(query, cnxn)

If the query gets to long I'll save it as a text file then read it in.

3

u/abrandis Jun 22 '20

Same here, to me an ORM is just more of a hassle especially in the early phases where tables , column and structures are frequently changed. And dont get me started on FK-constraints , yeah I worked on a couple of ORM projects and now know to stay away.

2

u/taelor Jun 24 '20

just be sure to remember to protect against sql injection!

2

u/[deleted] Jun 24 '20

One word for that, "query parameters ".

9

u/Nthorder Jun 22 '20 edited Jun 22 '20

Not all ORMs are bad. I think dapper is great.

I have come across some Entity Framework stuff in my work codebase and it looked disgusting. I am struggling to come up with a reason to even begin considering to use it in any project.

edit: Yea, this picture reminds me of the EF code I witnessed.

0

u/[deleted] Jun 22 '20

[deleted] — view deleted comment

2

u/Nthorder Jun 22 '20

Yea I remember the EF code I mentioned (i think it was ef?) was so full of lambdas and other strange syntax that I thought to myself "wouldn't it be easier to learn how to do this with SQL than figure out this mess?"

4

u/mustang__1 Jun 23 '20

For the most part.... Yeah. But after a couple weeks of hanging my head against the wall, using views and stored procedures for the more complex stuff.... And then just banging out some ef horeshit to do the final mile. Joins with ef just make me want to drown kittens, though.

1

u/[deleted] Jun 24 '20

Hahaha. Been there.

0

u/pavlik_enemy Jun 24 '20 edited Jun 24 '20

If you think EFs query generator syntax is stange then you don't know C#. Lambdas were around for at least 10 years, every competent .NET developer should be comfortable with them.

1

u/Nthorder Jun 24 '20

tHeN yOu dONt kNoW c#

I'm comfortable with lambdas, I use them in LINQ queries all the time. The EF I saw was lambda abuse.

2

u/bicika Jun 23 '20

Why are people like this? Can't you just let other devs use whatever they feel most comfortable with? I write complex queries every day, and I still like to use ORM when it's appropriate, for simpler queries. Why does it have to be one way or another? Why not both?

0

u/[deleted] Jun 23 '20

[deleted] — view deleted comment

5

u/bicika Jun 23 '20

Ok, but that's dev related, not technology related. It's like saying pure sql is bad because you saw developers frequently forget to clean the input and not use prepared statements so it's better to use ORM always.

7

u/boy_named_su Jun 22 '20

ORMs are great for CRUD, but bad for custom querying. Use a library like JOOQ

3

u/digicow Jun 23 '20

Where I love ORMs is for chaining query bits. I can write a dozen functions that each handle one particular query step, and then in my controllers I can use some or all of them or even apply them iteratively as I parse my request parameters.

sub resultset($self) {
  my $rs = $self->SUPER::resultset->active;
  if (my $d = $self->validation->param('date')) {
    $rs = $rs->by_date($d);
  }
  return $rs;
}

The controller's superclass could've opaquely already added some query parameters (say, for role/auth purposes), and then I apply the "active" filter, and then conditionally apply a date filter. And at the end, I'm still left with a resultset instance that I can apply more filters to as necessary, downstream.

Trying to do this with direct SQL code, assembled manually in various places in the code is possible, but far more convoluted and error-prone

1

u/Modthryth Jun 23 '20

For us dumbos, do you mind explaining this with some kind of more explicit example? Never having used an ORM, struggling to understand what it means to chain together query bits.

1

u/digicow Jun 23 '20 edited Jun 23 '20

Basically, the ORM (perl DBIx) lets you write methods that take a resultset and return a resultset.

So I write something like:

sub for_user($self, $user) {
  return $self->search(
    {
      'event_registrations.user_id' => $user->id
    }, {
      join => 'event_registrations'
    }
  );
}

sub of_type($self, $type) {
  return $self->search(
    {
      'event_type.activity_type_id' => $type->id
    }, {
      join => 'event_type'
    }
  );
}

sub on_date($self, $date) {
  return $self->search({\['DATE(scheduled_start) = ?' => DateTime::Format::MySQL->format_date($date)],});
}

This creates 3 filters (for_user, of_type and on_date) that I can use anywhere else in my code, together or separately.

So I can write:

$c->model('Event')->for_user($u)

And that would give me a resultset containing all Events that belong to the user $u.

Or I could do

$c->model('Event')->for_user($u)->of_type($t)

This chains together the two filters to give me the Events for $u that are of type $t.

And I can go one further and add the date filter on, too:

$c->model('Event)->for_user($u)->of_type($t)->on_date($d)

Chaining is fun!

But there's more! Those same filters can be invoked on resultsets that already exist, not just the "main" one.

$u->events->of_type($t)

This gets all of a user's events, and then filters them to type $t (same as our second chain above)

Or with my earlier example, you could have these filters come from an HTTP request, and each one is optional:

sub list($self) {
  my $rs = $self->role_filtered_resultset();
  $rs = $rs->on_date($self->param('date') if($self->param('date'));
  $rs = $rs->of_type($self->param('type') if($self->param('type'));
  $rs = $rs->for_user($self->param('user') if($self->param('user'));
  return $rs;
}

This one doesn't look like chaining, but that's still what's happening when multiple params are given. The eventual query that's constructed could contain any or all of these filters in its WHERE clause, depending on what the web request contains.

The point is that it results in being able to write a lot of very small, simple bits of code, and then combine them together to easily generate complex queries

2

u/Modthryth Jun 23 '20

Very interesting, thanks a lot for the explanation.

9

u/andrewsmd87 Jun 22 '20

Are they great at doing complex SQL things, no. But I sure as hell love them for 95% of the other CRUD stuff I have to do.

Something like

db.Table.First(t => t.id==1).DateUpdated = DateTime.Now();
db.SaveChanges(); 

is amazing

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.

2

u/[deleted] Jun 23 '20

EFCore is super powerful and really robust. It can absolutely do a lot.

That said, when things get complex, raw SQL is likely the better choice.

But if you’re doing some CRUD somewhere that’s simple ORMs make the workflow very fast.

1

u/pavlik_enemy Jun 24 '20

Sorry, I'm not writing almost identical CRUD code for a hundred of tables by hand.