Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Migrating DB #70

Closed
ditiem opened this issue Jul 15, 2018 · 9 comments
Closed

Migrating DB #70

ditiem opened this issue Jul 15, 2018 · 9 comments

Comments

@ditiem
Copy link

@ditiem ditiem commented Jul 15, 2018

Hello,

I am facing issues when the DB model is updated (example I add a new field). I think this can be easily fixed by adding a "version" option in the config call:

nSQL()
.models[...]
.config({
    id: "myDB" // name of my database
    version: "1.0.1" 
})

and a new migrate method. Examples of use:
1- nSQL( 'table' ).migrate( (old_version, new_version) => { ... update this table and return a promise ... } )
2- nSQL( ).migrate( (old_version, new_version) => { ... here you can update the whole database ... } )

There could be another option for migrate (I am commenting it for documentation purposes) that it is more declarative:

.migrateTo( "1.0.1", ( from_version ) => ... ) 
.migrateTo( "1.0.2", ( from_version should be "1.0.1" ) => ... )

It allows to descentralice migration code (I think it is not a good idea, as you may need to do the update in certain order), and requires que execute migrations in order (from 1.0.0 to 1.0.1, once finished, from 1.0.1 to 1.0.2...), which looks "cool" but you can shoot are your feet some times - imaging a huge table is removed in 1.0.3, you do huge updates from .1 to .2, to simply be destroyed in .3

So in summary, if a new "version" is detected, nSQL could simply call the migrate method and wait till all of them are completed before returning in the connect method.

I do not know if there are any plans for this.

@sebastianmacias
Copy link

@sebastianmacias sebastianmacias commented Jul 15, 2018

For reference this is how dexie handles migrations http://dexie.org/docs/Dexie/Dexie.version(). The upgrade method to modify your existing data during a migration is very helpful.

@ditiem
Copy link
Author

@ditiem ditiem commented Jul 16, 2018

Thank you @sebastianmacias for the reference.

I see some cavities, for instance how do you migrate from version 1 to 3? As I only read the example there is probably a way to do it, but it is not clear to me.

@sebastianmacias
Copy link

@sebastianmacias sebastianmacias commented Jul 17, 2018

It runs all migrations. 1,2,3

@only-cliches
Copy link
Owner

@only-cliches only-cliches commented Jul 20, 2018

Just implemented this feature in 1.71, which should be live on NPM in the next few hours.

There are two new config options, version and onVersionUpdate.

The onVersionUpdate is a function that receives the current version as it's argument and returns a promise. The promise must return the version that it's been migrated to.

nSQL()
.config({
    version: 8,
    onVersionUpdate: (currentV: number) => {
        return new Promise((res, rej) => {
            switch (currentV) {
                case 1:
                    // migrate from 1 to 2
                    res(2);
                case 2:
                    // migrate from 2 to 3
                    res(3);
                break;
                case 3:
                    // migrate from 3 to 8
                    res(8);
                break;
                default:
                   rej("What kinda version is this?");
            }
        });
    }
});

The onVersionUpdate will be called until the promise returned matches the version provided in the config.

Hope that's everything you were after!

@ditiem
Copy link
Author

@ditiem ditiem commented Jul 20, 2018

Thank a lot Scott!

@s-devaney
Copy link

@s-devaney s-devaney commented Oct 14, 2018

This is the feature which convinced me to adopt Nano for my next Cordova app. Thanks!

@Ruuudi
Copy link

@Ruuudi Ruuudi commented Nov 4, 2019

can someone please give me an example of how to rename or delete a column from the existing data within the onVersionUpdate function? Thank you in advance.

@only-cliches
Copy link
Owner

@only-cliches only-cliches commented Nov 5, 2019

The conform rows query is used when you want to modify the existing rows to match the current data model.

So the migration code would look something like this: (assuming you're using nanoSQL v2 here)

Let's say we want to move from this data model:

// version 1 data model
{
    "id:uuid": {pk:true},
    "name:string": {},
    "email:string": {},
    "age:int": {}
}

to this one:

// version 2 data model
{
    "id:uuid": {pk:true},
    "name:string": {},
    "user_email:string": {},
}

Always put the current data model in the createDatabase call and increment the version number every time you change the data model.

NanoSQL will save the version number each time it loads up the database, and if it finds that the version number has changed since the last time it was loaded the migration code is ran.

nSQL().createDatabase({
    id: "my-db",
    version: 2,
    tables: [
        {
            name: "users",
            model: {
                "id:uuid": {pk: true},
                "name:string": {},
                "user_email:string": {}
            }
        }
    ],
    onVersionUpdate: (version) => {
        return new Promise((res, rej) => {
            switch (version) {
                case 1: // loading original model
                    nSQL("users").query("conform rows", (oldRow) => {
                        // conform rows will automatically remove columns that no longer exist
                        // just need to provide mapping from old column values to new ones (if there are any)
                        return {
                            ...oldRow,
                            email: oldRow.user_email
                        }
                    }).exec().then(() => {
                        res(2);  // migration complete
                    }).catch(() => {
                        // handle migration error
                    });
                    break;
                default:
                    rej("Unknown version!");
            }
        });
    }
})

@Ruuudi
Copy link

@Ruuudi Ruuudi commented Nov 5, 2019

thank you very much for the quick feedback and the illustrative example!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
5 participants