But I'm really surprised every time people tell me they look at the schema as defined into the ORM instead of at the table in the database.
I'm really jaw dropped the few times I know somebody doesn't even know SQL, only the ORM. Maybe they look at it as if it were the reaction of somebody that thinks you must know assembly if you want to program Ruby, Python or Node (I don't.) Still, if you work with a database you must know it's internal language, SQL or NoSQL. Your going to need it or build a mess.
And involving a DBA early in the project can make your database at least twice as fast, with the right schema and the right queries. Then you translate that into the ORM you want to use.
I expect that everybody with a degree knows SQL and I'm realizing that I could be wrong. Maybe sometimes I'm the only one in the room that knows it. I'll check it next time I'm at a technical event leaning on the backend side.
Very happy to not have and ORM on our stack for many years.
I believe that the interaction with the database is just about the most important part of code that you need to rely on. We have had messy data written to the database due to misused or misconfigured ORM (perfomance issues, bad orphan handling, session state problems, overflown sequences, to list a few) and decided that we should not rely on all devs touching that code to be experts in the ORM api to avoid the pitfalls, we rather our devs be expert in SQL.
Never had any of them complain about mapping using something like jOOQ. Type checking and composability are also built in.
Although, I think what you plan to do with the data also makes a big difference. Which probably explains why opinions vary so widely.
For short-lived processes, like typical web applications and command line utilities – that load data from the database, do something with it, and then purge it from memory again – I'm becoming less and less convinced that ORMs are actually a benefit.
On the other hand, if your application plans to map the database data to memory for long periods of time, with the need to keep them in sync, then you're probably going to end up writing something that resembles an ORM anyway, and poorly at that. In this case a good ORM is beneficial.
For 3: the proper abstraction for relational data is, surprise, a relation.
I used to work in an environment where we had relations as full first class data structures. They are very pleasant to work with.
We actually had 'relation-object-mappers', ie when we had to interact with other systems that didn't use relations, we often mapped them to relations internally to make them play nicer.
How were those relations represented? If I understand correctly, you were not just wrapping tables in a database, but using some other backing implementation. What were the most common operations, and what was the performance like?
Oh, the implementation was fairly straight-forward. I think just sorted arrays are something.
It wasn't about speed of execution, but expressiveness when coding. Later on they even added proper type system support.
Common operations were things like map/project, extend, filter, join, collect-by-key / expand, etc.
Just as Codd pointed out in his original papers, relations allow you to not have to make a choice about a hierarchy for your data.
Using key-value store like a hash-table in your program, or the much vaunted has-a relationship between objects would force you to make these choices. Thus making interacting with the data awkward for all but one access pattern.
Relations work best when your program is written in a style that deals largely in immutable data. (What we call 'purely functional', but people in dysfunctional languages have also picked up on the advantages recently.)
> Just as Codd pointed out in his original papers, relations allow you to not have to make a choice about a hierarchy for your data.
Or, alternatively, make it really painful when you do actually need to query hierarchies, along the lines of "give me all the tuples above this one in the hierarchy".
so, i think #3 is less useful for switching out the actual DB server itself in an ongoing project, but i’ve found it immensely useful in a couple of ways:
- replaced the DB driver mid way through a project to a slower, but more complete implementation. this went flawlessly, because it’s all so generic and in the end the same DB under the hood, so a simple change (unless you don’t use an ORM where i could see it being a nightmare)
- started a new project where i had to use MSSQL, which I’d never used before, but i’m a big fan of postgres/sqlalchemy. other than ODBC oddities, it was really simple to write the new app with all the same patterns i was used to with things like update on write, lazy joins, constraint deferral, and i think most importantly would be MIGRATIONS! huge help to have the same migration framework that i was used to
> As a theoretical abstraction above the data-store
I worked on a project with a home-grown ORM (in C; it was horrible) that abstracted over both MySQL and Postgres ... except the overarching application required Postgres-specific column types and functions.
ORMs of various stripes were in common use well before 2004 so 1 & 2 don’t sound terribly persuasive. 3 is definitely an evergreen selling point - you might change to a different RDBMS vendor (back when there was such a thing), etc.
1. As a reaction to common SQL injection from poor libraries not implementing parameterized queries. (2004 or so)
2. Novice engineers not wanting to learn SQL (look I learned how to make a blog in RoR, and I like mongo!)
3. As a theoretical abstraction above the data-store (as though you might someday be able to switch the data-store beneath the ORM)
1 has been solved, 2 was never okay (point of the article), and 3 isn't really okay either because it's too leaky (performance specifics).