React Native, SQLite, and Migrations

A simple example of React Native, SQLite, and migrations; nothing too special.

Motivation

Think the following issue says it all…

The questions is the following: How to deal with database structure changes that may happen in the future? I come from Ruby on Rails and there we have a migration thing, many files, one for each change in the database that runs in sequence. That makes sure we get a consistent database if we need to install the system again.

Is there anything like that for SQLite and React Native? How should I deal with possible database changes when an user updates my app? What are the steps to follow? Any material about this would be great.

— react-native-sqlite-storage — How to deal with migrations

Solution

The working example for this article is available for download.

We begin our exploration of the solution by examining the application’s entry React component. The primary purpose of this component is to initialize the application’s global variables, one being a reference to a SQLite client .

There are three possible outcomes from this initialization:

• After running any outstanding SQLite database migrations, the application starts normally; displaying Hello World
• If the version of the SQLite database exceeds the application’s version (more on these versions later), the application reports an error; displaying Downgrade error. This would happen if one attempted to downgrade the application without first uninstalling it.
• If the SQLite database is unavailable, the application reports an error; displaying Unexpected error. This should never really happen.

src/components/App.tsx

Let us now look into the initialize function. In this simplified example, the application is only working with the SQLite database using the react-native-sqlite-storage library. The only non-obvious feature is the value of DB_MIGRATIONS variable; the intention here is this is where one defines the array of migration scripts (creating tables, indices, etc). In this particular example, there is only one migration that actually does nothing.

src/util/global.ts

The heavy lifting in this application is done in SQLiteClient.ts; although the code is still fairly explanatory. The key here is that the database holds an integer version in the SQLite Pragma user_version and the application’s version is determined by the length of the migrations array. With these two versions on hand, the application can determine which migrations to run.

src/util/SQLiteClient.ts

Sidebar into Examining SQLite Database During Development (iOS Only)

In writing this article, I determined that we needed an easy way to inspect the SQLite database during development. Here is how one can do this; only provided for iOS development on the iOS simulator.

1. First install the DB Browser for SQLite application
2. Find the iOS simulator’s unique identifier; from simulator use menu Hardware > Device > Manage Devices…
3. Open terminal in the folder: ~/Library/Developer/CoreSimulator/Devices/[UNIQUE IDENTIFIER]
4. Run the command:

find . -name "Test.db" -print

5. To make it easier to find Test.db using DB Browser for SQLite, create a symbolic link from file on desktop to it, e.g.,

ln -s /Users/johntucker/Library/Developer/CoreSimulator/Devices/F2E515C8-E48B-469B-A33A-AF208F116AA5/data/Containers/Data/Application/D3A99315-6E01-42F8-9A8C-ECBA8516A163/Library/LocalDatabase/Test.db Test.db

Wrap Up

Hope you found this useful.