Database Schema

Supaplate uses Drizzle ORM to manage the database schema.

In this document you will learn everything related to managing the database schema with Drizzle and securing your data with Row Level Security (RLS).

Scripts

In the package.json file you will find the following database related scripts:

"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: Searches for changes in any of the schema.ts files using the ./app/features/**/schema.ts pattern and generates new migration files in the ./sql/migrations directory.
• db:migrate: Applies the migrations to the database.
• db:typegen: Generates the database types for the Supabase client.

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

Drizzle + Supabase

Drizzle has some very helpful utilities that make it easier to work with Supabase.

authUsers

This is an export from drizzle-orm/supabase that you can use to reference the users table from Supabase Auth in your foreign keys.

As an example, this is what we actually do in the users/schema.ts file to reference the users table from the profile table:

// ./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

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.

For example, if you want to build real-time features like chat or live notifications, if you build a mobile app, if you want to use the Supabase Storage bucket in the client or if you want to use React Router's clientLoader or clientAction.

In all these cases, you will need to expose the Supabase keys to the client, which means that you will need to enable Row Level Security (RLS) in your database's tables and write some policies to control access to the data.

Writing policies in raw SQL is not the best developer experience, so we recommend using Drizzle to manage your RLS policies.

This has several benefits:

• You can write the policies in TypeScript, so you get autocompletion and type safety.
• You can write the policies in the same place as the table schema, so you can keep everything related to a table in the same place.
• You can check in the policies to version control, so you can keep track of the changes and you can collaborate with your team.
• You can use Drizzle's helper functions to make the policies more readable and easier to write.

Some of those helper function are:

• authUid: The authenticated user's ID, you can use it instead of writing (select auth.uid()) everywhere.
• authenticatedRole: For policies that apply to authenticated users.
• publicRole: For policies that apply to unauthenticated users.

Here is an example of a policy for the payments table, it makes use of authUid and authenticatedRole to check if the user is authenticated and if so, it allows them to see their own payments.

// ./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}`,
    }),
  ],
)

Please refer to the Drizzle RLS docs for more information on how to write policies and the Supabase specific helpers.

Extras

Helpers

Supaplate comes with a couple of helpers to add what we consider to be good defaults to your tables, timestamps and identity columns.

// ./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

When you ran npm run db:migrate the first time, it created a set_updated_at function in your database.

This function is used to update the updated_at column of a table when a row is updated, it looks like this:

-- ./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;
$$;

Every time you create a new table, you need to add a trigger to the table using the set_updated_at function to update the updated_at column, this is due to the fact that PostgreSQL does not automatically update the updated_at column when a row is updated.

Example

Let's say you want to create a new table called todos, here is the process we suggest you follow to do so:

1. Create the table

Create the table in the /app/features/todos/schema.ts file and add the policies to control access to the table using all the helpers we mentioned before, for example:

// ./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. Generate the migration

Run npm run db:generate to generate the migration file:

npm run db:generate

This will create a new migration file with the new table and it's RLS policies in the ./sql/migrations directory.

3. Create an empty migration to set the updated_at trigger

Run npm run db:generate with the --custom flag to generate an empty migration file:

npm run db:generate -- --custom

This will create an empty migration file in the ./sql/migrations directory, for example:

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

Edit the migration file to add the set_updated_at trigger to the table:

-- ./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. Apply the migrations

Run npm run db:migrate to apply the migrations to the database.

npm run db:migrate

5. Generate the database types

Run npm run db:typegen to generate the database types for the Supabase client.

npm run db:typegen

We suggest adding a postdb:migrate script to your package.json file that runs the db:typegen command after the migrations have been applied.

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

This will ensure that the database types are generated after the migrations have been applied.