Ask HN: Are ORMs overkill for using SQL databases?

#1

Post by samblr » Fri, Aug 04, 2017, 7:12 PM UTC

SQL is something fundamental we learn with programming. The syntax is mostly standard.

Why trade this 'CONSTANT' with myriad variations of ORMs across languages/frameworks for using same flavour of SQL.

I feel ORMs are overkill. For simple queries ORMs are ok but with complex queries - there is extra effort spent on translations/debugging/optimisations etc.

- -

Having said that - ORM can be helpful in one-off cases like below:

- if-and-when database migration happens from one type of database to other (eg: postgres->mariadb etc)

What is your take on this ?

edit: rephrased sentence

Re: Ask HN: Are ORMs overkill for using SQL databases?

#2

Post by andreasgonewild » Fri, Aug 04, 2017, 7:37 PM UTC

I would go one step further and claim that SQL-databases are overkill for most purposes. Relational logic is very useful, as is transacted storage and indexing; but the SQL-standard is clearly overkill in many cases.

An ORM is a simplification; it offers a single, convenient, but ultimately limiting perspective with most complexity swept under the rug. Anyone who's actually tried migrating between different databases or spent a week or so chasing down nasty coherency/performance-problems in their chosen framework knows what I'm talking about.

A more constructive approach that's rarely taken is providing storage/indexing as first class abstractions within the language to build your storage logic on top of.

Embedding raw SQL in a piece of software makes it much harder to reason about and maintain. Since storage/indexing are often fundamental, they deserve a more sophisticated interface than simply passing written notes back and forth on cocktail napkins.

Lately, I've been implementing my own, special purpose storage/indexing (https://github.com/andreas-gone-wild/snackis/tree/master/src...) to cut down on the complexity and get a tighter fit with the application.

Re: Ask HN: Are ORMs overkill for using SQL databases?

#3

Post by saluki » Fri, Aug 04, 2017, 7:39 PM UTC

I think the biggest plus for using an ORM is increased security over rolling your own interactions with the database.

Plus the time savings/overall security using a framework that is typically tied to the ORM.

So I would recommend using a framework and ORM.

If you're just getting started and are working on a web app for your own use I would recommend building at least one from scratch. Rolling your own Auth, etc. Once you do that you'll understand more of what appears as 'magic' in a framework like Rails/Laravel.

Re: Ask HN: Are ORMs overkill for using SQL databases?

#4

Post by shortoncash » Fri, Aug 04, 2017, 7:39 PM UTC

I like that you asked this question and I'll probably refresh this page a few times to read the responses.

I use an ORM called odb by CodeSynthesis. It is one of my favorite ORMs and an extremely high quality product. This ORM has saved me in more ways than one, but the biggest benefit is exactly what you've described.

I can't speak to other ORMs, but odb has such a nice system that it makes C++ development involving databases tolerable. Prior to this, managing queries, transactions, and keeping on top of problems related to interactive with specific databases was a real nuisance. There are enough quirks and differences between databases that I feel like it's too easy to understate what a pain the differences between variants of SQL can actually become in a large project.

Re: Ask HN: Are ORMs overkill for using SQL databases?

#5

Post by xor_null » Fri, Aug 04, 2017, 8:00 PM UTC

I like to use orm for simple projects. If there are some complicated queries or other heavy stuff I use views and stored procedures to make a suitable abstraction layer. Then I use the orm to access those views and stored procedure. Just to be clear, there is no business logic in those stored procedures.

Most orms generate awful sql queries, and then everybody wonder about bad performance (...and then they switch to nosql..because performance...).

Re: Ask HN: Are ORMs overkill for using SQL databases?

#6

Post by jrs95 » Fri, Aug 04, 2017, 8:08 PM UTC

Sometimes ORMs can be very convenient for reducing time to complete complex things as well. I recently did a project which had a graph data model that was mapped to a relational database, and SQLAlchemy saved me a lot of manual query manipulation to get that done. Many ORMs would make it rather difficult to achieve that sort of thing, though. Some are much more robust than others.

Re: Ask HN: Are ORMs overkill for using SQL databases?

#7

Post by ufmace » Fri, Aug 04, 2017, 8:16 PM UTC

I'd go with a very useful It Depends.

ORMs are great for smoothing over some of the rough spots between SQL and OOP. It's kind of a drag to manually hard-code all of the cascade behavior for your whole class hierarchy. Hand-rolling your own caching isn't so great either. Most languages have a lot of boilerplate around setting up SQL queries and working with the results, particularly if you want to convert the rows into objects.

Flip side, some of them have a ton of their own boilerplate and confusing setup, and can generate strange errors and poor behavior. I still haven't figured out how to use Entity Framework well, despite working with C# for years. They're good enough for like 90% of things, but the other 10%, you can get big savings writing your own SQL.

Nowadays, I think Ruby ActiveRecord is my favorite. It handles everything that's reasonable for an ORM to do without a ton of complex setup, supports some more complex stuff easily, and makes it easy to drop to SQL when you need to.

FWIW, I've never actually seen any company try to switch to a different type of database.

Re: Ask HN: Are ORMs overkill for using SQL databases?

#8

Post by yen223 » Fri, Aug 04, 2017, 8:47 PM UTC

ufmace wrote: post #7

I'd go with a very useful It Depends. ORMs are great for smoothing over some of the rough spots between SQL and OOP. It's kind of a drag to manually hard-code all of the cascade behavior for your whole class hierarchy. Hand-rolling your own caching isn't so great either. Most languages have a lot of boilerplate around setting up SQL queries and working with the results, particularly if you want to convert the rows in…

"FWIW, I've never actually seen any company try to switch to a different type of database."

The active record pattern, where you have objects like RoR's ActiveRecord or Django's Model that directly wrap a row in a table in a database, has a nasty hidden trap that rears its head once your app is complex enough.

By tightly coupling your database backend to your application models, it all but guarantees you can't switch to another database without a major rewrite of your application.

This is probably why you don't see companies switching databases. They can't.

Re: Ask HN: Are ORMs overkill for using SQL databases?

#9

Post by jetti » Fri, Aug 04, 2017, 8:58 PM UTC

yen223 wrote: post #8

ufmace wrote: post #7

I'd go with a very useful It Depends. ORMs are great for smoothing over some of the rough spots between SQL and OOP. It's kind of a drag to manually hard-code all of the cascade behavior for your whole class hierarchy. Hand-rolling your own caching isn't so great either. Most languages have a lot of boilerplate around setting up SQL queries and working with the results, particularly if you want to convert the rows in…

"FWIW, I've never actually seen any company try to switch to a different type of database." The active record pattern, where you have objects like RoR's ActiveRecord or Django's Model that directly wrap a row in a table in a database, has a nasty hidden trap that rears its head once your app is complex enough. By tightly coupling your database backend to your application models, it all but guarantees you can't switch…

"This is probably why you don't see companies switching databases. They can't."

I think we don't see companies switching databases because it would be a pain to support the transition. There are implementation specific features that could be used that would break on a switch. Then migrating all of the data and stored procs/functions/views/etc would be a pain too as they would need to be updated for the syntax of the new database. Simple things like TOP in T-SQL do not exist in other database implementations, so that is a simple thing that would have to be fixed. Other things like CROSS APPLY in T-SQL do not (to the best of my limited knowledge) exist in other RDMS, so that would be a more complicated re-write.

Re: Ask HN: Are ORMs overkill for using SQL databases?

#10

Post by ufmace » Fri, Aug 04, 2017, 9:20 PM UTC

jetti wrote: post #9

yen223 wrote: post #8

Earlier quoted context omitted.

"FWIW, I've never actually seen any company try to switch to a different type of database." The active record pattern, where you have objects like RoR's ActiveRecord or Django's Model that directly wrap a row in a table in a database, has a nasty hidden trap that rears its head once your app is complex enough. By tightly coupling your database backend to your application models, it all but guarantees you can't switch…

"This is probably why you don't see companies switching databases. They can't." I think we don't see companies switching databases because it would be a pain to support the transition. There are implementation specific features that could be used that would break on a switch. Then migrating all of the data and stored procs/functions/views/etc would be a pain too as they would need to be updated for the syntax of the…

TOP actually exists in all other RDBMSes that I know of (MySQL, PostgreSQL, SQLite), except it's called LIMIT and goes at the end of the query instead of the beginning. CROSS APPLY is in PostgreSQL as well, except it's CROSS JOIN LATERAL instead. AFAICT MySQL and SQLite don't support this. I don't have much experience with Oracle, so I'm not sure what it supports, but I'd be amazed if it didn't support some variant of LIMIT/TOP.

LIMIT/TOP is a good example actually of the sort of thing that every ORM I've used is great at covering between databases. I haven't seen any ORMs that support queries with sophisticated JOINs, much less CROSS APPLY/CROSS JOIN LATERAL.

But I would agree that, if you're using the more complex stuff like custom functions, stored procs, triggers, etc, you're much more likely to get tripped up by that switching between databases, though ORMs mostly don't touch that kind of stuff.