MySQL8 JSON SchemaでCHECK制約をつける

イタンジ株式会社でテックリードをしている大原です。普段は基盤系のプロダクト開発やSREを担当しています。

今回はMySQL8.0.17以降から使用できるJSON_SCHEMA_VALID関数と、この関数を使用したCHECK制約についてのお話をしようと思います。

また、弊社にはRubyOnRailsのプロダクトが多くありますが、Railsのマイグレーションで上記のCHECK制約を付けようとした際に困ったことがありましたので、それもあわせて共有させていただきます。

背景

用途にもよりますが、RDBでJSONを取り扱いたい場面というのは出てくると思います。

ただ、なんでも保存したいというわけではなくスキーマは保証したい、、、ということもありますよね。

アプリケーション上で書き込み前にチェックすることもできますが、RDB側で担保できるのであれば、それはそれで嬉しいです。

JSON_SCHEMA_VALID関数について

JSON_SCHEMA_VALID関数はMySQL8.0.17以降で使用可能な、JSON Schemaに対する値の検証を行うことが可能な関数で、検証結果を真偽値で返します。 (ここでいうJSON SchemaはDraft4の仕様のようです。)

簡単な例としてJSONの中に都道府県IDが入っていて、整数であること・必須条件(required)・最小値・最大値の検証を行ってみます。

SELECT VERSION();
-- 8.0.29

SET @schema = '{
  "type": "object",
  "properties": {
    "prefecture_id": {
      "type": "integer",
        "minimum": 1,
        "maximum": 47
     }
   },
   "required": [
     "prefecture_id"
   ]
}';

SELECT JSON_SCHEMA_VALID(@schema, '{ "prefecture_id": 13 }');
-- 結果は1です。

SELECT JSON_SCHEMA_VALID(@schema, '{ "prefecture_id": 48 }');
-- 結果は0です。

SELECT JSON_SCHEMA_VALID(@schema, '{}');
-- 結果は0です。

もちろん他の関数と同様にカラムに対しても実行することが可能です。

SELECT JSON_SCHEMA_VALID(@schema, column_name) FROM table_name;

ここのカラムはJSON型でないといけないわけではなく、TEXT型やVARCHAR型も有効です。

CHECK制約で活用する

CHECK制約についてもMySQL8.0.16以降から使用できる機能ですが、設定するブール式の評価がFALSEの場合に制約違反を発生させることができます(NULLの場合は制約違反にはなりません)。

JSON_SCHEMA_VALID関数もCHECK制約のブール式として使用することができるので、書き込み時にJSON Schemaによる検証ができそうです!

例として適切ではないかもしれませんが、ログをRDBにもそのまま保存しておくというケースを考えます。

CREATE TABLE application_logs (
  message JSON,
  CONSTRAINT application_logs_check_message CHECK(
    JSON_SCHEMA_VALID(
      '{
         "type": "object",
         "properties": {
           "timestamp": {
             "type": "string",
             "format": "date-time"
           },
           "message": {
             "type": "string"
           }
         },
         "required": ["timestamp", "message"]
      }',
      message
    )
  )
);

(CHECK制約内では変数を使用することができないので、JSON Schemaもインラインで渡す必要があります。)

検証してみましょう。

-- 失敗例
INSERT INTO application_logs (message) VALUES ('{ "timestamp": 1654045200, "message": "hello"}');
ERROR 3819 (HY000): Check constraint 'application_logs_check_message' is violated.

timestampをUNIXタイムスタンプで指定したので、エラーになりました。

-- 成功例
INSERT INTO application_logs (message) VALUES ('{ "timestamp": "2022-06-01T01:00:00Z", "message": "hello"}');
Query OK, 1 row affected (0.02 sec)

今度は問題なく保存できました。 良さそうです。

テーブル制約とカラム制約について

CHECK制約にはテーブル制約とカラム制約があります。

テーブル制約は任意のテーブルのカラムを参照できるのに対し、カラム制約は制約定義を含めたカラムのみを参照することができます。

テーブル制約については複数のカラムを参照することができるので、「カラムAとカラムBの一方のみに値が設定されていること」といった制約もかけることができます。

RubyOnRailsのマイグレーションでJSON_SCHEMA_VALID関数および、CHECK制約を使用する

Rails 6.1以降ですが、マイグレーションにcheck_constraintメソッドが追加されたので、こちらを使ってCHECK制約を定義してみます。

# frozen_string_literal: true

class CreateApplicationLogs < ActiveRecord::Migration[7.0]
  # @return [String]
  CHECK_MESSAGE_JSON_SCHEMA = JSON.generate(
    {
      type: 'object',
      properties: {
        timestamp: {
          type: 'string',
          format: 'date-time'
        },
        message: {
          type: 'string'
        }
      },
      required: %w[timestamp message]
    }
  ).freeze
  private_constant :CHECK_MESSAGE_JSON_SCHEMA

  # @return [void]
  def change
    create_table :application_logs, comment: 'Logs of application' do |t|
      t.json :message, null: false, comment: 'JSON of message'

      t.timestamps

      t.check_constraint(
        "JSON_SCHEMA_VALID('#{CHECK_MESSAGE_JSON_SCHEMA}', message)",
        name: 'application_logs_check_message_json_schema'
      )
    end
  end
end

rails db:migrateを実行後、ログに以下のSQLが流れており、

CREATE TABLE `application_logs` (`id` bigint NOT NULL AUTO_INCREMENT PRIMARY KEY, `message` json NOT NULL COMMENT 'JSON of message', `created_at` datetime(6) NOT NULL, `updated_at` datetime(6) NOT NULL, CONSTRAINT application_logs_check_message_json_schema CHECK (JSON_SCHEMA_VALID('{"type":"object","properties":{"timestamp":{"type":"string","format":"date-time"},"message":{"type":"string"}},"required":["timestamp","message"]}', message))) COMMENT 'Logs of application'

show create table application_logsでの確認も問題なさそうです!

(この場合のCHECK制約はテーブル制約になります。)

困ったこと

マイグレーション実行後に生成されるdb/schema.rbに問題がありそうでした。

create_table "application_logs", charset: "utf8mb4", collation: "utf8mb4_0900_ai_ci", comment: "Logs of application", force: :cascade do |t|
  t.json "message", null: false, comment: "JSON of message"
  t.datetime "created_at", null: false
  t.datetime "updated_at", null: false
  t.check_constraint "son_schema_valid(_utf8mb4\\'{\"type\":\"object\",\"properties\":{\"timestamp\":{\"type\":\"string\",\"format\":\"date-time\"},\"message\":{\"type\":\"string\"}},\"required\":[\"timestamp\",\"message\"]}\\',`message`", name: "application_logs_check_message_json_schema"
end

t.check_constraintの部分を見ると先頭(j)と末尾())の文字が削られてしまっていそうでした。。。 この状態では、rails db:schema:loadなどを実行した際にエラーが発生してしまいそうです(実はdb:schema:loadした際に気づきました)。

ちょっと調べた限りだと情報があまりなく、特にRailsのIssueも立っていなさそうかなと思いました。

Railsの実装を追ったところ、こちらの部分が関係ありそうです。

MariaDBかどうかによって処理が分岐していますが、MySQLで以下のようなCHECK制約はCHECK_CONSTRAINTSテーブルを見る限りはカッコ(())で囲われます。

CREATE TABLE examination_results (
  id bigint NOT NULL AUTO_INCREMENT PRIMARY KEY,
  score int unsigned NOT NULL,
  CONSTRAINT check_score CHECK (score <= 100)
);

SELECT check_clause FROM information_schema.check_constraints where constraint_name = "check_score";
-- (`score` <= 100)

JSON_SCHEMA_VALID等の関数を使用した場合はこのカッコが付かないのですが、それに関わらず先頭と末尾の文字列を削除する処理が入ってしまっているため、先ほどのようなschema.rbが生成されてしまったということのようです。。

また時間がある際にMariaDBとの違いやバージョンによる違い等も調査して、必要があればRails本体にIssueやPRを作りたいと思いますが、一旦は以下のようなパッチを作ることで手元では修正できるようにしました。

# lib/active_record_mysql_adapter_patch.rb
module ActiveRecordMysqlAdapterPatch
  def check_constraints(table_name)
    # 省略
          remove_parentheses = !mariadb? && row['expression'].match?(%r{\A\(.+\)\z})
          expression = remove_parentheses ? row['expression'][1..-2] : row['expression']
    # 省略
  end
end

# Rakefile
desc 'fix db schema'
task 'db:schema:dump:fix': :environment do
  require Rails.root.join('lib/active_record_mysql_adapter_patch')
  require 'active_record/connection_adapters/abstract_mysql_adapter'

  ActiveRecord::ConnectionAdapters::AbstractMysqlAdapter.prepend(ActiveRecordMysqlAdapterPatch)
  Rake::Task['db:schema:dump'].invoke
end

既存のタスクに極力影響を与えたくなかったため、独自に定義したタスクを実行する場合に限ってパッチをprependし、その中で通常のdb:schema:dumpを実行するようにしました。手動で実行する必要はありますが、必要があれば自動化することもできそうです。

まとめ

JSON_SCHEMA_VALID関数およびCHECK制約を使用することで、データベースレベルでJSONのスキーマ検証を行うことができるとわかりました。

今のところRailsで扱う際には少し気を使う部分もありそうですが、使い所があれば活用していきたいと思います。

参考