And given that RDBMS’s tend to do have roughly similar performance profiles, except when they have radically different profiles (e.g. switching from row to columnar, or unique optimizations eg GIS in postgres), there’s not much incentive to be moving around — unless you hit the end of the envelope, at which point you’re probably looking at a re-design anyways if you’re already looking at re-architecting it.
And if you’re switching to something with a different performance profile, you’re going to be rewriting those queries anyways.
Personally I stopped considering databases as “standardized” or swappable — and using an ORM for hot swapping purposes I think might be as ridiculous as using a library for unifying web server frameworks (you can definitely do it… but your common denominator is fairly pathetic).
The only scenario I’ve seen where one changes DB with an explicit goal of minor code changes is migrating from some legacy DB on some ancient version to something more modern (especially today, targeting a DB the cloud can auto-manage), where your ORM DB flexibility won’t help you whatsoever
I've never seen it happen on a production system in ~20 years.
Might happen at the current gig but that's more down to "Firebase is junk for this job and we're rewriting everything anyway, might as well consider switching DB too".
E.G: you can use django, no matter if you are a mysql or a posgreq shop and still use the entire ecosystem of apps.
I moved them from Oracle to PostgreSQL because we would have had to start paying for Oracle licenses ourselves. It's a very good reason to change databases.
There was no ORM, so I just rewrote the queries to be portable and added some runtime branching in cases where that wasn't possible. PostgreSQL has an extension that adds some Oracle compatibility functions and views; that helped. The initial data migration went pretty nicely with ora2pg.
For the largest (and most important) application I had the writing side duplicate writes to both databases so I could run two instances in "production" and compare their behaviour.
It took a couple weeks of work and testing and then a couple months of observing that the new system works fine; the biggest pain was Oracle's '' = NULL thing and the lack of common SQL syntax for sequences; and the complete lack of tests, of course...
I’m really in favour of having tighter and clearer/cleaner integrations using technology specific features (eg PostgreSQL specific or gRPC specific) getting the most out of the tool than wasting potential for the eventuality that you might need to change it someday. As long as you stay open to the idea of having to change it all someday.
I have had multiple clients who have wanted to migrate from MySQL/MariaDB to Postgres, but afaik none have ever undertaken the Herculean effort to do so with the multiple years of slightly incorrect cruft that has accumulated in their old databases that have evolved over time.
Never happened with me. Only used something different (or even a newer major version) when writing a new tool or doing a whole rewrite of a current tool. Which kind of counts as a new one, right?
We (an insurance company in Europe) have around 200 DBs and half of them have been running for over 15 years. We need to migrate at least these to modern systems in the next 5 years. These new systems all come with their own ORM and RDBMS.
Most of the longer existing institutions have the same challenges. But even new companies are in a similar situation. The main problem with ORMs always arises when the business itself changes and/or new requirements (e.g. GDPR) come in.
Data is generally far more valuable than application code and often times your database will far outlive your application code (and sometimes even your choice of programming language).
Moreover if you're using DB-specific features rather than generic SQL, there's usually a strong business need driving it that would cause DB-specific coupling even in a supposedly DB-independent ORM.
I agree with this. At my current company, we have stored procedures that are over 30 years old still running. The original application code was VB6, then VB.NET, and now there are C# NET5 services. There’s also all kinds of other document stores, but that old SQL DB isn’t going away any time soon.
Some applications have regulatory requirements associated with them, especially retention, or otherwise serve critical needs areas.
Which is why the choice ends up between “keep a legacy system older than the average age in the IT industry” and “take it apart and start over”.
Basically if your app might last 20 years (this one went online when I was 4) it might be a consideration.
If you did use the DB specific features, you’re screwed regardless of an ORM.
NB: I work for a DB migration firm, specializing in legacy->modern — where our usual customer has thousands of SP’s that have to be migrated and an ORM would have saved you maybe 5% of the effort; so my experience is biased towards that level of difficulty.
I work on Mammoth which is a pur sang Postgres query builder, see https://github.com/Ff00ff/mammoth.
Granted, there's much more to learn than that, but so is the case with ORMs. I've had more issues writing good ORM queries than SQL queries.
https://blog.logrocket.com/why-you-should-avoid-orms-with-ex...
Personally, I love solving fancy puzzles with SQL but honestly prefer Rails' ActiveRecord ORM because it drastically cuts down on typing.
We do use query builders when we need to, usually the scenario is: a form with incremental filtering (multiple conditions), but not a full fledged ORM.
I would just note that these two statements are contradictory:
> The name pureORM reflects both that it is pure ORM (there is no query builder dimension)
and then
> Specifying all the columns is tedious; lets use BaseBo.getSQLSelectClause() to get them for free.
the "getSQLSelectClause()" is absolutely a query builder function. Building out the columns to select from is in fact where things get very complicated if you are for example using SQL aliases, selecting the entity from subqueries, etc. I would predict this method would have to be very complicated to truly be useful in such real world scenarios, so you'd end up with a "pure" ORM that still has a significant query builder, just one that has its own particular brand of awkwardness in that the textual SQL you write has to match up with the assumptions of getSQLSelectClause().
I spent a couple of months exhaustively documenting these relationships in the form of SQLAlchemy models, so that I could eventually write table_1.join(table_2) and have it do the right thing without having to remember (and implement!) that complexity everywhere those two tables touched. It was sanity saving, and for that I’m hugely grateful. Thank you.
If you’re starting with a brand new DB schema, shiny and pure, perhaps you can get away with hardcoding a bunch of SQL. I’ve rarely had that luxury, so query builders are one of my favorite things.
Or, if for some reason views couldn't be added (lack of access) a set of generic CTE includes?
When the company later committed to rewriting the VFP app in a sane language, they wrote it to run directly against PostgreSQL. That was quite a few years after I'd started there, though.
The thing is, the more you use an ORM, the less and less thought you put into designing your database to have a sensible interface of its own, so of course your database isn't going to have a sensible interface. Probably not as horrific as what you're describing, but as much as an ORM can solve that kind of problem, it can also perpetuate it. I'm almost terrified to ask how your DB schema got that fucked, but I'm going to guess it's because it's very tightly coupled to that giant legacy internal application and no one thought to take it seriously as its own service component with a consciously designed interface.
For example: https://stackoverflow.com/questions/65596920/use-django-subq...
Composite types & triggers, for some other examples, are only (at least they are!) available via third-party plugin/'installed app's too. You cannot defer a Django unique 'constraint', because it happens to implement it as a unique index instead.
That's just the few that come to mind.
However if you'd like to view your relational data gasp relationally, then the ORM is a giant anchor around your neck.
For example, I really enjoyed using rusqlite for a little side project/personal thing, but found apart from anything else just the repetition of `(?,?,?,?,?,?)` any time I wanted to insert something was annoying, for example. I started working a bit on [0] .. I don't know if I'd call it an ORM, just so I didn't have to do that, and could write `Model { ... }.insert(&conn)` instead. But still write plain SQL queries, I just want easy mapping between db table and language 'model' struct/class; column and field/attr.
You get much more elaborate the more time you invest in learning, and the skills gained will still be valuable in 25 years - as opposed to whatever subset of SQL the query builder du jour would give you.
I know SQL, 99% of the time, an ORM is more productive, for the rest, I can still write SQL manually. It's not an XOR.
One major advantage of vi is that it's always there (except on Windows :().
Writing raw SQL comes with all kinds of risks you can abstract away through nice ORM APIs, and with a good ORM API I find that it's much easier to clearly communicate intent in the code.
YMMV.
I much prefer being able to write a simple SQL query to deliver a piece of business functionality, especially if this is part of some configuration-time setup and not a code-time thing.
It's also 1000x easier to discuss the implications of a particular SQL query with other non-wizards. Emailing my project managers C# method snippets is just going to return more questions to my inbox.
>This contrasts against traditional ("stateful") ORMs which use query builders (rather than raw SQL) to return database-aware (rather than pure) objects.
>The name pureORM reflects both that it is pure ORM (there is no query builder dimension) as well as the purity of the mapped Objects.
I don't know what you mean by "pure" or "purity" here, and i think an explantion would help.
Also, in the code:
db.one(query)
Aside, Kotlin collection extensions might be my favorite thing I learned in the last two years.
>>> listOf(1, 2, 3).single { it > 2 }
res0: kotlin.Int = 3
>>> listOf(1, 2, 3).single { it > 1 }
java.lang.IllegalArgumentException: Collection contains more than one matching element.
>>> listOf(1, 2, 3).single { it > 100 }
java.util.NoSuchElementException: Collection contains no element matching the predicate.
>>> listOf(1, 2, 3).firstOrNull { it > 2 }
res0: kotlin.Int? = 3
>>> listOf(1, 2, 3).firstOrNull { it > 1 }
res1: kotlin.Int? = 2
>>> listOf(1, 2, 3).firstOrNull { it > 100 }
res2: kotlin.Int? = null
Somewhat counter intuitively, type hierarchies cause leaky abstraction layers.
I use javascript too, and at most I need a couple of HOF (higher order functions) to do most of the work I need.
Then again, I do use typescript and its type system, so maybe I am not the target audience.
Probably one of the best parts of Norm, and a big part of why I wrote it, is that it doesn't require you to have a hardcoded copy of the databases schema in your code, it just works like SQL.
I put quite a bit of effort into making bulk inserts efficient, as well as making sure rows could be streamed from the database while buffering as few as possible in memory on the client.
I still maintain and update for my own use. Feel free to make suggestions or request features.