Database migrations in Cordova.
Let's say you're developing a mobile application using Apache Cordova. Your app has a local Sqlite3 database on the device and you use the Cordova-sqlite-storage plugin to fetch and store data into it. You're planning to ship the first version and it is going to be used by quite a few non-negligible number of users. You expect you will release several other versions of this app in the near future. Some of these next versions might need to make changes to the database schema, too. So how should you manage these database migrations? Here is how I tackled this problem.
In the Java ecosystem, there are tools and libraries that can help you automate this process: Flyway, MyBatis Migrations, Liquibase, etc. For Cordova, the only relevant things I found out there are a blogpost about migrating WebSQL databases (a dead standard, remotely relevant) and persistencejs, a mapper library which provides a plugin to support migrations and which can be used with Cordova. Seeing as I don't trust ORMs that much, I wanted to implement my own solution.
Before diving into code, note that in order to avoid nested callbacks (aka callback hell), I decided to use ngCordova's sqlite plugin wrapper which returns a promise for each API method, allowing me to neatly chain the SQL operations. This also means that we'll have to use AngularJS.
Migration process overview
Each new version of the app will introduce a set of changes to the database (creating new tables, adding new columns to existing tables, etc) as a collection of DDL statements associated with a version number.
var version1 = {
versionNumber: 1,
queries: [
"CREATE TABLE IF NOT EXISTS person(id INTEGER PRIMARY KEY NOT NULL, firstname VARCHAR(100), lastname VARCHAR(100))",
"CREATE TABLE IF NOT EXISTS pet(id INTEGER PRIMARY KEY NOT NULL, name VARCHAR(100))"
]
};
var version2 = {
versionNumber: 2,
queries: [
"ALTER TABLE person ADD address VARCHAR(100)",
"ALTER TABLE pet ADD ownerId INTEGER"
]
};
We will also need a migration history table which will keep a record of all the migration steps that were already applied to the database of the device. We'll wrap this in a function that will return a promise resolving to the version number 0 after executing successfully.
var createVersionHistoryTable = function() {
var query = "CREATE TABLE IF NOT EXISTS version_history(versionNumber INTEGER PRIMARY KEY NOT NULL, migratedAt DATE)";
var promise = $cordovaSQLite.execute(db, query, [])
.then(function() {
var versionNumber = 0;
return versionNumber;
});
return promise;
};
Before running the migration, the app will need to find out what schema version is currently installed on the device, so that it can skip the steps that have been already applied.
var selectCurrentVersion = function() {
var query = "SELECT MAX(versionNumber) AS maxVersion FROM version_history";
var promise = $cordovaSQLite.execute(db, query)
.then(function(res) {
var maxVersion = res.rows.item(0).maxVersion;
return maxVersion;
});
return promise;
};
These two functions are the initial steps that need to be performed everytime a new app is installed. (Actually, running them every time the app starts seems to be a good enough compromise.) The easiest way to go about this would be:
createVersionHistoryTable().then(selectCurrentVersion)
.then(function(maxVersion) {
console.log("The current version is: " + maxVersion);
})
But what if there were 10 initial steps to be performed, instead of two? Would I write 10 consecutive thens? I could, but I think there's a more elegant way to chain promises using Array.reduce. You'll later see how this benefits us.
var initialSteps = [
createVersionHistoryTable,
selectCurrentVersion
];
initialSteps.reduce(function(previous, current) {
return previous.then(current);
}, $q.when())
.then(function(maxVersion) {
console.log("The current version is: " + maxVersion);
})
The above snippet is equivalent to the one above it. If I had 10 initial steps, I would just add them to the array. $q is just Angular's implementation of promises/deferred objects. $q.when() stands for the initial value of the reduce operation. It's basically a noop for promises.
In a similar way, we will need to be able to execute in chain an array of queries. Each version will have a different array of queries.
var executeInChain = function(queries) {
var promise = queries.reduce(function(previous, query) {
return previous.then(function() {
return $cordovaSQLite.execute(db, query, [])
});
}, $q.when());
return promise;
};
After running a migration step, we need to store into the version history table the version number associated with the queries array that we just ran.
var storeVersionInHistoryTable = function(versionNumber) {
var query = "INSERT INTO version_history (versionNumber, migratedAt) VALUES (?, ?)";
var promise = $cordovaSQLite.execute(db, query, [versionNumber, new Date()])
.then(function(res) {
console.log("Stored version in history table: " + versionNumber);
return versionNumber;
});
return promise;
};
For each version object, we need to check the current version number, execute the queries if the version was not applied yet, store the version number in the history table and return a promise that can be chained together with the rest of the other versions.
var versions = [
version1,
version2
];
var migrationSteps = versions.map(function(version) {
return function(currentVersion) {
if (currentVersion >= version.versionNumber)
return $q.when(currentVersion);
var promise = executeInChain(version.queries).then(function() {
console.log("Version "+version.versionNumber+" migration executed");
return version.versionNumber;
})
.then(storeVersionInHistoryTable);
return promise;
};
});
An important thing to note here is that we pass the current version number from one promise to the next, to determine if a certain step needs to be applied or not.
Let's put it all together, including the initial steps related to the history table:
var steps = initialSteps.concat(migrationSteps);
steps.reduce(function(previous, current) {
return previous.then(current);
}, $q.when())
.then(function() {
console.log("All migrations executed");
})
.catch(function(error) {
console.error(JSON.stringify(error));
});
Now, when we'll need a new version, all that needs to be done is to declare it, and add it to the array. Everything else should work outside the box.
var version3 = {
versionNumber: 3,
queries: [
// whatever
]
};
var versions = [
version1,
version2,
version3 // new version, yay!
];
Conclusions
You can see a working example in its entirety in my demos GitHub repo. Note that it uses the Ionic framework, although it's not a must. On the other hand, AngularJS is required because of the ngCordova dependency.
With a little bit more work, I think this could be converted into a separate library. The end user of this library would only need to declare the versions array and run a migrate() API function when the device is ready. In the end, it would be similar to what persistencejs is offering in its migration plugin, without the ORM stuff.
One downside of all this is that rollback is not supported. If one of the DDL statements defined in any migration step has a syntax error, the database might be left in a state that needs manual intervention. So be sure to double check the correctness of your queries!