Zenn
Open5

Supabase（PostgreSQL）の逆引き大全

PostgreSQL
SQL
supabase
株式会社Never株式会社Never

PostgreSQLのRowデータの上限や下限を検証する方法

PostgreSQLのトリガ関数を使用
https://www.postgresql.jp/docs/11/plpgsql-trigger.html

コード
-- テーブル作成
create table
    sprint_areas (
        id bigint primary key generated always as identity,
        sprint_spot_id bigint references sprint_spots(id) on delete cascade,
        created_at timestamp with time zone not null default timezone('utc'::text, now()),
        updated_at timestamp with time zone not null default timezone('utc'::text, now())
    );

-- 挿入バリデーション
create or replace function check_sprint_areas_max_limit()
returns trigger as $$
declare
    count integer;
begin
    select count(*) into count from sprint_areas where sprint_spot_id = NEW.sprint_spot_id;
    if count >= 5 then
        raise exception 'Cannot insert more than 5 rows for a single sprint_spot_id.';
    end if;

    return NEW;
end;
$$ language plpgsql;

create trigger sprint_areas_before_insert
before insert on sprint_areas
for each row execute function check_sprint_areas_max_limit();

-- 削除バリデーション
create or replace function check_sprint_areas_min_limit()
returns trigger as $$
declare
    count integer;
begin
    select count(*) into count from sprint_areas where sprint_spot_id = OLD.sprint_spot_id;
    if count <= 1 then
        raise exception 'Cannot delete rows for a single sprint_spot_id.';
    end if;

    return OLD;
end;
$$ language plpgsql;

create trigger sprint_areas_before_delete
before delete on sprint_areas
for each row execute function check_sprint_areas_min_limit();
株式会社Never株式会社Never

SupabaseのAuthenticationのアカウント削除と連動してDBのRowデータも削除したい

主キーに対する外部制約をauth.usersに設定する。
https://supabase.com/docs/guides/auth/managing-user-data

コード
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;
shohei@株式会社Nevershohei@株式会社Never

アカウント削除する方法

サーバー側（Service Role Keyがセットされたクライアント）でsupabase.auth.admin.deleteUserを使って削除する。

Next.js 14 + API Routesのコード
pages/api/users/me.ts
import { createClient } from '@supabase/supabase-js'
import { NextApiRequest, NextApiResponse } from 'next'
import { supabaseUrl, supabaseServiceRoleKey } from '@/app/_repositories/supabase/supabase'

export default async function handler(req: NextApiRequest, res: NextApiResponse) {
  const token = req.headers.authorization?.split(' ')[1]
  const supabase = createClient(supabaseUrl, supabaseServiceRoleKey)

  const { data, error } = await supabase.auth.getUser(token)
  if (error) {
    return res.status(400).send({ message: `Invalid authorization. ${error.message}` })
  }

  if (req.method == 'DELETE') {
    const { error } = await supabase.auth.admin.deleteUser(data.user.id)
    if (error) {
      return res.status(401).send({ message: error.message })
    } else {
      return res.status(200).send({ message: 'success' })
    }
  }

  return res.status(400).send({ message: `No supported. Request method is ${req.method}` })
}

curl -X DELETE http://localhost:3000/api/users/me -H "Authorization: Bearer YOUR_TOKEN"
shohei@株式会社Nevershohei@株式会社Never

SupabaseをSSRで使う方法

@supabase/ssrを使う。
https://supabase.com/docs/guides/auth/server-side/creating-a-client

コード

インストール

npm install @supabase/ssr @supabase/supabase-js

Client Component

"use client";

import { createBrowserClient } from '@supabase/ssr'

export default function Page () {
  const supabase = createBrowserClient(
    process.env.NEXT_PUBLIC_SUPABASE_URL!,
    process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!
  )

  return ...
}

Server Component


import { createServerClient, type CookieOptions } from '@supabase/ssr'
import { cookies } from 'next/headers'

export default async function Page () {
  const cookieStore = cookies()

  const supabase = createServerClient(
    process.env.NEXT_PUBLIC_SUPABASE_URL!,
    process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!,
    {
      cookies: {
        get(name: string) {
          return cookieStore.get(name)?.value
        },
      },
    }
  )

  return ...
}