Re:Ask HN: Are ORMs overkill for using SQL databases?
by jetti on Friday August 04, 2017 @05:36PM
(#14932509)
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?
by yen223 on Friday August 04, 2017 @10:33PM
(#14933914)
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?
by SQLite on Friday August 04, 2017 @06:02PM
(#14932719)
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?
by ufmace on Saturday August 05, 2017 @12:34AM
(#14934248)
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);
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.