Supabaseのバリデーションはどうするのか問題（PostgreSQL童貞が頑張るやつ）
masa57142025年3月23日追記（注意）
本スクラップは「フロントエンドだけで実装する」前提で語られているので注意してください。
このスクラップを書いた当時はフロントエンドのみで実装したいという気持ちがありましたが、現在ではサーバーサイドを介しながら実装するのが良いという考えに至っています。
Next.jsで実装するならば、 API Routes で service role key を使ってSupabaseクライアントを作成し、RLSでは service_role 権限のリクエストのみを受け付けるという形にすると良いでしょう。service role keyがフロントエンド側に露出しないように十分注意して実装しましょう。
masa5714Supabase + Next.js の構成では、基本的には API Routes を使いたくない。
理由は分からない。気分的な問題かも。
ということで、Supabase側だけでINSERT / UPDATEの値をバリデーションしたい。
SupabaseのドキュメントやGithubのディスカッションを読んでもバリデーションっぽい記述は全く存在せず、全く分からなかった。（PostgreSQLに慣れている人はこんなことに困ることは無いんだろうね。）
RLSでやるのか？とも思ったのだが、POST項目が増減すると、どうも正しく動いてくれなかった。ということで別の手段を模索。
結論としては、
PostgreSQLで使われる CHECK制約 を用いる必要があることがわかった。
GUIではCHECK制約を管理する画面が存在していないため、SQL Editorで頑張ることが前提のようだ。
masa5714今回使うデータベース構造
◆要件
- nameは2文字以上、16文字以下にしたい。
- descriptionは0文字以上、160文字以下にしたい。
◆やってみる
SupabaseのGUI「SQL Editor」から
// nameの文字数を制限する
alter table public.profiles
add constraint valid_name_length
check (
LENGTH(name) >= 2
AND LENGTH(name) <= 16
)
// descriptionの文字数を制限する
alter table public.profiles
add constraint valid_description_length
check (
LENGTH(description) >= 0
AND LENGTH(description) <= 160
)
を実行すれば public.profiles テーブルに対してCHECK制約が付与される。
ただ、この程度ならregexでやった方が良さそう（下記で触れてる）
masa5714不要になったCHECK制約は add constraint 制約名 で設定した「制約名」を指定することで、名指しで削除することができる。
descriptionに付与した制約「valid_description_length」を削除する例
alter table public.profiles drop constraint valid_description_length;
- 1文字以上、30文字以下（Googleアカウントの名前は文字数が30文字以下のため。ほんとは16文字以下が良かった。）
- ひらがな
- カタカナ
- 漢字
- 英数字（全角・半角）
- 一般的な記号
- 絵文字
を許可する形での制約
alter table public.profiles
add constraint valid_name_regex
check (
name ~ '^[ぁ-んァ-ン一-龠０-９a-zA-Z0-9\-!"#$%&''()*+-.,\/:;=?@\^_`|~\U00000023,\U0000002A,\U00000030-\U00000039,\U000000A9,\U000000AE,\U0000200D,\U0000203C,\U00002049,\U00002122,\U00002139,\U00002194-\U00002199,\U000021A9-\U000021AA,\U0000231A-\U0000231B,\U00002328,\U000023CF,\U000023E9-\U000023F3,\U000023F8-\U000023FA,\U000024C2,\U000025AA-\U000025AB,\U000025B6,\U000025C0,\U000025FB-\U000025FE,\U00002600-\U00002604,\U0000260E,\U00002611,\U00002614-\U00002615,\U00002618,\U0000261D,\U00002620,\U00002622-\U00002623,\U00002626,\U0000262A,\U0000262E-\U0000262F,\U00002638-\U0000263A,\U00002640,\U00002642,\U00002648-\U00002653,\U0000265F-\U00002660,\U00002663,\U00002665-\U00002666,\U00002668,\U0000267B,\U0000267E-\U0000267F,\U00002692-\U00002697,\U00002699,\U0000269B-\U0000269C,\U000026A0-\U000026A1,\U000026A7,\U000026AA-\U000026AB,\U000026B0-\U000026B1,\U000026BD-\U000026BE,\U000026C4-\U000026C5,\U000026C8,\U000026CE-\U000026CF,\U000026D1,\U000026D3-\U000026D4,\U000026E9-\U000026EA,\U000026F0-\U000026F5,\U000026F7-\U000026FA,\U000026FD,\U00002702,\U00002705,\U00002708-\U0000270D,\U0000270F,\U00002712,\U00002714,\U00002716,\U0000271D,\U00002721,\U00002728,\U00002733-\U00002734,\U00002744,\U00002747,\U0000274C,\U0000274E,\U00002753-\U00002755,\U00002757,\U00002763-\U00002764,\U00002795-\U00002797,\U000027A1,\U000027B0,\U000027BF,\U00002934-\U00002935,\U00002B05-\U00002B07,\U00002B1B-\U00002B1C,\U00002B50,\U00002B55,\U00003030,\U0000303D,\U00003297,\U00003299, \U0000FE0F, \U0001F004,\U0001F0CF,\U0001F170-\U0001F171,\U0001F17E-\U0001F17F,\U0001F18E,\U0001F191-\U0001F19A,\U0001F1E6-\U0001F1FF,\U0001F201-\U0001F202,\U0001F21A,\U0001F22F,\U0001F232-\U0001F23A,\U0001F250-\U0001F251,\U0001F300-\U0001F321,\U0001F324-\U0001F393,\U0001F396-\U0001F397,\U0001F399-\U0001F39B,\U0001F39E-\U0001F3F0,\U0001F3F3-\U0001F3F5,\U0001F3F7-\U0001F4FD,\U0001F4FF-\U0001F53D,\U0001F549-\U0001F54E,\U0001F550-\U0001F567,\U0001F56F-\U0001F570,\U0001F573-\U0001F57A,\U0001F587,\U0001F58A-\U0001F58D,\U0001F590,\U0001F595-\U0001F596,\U0001F5A4-\U0001F5A5,\U0001F5A8,\U0001F5B1-\U0001F5B2,\U0001F5BC,\U0001F5C2-\U0001F5C4,\U0001F5D1-\U0001F5D3,\U0001F5DC-\U0001F5DE,\U0001F5E1,\U0001F5E3,\U0001F5E8,\U0001F5EF,\U0001F5F3,\U0001F5FA-\U0001F64F,\U0001F680-\U0001F6C5,\U0001F6CB-\U0001F6D2,\U0001F6D5-\U0001F6D7,\U0001F6DD-\U0001F6E5,\U0001F6E9,\U0001F6EB-\U0001F6EC,\U0001F6F0,\U0001F6F3-\U0001F6FC,\U0001F7E0-\U0001F7EB,\U0001F7F0,\U0001F90C-\U0001F93A,\U0001F93C-\U0001F945,\U0001F947-\U0001F9FF,\U0001FA70-\U0001FA74,\U0001FA78-\U0001FA7C,\U0001FA80-\U0001FA86,\U0001FA90-\U0001FAAC,\U0001FAB0-\U0001FABA,\U0001FAC0-\U0001FAC5,\U0001FAD0-\U0001FAD9,\U0001FAE0-\U0001FAE7,\U0001FAF0-\U0001FAF6]{1,30}$'
)
シングルクォーテーションを含めるときは ' や \' ではなく、 '' とする必要があることを知った。
めちゃくちゃ時間喰った。
こういうやつってどんなキーワードで検索するといいんけ？
masa5714適用中の制約をテーブル毎に確認する
SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_SCHEMA = 'public' AND table_name = 'profiles'
table_name の値を変更すれば public のテーブル名を指定して確認できる。
制約の中身は見れない模様？よーわからんので、SQL Editor に記述残しておくべきかと。
masa5714Regexを色々検証するなら https://regex101.com/ めちゃ便利ぃ！
Supabaseの中の人も使ってるっぽい（YouTube Liveにて使ってるとこ見た）
masa5714制約を更新したいとき、いちいち
alter table public.profiles drop constraint valid_name_regex;
などで制約キーを指定して削除する必要がある。
これでは面倒くさすぎる。
ということで、functionsにて関数を作っておいて、その関数を用いて constraint check しておくと、関数を編集するとリアルタイムに反映されるので楽ちん！
Return Typeはtextにしといた。
そして、下記のように alter tableで関数を使って constraint checkを適用。
1つ目の引数が最小文字数。
2つ目の引数が最大文字数。
もしも許可したい文字が増減した場合には、作成したfunctionsの regex_japanese を編集すればOK！
masa5714constraint_nameは命名規則をしっかりと決めた方がいいかも？
カラム名を名前に含めておかないとどこに影響しているものかが分かりづらい気がする。
LIKE句でカラムを絞り込めるだろうし、メンテ性に大きな差が出そう
- valid_regex_column_description
- valid_regex_column_name
ただし、テーブル名は含める必要なし。
SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_SCHEMA = 'public' AND table_name = 'profiles'
でテーブル名を絞り込みできるからね。
SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_SCHEMA = 'public' AND table_name = 'profiles' AND constraint_name LIKE '%description'
カラム名を絞り込んで見たいときは上記みたいな感じで。
末尾にカラム名を含めるという命名規則を守っていれば、descriptionが末尾についているものだけが一覧で取得できる。あとは自分的に分かりやすく名前を付けたconstraint_nameを見て何をやっているか判断をするか、SQL Editorに残っている記述を見てメンテしよう。
masa5714多層のJSONを参照する
select life_categories::json->1->'id' FROM public.profiles WHERE id = '10b6c87hogehogehoge';
こんな感じで ->1 で何番目の連想配列を見るかを指定し、
->>'id' でその連想配列のどのキーの値を取得するかを指定できる。
> にすると数値として、
>> にすると文字列として取得することになります。
これらを駆使して取得して各項目の値をCHECKなんてこともするべきだね！