SQLファイルをコマンドラインから実行してテーブル定義する

Sinatraを使ったメモアプリのDB版を進めています。テーブルを作成するDDL文を書いたSQLファイルを読み込み、PostgreSQLにテーブル定義したいと思ったので調べてみました。

結論

psql コマンドの-f オプションを使ってDDLを定義したSQLファイルを読み込んでテーブル定義する。

`psql` コマンドの`-c` と`-f` オプション

コマンドラインから実行するpsql コマンドは次のようなオプションが用意されている。

-c: コマンドラインからSQL実行ができる。
-f: コマンドラインからファイル実行できる。

# コマンドラインから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)