database - Sequelize.js: how to use migrations and sync

Question

I'm close to having my project ready to launch. I have big plans for after launch and the database structure is going to change -- new columns in existing tables as well as new tables, and new associations to existing and new models.

I haven't touched migrations in Sequelize yet, since I've only had testing data which I don't mind wiping out every time the database changes.

To that end, at present I'm running sync force: true when my app starts up, if I have changed the model definitions. This deletes all the tables and makes them from scratch. I could omit the force option to have it only create new tables. But if existing ones have changed this is not useful.

So once I add in migrations how do things work? Obviously I don't want existing tables (with data in them) to be wiped out, so sync force: true is out of the question. On other apps I've helped develop (Laravel and other frameworks) as part of the app's deployment procedure we run the migrate command to run any pending migrations. But in these apps the very first migration has a skeleton database, with the database in the state where it was some time early in development -- the first alpha release or whatever. So even an instance of the app late to the party can get up to speed in one go, by running all migrations in sequence.

How do I generate such a "first migration" in Sequelize? If I don't have one, a new instance of the app some way down the line will either have no skeleton database to run the migrations on, or it will run sync at the start and will make the database in the new state with all the new tables etc, but then when it tries to run the migrations they won't make sense, since they were written with the original database and each successive iteration in mind.

My thought process: at every stage, the initial database plus each migration in sequence should equal (plus or minus data) the database generated when sync force: true is run. This is because the model descriptions in the code describe the database structure. So maybe if there is no migration table we just run sync and mark all the migrations as done, even though they weren't run. Is this what I need to do (how?), or is Sequelize supposed to do this itself, or am I barking up the wrong tree? And if I'm in the right area, surely there should be a nice way to auto generate most of a migration, given the old models (by commit hash? or even could each migration be tied to a commit? I concede I am thinking in a non-portable git-centric universe) and the new models. It can diff the structure and generate the commands needed to transform the database from old to new, and back, and then the developer can go in and make any necessary tweaks (deleting/transitioning particular data etc).

When I run the sequelize binary with the --init command it gives me an empty migrations directory. When I then run sequelize --migrate it makes me a SequelizeMeta table with nothing in it, no other tables. Obviously not, because that binary doesn't know how to bootstrap my app and load the models.

I must be missing something.

TLDR: how do I set up my app and its migrations so various instances of the live app can be brought up to date, as well as a brand new app with no legacy starting database?

Answer 1

Generating the "first migration"

In your case, the most reliable way is to do it almost manually. I would suggest to use sequelize-cli tool. The syntax is rather plain:

sequelize init
...
sequelize model:create --name User --attributes first_name:string,last_name:string,bio:text

This will create both model AND migration. Then, manually merge your existing models with generated with sequelize-cli, and do the same with migrations. After doing this, wipe database (if possible), and run

sequelize db:migrate

This will create schema will migrations. You should do this only once to switch to proper process of schema developments (without sync:force, but with authoritative migrations).

Later, when you need to change schema:

1. Create a migration: sequelize migration:create
2. Write up and down functions in your migration file
3. According to your changes in migration file, change your model manually
4. Run sequelize db:migrate

Running migrations on production

Obviously you can't ssh to production server and run migrations by hands. Use umzug, framework agnostic migration tool for Node.JS to perform pending migrations before app starts.

You can get a list of pending/not yet executed migrations like this:

umzug.pending().then(function (migrations) {
  // "migrations" will be an Array with the names of
  // pending migrations.
}); 

Then execute migrations (inside callback). The execute method is a general purpose function that runs for every specified migrations the respective function:

umzug.execute({
  migrations: ['some-id', 'some-other-id'],
  method: 'up'
}).then(function (migrations) {
  // "migrations" will be an Array of all executed/reverted migrations.
});

And my suggestion is to do it before app starts and tries to serve routes every time. Something like this:

umzug.pending().then(function(migrations) {
    // "migrations" will be an Array with the names of
    // pending migrations.
    umzug.execute({
        migrations: migrations,
        method: 'up'
    }).then(function(migrations) {
        // "migrations" will be an Array of all executed/reverted migrations.
        // start the server
        app.listen(3000);
        // do your stuff
    });
});

I can't try this right now, but at first look it should work.

UPD Apr. 2016

After a year, still useful, so sharing my current tips. For now, I'm installing sequelize-cli package as required live dependancy, and then modify NPM startup scripts in package.json like this:

...
"scripts": {
  "dev": "grunt && sequelize db:migrate && sequelize db:seed:all && node bin/www",
  "start": "sequelize db:migrate && sequelize db:seed:all && node bin/www"
},
...

The only thing I need to do on production server is npm start. This command will run all migrations, apply all seeders and start app server. No need to call umzug manually.