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

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

タグ:postgres

初めて Heroku Connect を使ったアプリケーションを作ってみました。普段使わない環境や手順もあったことに加え、日本語資料をあまり多く見つけられなかったので、自分の備忘録も兼ねて一連の手順をまとめてみました:
2022022700



【Heroku Connect とは】
Heroku Connect は Heroku のアプリケーションリソースの1つで、SalesForce のデータと Heroku Postgres( PostgreSQL データベース)との双方向同期機能です。これによって SalesForce 上のデータを Postgres データベースのデータとして読み書きできるようになる(Heroku アプリケーションからは PostgreSQL DB に接続することで SalesForce のデータを読み書きできるようになる)というものです。ある意味で閉じられた SalesForce データを、オープンな Heroku アプリケーション環境の一部として取り扱うことができるようになります。

なお Heroku Connect にも Heroku Postgres にも無料枠があり、一定条件内であれば無料で動作確認程度はできるものです。

Heroku Connect について、詳しくはこちらも参照ください:
https://devcenter.heroku.com/articles/heroku-connect


【Heroku Connect の設定】
実際に Heroku アプリケーション上で Heroku Connect を有効に設定し、SQL でデータを取り出す、という手順を行うまでの設定手順を紹介します。

前提条件として、Heroku のアカウントはもちろんですが、SalesForce.com でオブジェクト開発のできるアカウントが必要です。無料の Developer Edition も用意されているので、アカウントを持っていない場合はまずはアカウントを作成しておいてください。Developer Edition はこちらから:
https://developer.salesforce.com/ja


順序としてはもう少し後でもいいと思うのですが、SalesForce.com 側の話になっているこのタイミングで Heroku Connect で読み書きするデータオブジェクトを決めておきます。私自身が SalesForce.com にあまり詳しくないので標準オブジェクトから1つ選択しますが、ここでの対象はカスタムオブジェクトでも構わないはずです。

SalesForce.com (Developer Edition)にログインし、「オブジェクトマネージャ」と書かれた箇所をクリックします:
2022022701


標準で用意されている(と、カスタムオブジェクトを追加した場合はカスタムオブジェクトも含めた)オブジェクトの一覧が表示されます。この中から Heroku Connect で取り出す対象を1つ決めます。私はよく分かっていないこともあって、標準オブジェクトでサンプルデータもはじめからいくつか格納済みの「取引先」オブジェクトを対象にする前提で以下を紹介します。別のオブジェクトを使うこともできると思いますが、適宜読み替えてください:
2022022702


オブジェクトの表示を絞り込む場合は「クイック検索」フィールドに名前を一部入力すると、オブジェクトのラベルでフィルタリングされます。下の例では「取引先」でフィルタリングした結果です。「取引先」オブジェクトは API 参照名が "Account" となっていることがわかります。この名称は後で使うことになるのでメモしておきましょう:
2022022703


SalesForce.com 側の準備はこれだけです。Heroku Connect で同期するオブジェクトデータが決まっていれば Heroku 側の準備にとりかかります。

改めて Heroku にログインし、アプリケーションを1つ作成します。以下の例では "forceobject" というアプリケーションに対して Heroku Connect を設定する想定で紹介しているので、アプリケーション名は自分のアプリケーション名に読み替えてください。またこの時点では Heroku Connect を含めたアドオンは1つも設定していないものとします:
2022022701


このアプリケーションに Heroku Connect をアドオンします。上記画面の "Configure Add-ons" と書かれた箇所をクリックします:
2022022702


アプリケーションのリソース画面に移動します。この "Add-ons" と書かれたフィールドに "Heroku Connect" と入力すると "Heroku Connect" が見つかります。見つかった名前の部分をクリックします:
2022022703


アプリケーションに Heroku Connect を追加する確認ダイアログが表示されます。使用条件と、プランが "Demo Edition - Free"(無料)となっていることを確認して "Submit Order Form" ボタンをクリックします※1:
2022022704


※1 Heroku Connect Demo Edition には以下の制約があるようです:
 ・最大 10,000 行のデータまで同期
 ・同期間隔の最小値は 10 分
 ・ログは7日間保持


アプリケーションに Heroku Connect がアドオンされました:
2022022701


続けて同期を行うデータベースである Heroku Postgres をアドオンします。先程と同様に "Add-ons" フィールドに "Heroku Postgres" と入力して、候補として見つかる "Heroku Postgres" をクリックします:
2022022701


アプリケーションに Heroku Postgres を追加する確認ダイアログが表示されます。使用条件と、プランが "Hobby Dev - Free"(無料)となっていることを確認して "Submit Order Form" ボタンをクリックします:
2022022702


これで Heroku Postgres もアドオンとして追加できました。次にこの2つ(Heroku Connect と Heroku Postgres)を接続するための設定が必要ですが、これにはスキーマと呼ばれる DB の情報が必要になります。スキーマを確認するため、画面上部の "Settings" と書かれたタブをクリックします:
2022022703


"Config Vars" 節の "Reveal Config Vars" ボタンをクリックして環境変数を確認します:
2022022702


すると環境変数 DATABASE_URL が設定されていることがわかります。その値を確認するため、 "DATABASE_URL" と書かれた行の右にある鉛筆ボタンをクリックします:
2022022703


以下のようなダイアログが表示され、"Value" と書かれたフィールドに環境変数 DATABASE_URL に設定された値を確認できます:
2022022704


この値は以下のような形式になっているはずです。この最後の "/" 文字から右にある部分がスキーマです。このスキーマ値を確認した上で Heroku Connect の設定に移ります:
postgres://*****:*******@ec2-xxx-xxx-xxx-xxx.compute-1.amazonaws.com:5432/(スキーマ)


改めて Heroku Connect の設定を続けるため、"Heroku Connect" と書かれた箇所をクリックします:
2022022701



Heroku Connect の設定画面が表示されます。"Setup Connection" ボタンをクリックします:
2022022702


以下のような画面に切り替わります。"Enter schema name" と書かれたフィールド(デフォルトでは "salesforce" と入力されたフィールド)に先程確認したスキーマ名を入力します。正しく入力できたら画面右上の "Next" ボタンをクリック:
2022022701


ここで SalesForce.com のアカウント認証が必要になります。画面右上の "Authorize" ボタンをクリック:
2022022702


SalesForce.com のログイン画面が表示されるので、ID とパスワードを入力してログインします:
2022022703


正しく認証が行われると Heroku Connection の接続が行われ、以下のような画面になります:
2022022704


Heroku Connect 側の準備としての最後にマッピングを行います。画面上部の "Mapping" タブを選択し、右下の "Create Mapping" ボタンをクリックします:
2022022701


SalesForce 側のオブジェクトの一覧が表示されます。今回は「取引先」を対象に同期したいので、"Account" オブジェクトを選択します:
2022022702


Account オブジェクトのマッピング条件を指定する画面が表示されます。デフォルトでは10分おきに SalesForce からデータベースへの同期のみが有効になっていますが、その条件を変えたい場合はここで指定します。また画面下部には Account オブジェクトの中のどの属性値を同期の対象とするかを指定する表があります:
2022022703


デフォルトでもいくつか指定されていて、そのままでもいいと思います。とりあえず取引先名称となる Name がチェックされていることを確認しておきましょう:
2022022704


画面上部に戻り、最後に "Save" ボタンをクリックして、この条件を保存します:
2022022703


指定された条件が保存され、最初の同期が行われます。少し待つと Status が "OK" となります。これで SalesForce の取引先情報が Heroku Postgres のデータベースに格納されました。Heroku Connect の設定が完了です:
2022022701



【アプリケーションから同期されたデータを参照する】
ここまでの設定ができていれば、アプリケーションから(PostgreSQL を参照して)SalesForce のデータを取り出すことができます。といっても、ここまでの設定が済んでいれば該当の PostgreSQL サーバーに接続して、以下の SQL を実行するだけ(この SQL を実行するプログラムを書くだけ)です:
select * from (スキーマ名).Account

例えば Node.js の Express フレームワークを使ったウェブアプリとして実装するとこんな感じになります:
//. app.js
var express = require( 'express' ),
    app = express();

var PG = require( 'pg' );
PG.defaults.ssl = true;
var database_url = 'DATABASE_URL' in process.env ? process.env.DATABASE_URL : ''; 
var schema = '';
if( database_url.indexOf( '/' ) > -1 ){
  var tmp = database_url.split( '/' );
  schema = tmp[tmp.length-1];
}

var pg = null;
if( database_url ){
  console.log( 'database_url = ' + database_url );
  pg = new PG.Pool({
    connectionString: database_url,
    ssl: { require: true, rejectUnauthorized: false },
    idleTimeoutMillis: ( 3 * 86400 * 1000 )
  });
}


app.get( '/', async function( req, res ){
  res.contentType( 'application/json; charset=utf-8' );

  try{
    var conn = await pg.connect();
    var sql = 'select * from ' + schema + '.Account';
    var query = { text: sql, values: [] };
    conn.query( query, function( err, result ){
      if( err ){
        console.log( err );
        res.status( 400 );
        res.write( JSON.stringify( err, null, 2 ) );
        res.end();
      }else{
        res.write( JSON.stringify( result, null, 2 ) );
        res.end();
      }
    });
  }catch( e ){
    console.log( e );
    res.status( 400 );
    res.write( JSON.stringify( e, null, 2 ) );
    res.end();
  }finally{
    if( conn ){
      conn.release();
    }
  }

});

var port = process.env.PORT || 8080;
app.listen( port );
console.log( "server starting on " + port + " ..." );

(サンプルはこちら)
https://github.com/dotnsf/forceobject


上述の環境変数 DATABASE_URL を指定して、以下のように実行します:
$ DATABASE_URL=postgres://*****:*******@ec2-xxx-xxx-xxx-xxx.compute-1.amazonaws.com:5432/ssssss node app

するとピンク色の部分で DATABASE_URL からスキーマを取り出し、青字の部分で SQL 文を生成して実行する、というものです。実行後にウェブブラウザで http://localhost:8080/ にアクセスすると以下のような SQL 実行結果を得ることができます:
2022022700


実行結果の JSON オブジェクト内の rows キーの配列値として SQL の実行結果が格納されています。各要素に含まれる属性はマッピング時に指定したものになっていて、特に name 属性には取り出したオブジェクト名称(つまり取引先の名称)が格納されていることが確認できます。


これを有効活用するには、まず自分自身がもう少し SalesForce 自体に詳しくなる必要がありそうですが、今は SalesForce 傘下にある Heroku らしいビジネス色の濃い機能が、Heroku の機能にうまく統合されて提供されているようでした。設定段階が比較的面倒な気もしていますが、一度設定できてしまえば後は普通に PostgreSQL を操作する感覚で使えるし、双方向同期を有効にすればウェブアプリ側から SalesForce のデータを活用して作成・更新・削除といった変更処理もできるようになると思います。


 

IBM Cloud から提供されている 30 日間無料 Kubernetes サービスIBM Kubernetes Service 、以下 "IKS")環境を使って利用することのできるコンテナイメージを1日に1個ずつ 30 日間連続で紹介していきます。

環境のセットアップや制約事項については Day0 のこちらの記事を参照してください。

Day 6 からはデータベース系コンテナとその GUI ツールを中心に紹介してます。Day 8 では Day 6 で紹介した MySQL と並んで多くの利用者を持つデータベース・サーバー PostgreSQL イメージをデプロイする例を紹介します。
20180804134032



【イメージの概要】
今更改めて説明する必要もないくらい有名で、かつ広く使われているオープンソースのリレーショナル・データベース・サーバーだと思っています。個人的には MySQL と PostgreSQL が2大オープンソース RDB という印象を持っています。



【イメージのデプロイ】
まずはこちらのファイルを自分の PC にダウンロードしてください:
https://raw.githubusercontent.com/dotnsf/yamls_for_iks/main/postgresql.yaml


次にこのファイルをテキストエディタで開いてパラメータを編集します。具体的には "POSTGRES_" で始まる3箇所の env.name の value 値を変更してください。それぞれの具体的な意味は以下の通りです(初期値として指定されている値のまま動かすことも可能ですが、安全のためなるべく変更してください):
・POSTGRES_PASSWORD : 管理者パスワード(初期値 P@ssw0rd)
・POSTGRES_DB : デプロイと同時に作成するデータベースの名前(初期値 mydb)
・POSTGRES_USER : デプロイと同時に作成するデータベースを利用するユーザー名(初期値 admin)

ではこのダウンロード&編集した postgresql.yaml ファイルを指定してデプロイします。以下のコマンドを実行する前に Day 0 の内容を参照して ibmcloud CLI ツールで IBM Cloud にログインし、クラスタに接続するまでを済ませておいてください。

そして以下のコマンドを実行します:
$ kubectl apply -f postgresql.yaml

以下のコマンドで PostgreSQL 関連の Deployment, Service, Pod, Replicaset が1つずつ生成されたことと、サービスが 30432 番ポートで公開されていることを確認します:
$ kubectl get all

NAME                            READY   STATUS    RESTARTS   AGE
pod/postgres-558dc49c46-wkdlp   1/1     Running   0          20s

NAME                     TYPE        CLUSTER-IP      EXTERNAL-IP   PORT(S)          AGE
service/kubernetes       ClusterIP   172.21.0.1      <none>        443/TCP          26d
service/postgresserver   NodePort    172.21.82.102   <none>        5432:30432/TCP   21s

NAME                       READY   UP-TO-DATE   AVAILABLE   AGE
deployment.apps/postgres   1/1     1            1           21s

NAME                                  DESIRED   CURRENT   READY   AGE
replicaset.apps/postgres-558dc49c46   1         1         1       21s

この後に実際にサービスを利用するため、以下のコマンドでワーカーノードのパブリック IP アドレスを確認します(以下の例であれば 161.51.204.190):
$ ibmcloud ks worker ls --cluster=mycluster-free
OK
ID                                                       パブリック IP    プライベート IP   フレーバー   状態     状況    ゾーン   バージョン
kube-c3biujbf074rs3rl76t0-myclusterfr-default-000000df   169.51.204.190   10.144.185.144    free         normal   Ready   mil01    1.20.7_1543*

つまりこの時点で(上述の結果であれば)アプリケーションは 169.51.204.190:30432 で稼働している、ということになります。早速確認してみます、と言いたいところなのですが、Day 6 の MySQL 同様、これまでのようにウェブブラウザで確認できるものではなく、専用の CLI コマンドが使えると確認できるのですが・・・ ここでは動作確認を Day 9 で行うことにして、今回はこのままにしておきます。




【YAML ファイルの解説】
YAML ファイルはこちらを使っています(編集する前の状態です):
apiVersion: v1
kind: Service
metadata:
  name: postgresserver
spec:
  selector:
    app: postgres
  ports:
  - port: 5432
    protocol: TCP
    targetPort: 5432
    nodePort: 30432
  type: NodePort
---
apiVersion: apps/v1
kind: Deployment
metadata:
  name: postgres
spec:
  replicas: 1
  selector:
    matchLabels:
      app: postgres
  template:
    metadata:
      labels:
        app: postgres
    spec:
      containers:
      - name: postgres
        image: postgres
        env:
        - name: POSTGRES_PASSWORD
          value: "P@ssw0rd"
        - name: POSTGRES_DB
          value: "mydb"
        - name: POSTGRES_USER
          value: "admin"
        ports:
        - containerPort: 5432

Deployment 1つと、Service 1つのごくごくシンプルな YAML ファイルですが、一応解説を加えておきます。アプリケーションそのものは 5432 番ポートで動作するように作られているため、NodePort 30432 番を指定して、外部からは 30432 番ポートでアクセスできるようにしています(NodePort として指定可能な番号の範囲は 30000 ~ 32767 です、指定しない場合は空いている番号がランダムに割り振られます)。また ReplicaSet は1つだけで作りました(データベースなので、別途クラスタ構成の準備をしない限りはこの数値だけを増やしてもあまり意味ないと思います)。


デプロイしたコンテナイメージを削除する場合はデプロイ時に使った YAML ファイルを再度使って、以下のコマンドを実行します。不要であれば削除しておきましょう(ちなみにこの PostgreSQL コンテナは明日の Day 9 でも使う予定なので、削除するのはそのあとの方がいいかもしれません):
$ kubectl delete -f postgresql.yaml


【紹介したイメージ】
https://hub.docker.com/_/postgres


【紹介記録】
Dayカテゴリーデプロイ内容
0準備準備作業
1ウェブサーバーhostname
2Apache HTTP
3Nginx
4Tomcat
5Websphere Liberty
6データベースMySQL
7phpMyAdmin
8PostgreSQL
9pgAdmin4
10MongoDB
11Mongo-Express
12Redis
13RedisCommander
14ElasticSearch
15Kibana
16CouchDB
17CouchBase
18HATOYA
19プログラミングNode-RED
20Scratch
21Eclipse Orion
22Swagger Editor
23R Studio
24Jenkins
25アプリケーションFX
262048
27DOS Box
28VNC Server(Lubuntu)
29Drupal
30WordPress

MySQL 派な自分にとって、初体験中の PostgreSQL の話です。CLI から利用する場合の(MySQL との)コマンドの違いに戸惑いましたが、まあ慣れてしまえばさほどは気になりません。

ただ1つ困ったことがありました。前提として自分はプログラミングで Node.js を使っていて、Node.js から PostgreSQL にアクセスするには node-postgres(pg) というライブラリパッケージを使っています。

今、ある配列(数値または文字列)があったとして、「その配列内のいずれかの値と一致する ID を持つレコードをすべて取り出す」という処理を実行したいとします。PostgreSQL 含めて一般的なリレーショナル・データベースであれば、"in" 句を使って以下のように処理できます:
> select * from mytable where id in ( '000', '001', '002' );

node-postgres を使った場合、この処理は以下のように記述することで同様に実行することができます:
var PG = require( 'pg' );
var pg = new PG.Client({ 
    "postgres://user:pass@host:5432/db"
});
pg.connect( function( err, client ){
  if( err ){
    console.log( 'err00', err );
  }else{
    client.query( { text: "select * from mytable where id in ( '000', '001', '002' )", values: [] }, function( err, result ){
      if( err ){
        console.log( 'error', err );
      }else{
        console.log( 'success', result );
      }
    });
  }
});


困ったことというのは、上述の配列部分を変数にした場合です。例えば以下のようにすると文法エラーにはなりませんが、(期待通りに展開されないのか)該当データが存在していても結果は空でした:
var PG = require( 'pg' );
var pg = new PG.Client({ 
    "postgres://user:pass@host:5432/db"
});
pg.connect( function( err, client ){
  if( err ){
    console.log( 'err00', err );
  }else{
    var ids = [ '000', '001', '002' ];
    client.query( { text: "select * from mytable where id in ( $1 )", values: [ ids ] }, function( err, result ){
      if( err ){
        console.log( 'error', err );
      }else{
        console.log( 'success', result );
      }
    });
  }
});

文法的にはこっちの書き方のほうが正しいかな、と思って以下の SQL に変えてみると、今度は文法エラーになってしまいました:
var PG = require( 'pg' );
var pg = new PG.Client({ 
    "postgres://user:pass@host:5432/db"
});
pg.connect( function( err, client ){
  if( err ){
    console.log( 'err00', err );
  }else{
    var ids = [ '000', '001', '002' ];
    client.query( { text: "select * from mytable where id in $1", values: [ ids ] }, function( err, result ){
      if( err ){
        console.log( 'error', err );
      }else{
        console.log( 'success', result );
      }
    });
  }
});

-> syntax error at or near "$1"

いずれにせよ、配列部分を SQL 内で直接記述して具体的に指定すれば動くのですが、配列を変数化して実行する正しい方法がわかりませんでした。配列をループさせて SQL 文を直に作ればできないこともなさそうですが、そうすると SQL インジェクションにも気を付ける必要がでてくるので、配列変数のままでうまいこと実行する術はないだろうか、、、と悩んでいました。

で、やっとその解決策を見つけることができました。具体的には以下の方法です:
var PG = require( 'pg' );
var pg = new PG.Client({ 
    "postgres://user:pass@host:5432/db"
});
pg.connect( function( err, client ){
  if( err ){
    console.log( 'err00', err );
  }else{
    var ids = [ '000', '001', '002' ];
    client.query( { text: "select * from mytable where id = any($1::varchar[])", values: [ ids ] }, function( err, result ){
      if( err ){
        console.log( 'error', err );
      }else{
        console.log( 'success', result );
      }
    });
  }
});


SQL 文中の "varchar" 部分はテーブル定義した際の配列要素の型です。上の例では id は文字列(varchar)だったのでこのように varchar[] となりますが、int 型で定義していた場合は int[] などとなります。

このように記述すると PostgreSQL 側が実行時に any($1::varchar[]) 部を指定された型(varchar)のパラメータに自動変換してくれるらしく、SQL インジェクションの心配もなく実現できるようでした。


(参考)
https://stackoverflow.com/questions/10720420/node-postgres-how-to-execute-where-col-in-dynamic-value-list-query



このページのトップヘ