オンプレPostgreSQLをPower Appsのデータソースにしてみる

概要

オンプレPostgreSQLをデータソースにしてPower Appsで書籍管理アプリをつくりました。本来はDataverseやSharePointで完結できますが、そこはさまざまな事情があるので察していただければと思います。というわけで今回はオンプレPostgreSQLにOn-premises data gatewayを介して接続をしました。他にもさまざまな接続を試しましたが、ラインで囲った部分の環境つくってPower Appsを接続するまでの備忘録です。

今更ながら、PowerAppsからオンプレのPostgreSQLをデータソースにして試しました。 pic.twitter.com/yS8LhyStuR
— sys_zero (@sys_zero) April 24, 2022

環境

Windows 10 21H2
Microsoft 365 開発者プログラム
Power Apps 開発者プログラム
Power Automate 試用ライセンス
PostgreSQL 14.2
A5:SQL Mk-2 2.17.1 Micorosoft Store版
On-premises data gateway 3000.118.2

注意事項

接続を試す目的なのでPostgreSQLは特権ロールのまま使用します。
運用を考える場合は適切なロール設定が必要になります。
SSLの設定については触れません。
別途Npgsql-4.0.10のインストールが必要になる場合があります。
自己責任でお願いいたします。

準備

On-premises data gateway

ダウンロードセンターまたはPower Automateマイフロー画面右上のインストールボタンからダウンロードして実行します。

インストールしています。します。は相変わらずですね。完了したらサインインします。

続いてサービス設定に移動してゲートウェイサービスアカウントを設定します。

オンプレミスデータゲートウェイサービスアカウントを変更するアカウント変更から進みます。今回は個人PCでドメインに参加していないのでサービスアカウント

PC名\ユーザー名

パスワード

ユーザーのパスワード

を入力し構成で進みます。もう一度サインインが求められたら入力します。

私は回復キーで戻しましたが新規にオンプレミスデータゲートウェイを作る場合は On-premises data gatewayの名前と回復キーを入力し構成します。

構成が終わると次のようになります。

PostgreSQL

EDBからダウンロードします。今回はWindows x86-64の14.2を使用しました。

セットアップします。

デフォルト設定でどんどん進みます。

特権アカウント(postgres)のパスワードを設定します。忘れないようにします。

ポートはデフォルト

Japanese,Japanを設定しました。

進みます。

今回は使いませんがODBCドライバーをインストールしたい場合はStack Builderにチェックを入れます。Power Automate for desktopやデータフローを利用しない場合はチェックなしで終了します。（後からでもインストールできます）

利用する場合は

コマンドプロンプトから操作しやすいようにPathを通しておきます。システムの詳細設定から環境変数設定に入ります。ユーザー環境変数のPathに

C:\Program Files\PostgreSQL\14\bin

を追加します。

コマンドプロンプトから確認します。

psql --version

インストールが成功し、Pathが通っていればバージョンが返ってきます。

データベースの作成（コマンドプロンプト）

ユーザーpostgresでログインします。

psql -U postgres

パスワードを要求されるのでインストール時に設定したものを使います。

ログインできたらデータベースを作成します。今回はsampledbという名前で作成しました。

CREATE DATABASE sampledb;

CREATE DATABASEが返ってきたら

\q

を打つと終了できます。

A5:SQL Mk-2

このままコマンドプロンプトだけで進めてもよいのですが、視覚的なほうが扱いやすいためA5:SQL Mk-2を導入します。なおPostgreSQLと同時にインストールされたpgAdmin4もよさげですが今回はA5:SQL Mk-2を使います。 Microsoft Storeから検索しインストールします。

インストールが済んだら早速PostgreSQLに接続します。データベースの追加と削除

追加

PostgreSQL（直接接続）

データベースの内容登録

サーバ名　localhost
データベース名 sampledb
ユーザー名 postgres
パスワード　最初に設定したもの

設定したらテスト接続をおこないます。問題なければOKで進みます。

ツリー画面でsampledbをクリックするとログイン画面が出るので接続します。

接続するとツリーが展開されます。

カレントスキーマの中にテーブルを作成します。次のような内容で作成します。

列名	型	文字数制限
jan_code	文字	13
title	文字	100
author	文字	50
description	文字	200
price	整数
date_of_issue	文字	20
registration_date	日付

Query1のデータベースにsampledbを選択します。

Query1に次のように記述します。

CREATE TABLE books(
    jan_code CHAR(13) NOT NULL,
    title  VARCHAR(100),
    author VARCHAR(50) ,
    description VARCHAR(200),
    price INTEGER ,
    date_of_issue VARCHAR(20) ,
    registration_date DATE ,
PRIMARY KEY (jan_code));