[Debate] SqLite db versions and migrations, how is it intended to be?

#1

Hi, thinking in future, how is the update process when the user already has a db in it’s app? as far as i can tell the db get’s deleted when i install from usb as debugging mode, that’s undesired behavior for me, i would like my users to hold all data already inside the db in ionic 2 app.

Could someone on the Ionic staff (@brandyshea @mhartington) tell if that’s intended behavior or just unexpected bug?

If unexpected, when it get fixed how would we provide migration strategies for the versions of our app when updating?

#2

Hi,

The update process will not delete the SQLite DB nor the data.
If you want to alter the structure of the database, you can run a script on startup to update it.
You could have a Table to keep track of your DB version.

#3

Well that’s weird, each time i do ionic run android it will install the app and in start i ask for some values in the kv table and since it doesn’t find the data from before the new install, my app logic declares that the user is new and will delete all data (which i suspect was erased when installing the new version) and so the app would be resetted.

Currently i have an schema ts file that exports the schema of the DB, then in startup i cycle through the schema creating each table if not exists, how would you set up the script to have a base DB and just apply the necessary changes?

Maybe my app’s DB get’s deleted because i’m updating it with usb cable?

#4

Yes indeed, it seems like ionic run android is completly overwriting the existing DB.

Instead of doing that, could you try to build the new app with ionic build android, transfer it to your phone, and manually install it (make sure it has a higher version number in your config.xml), and check if it still delete everything.

#5

I haven’t been bumping up the version, maybe is that what’s causing the DB override? i tried installing with adb but it says that it already exists, i guess the ionic run android command is calling adb install -r <apkfile.apk> with the -r flag that replace the app thus replacing the DB as well?

How would i pass the apk to phone without creating a server with url exposed and serving the apk? i think adb has a push function but not sure how it works.

#6

Yes, I think the adb install is replacing everything.

What I would do is:

  • First time, execute ionic run android
  • Then bump the version in config.xml
  • execute ionic build android
  • copy/paste the .apk in your phone storage via USB (e.g in the Downloads folder).
  • Then go to that folder and try to install the app, it should prompt you if you want to overwrite the existing app.
#7

Just to chime in here

Yes, installing the app via usb/adb will replace everything, including the DB.
But as the user updates the app via the app store, this won’t happen.

#8

It seems the ios platform doesn’t do that, xcode overrides the code but not the data, am i right?

#9

I believe so. One of the little nuances of how ios builds.

#10

Soooo @woopy how do you spect to improve the database? for example i have a script that maps the schema object and creates the table if not exists, but that’s all, if i need to create a new field in the db i have to delete the database, i would like the thing to be like Rails migrations that get run if hasn’t yet been applied to the db, i guess that’s the db version you mention earlier?.

#11

On Android I’ve found that transferring the app to the phone using AirDroid Web then installing it does not overwrite the database. Not quite as efficient as transferring using ADB, but at least you can develop without having to build data into your db with each update.

#12

If you need to update/add something in your database, you have to run a SQL script after opening the connection to your DB.

in your SQL script, you put all your transactions, (UPDATE, CREATE, INSERT…).

You can add an extra table in your DB to save what’s the schema version and the last time the DB has been updated. so you don’t have to run the SQL everytime the app is opened.

#13

Just as a note, I’m seeing the same behavior in Ionic 1. @mhartington I assume updating via the play store will work the same as in Ionic 2?

#14

@woopy why not just save the db version in the kv table? it is created by default by ionic SqLite service, so we could just store it in that table like:

this.db.set('db_version', '0.1.0')
#15

I think the replace of database is the way the underlying Cordova works, i’m not sure but i think that when it installs in device through USB cable it uses the adb command with replace flag -r on, so it replaces it as so, up in the thread @SDP1966 said it wouldn’t be replaced if you build the apk, copy the file to the device memory and open it from there, the db will not be replaced, this mimics the way installation works when downloading the apk and when installing from Play Store.

#16

This entry has some good overall ideas on how to do database versioning. Ignore he fact that it’s java, the high level idea seems sound.

This approach splits each version upgrade into its own file. I can see this being implemented using the sql porter plugin.