I'm somewhat new to NR and working with open-source projects in general. I've created an open-source project, which I am sharing via Github.
It has occurred to me that if someone downloads a specific version, and I make updates later on which they then download, the new flows might reference SQLite tables and/or columns that they don't have, as it was added subsequent to the version they are updating their flows from.
What would be the correct way to handle schema changes between flow versions for production users?
A bit of research suggests that normally in software development, one would use database versioning, and then have scripts for each upgrade path to the latest version. Would that be the correct way to do it using NR as well? If so, can someone give me some pointers on how to achieve this in NR?
What I would do is store a DB_Version number in the database. Then have a flow that runs at startup and is on the first Tab of the flows. The inject that kicks off that flow would have the current DB version number and would read the DB to get the existing DB number. If the current is greater than the existing then do the upgrade
rename the old table,
make a new table with the old name and with the correct column definitions,
copy the data over, and
drop the old table.
and then realized that you could run into problems is someone jumped from DB version 1 to DB version 14 because of all the missing columns that were added in DB versions 2-13. AND what happens when someone jumps multiple DB releases and tthe new code uses a column that is empty - your main flow will have to be ready to handle that.
So you have a couple choices,
force the user to upgrade one version at a time
create a seperate 'update' flow that handles upgrades from version N to version N+?
if you don't have to migrate the data, just drop the old table andcreate a new one.
The first step is to adopt good semver version control of course. A schema change would be a breaking change and therefore you would change the major version number. If managed via npm, the users would have to make a consious choice to upgrade.
Second step would be to include, as Paul says, an upgrade script to change existing dbs to the new schema - if this is possible. To deal with a multi-version update, you would need your script to handle that and, again as Paul says, one thing that might help would be to have a special table that contains the schema version.
It might actually be easier (in some cases) to move/rename the original table, create a new one with the new schema and then have an import script.
Of course, the best answer of all would be to design the schema correctly in the first instance so that you don't have to change it later
