Database Schema

Supaplate는 Drizzle ORM을 사용하여 데이터베이스 스키마(schema)를 관리합니다.

이 섹션에서는 Drizzle로 데이터베이스 스키마(schema)를 관리하고 RLS(Row Level Security)로 데이터를 안전하게 보호하는 방법에 대해 배울 수 있습니다.

Scripts

package.json 파일에는 다음과 같은 데이터베이스 관련 스크립트가 있습니다:

"scripts":{
...
"db:generate":"drizzle-kit generate",
"db:migrate":"drizzle-kit migrate",
"db:typegen":"supabase gen types typescript --project-id <project-id> > database.types.ts",
}

• db:generate: ./app/features/**/schema.ts 패턴을 사용하여 schema.ts 파일에서 변경 사항을 검색하고, ./sql/migrations 디렉토리에 새 마이그레이션 파일을 생성합니다.
• db:migrate: 데이터베이스에 마이그레이션(migration)을 적용합니다.
• db:typegen: Supabase 클라이언트에 대한 데이터베이스 타입을 생성합니다.

export const profiles = pgTable('profiles', {
  // ...
})

Drizzle + Supabase

Drizzle은 Supabase와의 작업을 더 쉽게 할 수 있도록 도와주는 매우 유용한 도구를 제공합니다.

authUsers

이것은 drizzle-orm/supabase에서 export 한 것으로, 외부 키에서 Supabase Auth의 유저 테이블을 참조하는 데 사용할 수 있습니다.

예를 들어, 프로필 테이블에서 유저 테이블을 참조하기 위해 users/schema.ts 파일에서 실제로 수행하는 작업은 다음과 같습니다:

// ./app/features/users/schema.ts
import { authUsers } from 'drizzle-orm/supabase'
import { pgTable, uuid } from 'drizzle-orm/pg-core'

export const profiles = pgTable('profiles', {
  profile_id: uuid()
    .primaryKey()
    .references(() => authUsers.id, {
      onDelete: 'cascade',
    }),
  // ...
})

RLS + Supabase Roles

Supaplate에서는 보안을 유지하기 위해 기본적으로 Supabase와의 모든 상호작용이 백엔드에서 처리됩니다. 이를 통해 Supabase 키가 외부에 노출되지 않습니다. 다만 특정 상황에서는 클라이언트 측에서 Supabase 키에 접근할 필요가 있고, 이러한 경우 클라이언트에 키를 공개해야 하는 경우가 발생합니다. (원문 : In Supaplate all the interactions with Supabase happen in the backend by default, so the Supabase keys are never exposed to the client, however, there are many cases where you will need to expose the keys to the client.)

예를 들어 채팅이나 실시간 알림과 같은 리얼타임(real-time) 기능을 구축하려는 경우나 모바일 앱을 구축하려는 경우, 또는 클라이언트에서 Supabase 저장소 버킷을 사용하려는 경우, 혹은 React Router의 clientLoader 또는 clientAction을 사용하려는 경우 등이 있습니다.

이러한 경우에 Supabase 키를 클라이언트에 공개해야 할 필요가 있습니다. 이 때 데이터베이스의 테이블에서 RLS(Row Level Security)를 활성화 해야합니다. 그리고 엑세스 권한을 세부적으로 관리하기 위한 몇 가지 정책을 작성해야 합니다.

원시 SQL로 정책을 작성하는 것은 개발자 경험 측면에서 최적의 방법이 아닙니다. 따라서 RLS(Row Level Security) 정책 관리를 위해 Drizzle을 사용할 것을 권장합니다.

이렇게 할 경우 몇 가지 장점이 있습니다:

• 정책을 TypeScript로 작성할 수 있으므로 자동 완성과 타입 안전성을 확보할 수 있습니다.
• 테이블 스키마(schema)와 같은 위치에 정책을 작성할 수 있으므로 테이블과 관련된 모든 것을 한 곳에서 관리할 수 있습니다.
• 정책을 버전 관리에 포함할 수 있어 변경 사항을 추적하고 팀과 협업할 수 있습니다.
• Drizzle의 헬퍼 함수를 사용하여 정책을 더 읽고 쓰기 쉽게 만들 수 있습니다.

헬퍼 함수 중 일부는 아래와 같습니다:

• authUid: 인증된 유저의 ID로, 매번 (select auth.uid())를 모든 곳에 작성하는 대신 사용할 수 있는 함수입니다.
• authenticatedRole: 인증된 유저에게 적용되는 정책을 위한 함수입니다.
• publicRole: 인증되지 않은 유저에게 적용되는 정책을 위한 함수입니다.

다음은 payments 테이블에 대한 정책의 예시입니다. authUid 및 authenticatedRole을 사용하여 유저가 인증되었는지 확인하고 인증된 경우 자신의 결제 내역을 볼 수 있도록 합니다.

// ./app/features/payments/schema.ts
import { sql } from 'drizzle-orm'
import { pgPolicy, pgTable } from 'drizzle-orm/pg-core'
import { authUid, authUsers, authenticatedRole } from 'drizzle-orm/supabase'

export const payments = pgTable(
  'payments',
  {
    user_id: uuid().references(() => authUsers.id, {
      onDelete: 'cascade',
    }),
  },
  (table) => [
    pgPolicy('select-payment-policy', {
      for: 'select',
      to: authenticatedRole,
      as: 'permissive',
      using: sql`${authUid} = ${table.user_id}`,
    }),
  ],
)

정책 작성 방법 및 Supabase 관련 헬퍼 함수에 대한 자세한 내용은 Drizzle RLS 문서를 참조하세요.

추가 기능

Helpers

Supaplate에는 테이블, 타임스탬프 등 ID 열에 알맞은 기본 설정을 추가할 수 있는 몇 가지 헬퍼를 제공합니다.

// ./core/db/helpers.server.ts
import { bigint, timestamp } from 'drizzle-orm/pg-core'

export const timestamps = {
  updated_at: timestamp().defaultNow().notNull(),
  created_at: timestamp().defaultNow().notNull(),
}

export function makeIdentityColumn(name: string) {
  return {
    [name]: bigint({ mode: 'number' }).primaryKey().generatedAlwaysAsIdentity(),
  }
}

set_updated_at.sql

처음 npm run db:migrate 를 실행하면 데이터베이스에 set_updated_at 함수가 생성됩니다.

이 함수는 행이 업데이트될 때 테이블의 updated_at 열을 업데이트하는 데 사용되며, 다음과 같이 보입니다:

-- ./sql/functions/set_updated_at.sql
CREATE OR REPLACE FUNCTION public.set_updated_at()
RETURNS TRIGGER
LANGUAGE PLPGSQL
SECURITY DEFINER
SET SEARCH_PATH = ''
AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP AT TIME ZONE 'UTC';
    RETURN NEW;
END;
$$;

새 테이블을 생성할 때마다, set_updated_at 함수를 사용하여 updated_at 열을 업데이트하기 위한 트리거를 테이블에 추가해야 합니다. 이는 PostgreSQL이 행이 업데이트될 때 updated_at 열을 자동으로 업데이트하지 않기 때문입니다.

예시

'todos'라는 새 테이블을 만들고 싶다고 가정할 때, 다음과 같은 프로세스를 따르는 것이 좋습니다:

1. 테이블을 만듭니다.

/app/features/todos/schema.ts 파일에 테이블을 만들고 앞서 언급한 모든 헬퍼 함수를 사용하여 테이블에 대한 액세스를 제어하는 정책을 추가합니다:

// ./app/features/todos/schema.ts
import { sql } from 'drizzle-orm'
import { pgPolicy, pgTable, text, uuid, boolean } from 'drizzle-orm/pg-core'
import { authUsers, authenticatedRole, authUid } from 'drizzle-orm/supabase'
import { makeIdentityColumn } from '~/core/db/helpers.server'

export const todos = pgTable(
  'todos',
  {
    ...makeIdentityColumn('todo_id'),
    user_id: uuid().references(() => authUsers.id, {
      onDelete: 'cascade',
    }),
    title: text().notNull(),
    completed: boolean().notNull().default(false),
    ...timestamps,
  },
  (table) => [
    pgPolicy('select-todo-policy', {
      for: 'select',
      to: authenticatedRole,
      as: 'permissive',
      using: sql`${authUid} = ${table.user_id}`,
    }),
    pgPolicy('insert-todo-policy', {
      for: 'insert',
      to: authenticatedRole,
      as: 'permissive',
      check: sql`${authUid} = ${table.user_id}`,
    }),
    pgPolicy('update-todo-policy', {
      for: 'update',
      to: authenticatedRole,
      as: 'permissive',
      using: sql`${authUid} = ${table.user_id}`,
      check: sql`${authUid} = ${table.user_id}`,
    }),
    pgPolicy('delete-todo-policy', {
      for: 'delete',
      to: authenticatedRole,
      as: 'permissive',
      using: sql`${authUid} = ${table.user_id}`,
    }),
  ],
)

2. 마이그레이션을 생성합니다.

npm run db:generate 를 실행하여 마이그레이션 파일을 생성합니다:

npm run db:generate

이렇게 하면 새 테이블이 포함된 새 마이그레이션 파일과 해당 RLS 정책이 './sql/migrations' 디렉토리에 생성됩니다.

3. 빈 마이그레이션을 생성하여 updated_at 트리거(trigger)를 설정합니다.

--custom 플래그를 사용하여 npm run db:generate를 실행하고 빈 마이그레이션 파일을 생성합니다:

npm run db:generate -- --custom

이렇게 하면 './sql/migrations' 디렉토리에 빈 마이그레이션 파일이 생성됩니다. 예시:

./sql/migrations/20240101000000-empty-migration.sql

마이그레이션 파일을 편집하여 테이블에 set_updated_at 트리거(trigger)를 추가합니다:

-- ./sql/migrations/20240101000000-empty-migration.sql
CREATE TRIGGER set_todos_updated_at -- <- name of the trigger
BEFORE UPDATE ON todos
FOR EACH ROW
EXECUTE FUNCTION public.set_updated_at();

4. 마이그레이션을 적용합니다.

npm run db:migrate 를 실행하여 데이터베이스에 마이그레이션을 적용합니다.

npm run db:migrate

5. 데이터베이스 타입을 생성합니다.

npm run db:typegen 을 실행하여 Supabase 클라이언트의 데이터베이스 타입을 생성합니다.

npm run db:typegen

package.json 파일에 postdb:migrate 스크립트를 추가할 것을 권장합니다. 이 스크립트는 마이그레이션이 적용된 후에 db:typegen 명령을 실행합니다.

"scripts":{
...
"postdb:migrate":"npm run db:typegen",
}

이렇게 하면 마이그레이션 적용 후 데이터베이스 타입이 자동으로 생성됩니다.