Herokuのアドオン「Heroku Postgres」を使ってみる



どうも!
大好きな宮崎を旅立ちまして、名古屋市民になりました。
4日間荷物が来なかったので、
床に毛布一枚でねるという生活で腰が痛かったです。
荷物がきた時は嬉しくて、
4時間以内にすべてダンボールを開封し部屋完成させました。

Heroku好きなんですが、DB使えるの知らなかったです。
!! アドオンでPostgreSQLを使えるのです !!(無料枠あり)

使い方

1.Heroku Postgresの導入・データベースの作成

– コマンドより

$ cd [APP_NAME]
$ heroku addons:create heroku-postgresql:hobby-dev
Creating heroku-postgresql:hobby-dev on ⬢ [APP_NAME]... ⣾
Creating heroku-postgresql:hobby-dev on ⬢ [APP_NAME]... free
Database has been created and is available
 ! This database is empty. If upgrading, you can transfer
 ! data from another database with pg:copy
Created [DATABASE_NAME] as HEROKU_POSTGRESQL_GRAY_URL
Use heroku addons:docs heroku-postgresql to view documentation

データベースが作成されます。
[DATABASE_NAME]の部分に具体的なデータベース名が入ります。

$ heroku pg:info
(node:6031) [DEP0066] ...
=== DATABASE_URL
Plan:                  Hobby-dev
Status:                Available
Connections:           0/20
PG Version:            11.3
Created:               2019-06-19 01:54 UTC
Data Size:             8.1 MB
Tables:                3
Rows:                  1/10000 (In compliance)
Fork/Follow:           Unsupported
Rollback:              Unsupported
Continuous Protection: Off
Add-on:                [DATABASE_NAME]

 

– Herokuダッシュボードより
Herokuダッシュボード > Heroku Postgresを導入したいAPPを選択後、
Resorce  > Add-ons に遷移する。
「Heroku Postgres」を検索し、プランを選んでをProvisionする
今回はフリープランの、「Hobby Dev – Free」を選びました。

データベースが作成されます。
[DATABASE_NAME]の部分に具体的なデータベース名が入ります。

 

2.データベース操作

– コマンドより
※ローカルPCより、HerokuPostgresに接続して直接SQL操作をしたい場合、
ローカルPCにpsqlコマンドが導入されていないとできませんので以下を参考に導入してください。
参考:https://qiita.com/ucan-lab/items/fb74af3d78e71407db7b

$ heroku pg:psql [DATABASE_NAME]  //データベースに接続
(node:6110) [DEP0066] DeprecationWarning: OutgoingMessage.prototype._headers is deprecated
--> Connecting to [DATABASE_NAME]
psql (11.3)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.



-- テーブル作成
[APP_NAME]::DATABASE_URL=> CREATE TABLE member_tb (id serial PRIMARY KEY,user_id TEXT NOT NULL,display_name TEXT NOT NULL);
CREATE TABLE

[APP_NAME]::DATABASE_URL=> SELECT * FROM member_tb;
 id | user_id | display_name
----+---------+--------------
(0 rows)



-- レコード挿入
[APP_NAME]::DATABASE_URL=> INSERT INTO member_tb(user_id, display_name) VALUES ('abc123', 'taro');
INSERT 0 1

[APP_NAME]::DATABASE_URL=> SELECT * FROM member_tb;
 id | user_id | display_name
----+---------+--------------
  1 | abc123  | taro
(1 row)

 

– Herokuダッシュボードより
Datestoresにてデータベースを選択後、
Dataclips > Create a new dataclip

ここでは、読み込み(SELECT)のみ実行可能。
書き込み(CREATE TABLEやINSERT)はできませんでした・・・。

 

 

3.アプリとデータベースの接続例

データベースが作成されると自動で、
Setting > Config Varsに「DATABASE_URL」が追加されます。
これをgetenvで使用します!
※データベースが複数存在する場合、「HEROKU_POSTGRESQL_GRAY_URL」「HEROKU_POSTGRESQL_MAROON_URL」のように追加されます。

PHP Data Objects (PDO) 拡張モジュールを使いました。

<?php

$url = parse_url(getenv('DATABASE_URL'));
$dsn = sprintf('pgsql:host=%s;dbname=%s', $url['host'], substr($url['path'], 1));
$pdo = new PDO($dsn, $url['user'], $url['pass']);


$stmt = $pdo->prepare("SELECT * FROM member_tb WHERE id = ?");
$stmt->execute([$id]);
$result = $stmt->fetch();

 

参考:

http://neos21.hatenablog.com/entry/2018/12/06/080000
https://qiita.com/ucan-lab/items/fb74af3d78e71407db7b

まとめ

無料枠あるのすごいありがたいです^^
これでいっぱいアプリ作れますね。
わたしはHerokuPostgresを使ってLINEBOT作成中です。
近々、BOTの記事投稿できるように頑張りまーす!



達人に学ぶSQL〜CASE式〜

おはようございます!愛知に行ってきます!
ちびまる子ちゃんランドに行くのがたのしみです。

https://amzn.to/3vcFDbG

この本のサブタイトルが通り、
「初心者で終わりたくない」ので読んでます。
中級者向けですね、わたしにはちょいムズです^^;
ただ、読み込めばいけそうな気がしてます!

 

今回は、その本の最初の節である「CASE式のススメ」を自分なりにまとめます。

CASE式の種類

– 単純CASE式
– 検索CASE式
「単純CASE式」で書ける =「検索CASE式」でも書ける

-- 単純CASE式
CASE  sex
  WHEN 1 THEN '男'
  WHEN 2 THEN '女'
ELSE 'その他' END

上の単純CASE式を検索CASE式で書くと、以下になります。

-- 検索CASE式
CASE
  WHEN sex = 1 THEN '男'
  WHEN sex = 2 THEN '女'
ELSE 'その他' END

CASE式の注意点

– CASE式の評価は真になるWHEN句が見つかった時点で打ち切られる。
よって、下位のWHEN句は無視される。

– 各分岐が返すデータの型は統一させる

– 「END」を忘れないようにする。→エラーはでる。

– 「ELSE X」は省略することができる。その場合は、「ELSE NULL」になる。
明示的に「ELSE X」は書くようにした方がいい!

– 「CASE文」ではない。「CASE式」

SELECT と GROUP BY で 同じCASE式を使う

→SELECT句の「AS {別名}」をGROUP BYで流用することができる。

※PostgrSQL,  MySQLで使用可

こんなテーブル(pop_tbl)があったとして、

# SELECT * FROM pop_tbl;
 pref_name | population 
-----------+------------
 徳島      |        100
 香川      |        200
 愛媛      |        150
 高知      |        200
 福岡      |        300
 佐賀      |        100
 長崎      |        200
 東京      |        400
 群馬      |         60

[結果] ←こうしたい

 region | sum 
--------+-----
 その他 | 660
 四国   | 650
 九州   | 400

別名を流用せずに書くと、こんな感じで長いです。

SELECT
  (CASE pref_name
    WHEN '徳島' THEN '四国'
    WHEN '香川' THEN '四国'
    WHEN '愛媛' THEN '四国'
    WHEN '高知' THEN '四国'
    WHEN '佐賀' THEN '九州'
    WHEN '福岡' THEN '九州'
  ELSE 'その他' END) AS region
  ,SUM(population)
FROM pop_tbl
GROUP BY  
  (CASE pref_name
    WHEN '徳島' THEN '四国'
    WHEN '香川' THEN '四国'
    WHEN '愛媛' THEN '四国'
    WHEN '高知' THEN '四国'
    WHEN '佐賀' THEN '九州'
    WHEN '福岡' THEN '九州'
  ELSE 'その他' END);

上記のような長いSQLを以下のように短くすることができます。

SELECT
  (CASE pref_name
    WHEN '徳島' THEN '四国'
    WHEN '香川' THEN '四国'
    WHEN '愛媛' THEN '四国'
    WHEN '高知' THEN '四国'
    WHEN '佐賀' THEN '九州'
    WHEN '福岡' THEN '九州'
  ELSE 'その他' END) AS region
  ,SUM(population)
FROM pop_tbl
GROUP BY region;

 

クロス形式で表にする

こんなテーブル(manju_tbl)があったとして、

# SELECT * FROM manju_tbl;
  name  | anco_id | count 
--------+---------+-------
 太郎   |       1 |    10
 太郎   |       2 |    10
 二郎   |       1 |    10
 二郎   |       2 |     0
 小次郎 |       1 |    14
 小次郎 |       2 |     8

※anco_id = 1→「黒あん」、anco_id = 2→「白あん」のイメージです。

[結果] ←こうしたい。
各人がkuroan_count/shiroroan_countを何回ずつ食べたのか1行でみれるようにしたい。

  name  | kuroan_count | shiroroan_count 
--------+--------------+-----------------
 二郎   |           10 |               0
 小次郎 |           14 |               8
 太郎   |           10 |              10

こんな感じです。
nameごとに1行にしたいので、GROUP BY nameを忘れない!
SUMで合計するので、ELSE 0にしにしたほうが読みやすいです。

SELECT
  name
  ,SUM(CASE WHEN anco_id = 1 THEN count ELSE 0 END) AS kuroan_count
  ,SUM(CASE WHEN anco_id = 2 THEN count ELSE 0 END) AS shiroroan_count
FROM manju_tbl
GROUP BY name;

 

CHECK制約でCASE式を使う

CONSTRAINT {制約名}  CHECK ({制約内容})

とし、制約に名前がつけれます。
これにより、エラー発生時に制約名が表示されるので、
どこで引っかかったのかわかりやすくなります。
CREATE TABLEやALTER TABLEで使えます。

 

今回の例では、
「女性社員の給与は20万円以下である」
という制約をみたすためのテーブル作成時のSQLです。
※この制約のポイントは男性社員に関しては、給与の制約がないことです。

CREATE TABLE Salaries(
  name VARCHAR(50),
  sex SMALLINT,
  salary INT CONSTRAINT Salaries_salary_check CHECK (
    CASE WHEN sex = 2
         THEN CASE WHEN salary <= 200000
                   THEN 1 ELSE 0 END
    ELSE 1 END = 1
  )
);
--すでにテーブル Salariesがあり、カラムsexとカラムsalaryがある前提
ALTER TABLE Salaries ADD CONSTRAINT
  Salaries_salary_check CHECK (
    CASE WHEN sex = 2 
         THEN CASE WHEN salary <= 200000
                   THEN 1 ELSE 0 END
    ELSE 1 END = 1
 );

 

データ挿入時に、制約が走ります。

-- 以下3つの INSERT文は 正常に追加されます。
INSERT INTO salaries VALUES('太郎', 1, 220000);  
INSERT INTO salaries VALUES('二郎', 1, 190000);
INSERT INTO salaries VALUES('花子', 2, 200000);
-- 制約に違反すると
-- 以下のSQL エラー と表示されます。
-- ERROR:  new row for relation "salaries" violates check constraint "salaries_salary_check"

INSERT INTO salaries VALUES('卑弥呼', 2, 220000);

 

 

制約名は省略もできます。

・制約名を省略したパターン

CREATE TABLE Salaries2(
  name VARCHAR(50),
  sex SMALLINT,
  salary INT CHECK (
    CASE WHEN sex = 2
         THEN CASE WHEN salary <= 200000
                   THEN 1 ELSE 0 END
    ELSE 1 END = 1
  )
);
--すでにテーブル Salaries2があり、カラムsexとカラムsalaryがある前提 
ALTER TABLE Salaries4 ADD CHECK (
 CASE WHEN sex = 2 
      THEN CASE WHEN salary <= 200000
                THEN 1 ELSE 0 END
 ELSE 1 END = 1
);

・エラーの場合の表記

-- ERROR: new row for relation "salaries2" violates check constraint "salaries2_check"

制約名を省略した場合のエラー時の制約名は、

制約名(デフォルト): {テーブル名}_check

ってなってますね。

 

PostgreSQL 9.4.5文書 では、以下のように書いてますね。詳細わかりませんね。

(この方法で制約名を指定しない場合は、システムにより名前が付けられます。)

 

まとめ

CASE式は、実行時に評価されて1つの値に定まるので、

列名や定数がかける部分であれば、どこにでもかけます!

今回例を出した以外の、UPDATE文や集約関数のなかでもCASE式は使うことができてとても便利です。

複数レコードをカンマ区切りの1レコードにまとめる方法

※PostgresSQLでの方法です。Mysqlとかは知りません。

こんな2つのテーブルがあったとして、、

 

<category_table>

category_id category_name
1 html
2 SQL
3 Git
4 PHP

 

<story_table>

story_id category_id
1 1
1 2
1 3
2 2
2 4
3 1
4 4

 

「story_idごとにcategory_nameをカンマ区切りで表示したい。」
そんな時あると思います!

 

 

そんな時は・・・

ARRAY_TO_STRING(カラム, ‘,’)

=配列を「,」区切り文字列にする

・・・ 「カラム, カラム, カラム」こんな感じになる。ウオー

 

 

 

で、やりたいこと

「story_idごとにcategory_nameをカンマ区切りで表示したい。」

を実現する方法

 

 

SELECT
  story_id,
  ARRAY_TO_STRING(ARRAY_AGG(category_name), ‘,’)
FROM   category_table
LEFT JOIN   story_table
USING(category_id)
GROUP BY
  story_id;

 

ARRAY_AGG(カラム)

….複数行→配列に変換

今回はARRAY()ではダメだった

 

GROUP BY story_idで

story_idごとのcateory_nameを1行にするのがポイント!