DB操作の続きです。前回はターミナルからHeroku CLIを使ってHerokuのPostgreSQLに接続しました。今回はここからテーブルを作成してデータをCRUD操作したく思います。それではいきましょう！
【目次】
- 前回のおさらい
- アドオン確認
- DBに接続
- DBの一覧表示（←取扱注意！）、選択
- テーブル作成（CREATE TABLE）
- Create：データの追加（INSERT文）
- Read：データの取得（SELECT文）
- Update：データの更新（UPDATE文）
- Delete：データの削除（DELETE文）
- PostgreSQLの接続、解除、情報表示
- 最後に
※参考：前回記事
【SQL】ターミナルからHerokuのPostgreSQLに接続する - クモのようにコツコツと
※参考：Web開発環境についてのまとめ
qiita.com
前回のおさらい
ターミナルからPostgreSQLのDBを作成（アプリと紐付け）。中身はまだ無い。
その後、ローカル環境にPostgreSQLをインストールしてPATHを通した。
下記のコマンドでHeroku CLIからHerokuのPostgreSQLに接続することができた。
heroku pg:psql
詳細は前回記事を参照
※参考：【SQL】ターミナルからHerokuのPostgreSQLに接続する - クモのようにコツコツと
アドオン確認
ターミナルのcdコマンドでアプリのあるフォルダに移動。
cd /(パス)/node-js-getting-started
アドオンの追加状況を確認（前回は「アドオン無し」だった）
前回と同じコマンド。
$ heroku addons
結果、heroku-postgresqlが入っている！
Add-on Plan Price State ──────────────────────────────────────────────── ───────── ───── ─────── heroku-postgresql (postgresql-aerodynamic-98170) hobby-dev free created └─ as DATABASE The table above shows add-ons and the attachments to the current app (aqueous-mountain-80366) or other apps.
アプリ「aqueous-mountain-80366」と紐づいていることがわかる。
DBに接続
データベース接続。これも前回と同じコマンド。
$ heroku pg:psql
結果
--> Connecting to postgresql-aerodynamic-98170 psql (12.4) SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off) Type "help" for help.
DBに接続した！
行頭の記号が$から下記に変わる。
DATABASE=>
DBの一覧表示（←取扱注意！）、選択
こちらのPostgreSQLの基本コマンドを参考にDB情報を表示したい。
※参考：PostgreSQLの基本的なコマンド - Qiita
DBの一覧を表示するコマンドは\lのみ！（注：下記のコマンドを実行すると自分の環境では収集つかない結果になりました。。）
DATABASE=> \l
結果、なんか下記のような膨大な情報をいっぱい出てきた。。
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
----------------+----------------+----------+-------------+-------------+-----------------------------------
xxxxxxxxxxxxxx | xxxxxxxxxxxxxx | UTF8 | en_US.UTF-8 | en_US.UTF-8 | xxxxxxxxxxxxxx=CTc/xxxxxxxxxxxxxx
xxxxxxxxxxxxxx | xxxxxxxxxxxxxx | UTF8 | en_US.UTF-8 | en_US.UTF-8 | xxxxxxxxxxxxxx=CTc/xxxxxxxxxxxxxx
xxxxxxxxxxxxxx | xxxxxxxxxxxxxx | UTF8 | en_US.UTF-8 | en_US.UTF-8 | xxxxxxxxxxxxxx=CTc/xxxxxxxxxxxxxx
xxxxxxxxxxxxxx | xxxxxxxxxxxxxx | UTF8 | en_US.UTF-8 | en_US.UTF-8 | xxxxxxxxxxxxxx=CTc/xxxxxxxxxxxxxx
xxxxxxxxxxxxxx | xxxxxxxxxxxxxx | UTF8 | en_US.UTF-8 | en_US.UTF-8 | xxxxxxxxxxxxxx=CTc/xxxxxxxxxxxxxx
xxxxxxxxxxxxxx | xxxxxxxxxxxxxx | UTF8 | en_US.UTF-8 | en_US.UTF-8 | xxxxxxxxxxxxxx=CTc/xxxxxxxxxxxxxx
xxxxxxxxxxxxxx | xxxxxxxxxxxxxx | UTF8 | en_US.UTF-8 | en_US.UTF-8 | xxxxxxxxxxxxxx=CTc/xxxxxxxxxxxxxx
...
いくらスクロールしても終わらない！！！困った。。
どうにも収集つかないので別のターミナルを立ち上げる。
DBに再接続
$ heroku pg:psql
今度はDBを選択して絞り込もうか。コマンドは\cとDB名。
DATABASE=> \c postgresql-aerodynamic-98170
結果
FATAL: database "postgresql-aerodynamic-98170" does not exist Previous connection kept
致命的：データベース「postgresql-aerodynamic-98170」は存在しません
以前の接続は維持されました
むむ？「存在しません」となった。「接続は維持されました」については確かに今このDBに接続をしているわけで、あっている。。
このコマンドは違うDBに接続を変更する時に使うコマンドか。DB表示、選択は不要な作業だったかも。
テーブル作成（CREATE TABLE）
調べると下記の記事ではDBの接続後にいきなりテーブル操作に入っていた。自分もこれを参考にする。
※参考：Herokuのアドオン「Heroku Postgres」を使ってみる – kin29.info
テーブル一覧の表示のコマンドは\d。
DATABASE=> \d
結果
Did not find any relations.
関係は見つかりませんでした。
うん、そうだね。まだ作ってないからね。
ちなみにMacのバックスラッシュは「オプション」+「¥」で出る。
※参考：Macにおけるバックスラッシュ（\）の入力方法 - Qiita
テーブルの作成はCREATE TABLEコマンドを使う。
※参考：https://www.postgresql.jp/document/9.2/html/sql-createtable.html
CREATE TABLEはSQL文で以前やってMySQLのテーブル作成も同じコマンドだった。
※参考：【SQL】ターミナルからMAMPのMySQLにCRUDする - クモのようにコツコツと
ただし、MySQLとPostgreSQLでは一部異なる書き方があった（そのままだとエラーになる）。
MySQLの時は下記のように書いた。
CREATE TABLE member ( id MEDIUMINT NOT NULL AUTO_INCREMENT, name VARCHAR(100), part VARCHAR(100), PRIMARY KEY (id) );
結果、PostgreSQLだと下記のエラーになる。
ERROR: syntax error at or near "AUTO_INCREMENT" LINE 2: id MEDIUMINT NOT NULL AUTO_INCREMENT,
エラー：「AUTO_INCREMENT」またはその近くの構文エラー 2行目：id MEDIUMINT NOT NULL AUTO_INCREMENT、
2行めのid自動連番のところ、「AUTO_INCREMENT属性」はMySQLの書き方のようだ。PostgreSQLでは代わりに「SERIAL型」を使う。
※参考：PostgreSQLでMySQLのAUTO_INCREMENTを使う - Qiita
serial は正確にはデータ型ではなくてテーブルの列に一意の識別子を設定する簡便な表記法です。
※参考：https://www.postgresql.jp/document/7.3/user/datatype.html#DATATYPE-SERIAL
idのところを「SERIAL型」に書き直して再実行
CREATE TABLE member ( id SERIAL NOT NULL, name VARCHAR(100), part VARCHAR(100), PRIMARY KEY (id) );
結果、成功！
CREATE TABLE
再びテーブル一覧を表示してみる。
\d
結果、テーブルが表示された！
List of relations Schema | Name | Type | Owner --------+---------------+----------+---------------- public | member | table | xxxxxxxxxxxxxx public | member_id_seq | sequence | xxxxxxxxxxxxxx (2 rows)
memberというテーブルが追加された！member_id_seqというシーケンスも作成されたようだ。これはidの自動連番だな。
ブラウザ上の表示も「Table」が1になった！
Create：データの追加（INSERT文）
ここからいよいよCRUD操作になる。参考にした記事。
※参考：【PostgreSQL】 基本的なCRUD操作 - Qiita
基本的にMySQLと同じSQL文でいけるようだ！前回のMySQLと同じSQL文で実行してみる。
※参考：【SQL】ターミナルからMAMPのMySQLにCRUDする - クモのようにコツコツと
まずはCRUDのC、データの追加（Create）から。
INSERTでビートルズのリーダー、ジョン・レノンを追加する。
DATABASE=> INSERT INTO member (name, part) VALUES ( 'ジョン・レノン', 'ギター' );
結果、追加された！
INSERT 0 1
ブラウザの方もRowが「1」になった！
次、2人連続追加してみる。ポールとジョージ。クオリーメンのオリジナルメンバーね。
DATABASE=> INSERT INTO member (name, part) VALUES ('ポール・マッカートニー', 'ギター'), ('ジョージ・ハリスン', 'ギター');
結果、2行追加されようだ！
INSERT 0 2
ブラウザの方は…あれ？ Rowが「1」のままだ。
Read：データの取得（SELECT文）
次はCRUDのR、データの取得（Read）
SELECT文でテーブルmemberの内容を表示する。
DATABASE=> SELECT id, name, part FROM member;
結果
id | name | part ----+------------------------+-------- 1 | ジョン・レノン | ギター 2 | ポール・マッカートニー | ギター 3 | ジョージ・ハリスン | ギター (3 rows)
おお、ちゃんと3行表示される！！
INSERT文でスチュとピートも追加してみる。
INSERT INTO member (name, part) VALUES ('スチュアート・サトクリフ', 'ベース'), ('ピート・ベスト', 'ドラム');
結果、2行追加された。
INSERT 0 2
SELECT文で再度読み込む。
DATABASE=> SELECT id, name, part FROM member;
結果、ちゃんと反映されてる！
id | name | part ----+--------------------------+-------- 1 | ジョン・レノン | ギター 2 | ポール・マッカートニー | ギター 3 | ジョージ・ハリスン | ギター 4 | スチュアート・サトクリフ | ベース 5 | ピート・ベスト | ドラム (5 rows)
Update：データの更新（UPDATE文）
次はCRUDのU、データの更新（Update）！
UPDATE文でポールのパートをベースにする。
DATABASE=> UPDATE member SET part = 'ベース' WHERE id = 2;
結果、UPDATE成功したっぽい。
UPDATE 1
SELECT文で表示してみる。
DATABASE=> SELECT id, name, part FROM member;
結果
id | name | part ----+--------------------------+-------- 1 | ジョン・レノン | ギター 3 | ジョージ・ハリスン | ギター 4 | スチュアート・サトクリフ | ベース 5 | ピート・ベスト | ドラム 2 | ポール・マッカートニー | ベース (5 rows)
ポールのパートがベースになった！順番は最後に入れ替わっている（MySQLは入れ替わらなかった）。
Delete：データの削除（DELETE文）
最後、CRUDのD、データの削除（Delete）！
DELETE文でスチュとピートを削除する。
DATABASE=> DELETE FROM member WHERE id BETWEEN 4 AND 5;
結果、2行削除された。
DELETE 2
SELECT文で確認
DATABASE=> SELECT id, name, part FROM member;
結果、クオーリメンの3人に戻った。
id | name | part ----+------------------------+-------- 1 | ジョン・レノン | ギター 3 | ジョージ・ハリスン | ギター 2 | ポール・マッカートニー | ベース (3 rows)
idで削除指定したので行の順番が入れ替わっても影響はなかった！
最後にINSERT文でリンゴを追加してみる。
DATABASE=> INSERT INTO member (name, part) VALUES ('リンゴ・スター', 'ドラム');
結果、追加成功。
INSERT 0 1
SELECT文で確認
DATABASE=> SELECT id, name, part FROM member;
結果、デビュー時のファブ・フォーになったどー♪
id | name | part ----+------------------------+-------- 1 | ジョン・レノン | ギター 3 | ジョージ・ハリスン | ギター 2 | ポール・マッカートニー | ベース 6 | リンゴ・スター | ドラム (4 rows)
リンゴのidは繰り上がらずに6番目になっている。これはMySQLの時もそうだった。
PostgreSQLの接続、解除、情報表示
CRUD操作は成功したが、ブラウザの方はやはりRow「１」のままなのが気になる…
一旦PostSQLを閉じる。\qコマンド。
DATABASE=> \q
頭が$に戻る
DBに再接続してみる。
$ heroku pg:psql
結果、接続される。
--> Connecting to postgresql-aerodynamic-98170 psql (12.4) SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off) Type "help" for help.
SELECT文でデータを再度表示してみる。
SELECT id, name, part FROM member;
結果は同じ。データは保持されている。よかった。
id | name | part ----+------------------------+-------- 1 | ジョン・レノン | ギター 3 | ジョージ・ハリスン | ギター 2 | ポール・マッカートニー | ベース 6 | リンゴ・スター | ドラム (4 rows)
DBまたいったん閉じて
DATABASE=> \q
DB情報を表示してみる。
$ heroku pg:info
結果
=== DATABASE_URL Plan: Hobby-dev Status: Available Connections: 0/20 PG Version: 12.4 Created: 2020-10-12 19:55 UTC Data Size: 8.2 MB Tables: 1 Rows: 1/10000 (In compliance) Fork/Follow: Unsupported Rollback: Unsupported Continuous Protection: Off Add-on: postgresql-aerodynamic-98170
Rowsが1のままだなー。この表記はブラウザと変わらない。
最後に
ということで、HerokuのPostgreSQLに接続し、CRUD処理が成功しました！
- DBの基本操作：
/dなど2文字で済むのでとても楽だった！
（しかしDB一覧の\lは膨大な一覧が表示されてビビった。。） - テーブル作成：CREATE TABLEを使う。idの自動連番は「SERIAL型」を使う
（AUTO_INCREMENT属性」はMySQLの書き方のためエラー） データの追加（Create）：INSERT文を使う。MySQLと同じ！ データの取得（Read）：SELECT文を使う。MySQLと同じ！ データの更新（Update）：UPDATE文を使う。MySQLと同じ！順番は入れ替わる
（MySQLは入れ替わらなかった） データの削除（Delete）：DELETE文を使う。MySQLと同じ！ - DB情報の表示：ブラウザまたは
pg:info。1行のまま増えない（？）
CREATE TABLEにちょっと違いはあったもののCRUD処理はMySQLと同じ書き方でいけました！（DB情報が1行から増えないのは不思議ではあります）
次はExpressのHerokuアプリからPostgreSQLに接続してみたく！DBの設定情報は環境変数を利用する想定です。それではまた！
※参考：Web開発環境についてのまとめ
qiita.com
