3

I'm curious, when I first heard of the term database migration, it was unequivocally pertaining to moving entire databases from one platform to another.

However, recently I had to build a server that required setting up a database myself. Then I chanced up this python library called Alembic. Apparently it is called a "database migration" tool as well when it's sole purpose, to speak out of turn, is a git of sorts for database. This is personally confusing for me. Could some one shed light on the difference between the database migration I understand and database migration as a tool for tracking changes to the database.

Again I might be ignorant and possibly missing out on some of these obvious differences but would really appreciate it if someone could enlighten me.

krenova
  • 33
  • 3
  • 1
    You are right to be confused. The term migration is used for for different things. – ypercubeᵀᴹ Apr 28 '22 at 07:11
  • @ypercubeᵀᴹ thanks for your reseponse. in short, the terms are what they are with respect to the context that they are used and there is no nuance tying the 2 together except that they are assigned the same name? – krenova Apr 28 '22 at 07:31
  • 1
    @krenova - exactly. A DB admin talking about migrations probably has your meaning in mind. On the other hand, I, as a developer, will almost always be talking about migrating schema from one state to another. – Davor Apr 28 '22 at 14:53
  • 1
    I can add that tools like https://flywaydb.org/ and https://docs.liquibase.com/ use the term migrate for schema evolvement. – Lennart - Slava Ukraini Apr 28 '22 at 15:01

4 Answers4

3

Personally, I understand database migration the same way as you. However, I am not sure whether there is a single formal definition. Possibly like many other computing jargon, every one has their own definition.

In Wikipedia (since everyone can edit articles, this shows how the same term is used differently by different people):

  • The Schema migration page describes the activity performed by Alembic. It claims this also called also "database migration". Ruby on Rails simply calls this activity "migrations"
  • The "Database migration" subsection of Data migration page describes it as "to move from one database vendor to another, or to upgrade the version of database software being used. ", so it matches our understanding.
Fat P
  • 286
  • 1
  • 4
  • 3
    I agree. I would even put moving "from one vendor to another" and "upgrade versions of database software" as separate sections – ypercubeᵀᴹ Apr 28 '22 at 07:49
  • 1
    I would also elaborate on what the definition of a schema migration is. I understand OP's confusion, and I would clarify for him that it's not even necessarily like Git. It doesn't have to revision control the history, rather the main goal is to facilitate the change from one version of a schema to a new version of it, typically done with the assistance of a migration tool. – J.D. Apr 28 '22 at 11:33
  • 1
    @J.D. thanks for this point. I'm from a data science background and my immediate impression is to relate it to something akin to git. Your point about facilitating change from different versions of a schema is the kind of nuances I was looking out for. – krenova Apr 29 '22 at 03:57
2

The word migration can mean "movement from one part of something to another". What you describe (see the answer from Fat P) is a schema migration. Speaking from personal experience in the field, these are the more common types of migrations:

data migration: move the data to another system, usually from one database software to another

database or host migration: move the database to another host, but usually on the same database software, possibly on a higher version

schema migration: change the schema of the database

Flourid
  • 511
  • 2
  • 7
1

Not mentioned (yet) when talking about a 'migration' between different database products:

  • (some level of) training of personnel in order to facilitate ...
  • conversion of database-level SQL code (eg, procs, triggers, packages)
  • conversion of application-level SQL code (eg, queries, connection profiles)
  • conversion of DBA-level operations (eg, monitoring, maintenance, backup/recovery, replication/duplication of data); a good bit of this also includes some application-level changes (eg, DBA scripting environment)

'course the term 'conversion' implies understanding differences in database architecture, DDL, SQL, etc.

markp-fuso
  • 2,569
  • 1
  • 8
  • 19
1

While the two sets of tools are generally distinct, I think it can be argued that they both mean the same thing by "migration".

For application code, we generally talk about "deployment" or "release": the new version replaces the old version, usually in one go. We might also talk about patching, but that's still effectively replacing code.

For databases, we don't generally delete the old database and create a new one. Sometimes, we are simply adding new functionality alongside the existing one, but often, we need to transform the schema in some way. This transformation is what is meant by "migration" in both of these contexts:

  • When moving from one database engine to another, we generally have to extract the data from the old database, transform it in some way, and then insert it into the new database.
  • When changing a live database from the schema required by version N of an application to the schema required by version N+1, we might have to do something similar: take the contents of old table X, populate new table Y, and drop table X.

The two types of "migration" are supported by different specialist tools, but the concept is the same: you give the tool an initial schema and a desired schema, and some hints about how they relate; and the tool generates the necessary SQL and DDL commands to "migrate" your live data from one schema to the other.

As you've observed, one major difference is that tools for managing the schema needed by your application often also include or integrate versioning functionality. That's because in this context you're likely to make frequent small migrations, and need to manage their deployment to different environments at different times. Tools focussed on migrating between wildly differing database engines don't need to focus as much on this feature, but may instead have features like continuous data replication, where you run the two databases in parallel.

IMSoP
  • 397
  • 1
  • 7