The Problem: I need to update users to my new database schema in React native
When you work with SQLite in React Native you will inevitably make updates to the database structure you did originally. You have added/removed tables, or fields, or updated field data types. The question is now what can you do to update existing users of your app to your new schema without wiping out their database data.
Before we set things up, it's important to understand some general things:
- We should not provide a new database, or an updated database, as this will cause the users existing database to be discarded along with all its data.
- We should connect to the user's existing database, and apply update scripts to it.
- The app should understand that the user may not have updated the app in a while, and could be behind a few versions of the schema. This means your db upgrade code must know exactly what upgrade scripts are relevant to the user's database version and apply only those.
- The app should understand that some database upgrades assume that the last upgrade is in place. This means your upgrade scripts must be applied in sequence from oldest to most recent.
With those items in mind, let's get started!
The Solution
Note: I am using react-native-sqlite-storage in these examples, but it doesn't matter what you use. The concepts are all the same.
Also, I will be writing under the assumption that you already have a database set up in your project, and you are looking for a way to upgrade it. That being the case, I am sticking with something more along the lines of pseudo-code that you should modify for your own purposes. I think the overall logic/idea here is what I want to get across more than anything else.
Let's assume we have a local database of version = 1, and we want to upgrade it with a set of scripts up to the latest version -- version 5.
Logic Overview
Our general logic is below:
- Load up a database upgrade configuration file
- Open the existing database
- Get the database's current version
- Get the config's target upgrade version
- If the version is lower than your app's target upgrade version, run upgrade scripts from the version right after the old database version, up to the target upgrade version
The Configuration File
This file will be in charge of storing the database version you'd like your app to upgrade to. It will also hold the upgrade scripts to run for each version of the app. Here is an example:
{
"version": 5,
"upgrades": {
"to_v2": [
[
"CREATE TABLE [IF NOT EXISTS] table_name1 column_1 data_type PRIMARY KEY, column_2 data_type NOT NULL, column_3 data_type DEFAULT 0;"
]
],
"to_v3": [
[
"CREATE TABLE [IF NOT EXISTS] table_name2 column_1 data_type PRIMARY KEY, column_2 data_type NOT NULL, column_3 data_type DEFAULT 0;"
]
],
"to_v4": [
[
"CREATE TABLE [IF NOT EXISTS] table_name3 column_1 data_type PRIMARY KEY, column_2 data_type NOT NULL, column_3 data_type DEFAULT 0;"
]
],
"to_v5": [
[
"CREATE TABLE [IF NOT EXISTS] table_name4 column_1 data_type PRIMARY KEY, column_2 data_type NOT NULL, column_3 data_type DEFAULT 0;"
]
]
}
}
What does the config file do?
The example above indicates your new intended version of the db is version 5, and the logic here will be that the scripts in the upgrades field will run from the version of the app's db up to the script that updates your db to version 5.
Folder structure
Let's assume we will work off a file called database.js located at /app/db. Let's place the config file we created earlier in /app/db as well and name it db-upgrade.json, so we will just work within the same folder.
Load up the config file
In database.js import the json file:
Open the Existing Database
We are assuming of course a database was already created in your own project and you'd like to upgrade it. As I said, I will be using react-native-sqlite-storage, but any library that allows you to use sqlite will work.
import dbUpgrade from "./db-upgrade.json";
import SQLite from "react-native-sqlite-storage";
export const open = () => {
const db;
SQLite.openDatabase({
name: "my-existing-data.db",
createFromLocation: "~data/my-existing-data.db",
})
.then((instance) => (db = instance))
.catch((error) => console.error(error));
};
Get the database's current version
Let's add a query that will give us the database version:
import dbUpgrade from "./db-upgrade.json";
import SQLite from "react-native-sqlite-storage";
export const open = () => {
const db;
SQLite.openDatabase({
name: "my-existing-data.db",
createFromLocation: "~data/my-existing-data.db",
})
.then((instance) => {
db = instance;
db.executeSql("SELECT max(version) FROM version")
.then((results) => {
let version = results[0];
if (version < dbUpgrade.version) {
//Call upgrade scripts
}
})
.catch((error) => console.error(error));
})
.catch((error) => console.error(error));
};
Since for this example, we assumed the local db has a version of 1, the query SELECT max(version) FROM version in this case would give us a value of 1
Upgrade function
Let's create the function that will be in charge of the logic of running our SQLite scripts, based on the database's previous version:
export const upgradeFrom = (db, previousVersion) => {
let statements = [];
let version = dbUpgrade.version - (dbUpgrade.version - previousVersion) + 1;
let length = Object.keys(dbUpgrade.upgrades).length;
for (let i = 0; i < length; i += 1) {
let upgrade = dbUpgrade.upgrades[`to_v${version}`];
if (upgrade) {
statements = [...statements, ...upgrade];
} else {
break;
}
version++;
}
statements = [
...statements,
...[["REPLACE into version (version) VALUES (?);", [dbUpgrade.version]]],
];
return db
.sqlBatch(statements)
.then(() => console.log("Success!"))
.catch((error) => console.log("Error:", error));
};
Upgrade function logic
Let's breakdown the logic of the upgrade function. First thing we do is we determine the version
let version = dbUpgrade.version - (dbUpgrade.version - previousVersion) + 1;
We get the version stored in our db-upgrade.json file (dbUpgrade.version) and subtract previousVersion from that. previousVersion is the version we retrieved from our local database (the outdated database you want to upgrade):
dbUpgrade.version - previousVersion
This would give us 4, since db-upgrade.json is set to 5, and our local db returns 1. This number tells us how far behind the database is. It is 4 versions behind. Before we can begin to run scripts, we need to know where do we start from. We don't want to run scripts the database already has ran. Hence, the next part of the expression:
dbUpgrade.version - (dbUpgrade.version - previousVersion)
Here we again use the db-upgrade.json version (5) to establish where we want to end, and we subtract by 4, to establish where our local db's version is (1). If we ran the scripts now, we would have a problem, because like I said, we don't want to run the scripts our current database already has. We shouldn't start running at version 1, but at version 2. Therefore we:
dbUpgrade.version - (dbUpgrade.version - previousVersion) + 1
As you can see, we added 1 at the end. Now we will start at the right script :)
Our next block of codes runs our scripts. We basically loop through each script, and use our version value to access the right script, we store the script in the statements variable, and we increment the version number so that on the next iteration we can get the next script. Once we see we have no more scripts left to upgrade, we break the loop.
...
let length = Object.keys(dbUpgrade.upgrades).length;
for (let i = 0; i < length; i += 1) {
let upgrade = dbUpgrade.upgrades[`to_v${version}`];
if (upgrade) {
statements = [...statements, ...upgrade];
} else {
break; //we found no more scripts, break
}
version++; //increment version from 2, to the end (5)
}
We then add a db version upgrade script, to make sure our new scripts will set the local database version to 5. And at the same time we add the scripts we extracted from db-upgrade.json using the spread operator ...statements:
statements = [...statements, ...[['REPLACE into version (version) VALUES (?);', [dbUpgrade.version]]]];
Finishing up
Now that we understand how the upgrade function works, let's use it in our code. Here's the entire code with the added call:
import dbUpgrade from "./db-upgrade.json";
import SQLite from "react-native-sqlite-storage";
export const open = () => {
const db;
SQLite.openDatabase({
name: "my-existing-data.db",
createFromLocation: "~data/my-existing-data.db",
})
.then((instance) => {
db = instance;
db.executeSql("SELECT max(version) FROM version")
.then((results) => {
let version = results[0];
if (version < dbUpgrade.version) {
upgradeFrom(db, version);
}
})
.catch((error) => console.error(error));
})
.catch((error) => console.error(error));
};
Your app database is now ready to be upgraded. Happy coding 🤓
