SQLite Migration

Expo SDK
1

Hi There,
We are using SQLite in our RN Project. The app is in Production. We have made some changes to the Table. I mean added two columns in the Table. How to migrate the DB on next App version update or is there any way in which Expo will automatically migrate the data in the DB after App update?

Thanks in Advance.

1 Like
2

There’s nothing that would automatically migrate your database. I’m guessing the first version of your app starts off by creating a new table if it doesn’t exist. If your update simply has those additional columns in the create table statement, the columns would not be added for anyone who updated the app, because the table can only be created once.

If you’re only ever adding schema, you could skate by without a real migration system. In your case, you could have code in your app when it starts up to a) create the original table (from version 1) if it doesn’t exist, and b) add the new columns for version 2 if they don’t exist. That would cover both new installs and upgrades.

However, this could become unmanageable after several updates, so a real SQL migration system would be ideal.

In a quick google search, I didn’t see a migration tool for RN Sqlite/ Expo SQLite. It wouldn’t be difficult to write one (and it’d be great to have one available as an open source library- you could make it!). It basically works by:

First time app runs on version 1 (or first time it runs with the migrator enabled):

  1. creating a VERSION table if it doesn’t exist.
  2. doing any schema creates/ updates associated with version 1.
  3. writing an entry in the VERSION table indicating that version 1 has been migrated.

Next version (with a schema update)

  1. Check VERSION table, see that version 1 has been migrated, but version 2 hasn’t.
  2. Do schema updates for version 2
  3. write an entry to VERSION table indicating that version 2 has been migrated.

What’s great is that this works even if you miss an update. If there’s three schema versions and a user is going directly from 1 to 3, the migrator should work in a loop to see that first version 2 needs to be applied, then version 3.

I’ve used Persistence.js for webSQL and Cordova before and it has a migrator. Another option perhaps instead of writing a migrator entirely is to build an adapter for Persistence.js for Expo’s SQLite implementation.

4 Likes
3

This topic was automatically closed 15 days after the last reply. New replies are no longer allowed.