Question: Migration after database model update

[plentylife]

Hi Scott,

I'm bothering you again :)

This is an experience of every project out there. You make a database model. Then you change it.
In the case of nano-sql and indexeddb, it creates a new database.

The question is, how do we get the old data into the new database?

I found this doc, https://docs.nanosql.io/adapters/transfer, but it doesn't exactly answer my question.

Best wishes,
Anton

PS. For me this is a real problem, but can be simplified to this: how do I extract one table from the old database and put it in the new database, if the table model is exactly the same?

[only-cliches]

Hello Anton,

No worries, the best thing to do in this case is just set an ID in your database config:

nSQL()
.config({
    id: "my_db"
})...

If you don't provide an ID one is generated from a hash of the data model, which is why you're getting a new database when the datamodel changes.

However, if you set an ID to the database it'll always attach to the same backend database (IndxedDB or whatever you're using) regardless of data model changes.

Hope that helps!

[plentylife]

Aha! It does. Thank you. This solves my problems.

So then, I can track the db version in a table, and have migrator functions that trigger based on that value.

But I won't be able to use two different models at the same time...

If I try to select old data from the database, using the new model, and the data types are incompatible, will nano-sql throw an error?

[only-cliches]

If I try to select old data from the database, using the new model, and the data types are incompatible, will nano-sql throw an error?

No, data sanitization happens only on upsert quries, not select queries. Meaning you could change the data model from anything to anything else and as long as the primary key column has remained the same everything would work just fine.

The major exception to this is the secondary indexes. If you add/remove secondary indexes to the data model you'll need to rebuild them after the data model change like this:

nSQL().extend("rebuild_idx", "tableToRebuild").then...

[plentylife]

This is awesome.

This should be enough of a guide for anyone trying to migrate their data.

Once I get around migrations in my own code, and try them out on my own skin, could I add to the nano-sql docs?

[aral]

@plentylife Did you ever get around to adding this to the docs? It would be very valuable as I couldn’t currently find any information on migrations whatsoever. Also, it would be a great feature to have migration/versioning support baked in (e.g., like in Dexie – which exposes version() and upgrade() methods – http://dexie.org/docs/Tutorial/Understanding-the-basics#migrate-data.)

[plentylife]

Hi @aral,

No, I never did add it to the docs. I never got around writing the code to migrate data yet.
So I haven't battle tested my own project with it yet.

As far as I understand, it's up to us to write our own migration code (for now), which shouldn't be that much of a problem. I mean realistically speaking, there's always migration code that needs to be written by hand.

The way I was going to go about it is:

• Keep the version info in the database id
• Make a function that checks db id, and triggers on it
• Create a new datbase
• That function would run queries on copying data from the old to the new database
• Done