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

91–100 of 300 posts

Re: To ORM or Not to ORM

#91

Post by revvx »

pbreit wrote: post #63
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.

Would something like Python "records" ever get traction? Just write SQL. https://github.com/kennethreitz/records

Dapper is similar and it got some traction in the .NET community.

https://github.com/StackExchange/Dapper

Re: To ORM or Not to ORM

#92

Post by mgkimsal »

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.

often they don't mean that - they really do just mean "run any sql you want". just worked on a project that explicitly wrote their own bolted on 'db layer' and avoided the built-in ORM which had parameterized query support. i now know what 1200 sql injection-capable queries look like...

Re: To ORM or Not to ORM

#93

Post by Daishiman »

jandrewrogers wrote: post #85

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…

Frankly that says more about the people you've worked with than the underlying technology.

I work with devs that have over a decade of Django experience. We use the ORM because it's just ridiculously easier to write queries on it and because SQL is impossible to compose without substantial problems.

90% of the code we write is CRUD and API endpoints. There's no reason to write SQL by hand except for the complex aggregations that comprise 5% of our queries, with luck.

Re: To ORM or Not to ORM

#94

Post by dliff »

I currently work on a project that is based on Django ORM. Before this, I almost exclusively hand-wrote all SQL.

I think you can quickly outgrow the limits of ORM... at least Django's. Whether it's needlessly fighting with ORM to get joins correct, ORM deciding it's going to loop through n records instead of joining on DB server, simply doing complex aggregates that ORM won't support, or doing DB-specific stuff. Postgres has some amazingly powerful features that many don't know about because they only learn ORM.

My experience: It can end up being double work because you spend an hour getting the query to work with ORM. Then, a week later, the requirements change and you have to add 1 thing for which it's just not feasible to use ORM. Then you're rewriting it completely with hand written SQL.

ORM seems really popular with web/mobile api now, and hand-in-hand a trend of not learning SQL. I think it's too bad as it creates another layer or separation to knowing how your app truly works. It's a layer that is still important to be familiar with, especially as a project grows.

Using custom SQL with Django has been a mixed bag for me. If you dump JSON from your query, some fields won't be consistently formatted with endpoints that still use the ORM. You can "load" the result into a Django model, but it can be difficult to make it work if you have custom fields you're returning from your custom SQL since Django doesn't know how to format them. (maybe there is a solution to this I haven't found?).

I like the approach of using a basic query-builder for routine insert/update and maybe even select.

Re: To ORM or Not to ORM

#95

Post by revvx »

ordu wrote: post #83
sdegutis wrote: post #60

Earlier quoted context omitted.

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 reliabl…

> The "write-raw-SQL-with-raw-strings" approach has one serious issue -- sql-injections.

Nobody has advocated writing "raw SQL with raw strings" in years.

The valid way of using Raw SQL is using prepared statements and parametrized queries.

This method will protect you from SQL injection, will handle most issues with type/conversions and the queries are cacheable, so it's fast too.

Parametrization is handled by the database itself (not the specific driver), so it is battle tested.

https://stackoverflow.com/questions/8263371/how-can-prepared...

Re: To ORM or Not to ORM

#96

Post by slaymaker1907 »

An ORM is a technology that has the problem of trying to make easy things easier (CRUD) while making difficult things more difficult (complex joins, SQL lock management, indices, etc.). Why spend so much $$$$$ on a fancy database if you are going to make 70-90% of its features impossible to use?

Re: To ORM or Not to ORM

#97

Post by bunderbunder »

I've come to a couple conclusions, over the years.

First, when you get down to it, the most-valued feature of ORMs is not the "writing queries in some language other than SQL" feature, it's the "not having to write a mess of mapping code" feature.

Second, the biggest drawbacks to ORMs all derive from the "writing queries in some language other than SQL" feature.

Fortunately, there are tools out there that solve the "mapping tables to objects" problem without trying to control all access to the database: So-called lightweight ORMs like Dapper and Sql2o. To me, these hit the sweet spot: They give you most the increase in productivity while steering clear of most the increase in complexity (and, by extension, decrease in maintainability).

Re: To ORM or Not to ORM

#98

Post by Johnny555 »

slaymaker1907 wrote: post #96

An ORM is a technology that has the problem of trying to make easy things easier (CRUD) while making difficult things more difficult (complex joins, SQL lock management, indices, etc.). Why spend so much $$$$$ on a fancy database if you are going to make 70-90% of its features impossible to use?

Why spend so much $$$$$ on a fancy database

You need to answer that question first. What feature do you really need from that fancy database?

Re: To ORM or Not to ORM

#100

Post by Risord »

rspeele wrote: post #49

I put a ton of work into an ORM that statically typed SQL a couple years ago. I always thought this would be a cool way to go. https://github.com/rspeele/Rezoom.SQL/ But I never got to use it at work, and thus lost interest. The biggest thing missing with it was that you lost type safety if you had to dynamically build a query. These days its main problem is lack of compatibility with .NET Core, which somebody else w…

Great work. I used rezoom in couple of minor projects and like most that I could really trust that query runs if it compiles. Unlike in Entity Framework where you have unlimited possiblities write code which compiles just fine but crash runtime. In principle I don't like an idea that your primary language get compiled to SQL due it's very leaky abstraction. Instead I like expressing queries as data (ideally compile time checked).

In rezoom query composition part wasn't the strongest part. When for example some model needed additional join you had to modify N queries. This kind of composition could be done of course with dynamics but like you say, you lose biggest selling point: compile-time safety.

However these compositions are usually all known at compile time so you should be able to just build them before rezoom checks them. But out of the box compile time programming in F# is not there yet...

Post reply on HN