Skip to content

Instantly share code, notes, and snippets.

@manuelbieh

manuelbieh/sequelize-schema-file-generator.js

Last active April 5, 2025 20:02
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save manuelbieh/ae3b028286db10770c81 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save manuelbieh/ae3b028286db10770c81 to your computer and use it in GitHub Desktop.
Download ZIP
Automatically generates migration files from your sequelize models
Raw
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);
};
@manuelbieh
Copy link
Author

manuelbieh commented Jan 21, 2016

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;

};

@deniscesar
Copy link

deniscesar commented May 12, 2016

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

Copy link

ghost commented May 26, 2016

Seems interesting updates please

@bisubus
Copy link

bisubus commented Jul 19, 2016

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.

@francolaiuppa
Copy link

francolaiuppa commented Dec 26, 2016

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.

@ahelord
Copy link

ahelord commented Mar 28, 2017

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

@manuelbieh
Copy link
Author

manuelbieh commented Mar 28, 2017

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.

@ahelord
Copy link

ahelord commented Mar 28, 2017 •
edited
Loading

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???

@ahelord
Copy link

ahelord commented Mar 29, 2017 •
edited
Loading

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

@ddyrcz
Copy link

ddyrcz commented Aug 3, 2017 •
edited
Loading

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

@jasonbronson
Copy link

jasonbronson commented Mar 19, 2019

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

@swilliamsilva
Copy link

swilliamsilva commented Aug 13, 2020

Muito bom!

@DanielOverdevestPZH
Copy link

DanielOverdevestPZH commented Jul 19, 2021 •
edited
Loading

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment