vonbraunlabs/sql-ddl-generator

SQL To DDL Generator

A tool to aid on generating DDL SQL. It was conceived to abstract away the boring part of writing the SQL tables and let developers/DBAs focus on the business logic. This tool assumes that every table have:, _(*1)

• an integer ID column that uniquely identifies the row and is named ID
• audit columns to specify created time and last updated time
• audit columns to specify the db user who created the row and the one who last updated the row
• active column that indicates wether that row is active or should be regarded as deleted/inactive

The file user.json contains the following JSON:, _(*2)

{
    "name": "mydatabase",
    "table": {
        "name": "user",
        "field_list": [
            {
                "name": "username",
                "type": "VARCHAR(30)",
                "not_null": true,
                "comment": "Login username"
            },
            {
                "name": "email",
                "type": "VARCHAR(64)",
                "not_null": true,
                "comment": "User email"
            },
            {
                "name": "password",
                "type": "VARCHAR(128)",
                "not_null": true,
                "comment": "User password"
            },            
            {
                "name": "confirmed_at",
                "type": "DATETIME",
                "not_null": false,
                "comment": "User confirmation date"
            }
        ],
        "fk_list": [
            {
                "name": "inviter_id",
                "references": "user",
                "comment": "If user was invited by someone, contains the ID of that user account, NULL otherwise"
            }
        ],
        "unique_list": [
            ["username"],
            ["email"]
        ]
    }
}

The name of the schema/database is mydatabase. It is creating a table named user, with four columns: username, email, password and confirmed_at. Moreover, it declares a foreign key column inviter_id to itself. On its last part, the snippet declares UNIQUE constraints. The rows must have unique username and unique email. Executing ./sql-ddl-generator user.json the following is printed to standard output:, _(*3)

-- Generated by VBL - sql-ddl-generator
-- -----------------------------------------------------
-- Database mydatabase
-- -----------------------------------------------------

CREATE SCHEMA IF NOT EXISTS `mydatabase` DEFAULT CHARACTER SET utf8 ;
USE `mydatabase`;

-- -----------------------------------------------------
-- Table `mydatabase`.`user`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `mydatabase`.`user` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `username` VARCHAR(30) NOT NULL COMMENT 'Login username',
    `email` VARCHAR(64) NOT NULL COMMENT 'User email',
    `password` VARCHAR(128) NOT NULL COMMENT 'User password',
    `confirmed_at` DATETIME COMMENT 'User confirmation date',
    `active` BOOLEAN NOT NULL DEFAULT 1,
    `create_by` VARCHAR(32) NOT NULL,
    `create_time` TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `update_by` VARCHAR(32) NOT NULL,
    `update_time` TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `inviter_id` INT UNSIGNED COMMENT 'If user was invited by someone, contains the ID of that user account, NULL otherwise',
    PRIMARY KEY (`id`),
    CONSTRAINT `fk_user_inviter_id`
        FOREIGN KEY(`inviter_id`)
        REFERENCES `mydatabase`.`user` (`id`)
        ON DELETE NO ACTION
        ON UPDATE NO ACTION
) Engine=InnoDB;

CREATE UNIQUE INDEX `unique_user_username` ON `mydatabase`.`user` (`username`);
CREATE UNIQUE INDEX `unique_user_email` ON `mydatabase`.`user` (`email`);

CREATE TRIGGER `user_before_insert` BEFORE INSERT ON `mydatabase`.`user`
FOR EACH ROW
    SET NEW.`create_by` = CURRENT_USER(),
        NEW.`update_by` = CURRENT_USER();

CREATE TRIGGER `user_before_update` BEFORE UPDATE ON `mydatabase`.`user`
FOR EACH ROW
    SET NEW.`update_by` = CURRENT_USER(),
        NEW.`update_time` = CURRENT_TIMESTAMP(3);

Auditing Special Feature

For some application, it is necessary to keep track of all changes on table, usually for auditing porposes. The audit flag creates a clone of the table with the prefix audit_ in its name and store all changes on the clonned table. If a row is updated N times, there will be N records of that specific row on the equivalent audit table., _(*4)

{
    "name": "mydatabase",
    "table": {
        "name": "user",
        "field_list": [
            {
                "name": "username",
                "type": "VARCHAR(30)",
                "not_null": true,
                "comment": "Login username"
            },
            {
                "name": "email",
                "type": "VARCHAR(64)",
                "not_null": true,
                "comment": "User email"
            },
            {
                "name": "password",
                "type": "VARCHAR(128)",
                "not_null": true,
                "comment": "User password"
            },            
            {
                "name": "confirmed_at",
                "type": "DATETIME",
                "not_null": false,
                "comment": "User confirmation date"
            }
        ],
        "fk_list": [
            {
                "name": "inviter_id",
                "references": "user",
                "comment": "If user was invited by someone, contains the ID of that user account, NULL otherwise"
            }
        ],
        "unique_list": [
            ["username"],
            ["email"]
        ],
        "audit": true
    }
}

Consider the JSON above. From this model, the sql-ddl-generator will produce SQL below. Mind how tedious it would be to write that that SQL by hand. Now imagine you have many tables that require that auditing feature., _(*5)

-- Generated by VBL - sql-ddl-generator
-- -----------------------------------------------------
-- Database mydatabase
-- -----------------------------------------------------

CREATE SCHEMA IF NOT EXISTS `mydatabase` DEFAULT CHARACTER SET utf8 ;
USE `mydatabase`;

-- -----------------------------------------------------
-- Table `mydatabase`.`user`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `mydatabase`.`user` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `username` VARCHAR(30) NOT NULL COMMENT 'Login username',
    `email` VARCHAR(64) NOT NULL COMMENT 'User email',
    `password` VARCHAR(128) NOT NULL COMMENT 'User password',
    `confirmed_at` DATETIME COMMENT 'User confirmation date',
    `active` BOOLEAN NOT NULL DEFAULT 1,
    `create_by` VARCHAR(32) NOT NULL,
    `create_time` TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `update_by` VARCHAR(32) NOT NULL,
    `update_time` TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `who_id` INT UNSIGNED NOT NULL,
    `inviter_id` INT UNSIGNED COMMENT 'If user was invited by someone, contains the ID of that user account, NULL otherwise',
    PRIMARY KEY (`id`),
    CONSTRAINT `fk_user_who_id`
        FOREIGN KEY(`who_id`)
        REFERENCES `mydatabase`.`user` (`id`)
        ON DELETE NO ACTION
        ON UPDATE NO ACTION,
    CONSTRAINT `fk_user_inviter_id`
        FOREIGN KEY(`inviter_id`)
        REFERENCES `mydatabase`.`user` (`id`)
        ON DELETE NO ACTION
        ON UPDATE NO ACTION
) Engine=InnoDB;

CREATE UNIQUE INDEX `unique_user_username` ON `mydatabase`.`user` (`username`);
CREATE UNIQUE INDEX `unique_user_email` ON `mydatabase`.`user` (`email`);

CREATE TRIGGER `user_before_insert` BEFORE INSERT ON `mydatabase`.`user`
FOR EACH ROW
    SET NEW.`create_by` = CURRENT_USER(),
        NEW.`update_by` = CURRENT_USER();

CREATE TRIGGER `user_before_update` BEFORE UPDATE ON `mydatabase`.`user`
FOR EACH ROW
    SET NEW.`update_by` = CURRENT_USER(),
        NEW.`update_time` = CURRENT_TIMESTAMP(3);

-- -----------------------------------------------------
-- Audit Table for user
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `mydatabase`.`audit_user` LIKE `mydatabase`.`user`;
ALTER TABLE `mydatabase`.`audit_user` ADD COLUMN `user_id` INT UNSIGNED NOT NULL AFTER `id`;
ALTER TABLE `mydatabase`.`audit_user` ADD     CONSTRAINT `fk_audit_user_user_id`
        FOREIGN KEY(`user_id`)
        REFERENCES `mydatabase`.`user` (`id`)
        ON DELETE NO ACTION
        ON UPDATE NO ACTION;

DROP INDEX `unique_user_username` ON `mydatabase`.`audit_user`;
DROP INDEX `unique_user_email` ON `mydatabase`.`audit_user`;

ALTER TABLE `mydatabase`.`audit_user` ADD     CONSTRAINT `fk_audit_user_who_id`
        FOREIGN KEY(`who_id`)
        REFERENCES `mydatabase`.`user` (`id`)
        ON DELETE NO ACTION
        ON UPDATE NO ACTION;
ALTER TABLE `mydatabase`.`audit_user` ADD     CONSTRAINT `fk_audit_user_inviter_id`
        FOREIGN KEY(`inviter_id`)
        REFERENCES `mydatabase`.`user` (`id`)
        ON DELETE NO ACTION
        ON UPDATE NO ACTION;
delimiter //
CREATE TRIGGER `audit_user_insert_trigger`
AFTER INSERT ON `mydatabase`.`user`
FOR EACH ROW
BEGIN
    INSERT INTO `mydatabase`.`audit_user` (
        `user_id`,
        `username`,
        `email`,
        `password`,
        `confirmed_at`,
        `active`,
        `create_by`,
        `update_by`,
        `who_id`,
        `inviter_id`
    ) VALUES (
        NEW.`id`,
        NEW.`username`,
        NEW.`email`,
        NEW.`password`,
        NEW.`confirmed_at`,
        NEW.`active`,
        NEW.`create_by`,
        NEW.`update_by`,
        NEW.`who_id`,
        NEW.`inviter_id`
    );
END;//
DELIMITER ;

delimiter //
CREATE TRIGGER `audit_user_update_trigger`
AFTER UPDATE ON `mydatabase`.`user`
FOR EACH ROW
BEGIN
    INSERT INTO `mydatabase`.`audit_user` (
        `user_id`,
        `username`,
        `email`,
        `password`,
        `confirmed_at`,
        `active`,
        `create_by`,
        `update_by`,
        `who_id`,
        `inviter_id`
    ) VALUES (
        NEW.`id`,
        NEW.`username`,
        NEW.`email`,
        NEW.`password`,
        NEW.`confirmed_at`,
        NEW.`active`,
        NEW.`create_by`,
        NEW.`update_by`,
        NEW.`who_id`,
        NEW.`inviter_id`
    );
END;//
DELIMITER ;