Lazy Syntax for Sequelize Migration

Docs.md

Lazy Syntax

Lazy syntax is for migration pattern for migrate models to database. They have their own code just for lazy purpose created this so named it lazy sytnax. Current features have priority and error handling.

Example lazy syntax migration pattern
migrations:
    {
        ['1-clean-startup']: // migration file_name
        {
            table: 'toppings', // table name
            timestamps: true,  // use timestamp?
            paranoid: true,    // use paranoid
            priority: 7,       // action priority
            createTable: {     // action strategy
                toppingId: '[INTEGER][PK][AUTO]',
                topping_code: '[STRING]',
                topping_name: '[STRING]',
                topping_price: '[DECIMAL(2)]',
                productId: '[INTEGER][REF_TABLE:products][REF_KEY:productId][UPDATE:CASCADE][DELETE:CASCADE]'
            }
        }
    }

Every migration files must with these code.

require('module-alias/register');
module.exports = require('@migration')(module.filename);

Command

Migrating - node_modules/.bin/sequelize db:migrate

Undo migration - node_modules/.bin/sequelize db:migrate:undo

---

Syntax

Default first syntax is data type and others order didn't care.

[DATA_TYPE]
[NOT_NULL]
[NULLABLE]
[UNIQUE]
[AUTO]
[PK]
[UPDATE:CASCADE]
[DELETE:CASCADE]
[REF_TABLE:reference_table]
[REF_KEY:reference_key]

DATA_TYPE

You can refer to here all of the data types ( http://docs.sequelizejs.com/variable/index.html#static-variable-DataTypes )

Example of data type syntax :

Sequelize.STRING = [STRING]
Sequelize.ARRAY(Sequelize.STRING) = [ARRAY(S@STRING)]
Sequelize.STRING.BINARY = [STRING.BINARY]
Sequelize.INTEGER(10).ABC = [INTEGER(10).ABC]

NOT_NULL & NULLABLE

Allow null or not null for data constraint.

UNIQUE

Is the property unique.

AUTO

Auto increment for property. Usually for primary key only.

PK

Is the property primary key.

UPDATE & DELETE

When foreign key update or delete what should we do. Default value : CASCADE, values: CASCADE, CASCDE, NO ACTION

REF_TABLE & REF_KEY

Foreign Key table name & key name.

Example: 
[REF_TABLE:menus]
[REF_KEY:menuId]

---

Migration Strategy

There have six migration strategy, these also normally we use.

• create table
• change column
• add column
• remove column
• add constraint
• remove constraint
• insert data

Create Tables

Create table is for creating new table when migrate. when migrating will create new table for models.

CreateTable Pattern

createTable: {
    field_name: 'SYNTAX'
}

Change Column

Change column is for changing existing columns. when migrating will change column by after and undo migrating will change column by before.

ChangeColumn Pattern

changeColumn: {
    field_name: {
        before: 'SYNTAX',
        after: 'SYNTAX'
    }
}

Add Column

Add column is for adding new columns. when migrating will add new columns by given syntax and undo migrating will just remove by name.

AddColumn Pattern

addColumn: {
    field: 'SYNTAX'
}

Remove Column

Remove column is for remove existing column. when migrating will remove column by name and undo-migrating will add column by given syntax.

RemoveColumn Pattern

removeColumn: {
    field: 'SYNTAX'
}

Add Constraint

Add contraints for specific fields. when migrating will add constraint to t he field and undo-migrating will remove contrainst from field. More information you can check from here 'http://docs.sequelizejs.com/class/lib/query-interface.js~QueryInterface.html#instance-method-addConstraint'.

AddConstraint Pattern

addConstraint: [{
    fields: [],
    options: {
        type: 'unique',
        name: '',
    },
}, {
    fields: [],
    options: {
        type: 'unique',
        name: '',
    },
}]

Remove Constraint

Remove constraints for specific fields. when migrating will remove constraint from field and undo-migrating will add constraint back to the fields. More information you can check from here 'http://docs.sequelizejs.com/class/lib/query-interface.js~QueryInterface.html#instance-method-addConstraint'.

removeConstraint: [{
    fields: [],
    options: {
        type: 'unique',
        name: '',
    },
}, {
    fields: [],
    options: {
        type: 'unique',
        name: '',
    },
}]

Insert Data ( Seeding )

Insert data to database. when migrating will insert data and undo-migrating will delete data.

insertData: {
	seeds: [{
		account_token: 'XXX',
		account_owner: 'XXX',
		account_type: 'ADMIN',
		id: null
	}]
}

Last

Lastly, like others migration tools too, output will be up & down function. Migration builder code you can view at '/custom/internal/migration-builder.js'

{
    up: async (queryInterface, Sequielize) =>
    {

    },
    down: async (queryInterface, Sequelize) =>
    {

    }
}

LazySyntax.js

const path = require("path");
const glob = require("glob");

module.exports = (migration_name) => ({
    up: async (queryInterface, Sequelize) => {
        try {
            await migrate(queryInterface, Sequelize, migration_name, true);
        } catch (error) {
            console.error(error.stack);
            throw error;
        }
    },
    down: async (queryInterface, Sequelize) => {
        try {
            await migrate(queryInterface, Sequelize, migration_name, false);
        } catch (error) {
            console.error(error.stack);
            throw error;
        }
    },
});

async function migrate(queryInterface, Sequelize, migration_name, up) {
    global.Sequelize = Sequelize;
    await queryInterface.sequelize.transaction(async (transaction) => {
        const migrations = buildMigrations(
            Sequelize,
            path.basename(migration_name, ".js"),
            up
        ).sort((a, b) =>
            a.priority > b.priority ? 1 : b.priority > a.priority ? -1 : 0
        );

        for (const migration of migrations) {
            try {
                await migration.action(queryInterface, transaction);
            } catch (error) {
                throw error;
            }
        }
    });
}

function buildMigrations(Sequelize, migration_name, up) {
    const migrations = [];
    const models = glob.sync("../../models/**/*.js", { cwd: __dirname });
    for (const model_path of models) {
        const model_name = path.basename(model_path, ".js");
        const model = require(model_path);

        if (!model.migrations) {
            continue;
        }

        const migrate_options = model.migrations[migration_name];
        if (!migrate_options) {
            continue;
        }

        const timestamps = migrate_options.timestamps;
        const paranoid = migrate_options.paranoid;
        if (paranoid && !timestamps) {
            throw new Error(
                `'paranoid' is enabled but timestamps not enabled.`
            );
        }

        const table = migrate_options.table;
        if (!table) {
            throw new Error(
                `'table' is not defined in migration:${migration_name} of model:${model_name}.`
            );
        }

        /*
            createTable: {
                field: '[LAZY][SYNTAX]'
            }
        */

        const createTable = migrate_options.createTable;
        if (createTable) {
            if (up) {
                const attributes = {};
                for (const attribute_name in createTable) {
                    attributes[attribute_name] = lazySyntaxToAttribute(
                        createTable[attribute_name],
                        Sequelize
                    );
                }
                if (timestamps) {
                    attributes["updatedAt"] = Sequelize.DATE;
                    attributes["createdAt"] = Sequelize.DATE;
                    if (paranoid) {
                        attributes["deletedAt"] = Sequelize.DATE;
                    }
                }
                console.info(
                    `Migration: Creating table for ${model_name} with name: ${table}.`
                );
                migrations.push({
                    action: async (query, transaction) =>
                        await query.createTable(table, attributes, {
                            transaction,
                        }),
                    priority:
                        createTable.priority || migrate_options.priority || 99,
                });
            } else {
                console.info(`Migration: Dropping table of ${model_name}.`);
                migrations.push({
                    action: async (query, transaction) =>
                        await query.dropTable(table, {
                            transaction,
                            force: true,
                        }),
                    priority:
                        createTable.priority || migrate_options.priority || 99,
                });
            }
        }
    }

    return migrations;
}

function lazySyntaxToAttribute(lazy_syntax, Sequelize) {
    const regex = lazy_syntax.match(/(?<=\[).+?(?=\])/gs);
    if (!regex) {
        throw new Error(`Lazy syntax doesn't match '/(?<=\[).+?(?=\])/gs'.`);
    } else {
        let typeSyntax = regex.shift();
        let type = undefined;
        let primaryKey = undefined;
        let autoIncrement = undefined;
        let unique = undefined;
        let allowNull = undefined;
        let defaultValue = undefined;
        let ref_table = undefined;
        let ref_key = undefined;
        let ref_ondelete = undefined;
        let ref_onupdate = undefined;

        try {
            if (typeSyntax.includes("(")) {
                if (typeSyntax.includes(".")) {
                    // INTEGER(10).ABC.ABC
                    typeSyntax = typeSyntax.split(".");
                    type = Sequelize;
                    for (const name of typeSyntax) {
                        const funcSyntax = name.match(/(?<=\().+?(?=\))/gs);
                        if (funcSyntax) {
                            name = name.replace(`(${  funcSyntax[0]  })`, "");
                            type = type[name](funcSyntax[0]);
                        } else {
                            type = type[name];
                        }
                    }
                } else if (typeSyntax.includes("S@")) {
                    // ARRAY(S@STRING)
                    const funcSyntax = typeSyntax.match(/(?<=\().+?(?=\))/gs);
                    typeSyntax = typeSyntax.replace(
                        `(${  funcSyntax[0]  })`,
                        ""
                    );
                    type = Sequelize[typeSyntax](
                        Sequelize[funcSyntax[0].replace("S@", "")]
                    );
                } else {
                    const funcSyntax = typeSyntax.match(/(?<=\().+?(?=\))/gs);
                    typeSyntax = typeSyntax.replace(
                        `(${  funcSyntax[0]  })`,
                        ""
                    );

                    if (funcSyntax.includes(",")) {
                        // DOUBLE(10, 20)
                        type = Sequelize[typeSyntax].apply(
                            this,
                            funcSyntax[0].split(",")
                        );
                    } else if (funcSyntax.includes("S@")) {
                        type = Sequelize[typeSyntax](
                            funcSyntax[0].replace("S@", "")
                        );
                    } else {
                        // INTEGER(10)
                        type = Sequelize[typeSyntax](funcSyntax[0]);
                    }
                }
            } else if (typeSyntax.includes(".")) {
                // INTEGER.XXX.XXX
                type = Sequelize;
                for (const name of typeSyntax.split(".")) {
                    type = type[name];
                }
            } else {
                // INTEGER
                type = Sequelize[typeSyntax];
            }

            for (const syntax of regex) {
                if (syntax === "PK") {
                    primaryKey = true;
                } else if (syntax === "NULLABLE") {
                    allowNull = true;
                } else if (syntax === "NOT_NULL") {
                    allowNull = false;
                } else if (syntax === "AUTO") {
                    autoIncrement = true;
                } else if (syntax === "UNIQUE") {
                    unique = true;
                } else if (syntax.includes(":")) {
                    const syntax_value = /^([^:]*?):(.*)$/gs.exec(syntax);
                    syntax_value.shift();

                    switch (syntax_value[0]) {
                        case "UNIQUE":
                            unique = syntax_value[1];
                            break;
                        case "DEFAULT":
                            defaultValue = parsingValue(
                                typeSyntax,
                                syntax_value[1],
                                Sequelize
                            );
                            break;
                        case "UPDATE":
                            ref_onupdate = syntax_value[1];
                            break;
                        case "DELETE":
                            ref_ondelete = syntax_value[1];
                            break;
                        case "REF_TABLE":
                            ref_table = syntax_value[1];
                            ref_key = "id";
                            break;
                    }
                }
            }
            const attributes = {
                type,
                primaryKey,
                autoIncrement,
                unique,
                allowNull,
                defaultValue,
                references:
                    ref_key && ref_table
                        ? {
                            model: ref_table,
                            key: ref_key,
                        }
                        : undefined,
                onDelete: ref_ondelete,
                onUpdate: ref_onupdate,
            };
            Object.keys(attributes).forEach(
                (key) => !attributes[key] && delete attributes[key]
            );
            return attributes;
        } catch (error) {
            throw new Error(
                `Unable define 'type': ${typeSyntax}. Error ${error.message}`
            );
        }
    }
}

function parsingValue(type, value, Sequelize) {
    if (type.startsWith("INTEGER")) {
        // 1
        return parseInt(value);
    }
    if (
        type.startsWith("NUMBER") ||
        type.startsWith("DECIMAL") ||
        type.startsWith("DOUBLE")
    ) {
        // 1.0
        return Number(value);
    }
    if (
        type.startsWith("JSON") ||
        type.startsWith("JSONB") ||
        type.startsWith("ARRAY")
    ) {
        // { object: 'literal' }
        // ARRAY [ 'array', 'object' ]
        return JSON.parse(value);
    }
    if (type.startsWith("DATE") || type.startsWith("DATEONLY")) {
        // year month date hour minute second millisecond
        // *     *     *    *     *      *        *
        // 2018 11 3 2 36 20 *
        // 2018年 11月 3日 凌晨2点 36分 20秒 0毫秒
        if (value.includes(" ")) {
            const targetDate = value
                .split(" ")
                .map((x) => (x !== "*" ? parseInt(x) : 0));
            if (targetDate.length >= 6) {
                return new Date.apply(
                    this,
                    targetDate.map((x) => (x !== "*" ? parseInt(x) : 0))
                );
            } else {
                throw new Error(`${value} isn't valid format for DATE Object`);
            }
        } else if (value === "NOW") {
            // Sequelize DATE NOW
            return Sequelize.NOW;
        } else {
            throw new Error(`Unable parse ${value} as DATE Object.`);
        }
    }
    if (type.startsWith("BOOLEAN")) {
        // true / false
        return Boolean(value);
    }
    if (type.startsWith("RANGE")) {
        // RANGE return [1, 2]
        const innerType = type.replace("RANGE(", "");
        const values = value.split(",");
        return [
            parsingValue(innerType, values[0], Sequelize),
            parsingValue(innerType, values[1], Sequelize),
        ];
    }
    return value;
}