先日、このようなブログを書きました：
"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 を実行すると、以下のルールでデータベース内容をまるごと１つのエクセルファイルに変換します：
・データベース内の全テーブルと、各テーブル内のレコードデータを取り出す
・各テーブルが１枚のエクセルシート（タブ）になる　テーブル名がシート名になる
・シートの１行目にテーブルの全列が書き出される
・シートの２行目以降にテーブル内の全レコードデータが１行ずつ格納される（ただし BLOB などのバイナリデータは無視する）
・処理が完了すると、（データベース名）.xlsx という名前のエクセルファイルでダウンロードできる

という誰得（？）な処理を行います。　もう１つの「エクセルをデータベースにする」は基本的にこの逆の処理を行います：

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


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


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

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

（１）ターミナルやコマンドプロンプトなどの端末を開く

（２）ソースコードをダウンロード

$ git clone https://github.com/dotnsf/db2xlsx

（３）必要なライブラリをインストール

$ cd db2xlsx

$ npm install

（４）実行（終了時は Ctrl+C）

$ npm start

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


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


Docker で PostgreSQL データベースを１つ用意する場合は以下を実行します：

$ 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" となります。

データベースを２つ作る必要はないので 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" となります。ユーザー名部分やパスワードなどは適宜変更して読み替えてください。


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


データベースが稼働している状態で上述の（４）までを実行して本ツールを起動します（本ツール起動後にデータベース起動でも構いません）：

$ npm start

> db2xlsx@0.0.1 start
> node app.js

server starting on 8080 ...

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



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


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

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


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





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

（items）

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

（brands）

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

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



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



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



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


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

それ以外の部分は必要に応じて指定するものですが、今回指定したワークブックファイルは各シートの２行目にテーブルの列定義が記載されています。このような場合（シートの２行目がレコード情報ではなく、列定義情報の場合）は col_attr パラメータに "1" という値を指定する必要があります（何も指定しない場合は２行目もデータレコードとみなされて 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 処理が実行されます：



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



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

ターミナルやコマンドプロンプトなどの端末をもう１つ開いて、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 | ブランド１ | 説明文１説明文１説明文１説明文１説明文１説明文１説明文
説明文１説明文１説明文１説明文１説明文１説明文１
  | https://www.brand1.com | 2023-12-20 00:00:00
 brand02 | ブランド２ | 説明文２説明文２説明文２説明文２説明文２説明文２説明文
説明文２説明文２
  | https://www.brand2.net | 2023-12-21 00:00:00
 brand03 | ブランド３ | 説明文３説明文３説明文３説明文３説明文３説明文３説明文
説明文３説明文３説明文３説明文３説明文３説明文３説明文３説明文３説明文３説明文
 | 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" のボタンをクリックします：



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



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



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



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



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





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


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


 

タグ ：

#db2xlsx

#xls

#xlsx

#xslx-js-style

#db

#excel

#mysql

#postgres

毎年恒例のマンホールマップ年間アクセスランキングを発表します。2023 年にマンホールマップでもっとも人気のあったマンホール蓋をベスト１０形式で紹介します。また今年新たに投稿された蓋の中で最も人気があった「新人賞」と、今年最も多くの蓋画像を投稿いただいた方「最多投稿賞」を紹介します。

集計のルールとしては 2022/12/21 から 2023/12/20 までの集計期間における、PC およびスマホのブラウザから単独ページとしてのアクセス数を集計しています。ページビューとしての集計なので、例えば同じページの画面をリロードした場合は１回とだけカウントされます。

なお、過去９回の結果はこちらを参照ください：
2014 マンホールマップ年間アクセス数ランキング
2015 マンホールマップ年間アクセス数ランキング
2016 マンホールマップ年間アクセス数ランキング
2017 マンホールマップ年間アクセス数ランキング
2018 マンホールマップ年間アクセス数ランキング
2019 マンホールマップ年間アクセス数ランキング
2020 マンホールマップ年間アクセス数ランキング



浅草名物「雷門」からも近い墨田川沿いに設置された古典歌舞伎「助六」の演目の１シーンが描かれた美しい蓋です。東京２３区のソメイヨシノ蓋同様、ユリカモメに囲まれています。私も職場が比較的近いこともあって実物を見に行ってきました。

実は minamu450 さん（このアカウントで）が 2023 年に投稿した唯一の蓋が２月のこれでした。新人賞おめでとうございます。


では改めて 2023 年のランキングを発表します。まずは 10 ～ 4 位です。
が、今年も10位付近は大激戦で、10位は３つの蓋が同数でした（なんと２年連続！そして次点は１アクセス差！！）。その大混戦から抜け出して滑り込んだ第１０位！


気象庁・マスコットキャラクターの「はれるん」が描かれた東京都清瀬市、気象衛星センター内のカラーマンホールです。投稿者は私なのですが撮影者は別で、いわゆる代理投稿です（おめでとー！）。実はこの気象衛星センター内は撮影していい箇所といけない箇所があって、この蓋は本当に大丈夫なのかどうか・・・　ともあれ年間トップ１０入り、おめでとー！！！！


２つ目の第１０位！

順位	昨年順位	市区町村	投稿者	画像
10	-	大阪府門真市	SakiYumeno

門真市のイメージキャラクター「ガラスケ」が描かれた大阪府門真市のデザインマンホールです。撮影者はたまにイベントでお会いする SakiYumeno さん。門真は私も仕事でよく行く場所の１つなのですが、このデザインは見たことありませんでした。今度いこ。　というわけで初の年間トップ１０入り（ですよね？）、おめでとうございます！！


最後、３つ目の第１０位！

順位	昨年順位	市区町村	投稿者	画像
10	-	埼玉県所沢市	42ER03

武蔵坊弁慶が白紙の勧進帳を見て「清き水となり大地をうるおせ」と読んだとされる歌舞伎でも有名なシーンが描かれたマンホール、投稿者は今年の（も）最多投稿者でもある 42ER03 さまです。

・・ただ、この蓋はマンホールマップ上では埼玉県所沢市に投稿されているのですが、一般的には石川県小松市の蓋として有名なやつだと思っています。越境蓋なのかな？　このあたり続報があればいただきたいです（必要であればこのブログも修正します）。


第９位！

順位	昨年順位	市区町村	投稿者	画像
9	-	東京都世田谷区	EkikaraManhole

EkikaraManhole 大先生による、いわゆる「ご本人登場」の蓋投稿です。かつて世田谷区を流れていた北沢川を暗渠化して整備された緑道にカラー設置されたかつての姿。昔はこの辺りは蛍やメダカが生息してたんですかね。

第８位！

順位	昨年順位	市区町村	投稿者	画像
8	-	静岡県熱海市	tenore0

本ランキング初の地味蓋登場。静岡県熱海市に設置された「ガス」の蓋、なのですが、その正体がイマイチわかりません。投稿者の tenore0 さんも「ちょっと自信がないのですが、熱海市内の都市ガスを取り扱っている会社のふただと思います」と書かれてますし、「熱海ガス」という会社が存在することも分かっているのですが、では本当に熱海ガスの蓋か？と言われると、私も分かりませんでした。真ん中のマークは EX と書かれているようにも見えるんだけど、これはヒントになってるんだろうか・・詳細分かる人いる？


第７位！

順位	昨年順位	市区町村	投稿者	画像
7	9	群馬県高崎市	TMW_papa

昨年は９位だった、群馬県高崎市の「ぐんまちゃん」サッカーデザインです。2015 年、2016 年にも連続トップ10入りしている、マンホールマップでも指折りの人気蓋が、今年も多くのアクセスを記録しました。投稿者は TMW_papa さんです。

第６位！

順位	昨年順位	市区町村	投稿者	画像
6	3	岩手県盛岡市	minamu4545

昨年３位だった盛岡市の「わんこきょうだい」蓋が今年も６位でランクインしました。去年も言った気がしますが、わんこずんだだけはヤバそうな気がしています。投稿者は最多投稿賞でも常連の minamu4545 さん（minamu450 さん）です。


第５位！

順位	昨年順位	市区町村	投稿者	画像
5	-	東京都世田谷区	jishiha

世田谷区から２つ目のランクイン！玉川電気鉄道をあしらったデザインマンホール（？）。投稿者はモバイル版マンホールマップを開発されていた jishiha さんです。どーも、ご無沙汰しております。　そんなこのマンホール、石畳に紛れ込ませた感じのこれをデザイン蓋に分類していいのか迷いますが、玉電でググった人が多かったんですかね？ともあれ何故か 2023 年に多くのアクセスを集める結果となりました。


第４位！

順位	昨年順位	市区町村	投稿者	画像
4	-	高知県高知市	minamu4545

名物のカツオが飛び跳ねる様子がデザインされた高知市の蓋です。デザインは公募らしく、よさこいアイテムの鳴子も鮮やかにデザインされているのがわかります。マンホールマップへの投稿は 2017 年でした。これまでランキングに入ることはなかったのですが、今年突然のブレーク！といった所でしょうか。　投稿者はこれも minamu4545 さんです。



ここからはトップ３の発表です。第３位！！

順位	昨年順位	市区町村	投稿者	画像
3	-	東京都台東区	minamu450

上の新人賞でも紹介した助六夢通りの蓋が 2023 年の総合３位でした。このデザイン蓋が設置されているのは一枚だけですが、浅草雷門からスカイツリーに向かう道の途中、墨田川を渡る直前あたりという、人通りの多そうな場所に設置されています。今後も人気が続くことが考えられる蓋だと思っています。


第２位！！！

順位	昨年順位	市区町村	投稿者	画像
2	-	北海道札幌市	yanapong

実は昨年の10位にはカラー版がランクインしていたのですが、なぜかその地味版が 2023 年の総合２位となるアクセスを記録しました。こういう解説に困るアクセス順位になるのがマンホールマップです。　というわけで、有名な札幌市の時計台がデザインされた人気マンホール（の地味版）が何故か大量のアクセスを集めていました。　投稿はこの界隈では有名な yanapong さんでした。やなぽんさんは（別の蓋ですが）2018 年まで４年連続トップ10入りを記録した「リスのクリちゃん」マンホールの投稿者でもあり、その時以来５年ぶりのトップ10入りとなりました。


さて 2023 年の MVM はどの蓋に？　注目の第１位！！！！

↓
↓
↓
↓
↓
↓
↓
↓
↓
↓
↓
↓
↓
↓
↓
↓
↓
↓
↓
↓
↓

順位	昨年順位	市区町村	投稿者	画像
1	1	香川県東かがわ市	minamu4545

ついに歴史が動きました。マンホールマップ年間アクセスランキング初の２連覇達成、香川県東かがわ市に設置され、昨年ポケ蓋では初の１位を記録した「うどん県のヤドン」マンホールが 2023 年マンホールマップ年間アクセスランキングの１位を記録しました。投稿者である minamu4545 さん（minamu450 さん）、おめでとうございます！！


ちなみに１位となったヤドンのマンホール蓋のアクセス数は「ダントツ」でした。２位以下の混戦を数馬身離しての１着という感じで、この人気はもうしばらく続くのではないかと思っています。



さて来年のマンホールマップランキングはどうなるのでしょう？　３連覇はあるのか？　そしてツイッターは大丈夫なのか？　期待と不安が入り混じります。

では辰年となる 2024 年も引き続きマンホールマップをよろしくおねがいします。

（↓ これも minamu4545 様投稿蓋です）

タグ ：

#2023

#manhole

#manholemap

#mvm

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

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 文の実行結果の中に含まれるので、１行でもレコードが登録されていればそこから分かる、ということは知っていました。が、レコードが１件も登録されていないケースだったり、列名以外の型の情報まで必要な場合の取得方法が分かっていませんでした）。ちなみにこの情報は 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 を参照することで取り出すことができる
・実行結果のレコードが０件でも（１件もレコードが登録されていなくても）、上の方法で列情報を取り出すことはできる

具体的なコードとしてはこのような感じです：

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）そのものを参照しているわけではないため、実行結果が０件であっても（レコードがまだ１件も登録されていない場合でも）実行できるようです。

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


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

タグ ：

#node.js

#nodejs

#postgres

#postgresql

#fieldtype