自作サービスを開発している途中で調査した内容のアウトプットです。
Node.js を使って PostgreSQL データベースを操作する場合、pg(node-postgres) ライブラリを使うのが定番だと思っています。実際これまで何度も使ってきているし、データの読み書き更新削除といった作業で特に困ったことはありませんでした。
しかし今回ちょっとしたことで詰まってしまいました。結果的には解決できたのですが、データベース内に定義されたテーブルの、列の定義情報を調べたいと思った時に「これどうやるんだろ？」となってしまいました。
もう少し具体的に説明します。例えば以下のような SQL を使って items テーブルを作成したとします：
このようにして作成した items テーブルの各列の定義情報（上の例の青字部分）を取り出す方法が分からなかったのでした（列名だけであれば select 文の実行結果の中に含まれるので、１行でもレコードが登録されていればそこから分かる、ということは知っていました。が、レコードが１件も登録されていないケースだったり、列名以外の型の情報まで必要な場合の取得方法が分かっていませんでした）。ちなみにこの情報は psql コマンドを使った場合はログイン後に
というコマンドを実行することで取得できることは知っていました（"items" の部分に知りたいテーブル名を指定して実行します）：
この方法を知っていたので、これまであまり気にすることもありませんでした。ところがこれはあくまで psql コマンドを利用する際のコマンドであって、これをそのまま SQL として pg を使って実行すると（SQL ではないので当然ですが）エラーとなってしまいます。ではいったいどうすれば pg でこの情報をプログラムのコード内で取り出すことができるのだろうか・・・ というのが今回のブログエントリのテーマです。
結論として分かったのは、こんな感じでした：
・SQL としては実行結果にすべての列が含まれるような SELECT 文（例： "select * from items"）を実行する
・実行結果からレコードを取り出す場合は result.rows を参照するが、実行結果の列情報は result.fields と result._types._types.builtins を参照することで取り出すことができる
・実行結果のレコードが０件でも（１件もレコードが登録されていなくても）、上の方法で列情報を取り出すことはできる
具体的なコードとしてはこのような感じです：
赤字の部分の解説をします。まず "postgres://（ユーザー名）:（パスワード）@（PostgreSQL サーバー名）:（ポート番号）/（DB名）" というフォーマットの接続文字列を使ってデータベースに接続します（正しく接続できるのであれば、このフォーマットである必要はありません）。 そして接続後に "select * from items" というシンプルな SQL を実行して、結果を result という変数で受け取ります。この SQL 実行結果（レコード情報）自体は result.rows という属性に配列形式で格納されているのですが、今回ここは使いません。
この SQL を実行することにより、指定したテーブル（今回の場合は items）の列名とデータ型IDの情報が result.fields に、データ型IDとデータ型の関係を示す表が result._types._types.builtins に格納されているはずです。これらを取り出し、各列のデータ型を ID ではなく文字列に変換しなおして、最終的に columns という配列変数に記録しています。
この columns の実行結果を参照すると、このような値になっているはずです：
"integer" 型が、より正確な "INT4" という型になっていたりはしますが、当初取得したかった列の定義情報を取得することができました。なお、この方法であれば SQL の実行結果（result.rows）そのものを参照しているわけではないため、実行結果が０件であっても（レコードがまだ１件も登録されていない場合でも）実行できるようです。
サンプルソースコードはこちらからどうぞ：
https://github.com/dotnsf/pg_fieldtype
（2023-12-18 追記）
ちなみに MySQL の場合、その名もズバリの mysql ライブラリを使うのが定番だと思ってますが、こちらの場合はテーブル一覧（"show tables"）もテーブル定義（"desc （テーブル名）"）も、CLI で使う命令文をそのまま利用して取得することができるので、深く考えなくてもよいのでした。
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 で使う命令文をそのまま利用して取得することができるので、深く考えなくてもよいのでした。
