Help us understand the problem. What is going on with this article?

mysqlのテーブル定義からJSON Schemaを生成する

More than 3 years have passed since last update.

Node.js+mysqlでRESTfulなAPIを作っているときに思った。
変なデータをDBに突っ込みたくない。

そうだ、JSON Schemaだ。

カラム名のチェックやら最大長ぐらいの簡単なチェックならこれでできる。
DBの型情報を読み取ってJSON Schemaを自動生成できるようにしよう。

mysql => JSON Schemaの自動生成を設計する

テーブルごとのJSON Schemaを生成したい。
とりあえず列名と最大長、必須あたりがあればいいや。

mysqlの接続情報を渡せばあと勝手にやってくれる感じにしたい。

// Mysql connect config.
var config = {
    user: 'root',
    password: 'my_password',
    host: 'localhost',
    database: 'my_db'
};
// Get spec for connected database
mysqlspec(config, function (err, schema) {
    console.log("schema=" + JSON.stringify(schema, null, 4));
});

で実行して、以下のような結果が欲しい。

schema = { // Schemas for in "my_db" database.
    'TEST_SHOP': { // Schema for in "my_db.TEST_SHOP" table.
        name: 'my_db.TEST_SHOP',
        properties: {
            id: {type: 'integer', maxLength: 5},
            article: {type: 'integer', maxLength: 4},
            dealer: {type: 'string', maxLength: 20},
            price: {type: 'number'}
        },
        required: ['id', 'article', 'dealer', 'price'],
        additionalProperties: false
    },
    'TEST_PERSON':{
        /**...*/
    }
};

mysql => JSON Schemaの自動生成を実装する

まず、mysqlテーブルからテーブル定義を取る必要がある。

node-mysqlを利用して、

SHOW DATABASES;
SHOW TABLES IN <database_name>;
DESC <table_name>;

あたりを実行するようにしよう。

"use strict";

var mysql = require('mysql'),
    argx = require('argx');

/** @constructor */
function MysqlDescriber(config) {
    var s = this;
    s._config = config;
}

MysqlDescriber.prototype = {
    _connection: undefined,
    /**
     * Connect to database.
     * @returns {MysqlDescriber} - Returns self.
     */
    connect: function () {
        var s = this;
        s._connection = mysql.createConnection(s._config);
        return s;
    },
    /**
     * Disconnect from data base.
     * @returns {MysqlDescriber} - Returns self.
     */
    disconnect: function () {
        var s = this;
        if (s._connection) {
            s._connection.end();
        }
        return s;
    },
    /**
     * Execute a sql.
     * @param {string} sql - SQL String to execute.
     * @param {string[]} [values] - Values to apply
     * @param {function} callback - Callback when done.
     * @returns {MysqlDescriber} - Returns self.
     */
    execute: function (sql, values, callback) {
        var args = argx(arguments);
        callback = args.pop('function');
        sql = args.shift();
        values = args.remain() || [];
        var s = this;
        s._connection.query(sql, values, callback);
        return s;
    },
    /**
     * Show databases.
     * @param {function} callback - Callback when done.
     * @returns {MysqlDescriber} - Returns self.
     */
    showDatabases: function (callback) {
        var s = this;
        s.execute("SHOW DATABASES;", callback);
        return s;
    },
    /**
     * Show tables.
     * @param {string} database - Name of database.
     * @param {function} callback - Callback when done.
     * @returns {MysqlDescriber} - Returns self.
     */
    showTables: function (database, callback) {
        var s = this;
        var sql = ["SHOW TABLES IN", database].join(' ');
        s.execute(sql, callback);
        return s;
    },
    /**
     * Desc table.
     * @param {string} database - Name of database.
     * @param {string} table - Name of table.
     * @param {function} callback - Callback when done.
     * @returns {MysqlDescriber} - Returns self.
     */
    descTable: function (database, table, callback) {
        var s = this,
            sql = ["DESC", [database, table].join('.')].join(' ');
        s.execute(sql, callback);
        return s;
    }
};

module.exports = MysqlDescriber;

テーブル定義がとれたら後は、中身をごにょごにょ変換すればよし。

DESCコマンドで取れたカラム定義は

  { Field: 'last_name',
       Type: 'varchar(255)',
       Null: 'YES',
       Key: '',
       Default: null,
       Extra: '' },

みたいな感じでくるから、
解釈用の関数をあれこれ用意する。

function _parseRequired(col) {
        return col.Null === 'NO';
}

function _parseType(col) {
    var s = this;
    var type = col.Type;
    if (!type) {
        return undefined;
    }
    var mysqlType = type.replace(/\([\d,]+\)/, '').toUpperCase().split(' ').shift();
    switch (mysqlType) {
        case 'LONGTEXT':
        case 'MEDIUMTEXT':
        case 'TEXT':
        case 'TINYTEXT':
        case 'VARCHAR':
        case 'CHAR':
            return 'string';
        case 'TINYINT':
        case 'SMALLINT':
        case 'MEDIUMINT':
        case 'INT':
        case 'BIGINT':
            return 'integer';
        case 'FLOAT':
        case 'DOUBLE':
        case 'DECIMAL':
        case 'NUMERIC':
            return 'number';
        case 'DATE':
        case 'DATETIME':
            return 'date';
        case 'LONGBLOB':
        case 'MEDIUMBLOB':
        case 'TINYBLOB':
            return 'string';
        default:
            return mysqlType;
    }
}
function _parseMaxlength(col) {
    var type = col.Type;
    if (!type) {
        return undefined;
    }
    var matched = type.match(/\((\d+)\)/);
    return matched ? Number(matched.pop()) : undefined;
}
function _cleanEmptyProperty(data) {
    Object.keys(data).forEach(function (key) {
        var isEmpty = (key === undefined) || (key === null) || (key === '');
        if (isEmpty) {
            delete data[key];
        }
    });
    return data;
}

npmモジュールにまとめて公開

あとはいい感じに処理をかまして

  • データベース全体取得
  • 特定のテーブル取得

みたいなバリエーションをつけた関数を用意してテストを書いていつも通りちゃちゃっと公開

npm install mysqlspec --save-dev

でインストールして、

var mysqlspec = require('mysqlspec');

// Mysql connect config.
var config = {
    user: 'root',
    password: 'my_password',
    host: 'localhost',
    database: 'my_db'
};
// Get spec for connected database
mysqlspec(config, function (err, schema) {
    console.log("schema=" + JSON.stringify(schema, null, 4));
});

とやると、

schema = { // Schemas for in "my_db" database.
    'TEST_SHOP': { // Schema for in "my_db.TEST_SHOP" table.
        name: 'my_db.TEST_SHOP',
        properties: {
            id: {type: 'integer', maxLength: 5},
            article: {type: 'integer', maxLength: 4},
            dealer: {type: 'string', maxLength: 20},
            price: {type: 'number'}
        },
        required: ['id', 'article', 'dealer', 'price'],
        additionalProperties: false
    },
    'TEST_PERSON':{
        /**...*/
    }
};

みたいなのがわらわらでてくる。

mysqlのテーブルからカラム定義を取ってくるところ(JSONSchemaへの変換前までの処理)は他でも使えそうなので
別のnpmパッケージにした。

Links

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした