Update a pre-populated sqlite database

[jakob-fankhauser]

Short: I tried to update the db file, delete the db file on the device and reopen it - however app (simulator) still uses the old db data.

When I make changes to the Database I want to update the Database in my App as well. So I copy the updated db file into the default www folders. Then on startup of the app I want to delete the existing (old) db file using:

SQLite.deleteDatabase({name: 'ChemoDatabase.db', location: 1}, () => {console.log('db deleted')}, error => {console.log('ERROR: ' + error);})

And reopen the updated db file inside the app with:

SQLite.openDatabase({name: 'ChemoDatabase.db', createFromLocation: 1});

However after that, the app still uses the outdated database which should be deleted with SQLite.deleteDatabase(). The only way I got the app to use the updated db file is by rebuilding the whole app...

[MacKenzieHnC]

EDIT: Oh, I misread your question. You'll probably have to fix the code yourself if it's broken. No one maintains this repo currently.

On android and windows at least, the db gets copied from the appbundle to a different location. Easy way is to uninstall and re-install the app. Hard way is to locate the db file and delete it.

On windows, it's something like C/Users/<your-name>/AppData/Local/Packages/<random-numbers>/LocalState.

No idea for android or iOS, but you could probably dig through the files here to see where it copies to. That's what I did for windows.

[cglacet]

@jakob-fankhauser did you found a solution to this issue?

[jakob-fankhauser]

@cglacet

I could not get it to work with SQLite.deleteDatabase(). What I did:

1. make changes to .db file
2. rename it
3. replace old .db file with new one in the project
4. make sure all .db related functions use new .db file
5. rebuild app

This procedure works for production as the new (renamed) .db file will be installed with the update to the user device. Only thing i am not sure is, if the old .db file is still persisted on user devices after an update. So i would recommend trying to delete the old .db file by locating it on the user device like @MacKenzieHnC suggested. However i did not continue trying to do that.

[cglacet]

@jakob-fankhauser Ok that makes sense. I'm not sure how the user device would know the old database should be removed so I guess we should delete it ourselves. I'll investigate some more, thanks for the update 👍

[cglacet]

I think the following should work, I havent checked it yet on production build but it works locally (doesn't seem to appear in Flipper's databases explorer anymore).

import {
    deleteDatabase,
    enablePromise,
    openDatabase,
} from 'react-native-sqlite-storage';

export function initialize() {
    enablePromise(true);
}

export const OLD_DATABASES_NAMES = ['things.db'];

export async function removeOldDatabases() {
    for (const dbName of OLD_DATABASES_NAMES) {
        try {
            await deleteDatabase({ name: dbName, location: 'default' });
            console.log(`OLD database ${dbName} deleted.`);
        } catch (error) {
            console.log(`ERROR while trying to delete ${dbName}:` + error);
        }
    }
}

const oldDbRemoval = removeOldDatabases();

export const connection = openDatabase({
    name: 'things_v2.db',
    readOnly: true,
    createFromLocation: 1,
    location: 'default',
});

export async function database() {
    try {
        await oldDbRemoval;
        return await connection;
    } catch (err: any) {
        if ('message' in err) {
            console.log(`Error while loading the SQLite DB: ${err.message}`);
        } else {
            console.log(`Unknown error while loading the SQLite DB: ${err}`);
        }
    }
}

The database call is something you would have in a "database provider" on the root level of your app. I added the await oldDbRemoval but I think it isn't really required as we just want the old databases to be removed, we don't care so much about when it happens. I mostly added this to make sure the code crash/doesn't crash as expected.

[MacKenzieHnC]

@jakob-fankhauser Ok that makes sense. I'm not sure how the user device would know the old database should be removed so I guess we should delete it ourselves. I'll investigate some more, thanks for the update 👍

There is a native solution where you could replace the delete function (or add a new one) that deleted the copy. I've only worked on the Windows side, but there's already code for locating the copy, so deleting it should be pretty easy. To be fair, Windows fs stuff is kind of goofy, so maybe it only works there.

I only glanced over your code, but if I'm understanding it, it seems like a bad idea to automatically delete the old db every time you wanna open it, and I don't think a javascript solution wouldn't locate it properly anyway.

What system are you testing on?

[cglacet]

@MacKenzieHnC I tested only on Android for now, also I only tested it locally. I don't know how to check wether it works in store builds.

[MacKenzieHnC]

@MacKenzieHnC I tested only on Android for now, also I only tested it locally. I don't know how to check wether it works in store builds.

Gotcha. I really do think a native solution will end up being necessary to make the delete function point to the correct place in all platforms. Again, I only really know Windows, but the fact that it copies to a weird location makes me think this won't work universally.

[cglacet]

@MacKenzieHnC I have no idea how this works, but it seems like the filename is stored somewhere in the app context. The deletion seems to be relying only on this context to locate the database. So I think this is native code.

I can't tell for iOS because it is a bit more complex. Maybe its not native code but I can't tell (I don't know the language and the location initialization code is handled locally).

[MacKenzieHnC]

@cglacet Yeah, the whole module is native. What I meant was that it seems like your solution was only javascript, and I'm skeptical that will work across all platforms.

As for Android, just like with Windows there seem to be some bizarre choices as far as copying files, and without comments, I can't really tell at a glance what the devs were thinking.

It does seem like you're right about how it gets the location, but then that doesn't explain why deletion isn't working. Is it just deleting the Assets version of the db?

Would you humor me and add '1' to the end of your db name when you try a deletion and see if that works?