🔐
Database Functions

2024/12/14に公開
GitHub
PostgreSQL
Supabase
idea
 What Database Functions?Databaseに対してイベントが発生するとハッカして実行される関数のようだ。Next.jsで自社サービスを作っているのですが、フロントエンドからではなくSupabaseのコンソールで設定した関数で、usersテーブルにデータを保存する処理を実行する必要がありました。
先に結論を述べておくと、「関数の設定すればOK！」他は何もしない。するけど。
Use Caseは、Github Sign Inが実行されたときです。SignUpしたらINSERTして、SignInしたら、UPDATEのクエリを実行します。
Database Functionsなるものを今回ははじめて使いましたね。
https://supabase.com/docs/guides/database/functions
Postgres has built-in support for SQL functions.

These functions live inside your database, and they can be used with the API.
PostgresにはSQL関数の組み込みサポートがあります。

これらの関数はデータベース内に存在し、APIで使用することができます。
認証時のトリガーの設定はこちらに書いてあるようだ。

https://supabase.com/docs/guides/auth/managing-user-data#using-triggers

 ユーザー管理ユーザー情報の表示、削除、エクスポート
ダッシュボードのユーザ・ページでユーザを見ることができます。また、テーブルエディタでAuthスキーマの内容を見ることもできます。
API# によるユーザーデータへのアクセス

セキュリティのため、自動生成APIではAuthスキーマは公開されません。API 経由でユーザー・データにアクセスしたい場合は、公開スキーマで独自のユーザー・テーブルを作成できます。
必ず行レベル・セキュリティを有効にしてテーブルを保護してください。データの整合性を確保するために、auth.usersテーブルを参照してください。参照で削除カスケードを指定します。例えば、public.profilesテーブルは以下のようになります：
create table public.profiles (
  id uuid not null references auth.users on delete cascade,
  first_name text,
  last_name text,

  primary key (id)
);

alter table public.profiles enable row level security;
私はusersテーブルなるものを作っていたのでそちらにGithubSignInしたときに、id、name、emailに保存する情報を取得しました。
この画面で欲しいデータのプロパティをJSONの中から探す。


 AIの力が必要なので借りたSupabase AIアシスタントも使ってみたのですが期待した動作をしてくれない😅
JSONのraw_user_meta_dataかなこの中に欲しい情報があるようだ。

 AIに質問しながら解決するしかないSupabaseのデータベース機能を使って、認証時にユーザー情報を自動的に保存する方法について説明させていただきます。
これはDatabase Functions（データベーストリガー）を使用して実装できます。具体的な手順は以下の通りです：
Supabaseのダッシュボードで「Database」→「Functions」に移動します。
新しいデータベース関数を作成し、auth.usersテーブルの変更をトリガーとして設定します。以下のようなSQL関数を作成できます：
create or replace function public.handle_new_user()
returns trigger as $$
begin
  insert into public.users (id, email, username, avatar_url)
  values (
    new.id,
    new.email,
    new.raw_user_meta_data->>'user_name',
    new.raw_user_meta_data->>'avatar_url'
  );
  return new;
end;
$$ language plpgsql security definer;

-- トリガーの作成
create trigger on_auth_user_created
  after insert on auth.users
  for each row execute procedure public.handle_new_user();
このように設定すると、ユーザーがGithubやGoogleで認証した際に、自動的に必要な情報がpublic.usersテーブルに保存されます。
raw_user_meta_dataには、プロバイダー（GithubやGoogle）から提供される追加情報が含まれています。例えば：
Github認証の場合：username, avatar_url, name等
Google認証の場合：name, picture, email等
これらの情報はauth.usersテーブルのraw_user_meta_dataカラムにJSONとして保存されており、必要な情報を抽出してユーザーテーブルに保存することができます。
このアプローチのメリットは：
フロントエンドのコードを簡潔に保てる
認証直後に確実にユーザー情報が保存される
セキュリティ的により安全（フロントエンドからの直接のデータ操作を避けられる）
必要に応じて、更新時のトリガーも追加することで、ユーザー情報の変更も自動的に同期させることができます。

 try1なんか違ったぞ
スミマセン。このSQLには問題があります。提案された関数とトリガーの定義に以下の修正が必要です：
トリガー関数は引数を直接取ることができません。代わりにNEWレコードから情報を取得する必要があります。
GithubのユーザーデータはNEW.raw_user_meta_dataから取得する必要があります。
修正したバージョンはこちらです：
CREATE OR REPLACE FUNCTION public.save_github_user_info()
RETURNS trigger AS $$
BEGIN
    INSERT INTO public.users (
        id,
        name,
        email,
        created_at,
        updated_at
    ) VALUES (
        NEW.id,
        NEW.raw_user_meta_data->>'name',  -- Githubのメタデータからname取得
        NEW.email,
        NOW(),
        NOW()
    )
    ON CONFLICT (id) DO UPDATE  -- 既存ユーザーの場合は更新
    SET
        name = EXCLUDED.name,
        email = EXCLUDED.email,
        updated_at = NOW();
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER after_github_sign_in
    AFTER INSERT ON auth.users
    FOR EACH ROW
    WHEN (NEW.raw_user_meta_data->>'provider' = 'github')  -- Githubからのサインインの場合のみ実行
    EXECUTE FUNCTION public.save_github_user_info();
主な修正点：
関数の引数を削除し、トリガー関数の標準的な形式に変更

raw_user_meta_dataからGithubユーザー情報を取得

ON CONFLICT句を追加して既存ユーザーの更新に対応
トリガーの条件をGithubプロバイダーの場合のみに限定

SECURITY DEFINERを追加してセキュリティを強化
これで、Githubサインイン時に正しくユーザー情報が保存されるはずです。

 try2では、既存のクエリを確認・修正していきましょう。
まず、トリガーが反応していない理由を確認するために、以下の点をチェックしましょう：
まず、デバッグのためにraise noticeを追加したバージョンを試してみましょう：
CREATE OR REPLACE FUNCTION public.save_github_user_info()
RETURNS trigger AS $$
BEGIN
    RAISE NOTICE 'Trigger executed: User ID: %, Provider: %', 
                  NEW.id, 
                  NEW.raw_user_meta_data->>'provider';

    INSERT INTO public.users (
        id,
        name,
        email,
        created_at,
        updated_at
    ) VALUES (
        NEW.id,
        COALESCE(NEW.raw_user_meta_data->>'name', NEW.raw_user_meta_data->>'user_name'),
        NEW.email,
        NOW(),
        NOW()
    )
    ON CONFLICT (id) DO UPDATE
    SET
        name = COALESCE(EXCLUDED.name, users.name),
        email = COALESCE(EXCLUDED.email, users.email),
        updated_at = NOW();
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- トリガーの再作成
DROP TRIGGER IF EXISTS after_github_sign_in ON auth.users;

CREATE TRIGGER after_github_sign_in
    AFTER INSERT OR UPDATE ON auth.users
    FOR EACH ROW
    WHEN (NEW.raw_user_meta_data->>'provider' = 'github')
    EXECUTE FUNCTION public.save_github_user_info();
主な変更点：

RAISE NOTICEでデバッグ情報を追加

COALESCEを使用してname取得の冗長性を高めた
トリガーを一度削除して再作成
このSQLを実行後、サインインを試してみて、ログに何か出力されるか確認してみましょう。出力がない場合は、トリガーが実行されていない可能性が高いので、その場合は：

auth.usersテーブルの更新が実際に行われているか

raw_user_meta_data->>'provider'の値が本当に'github'になっているか
を確認する必要があります。確認用のクエリ：
-- 認証済みユーザーの情報を確認
SELECT id, email, raw_user_meta_data->>'provider' as provider
FROM auth.users
WHERE raw_user_meta_data->>'provider' = 'github';
これらの情報があれば、より正確な問題の特定と修正が可能になります。

 try3Supabaseのダッシュボードで確認できます：

Databaseセクション
左メニューからDatabaseを選択

SQL Editorを選択
以下のクエリを実行してログを確認：
SELECT * FROM pg_catalog.pg_stat_activity 
WHERE state = 'active' 
AND query NOT LIKE '%pg_stat_activity%'
ORDER BY query_start DESC;
さらに、Supabaseのダッシュボードには専用のログビューアもあります：
プロジェクトのダッシュボードでDatabaseを開く
左サイドバーの下の方にあるDatabase logs（またはLogs）を選択
また、デバッグ用にテーブルの変更を直接監視することもできます：
-- auth.usersテーブルの変更を監視
SELECT * FROM auth.users 
ORDER BY created_at DESC 
LIMIT 10;

-- 自分のusersテーブルの変更を監視
SELECT * FROM public.users 
ORDER BY created_at DESC 
LIMIT 10;
これらのクエリを実行することで、最新の変更やトリガーの動作状況を確認できます。

 これ書けば上手くいったこのクエリを書くとフロントエンド側では、Github SignInするだけで、イベントがハッカして、usersテーブルに設定したプロパティのデータを保存してくれる。

id=github id、name=github username、 email=github emailこんな感じか。created_atとかは、now()なので設定は必要なし。

BEGIN
    -- デバッグ用に完全なメタデータを出力
    RAISE NOTICE 'Full metadata: %', NEW.raw_user_meta_data;

    -- GitHubユーザーの場合のみ実行（条件を調整）
    IF NEW.raw_user_meta_data->>'user_name' IS NOT NULL THEN  -- GitHubの特徴的なフィールドをチェック
        INSERT INTO public.users (
            id,
            name,
            email,
            created_at,
            updated_at
        ) VALUES (
            NEW.id,
            COALESCE(NEW.raw_user_meta_data->>'name', NEW.raw_user_meta_data->>'user_name'),
            NEW.email,
            NOW(),
            NOW()
        )
        ON CONFLICT (id) DO UPDATE
        SET
            name = COALESCE(EXCLUDED.name, users.name),
            email = COALESCE(EXCLUDED.email, users.email),
            updated_at = NOW();
    END IF;

    RETURN NEW;
END;



 感想Supabase Meetupに参加してAIアシスタントの情報も聞いていて試してみたが、知識あるの前提でチャットで質問しないと解決が難しかった😭

動くクエリを作ったらドキュメントの例と比較してどうやればロジックを組めるのか理解できると思いたい。この辺の分野は知識がないので苦労しますね。
What Database Functions?

ユーザー管理

AIの力が必要なので借りた

AIに質問しながら解決するしかない

try1

try2

try3

これ書けば上手くいった

感想

Discussion
