まだプログラマーですが何か?

プログラマーネタ中心。たまに作成したウェブサービス関連の話も https://twitter.com/dotnsf

タグ:postgres

我ながら分かりにくいタイトルになってしまいましたが、やりたいことはこういうことです:

データベースに Postgres を使っているケースで、テーブルのある bigint 列にそのレコードの作成日時が記録されているものとします(これ自体はそこまで珍しくないと思っています):
create table items( id varchar(50) primary key, name varchar(100), created bigint );

上の例では items テーブルの "created" 列が bigint 型で定義されていて、このレコードが作成された日時の(ミリ秒単位の)タイムスタンプ値が格納されているものとします。

そしてこの items テーブルに格納されているレコードから、created の値が(例えば)1月17日のものだけを取り出す、というクエリーを実行するにはどのような SQL にすればよいか、という命題です。

要は facebook の思い出(過去のこの日)機能のような「何年か前の今日と同じ日付に作成したデータ」を取り出したくなることがあるのです。年や時分秒は違ってもいいので、月と日だけが一致している過去のデータを取り出したい、というケースです。日付が文字列で記録されていれば部分一致検索すればいいだけなので難しくはないと思いますが、これを bigint 型のタイムスタンプで格納されている中で実現するにはどのような SQL を実行すればよいか? というのがやりたいことでした。

で、その方法です。PostgreSQL には to_char() という組み込み関数が存在していて、この関数を使うと(PostgreSQL の)タイムスタンプ値をフォーマットを指定した文字列に変換することができます。また to_timestamp() という組み込み関数もあり、こちらは bigint などの値を(PostgreSQL の)timestamp 型に変換してくれます。

この2つの関数を併用して、例えば、
select id, name, created from items where to_char( to_timestamp( created / 1000 ), 'MM-DD' ) = '01-17' order by created desc

のように実行すると、
  • まずミリ秒単位の created が created / 1000 によって( timestamp 型と同じ)秒単位になり、
  •  to_timestamp( created / 1000 ) によってタイプスタンプ型に変換され、更に、
  •  to_char( to_timestamp( created / 1000 ), 'MM-DD' ) によってタイムスタンプ値が '月-日' というフォーマットの文字列に変換される
といった一連の処理が実行されます(上の SQL 例では、その結果が '01-17' となるレコードの id と name と created を、 created の新しい順に取り出す、という処理をしています)。


さらにおまけを。上の SQL は正しく実行できるのですが、タイムスタンプ値は UTC 時間で計算されるので、このままだと「UTC 時間で1月17日」のデータを取り出すことになります。

これを「日本時間で1月17日」のデータを取り出す場合は9時間のオフセットを考慮して、
select id, name, created from items where to_char( to_timestamp( created / 1000 ) + 9 * 3600, 'MM-DD' ) = '01-17' order by created desc

のように実行することで実現できます。後はこの "9" の部分をプログラムで動的に取得するとか、外部からパラメータ指定できるようにすれば色々なロケールでの過去の同じ月日のデータを取り出すことができる、ということになります。


自作アプリに思い出機能を実装しようとする時に役立つ情報・・・だと思ってます。


先日、このようなブログを書きました:
"Image to Excel" を REST API 化(xlsx-js-style の使い方)


xlsx-js-style というライブラリを使って、ネットでプチバズった Image to Excel というツール(を REST API 化したもの)を独自実装した、という紹介エントリでした。

この REST API を作る上で xlsx-js-style というエクセルシートやエクセルブックファイルを Node.js で作成・編集するライブラリを使いました。Image to Excel を作るだけであればセルのサイズや背景色を設定する機能を使えばできるので(セルのテキストを読み書きする必要はなかったので)さほど難しくありませんでした。

というわけで、その勉強の続きという意味も含め、より実践的なセルの読み書きを伴うサービスを作ってみようと思い立ち、苦労しながらブログネタになりそうなものを作ることができたので紹介させていただきます。


【作ったサービスの内容】
一言でいうと「データベースとエクセルの相互変換ツール」です。データベースをエクセルファイルに、エクセルファイルはデータベースに、それぞれ変換して記録(保存)するツール。正確にはツールというよりも REST API として実装しているので、別の外部アプリケーションから使うことを想定しています(Swagger API の UI は標準で用意したので、単体でも動かしてみせることはできます)。

作ったサービスをもう少し詳しく説明します。まず「データベースをエクセルにする」部分から、現時点では PostgreSQL か MySQL だけで動くのですが、こんな感じのデータベース接続文字列を指定して API を実行します:
 (PosgreSQL の場合)postgres://username:password@servername:port/dbname
 (MySQL の場合)  mysql://username:password@servername:port/dbname


PostgreSQL の場合は最初のプロトコル名部分を "postgres" に、MySQL の場合は "mysql" と指定します。"username:password" 部分は接続する際のユーザー名(username)とパスワード(password)を指定します。servername はデータベースサーバー名(または IP アドレス)で、port は接続ポート番号です(PostgreSQL のデフォルト値は 5432 、MySQL のデフォルト値は 3306 です)。そして dbname は接続先のデータベース名を指定します。

例えば localhost の 5432 番ポートで動いている PostgreSQL サーバーの mydb データベースに、ユーザー名 user1 、パスワード pass1 で接続する場合は以下のようなデータベース接続文字列が必要となります:
 postgres://user1:pass1@localhost:5432/mydb


話を戻します。このようなデータベース接続文字列を指定して REST API を実行すると、以下のルールでデータベース内容をまるごと1つのエクセルファイルに変換します:
・データベース内の全テーブルと、各テーブル内のレコードデータを取り出す
・各テーブルが1枚のエクセルシート(タブ)になる テーブル名がシート名になる
・シートの1行目にテーブルの全列が書き出される
・シートの2行目以降にテーブル内の全レコードデータが1行ずつ格納される(ただし BLOB などのバイナリデータは無視する)
・処理が完了すると、(データベース名).xlsx という名前のエクセルファイルでダウンロードできる

という誰得(?)な処理を行います。 もう1つの「エクセルをデータベースにする」は基本的にこの逆の処理を行います:

・実行時のパラメータとして(上述の処理と同様に)データベース文字列を指定する
・併せてエクセルファイルをアップロードする形で REST API を実行する
・エクセルファイル内の全シートを1つのテーブル内容としてデータベースに格納する
 ・テーブル名はシート名
 ・各シートの1行目にテーブルの列名が格納されているとみなす
 ・各シートの2行目以降にテーブルのレコードデータが1行ずつ格納されているとみなし、
  テーブルに1行ずつ追加される(ただし BLOB などのバイナリデータは無視する)
・REST API 実行時のパラメータにより各シートの2行目にある情報はレコード情報ではなく、各列の定義情報とみなすこともできる。その場合、3行目以降にテーブルのレコードデータが格納されているとみなす。
・同様にして REST API 実行時のパラメータによりシート名の既存テーブルを削除(drop table)してから実行するか、新規作成(create table)してから実行するかを選択することができる
・処理が完了するとエクセル内の各シートに定義されていた情報がまるごとデータベース化される


【ソースコード】
作成したサービスのソースコードをこちらで公開しています:
https://github.com/dotnsf/db2xlsx


実際にネット上のどこかでサービスとして公開することも考えたのですが、現実問題としてデータベースがインターネット上に公開されていないと使えないので(普通、公開されていないはずなので)、公開しても使えるものではないと判断しています。 そういうこともあってのソースコードでの公開としました。

サービスとして自分の PC で使ってみたい場合は、Git と Node.js がインストールされた環境で以下の手順を実行してください:

(1)ターミナルやコマンドプロンプトなどの端末を開く

(2)ソースコードをダウンロード
$ git clone https://github.com/dotnsf/db2xlsx


(3)必要なライブラリをインストール
$ cd db2xlsx

$ npm install


(4)実行(終了時は Ctrl+C)
$ npm start


ただ単にサービスとして実行しても対象のデータベースやエクセルファイルが無いと使えないので、より具体的な手順を次のセクションで紹介します。


【Docker と Swagger API で実際に使う手順】
このサービスを実際に使うにはインポートやエクスポートの対象となるデータベースや、エクセルファイルが必要です(そういうサービスです)。エクセルファイルはサンプルを用意できるのですが、データベースはそうはいきません。 実際に使える PostgreSQL や MySQL のデータベースがあればそれをそのまま使っていただいてもいいのですが、そういう人も多くはないと思うので、以下に Docker を使ってデータベースを用意する手順を紹介します。というわけでまずは Docker(個人利用であれば Docker Desktop がお奨めですが、業務利用の場合は有料なので、無料を希望する場合は WSL などを使って Community Edition)をインストールしておいてください。


Docker で PostgreSQL データベースを1つ用意する場合は以下を実行します:
$ docker run -d --name postgres -p 5432:5432 -e POSTGRES_USER=user1 -e POSTGRES_PASSWORD=pass1 -e POSTGRES_DB=db1 postgres

↑このコマンドで作成した場合、ユーザー名 user1 、パスワード pass1 、データベース名 db1、ポート番号 5432 番で postgres という名前の PostgreSQL コンテナが起動します。この例だとデータベース接続文字列は "postgres://user1:pass1@localhost:5432/db1" となります。

データベースを2つ作る必要はないので PostgreSQL で作成した場合はそれを使えばいいのですが、どうしても MySQL を使いたい場合はこちらを実行します:
$ docker run -d --name mysql -e MYSQL_ROOT_PASSWORD=P@ssw0rd -e MYSQL_USER=user1 -e MYSQL_PASSWORD=pass1 -e MYSQL_DATABASE=db1 -p 3306:3306 mysql:5.7 --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci

この場合だとデータベース接続文字列は "mysql://user1:pass1@localhost:3306/db1" となります。ユーザー名部分やパスワードなどは適宜変更して読み替えてください。


この方法を含め、なんらかの方法で接続して読み書き可能なデータベースが用意できているものと仮定して以下の説明を続けます。


データベースが稼働している状態で上述の(4)までを実行して本ツールを起動します(本ツール起動後にデータベース起動でも構いません):
$ npm start

> db2xlsx@0.0.1 start
> node app.js

server starting on 8080 ...

↑の青字のような表示が出力されれば起動に成功しています。ツールは 8080 番ポートで稼働しているので、ウェブブラウザで http://localhost:8080/_doc にアクセスして Swagger API Document を開きます:
2023122201


↑のような画面が表示されます。この画面でも "POST /db2xlsx" と "POST /xlsx2db" の2つの REST API が存在していることが確認できます。名前の通りですが、それぞれ「データベースからエクセル」、「エクセルからデータベース」に変換する API になっています。


これらの API をそれぞれ使ってみましょう。順番としてはデータベースは空の状態であると想定して、
(1)まずエクセルファイルの内容をデータベースにインポート(POST /xlsx2db)して、
(2)そのデータベースの内容をエクセルファイルにエクスポート(POST /db2xlsx)する
という順に実行してみることにします。

まずはデータベースにインポートするエクセルファイルを用意します。独自のものを準備いただいてもいいのですが、すぐ使えるようなサンプルを用意しておきました:
https://dotnsf.blog.jp/items-brands.xlsx


↑このリンクからエクセルファイル items-brands.xlsx をダウンロードしてください。エクセルや互換ツールを使って開ける場合は内容を確認してみてください(自由に変更していただいても構いません):
2023122301

2023122302


この items-brands.xlsx ワークブックには items と brands という2つのシート(タブ)があります。それぞれ以下のようなデータが表形式で格納されています:

(items)
id name brand_id price body updated
varchar(20) primary key varchar(100) not null varchar(20) integer text timestamp
item001 商品1 brand001 100 商品説明1商品説明1商品説明1商品説明1商品説明1商品説明1商品説明1商品説明1商品説明1商品説明1商品説明1商品説明1 2023-12-20
item002 商品2 brand002 1200 商品説明2商品説明2商品説明2商品説明2商品説明2商品説明2商品説明2 2023-12-21
item003 商品3 brand003 500 商品説明3商品説明3商品説明3商品説明3商品説明3商品説明3商品説明3商品説明3商品説明3商品説明3商品説明3商品説明3商品説明3商品説明3 2023-12-22
item004 商品4 brand002 2980 商品説明4商品説明4商品説明4商品説明4商品説明4商品説明4 2023-12-23


(brands)
id name body url updated
varchar(20) primary key varchar(100) not null text varchar(100) timestamp
brand01 ブランド1 説明文1説明文1説明文1説明文1説明文1説明文1説明文1説明文1説明文1説明文1説明文1説明文1説明文1 https://www.brand1.com 2023-12-20
brand02 ブランド2 説明文2説明文2説明文2説明文2説明文2説明文2説明文2説明文2説明文2 https://www.brand2.net 2023-12-21
brand03 ブランド3 説明文3説明文3説明文3説明文3説明文3説明文3説明文3説明文3説明文3説明文3説明文3説明文3説明文3説明文3説明文3説明文3説明文3 https://www.brand3.jp/ 2023-12-22


エクセルファイルが用意できたらこれをデータベースにインポートしてみましょう。先ほどのブラウザ画面内の "POST /xlsx2db" と書かれた箇所をクリックします:
2023122303


すると下のような隠れていた画面が展開されます。この API を実行する時に指定するパラメータに関する情報が表示されています。内容を確認し、そのまま実行したいので "Try it out" と書かれたボタンをクリックします:
2023122304


するとパラメータ部分が編集可能状態に切り替わります。ここに必要な情報を入力していきます:
2023122305


まず一番上の database_url パラメータは指定が必須になっています。ここにはデータベース接続文字列を入力します。上の方法で PostgreSQL データベースを起動した場合であれば、
 postgres://user1:pass1@localhost:5432/db1
また MySQL データベースを起動した場合であれば、
 mysql://user1:pass1@localhost:3306/db1
を指定してください(別の方法で起動したデータベースの場合はそのデータベースに接続するための接続文字列を指定してください)。


また一番下の「エクセルワークブックファイル」と書かれた file パラメータも必須入力です。ここは「ファイルを選択」ボタンをクリックしてから、先ほど用意したワークブックファイル(ダウンロードしたものを使う場合は items-brands.xlsx )を指定します。

それ以外の部分は必要に応じて指定するものですが、今回指定したワークブックファイルは各シートの2行目にテーブルの列定義が記載されています。このような場合(シートの2行目がレコード情報ではなく、列定義情報の場合)は col_attr パラメータに "1" という値を指定する必要があります(何も指定しない場合は2行目もデータレコードとみなされて insert into table が実行されます。またテーブルの各列は全て text 型として create table が実行されます)。

また今回は初回実行なのでまだデータベース内にテーブルは何も作成されていません。したがって create table を実行してから insert into table を実行する必要があります。その場合は create_table パラメータに "1" を指定してください。今回は指定しませんが drop_table パラメータに "1" をセットして実行すると create table の前に drop table が実行されます。定義の異なる同名のテーブルが作成されている可能性があって、そちらを削除してから実行する場合や、現在登録済みのデータを削除してから再実行したい場合は drop_table の方にも "1" を指定してください。

パラメータの指定が完了したら最後に青い "Execute" ボタンを実行すると xlsx2db 処理が実行されます:
2023122601


実行後、ボタンの下のほうに実行結果が表示されます。またこの REST API を実行した時のコマンド内容も表示されるので、この REST API を外部から利用する機会があれば参考にしてください:
2023122307


とりあえず実行は完了し、成功しているようです。 せっかくなので実際にデータベースにアクセスして成功した結果を確認してみましょう。

ターミナルやコマンドプロンプトなどの端末をもう1つ開いて、CLI からデータベースに接続します。上述のような Docker でデータベースを起動している場合はそのコンテナに直接ログインして調べることができます。

データベースが PostgreSQL の場合は、以下のようなコマンドで確認できます(赤字が入力内容、青字は説明用のコメントです。実際に入力しないでください):
$ docker exec -it postgres bash (データベースが動いているコンテナにログイン)

/# psql "postgres://user1:pass1@localhost:5432/db1" (接続文字列を指定して psql コマンドでログイン)

db1=# \dt (テーブル一覧を表示、brands テーブルと items テーブルが作成されていることを確認)

        List of relations
 Schema |  Name  | Type  | Owner
--------+--------+-------+-------
 public | brands | table | user1
 public | items  | table | user1

db1=# select * from brands; (brands テーブルの内容を確認し、エクセルに入力されていた内容が格納されていることを確認)

   id    |    name    |
           body
   |          url           |       updated
---------+------------+--------------------------------------------------------
-------------------------------------------------------------------------------
---+------------------------+---------------------
 brand01 | ブランド1 | 説明文1説明文1説明文1説明文1説明文1説明文1説明文
説明文1説明文1説明文1説明文1説明文1説明文1
  | https://www.brand1.com | 2023-12-20 00:00:00
 brand02 | ブランド2 | 説明文2説明文2説明文2説明文2説明文2説明文2説明文
説明文2説明文2
  | https://www.brand2.net | 2023-12-21 00:00:00
 brand03 | ブランド3 | 説明文3説明文3説明文3説明文3説明文3説明文3説明文
説明文3説明文3説明文3説明文3説明文3説明文3説明文3説明文3説明文3説明文
 | https://www.brand3.jp/ | 2023-12-22 00:00:00
(3 rows)

db1=# select * from items; (items テーブルも同様に確認する)

(略)

db1=# \q (psql からログアウトする)

/# exit (コンテナからもログアウトする)

データベースが MySQL の場合も、コマンドが少し異なりますが同様に確認できます:
$ docker exec -it mysql bash (データベースが動いているコンテナにログイン)

# mysql -u user1 -ppass1 db1 --default-character-set=utf8mb4" (接続情報を指定して mysql コマンドでログイン)

mysql> show tables; (テーブル一覧を表示、brands テーブルと items テーブルが作成されていることを確認)

+--------------+
| Tables_in_db |
+--------------+
| brands       |
| items        |
+--------------+
2 rows in set (0.01 sec)

mysql> select * from brands; (brands テーブルの内容を確認し、エクセルに入力されていた内容が格納されていることを確認)

(略)

mysql> select * from items; (items テーブルも同様に確認する)

(略)

mysql> quit (mysql からログアウトする)

# exit (コンテナからもログアウトする)

エクセルファイルをアップロードして、データベース化できることが確認できました。


では次に「データベースからエクセル」も試してみましょう。上の作業でエクセルファイルからデータベースを作成した直後だと、アップロードしたものと同じエクセルファイルが作成されるだけ(苦笑)ではあるのですが、データベースの内容がエクセルになる、という部分については体験できると思います。

Swagger API ドキュメントに戻り、今度は "POST /db2xlsx" のボタンをクリックします:
2023122301


API 実行時のパラメータが展開されます。今回は "POST /xlsx2db" の時よりもシンプルなパラメータになっていることがわかります。"Try it out" ボタンをクリックします:
2023122302


こちらの API ではエクセルにエクスポートしたいデータベースを database_url パラメータにデータベース接続文字列の形で指定するだけです。先ほどと同様のデータベース接続文字列を入力して "Execute" ボタンをクリックします:
2023122303


処理が成功すると "Download file" と書かれたリンクが現れます。ここをクリック:
2023122304


すると、(データベース名).xlsx というエクセルファイルがダウンロードされます。ダウンロード後に開いてみてください:
2023122305


ダウンロードしたエクセルファイルを開いてみると、items と brands という2つのワークシート(タブ)が含まれていて、それぞれのワークシート内に items テーブルと brands テーブルの内容がエクスポートされていることが確認できます。日付列は日付情報として取り出しているので少しフォーマットが変わっていますが中身(日付は)正しいはず:
2023122306

2023122307


データベースからエクセル、の REST API も正しく動くことが確認できるはずです。


【あとがき的なもの】
一部でエクセルがデータベースの代わりに使われていることを知っている身として、その状況はなんとかならんかという思いで作ってみました。REST API 化したので簡単なプログラムを書けば一括処理もできるはずです(笑)。で、どうしてもまたエクセル化する必要が生じた場合にも対応できるような逆方向の REST API も用意しているので、これでなんとかしてください。


 

自作サービスを開発している途中で調査した内容のアウトプットです。

Node.js を使って PostgreSQL データベースを操作する場合、pg(node-postgres) ライブラリを使うのが定番だと思っています。実際これまで何度も使ってきているし、データの読み書き更新削除といった作業で特に困ったことはありませんでした。

しかし今回ちょっとしたことで詰まってしまいました。結果的には解決できたのですが、データベース内に定義されたテーブルの、列の定義情報を調べたいと思った時に「これどうやるんだろ?」となってしまいました。

もう少し具体的に説明します。例えば以下のような SQL を使って items テーブルを作成したとします:
CREATE TABLE items( id varchar(50) primary key, name varchar(100) not null, price int default 0, body text, image bytea, datetime timestamp );

このようにして作成した items テーブルの各列の定義情報(上の例の青字部分)を取り出す方法が分からなかったのでした(列名だけであれば select 文の実行結果の中に含まれるので、1行でもレコードが登録されていればそこから分かる、ということは知っていました。が、レコードが1件も登録されていないケースだったり、列名以外の型の情報まで必要な場合の取得方法が分かっていませんでした)。ちなみにこの情報は psql コマンドを使った場合はログイン後に
# \d items

というコマンドを実行することで取得できることは知っていました("items" の部分に知りたいテーブル名を指定して実行します):
db=# \d items
                          Table "public.items"
  Column  |            Type             | Collation | Nullable | Default
----------+-----------------------------+-----------+----------+---------
 id       | character varying(50)       |           | not null |
 name     | character varying(100)      |           | not null |
 price    | integer                     |           |          | 0
 body     | text                        |           |          |
 image    | bytea                       |           |          |
 datetime | timestamp without time zone |           |          |
Indexes:
    "items_pkey" PRIMARY KEY, btree (id)

この方法を知っていたので、これまであまり気にすることもありませんでした。ところがこれはあくまで psql コマンドを利用する際のコマンドであって、これをそのまま SQL として pg を使って実行すると(SQL ではないので当然ですが)エラーとなってしまいます。ではいったいどうすれば pg でこの情報をプログラムのコード内で取り出すことができるのだろうか・・・ というのが今回のブログエントリのテーマです。


結論として分かったのは、こんな感じでした:
・SQL としては実行結果にすべての列が含まれるような SELECT 文(例: "select * from items")を実行する
・実行結果からレコードを取り出す場合は result.rows を参照するが、実行結果の列情報は result.fields と result._types._types.builtins を参照することで取り出すことができる
・実行結果のレコードが0件でも(1件もレコードが登録されていなくても)、上の方法で列情報を取り出すことはできる

具体的なコードとしてはこのような感じです:
var PG = require( 'pg' );
var pg = new PG.Pool({
  connectionString: "postgres://user:pass@hostname:5432/db",
  idleTimeoutMillis: ( 3 * 86400 * 1000 )
});

  :
  :

if( pg ){
  var conn = await pg.connect();
  if( conn ){
    try{
      var sql = 'select * from items';
      var query = { text: sql, values: [] };
      conn.query( query, function( err, result ){
        if( err ){
          console.log( err );
        }else{
          var fields = r1.result.fields;
          var types = r1.result._types._types.builtins;
          var columns = [];
          fields.forEach( function( f ){
            var dt = Object.keys( types ).reduce( function( r, key ){
              return types[key] === f.dataTypeID ? key : r;
            }, null );
            columns.push( { column_name: f.name, type: dt } );
          });

            :
            :
        }
      });
    }catch( e ){
      console.log( e );
    }finally{
      if( conn ){
        conn.release();
      }
    }
  }
}


赤字の部分の解説をします。まず "postgres://(ユーザー名):(パスワード)@(PostgreSQL サーバー名):(ポート番号)/(DB名)" というフォーマットの接続文字列を使ってデータベースに接続します(正しく接続できるのであれば、このフォーマットである必要はありません)。 そして接続後に "select * from items" というシンプルな SQL を実行して、結果を result という変数で受け取ります。この SQL 実行結果(レコード情報)自体は result.rows という属性に配列形式で格納されているのですが、今回ここは使いません。

この SQL を実行することにより、指定したテーブル(今回の場合は items)の列名とデータ型IDの情報が result.fields に、データ型IDとデータ型の関係を示す表が result._types._types.builtins に格納されているはずです。これらを取り出し、各列のデータ型を ID ではなく文字列に変換しなおして、最終的に columns という配列変数に記録しています。

この columns の実行結果を参照すると、このような値になっているはずです:
    [
      {
        "column_name": "id",
        "type": "VARCHAR"
      },
      {
        "column_name": "name",
        "type": "VARCHAR"
      },
      {
        "column_name": "price",
        "type": "INT4"
      },
      {
        "column_name": "body",
        "type": "TEXT"
      },
      {
        "column_name": "datetime",
        "type": "TIMESTAMP"
      }
    ]

"integer" 型が、より正確な "INT4" という型になっていたりはしますが、当初取得したかった列の定義情報を取得することができました。なお、この方法であれば SQL の実行結果(result.rows)そのものを参照しているわけではないため、実行結果が0件であっても(レコードがまだ1件も登録されていない場合でも)実行できるようです。

サンプルソースコードはこちらからどうぞ:
https://github.com/dotnsf/pg_fieldtype


(2023-12-18 追記)
ちなみに MySQL の場合、その名もズバリの mysql ライブラリを使うのが定番だと思ってますが、こちらの場合はテーブル一覧("show tables")もテーブル定義("desc (テーブル名)")も、CLI で使う命令文をそのまま利用して取得することができるので、深く考えなくてもよいのでした。




自分は多くのウェブサービスやウェブアプリを開発/公開していますが、そのほとんどが無料です。中には「有償でもいいかな?」と思えるものもあったりしますが、無料で公開している最大の理由は「課金管理の仕組みが難しい」からというのが大きいです。

自分で作ったサービスを有料公開しようとすると、いくつかの問題点が出てきます。まずそもそも「どうやって支払ってもらうのか?」を解決する必要があります。理想的には月額制サブスクにして、クレジットカードで・・みたいな感じになりますが、クレジットカードと連動する仕組みを個人で用意するのはかなり大変です。クレジットカード情報は自サービスに記録しないとしても、サービスと連動させるための情報は記録する必要があり、場合によっては個人情報保護も考慮する必要が出てきます。 個人情報を取得しなかった場合でも自サービスのユーザーの情報は自サービス内で管理する必要があります。ログインとかオンラインサインアップとか、パスワードを忘れてしまった場合のリセットなどです。これらの仕組みを提供してユーザーを管理した上で、どのユーザーが有償サービスに移行して、どのユーザーは無料ユーザーのままで、そして有償サービスに移行したユーザーはどういうプランで・・・といった情報をすべて管理する必要があるのでした。これらを解決するための仕組みづくりがサービス本体と比べてもかなり面倒なのでした。多くの個人開発者が共通に悩む点だと思っています。

そんな中で一念発起して、この「ユーザー管理&課金管理」に挑戦してみました。今回はユーザー管理機能として Auth0 を、課金部分は LINE Pay を使ってみました。いずれも Node.js 向けの外部連携ウェブ API (SDK) が提供されていて、普段の自分が開発している環境で比較的容易に実現できるものでした。ユーザー管理機能は Auth0 である必要はありませんが、他の IDaaS を使う場合はその IDaaS 向けにログインやログアウト、オンラインサインアップ等を実装する必要があります。ユーザー毎の課金管理はこのアプリ内のデータベースで管理しますが、その際に使うユーザーの ID をどのように用意するかをあらかじめ考慮しておく点がある点に注意ください。例えば後述のサンプルでは Auth0 のユーザーIDをそのまま使っています。これを見た目でよりわかりやすくするために、ユーザーのメールアドレスを ID として利用することも可能ですが、その場合は個人情報をデータベースに記録することになる、という点に留意が必要です(それが問題ないケースであれば、メールアドレスをユーザー ID とするのがリーズナブルだと思っています。今回は個人情報取得を避ける目的でユーザーを特定しにくい ID を使っています)。

以下にサンプルアプリの使い方と、そのサンプルアプリを利用するための準備作業を記載していきます。なお実際に動かす場合(実際の支払いは発生しませんが、一連の手順を確認する場合)、本アプリでは支払いに LINE Pay を使うので、LINE のアカウントと LINE のインストールされたスマートフォンが必要になります。こちらは事前に用意してください(※後述のサンプルは実際に料金を支払うわけではないのですが、LINE Pay の仕組みを使って支払い処理を行うことにはなるので LINE のインストールされたスマホが必要です)。

【PostgreSQL データベースの準備】
本サンプルアプリケーションでは(Auth0 の)ユーザーと、そのトランザクションを PostgreSQL データベースを使って紐づけて管理します。そのため PostgreSQL データベースが必要です。

クラウド環境でも、ローカルへのインストールでも、Docker コンテナなどでも構わないので、PostgreSQL データベースを1つ用意してください。以下では
 postgres://user:pass@xx.xx.xx.xx:5432/db  
(ユーザー名=user、パスワード=pass で利用可能なデータベース db が xx.xx.xx.xx:5432 で動いている)

という URL でアクセスできる PostgreSQL データベースが存在している想定で説明を続けます。


【IDaaS (Auth0)側の準備】
本サンプルではユーザー管理機能として Auth0 を使います。そのため Auth0 のアカウントを取得した上で、アプリ連携するための準備が必要です。Auth0 のアカウントを未取得の場合はこちらからサインアップしてください(開発用の無料アカウントでかまいません):
https://auth0.com/signup

アカウント取得後にログインし、ダッシュボード画面で「アプリケーション」を1つ作成します。ログイン後のダッシュボード画面左で "Applications" - "Applications" を選び、画面右上に表示される "Create Applications" ボタンをクリックします:
2022090501


"Create application" ダイアログが表示されるので、アプリケーション名(下図では "LINE Pay with IDaaS")を適当に入力後、"Regular Web Applications" を選択してから "Create" ボタンをクリックします:
2022090502


アプリケーション作成後に "Settings" タブを選択します。そして Domain, Client ID, Client Secret とそれぞれ書かれた3つのフィールドに値が設定された値を確認します(これらの値は後で必要になります):
2022090503


そのまま下にスクロールして、"Application URLs" を探します。ここには "Application Login URLs", "Application Callback URLs", "Allowed Logout URLs", ・・といった設定項目が並んでいます。最初の "Application Login URLs" は空のままでいいのですが、次の2つにはそれぞれ以下を入力します(※1):
 ・"Application Callback URLs": "http://localhost:8080/auth0/callback"
 ・"Allowed Logout URLs": "http://localhost:8080"
2022090601


※1 これらは localhost 上で動かす場合の指定です。このアプリをインターネット上の公開サーバーで実行する場合は、そのホスト名や IP アドレスも URL 形式で指定する必要があります。複数の URL を指定する場合は半角カンマで区切って指定します。


ここまで指定できたら画面の最下部までスクロールして "Save Changes" ボタンをクリックして内容を保存します。これで Auth0 の(最低限の)準備は完了です:
2022090602



この後でサンプルアプリケーションを動かします。その際に利用するユーザーは(そのユーザーのメールアドレスを使って)オンラインサインアップすることもできるのですが、この時点で直接作成しておくこともできます。ここではユーザーの作成方法を紹介するので、この方法でアプリケーションで利用するユーザーを作るか、あるいは後ほどアプリケーション実行時にオンラインサインアップしてユーザー登録を行ってください。

Auth0 のユーザーをダッシュボードから作成する場合は、左メニューで "User Management" - "Users" を選び、画面右上の "Create User" ボタンをクリックします:
2022090603


"Create user" ダイアログが表示されるので、上からメールアドレス、パスワード、パスワード確認を入力します。また "Connection" は "Username-Password-Authentication" を選択したままにします。最後に右下の "Create" ボタンをクリックするとユーザーを直接登録することができます:
2022090604


作成されたユーザーは Users 画面に(ダッシュボードから作成したユーザーと、オンラインサインアップしたユーザー両方が)一覧表示されます:
2022090605


Auth0 側の事前準備はこれで終わりです。


【LINE Pay 側の準備】
続いて課金管理を行う LINE Pay 側の事前準備作業を紹介します。具体的にはアプリケーションを LINE Pay 連携させる際に必要な Channel ID と Channel Secret と呼ばれるキー値が必要になるので、これらを取得します。

最終的に作成したアプリで本当にユーザー課金を求める(実際のお金で決済する)場合は LINE Pay に加盟店登録が必要になります。が、今回は試験的に動作確認できればよいレベルで動かしたくて、(開発途中で)試験的に動かした際に課金してほしくもない状態です。 そのような場合向けにサンドボックスと呼ばれる開発者向け環境が用意されているので、そのサンドボックスを使って Channel ID と Channel Secret を取得し、動作確認させてみます(サンドボックス環境での決済はあくまで動作確認のための決済処理であり、実際に支払われることはありません)。

サンドボックスを使うには以下のアドレスからサンドボックス生成申請を行います:
https://pay.line.me/jp/developers/techsupport/sandbox/creation?locale=ja_JP

国(JP(日本), TW(台湾),TH(タイ)から選択)、サービスタイプ(Online)、利用通貨(JPY(日本円)かUSD(アメリカドル)から選択)、そしてメールアドレスを入力し、最後に "Submit" ボタンをクリックします:
2022090606


すると指定したメールアドレスに以下のようなメールが届きます:
2022090607


メールに記載されているテスト ID とパスワードを使って LINE Pay の加盟店 My Page にアクセス/ログインしてみます。以下のページを開いてテスト ID とパスワードを入力し、最後に「ログイン」ボタンをクリックします:
https://pay.line.me/portal/jp/auth/login

2022090608


ログイン直後は以下のような画面が表示されます。サンドボックスに移動するには画面右上の "Sandbox" と書かれたメニューをクリックします:
2022090601


サンドボックスが表示されたら、画面左のメニューから「決済連動管理」- 「連動キー管理」を選択します。連動キー管理画面でパスワードチェックの画面が表示されたら、(上述のサンドボックス申請をした時にメールで送られてきた)パスワードを入力して「確認」ボタンをクリックします:
2022090602


すると連動キー管理画面内に "Channel ID" と "Channel Secret Key" が表示されます。これらはアプリケーションを動かす際に必要になるものです:
2022090603


LINE Pay 側の事前準備もこれで終わりです。ここまでできていれば実際にサンプルアプリを動かすことができます。


【サンプルアプリの準備】
では、ここまで準備してきた
 ・PostgreSQL データベース
 ・Auth0 によるユーザー管理
 ・LINE Pay によるオンライン支払い処理
が連動する Node.js サンプルアプリケーションを実際に動かしてみます。

まずサンプルアプリケーションは Node.js 前提で作っているので、 Node.js の導入がまだであれば最初に自分の環境向けのモジュールをインストールしておいてください:
https://nodejs.org/ja/


次にソースコードを入手します。以下のリポジトリから git clone するか zip & download & unzip でソースコードを入手してください:
https://github.com/dotnsf/linepay-idaas

2022090604


このソースコードのルートフォルダに linepay-idaas.ddl というファイルがあります。この DDL ファイル(の内容)で PostgreSQL データベースのテーブル定義を行います。psql などを使って PostgreSQL に接続し、この DDL ファイルと同じ内容を実行してテーブルを定義してください(psql を使う場合は接続語に "\t linepay-idaas.ddl" コマンドで実行できます):
> \i linepay-idaas.ddl
> \q

また、このアプリケーションはいくつかの環境変数を参照して動きます。その環境変数を指定できるよう、ソースコードのルートフォルダ(app.js と同じフォルダ)内に .env というファイルを作り、以下のような内容に編集して保存してください:
LINE_PAY_CHANNEL_ID=XXXXXXXX(LINE Pay Channel ID の値)
LINE_PAY_CHANNEL_SECRET=XXXXXXXX(LINE Pay Channel Secret の値)
LINE_PAY_CONFIRM_URL=http://localhost:8080/pay/confirm(このままの値を指定)
DATABASE_URL=postgres://user:pass@xx.xx.xx.xx:5432/db(PostgreSQL データベースのアクセスURL)
PGSSLMODE=no-verify(PostgreSQL データベースにアクセスする際の SSL モードを指定、SSL を使わない場合は disable を指定)
AUTH0_CALLBACK_URL=http://localhost:8080/auth0/callback(このままの値を指定)
AUTH0_CLIENT_ID=XXXXXXXX(Auth0 Client ID の値)
AUTH0_CLIENT_SECRET=XXXXXXXX(Auth0 Client Secret の値)
AUTH0_DOMAIN=dev-xxxxxxxx.us.auth0.com(Auth0 Domain の値)

↑上で説明した PostgreSQL や Auth0、 LINE Pay で取得した値を多く使っています。設定時に取得した値を正しく入力してください。

またこちらは編集必須ではないのですが、実際にオンラインで購入する(LINE Pay で支払う)商品の情報を item.json ファイルに記載しています。この中身を編集することで、この後の動作確認時に購入する商品の名称や価格を変更することができます(デフォルト状態では以下のようになっていて、この場合は「サービス利用料金」という商品を 「100 円」で購入することになります)。必要に応じて編集して使ってください:
{
  "productName": "サービス利用料金",
  "amount": 100,
  "currency": "JPY"
}


.env ファイルと item.json ファイルの準備ができたら、後はアプリケーションの実行に必要なライブラリをインストールします。以下のコマンドを実行して、依存ライブラリをインストールします:
$ cd linepay-idaas
$ npm install

【サンプルアプリの実行】
では実際にサンプルアプリケーションを起動して、実際にログイン&支払い処理の稼働確認をしてみましょう。まずは以下のコマンドでアプリケーションサーバーを起動します(ちなみにアプリケーションサーバーを止める場合は Ctrl +C を押します):
$ npm start

アプリケーションが起動すると 8080 番ポートで待ち受けるので、ウェブブラウザから http://localhost:8080 を指定してアクセスします(実際の運用ではスマートフォンのブラウザからアクセスすることもあると思っていますが、今回は PC ブラウザを使っています)。このアプリケーションは未ログインの状態でアクセスすると、強制的にログインページに遷移するように作られているので、Auth0 のログイン画面に移動します。ここで(ダッシュボードからユーザーを作成済みであれば)メールアドレスとパスワードを指定してログインします。または一番下のリンクからオンラインサインアップすることも可能です。いずれかの方法でログインします:
2022090605


ログインに成功すると、以下のような画面が表示されます:
2022090606


画面右上にはログインしたユーザーの情報が表示されています。鍵のマークがついたユーザーは「(まだ支払い処理を行っていない)無料ユーザー」であることを示しています。またその右には Auth0 のユーザーアイコン(特に指定していない場合はデフォルトアイコン)が表示されます。なお、この部分をクリックすることでログアウトも可能です:
2022090607


また画面中央にはそのユーザー(今の状態であれば無料ユーザー)向けの内容が表示されています。現在は無料ユーザーなので、無料ユーザー向けのコンテンツと、有料ユーザーになるための支払いを行うアイコンボタン(緑の "LINE Pay" ボタン)が表示されています。後で有料ユーザーになると、この画面が変わるので確認しておいてください:
2022090608


ではこの無料ユーザーが支払い処理を行うことにします。"LINE Pay" と書かれた緑のボタンをクリックします。すると以下のような画面に切り替わります。LINE Pay での支払い処理に移るため、LINE がインストールされたスマホを使って、LINE アカウントを指定してログインするか、画面の QR コードをスキャンしてログインしてください:
2022090601


するとスマホ側には以下のような画面が表示されます。item.json ファイルで指定した内容の商品名と価格が表示され、"PAY NOW" というボタンが表示されています。この "PAY NOW" ボタンをクリックして購入します(サンドボックス環境で実行しているので、実際の決済や支払いは行われません):
2022090601


購入すると、"PAY NOW" ボタンは「決済が完了しました」というメッセージに切り替わります:
2022090602


購入処理と同時に Auth0 にログインしていたウェブブラウザの画面が自動的に更新され、以下のような内容になります:
2022090606


変わった点として、まず画面右上のアイコンから鍵マークが消えています。これは「有料ユーザー」のアイコンで、無料ユーザーから有料ユーザーへ切り替わり、そのことをアプリケーションでも認識できたことを意味しています:
2022090607


また画面中央の内容が無料ユーザー向けコンテンツから有償ユーザー向けコンテンツに変わりました。入金によってユーザー属性が切り変わったことで有償ユーザー向けの画面を表示しています:
2022090608


↑なお、この画面内には「(動作確認検証用)フリーユーザーに戻る」というリンクが表示されています。実際のアプリケーションでは有償ユーザーがわざわざ無料ユーザーに戻る、というアクションを起こしたり、そのためのリンクを用意することはないと思うのですが、今回のアプリケーションでは何度も動作確認できるよう(一度有償ユーザーになった後も、無料ユーザーに戻って実行したくなることもあると思うので)意図的にこのようなリンクを用意しています。 このリンクをクリックすると確認メッセージ後に支払いデータの削除が実行され、無料ユーザーに戻ることができます(繰り返しますが、あくまで動作確認用の機能であり、実際のアプリケーションではこのようなリンクを付けることはないと思っています):
2022090606


なお、上述の「無料ユーザー向け画面」「有償ユーザー向け画面」は、ソースコード内の views/index.ejs 内で以下のように定義しています。この部分を変更して再実行することで無料ユーザーおよび有償ユーザー向けの画面をカスタマイズすることができます。興味ある方はカスタマイズにも挑戦してみてください:
  :
  :
<div class="container">
<% if( user.type == 0 ){ %>
フリーユーザー向けコンテンツ部分<br/>
<a href="/pay/reserve"><img src="/pay_btn.png"/></a>
<% }else{ %>
有償ユーザー向けコンテンツ部分<br/>
<a href="#" onClick="userDeleteType();">(動作確認検証用)フリーユーザーに戻る</a>
<% } %>
</div>
  :
  :

緑のタグに囲まれた赤字部分が無料ユーザー向け画面青字部分が有償ユーザー向け画面として表示されます

サンプルアプリ内でログインしたユーザーが課金しているかどうかの判断を含めて実現できていると思っています。


決済処理をした後で LINE Pay 加盟店 My Page にアクセスすると、取引管理や売上管理メニューから決済内容やその ID(=トランザクションID)を参照することができます(ただこの時点では、あくまで LINE 内の取引としてしか参照できないので、「誰が」決済したかを確認することはできません):
2022090609


このアプリケーションを使って「誰が」決済したのかを参照するには、アプリケーションの PostgreSQL データベース内を参照する必要があります。PostgreSQL データベースに接続して transactions テーブル内を参照すると、トランザクション ID とユーザーID、注文 ID などが紐づけられて管理されていることが確認できます:
2022090602


ユーザーIDは(このアプリケーションの場合は)Auth0 のユーザー ID なので、ユーザーを特定したい場合は Auth0 のユーザー一覧から、この ID をキーに検索することでユーザーを特定することができます。


なお、本サンプルアプリケーションでは IDaaS として Auth0 を使っていますが、他の IDaaS でも同様に使うこともできると思います。ログインやコールバックといった処理部分は書き換えが必要だと思っていますが、ユーザーを一意に特定する ID さえ取得できれば、その ID を PostgreSQL データ内の user.id として使うことになるだけなので、比較的容易に移植できると思っています。

いかがでしょう? とりあえずこのアプリをベースに改良することで(または同等のアプリを実装することで)個人開発サービスに組み込めるレベルで Auth0 と LINE Pay を使ったウェブサービスを開発できそうな目途は立ったように感じています。


【(サンドボックス環境ではなく)実際に取引する場合の注意】
とりあえず開発環境内で実際に取引できることは確認できたと思っています。これを(画面を変えたり、商品や値段を変えたり、決済方法を変えたり、など)カスタマイズして組み込むことで、個人の作ったウェブサービスに課金機能を組み込むこともできると思っています。が、そのためには3つの課題を意識する必要があります。

1つ目は「サンドボックスではなく、正式な取引をするには LINE Pay に加盟店登録が必要になる」ことです。私自身もここは実際に試したことはなく、どのくらい大変なのか/よゆーなのか、よくわかっていません。ちょっと調べた範囲で(2022/09/06 時点の)現状では PayPay への加盟店登録も必要そうで、ちと面倒そうな印象もあります・・・ 詳しい手続きについてはこちらを参照ください:
https://pay.line.me/merchant-apply/jp/selection/login-v2

(2022/09/11 追記)
実際に加盟店登録に挑戦してみました:
http://dotnsf.blog.jp/archives/1080934049.html


2つ目は支払い方法の問題です。例えば有償で提供するサービスが1回ごとの課金であったり、初回まとめて課金後はずっと使える、というものであれば実装にも大きな問題はないと思うのですが、例えば「月額サブスクリプション」のような支払いサービスだと LINE Pay 側が未対応のように思っています(違っていたらごめんなさい)。例えば支払いのタイミングを記録しておいて、過去の同一月に支払いの記録があればその月は有償ユーザーとして扱い、支払い記録がない場合は無料ユーザーとして扱う、などの工夫をアプリケーション側で行う必要があるように思えます。


3つ目は決済手数料の問題です。おそらくこの情報が最新だと思っているのですが、デジタルコンテンツの決済手数料は 5.5% です。つまり実際に1万円売り上げた場合 550 円を手数料として LINE Pay に支払う必要がある、またこの割合はいつか変わる可能性がある、という点に留意ください。
https://linecorp.com/ja/pr/news/ja/2021/3876



(こちらを参考にさせていただきました)
https://qiita.com/nkjm/items/b4f70b4daaf343a2bedc



Node.js + PostgreSQL 環境で複数レコードのバルクインサートを、それも対象テーブルに UNIQUE 制約が存在している場合のバルクインサート方法について調べる機会があったので、ブログにまとめておきます。


【Node.js で PostgreSQL を使う】
まず、Node.js で PostgreSQL クライアント機能を実装する場合、よく使われているのが pg(node-postgres) という npm のライブラリだと思っています:
2022080601


こんな感じでコネクションプーリングを作って、、
  :
  :
var PG = require( 'pg' );
var pg = new PG.pool( 'postgres://localhost:5432/mydb' );
  :
  :


こんな感じで SQL (以下の例は "INSERT" 処理)を実行し、結果を取得します:
  :
  :
if( pg ){
  var conn = await pg.connect();                                      //. コネクション割当
  if( conn ){
    try{
      var sql = "insert into table1( name, num ) values ( $1, $2 )";  //. SQL
      var query = { text: sql, values: [ "木村", 100 ] );             //. SQL パラメータ
      conn.query( query, function( err, result ){
        if( err ){
          console.log( err );                                         //. SQL 実行時エラー
        }else{
          console.log( result );                                      //. SQL 実行結果
        }
      });
    }catch( e ){
      console.log( e );                                               //. 例外発生
    }finally{
      if( conn ){
        conn.release();                                               //. コネクションのリリース
      }
    }
  }
}
  :
  :


今回もこの pg を使って紹介します。


【Node.js + PostgreSQL でバルクインサートを実装する】
上の例では一回の SQL 実行で1つのレコードをインサートする、という処理の例を紹介しました。複数のレコードをインサートする場合もこれを複数回続けて実行してもよいのですが、レコード数が多い場合に効率よくインサートできると便利です。このように「複数のレコードを1回の処理でまとめて作成する」ことを「バルクインサート」と呼びます。

Node.js + PostgreSQL でバルクインサートを実装する方法もいくつかありますが、今回は node-pg-format という npm ライブラリを使った例を紹介します:
2022080602


実際に使う場合はバルクインサートデータを配列で用意して、以下のように実装します:
  :
  :
var format = require( 'pg-format' );

var records = [         //. インサートするレコードを配列で用意
  [ "木村", 100 ],
  [ "鈴木", 101 ],
  [ "木村", 102 ]
];

if( pg ){
  var conn = await pg.connect();                                      //. コネクション割当
  if( conn ){
    try{
      var sql = format( "insert into table1( name, num ) values %L", records );  //. SQL
      var query = { text: sql, values: [] );                          //. SQL パラメータ
      conn.query( query, function( err, result ){
        if( err ){
          console.log( err );                                         //. SQL 実行時エラー
        }else{
          console.log( result );                                      //. SQL 実行結果
        }
      });
    }catch( e ){
      console.log( e );                                               //. 例外発生
    }finally{
      if( conn ){
        conn.release();                                               //. コネクションのリリース
      }
    }
  }
}
  :
  :

上例のように、SQL 文全体を pg-format でインスタンス化しています。その際にインサートする複数レコードを配列データとして付与しています(conn.query() 実行時のパラメータは空です)。これで records 配列変数で指定された複数レコードを1回の SQL 実行でまとめてインサートすることができるようになります。


【Node.js + PostgreSQL のバルクインサートと UNIQUE 制約】
ここからがこのブログエントリの本題に入っていきます。バルクインサートの方法は上で紹介しました。問題はこれではうまくいかないケースがあるのでした。具体的には対象テーブルに UNIQUE 制約が付与されていて、この制約にかかるレコードがバルクインサートの一部に含まれる場合です。

具体的に紹介します。例えば今回は table1 という名称のテーブルに複数レコードをバルクインサートする例を紹介していますが、この table1 テーブル作成時に以下のような指定がされていた可能性は排除できません:
create table table1( name varchar(50) default '', num int default 0 );
alter table table1 add unique( name, num );

1行目の "create table ..." はいいとして、問題は2行目です。この制約を与えたことによって name と num の組み合わせは一意(UNIQUE) でないといけない、という条件が付与されました。name は被ってもいいし、num も被ってかまわないのですが、name と num の組み合わせはユニークでないといけない(この条件に反するデータはインサートできない)ようになりました。

このような UNIQUE 制約が存在している前提で、改めて上例を見てみます。この例でバルクインサートしようとしているレコードは、
var records = [         //. インサートするレコードを配列で用意
  [ "木村", 100 ],
  [ "鈴木", 101 ],
  [ "木村", 102 ]
];

という内容でした(配列の1つ目が name 、2つ目が num としてバルクインサートされます)。ここだけを見るとこの例に限っては(name と num の組み合わせは全て異なるので)問題なくバルクインサートできそうです。 ・・・が、もしこのバルクインサート実行前に既に { name: "木村", num: 100 }
のデータが記録済みであった場合
はどうでしょう?バルクインサートの最初のレコードは UNIQUE 制約にかかってしまいます。つまり実行エラーとなります。

そして更にこの問題をややこしくしている事情があります。それは「バルクインサートが UNIQUE 制約エラーで失敗するのはいいが、その時 UNIQUE 制約にかからなかったレコードはインサートされるべきか?否か? という問題です。この答はおそらくデータやアプリケーション次第、つまりケース・バイ・ケースです。まとめてインサートされないと意味がないレコードであった場合はまとめてエラー(UNIQUE 制約にかからなかったレコードもインサートされない)という挙動が正しい挙動となり、UNIQUE 制約にかかったレコードはインサートされないが、他のレコードはインサートされるべきデータであった場合はバルクインサートの一部だけが失敗し、残りのレコードは挿入されているべき、ということになります。 

ちなみに上例で紹介したバルクインサート実装の場合は前者の挙動となります。つまり「全てのレコードが制約違反にならなかった場合のみバルクインサートが成功し、1つでも制約違反のレコードがあった場合はそうでないレコードも含めて1つもインサートされずにエラーとなる」という挙動です(そもそも厳密には「バルクインサート」とはそのような挙動および処理を意味する言葉です)。


【Node.js + PostgreSQL のバルクインサートと UNIQUE 制約】
長い前置きでした。ではここまでの背景を理解した上で UNIQUE 制約に違反しているレコードのみ挿入されず、違反していないレコードは正しく挿入されるようなバルクインサート(「バルクインサート」の定義としてはおかしいですけど・・)を実装するにはどのようにすればよいでしょうか? これが本ブログエントリの肝となる部分です。

これは実際には使っている RDB やライブラリ、プログラミング言語によって異なるようですが、PostgreSQL 9.5 以上で、Node.js + node-postgres を使っている場合であれば以下の方法で実現できるようです:
  :
  :

var records = [         //. インサートするレコードを配列で用意
  [ "木村", 100 ],
  [ "鈴木", 101 ],
  [ "木村", 102 ]
];

if( pg ){
  var conn = await pg.connect();                                      //. コネクション割当
  if( conn ){
    try{
      //. UNIQUE 制約に違反したデータを無視するような SQL を作る
      var sql = "insert into table1( name, num ) ";
      var selects = [];
      for( var i = 0; i < records.length; i ++ ){
        selects.push( "select '" + records[i][0] + "', " + records[i][1] );
      }
      sql += selects.join( " union all" ) + " on conflict ( name, num ) do nothing";

      var query = { text: sql, values: [] );                          //. SQL パラメータ
      conn.query( query, function( err, result ){
        if( err ){
          console.log( err );                                         //. SQL 実行時エラー
        }else{
          console.log( result );                                      //. SQL 実行結果
        }
      });
    }catch( e ){
      console.log( e );                                               //. 例外発生
    }finally{
      if( conn ){
        conn.release();                                               //. コネクションのリリース
      }
    }
  }
}
  :
  :


この例ではプログラミングによって、以下のような SQL 文を生成して実行しています:
insert into table1( name, num )
  select '木村', 100 union all
  select '鈴木', 101 union all
  select '木村', 102
  on conflict ( name, num ) do nothing

厳密にはバルクインサートではないのですが、1回の SQL 実行で複数のレコードをインサートし、"conflict"(UNIQUE 制約違反)が発生した場合は "do nothing"(無視)するよう指定しています。その結果、UNIQUE 制約違反のないレコードのみがまとめてインサートされる、という処理が実現できています。

バルクインサートで1度に挿入するレコード数が莫大になるようなケース(SQL 文自体が膨大で複雑になるケース)には向かないかもしれませんが、自分の手元では一応期待どおりに動くことを確認しました。ちょうどこういう処理を行いたいアプリを作っていて調べたのですが、他の人の参考になれば。


【参照】
https://stackoverflow.com/questions/53722405/how-to-insert-bulk-rows-and-ignore-duplicates-in-postgresql-9-3










このページのトップヘ