はじめに
PostgreSQL前提で、データ作成用ユーティリティを作っていますので頭出しで使い方と実装内容の紹介をします。
バックエンドのデータベースとの結合テストをする際の、テストデータ作成での利用を想定しています。
入力はみんな大好きExcelで編集したTSVファイル、出力はPostgreSQLのUPSERT文（INSRETとUPDATEを同時に行える文）のSQLテキストファイルです。なぜUPSERT文かというと何度も実行しても同じデータを作る冪等性を持たせるためです。
現在、200行と少しのコード量ですが、実際に使ってみて結構便利でした。
しかし、まだ細かい点でいえば、データの改行に対応する、IDが無いテーブルへの対応するなどができていません。
SQL生成部分の実装を変えればUPSERT文をサポートしている他のデータベースへも、簡単に切り替えられると思います。
使い方
generate-upsert-postgres リポジトリから、git cloneし、npm install してビルドします。
その後、コマンドプロンプトから以下を実行します。
npm run start -- <入力用TSVファイル>
入力用TSVファイルのフォーマット
ヘッダー行は4行あります。
- 1行目：テーブル名 + "=" + IDカラム名をカンマ区切り
テーブル名を書き、イコールの後に、INSERT時のコンフリクトを確認するためのIDカラム名をカンマ区切りで指定します。 - 2行目：コメント行です。カラム名のコメントを記載する用途で使います。
- 3行目：カラムIDを記載します。
- 4行目：メタ情報を記載します。
メタ情報は後述しますが、基本的に、カラムの型情報（serial, number, string, datetime, bool）などを指定します。
ヘッダー行の情報はUPSERT文を作る際に利用され、5行目以降はデータ行として、UPSERT文の値に使われます。
1シート、基本的には1テーブルで書きますが、テーブル間のリレーションが１：１であれば複数テーブルを書くこともできます。
以下のように、peopleテーブルとprofileテーブルは、peopleIDで１：１になるため、1シートに2テーブルの情報を記載しています。もちろん、1シートに3テーブル以上の定義も書くことができます。
メタ情報の種類
カラムのメタ情報の種類は、下記のコードのように分別しています。
メタ情報は基本的に値をシングルクォート''でくくるか、未定義にnullにするかで使っています。
特殊なメタ情報として、以下の3つがあります
- fix=＜値＞：データ行の値によらず固定値を設定します 例：fix=TRUE, fix='2024/12/14 01:23:45'
- ref=＜1から始まる列数＞：外部キーで利用することを想定。ref=3なら、3列目の値を参照して設定します
- refstr=＜1から始まる列数＞：refと同じ仕様ですが、SQLにするとき、値をシングルクォートでくくります
switch (type) {
case "string":
sqlValue = `'${value}'`;
break;
case "datetime":
if (!value) {
sqlValue = "null";
} else {
sqlValue = `'${value}'`;
}
break;
case "serial":
// 必須とみなす
break;
case "double":
if (!value) {
sqlValue = "null";
}
break;
case "int":
if (!value) {
sqlValue = "null";
}
break;
case "bool":
sqlValue = value.toUpperCase() === "TRUE" ? "true" : "false";
break;
case "refstr":
sqlValue = `'${values[Number(typeOption) - 1]}'`;
break;
case "ref":
sqlValue = `${values[Number(typeOption) - 1]}`;
break;
case "fix":
sqlValue = `${typeOption}`;
break;
実装内容
3ファイルから構成されています。
- classes.ts ... シンプルな型を定義
- functions.ts ... ヘッダーの検証関数とヘッダーのロード関数
- main.ts ... TSVを読み込み、データの加工を行い、SQLに変換する処理を行う
classes.tsは純粋なオブジェクト指向にのっとった構造を定義していて、以下のように実装されています。
SQLRecord::toSQL() 関数の実装がPostgreSQLのUPSERT文作成になっています。
もし、ほかのデータベースで同様のユーティリティを作りたい場合は、そこを書き換えるとよいでしょう。
export class TableDef {
name: string = "";
idColumns: string = "";
columnDefs: ColumnDef[] = [];
}
export class ColumnDef {
name: string = "";
type: string = "";
index: number = 0;
}
export class SQLRecord {
tableName: string;
idColumns: string;
items: SQLRecordItem[] = [];
constructor(tableName: string, idColumns: string) {
this.tableName = tableName;
this.idColumns = idColumns;
}
toSQL(): string {
const columns: string[] = [];
const values: string[] = [];
const sets: string[] = [];
this.items.forEach((item) => {
columns.push(`"${item.columnName}"`);
values.push(item.sqlValue);
sets.push(`"${item.columnName}" = ${item.sqlValue}`);
});
return (
`INSERT INTO "${this.tableName}" (${columns.join(", ")}) \n` +
` VALUES (${values.join(", ")})\n` +
` ON CONFLICT (${this.idColumns
.split(",")
.map((id) => `"${id.trim()}"`)
.join(",")})\n` +
` DO UPDATE SET ${sets.join(", ")};`
);
}
}
export class SQLRecordItem {
columnName: string;
sqlValue: string; //引用符で括ったり、nullにしたりする
constructor(columnName: string, sqlValue: string) {
this.columnName = columnName;
this.sqlValue = sqlValue;
}
}
function.tsはひたすらヘッダーをTableDefとColumnDefに変換する関数があるだけですのでコードの抜粋はしません。
main.tsで全体の管理と上で抜粋したように、SQLRecordとSQLRecordItemを作っています。
おわりに
以上が使い方と解説になります。
シンプルな実装量ですが、結構便利なユーティリティになっていると思っています。