🐘

【Flutter x Supabase】最低限理解するPostgreSQL(Postgres)

heyhey1028

2023/07/04に公開

これはなにか？

まず先に謝っておくと、今回の記事は直接Flutterとは関係ありません。ただFlutterエンジニアの自分がSupabaseに入門する際に最低限必要だと感じたPostgreSQLについて整理した記事になります。

その為、アプローチとしては以下の記事で紹介されているテーブル作成、Postgres Function、Postgres Trigger を読み解きながら、それぞれの作成方法と構文について最低限理解する構成を取っています。

また全ての処理はSupabase上に直接定義する方針を取ります。ローカルで定義、実行する事はしません。

それらを理解した上でより詳細にPostgreSQLについて学びたい方は、詳細な記事があるかと思いますのでそちらご覧になってみてください。

話さないこと

PostgreSQLの構文を網羅的にカバーはしません
テーブル、Postgres Function、Postgres Trigger以外(index, procedure,etc.)の作成方法
RDBに関する基本的概念の説明

環境

	バージョン
PostgreSQL	15.1

PostgreSQLとは

PostgreSQLは、オープンソースのリレーショナルデータベース管理システム（RDBMS）の一つです。

RDBMSで言うと他にもMySQLなどが有名ですが、違いとしてわかりやすいのが、MySQLはRDB（リレーショナルデータベース）とされているのに対し、PostgreSQLは “オブジェクト”リレーショナルデータベース とされている点です。

RDBではエクセルのような表形式でデータが格納されるのに対し、ORDBMSではオブジェクト単位でデータの管理が行われます。結果、MySQLがシンプルな事に対し、PostgreSQLは色々と多機能なRDBMSとなっています。

説明不要かもしれませんが、Supabaseで提供されているSupabase DatabaseはこちらのPostgreSQLをベースとしています。
https://supabase.com/database

ちなみに名称に関しては簡略化してPostgresと呼称することも多い様です。

基礎

トップレベル関数

関数	説明
`create`	新しいオブジェクト（テーブル、データベース、インデックスなど）を作成する
`alter`	既存のオブジェクトを変更・更新する
`drop`	オブジェクトを削除する

CREATEに対して使える修飾子

修飾子	説明
`create or replace`	オブジェクトが存在する場合は置き換え、存在しない場合は新しく作成する
`create 〇〇 if not exists`	指定されたオブジェクトが存在しない場合のみ作成する
`create table 〇〇 (like 〇〇 include all)`	既存のテーブルの構造をコピーして新しいテーブルを作成する

生成オブジェクト

オブジェクト	説明
`table`	データを格納する行と列の構造
`database`	関連するテーブルとデータオブジェクトの集合
`index`	テーブル内のデータ検索を高速化する構造
`sequence`	自動生成される整数値の系列
`function`	出力値を返すSQLステートメントの集合
`procedure`	出力値を返さない操作のSQLステートメントの集合
`trigger`	データベースイベントに応じて自動実行されるSQLステートメント
`schema`	データベースオブジェクトの名前空間
`policy`	行レベルのセキュリティを制御するオブジェクト

主なデータ型

データ型	説明	例
`int`	整数型	123
`decimal`	固定小数点数型	123.45
`real`	浮動小数点数型	123.45678
`serial`	自動でインクリメントされる整数型	(自動生成されます)
`uuid`	ユニバーサル一意識別子型	'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'
`char(n)`	固定長文字列型	'hello'
`varchar(n)`	可変長文字列型	'hello world'
`text`	長文文字列型	'Lorem ipsum dolor sit amet, consectetur adipiscing elit.'
`boolean`, `bool`	真偽値型	true
`date`	日付型	'2023-06-27'
`time`	時刻型	'15:30:00'
`timestamp`	日付と時刻の両方を保持する型	'2023-06-27 15:30:00'
`json`, `jsonb`	JSON型、JSONBはバイナリ形式でJSONデータを保存	'{"key": "value"}'
`<データ型>[]`	配列型	[1,5,32,78]
`int4range`,`int8range`,`tsrange`, `tstzrange`,`daterange`	特定の型の範囲を表すデータ型。参考	'[2010-01-01 14:30, 2010-01-01 15:30)'

制御構文

if-then-else: 条件付きの処理を記述するために使用されます。条件が真であればthen節のコードが実行され、偽であればelse節のコードが実行されます。
```
if condition then
   -- 実行するコード
else
   -- 別のコードを実行
end if;
```

loop: ループ内のコードを繰り返し実行します。ループはexit条件で終了します。

loop
   -- ループ内で実行するコード
   exit when condition; -- 条件が真であればループを終了
end loop;

for: 一定回数の反復処理を行います。指定した範囲内の整数値に対して繰り返し処理を実行します。
```
for i in 1..10 loop
   -- ループ内で実行するコード
end loop;
```

exception: エラーハンドリングを行います。例外が発生した場合、この節内のコードが実行されます。

begin
   -- エラーが発生する可能性のあるコード
exception when condition then
   -- 例外処理のコード
end;

テーブル定義

では参考記事の中からまず以下のテーブル作成のpgsqlを読み解いていきましょう

create table if not exists public.profiles (
    id uuid references auth.users on delete cascade not null primary key,
    username varchar(24) not null unique,
    created_at timestamp with time zone default timezone('utc' :: text, now()) not null,
);
comment on table public.profiles is 'ユーザー名などのユーザー情報を保持する';

基本構成

以上のSQL文は以下のような構成となっています。

[create コマンド]　[スキーマ名].[対象物の命名](
    [フィールド定義]
);
[対象物に対するコメント追加] // Optional

create コマンド：`create <対象物> if not exists`

まずこちらのSQL文のcreateコマンドは、前項で紹介したcreate 〇〇 if not existsです。これにより対象物が存在しない場合、作成するという関数である事が分かります。また今回は<対象物>がtableとなっているのでテーブル作成をする関d数となる事が分かります。

スキーマ名.対象物の命名: `public.profiles`

今回の場合、profilesがテーブル名 になります。一方、publicというのはスキーマ名 になります。スキーマというのはデータベース内でテーブルや関数などをグループ化する為の名前空間です。デフォルトで用意されているpublicというスキーマ内にprofilesテーブルを作成している事になります。

詳しくは以下をご参考ください。

フィールド定義:

こちらは次項で詳しく見ていきます

対象物に対するコメント追加: `comment on <対象物> <スキーマ名>.<対象物の命名> is <コメント内容>`

こちらの処理はコメントを対象物に対して付与します。あくまでも管理しやすくする為なので、必須ではありません。

フィールド定義

フィールド定義の詳細を見ていきましょう

...
id uuid references auth.users on delete cascade not null primary key,
username varchar(24) not null unique,
created_at timestamp with time zone default timezone('utc' :: text, now()) not null,
...

基本構成

以上のフィールド定義は以下のような構成となっています。

[フィールド名] [データ型] [付加条件] [デフォルト値] [nullableか否か] [キー]

全ての要素が必須項目というわけではありません。フィールドごとに見ていきましょう

id uuid references auth.users on delete cascade not null primary key

フィールド名：id
データ型：uuid
付加条件： references auth.users on delete cascade
nullableか否か：not null
キー：primary key

primary key はこのフィールドがテーブルの「主キー」である事を示し、テーブル内で一意である事を意味します。

references auth.users　は「外部キー制約」と呼ばれ、このフィールドがauth.usersテーブルの主キーと一致しなければならない事を示します。

on delete cascade は「カスケード削除」と呼ばれ、auth.usersテーブルの対象となるレコードが削除された場合、このテーブルの該当レコードも自動的に削除するという条件を付与しています。

username varchar(24) not null unique

フィールド名：username
データ型：varchar(24)
付加条件： unique
nullableか否か：not null

unique ではこのフィールドの値がテーブル内で一意の値でなければならない事を示しています。もし同一のusernameを持つレコードを作成しようとするとエラーが返されます。

created_at timestamp with time zone default timezone('utc' :: text, now()) not null

フィールド名：created_at
データ型：timestamp with time zone
デフォルト値：default timezone('utc' :: text, now())
nullableか否か：not null

Postgres Functions

そもそも何？

Postgres Functions はストアドファンクション(Stored Function)とも呼ばれ、データベースに直接登録する事が出来る関数の事です。FirebaseやGCPを使った事がある方なら、「PostgreSQL版のCloud Functions」 を想像して頂くとイメージしやすいかと思います。

Cloud Functionsにも外部から呼び出されるCallable functionとFirestoreのデータ変更を起点に発火するTrigger functionがある様に、Postgres functionsにも外部から呼び出されるStored functionとデータベースへの変更を起点に発火するTrigger functionが存在します。

今回の参考記事ではその内、Trigger functionの作成方法について言及してくれています。紹介されている以下の定義を読み解いていきましょう。

create or replace function public.handle_new_user() returns trigger as $$
begin
    insert into public.profiles(id, username)
    values(new.id, new.raw_user_meta_data->>'user_name');
    return new;
end;
$$ language plpgsql security definer;

基本構成

Stored Functionの定義は以下のような構造をしています。

[create コマンド] function [スキーマ名].[function名]([引数]) returns [戻り値の型] as $$
begin
    // 一連の処理
    return [戻り値];
end;
$$ language [記述言語] [セキュリティ句]

こちらは大きく分けると3つのパートに分かれています。まず「createコマンド」から「戻り値の型」までがシグネチャ、「as $$ ... $$」の間がボディ、それ以降がキャラクタリスティックスという構成になっています。

シグネチャ部分がFunctionの要件を定義し、ボディ部分には処理、キャラクタリスティックスの部分にFunctionに関する性質が記述されます。

シグネチャ

Functionsの要件を定義する部分です。

create or replace function public.handle_new_user() returns trigger

createコマンド：create or replace
スキーマ名.function名：public.handle_new_user
戻り値の型：trigger

create or replaceである事で、作成もしくは更新が行われる為、該当の
functionは常に1つだけとなります。

また戻り値がtriggerと指定されている事で、こちらのFunctionsがtrigger functionsである事を表現しています。

ボディ

実際の処理が記述されるパートです。as $$ ... $$で囲われた部分になります。

begin
    insert into public.profiles(id, username)
    values(new.id, new.raw_user_meta_data->>'user_name');
    return new;
end;

`begin ... ends;`と`declare`：

as $$ ... $$で囲われた部分の中でも複数のブロックに分かれ、begin ... ends;で囲われた部分が実際の処理、またこちらの参考記事では使用されていませんが、変数定義を含む場合、begin ... ends;の前にdeclareというブロックを用意して、変数への代入が行われます。

`insert into 〇〇 values(XX);`：

今回の参考記事の2-3行目では insert into 〇〇 values(XX); で「〇〇テーブルにXXという値を代入」という処理を行なっています。

`new`キーワード：

new はtrigger functionで使える特別な値で、trigger functionを発火した元データの値が入ってきます。今回の場合だとtrigger functionを発火した元データではidとraw_user_meta_dataというフィールドを持っている事が分かります。

`->>`オペレーター：

->> はjsonデータから特定のフィールドの値を取得する際に用いられるオペレーターで、今回の場合はraw_user_meta_dataというjsonデータの中のuser_nameというフィールドの値を取り出しています。

キャラクタリスティックス

language plpgsql security definer;

記述言語：plpgsql
セキュリティ句：security definer

security definer はこのファンクションの実行権限を指定しています。通常ファンクションの実行は呼び出しユーザーsecurity invokerの権限で実行されます。しかしsecurity definerで呼び出しユーザーではなく、定義ユーザー(Admin)の権限で呼び出す事が出来ます。

firebaseでいう所のAdmin権限での呼び出しになります。ユースケースとしては、操作が一定のユーザーに限定されているテーブルをトリガーファンクションで操作したい場合に用いられます。

データベースへのどの様な操作も可能となる強い権限になので、使うタイミングには気を付けましょう。

Postgres Triggers

次はトリガーについて見ていきましょう

create trigger on_auth_user_created
after insert on auth.users
for each row
execute procedure handle_new_user();

そもそも何？

前述のtrigger functionでは何かしらテーブルに対する変更によって発火される関数が定義されていました。その発火条件を定義するのが postgress tirggers (もしくは単純にtrigger)です。

つまり trigger functionとpostgres triggerは対で使われます。trigger function=「発火される処理」、postgress trigger=「発火条件」になります。

基本構成

create trigger [トリガー名]
[発火タイミング] [トリガーとするテーブル操作] on [テーブル名]
for each [発火対象]
execute procedure [ファンクション名]

参考記事のトリガーを例に1つ1つ見ていきましょう

トリガー名：　`create trigger on_auth_user_created`

on_auth_user_createdというトリガーを作成する事が分かります。

発火タイミング：　`after`

発火タイミングは after, before から選びます。後続するトリガー条件の前に発火するか後に発火するかを指定します。

トリガーとするテーブル操作：`insert`

トリガーとするテーブル操作を insert,update,delete,truncate から選びます。or を使って、複数のテーブル操作をトリガーにすることも可能です。

発火対象: `for each row`

発火対象はfor each rowもしくはfor each statementを選択する事ができます。for each rowは定義されたトリガーがレコード毎に、一方、for each statementではDBに対して実行されたアクション毎に発火されます。

例えば10個のレコードを一度に追加する処理があったとして、for each rowではレコード毎に発火するので10回発火されるのに対し、for each statementでは処理毎に発火されるので1度だけ発火されます。

ファンクション名：`execute procedure handle_new_user();`

ここまでの条件が合致した場合に発火するファンクションを指定しています。こちらのトリガーでは、前述のhandle_new_userファンクションを指定しています。

以上

以上、最低限理解するPostgreSQLと題して、基礎的な文法と以下の４つの基本構成を読み解いていきました。

テーブル定義
フィールド定義
Postgres Functions
Postgres Triggers

Postgresの基本を網羅しているわけではないので、抜けている部分もあるかと思いますが、実際の例を元にざっくりと全体像だけでも掴めていたら幸いです。

Flutter大学Publication

Flutterエンジニアの学習コミュニティ。詳しくは→ flutteruniv.com

これはなにか？

話さないこと

環境

PostgreSQLとは

基礎

トップレベル関数

CREATEに対して使える修飾子

生成オブジェクト

主なデータ型

制御構文

テーブル定義

基本構成

create コマンド：create <対象物> if not exists

スキーマ名.対象物の命名: public.profiles

フィールド定義:

対象物に対するコメント追加: comment on <対象物> <スキーマ名>.<対象物の命名> is <コメント内容>

フィールド定義

基本構成

Postgres Functions

そもそも何？

基本構成

シグネチャ

ボディ

begin ... ends;とdeclare：

insert into 〇〇 values(XX);：

newキーワード：

->>オペレーター：

キャラクタリスティックス

Postgres Triggers

そもそも何？

基本構成

トリガー名： create trigger on_auth_user_created

発火タイミング： after

トリガーとするテーブル操作：insert

発火対象: for each row

ファンクション名：execute procedure handle_new_user();

以上

Discussion

create コマンド：`create <対象物> if not exists`

スキーマ名.対象物の命名: `public.profiles`

対象物に対するコメント追加: `comment on <対象物> <スキーマ名>.<対象物の命名> is <コメント内容>`

`begin ... ends;`と`declare`：

`insert into 〇〇 values(XX);`：

`new`キーワード：

`->>`オペレーター：

トリガー名：　`create trigger on_auth_user_created`

発火タイミング：　`after`

トリガーとするテーブル操作：`insert`

発火対象: `for each row`

ファンクション名：`execute procedure handle_new_user();`