Next.jsのバックエンドとして便利そうなPostgreSQLの無料BaaS「Supabase」について調べてたことのメモ

create table if not exists posts
(
    id    serial  not null,
    title varchar not null,
    body  text    not null
);

insert into posts (title, body)
values ('first post', 'first post body'),
       ('second post', 'second post body');

npm install --save @supabase/supabase-js

import { createClient } from "@supabase/supabase-js";

const SUPABASE_KEY = "...";
const SUPABASE_URL = "...";
const supabase = createClient(SUPABASE_URL, SUPABASE_KEY);

async function main() {
  const { data: posts, error } = await supabase.from("posts").select("*");
  console.log(posts);
}

main().catch(console.error);

npx ts-node test.ts
[
  { id: 1, title: 'first post', body: 'first post body' },
  { id: 2, title: 'second post', body: 'second post body' }
]

import { createClient } from "@supabase/supabase-js";

const supabase = createClient(
  process.env.SUPABASE_URL!,
  process.env.SUPABASE_KEY!
);

async function insert() {
  const { data, error } = await supabase
    .from("posts")
    .insert([{ title: "投稿タイトル", body: "本文" }]);
  console.log("insert", { data, error });
}

async function select() {
  const { data, error } = await supabase.from("posts").select();
  console.log("select", { data, error });
}

insert().then(select).catch(console.error);

insert { data: [ { id: 5, title: '投稿タイトル', body: '本文' } ], error: null }
select { data: [ { id: 5, title: '投稿タイトル', body: '本文' } ], error: null }

import { createClient } from "@supabase/supabase-js";

const supabase = createClient(
  process.env.SUPABASE_URL!,
  process.env.SUPABASE_KEY!
);

async function createUser() {
  const { data, error } = await supabase.auth.signUp({
    email: "alice@example.com",
    password: "p@ssW0rd123456",
  });
  console.log({ data, error });
}

createUser().catch(console.error);

{
  data: {
    id: 'c6dbd971-6811-45b9-a4a1-b83004138890',
    aud: 'authenticated',
    role: 'authenticated',
    email: 'alice@example.com',
    confirmation_sent_at: '2021-07-14T00:27:09.817413531Z',
    app_metadata: { provider: 'email' },
    user_metadata: null,
    created_at: '2021-07-14T00:27:09.814313Z',
    updated_at: '2021-07-14T00:27:12.194661Z'
  },
  error: null
}

import { createClient } from "@supabase/supabase-js";

const supabase = createClient(
  process.env.SUPABASE_URL!,
  process.env.SUPABASE_KEY!
);

async function logIn() {
  const { user, error } = await supabase.auth.signIn({
    email: "alice@example.com",
    password: "p@ssW0rd123456",
  });
  console.log({ user, error });
}

logIn().catch(console.error);

alter default privileges in schema public revoke all on tables from anon;
alter default privileges in schema public revoke all on functions from anon;
alter default privileges in schema public revoke all on sequences from anon;

revoke all privileges on all tables in schema public from anon;

import { createClient } from "@supabase/supabase-js";

const supabase = createClient(
  process.env.SUPABASE_URL!,
  process.env.SUPABASE_KEY!
);

async function anonymousUsersOperation() {
  // INSERT
  const { data: data1, error: error1 } = await supabase
    .from("posts")
    .insert({ title: "匿名ユーザーのINSERT", body: "テスト" });
  console.log({ data1, error1 });

  // SELECT
  const { data: data2, error: error2 } = await supabase.from("posts").select();
  console.log({ data2, error2 });

  // UPDATE
  const { data: data3, error: error3 } = await supabase
    .from("posts")
    .update({ title: "匿名ユーザーのUPDATE" })
    .match({ id: 1 });
  console.log({ data3, error3 });

  // DELETE
  const { data: data4, error: error4 } = await supabase
    .from("posts")
    .delete()
    .match({ id: 1 });
  console.log({ data4, error4 });
}

anonymousUsersOperation().catch(console.error);

{
  data1: null,
  error1: {
    hint: null,
    details: null,
    code: '42501',
    message: 'permission denied for table posts'
  }
}
{
  data2: null,
  error2: {
    hint: null,
    details: null,
    code: '42501',
    message: 'permission denied for table posts'
  }
}
{
  data3: null,
  error3: {
    hint: null,
    details: null,
    code: '42501',
    message: 'permission denied for table posts'
  }
}
{
  data4: null,
  error4: {
    hint: null,
    details: null,
    code: '42501',
    message: 'permission denied for table posts'
  }
}

import { createClient } from "@supabase/supabase-js";

const supabase = createClient(
  process.env.SUPABASE_URL!,
  process.env.SUPABASE_KEY!
);

async function authenticatedUsersOperation() {
  await supabase.auth.signIn({
    email: "alice@example.com",
    password: "p@ssW0rd123456",
  });

  // INSERT
  const { data: data1, error: error1 } = await supabase
    .from("posts")
    .insert({ title: "認証済みユーザーのINSERT", body: "テスト" });
  console.log({ data1, error1 });

  // SELECT
  const { data: data2, error: error2 } = await supabase.from("posts").select();
  console.log({ data2, error2 });

  // UPDATE
  const { data: data3, error: error3 } = await supabase
    .from("posts")
    .update({ title: "認証済みユーザーのUPDATE" })
    .match({ id: 1 });
  console.log({ data3, error3 });

  // DELETE
  const { data: data4, error: error4 } = await supabase
    .from("posts")
    .delete()
    .match({ id: 1 });
  console.log({ data4, error4 });
}

authenticatedUsersOperation().catch(console.error);

{
  data1: [ { id: 5, title: '認証済みユーザーのINSERT', body: 'テスト' } ],
  error1: null
}
{
  data2: [
    { id: 1, title: '投稿1', body: '' },
    { id: 2, title: '投稿2', body: '' },
    { id: 5, title: '認証済みユーザーのINSERT', body: 'テスト' }
  ],
  error2: null
}
{
  data3: [ { id: 1, title: '認証済みユーザーのUPDATE', body: '' } ],
  error3: null
}
{
  data4: [ { id: 1, title: '認証済みユーザーのUPDATE', body: '' } ],
  error4: null
}

https://your-project.supabase.co/rest/v1/?apikey=your-anon-key

http "https://${SUPABASE_PROJECT}.supabase.co/rest/v1/?apikey=${SUPABASE_KEY}"

create table product
(
    id    serial not null primary key,
    name  varchar,
    price int4
);

insert into product (name, price) values ('', -100);

create table product
(
    id    serial not null primary key,
    name  varchar check (length(name) > 0),
    price int4 check ( price > 0 )
);

{
  hint: null,
  details: 'Failing row contains (4, , -1).',
  code: '23514',
  message: 'new row for relation "product" violates check constraint "product_name_check"'
}

create table product
(
    id    serial not null primary key,
    name  varchar
        constraint product_name_must_be_non_empty check (length(name) > 0),
    price int4
        constraint product_price_must_be_positive check ( price > 0 )
);

yarn add @raphiniert/ra-data-postgrest

import postgrestRestProvider from "@raphiniert/ra-data-postgrest";
import { fetchUtils } from "react-admin";
import { supabase } from "./supabaseClient";

const httpClient = (url: any, options: fetchUtils.Options = {}) => {
  const apikey = process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!;
  const authBearer = supabase.auth.session()?.access_token ?? apikey;
  const Authorization = `Bearer ${authBearer}`;
  const headers =
    options.headers instanceof Headers ? options.headers : new Headers();
  headers.set("apikey", apikey);
  headers.set("Authorization", Authorization);
  options.headers = headers;
  return fetchUtils.fetchJson(url, options);
};
export const dataProvider = postgrestRestProvider(
  `${process.env.NEXT_PUBLIC_SUPABASE_URL}/rest/v1`,
  httpClient
);