Automatically generates migration files from your sequelize models

sequelize-schema-file-generator.js

import * as models from "models";
import Sequelize from "sequelize";
import fs from "fs";

delete models.default;

const sequelize = new Sequelize(
    '',
    '',
    '', {
        host: '',
        dialect: '',
    }
);

for(let model in models) {

    let attributes = models[model].attributes;

    for(let column in attributes) {
        delete attributes[column].Model;
        delete attributes[column].fieldName;
        delete attributes[column].field;
        for(let property in attributes[column]) {
            if(property.startsWith('_')) {
                delete attributes[column][property];
            }
        }

        if(typeof attributes[column]['type'] !== 'undefined') {

            if(typeof attributes[column]['type']['options'] !== 'undefined' && typeof attributes[column]['type']['options'].toString === 'function') {
                attributes[column]['type']['options'] = attributes[column]['type']['options'].toString(sequelize);
            }

            if(typeof attributes[column]['type'].toString === 'function') {
                attributes[column]['type'] = attributes[column]['type'].toString(sequelize);
            }

        }

    }

    let schema = JSON.stringify(attributes, null, 4);
    let tableName = models[model].tableName;
    let indexes = ['\n'];

    if(models[model].options.indexes.length) {

        models[model].options.indexes.forEach((obj) => {

            indexes.push('        .then(() => {');
            indexes.push('            return queryInterface.addIndex(');
            indexes.push(`                '${tableName}',`);
            indexes.push(`                ['${obj.fields.join("','")}']`);

            let opts = {};
            if(obj.name) {
                opts.indexName = obj.name;
            }
            if(obj.unique === true) {
                opts.indicesType = 'UNIQUE';
            }
            if(obj.method === true) {
                opts.indexType = obj.method;
            }
            if(Object.keys(opts).length) {
                indexes.push(`                , ${JSON.stringify(opts)}`)
            }

            indexes.push('            )');
            indexes.push('        })');

        });

    }

    schema = schema.split('\n').map((line) => '            ' + line).join('\n');

    let template = `'use strict';
module.exports = {
    up: function(queryInterface, Sequelize) {
        return queryInterface.sequelize.query('SET FOREIGN_KEY_CHECKS = 0')
        .then(() => {
            return queryInterface.createTable('${tableName}',
${schema})
        })${indexes.join('\n')}
        .then(() => {
            return queryInterface.sequelize.query('SET FOREIGN_KEY_CHECKS = 1');
        });
    },
    down: function(queryInterface, Sequelize) {
        return queryInterface.sequelize.query('SET FOREIGN_KEY_CHECKS = 0')
        .then(() => {
            return queryInterface.dropTable('${tableName}');
        })
        .then(() => {
            return queryInterface.sequelize.query('SET FOREIGN_KEY_CHECKS = 1');
        });
    }
};`

    let d = new Date();

    let filename = [d.getFullYear(), d.getMonth()+1, d.getDate(), d.getHours(), d.getMinutes(), d.getSeconds()]
    .map((num) => num <= 60 && (num + 100).toString().substring(1) || num)
    .join('') + `-${models[model].tableName}`;

    fs.writeFileSync(`./${filename}.js`, template);

};

Creates files like this one:

'use strict';
module.exports = {
    up: function(queryInterface, Sequelize) {
        return queryInterface.sequelize.query('SET FOREIGN_KEY_CHECKS = 0')
        .then(() => {
            return queryInterface.createTable('topics',
            {
                "id": {
                    "type": "INTEGER",
                    "allowNull": false,
                    "primaryKey": true,
                    "autoIncrement": true
                },
                "title": {
                    "type": "VARCHAR(255)"
                },
                "description": {
                    "type": "VARCHAR(255)"
                },
                "teamId": {
                    "type": "INTEGER",
                    "allowNull": true,
                    "references": {
                        "model": "teams",
                        "key": "id"
                    },
                    "onDelete": "SET NULL",
                    "onUpdate": "CASCADE"
                },
                "responsibleId": {
                    "type": "INTEGER",
                    "allowNull": true,
                    "references": {
                        "model": "users",
                        "key": "id"
                    },
                    "onDelete": "SET NULL",
                    "onUpdate": "CASCADE"
                }
            })
        })

        .then(() => {
            return queryInterface.addIndex(
                'topics',
                ['title','teamId']
                , {"indicesType":"UNIQUE"}
            )
        })
        .then(() => {
            return queryInterface.sequelize.query('SET FOREIGN_KEY_CHECKS = 1');
        });
    },
    down: function(queryInterface, Sequelize) {
        return queryInterface.sequelize.query('SET FOREIGN_KEY_CHECKS = 0')
        .then(() => {
            return queryInterface.dropTable('topics');
        })
        .then(() => {
            return queryInterface.sequelize.query('SET FOREIGN_KEY_CHECKS = 1');
        });
    }
};

from models such as this one:

module.exports = function(sequelize, DataTypes) {

    const Topic = sequelize.define('topic', {
        title: {
            type: DataTypes.STRING,
        },
        description: {
            type: DataTypes.STRING
        }
    }, {

        indexes: [
            {
                fields: ['title', 'teamId'],
                unique: true
            }
        ],

        timestamps: false,

        classMethods: {

            associate(models) {

                models.Topic.belongsTo(models.Team);
                models.Topic.belongsTo(models.User, {
                    as: 'responsible'
                });

            }

        }

    });

    return Topic;

};

Have any examples of how to use ? I tried but did not succeed .

Seems interesting updates please

Thanks for the excellent work. As for the models from sequelize-cli, the script misses some things.

There should be

if (!(models[model] instanceof Sequelize.Model)) break;

check, this eliminates the need for delete models.default also.

And

if(models[model].options.indexes.length) {

should be

if(models[model].options.indexes && models[model].options.indexes.length) {

instead.

Amazing work, thank you SO much!.
I've forked it and changed the import statements to use require, added some HOWTO instructions and also made the migrations write directly into the migrations folder.

have this error when run this file in node 6.6.0

sudo node sequelize-schema-file-generator.js

(function (exports, require, module, __filename, __dirname) { import * as models from "models";
^^^^^^
SyntaxError: Unexpected token import

Converted to ES5 using Babel Online Converter:

"use strict";

var _models = require("models");

var models = _interopRequireWildcard(_models);

var _sequelize = require("sequelize");

var _sequelize2 = _interopRequireDefault(_sequelize);

var _fs = require("fs");

var _fs2 = _interopRequireDefault(_fs);

function _interopRequireDefault(obj) { return obj && obj.__esModule ? obj : { default: obj }; }

function _interopRequireWildcard(obj) { if (obj && obj.__esModule) { return obj; } else { var newObj = {}; if (obj != null) { for (var key in obj) { if (Object.prototype.hasOwnProperty.call(obj, key)) newObj[key] = obj[key]; } } newObj.default = obj; return newObj; } }

delete models.default;

var sequelize = new _sequelize2.default('', '', '', {
    host: '',
    dialect: ''
});

var _loop = function _loop(model) {

    var attributes = models[model].attributes;

    for (var column in attributes) {
        delete attributes[column].Model;
        delete attributes[column].fieldName;
        delete attributes[column].field;
        for (var property in attributes[column]) {
            if (property.startsWith('_')) {
                delete attributes[column][property];
            }
        }

        if (typeof attributes[column]['type'] !== 'undefined') {

            if (typeof attributes[column]['type']['options'] !== 'undefined' && typeof attributes[column]['type']['options'].toString === 'function') {
                attributes[column]['type']['options'] = attributes[column]['type']['options'].toString(sequelize);
            }

            if (typeof attributes[column]['type'].toString === 'function') {
                attributes[column]['type'] = attributes[column]['type'].toString(sequelize);
            }
        }
    }

    var schema = JSON.stringify(attributes, null, 4);
    var tableName = models[model].tableName;
    var indexes = ['\n'];

    if (models[model].options.indexes.length) {

        models[model].options.indexes.forEach(function (obj) {

            indexes.push('        .then(() => {');
            indexes.push('            return queryInterface.addIndex(');
            indexes.push("                '" + tableName + "',");
            indexes.push("                ['" + obj.fields.join("','") + "']");

            var opts = {};
            if (obj.name) {
                opts.indexName = obj.name;
            }
            if (obj.unique === true) {
                opts.indicesType = 'UNIQUE';
            }
            if (obj.method === true) {
                opts.indexType = obj.method;
            }
            if (Object.keys(opts).length) {
                indexes.push("                , " + JSON.stringify(opts));
            }

            indexes.push('            )');
            indexes.push('        })');
        });
    }

    schema = schema.split('\n').map(function (line) {
        return '            ' + line;
    }).join('\n');

    var template = "'use strict';\nmodule.exports = {\n    up: function(queryInterface, Sequelize) {\n        return queryInterface.sequelize.query('SET FOREIGN_KEY_CHECKS = 0')\n        .then(() => {\n            return queryInterface.createTable('" + tableName + "',\n" + schema + ")\n        })" + indexes.join('\n') + "\n        .then(() => {\n            return queryInterface.sequelize.query('SET FOREIGN_KEY_CHECKS = 1');\n        });\n    },\n    down: function(queryInterface, Sequelize) {\n        return queryInterface.sequelize.query('SET FOREIGN_KEY_CHECKS = 0')\n        .then(() => {\n            return queryInterface.dropTable('" + tableName + "');\n        })\n        .then(() => {\n            return queryInterface.sequelize.query('SET FOREIGN_KEY_CHECKS = 1');\n        });\n    }\n};";

    var d = new Date();

    var filename = [d.getFullYear(), d.getMonth() + 1, d.getDate(), d.getHours(), d.getMinutes(), d.getSeconds()].map(function (num) {
        return num <= 60 && (num + 100).toString().substring(1) || num;
    }).join('') + ("-" + models[model].tableName);

    _fs2.default.writeFileSync("./" + filename + ".js", template);
};

for (var model in models) {
    _loop(model);
};

Remember to enter your database credentials in line 21.

thanks work to me with this file but have this error

{ SequelizeDatabaseError: unrecognized configuration parameter "foreign_key_checks"
at Query.formatError (/Users/lrodriguez/express_sequelize_github_scheme/express-example/node_modules/sequelize/lib/dialects/postgres/query.js:357:14)
at Query. (/Users/lrodriguez/express_sequelize_github_scheme/express-example/node_modules/sequelize/lib/dialects/postgres/query.js:88:19)
at emitOne (events.js:96:13)
at Query.emit (events.js:188:7)
at Query.handleError (/Users/lrodriguez/express_sequelize_github_scheme/express-example/node_modules/pg/lib/query.js:143:8)
at Connection. (/Users/lrodriguez/express_sequelize_github_scheme/express-example/node_modules/pg/lib/client.js:180:26)
at emitOne (events.js:96:13)
at Connection.emit (events.js:188:7)
at Socket. (/Users/lrodriguez/express_sequelize_github_scheme/express-example/node_modules/pg/lib/connection.js:136:12)
at emitOne (events.js:96:13)
at Socket.emit (events.js:188:7)
at readableAddChunk (_stream_readable.js:176:18)
at Socket.Readable.push (_stream_readable.js:134:10)
at TCP.onread (net.js:543:20)
name: 'SequelizeDatabaseError',

this issue is for your script is for mysql and my database is postgres???

this file dont work in postgress , i forked script
// with respect original file remove return queryInterface.sequelize.query('SET FOREIGN_KEY_CHECKS = 0')
// and return queryInterface.sequelize.query('SET FOREIGN_KEY_CHECKS = 1') because postgres dont work only for mysql
https://gist.github.com/ahelord/a7a7d293695b71aadf04157f0f7dee64

this work for postgress

Life would be beautiful if it could generate migrations based on models created using the sequelize-typescript decorators :)

Pretty good thanks for this. It has trouble with handling virtual fields and also default values of time or date fields as {} which throws errors. must be a bug but was an easy fix on my end

Muito bom!

I needed also to order the files to create tables in correct order (dependencies of related database tables). I used TopologicalSort order algorithm for correct order and add ordering number to exported file.

This does the trick:

var ts = new jsgraphs.TopologicalSort(g);
 
var order = ts.order().reverse();

Full source code:

"use strict"
const fs = require('fs');
const path = require('path');
const Sequelize = require('sequelize');
const basename = path.basename(__filename);
const env = process.env.NODE_ENV || 'development';
const config = require('../config/config.js');
const models = {}
const exportOrder = {}

let sequelize = new Sequelize(config.database, config.username, config.password, config);

fs
  .readdirSync('models')
  .filter(file => {
    return (file.indexOf('.') !== 0) && (file !== basename) && (file.slice(-3) === '.js') && (file !== 'index.js');
  })
  .forEach(file => {
    console.log(file)
    const model = sequelize['import'](path.join('../models', file));

    console.log(model)
    models[model.name] = model;
  });

Object.keys(models).forEach(modelName => {
  if (models[modelName].associate) {
    models[modelName].associate(models);
  }
});

// Override timezone formatting
Sequelize.DATE.prototype._stringify = function _stringify(date, options) {
  date = this._applyTimezone(date, options);

  // Z here means current timezone, _not_ UTC
  // return date.format('YYYY-MM-DD HH:mm:ss.SSS Z');
  return date.format('YYYY-MM-DD HH:mm:ss.SSS');
};

var jsgraphs = require('js-graph-algorithms');
var g = new jsgraphs.DiGraph(Object.keys(models).length); 

var node1  = -1

Object.keys(models).forEach(model => {
    node1++;
    console.log(model)
    g.node(node1).label = models[model].tableName;
    let attributes = models[model].tableAttributes;

    for(let column in attributes) {
        delete attributes[column].Model;
        delete attributes[column].fieldName;
        delete attributes[column].field;
        for(let property in attributes[column]) {
            if(property.startsWith('_')) {
                delete attributes[column][property];
            }
        }

        if(typeof attributes[column]['type'] !== 'undefined') {

            if(typeof attributes[column]['type']['options'] !== 'undefined' && typeof attributes[column]['type']['options'].toString === 'function') {
                attributes[column]['type']['options'] = attributes[column]['type']['options'].toString(sequelize);
            }

            if(typeof attributes[column]['type'].toString === 'function') {
                attributes[column]['type'] = attributes[column]['type'].toString(sequelize);
            }

        }
        if(typeof attributes[column]['references'] !== 'undefined') {
            var refModel = attributes[column]['references'].model
            var node2 = Object.keys(models).indexOf(refModel.tableName)
            g.addEdge(node1, node2)
            console.log(`Model associated with other model: ${refModel.tableName}`)
        }

    }

    let dbschema = JSON.stringify(attributes, null, 4);
    let tableName = models[model].tableName;
    let indexes = ['\n'];

    if(models[model].options.indexes.length) {

        models[model].options.indexes.forEach((obj) => {

            indexes.push('        .then(() => {');
            indexes.push('            return queryInterface.addIndex(');
            indexes.push(`                '${tableName}',`);
            indexes.push(`                ['${obj.fields.join("','")}']`);

            let opts = {};
            if(obj.name) {
                opts.indexName = obj.name;
            }
            if(obj.unique === true) {
                opts.indicesType = 'UNIQUE';
            }
            if(obj.method === true) {
                opts.indexType = obj.method;
            }
            if(Object.keys(opts).length) {
                indexes.push(`                , ${JSON.stringify(opts)}`)
            }

            indexes.push('            )');
            indexes.push('        })');

        });

    }

    dbschema = dbschema.split('\n').map((line) => '            ' + line).join('\n');

    let template = `'use strict';
module.exports = {
    up: function(queryInterface, Sequelize) {
        return queryInterface.createTable('${tableName}', ${dbschema},
        {
          schema: '${models[model]._schema}'
        })${indexes.join('\n')}
        
    },
    down: function(queryInterface, Sequelize) {
        return queryInterface.dropTable({tableName: '${tableName}', schema: '${models[model]._schema}'});
    }
};`
    exportOrder[node1] = {tableName: models[model].tableName, template: template}

});

var ts = new jsgraphs.TopologicalSort(g);
 
var order = ts.order().reverse();
console.log(order);

order.forEach((item, index) => {
    let exporter = exportOrder[item]
    let d = new Date();
    let index_pad = String(index).padStart(3, '0')
    let filename = [d.getFullYear(), d.getMonth()+1, d.getDate(), d.getHours(), d.getMinutes(), d.getSeconds()]
    .map((num) => num <= 60 && (num + 100).toString().substring(1) || num)
    .join('') + `-${index_pad}-${exporter.tableName}`;
    
    fs.writeFileSync(`./migrations/generated/${filename}.js`, exporter.template);    
});