Corredor

ウェブ、プログラミングの勉強メモ。

「pg」パッケージを使ってローカルの PostgreSQL や Heroku Postgres に接続する

Heroku Postgres をいじり始め、ローカルにも PostgreSQL 環境を構築したので、Node.js スクリプトから PostgreSQL DB に接続してみようと思う。

今回は、Heroku の公式リファレンスにも紹介されていた pg という npm パッケージを使ってみる。

素振り環境の用意

まずはお試し作業ディレクトリを作成し、pg パッケージをインストールする。

$ mkdir practice-pg && cd $_
$ npm init -y

# pg パッケージをインストールする
$ npm install --save pg

# サンプルコードを実装するファイルを作成する
$ touch main.js

以降、main.js ファイルにコードを実装していく。

PostgreSQL への接続文字列を確認する

PostgreSQL への接続の仕方は、ホスト・ユーザ名などをそれぞれ指定するパターンと、全てを盛り込んだ一つの「接続文字列」とするパターンがある。今回は管理が楽なので、「接続文字列」を渡して接続してみようと思う。

PostgreSQL の接続文字列の書式は以下のとおり。

postgres://username:password@hostname:port/database

つまり、ユーザ名「postgres」、パスワードが「PASSWORD」なユーザを使い、localhost:5432my_local_db というデータベースに接続するには、以下のように記述する。

postgres://postgres:PASSWORD@localhost:5432/my_local_db

Heroku Postgres の場合、Heroku Postgres をインストールすると、ダッシュボード上で確認できる環境変数の一つに DATABASE_URL というモノが追加されており、このような接続先文字列が環境変数に定義されている。本番環境ではこの環境変数を参照することを想定して、ローカル環境でも接続先文字列の書式でアクセスするコードを作っておけば、開発環境・本番環境ともにうまく動作させられるだろう。

ローカルの PostgreSQL にテスト用データベースとテーブルを用意する

DB に接続するコードを書く前に、まずはお試し用のデータベースを新たに作っておき、ダミーデータを入れておこうと思う。先程の例で「my_local_db」というデータベースに接続するような接続文字列を作ったので、my_local_db データベースを作ってみる。

$ psql -U postgres
# パスワードを入力する

# 接続完了。「my_local_db」という名前のデータベースを新規作成する
postgres=# create database my_local_db;
CREATE DATABASE

# 参照するデータベースを切り替える
postgres=# \c my_local_db;
データベース "my_local_db" にユーザ "postgres" として接続しました。

# プロンプトが変わる
my_local_db=#

# テスト用にテーブルを作り、データを入れる
my_local_db=# create table test_table ( id integer primary key, name text );
my_local_db=# insert into test_table ( id, name ) values ( 1, 'ローカルテスト1' );
my_local_db=# insert into test_table ( id, name ) values ( 2, 'ローカルテスト2' );
my_local_db=# select * from test_table;
# データが2件取得できるはず

最後に叩いた SELECT 文と同様のことを、このあと pg パッケージで行う、というワケ。

Heroku Postgres にもテスト用データを入れる

ローカル環境の PostgreSQL と同じことを、Heroku Postgres にもやってみる。Heroku CLI のコマンドを使えばデータベースごと相互に移行したりできるのだが、今回はそうしたバックアップの用途ではないので、サクッとお試しテーブルを作ることにする。

# Heroku Postgres に接続する
$ heroku psql

# データベースはデフォルトのままにしておく
# テストテーブル作ってみる
example-app::DATABASE=> create table test_table ( id integer primary key, name text );
example-app::DATABASE=> insert into test_table ( id, name ) values ( 1, 'Heroku テスト1' );
example-app::DATABASE=> insert into test_table ( id, name ) values ( 2, 'Heroku テスト2' );
example-app::DATABASE=> select * from test_table;
# データが2件取得できるはず

ちょっとだけ登録したデータの文言を変えているので、このあと pg パッケージからデータを取得した時に、どちらに接続しているか一目瞭然になるかと思う。

ローカルの PostgreSQL に接続する Node.js スクリプトを書いてみる

いよいよ pg パッケージを使用して DB に接続する、簡単な Node.js スクリプトを書いてみる。コードは Heroku 公式にあるサンプルコードが参考になるだろう。

自分は Client ではなく Pool を使って、またコールバック関数形式ではなく Promise 形式で書いてみた。main.js のコードは以下のとおり。

const pg = require('pg');

// 接続先文字列
const connectionString = 'postgres://postgres:PASSWORD@localhost:5432/my_local_db';

console.log(`接続開始 : ${connectionString}`);
const pool = new pg.Pool({
  connectionString: connectionString
});

// SELECT してみる
pool.query('SELECT * FROM test_table')
  .then((result) => {
    console.log('Success', result);
    // 結果データの表示
    if(result.rows) {
      result.rows.forEach((row, index) => {
        console.log(index + 1, row);
      });
    }
  })
  .catch((error) => {
    console.log('Failure', error);
  })
  .then(() => {
    console.log('切断');
    pool.end();
  });

あとはコレを $node main.js のように実行する。コンソールに SELECT したデータが表示されたことだろう。

ちなみに、pool.query() 部分を以下のように書けば、PreparedStatement も書ける。

pool.query({
  text: 'UPDATE test_table SET name = $1 WHERE id = $2',
  values: ['テストリネーム2', 2]
})

その他 pg (node-postgres) パッケージの使い方は公式を参照。

Heroku Postgres に接続する Node.js スクリプトに作り変える

さて、このままでは、main.jslocalhost の DB に接続を試みてしまうので、このスクリプトを Heroku 上にデプロイしても、Heroku Postgres とは接続してくれない。

そこで、先程も書いたように、Heroku Postgres アドオンをインストールした時に自動設定される DATABASE_URL 環境変数を参照して、接続先文字列を取得するように、スクリプトを書き換えてみる。

といっても簡単で、const connectionString 部分を以下のように変更するだけ。

const connectionString = process.env.DATABASE_URL || 'postgres://postgres:postgres@localhost:5432/my_local_db';

ローカルのコンソール上で、Heroku Postgres に接続するようにしてみたければ、以下のように環境変数を設定してから Node.js スクリプトを実行すれば良い。

# Heroku Postgres の接続先文字列を export コマンドで環境変数として設定する
$ export DATABASE_URL=postgres://hogehoge:fugafuga@ec0-00-00-00-000.compute-1.amazonaws.com:5432/useruser

# 先程のスクリプトを実行する
$ node main.js

ココで、自分の場合は以下のようなエラーメッセージが表示された。

Failure { error: no pg_hba.conf entry for host "153.184.168.17", user "USERNAME", database "MY_POSTGRES_DB", SSL off

どうも Heroku Postgres と SSL 通信ができていないせいみたい。色々調べたが、Heroku 上の環境変数に PGSSLMODE=require もしくは PGSSLMODE=allow というモノを設定しておくと上手く行った。ローカルのターミナルでは同じく export コマンドで設定しておけば動作する。

$ export PGSSLMODE=allow

Heroku の環境変数は Heroku CLI から heroku-config というプラグインを使って設定できるので、以下のように設定してみよう。ブラウザでダッシュボードを開いた時に、ちゃんと環境変数が設定されているはずだ。

# 環境変数を扱うためのプラグインをインストールしておく
$ heroku plugins:install heroku-config

# 環境変数を設定する
$ heroku config:set PGSSLMODE=allow
Setting PGSSLMODE and restarting ⬢ example-app... done, v6

この辺イマイチ分かっていない。

以上

ざっとこんな感じ。このままでは main.js はただの Node.js スクリプトなので、Web アプリからのリクエストに応じて呼び出し、データを返すような実装にしていかないと、アプリっぽくならない。

また、生の SQL をシコシコ書くのは大変なので、実際のアプリ開発では Sequelize などもう少し楽になるライブラリを使って DB 接続した方が良さそうだ。

PostgreSQL: Up and Running: A Practical Guide to the Advanced Open Source Database (English Edition)

PostgreSQL: Up and Running: A Practical Guide to the Advanced Open Source Database (English Edition)

psql コマンドで PostgreSQL に接続する時パスワード入力を省略する方法

psql コマンドで PostgreSQL に接続する時、いつも対話式プロンプトでパスワードを尋ねられる。コレが面倒なので省略したい。

調べたところ、専用の設定ファイルを用意して、接続先 URL やユーザ名とともにパスワードを書いておけば、それを読み取ってアクセスしてくれるようだ。

だが、Windows と Mac とで設定ファイルの配置場所と名前に違いがあったので、それぞれ紹介する。

Mac での設定方法

Mac の場合は、ホームディレクトリ.pgpass というファイルを置く。~/.pgpass である。

# 「localhost:5432」の「my_local_db」という DB に対し、ユーザ名「postgres」、パスワード「PASSWORD」で接続する、という設定
# ホームディレクトリ直下に .pgpass というファイルを生成してこの内容を記載する
$ echo 'localhost:5432:my_local_db:postgres:PASSWORD' > ~/.pgpass

# .pgpass ファイルはアクセス権を 600 に設定しないといけないので設定する
$ chmod 600 ~/.pgpass

Windows での設定方法

Windows の場合は、%APPDATA%\postgresql\ 配下に pgpass.conf というファイル名で設定ファイルを置く。ホームディレクトリ (C:\Users\【ユーザ名】\) 直下ではなく、ファイル名も .pgpass ではなく pgpass.conf であることに注意。

このあとも説明するが、pgpass.conf の書式は、Mac の .pgpass と同じ。Windows ではアクセス権の設定はしなくても大丈夫なようだ。

pgpass ファイルの書式

上述の Mac の場合のコマンドにも書いたが、pgpass ファイルの書式は以下のとおり。

hostname:port:database:username:password

全てコロン : で区切る。

つまり、「localhost」の「5432」ポート、「my_local_db」という名前のデータベースに接続するとして、ユーザ名「postgres」、パスワードが「PASSWORD」なら、以下のように記述する。

localhost:5432:my_local_db:postgres:PASSWORD

以上

コレで次のように psql コマンドを叩くと、パスワード入力を求められることなく接続できるようになった。

$ psql -U postgres --dbname=my_local_db
psql (11.0)
Type "help" for help.

my_local_db=#

[改訂新版]内部構造から学ぶPostgreSQL 設計・運用計画の鉄則 (Software Design plus)

[改訂新版]内部構造から学ぶPostgreSQL 設計・運用計画の鉄則 (Software Design plus)

Windows と macOS に EnterpriseDB 版の PostgreSQL をインストールする

Heroku Postgres をいじり始めたが、ローカル開発環境にも PostgreSQL 環境が欲しいので、Windows と Mac それぞれで PostgreSQL をインストールしてみる。

Windows なら Chocolatey、Mac なら Homebrew からも PostgreSQL がインストールできるが、ウィザード形式で簡単にインストールができるので、PostgreSQL の公式サイトで紹介されている EnterpriseDB 版を使用してみる。

(調べた感じ、Homebrew で落とせる PostgreSQL のバージョンが少し古そうだったのと、インストール後の設定が面倒臭そうだったので、Windows 版で試したことがあった、このインストーラ版を利用することにした)

インストーラをダウンロードする

まずは公式サイトからインストーラをダウンロードする。

それぞれの OS のページから「Download the installer」リンクを押し、PostgreSQL v11 のインストーラをダウンロードする。

  • Windows の場合は postgresql-11.1-1-windows-x64.exe
  • Mac の場合は postgresql-11.1-1-osx.dmg

というファイル名だった (v11.1.1 が本稿執筆時点の最新版)。

ウィザードに従ってインストールする

ダウンロードしたファイルを開いてインストーラを起動する。質問される内容は以下のとおり。

  • インストール先 : デフォルトのままで OK
  • インストール内容 : デフォルトのままで OK
  • データディレクトリ : デフォルトのままで OK
  • パスワード : 初期ユーザとして postgres ユーザが作られるので、このユーザのパスワードを決める。ユーザ名と同じ postgres でもなんでも適当に決める
  • ポート : デフォルトの 5432 のままで OK
  • ロケール : Windows においてエンコーディングの問題などが起こるようなので、「C」を選択するのが良いらしい。Mac でも「C」を選択した。

ウィザードの内容は Windows・Mac ともに同じ。

環境変数を設定する

Windows の場合は「システム詳細設定」の「環境変数」にて、Mac の場合は ~/.bash_profile1 にて、それぞれ PostgreSQL の環境変数を PATH に追加する。

  • Windows : C:\Program Files\PostgreSQL\11\bin;【PATH】
  • Mac : export PATH=/Library/PostgreSQL/11/bin:$PATH

このようにすると、ターミナルから PostgreSQL に接続するための psql コマンドが使えるようになった。

# デフォルトの postgres ユーザでログインする
$ psql -U postgres

# パスワードを尋ねられるので入力する

Mac のみ : SQL Shell (psql).app を使ってみる

インストールが完了すると、Applications/ 配下に PostgreSQL 11/ ディレクトリができている。SQL Shell (psql).app を開くと psql コマンドと同等の処理ができる。

$ /Library/PostgreSQL/11/scripts/runpsql.sh; exit
Server [localhost]: 
Database [postgres]: 
Port [5432]: 
Username [postgres]:  # ココまでは未入力のまま Enter で進める
Password for user postgres: 【パスワードを入れる】
psql (11.0)
Type "help" for help.

# DB 接続できた。試しにデータベース一覧を見てみる
postgres=# \l
                             List of databases
   Name    |  Owner   | Encoding  | Collate | Ctype |   Access privileges   
-----------+----------+-----------+---------+-------+-----------------------
 postgres  | postgres | SQL_ASCII | C       | C     | 
 template0 | postgres | SQL_ASCII | C       | C     | =c/postgres          +
           |          |           |         |       | postgres=CTc/postgres
 template1 | postgres | SQL_ASCII | C       | C     | =c/postgres          +
           |          |           |         |       | postgres=CTc/postgres
(3 rows)

ココで初めて気付いたのだが、Windows ではデフォルトのデータベースのエンコーディングが「UTF8」になっていたのに、Mac では「SQL_ASCII」になっていた。以下のような update 構文でエンコーディングを修正できる。

postgres=# update pg_database set encoding = pg_char_to_encoding('UTF8') where datname = 'postgres';
postgres=# update pg_database set encoding = pg_char_to_encoding('UTF8') where datname = 'template0';
postgres=# update pg_database set encoding = pg_char_to_encoding('UTF8') where datname = 'template1';

# 再確認。Encoding が UTF8 になった
postgres=# \l
                             List of databases
   Name    |  Owner   | Encoding | Collate | Ctype |   Access privileges   
-----------+----------+----------+---------+-------+-----------------------
 postgres  | postgres | UTF8     | C       | C     | 
 template0 | postgres | UTF8     | C       | C     | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
 template1 | postgres | UTF8     | C       | C     | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
(3 rows)

# 試しに DB を作る
postgres=# CREATE DATABASE my_local_db;

# ちゃんと UTF8 になった
postgres=# \l
                              List of databases
    Name     |  Owner   | Encoding | Collate | Ctype |   Access privileges   
-------------+----------+----------+---------+-------+-----------------------
 my_local_db | postgres | UTF8     | C       | C     | 
 postgres    | postgres | UTF8     | C       | C     | 
 template0   | postgres | UTF8     | C       | C     | =c/postgres          +
             |          |          |         |       | postgres=CTc/postgres
 template1   | postgres | UTF8     | C       | C     | =c/postgres          +
             |          |          |         |       | postgres=CTc/postgres

以上

コレでローカルの PostgreSQL の環境準備は OK だ。今度はこの PostgreSQL 環境に Node.js スクリプトから接続してみようと思う。

参考

PostgreSQL全機能バイブル

PostgreSQL全機能バイブル

Heroku アプリに PostgreSQL を導入する

最近 Heroku いじりを始めて、サーバサイドにも触れる機会ができた。サーバサイドといったら DB 使ってデータ永続化でしょ、ということで、今回は Heroku アプリに PostgreSQL を導入してみる。

Heroku Postgres は無料で使える

Heroku の PostgreSQL、公式サイトでは Heroku Postgres と表記されているが、コチラは無料利用も可能だ。無料枠である hobby-dev プランでは、全部で1万行のデータまでというデータ容量制限があったり、メモリキャッシュができないなど、Web サービスを本運用するには難しい制限がかかっているが、個人の開発用途なら十分であろう。

プランの詳細はコチラに記載アリ。

Web ダッシュボードから PostgreSQL を導入する

今回は試しに、Heroku の Web 上のダッシュボード (管理画面) から PostgreSQL を導入してみた。

PostgreSQL を導入したい Heroku アプリのダッシュボードに行き、右上のメニューから「Data」を選び、「Heroku Postgres」を選択してインストールする。

f:id:neos21:20181120230812p:plain

f:id:neos21:20181120230809p:plain

f:id:neos21:20181120230806p:plain

f:id:neos21:20181120230803p:plain

f:id:neos21:20181120230800p:plain

…コレだけ。すごい、メチャクチャ簡単に DB もらえた。

Heroku CLI から PostgreSQL を導入する

今回試さなかったが、Heroku CLI からも同様のインストール作業ができるようだ。

$ heroku addons:create heroku-postgresql:hobby-dev

PostgreSQL は「Addon」としてインストールするようだ。

Heroku Postgres の状況確認

PostgreSQL をインストールすると、heroku pg コマンドで Heroku Postgres を操作できるようになる。自分のローカル開発環境には既に PostgreSQL をインストールしていたので分からなかったのだが、もしかしたらローカル環境に PostgreSQL をインストールしておかないといけないかもしれない。

インストールした PostgreSQL の状況は以下のように確認できる。

$ heroku pg:info
=== DATABASE_URL
Plan:                  Hobby-dev
Status:                Available
Connections:           0/20
PG Version:            10.5
Created:               2018-11-06 13:43 UTC
Data Size:             7.6 MB
Tables:                0
Rows:                  0/10000 (In compliance)
Fork/Follow:           Unsupported
Rollback:              Unsupported
Continuous Protection: Off
Add-on:                postgresql-globular-28873

hobby-dev プラン (無料枠) で、Status は Available (有効) なようである。

Web 上の管理画面では、接続情報が確認できる。

f:id:neos21:20181120230835p:plain

また、アプリケーションから DB 接続するための接続文字列が、環境変数 DATABASE_URL として自動的に追加されていた。

f:id:neos21:20181120230832p:plain

Heroku Postgres に接続する

Heroku Postgres にコンソールからアクセスして、直接 SELECT 文や INSERT 文を流したりもできる。通常の PostgreSQL で使用する psql コマンドの代わりに、heroku pg:psql コマンドで接続できる。

$ heroku pg:psql
--> Connecting to postgresql-globular-28873

違いが分からなかったのだが、$ heroku psql コマンドでも接続できた。

このあとやりたいこと

Heroku アプリで PostgreSQL が使えるようになったワケだが、このままではまだ開発がやりづらい。まずはローカル開発環境にも同等の PostgreSQL 環境が欲しい。

そして、Node.js で PostgreSQL に接続してみたいと思う。まずは公式のリファレンスにもある pg パッケージを使って操作してみたい。

さらに、ローカルの PostgreSQL と、Heroku Postgres との接続情報を、環境変数で切り替えられるようにしたい。

以降、このあたりを整理してやっていこうと思う。

これからはじめる PostgreSQL入門

これからはじめる PostgreSQL入門