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

Ask HN: Are ORMs overkill for using SQL databases?

news.ycombinator.com

Post reply on HN
Print view View original

11–18 of 18 posts

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

#11

Post by jetti »

ufmace wrote: post #10
jetti wrote: post #9

Earlier quoted context omitted.

"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 o…

Yea I knew of limit which is why I knew you would have to change the query. It is a simple change but a change nonetheless. I didn't know that PostgreSQL also had a CROSS JOIN.

Anyways, my point was that it wouldn't be ORM support or tied to ActiveRecord that would prevent a business from switching databases but a whole lot of other work that would need to go on.

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

#12

Post by SQLite »

ufmace wrote: post #10
jetti wrote: post #9

Earlier quoted context omitted.

"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 o…

I'd never heard of "CROSS JOIN LATERAL" before. So I went and read the PostgreSQL documentation. If I understand correctly what LATERAL does, it is not needed by SQLite. The SQLite query planner will figure out on its own that you are doing a LATERAL join and do the right thing.

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

#14

Post by BjoernKW »

For the most part I prefer to use technologies like JdbcTemplate that only provide a very thin layer between the relational and the object-oriented world.

ORM systems usually try to paper over the impedance mismatch between these two worlds. This mismatch will always be there. There's no way around it. So, we might as well keep the systems dealing with it as simple as possible and not try to force object-orientation on relations and vice versa.

Document-oriented database systems are good alternative in many cases, too because by their very nature they're much more amenable to storing objects.

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

#15

Post by yen223 »

jetti wrote: post #11
ufmace wrote: post #10

Earlier quoted context omitted.

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

Yea I knew of limit which is why I knew you would have to change the query. It is a simple change but a change nonetheless. I didn't know that PostgreSQL also had a CROSS JOIN. Anyways, my point was that it wouldn't be ORM support or tied to ActiveRecord that would prevent a business from switching databases but a whole lot of other work that would need to go on.

Even if the company were treating their database as nothing more than a dumb data store, without stored procedures or the like, tight coupling within application code would still stifle any ability to integrate other data sources.

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

#16

Post by ufmace »

SQLite wrote: post #12
ufmace wrote: post #10

Earlier quoted context omitted.

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

I'd never heard of "CROSS JOIN LATERAL" before. So I went and read the PostgreSQL documentation. If I understand correctly what LATERAL does, it is not needed by SQLite. The SQLite query planner will figure out on its own that you are doing a LATERAL join and do the right thing.

Well that's interesting. I checked around a bit and tried a few things. Seems that SQLite doesn't support LATERAL joins, but you can get the same effect by putting the subquery in the JOIN condition[0]. I'm not sure if they're exactly the same and you can do all of the same things, but it worked for the quick use-case I came up with:

    select a.username, p2.*
    from accounts a
    join posts p2 on p2.id in (select p1.id from posts p1 where p1.account_id = a.id order by p1.score desc limit 2);
[0] https://dba.stackexchange.com/a/100850/

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

#17

Post by SarahBishop »

ORMs are good for only one thing, saving your models to the database and retrieving your models from the database for display on a single page.

Once you start using them for other things like generating lists of objects, pagination then you start hitting issues. In fact here you are better off using straight SQL and PDO to return an array of data that you format into HTML for display. https://19216811wiki.wordpress.com/

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

#18

Post by wolco »

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…

Switching databases is extremely easy and that is one of the advantages of using a orm like laravel's eloquent. Using multiple types of databases in one application or changing databases is simple.
Post reply on HN