Sequelize.js: how to use migrations and sync

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:

Create a migration: sequelize migration:create Write up and down functions in your migration file According to your changes in migration file, change your model manually 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: 'bob@bob.com'
.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: Workflow for handling sequelize migrations and initialization?

TL-DR version for a greenfield project

Design your database schema as you traditionally would using pure SQL scripts or if you use a gui tool instead 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 Make your first migration. Run sequelize init:migrate in the whatever folder where your models are at Make your first migrations file. Run sequelize migration:generate --name [name_of_your_migration] 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;
`),
};

https://i.stack.imgur.com/ldvsp.png

with this general folder structure

https://i.stack.imgur.com/9sQU2.png

Now your sequelize setup is synced with your initial database schema when you want to edit your database schema, run this again sequelize migration:generate --name [name_of_your_migration] 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 Run sequelize db:migrate You want the models synced to the changes to your remote db, so what you can do now is npm install sequelize-auto. 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');

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.

Set sequelize to create tables that don't exist Set sequelize to drop and re-create all tables in a blank database called _blank 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:

You type a command inside your project: npm run migrate:new This creates 3 files. A js file, and two sql files named up and down You put your SQL statement in those files, which is pure sql 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.

---