Hacker News Reader

Comment by tomrod | Hacker News Reader

I mean, good luck when you are later porting to another DB system.

How often does one even migrate DB’s? I’ve always seen the advice, but other than an explicit goal of supporting SQLite + Postgres simultaneously, I’ve never actually ending up needing to move.

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

> How often does one even migrate DB’s?

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

Lucky! I've done a few.

I've seen it happen once. Back in 2011, the company I was working for decided to switch all of their apps from MySQL to postgres. We had built everything in Django, so the transition was a lot less painful than it could have been, but it definitely wasn't free.

Not the point. The point is that people can then build db agnostic libs on top, so that several projects from different teams can use it despite using a different db.

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.

> How often does one even migrate DB’s?

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.

> How often does one even migrate DB’s?

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?

In my experience you seldom switch databases. Sometimes you need (to start) supporting multiple.

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.

More often than not, you aren't porting. You are making the system work with more than one kind of database. When my first company was acquired and was built on PostgreSQL (which was designed to be hosted) the acquirer wanted an on-premise version that also supported Oracle. Using an ORM made that a relatively simple process with only a few annoying differences between the two databases. Specifically, treatment of strings (oracle empty string is a null string), booleans (no boolean type in oracle) and DDL (oracle doesn't have transactional DDL).

That makes me think, is there a tool or document about the intersections of features provided by DBs? For example, if you're building for Postgres and MySQL, is there a way to only use features supported by the two? Same for MySQL and SQLite, or Postgres and SQL Server?

My anecdata says once in two decades. I migrated a few simple Perl webapps some time ago, the oldest being 20+ years old or something? Not completely sure when it was created.

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 mean hell, I've seen so many libraries need upgrading every few years. Two decades is a fucking steal in terms of man hours.

> How often does one even migrate DB’s?

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.

If anything, you're more likely to keep the database and migrate whatever application is using it.

Over time I've come to see DB-independence as a benefit that is seldom realized and therefore not a strong point in favor of a given system.

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.

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

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.

Done it once..in 20+ years accompanied by an entire re-write, so not often enough to care about this at all.

In my experience this is an overrated concern. And when it does happen, it's usually accompanied by a rewrite anyway.

Mid and low tail risk applications, certainly.

Some applications have regulatory requirements associated with them, especially retention, or otherwise serve critical needs areas.

My experience includes government, consumer unicorns, and new vehicle development in aerospace. I'm familiar with highly regulated and exponential-growth settings.

Me too! And my experience notes that it is exactly-rated, versus under-rated.

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

How big of a concern is this? Nowhere I’ve ever worked has been concerned with the ability to swap the database for a completely different one, in the same way they are not concerned with the ability to swap whatever programming language is being used for a different one.

I worked for an organization that used Oracle for relatively trivial applications. The difficulty of switching to MySQL and then later to Postgres convinced them to use an ORM going forward.

Basically if your app might last 20 years (this one went online when I was 4) it might be a consideration.

IME if you didn’t use the DB -specific features, migration is painful, but not that much so.

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.

TSQL and PSQL ideas of a stored procedure are not really the same.

If your SQL is mostly bound columns or some sort of ORM only then you can get lucky and just play the data type matching game. But most of the ones I have ever had to port was playing the 'rewrite these 300 stored procs again' game. They usually ended up in some sort of stored procedure for one of a few reasons 'they only knew how to do that', performance, that is what the senior guy wants.

In a type-safe environment I think you should just be able to switch your pur sang SQL builder to another dialect. Because of the type-safety you’ll be able to find incompatibilities at compile time which makes the migration easy enough (ignoring data migration). This avoids creating a weird ad hoc SQL dialect trying to fit all the others in a single API.

I work on Mammoth which is a pur sang Postgres query builder, see https://github.com/Ff00ff/mammoth.

RDBMs have proved to have far more staying power than random ORMs for various fashion languages.

Reply on news.ycombinator.com