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);

};

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);    
});