SQLファイルをコマンドラインから実行してテーブル定義する
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作成から初期データ投入まで行おうと考えていました。
しかし、次の理由から実現することができませんでした。
- PostgreSQLにはMySQLの
USEコマンドのようにDBを切り替えるSQLが用意されていない。 - PostgreSQLはテーブル作成時にどの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
