Iganinのブログ

日頃の開発で学んだ知見を中心に記事を書いています。

【Kotlin】Exposedのテーブル定義からテーブルスキーマ生成のSQL Scriptを作る

Kotlin Exposed 技術 Server

はじめに

下記の記事の続きです。 iganin.hatenablog.com

Exposedによってテーブル定義を比較的簡単に作成することができます。 また、作成したテーブル定義から実際にテーブルスキーマを作成することも可能です。

ただ、実際にサービスを開発する際はServerのコード実行時にテーブルスキーマ生成を行うのではなく、 DBに事前に生成しておいたり、Dockerでのcontainer初回起動時に作成することが多いと思います。

そこで本稿では、作成したテーブル定義から実際にテーブルスキーマを作成する方法、 および作成時のConsoleログ出力からSQL scriptを作成する方法を記載します。

書くこと

  • Exposedを使用したテーブル定義からテーブルスキーマを作成する方法
  • テーブル作成の実行時コンソールログ出力からSQL scriptを作成する方法

書かないこと

  • Exposedの導入方法
  • データベースとの接続方法

環境設定

以下の環境を使用しています。

  • Exposed 0.23.1

内容

対象テーブル

以下のテーブルを対象とします。

object Companies: Table("companies") {
    val id = long("id").autoIncrement()
    val name = varchar("name", 255)
    val createdAt = datetime("created_at")
    val updatedAt = datetime("updated_at")
    val deletedAt = datetime("deleted_at").nullable()
    override val primaryKey = PrimaryKey(id, name = "pk_company_id")
}

object DepartmentsEmployees: Table("departments_employees") {
    val id = long("id").autoIncrement()
    val departmentId = long("department_id").references(Departments.id)
    val employeesId = long("employee_id").index().references(Employees.id)
    val createdAt = datetime("created_at")
    val updatedAt = datetime("updated_at")
    val deletedAt = datetime("deleted_at").nullable()
    override val primaryKey = PrimaryKey(id, name = "pk_departments_employees_id")
}

object Departments: Table("departments") {
    val id = long("id").autoIncrement()
    val companyId = long("company_id").references(Companies.id)
    val name = varchar("name", 255)
    val createdAt = datetime("created_at")
    val updatedAt = datetime("updated_at")
    val deletedAt = datetime("deleted_at").nullable()
    override val primaryKey = PrimaryKey(id, name = "pk_department_id")
}

object Employees: Table("employees") {
    val id = long("id").autoIncrement()
    val familyName = varchar("family_name", 255)
    val givenName = varchar("given_name", 255)
    val createdAt = datetime("created_at")
    val updatedAt = datetime("updated_at")
    val deletedAt = datetime("deleted_at").nullable()
    override val primaryKey = PrimaryKey(id, name = "pk_employee_id")
}

Schemaの作成・削除

SchemaUtilsを使用することで、テーブル定義から実際のテーブルスキーマを作成することができます。 SchemaUtils.drop(テーブル名)でテーブルスキーマの削除を、　SchemaUtils.createでテーブルスキーマの作成を行うことができます。 SchemaUtils.drop(テーブル名)の際は外部制約の依存関係を考慮した削除が必要になります。例えば、上記のテーブル例では、 CompaniesはDepartmentsに参照されているため Departmentsを削除してからでなければ削除することはできません。

以下、実際のコードになります。

    transactions {
            // テーブルスキーマの削除
            SchemaUtils.drop(DepartmentsEmployees)
            SchemaUtils.drop(Departments)
            SchemaUtils.drop(Companies)
            SchemaUtils.drop(Employees)

           // テーブルスキーマの作成
            SchemaUtils.create(Companies)
            SchemaUtils.create(Employees)
            SchemaUtils.create(Departments)
            SchemaUtils.create(DepartmentsEmployees)
    }

実行されるSQL文の確認

SchemaUtils.drop実行時には実際は DROP TABLE IF EXITS table_nameというSQL文が実行されています。 addLogger(StdOutSqlLogger)を実行することでSQL文をConsoleの標準出力に表示させることができます。

例えば、先ほどのSchemaUtils.dropの実行結果は下記のように出力されます。

SQL: DROP TABLE IF EXISTS departments_employees
SQL: DROP TABLE IF EXISTS departments
SQL: DROP TABLE IF EXISTS companies
SQL: DROP TABLE IF EXISTS employees

ここからSQL scriptを作成するとします。SQL scriptを作成するに当たって、以下の対応が必要です。

  • SQL文以外のログの削除
  • SQL: の削除
  • 改行部分に ; を追加

上記を達成するために今回はCotEditorで正規表現を用いた置換処理を行います。

SQL Scriptの作成

SQL文以外のログの削除

CotEditorを開き、 command + Fなどで検索と置換画面を開きます。正規表現にチェックをつけ、 ^(?!.*XXX).*\nを入力します。 検索結果を空文字列に全て置換することで対応完了です。 f:id:Iganin:20200419102454p:plain

SQL: の削除

検索と置換画面でSQL:と入力し、すべてから文字列に置換します。

改行部分に ; を追加

改行文字列(\n)を入力し、 ;\nに全て置換します。 f:id:Iganin:20200419102739p:plain

以上までで作成されたファイルを .sql拡張子付きで保存します。 これで SQL scriptの作成は完了です。実際に先ほどのテーブル定義と実行コードから作成されたSQL scriptは下記のようになります。

DROP TABLE IF EXISTS departments_employees;
DROP TABLE IF EXISTS departments;
DROP TABLE IF EXISTS companies;
DROP TABLE IF EXISTS employees;
CREATE TABLE IF NOT EXISTS companies (id BIGSERIAL, "name" VARCHAR(255) NOT NULL, created_at TIMESTAMP NOT NULL, updated_at TIMESTAMP NOT NULL, deleted_at TIMESTAMP NULL, CONSTRAINT pk_company_id PRIMARY KEY (id));
CREATE TABLE IF NOT EXISTS employees (id BIGSERIAL, family_name VARCHAR(255) NOT NULL, given_name VARCHAR(255) NOT NULL, created_at TIMESTAMP NOT NULL, updated_at TIMESTAMP NOT NULL, deleted_at TIMESTAMP NULL, CONSTRAINT pk_employee_id PRIMARY KEY (id));
CREATE TABLE IF NOT EXISTS departments (id BIGSERIAL, company_id BIGINT NOT NULL, "name" VARCHAR(255) NOT NULL, created_at TIMESTAMP NOT NULL, updated_at TIMESTAMP NOT NULL, deleted_at TIMESTAMP NULL, CONSTRAINT pk_department_id PRIMARY KEY (id), CONSTRAINT fk_departments_company_id_id FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE RESTRICT ON UPDATE RESTRICT);
CREATE TABLE IF NOT EXISTS departments_employees (id BIGSERIAL, department_id BIGINT NOT NULL, employee_id BIGINT NOT NULL, created_at TIMESTAMP NOT NULL, updated_at TIMESTAMP NOT NULL, deleted_at TIMESTAMP NULL, CONSTRAINT pk_departments_employees_id PRIMARY KEY (id), CONSTRAINT fk_departments_employees_department_id_id FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE RESTRICT ON UPDATE RESTRICT, CONSTRAINT fk_departments_employees_employee_id_id FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE RESTRICT ON UPDATE RESTRICT);
CREATE INDEX departments_employees_employee_id ON departments_employees (employee_id);

まとめ

Exposedを使用してテーブル定義から実際のテーブルスキーマを削除したり作成したりする方法と、その実行結果のConsole出力から SQL Scriptを作成する方法を記載しました。

PostgresのDocker実行などの場合、 /docker-entorypoint-initdb.dにSQLファイルをおき、初期のテーブルスキーマ作成などを行うかと思います。 その際に使用するSQL scriptの作成などに本稿の方法がお役に立てれば幸いです。

参考