Sequelize.js: how to use migrations and sync

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?

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.

Just learning this myself, but I think I would recommend using migrations now so you get used to them. I've found the best thing for figuring out what goes in the migration is to look at the sql on the tables created by sequelize.sync() and then build the migrations from there.

migrations -c [migration name] 

Will create the template migration file in a migrations directory. You can then populate it with the fields you need created. This file will need to include createdAt/updatedAt, fields needed for associations, etc.

For initial table creation down should have:

migration.dropTable('MyTable');

But subsequent updates to the table structure can leave this out and just use alter table.

./node_modules/.bin/sequelize --migrate

An example create would look like:

module.exports = {
  up: function(migration, DataTypes, done) {
    migration.createTable(
        'MyTable',
        {
          id: {
            type: DataTypes.INTEGER,
            primaryKey: true,
            autoIncrement: true
          },
          bigString: {type: DataTypes.TEXT, allowNull: false},
          MyOtherTableId: DataTypes.INTEGER,
          createdAt: {
            type: DataTypes.DATE
          },
          updatedAt: {
            type: DataTypes.DATE
          }
        });
    done();
  },
  down: function(migration, DataTypes, done) {
    migration.dropTable('MyTable');
    done();
  }

To redo from start:

./node_modules/.bin/sequelize --migrate --undo
./node_modules/.bin/sequelize --migrate

I'm using coffee to run a seed file to populate the tables after:

coffee server/seed.coffee

This just has a create function in it that looks something like:

user = db.User.create
  username: 'bob'
  password: 'suruncle'
  email: '[email protected]'
.success (user) ->
  console.log 'added user'
  user_id = user.id
  myTable = [
    field1: 'womp'
    field2: 'rat'
  
    subModel: [
      field1: 'womp'
     ,
      field1: 'rat'
    ]
  ]
  

Remember to take your sync() out of index in your models or it will overwrite what the migrations and seed do.

Docs are at http://sequelize.readthedocs.org/en/latest/docs/migrations/ of course. But the basic answer is you have to add everything in yourself to specify the fields you need. It doesn't do it for you.

For development, there is now an option to sync the current tables by altering their structure. Using the latest version from the sequelize github repo, you can now run sync with the alter parameter.

Table.sync({alter: true})

A caveat from the docs:

>Alters tables to fit models. Not recommended for production use. Deletes data in columns that were removed or had their type changed in the model.

I went through this post and similar questions, it didn't really answer it for me. Migrations are useful for spinning up local databases and for updating data in production

I asked the question here and answered it as well: https://stackoverflow.com/questions/58003107/workflow-for-handling-sequelize-migrations-and-initialization

TL-DR version for a greenfield project

1. Design your database schema as you traditionally would using pure SQL scripts or if you use a gui tool instead
2. When you finalize all your 95% of your db schema and your happy with it, go ahead and move it to sequelize by moving the entire .sql file over
3. Make your first migration. Run sequelize init:migrate in the whatever folder where your models are at
4. Make your first migrations file. Run sequelize migration:generate --name [name_of_your_migration]
5. In that migration file, put this code in there

("use strict");
/**
 * DROP SCHEMA public CASCADE; CREATE SCHEMA public
 * ^ there's a schema file with all the tables in there. it drops all of that, recreates
 */
const fs = require("fs");
const initialSqlScript = fs.readFileSync("./migrations/sql/Production001.sql", {
  encoding: "utf-8",
});
const db = require("../models");
module.exports = {
  up: () => db.sequelize.query(initialSqlScript),
  down: () =>
    db.sequelize.query(`DROP SCHEMA public CASCADE; CREATE SCHEMA public;
`),
};

with this general folder structure

1. Now your sequelize setup is synced with your initial database schema
2. when you want to edit your database schema, run this again sequelize migration:generate --name [name_of_your_migration]
3. Go ahead and make your modifications here on the up and down migration paths. These are your ALTER statements to change column names, DELETE, ADD columns etc
4. Run sequelize db:migrate
5. You want the models synced to the changes to your remote db, so what you can do now is npm install sequelize-auto.
6. This will read the the current database schema on your database and auto generate model files. Use a command similar to this sequelize-auto -o "./models" -d sequelize_auto_test -h localhost -u my_username -p 5432 -x my_password -e postgres found under https://github.com/sequelize/sequelize-auto

You can use git to see difflogs on your model, there should be only changes reflecting changes in the database model. As a side note, do not ever modify the models directly if you use sequelize auto, as this will generate them for you. Likewise, you no longer should modify your database schema directly with SQL files, granted this is an option as you can import those .sql files as well

Now your database schema is up to date, and you've officially moved over to sequelize database migrations only.

Everything is version controlled. This is the ideal workflow for database and backend developer

Now with the new sequelize migration is very simple.

This is a example what you can do.

    'use strict';
    
    var Promise = require('bluebird'),
        fs = require('fs');
    
    module.exports = {
        up: function (queryInterface, Sequelize) {
    
            return Promise
                .resolve()
                .then(function() {
                    return fs.readFileSync(__dirname + '/../initial-db.sql', 'utf-8');
                })
                .then(function (initialSchema) {
                    return queryInterface.sequelize.query(initialSchema);
                })
        },
    
        down: function (queryInterface, Sequelize) {
            return Promise
                .resolve()
                .then(function() {
                    return fs.readFileSync(__dirname + '/../drop-initial-db.sql', 'utf-8');
                })
                .then(function (dropSql) {
                    return queryInterface.sequelize.query(dropSql);
                });
        }
    };

Remember you have to set:

"dialectOptions": { "multipleStatements": true }

on database config.

Use version. Version of the application depends on the version of the database. If the new version requires an update of a database, create migration for it.

update: I decided to abandon the migration (KISS) and run script update_db (sync forse: false) when it is needed.

Sequelize can run arbitrary SQL asynchronously.

What I would do is:

• Generate a Migration (To use as first migration);
• Dump your database, something like: mysql_dump -uUSER -pPASS DBNAME > FILE.SQL
• Either paste the full dump as text (Dangerous) or load a File with the full dump in Node:
• var baseSQL = "LOTS OF SQL and it's EVIL because you gotta put \ backslashes before line breakes and \"quotes\" and/or sum" + " one string for each line, or everything will break";
• var baseSQL = fs.readFileSync('../seed/baseDump.sql');
• Run this dump on Sequelize Migration:

module.exports = {
  up: function (migration, DataTypes) {
    var baseSQL = "whatever" // I recommend loading a file
    migration.migrator.sequelize.query(baseSQL);
  }
}

That should take care of setting up the database, albeit the async thing may become a problem. If that happens, I'd look at a way to defer returning the up sequelize function until the async query function is finished.

More about mysql_dump: http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html
More about Sequelize Migrations: http://sequelize.readthedocs.org/en/latest/docs/migrations/
More about Running SQL from within Sequelize Migration: https://github.com/sequelize/sequelize/issues/313

A bit late, and after reading the documentation, you don't need to have that first migration that you are talking about. All you have to do is to call sync in order to create the tables.

sequelize.sync()

You can also run a simple model synchronization by doing something like:

Project.sync() but I think that sequelize.sync() is a more useful general case for your project (as long as you import the good models at start time).

(taken from http://sequelizejs.com/docs/latest/models#database-synchronization)

This will create all initial structures. Afterwards, you will only have to create migrations in order to evolve your schemas.

hope it helps.

Here is my current workflow. I'm open to suggestions.

1. Set sequelize to create tables that don't exist
2. Set sequelize to drop and re-create all tables in a blank database called _blank
3. Use a mysql tool to compare _blank and and sync changes using that tool. Still looking for an affordable tool that can do this on mac. MySql workbench looks like you can import a model from an existing schema, and then sync schema. Trying to figure out how to do this via command line to make it easy.

That way you don't have to manually update the migrations table and have to worry about fat fingers, but you still get an ORM.

Friend I had the same question and managed to understand how to use them.

I started without ORM sequelize therefore I already had a data model.
I had to generate the models automatically with sequelize-auto and generate their migrations with this file that you create https://gist.github.com/ahelord/a7a7d293695b71aadf04157f0f7dee64 and put in sync ({Force: false})
This is in dev.I would have to version the model and the migrations and execute them every time I pull the code.

In production the server is only upstairs so you only have to run migrations and in each commit manage as you will version the model without stopping the backend

There is even more simple way (avoiding Sequalize). Which goes like this:

1. You type a command inside your project: npm run migrate:new

2. This creates 3 files. A js file, and two sql files named up and down

3. You put your SQL statement in those files, which is pure sql

4. Then you type: npm run migrate:up or npm run migrate:down

For this to work, please have a look at the db-migrate module.

Once you get it setup (which is not difficult), changing your DB is really easy and saves a lot of time.