Setting Up Prisma ORM Connection with Supabase (PostgreSQL)

Introduction

Prisma is one of the most innovative Object-Relational Mappers (ORMs) for JavaScript to emerge in recent years. It’s no wonder its adoption is growing, competing with more traditional ORMs like Sequelize and TypeORM. Prisma supports major relational database managers, including PostgreSQL, SQLite, MySQL (and MariaDB), Microsoft SQL Server, CockroachDB, and even MongoDB, a non-relational database.

Supabase, on the other hand, defines itself as an open-source alternative to Google’s Firebase. It offers a range of services, including a PostgreSQL instance as a DBaaS (Database as a Service), which can be used for free with generous storage limits (note that inactive projects go into hibernation after a week of inactivity). For this reason, Supabase has become a popular choice for hobby projects or MVPs (Minimal Viable Product) development.

Prisma and Supabase’s PostgreSQL work very well together. However, for those who have used Prisma with other databases, connecting Prisma to Supabase requires an extra step if you want to work with migrations in the project.

Below, I’ll show you how to create a PostgreSQL database in Supabase and configure the connection in a new Node.js project.

Creating a Project in Supabase

To access a PostgreSQL instance in Supabase, you need to register on the platform and create a project. Here’s a step-by-step guide:

1. Go to supabase.com and click on Start your project.
2. You can sign up with an email (which must be confirmed later) and a password. However, it’s quicker and easier to sign in with a GitHub account by clicking on Continue with GitHub.
3. If you choose to log in with GitHub, you’ll see a button labeled Authorize supabase. Click it to continue.
4. On your first login to Supabase, you’ll be asked to create an organization and select a billing plan. No need to change the default details—simply click Create Organization to proceed.

5. We’ve reached the project creation stage. Pay attention to the following points:
   • The project name can contain spaces and accented characters.
   • DO NOT USE special characters in the database password. Create a strong one by mixing uppercase, lowercase letters, and digits. Write down this password if needed, as it will be required to set up the Prisma connection.
   • Choose the region closest to you to allocate the server that will host the project.
   • Finish by clicking Create New Project and wait for the project provisioning to complete (this may take a few minutes).

6. Keep the Supabase page open; we’ll return to it shortly.

Creating a Node.js Project

Now, let’s create a Node.js project. You can do this by running npm init in an empty folder, or if you want something more sophisticated and functional, check out my article on creating a Node.js project with Express.js.

Once the project is created, the next step is to install Prisma by running the following command in the terminal:

npm install prisma --save-dev

Next, we need to initialize Prisma to use a PostgreSQL connection.

npx prisma init --datasource-provider postgresql

This will create two files: .env and prisma/schema.prisma. The first file is where we’ll place the database connection string. To get it, return to Supabase.

In Supabase, in the left menu, select Project Settings (it’s near the bottom, identified by a gear icon). Then, choose Database under the Configuration section. You’ll see a page similar to the one shown below. Click the Copy button to copy the connection string.

Back in your Node.js project, open the .env file and paste the connection string in the indicated location. Note that you’ll need to replace [YOUR-PASSWORD] with the actual password you set when creating the project in Supabase. The result should look like the following:

# Environment variables declared in this file are automatically made available to Prisma.
# See the documentation for more detail: https://pris.ly/d/prisma-schema#accessing-environment-variables-from-the-schema

# Prisma supports the native connection string format for PostgreSQL, MySQL, SQLite, SQL Server, MongoDB and CockroachDB.
# See the documentation for all the connection string options: https://pris.ly/d/connection-strings

DATABASE_URL="postgresql://postgres.abhriotztpfmbhpsgivc:[YOUR-PASSWORD]@aws-0-us-east-1.pooler.supabase.com:6543/postgres"

This connection string will be used for most operations that support transactions. However, if you plan to use migrations, you’ll need to set up a second connection string named DIRECT_URL. The connection string is the same, but with port 6543 changed to 5432.

# Environment variables declared in this file are automatically made available to Prisma.
# See the documentation for more detail: https://pris.ly/d/prisma-schema#accessing-environment-variables-from-the-schema

# Prisma supports the native connection string format for PostgreSQL, MySQL, SQLite, SQL Server, MongoDB and CockroachDB.
# See the documentation for all the connection string options: https://pris.ly/d/connection-strings

DATABASE_URL="postgresql://postgres.abhriotztpfmbhpsgivc:[YOUR-PASSWORD]@aws-0-us-east-1.pooler.supabase.com:6543/postgres"

# In DIRECT_URL, we change port 6543 by 5432
DIRECT_URL="postgresql://postgres.abhriotztpfmbhpsgivc:[YOUR-PASSWORD]@aws-0-us-east-1.pooler.supabase.com:5432/postgres"

Save the .env file and open prisma/schema.prisma. We need to reference the DIRECT_URL environment variable in the datasource db section.

// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema

// Looking for ways to speed up your queries, or scale easily with your serverless or edge functions?
// Try Prisma Accelerate: https://pris.ly/cli/accelerate-init

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider  = "postgresql"
  url       = env("DATABASE_URL")
  
  // Add the line below
  directUrl = env("DIRECT_URL")
}

Save the prisma/schema.prisma file, and the setup is complete.

Happy coding!

Featured image credits: Freepik, Prisma, Supabase