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

101–110 of 300 posts

Re: To ORM or Not to ORM

#101

Post by philwelch »

The ORM discussion reminds me of something Rich Hickey said in his talk, “Simple Made Easy”. He distinguished between “simple” (when a system is inherently low in complexity) and “easy” (when a system is made more complex so it is theoretically easier to use).

ORM’s are easy, but not simple. If your system uses a relational database but not an ORM, you have to understand your particular database and also SQL to understand your data layer. If you add an ORM, you aren’t actually saved from having to understand those things, you just also have to understand your ORM on top of all that. There is some positive tradeoff you get in return, since you don’t necessarily have to grapple with the added complexity all of the time.

The main saving grace of ORM (or at least query builders) seems to be that the alternatives aren’t well-supported in tooling, so the “raw SQL” alternatives often end up implemented as string concatenation hell, which is admittedly terrible. Installing parameterized SQL on the DB itself in stored procedures works great, but you have to go out of your way to do it; it never really comes across as a plug-and-play option even though in principle it easily could be.

Re: To ORM or Not to ORM

#102

Post by out_of_protocol »

For everyone complaining about orm-something - take a look at elixir's Ecto. It basically allows writing sql via native code. Good for composability, type casting, stuff like that.

    Account 
    |> where(active: true)
    |> join(:left, [a], p in Post, on: p.account_id == a.id)
    |> where([a, p], a.foo == "bar" or p.bar == "foo")
    |> group_by([a, p], a.id)
    |> select(...)
    |> limit(10)
    ...
    |> Repo.all()
Plus there are changesets for data validations (can e.g. catch and return uniquness errors via unique index)

P.S. For update/insert queries it doesn't make sense to write UPDATE statement by hand

   UPDATE accounts SET ... 50 fields ... WHERE id = 1
P.P.S. changeset example:

    def changeset(record, attrs) do
      record
      |> cast(attrs, [:number, :note])
      |> validate_required([:number])
      |> validate_format(:number, number_format())
      |> unique_constraint(:number, name: "foo_bar_index")
    end
will return nice error if number is missing (_before_ running sql query) or already taken (_after_ running a query, on DB error)

Re: To ORM or Not to ORM

#103

Post by ordu »

revvx wrote: post #95
ordu wrote: post #83

Earlier quoted context omitted.

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

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

It is an overstatement. Every time I look into some random PHP code I see there raw SQL with raw strings. Maybe it is just me being "lucky"?

By the way, the thread starter comment was mentioned it, I got phrase from it.

Re: To ORM or Not to ORM

#104

Post by he0001 »

I think the most common problem with ORMs is that people doesn’t know when to use the ORMs functionality or when to write their own query. This always leads to N+1 problems and unmaintainable code, because when you eventually finds those problems it’s usually quick fixed because there’s no time for fixing all of it. It leads to ad hoc code for the next guy trying to change. This is generally not the ORMs fault, but always the programmer and that they don’t understand those limitations. Just replacing the same code with “raw” sql would produce the same thing but you need to write it all instead.

Re: To ORM or Not to ORM

#105

Post by bpyne »

Daishiman wrote: post #93
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 aggre…

"and because SQL is impossible to compose without substantial problems."

Would you mind providing an example of what you mean?

Re: To ORM or Not to ORM

#106

Post by dillonmckay »

paddy_m wrote: post #81
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.

What are you using to version/migrate views?

Re: To ORM or Not to ORM

#107

Post by philwelch »

jweir wrote: post #77
wvenable wrote: post #42

Earlier quoted context omitted.

The real third approach is that you can safely pretend the database is object-oriented for manipulation and simple lists and still use SQL for complex queries. Most ORMs let you safely mix and match both methods easily. This ORM or not ORM is the wrong question. Use an ORM to save you headaches where it's appropriate and use direct SQL when it's not.

You are absolutely correct. Using both is a very valid options. We use ActiveRecord a lot, and then have custom SQL queries using `find_by_sql` for very complex, optimized joins. It works very well. Rails gets out of the way when we need it to.

Two caveats with find_by_sql: it’s read-only, so no insert or update commands, and it still does column-to-instance-variable monkeypatching on the object level, as opposed to the class-level monkeypatching that’s applied to normal ActiveRecord classes as soon as the DB schema is read.

Re: To ORM or Not to ORM

#108

Post by philwelch »

ordu wrote: post #103
revvx wrote: post #95

Earlier quoted context omitted.

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

> Nobody has advocated writing "raw SQL with raw strings" in years. It is an overstatement. Every time I look into some random PHP code I see there raw SQL with raw strings. Maybe it is just me being "lucky"? By the way, the thread starter comment was mentioned it, I got phrase from it.

“Random PHP code” might be the operative phrase there.

Re: To ORM or Not to ORM

#109

Post by dillonmckay »

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.

The last two projects I inherited were both using raw SQL with parameterized queries, different languages/frameworks.

Re: To ORM or Not to ORM

#110

Post by beders »

Just say no to ORM. Apart from the usual problems with impedance mismatch, there's one thing that is rarely talked about: Waste.

Most ORMs work like that: You get a request, you start a transaction, you begin instantiating lots of objects because you need those to do anything useful.

You then either serialize parts of that object tree into JSON or whatever, or you change a few objects, which then create UPSERT statements, the transaction commits (or not) and then...you are throwing it all away again!

You might save some state in some second level cache, but other than that, you just created a partially populated object graph, probably loaded way too much stuff, and then you do that AGAIN for the next transaction.

I've used JDO, Hibernate etc. before and nowadays it feels like extremely wasteful with limited productivity gains that quickly disappear if your objects become more complex.

Post reply on HN