How to implement migrations with SQL databases

[davidszanto]

Hi,

Is there a recommended way of implementing migration? I use sequelizer, which is capable of generating migration scripts but is there a way to integrate is with moleculer?

[devalexandre]

wouldn't it be safer to use migrations with sequelize-cli?
You can also run the script after after the service, but I don't recommend it.

[drone12]

Hi @davidszanto ,

For sequelize migrations you have 2 options, running them programmatically or during CICD pipeline. To run them programmatically you should use umzug library.

We added a mixin called dbExtensions.js and inside we have a migrate() method with the following code:

const sequelize = this.adapter.db;
const umzug = new Umzug({
migrations: {
// indicates the folder containing the migration .js files
path: path.join(__dirname, "../db/migrations/" + this.name),
// inject sequelize's QueryInterface in the migrations
params: [sequelize.getQueryInterface()],
},
storage: "sequelize",
storageOptions: {
sequelize: sequelize,
},
});
await umzug.up();
console.log("Migrated " + this.name);
return true;

and then you add the mixin to service that you want to migrate and the following code:

async afterConnected() {
await this.migrate();
},

Keep in mind that there are some inter-version issues so I am giving you the version of umzug that we currently use:

"umzug": "2.3.0",

This approach carries with it various potential problems, one of them being not able to do canary deployments or run multiple service versions at the same time. That is a whole other can of worms that you need to investigate. If you approach this carefully it will work fine in production environment (we have a daily, hourly backup PLUS before update backup).

For CICD migrations it depends of your pipeline and CICD strategy and thus is way out of scope of github comment.