manuelbieh/sequelize-schema-file-generator.js

manuelbieh · 2016-01-21T00:38:21Z

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 · 2016-05-12T19:19:25Z

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

ghost · 2016-05-26T13:13:23Z

Seems interesting updates please

bisubus · 2016-07-19T15:22:56Z

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 · 2016-12-26T16:26:48Z

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 · 2017-03-28T17:05:22Z

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 · 2017-03-28T17:11:51Z

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 · 2017-03-28T20:43:31Z

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 · 2017-03-29T16:30:21Z

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 · 2017-08-03T05:39:26Z

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

jasonbronson · 2019-03-19T13:23:23Z

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 · 2020-08-13T14:40:52Z

Muito bom!

DanielOverdevestPZH · 2021-07-19T14:41:59Z

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

manuelbieh/sequelize-schema-file-generator.js

manuelbieh commented Jan 21, 2016

deniscesar commented May 12, 2016

ghost commented May 26, 2016

bisubus commented Jul 19, 2016

francolaiuppa commented Dec 26, 2016

ahelord commented Mar 28, 2017

manuelbieh commented Mar 28, 2017

ahelord commented Mar 28, 2017 •

edited

Loading

ahelord commented Mar 29, 2017 •

edited

Loading

ddyrcz commented Aug 3, 2017 •

edited

Loading

jasonbronson commented Mar 19, 2019

swilliamsilva commented Aug 13, 2020

DanielOverdevestPZH commented Jul 19, 2021 •

edited

Loading

	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/sequelize-schema-file-generator.js

manuelbieh commented Jan 21, 2016

deniscesar commented May 12, 2016

ghost commented May 26, 2016

bisubus commented Jul 19, 2016

francolaiuppa commented Dec 26, 2016

ahelord commented Mar 28, 2017

manuelbieh commented Mar 28, 2017

ahelord commented Mar 28, 2017 • edited Loading

ahelord commented Mar 29, 2017 • edited Loading

ddyrcz commented Aug 3, 2017 • edited Loading

jasonbronson commented Mar 19, 2019

swilliamsilva commented Aug 13, 2020

DanielOverdevestPZH commented Jul 19, 2021 • edited Loading

ahelord commented Mar 28, 2017 •

edited

Loading

ahelord commented Mar 29, 2017 •

edited

Loading

ddyrcz commented Aug 3, 2017 •

edited

Loading

DanielOverdevestPZH commented Jul 19, 2021 •

edited

Loading