DEV Community

Cover image for Set up a free PostgreSQL database on Supabase to use with Prisma
Mahmoud Abdelwahab for Prisma

Posted on • Updated on

Set up a free PostgreSQL database on Supabase to use with Prisma

Supabase is a backend-as-a-service built on top of open source technologies. It gives you a database, authentication, a REST API, real-time subscriptions, and storage.

It offers a free plan which includes a hosted PostgreSQL database. This can be useful if you're getting started with a new project.

This guide explains how to quickly connect the Postgres database provided by Supabase to a Prisma project. There are other services out there that provide hosted PostgreSQL databases like Heroku or Digital Ocean.

Prisma is an open source next-generation ORM. It consists of the following parts:

  • Prisma Client: Auto-generated and type-safe query builder for Node.js & TypeScript.
  • Prisma Migrate: Migration system.
  • Prisma Studio: GUI to view and edit data in your database.

Step 1: Log into Supabase

Navigate your browser to https://app.supabase.io/api/login and log in with GitHub.

Login screen

Step 2: Create a new project

You'll find that an organization has been created for you under the same GitHub username you used when logging in.

Go ahead and create a new project by clicking on "New project" and then pick the organization.

Create a new project

You'll then need to provide a name and set a password for your database (we'll need it later). Finally, click on "create new project".

Set project name and database password

After creating the project, you'll need to wait for ~2 minutes for Supabase to finish creating the database.

Step 3: Get the connection string from the project settings

Go to the settings page from the sidebar and navigate to the Database tab. You'll find the database's connection string with a placeholder for the password you provided when creating the project.

Getting the connection string

Step 4: Testing the connection

To make sure that everything works correctly, let's try the connection string in a Prisma project.

If you already have one, all you need to do is set the DATABASE_URL to the connection string (including the password) in your .env file, and you're good to go.

In case you don't have a Prisma project or this is your first time working with Prisma, you're going to use with the repo from the quickstart guide.

Cloning the starter project

Navigate into a directory of your choice and run the following command in your terminal if you're on a Windows machine:

curl https://pris.ly/quickstart -L -o quickstart-master.tar.gz && tar -zxvf quickstart-master.tar.gz quickstart-master/typescript/starter && move quickstart-master\typescript\starter starter && rmdir /S /Q quickstart-master && del /Q quickstart-master.tar.gz
Enter fullscreen mode Exit fullscreen mode

And if you're using Mac OS or Linux, run the following command:

curl -L https://pris.ly/quickstart | tar -xz --strip=2 quickstart-master/typescript/starter
Enter fullscreen mode Exit fullscreen mode

You can now navigate into the directory and install the project's dependencies:

cd starter && npm install
Enter fullscreen mode Exit fullscreen mode

A look at the project's structure

This project comes with TypeScript configured and has the following structure.

  • A prisma directory which contains:
    • A dev.db file: This is a SQLite database.
    • A schema.prisma file: Where we define the different database models and relations between them.
  • A .env file: Contains the DATABASE_URL variable, which Prisma will use.
  • A script.ts file: where we will run some queries using Prisma Client.

This starter also comes with the following packages installed:

  • @prisma/client: An auto-generated and type-safe query builder that's tailored to your data.
  • prisma: Prisma's command-line interface (CLI). It allows you to initialize new project assets, generate Prisma Client, and analyze existing database structures through introspection to automatically create your application models.

Note: Prisma works with both JavaScript and TypeScript. However, to get the best possible development experience, using TypeScript is highly recommended.

Configuring the project to use PostgreSQL

Go ahead and delete the prisma/dev.db file because we will be switching to PostgreSQL.

Next, inside the prisma/.env file, update the value of the DATABASE_URL variable to the connection string you got in step 3. The URL might look as follows:

# prisma/.env

postgres://postgres:[YOUR-PASSWORD]@db.vdbnhqozmlzdsaejdxwr.supabase.co:5432/postgres

Enter fullscreen mode Exit fullscreen mode

Finally, inside your schema.prisma file, change the provider from "sqlite" to "postgresql".

This is what your schema.prisma file should look like:

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

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

model Post {
  id        Int     @id @default(autoincrement())
  title     String
  content   String?
  published Boolean @default(false)
  author    User?   @relation(fields: [authorId], references: [id])
  authorId  Int?
}

model User {
  id    Int     @id @default(autoincrement())
  email String  @unique
  name  String?
  posts Post[]
}
Enter fullscreen mode Exit fullscreen mode

To test that everything works correctly, run the following command to create a migration:

prisma migrate dev --name init
Enter fullscreen mode Exit fullscreen mode

You can optionally give your migration a name, depending on the changes you made. Since this is the project's first migration, you're setting the --name flag to "init".

If everything works correctly, you should get the following message in your terminal:

Your database is now in sync with your schema.

✔ Generated Prisma Client (2.x.x) to ./node_modules/@prisma/client in 111ms
Enter fullscreen mode Exit fullscreen mode

This will create a prisma/migrations folder inside your prisma directory and synchronize your Prisma schema with your database schema.

Note: if you want to skip the process of creating a migration history, you can use the db push command instead of migrate dev.

If you go to your Supabase project, in the table editor, you should see that two tables have been created, a Post and a User table.

tables created in the UI

That's it! You have now successfully connected a Prisma project to a PostgreSQL database hosted on Supabase and ran your first migration.

Connection pooling with Supabase

If you're working in a serverless environment (for example Node.js functions hosted on AWS Lambda, Vercel or Netlify Functions), you need to set up connection pooling using a tool like PgBouncer. That's because every function invocation may result in a new connection to the database.

Fortunately, Supabase projects support connection management using PgBouncer and are enabled by default.

Go to the Database page from the sidebar and navigate to connection pool settings

Connection pool settings

When running migrations you need to use the non pooled connection URL (like the one we used in step 4). However, when deploying your app, you'll use the pooled connection URL. and add the ?pgbouncer=true flag to the PostgreSQL connection URL. The URL might look as follows:

# prisma/.env

postgres://postgres:[YOUR-PASSWORD]@db.vdbnhqozmlzdsaejdxwr.supabase.co:6543/postgres?pgbouncer=true

Enter fullscreen mode Exit fullscreen mode

Prisma Migrate uses database transactions to check out the current state of the database and the migrations table. However, the Migration Engine is designed to use a single connection to the database, and does not support connection pooling with PgBouncer. If you attempt to run Prisma Migrate commands in any environment that uses PgBouncer for connection pooling, you might see the following error:

Error: undefined: Database error
Error querying the database: db error: ERROR: prepared statement "s0" already exists
Enter fullscreen mode Exit fullscreen mode

This is a known issue and it is being worked on, you can follow the progress on this GitHub issue.

If you want to learn more about Prisma, check out the docs. Also in case you have any questions or run into any issue, feel free to start a discussion in the repo's discussions section.

Discussion (14)

Collapse
kiwicopple profile image
Copple • Edited

This is a great guide!

I'm a cofounder Would you be interested in adding this to the Supabase docs? If yes, perhaps you can message me on twitter: twitter.com/kiwicopple

Collapse
zernonia profile image
Zernonia

I think you have the wrong account tagged. 😂

Collapse
kiwicopple profile image
Copple

thanks! removed the tag

Collapse
rosswaycaster profile image
Ross Waycaster

Thanks for the guide! I've been interested in using Prisma with Supabase, mainly to integrate it with Blitz.js. How can we utilize Supabase Auth with Prisma? Is there a way to send the user's JWT through Prisma so we can take advantage of Supbase Auth Policies? Thanks!

Re: @thisismahmoud @kiwicopple

Collapse
annjkai profile image
Annika Kaiser

@kiwicopple I'd be super interested in this too - I tried using Prisma together with Supabase but there are problems importing the Auth DB and my app's DB simultaneously. Even if I did, I'd have to handle the JWT Tokens on my own, why I don't have experience doing. Would love to know if you can expand on this guide with this use case in mind. Thank you!

Collapse
thisismahmoud profile image
Mahmoud Abdelwahab Author

hey @annjkai and @rosswaycaster 👋🏼 I haven't tried using Supabase Auth with Prisma yet. If I'm able to make it work I'll write a blog post 😄

Collapse
zernonia profile image
Zernonia

Hi @thisismahmoud , could you explain in why should we use Prisma instead of @supabase/supabase-js directly? It feels like an extra effort just to use Supabase.

Collapse
aftabbuddy profile image
Aftab Alam

Hey, though you asked @thisismahmoud I think I have the answer :-)

This article is for Prisma users(and it's written from Prisma's point of view), who use it for everything ranging from DB Management, CRUD operations to Migrations. Prisma works with a PostgreSQL instance (local or cloud) and Supabase let's you connect with the DB that powers your apps directly too. So, in Supabase, Prisma has found one more great cloud-managed PostgreSQL instance that you can connect with following the instructions shared in this article.

@thisismahmoud Hope, that's an all right explanation?

Collapse
zernonia profile image
Zernonia

Everything make sense when you said this is for Prisma users. As I am not one of them, I got confused.

Thank you for your input yar!

Collapse
thisismahmoud profile image
Mahmoud Abdelwahab Author

Yup that's a perfect answer! thank you so much 😄

Thread Thread
amosbastian profile image
Amos Bastian

Then what is the benefit of using Supabase with Prisma over just having your own Postgres database on the same server as your API?

Thread Thread
aftabbuddy profile image
Aftab Alam

The article just shows how easy it is to use Prisma with a cloud PostgreSQL instance. Supabase letting you access the PostgreSQL instance and independently use it, is a very powerful thing. You can prefer to use Supabase client, to perform Auth, and CRUD operation, for which Supabase is known for, but if you don't want that you can also interact with the DB that powers it, to have your hand-rolled, or Prisma-rolled CRUD methods and have it behave as you like.

Prisma loves(and plays well) PostgreSQL(your instance) or PostegreSQL(cloud instance: Heroku, Supabase, Planetscale, etc.) equally. Consider this just as a reference to one more possible integration type you may chose and it's not particularly Supbase v/s. your DB.

Collapse
touhidrahman profile image
Touhid Rahman

Using id Int @id @default(autoincrement()) in prisma schema creates an id field in supabase with following function -
nextval('"Entity_id_seq"'::regclass)

Which is obviously a syntax error in supabase. Do you have any suggestion to overcome this?

Collapse
medicmen profile image
MedicMen

I Did everything like in starter, but I got an error during connection --> Error: P1001: Can't reach database server at ...

Do you know why ?