Navigation Menu

Skip to content

Instantly share code, notes, and snippets.

@harish2704
Created February 5, 2020 13:01
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save harish2704/5df914fb52b2d90e4d4d4e13cbf9b8d3 to your computer and use it in GitHub Desktop.
Save harish2704/5df914fb52b2d90e4d4d4e13cbf9b8d3 to your computer and use it in GitHub Desktop.
Create knex.js migration for existing database. Input Database structure in the form of create table SQL statements, and this ejs template can create Knex.js migration
<%
const Parser = require('sql-ddl-to-json-schema');
const fs = require('fs');
const [ sqlFileName ] = argv;
if( !sqlFileName ) throw new Error('SQL file not specified');
const parser = new Parser('mysql');
parser.feed( fs.readFileSync( sqlFileName, 'utf-8'));
const data = parser.toCompactJson();
// const data = parser.toJsonSchemaArray();
// const util = require('util');
// console.log( util.inspect( data, { depth: null }));
// process.exit();
// console.log( JSON.stringify( data ));
// data = JSON.parse( dataStr );
var typemap = {
int: 'integer',
text: 'text',
varchar: 'string',
timestamp: 'timestamp',
datetime: 'datetime',
enum: 'enum'
};
function getType( col ){
if( col.options.autoincrement ){
return 'increments';
}
const out = typemap[col.type.datatype];
if( out === undefined ){ throw 'Unknown column: ' + col.type.datatype }
return out;
}
function getLen( col ){
var out;
var t = col.type;
switch( t.datatype ){
case 'int':
out = t.width;
break;
case 'varchar':
out = t.length;
break;
case 'enum':
out = "['" + t.values.join("','") + "']";
break;
}
if( out !== undefined ){
return ', ' + out;
}
return ''
}
var timestampCols = ['timestamp', 'datetime'];
function getDefVal(col){
if( ( timestampCols.indexOf(col.type.datatype) !== -1) && col.options.default === 'current_timestamp()'){
return 'knex.fn.now()';
}
return `'${col.options.default}'`;
}
function getFkCols( cols ){
return cols.map( v => v.column ).toString();
}
function capitalize( str ){
return str.charAt(0).toUpperCase() + str.slice(1);
}
%>
exports.up = async function(knex) {
<% data.forEach( function( tbl ){ %>
await knex.schema.createTable('<%-tbl.name -%>', function(table) {
/* Column defnitions */
<% tbl.columns.forEach( function( col ){ -%>
table.<%-getType( col )-%>('<%- col.name -%>'<%- getLen(col); _%>)
<%_ if(col.options.autoincrement){ _%>
.primary()
<%_}_%>
<%_ if(col.options.unsigned){ _%>
.unsigned()
<%_}_%>
<%_ if(col.options.nullable === false ){ _%>
.notNullable()
<%_}_%>
<%_ if( [undefined,null].indexOf(col.options.default) === -1 ){ _%>
.defaultTo(<%- getDefVal( col ) -%>)
<%_}_%>
<% }) %>
/* Column defnitions */
<% if(tbl.foreignKeys) {%>
/* Foreign key defnitions */
<% tbl.foreignKeys.forEach( function( fk ){ -%>
table.foreign('<%- getFkCols(fk.columns) -%>')
.references('<%- getFkCols(fk.reference.columns ) -%>')
<% fk.reference.on && fk.reference.on.forEach( function( on ){ -%>
.on<%- capitalize(on.trigger) -%>('<%- on.action -%>')
<% }) -%>
.inTable('<%-fk.reference.table-%>');
<% }) %>
/* Foreign key defnitions */
<%}%>
<% if(tbl.uniqueKeys) {%>
/* Unique key indexes */
<% tbl.uniqueKeys.forEach( function( uk ){ -%>
table.unique('<%- getFkCols(uk.columns) -%>');
<% }) %>
/* Unique key indexes */
<%}%>
});
<% })%>
};
exports.down = async function(knex) {
await knex.schema
<% data.reverse().forEach( function( tbl ){ _%>
.dropTable('<%- tbl.name -%>')
<% }) _%>
};
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment