How to use Supabase as your PostgreSQL hosting

I recently found out you can use Supabase as your PostgreSQL hosting.

Supabase is a very interesting project. It’s not “just a database hosting” but it’s also one, so you can use it as that. Every Supabase project contains a full, real PostgreSQL database — not a compatible clone, actual Postgres. It’s an app development platform built on top of PostgreSQL, and they offer a free connection pooler, which helps not exhausting the database connection limit with Prisma.

They allow up to 2 projects in the free account, so it’s worth trying it.

You can also work on a new project, then delete the old ones once you’re done.

Supabase markets itself as a Firebase alternative, and in addition to the database you have authentication, subscriptions, and a lot more you can explore later on.

NOTE: Supabase can also be self hosted, so your 2 projects limit will disappear, at the expense of having to manage your own infrastructure

To set it up, first login with GitHub on supabase.com

Create a new project

Then grab the direct connection string to the database. When I first wrote this post it lived under Settings → Database (click URI); in the current dashboard you click the Connect button at the top of the project page and pick “Direct connection”

That’s what you will need to put in your .env file for the DATABASE_URL variable.

Remember to fill [YOUR-PASSWORD] with the password you set for the project when you created it on Supabase.

If you’re ever unsure about the parts of a Postgres connection string, I built a free connection string builder that builds and parses them for you.

If you use Prisma now you can run npx prisma migrate dev to create the tables from the Prisma schema.

This would work already to connect to the database, but since Supabase offers a connection pooler, which helps us not exhaust the connections we can make to the database, why not use it?

The pooler comes in two flavors: a session pooler (each client keeps a server connection for the whole session) and a transaction pooler on port 6543 (a server connection is borrowed only for the duration of each transaction). The transaction pooler is the one you want for serverless functions, because hundreds of short-lived function invocations would otherwise each open their own direct connection and exhaust the database limit.

If you use Prisma: the connection pool does not work when using npx prisma migrate dev, for that command you must use the direct database connection string as shown above. So remember to swap the DATABASE_URL value in case you need it.

Below, you’ll see the connection pooling connection string:

When you copy that to your .env file, add ?pgbouncer=true at the end as explained here.

That flag tells Prisma it’s talking to a transaction-mode pooler, so it disables prepared statements, which transaction pooling cannot support (the next transaction might land on a different server connection).