Sinatraを使ったメモアプリのDB版を進めています。
テーブルを作成するDDL文を書いたSQLファイルを読み込み、PostgreSQLにテーブル定義したいと思ったので調べてみました。
結論
psql
コマンドの-f
オプションを使ってDDLを定義したSQLファイルを読み込んでテーブル定義する。
psql
コマンドの-c
と-f
オプション
コマンドラインから実行するpsql
コマンドは次のようなオプションが用意されている。
# コマンドラインからSQLを実行(SQL文の末尾の;は不要)
$ psql -d DB名 -c "SQL"
# コマンドラインからファイルを実行
$ psql -d DB名 -f ファイル名
注意点として-d
オプションでDBを指定しない場合はデフォルトのDBに対してSQL、ファイルが実行されてしまいます。
デフォルト以外のDBに対してSQL、ファイルを実行したい場合は-d
でDBを指定してください。
ファイルから SQL を読み込む (MySQL, PostgreSQL, SQLite3) - CUBE SUGAR CONTAINER
コマンドラインからDB作成、テーブル作成、初期データ投入までを行う
DDL文でのテーブル定義に加えて、初期データの投入まで同時に行います。
下記構成でDB、テーブルを作成します。
- DB名:
memo_app
- テーブル名:
memos
カラム名 |
データ型 |
制約 |
id |
CHAR(36) |
PRIMARY KEY |
title |
VARCHAR(100) |
NOT NULL |
content |
VARCHAR(255) |
なし |
投入する初期データです。
id |
title |
content |
bc9db029-408a-4f5b-bf49-5e5af9581532 |
memo1 |
this is memo1 |
c9e67c66-4219-411f-ab7f-625a7e5e4ceb |
memo2 |
this is memo2 |
下記のSQLをコマンドラインから実行してDB作成、テーブル作成、初期データの投入まで行います。
CREATE DATABASE memo_app;
CREATE TABLE memos(id CHAR(36) NOT NULL, title VARCHAR(100) NOT NULL, content VARCHAR(255), PRIMARY KEY (id));
INSERT INTO memos (id, title, content) VALUES ('bc9db029-408a-4f5b-bf49-5e5af9581532', 'memo1', 'this is memo1');
INSERT INTO memos (id, title, content) VALUES ('c9e67c66-4219-411f-ab7f-625a7e5e4ceb', 'memo2', 'this is memo2');
1. 初期化ファイルを作成
今回はSinatraを使ったアプリのカレントディレクトリにinitialize.sql
という初期化ファイルを作成しました。
ファイルの実行内容は次の2つです。
CREATE TABLE memos(id CHAR(36) NOT NULL, title VARCHAR(100) NOT NULL, content VARCHAR(255), PRIMARY KEY (id));
INSERT INTO memos (id, title, content) VALUES ('bc9db029-408a-4f5b-bf49-5e5af9581532', 'memo1', 'this is memo1');
INSERT INTO memos (id, title, content) VALUES ('c9e67c66-4219-411f-ab7f-625a7e5e4ceb', 'memo2', 'this is memo2');
ちなみに、今回はsql
という拡張子にしていますがテキストファイルなどでも大丈夫らしいです。
.sql【拡張子】とは|「分かりそう」で「分からない」でも「分かった」気になれるIT用語辞典
2. DB作成、初期化ファイルの実行
macにPostgreSQLがインストール済みであることを前提にしています。僕はHomebrewでPostgreSQLをインストールしました。
次のコマンドの実行でDB作成、テーブル作成、初期データの投入までが完了します。
# アプリのディレクトリに移動
$ cd app_name
# memo_app DBを作成
$ psql -c "CREATE DATABASE memo_app"
# テーブル作成、初期データ投入用SQLファイルを実行
$ psql -d memo_app -f initialize.sql
DB作成、テーブル作成、初期データ投入の確認。
# DB作成を確認
❯ psql -l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
karlley | karlley | UTF8 | C | C |
memo_app | karlley | UTF8 | C | C |
postgres | karlley | UTF8 | C | C |
template0 | karlley | UTF8 | C | C | =c/karlley +
| | | | | karlley=CTc/karlley
template1 | karlley | UTF8 | C | C | =c/karlley +
| | | | | karlley=CTc/karlley
(5 rows)
# memo_app DBを指定して接続
❯ psql -d memo_app
psql (14.4)
Type "help" for help.
# テーブル作成を確認
memo_app=# \d memos;
Table "public.memos"
Column | Type | Collation | Nullable | Default
id | character(36) | | not null |
title | character varying(100) | | not null |
content | character varying(255) | | |
Indexes:
"memos_pkey" PRIMARY KEY, btree (id)
# 初期データ投入を確認
memo_app=# SELECT * FROM memos;
id | title | content
bc9db029-408a-4f5b-bf49-5e5af9581532 | memo1 | this is memo1
c9e67c66-4219-411f-ab7f-625a7e5e4ceb | memo2 | this is memo2
(2 rows)
DB作成、テーブル作成、初期データ投入をコマンドラインから行うことができました。
できなかったこと
当初はCREATE DATABASE
もDDLファイルに含め1つのファイルでDB作成から初期データ投入まで行おうと考えていました。
しかし、次の理由から実現することができませんでした。
以上の2つの理由から1つのDB作成はpsql
コマンドの-c
オプションでCREATE DATABASE
を実行し、テーブル作成以降のSQLコマンドを-f
オプションでinitialize.sql
を実行する方法を選択しました。
PostgreSQLとMySQLの基本的なコマンドを比較 - Qiita
もしかしたら見落としや他の方法があるかもしれないのでもうちょっと調べて見たいと思います。
まとめ
DDLがどんなものなのか理解する良い機会になりました。
また、コマンドラインからDBを作る方法も学べたのでどんどん使っていきたいと思います。
SinatraのメモアプリのDB化がんばります!
参照
ファイルから SQL を読み込む (MySQL, PostgreSQL, SQLite3) - CUBE SUGAR CONTAINER
.sql【拡張子】とは|「分かりそう」で「分からない」でも「分かった」気になれるIT用語辞典
PostgreSQLとMySQLの基本的なコマンドを比較 - Qiita