Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

To ORM or Not to ORM

eli.thegreenplace.net

Post reply on HN
Print view View original

81–90 of 300 posts

Re: To ORM or Not to ORM

#81

Post by paddy_m »

hardwaresofton wrote: post #65

While I learn towards not using an ORM, the productivity gains (at the very least early on in development) are undeniable. What I've always looked for are frameworks that give you an ORM but also make lower level queries very easy, normally via a query builder, allowing you to go back and forth between levels of abstraction. If I had to choose, I prefer libraries that give you the lower level of abstractions first an…

You can do this with most any ORM by mapping tables on top of views defined in SQL. I regularly use this pattern with Django's ORM to make complex aggregations only a foreign key away. I can write detailed performant SQL that it is impossible to make an ORM output this way.

Re: To ORM or Not to ORM

#82

Post by d4mi3n »

hardwaresofton wrote: post #65

While I learn towards not using an ORM, the productivity gains (at the very least early on in development) are undeniable. What I've always looked for are frameworks that give you an ORM but also make lower level queries very easy, normally via a query builder, allowing you to go back and forth between levels of abstraction. If I had to choose, I prefer libraries that give you the lower level of abstractions first an…

There's another big aspect of ORMs a lot of people tend to skip in discussion: Security.

Raw SQL can be dangerous, and given enough people and code somebody will eventually make a mistake (as is human) and introduce a vector for a SQL injection attack or some other DB specific vulnerability.

A good ORM can be a fairly effective layer of safety.

Re: To ORM or Not to ORM

#83

Post by ordu »

sdegutis wrote: post #60
henning wrote: post #20

I wish people did not think the choice was solely between "write raw SQL with raw strings" and "try to pretend the database is object-oriented when it is not." The third approach is to safely wrap the database and its columns with code in a way that is composable. In Python, SQLAlchemy has an ORM, but it is optional, and you can just work with tables and columns if you want.

There are so many incompatible needs for databases that most DB wrappers of any kind rarely make sense except when prototyping. And database wrapper authors have a tendency to cater to lowest common denominator of features so that they can treat all databases the same, which is going even further in the wrong direction. Choosing a database requires learning what your requirements are, learning what the candidates are…

The "write-raw-SQL-with-raw-strings" approach has one serious issue -- sql-injections. Some people argue that it is not so hard to filter strings before concatenating them into sql-query, but I know also people who argue that it is not so hard to write C code and to not introduce bugs around NULL and wild-pointers, one just needs to be careful. I, personally, do not believe that strategy "be careful" can work reliably here.

I cannot imagine work with SQL without query builder, which introduces type checks and conversions, and escapes strings when needed. ORM is an optional thing, but it is nice to have some layer that maps rows into structs and vice-versa. With dynamically typed scripts it doesn't matter: in any case you would get a hashtable (the only difference is a syntax used), but with compiled language and static typing I feel some uneasiness when using slow hash table mapping instead of blazingly fast struct field access. And the tooling can help to declare that structs statically.

Re: To ORM or Not to ORM

#84

Post by GordonS »

d4mi3n wrote: post #82
hardwaresofton wrote: post #65

While I learn towards not using an ORM, the productivity gains (at the very least early on in development) are undeniable. What I've always looked for are frameworks that give you an ORM but also make lower level queries very easy, normally via a query builder, allowing you to go back and forth between levels of abstraction. If I had to choose, I prefer libraries that give you the lower level of abstractions first an…

There's another big aspect of ORMs a lot of people tend to skip in discussion: Security. Raw SQL can be dangerous, and given enough people and code somebody will eventually make a mistake (as is human) and introduce a vector for a SQL injection attack or some other DB specific vulnerability. A good ORM can be a fairly effective layer of safety.

When people talk of using "raw SQL", I (hope!) they generally mean using paramaterised queries, which mitigates against most injection attacks.

Re: To ORM or Not to ORM

#85

Post by jandrewrogers »

I don't have any issue with ORMs in principle, and even wrote an ORM once. However, in almost every place I've seen them used they've become a way for developers to avoid understanding how databases work, inevitably leading to inexplicable data models and poor performance. In practice, ORMs tend to end up creating crippling technical debt that is difficult to fix.

If ORMs were typically used by developers that fully understood the implications for the underlying database, there would be few issues with them and they would be a valuable tool. Unfortunately, they seem to be most popular with developers that are the opposite of that description.

Re: To ORM or Not to ORM

#86

Post by ChicagoDave »

You should decide on the type of data storage for a given domain and then decide on the patterns required to support that domain. The danger of using an ORM is that you can easily hide critical business functionality.

Re: To ORM or Not to ORM

#87

Post by hardwaresofton »

GordonS wrote: post #84
d4mi3n wrote: post #82

Earlier quoted context omitted.

There's another big aspect of ORMs a lot of people tend to skip in discussion: Security. Raw SQL can be dangerous, and given enough people and code somebody will eventually make a mistake (as is human) and introduce a vector for a SQL injection attack or some other DB specific vulnerability. A good ORM can be a fairly effective layer of safety.

When people talk of using "raw SQL", I (hope!) they generally mean using paramaterised queries, which mitigates against most injection attacks.

IMO it is a humongous red flag if someone on your team talks about "raw SQL" and does not mean parametrized queries. I guess I could say I was fairly fortunate in my education/early career but this is a lesson you have to learn early when working with user input and databases.

Re: To ORM or Not to ORM

#88

Post by RussianCow »

airstrike wrote: post #32
ordinaryperson wrote: post #3

Sample size one 1 but ORM drives me crazy. Why can't we just use SQL? How does it save time when I have to learn the ORM language, which probably has a lot less support and users? The OP here says it "reduces boilerplate" -- rarely have I created an application and thought its biggest problem was too much boilerplate. But everyone at work loves them so I must be wrong somehow.

I suppose it depends on your flavor of ORM. My experience using Django has been amazing from day one, and support for more exotic operations has improved by orders of magnitude since I last used it. I think a good ORM is one that strives to make 80-90% of operations ridiculously easy, leaving edge cases to the developer who can always drop down to a raw query as needed. Django does that fairly well, IMHO, and my limi…

> my limited experience with SQLAlchemy felt pretty underwhelming in comparison

That's interesting, because I usually hear of people having the exact opposite experience. The appeal of SQLAlchemy is that it lets you work at any level arbitrarily—you can use its high-level operations and abstractions for the 80-90% of common cases, but then drop down to its lower level and write complex queries where needed (including CTEs, window functions, stored procedures, etc), without ever resorting to writing SQL by hand. Django's ORM, in comparison, is very limited in the types of queries you can write with it, and is difficult and unintuitive to optimize.

Edit: That said, the Django ORM has improved by leaps and bounds over the past few years. These days, I'd say it gives you a way to cover about 90% of cases in an efficient manner (even if it has a bit of a learning curve). But if you're consistently doing more complex queries, I think SQLAlchemy is vastly superior.

Re: To ORM or Not to ORM

#89

Post by 34r45sdg »

megous wrote: post #61
chaostheory wrote: post #40

Earlier quoted context omitted.

Have you ever tried it? Your coworkers are right. It also helps you avoid common mistakes like SQL injection. When the ORM doesn't work for a specific case, they usually have ways where you can override the ORM and use your own SQL calls. Virtually every major, popular ORM does this across different languages (can personally confirm with Ruby, Java, & Python ORMs - confident that JS ones support it too).

SQL injection is prevented by not using user input as a part of the SQL query. It's orthogonal concern to whether to use ORM or not.

I disagree, and SQLi is not simply prevented by avoiding user input. There are many cases where you need to use user input for a SQL query and its a valid pattern, and can be done securely with Prepared Statements or Parameterization.

Using an ORM discourages you from writing SQL queries and it also automatically parameterized queries. This is a good thing! In fact, from experience, the single easiest way to mitigate a naive developer from introducing SQLi is requiring them to use an ORM. People should not be constructing SQL statements by hand today, its too easy to mess this up. SQLi shouldnt be a thing in 2019, but, it is.

My point is it is VERY MUCH not orthogonal, its very much related. Avoid naive SQLi, use an ORM. Directly related.

Re: To ORM or Not to ORM

#90

Post by 34r45sdg »

I am quite disappointed that both the article and the comments dont mention application security. USE AN ORM. Unless you are a hotshot SQL dev who knows the intricacies of every RDBMS, you should be delegating this to the ORM. Its there to prevent you from blowing your foot off by introducing SQLi vectors. Use an ORM, avoid SQLi.
Post reply on HN