3 ms·

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

by ufmace 9y ago

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.

yen223 9y ago
"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.
- jetti 9y ago
  "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.
  - ufmace 9y ago
    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.
    - jetti 9y ago
      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.
      - yen223 9y ago
        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.
    - SQLite 9y ago
      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.
- wolco 9y ago
  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.

by ufmace 9y ago

yen223 9y ago

"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.

jetti 9y ago
"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.
- ufmace 9y ago
  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.
  - jetti 9y ago
    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.
    - yen223 9y ago
      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.
  - SQLite 9y ago
    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.
wolco 9y ago
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.