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.
Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.
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.
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.
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
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.
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0