Intro

You can skip this intro if you're familiar with Prisma, Supabase, and TRPC.

In this blog post, we'll explore 3 tools and frameworks that when combined, deliver a powerful and efficient development experience: Prisma, Supabase, and TRPC.

Prisma is an open-source database toolkit that includes an Object-Relational Mapping (ORM) component, designed to facilitate easier database access. With features like auto-completion, database migrations, and type safety, it has grown in popularity among the development community.

Supabase is an open-source Firebase alternative platform that allows developers to build scalable and secure web applications. It provides a suite of tools that make it easy to build a backend for your application, including authentication, database management, and file storage.

Lastly, we have TRPC, an end-to-end typesafe Remote Procedure Call (RPC) framework. With TRPC, developers can create APIs that are highly efficient, type-safe, and easy to understand and maintain.

A year ago, I was introduced to Prisma, and since then, it has become an indispensable part of my development process. Simultaneously, Supabase subtly captured my attention from time to time. However, my preference typically has always leaned towards specialized tools over all-encompassing solutions. A couple of months ago, curiosity prevailed, and I decided to venture into the world of Supabase. It was then that I encountered a few hurdles. Replicating the effortless management of tables and seamless data migrations that Prisma made almost second nature was unexpectedly challenging in Supabase.

This led me to integrate Prisma with Supabase, creating a combination that leverages the best of both worlds - the robust backend capabilities of Supabase and the efficient data management features of Prisma. Surprisingly, emulating the effortless table management and seamless data migrations that Prisma offered turned out to be a bit of a challenge within Supabase.

In this blog post, I'll guide you through my journey, sharing both the challenges encountered and the solutions

Project Setup

To get started, we'll use the T3 boilerplate for our initial setup. T3 Boilerplate repo

npm

npm create t3-app@latest

yarn

yarn create t3-app

pnpm

pnpm create t3-app@latest

Create Supabase Project

Create a new Supabase project and get a connection string for your Postgres database. Visit supabase.com and initiate a new Project.

Head over to https://supabase.com and start a new Project
Copy your database password and keep it aside somewhere as we will need it for the next step

Once your project has been created, head to your database settings
Copy your database connection string and replace [Your Password] with the actual database password
Your connection string will be something like postgresql://postgres:[YOUR-PASSWORD]@db.oepaufoisavys.supabase.co:5432/postgres

Update Database URL

Update your .env file with your database URL from Supabase

Prisma Setup

We'll need to tweak the schema.prisma file to ensure Prisma and Supabase work well together. The changes will include enabling preview features, setting the database provider, URL, PostgreSQL extensions, and schemas.

generator client {
    provider        = "prisma-client-js"
    previewFeatures = ["multiSchema"]
}

datasource db {
    provider = "postgresql"
    url      = env("DATABASE_URL")
    schemas  = ["auth", "public", "storage"]
}

We are going to enable the following preview features in Prisma multiSchema
The multiSchema feature, allows you to define multiple schemas in a PostgreSQL database.
provider = "postgresql": This line indicates that you're using PostgreSQL as your database.
url = env("DATABASE_URL"): This line is used to connect to your database. It pulls the database URL from your environment variables.
schemas: This array lists the different schemas that Prisma should be aware of in your PostgreSQL database. In this case, it includes the auth, public, and storage schemas.

Baselining your database

Supabase databases come pre-configured with data/settings we cannot reset, so we must baseline our database to avoid issues. Baselining tells Prisma Migrate to assume that one or more migrations have already been applied to your database. We'll pull our current database, generate a Prisma migration file, make changes in our migration.sql file, and finally apply the migration as resolved.

Baselining refers to initializing your migration history for a database that might already contain data and cannot be reset, such as your production database. Baselining tells Prisma Migrate to assume that one or more migrations have already been applied to your database.

For more info see Baselining your database

First, we pull our current database by running npx prisma db pull
Initialize Run mkdir -p prisma/migrations/0_init
Then we need to generate a Prisma migration file npx prisma migrate diff --from-empty --to-schema-datamodel prisma/schema.prisma --script > prisma/migrations/0_init/migration.sql
Make the following changes in your migration.sql file

-- "email" TEXT DEFAULT lower((identity_data->>'email'::text)),
+ "email" TEXT GENERATED ALWAYS AS (lower((identity_data->>'email'::text))) STORED,

-- "confirmed_at" TIMESTAMPTZ(6) DEFAULT LEAST(email_confirmed_at, phone_confirmed_at),
+ "confirmed_at" TIMESTAMPTZ(6) GENERATED ALWAYS AS (LEAST(email_confirmed_at, phone_confirmed_at)) STORED,

-- "path_tokens" TEXT[] DEFAULT string_to_array(name, '/'::text),
    "path_tokens" TEXT[] GENERATED ALWAYS AS (string_to_array(name, '/'::text)) STORED,

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Add the following:
grant usage on schema public to postgres, anon, authenticated, service_role;

grant all privileges on all tables in schema public to postgres, anon, authenticated, service_role;
grant all privileges on all functions in schema public to postgres, anon, authenticated, service_role;
grant all privileges on all sequences in schema public to postgres, anon, authenticated, service_role;

alter default privileges in schema public grant all on tables to postgres, anon, authenticated, service_role;
alter default privileges in schema public grant all on functions to postgres, anon, authenticated, service_role;
alter default privileges in schema public grant all on sequences to postgres, anon, authenticated, service_role;

Once the changes have been made, you will need to apply the migration as resolved.
Run npx prisma migrate resolve --applied 0_init
You will need to run this every time you initialize any new environment. Otherwise, you will be forced to drop all tables and recreate them (you don't want to do this or all grants and permissions already set up by Supabase will be dropped).

Bonus - Create

If you also plan on utilizing Supabase's authentication, update your schema.prisma file and run specific commands to generate and apply migrations. Following this, you'll need to create an empty migration and update it to ensure that when using Supabase auth, your Users will be updated in the public schema.

Update your schema.prisma file with the following:

model UserProfiles {
    id        String   @id @db.Uuid
    username  String?  @unique
    fullName  String?  @map("full_name")
    email     String?
    phone     String?
    avatarUrl String?  @map("avatar_url")
    website   String?
    createdAt DateTime @default(now()) @map("created_at") @db.Timestamptz(6)
    updatedAt DateTime @updatedAt @map("updated_at") @db.Timestamptz(6)

    @@map("user_profiles")
    @@schema("public")
}

Run npx prisma migrate dev --name add_user_profile command to generate and apply migrations.
Next run npx prisma migrate dev --name user_trigger --create-only
This will create an empty migration
Update with the following. This will ensure when using Supabase auth, your Users will be updated in the public schema

-- This trigger automatically creates a profile entry when a new user signs up via Supabase Auth.
-- See https://supabase.com/docs/guides/auth/managing-user-data#using-triggers for more details.

create function public.handle_new_user()
returns trigger as $$
begin
    insert into public.user_profiles (id, full_name, avatar_url, email, phone, updated_at)
    values (new.id, new.raw_user_meta_data->>'full_name', new.raw_user_meta_data->>'avatar_url', new.email, new.phone, now());
    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();

-- FUNCTION TO UPDATE USER PROFILE
create or replace function public.handle_update_user() 
returns trigger as $$
begin
    update public.user_profiles
    set email = new.email
    where id = new.id;
    return new;
end;

$$ language plpgsql security definer set search_path = public;
create trigger on_auth_user_updated
    after update of email on auth.users
  for each row execute procedure public.handle_update_user();

Run npx prisma migrate dev to run your migration
Every time a new user is created, the user will be synced to the user_profiles table

Now you can use Prisma and Supabase.

Conclusion

Combining the power of Prisma, Supabase, and TRPC can result in a fast, scalable, and type-safe application. Understanding how these tools integrate and complement each other is key to leveraging their benefits. This guide serves as a foundation, but the potential for what you can build is limitless. Happy coding!

Tobi Codes