Sequelize.js: how to use migrations and sync

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 :-(

Generating the "first migration"

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

sequlize 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 sequlize-cli, and do the same with migrations. After doing this, wipe database (if possible), and run

sequlize 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 mending 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 everytime. 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.

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.

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.

Sequelize can run arbitrar SQL. But be careful for it is asynchronous.

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