[Guide] Migrations & Connection Pool URLs (Supabase)

[danny]

Redwooders!

Headsup: This guide is now outdated
Prisma released a new feature to let you specify a direct url for migrations in version Prisma 4.10.1 Use Prisma Migrate and Introspect with the Data Proxy
This means the techniques used in this guide are no longer necessary, as you can just specify the directUrl in your prisma schema file, that’ll be used for running migrations.

---

I know Supabase is a firm favourite among Redwood users for a DB provider, and if you, like me, deploy to serverless environments i.e. Lamba, Netlify or Vercel, will probably want to be using the connection pool url.

However, when you use a connection pool url as your in your DATABASE_URL env var - data migrations fail with Prisma. So what’s a dev to do!?

This guide will walk you through how I set it up to work for my app on Vercel. Very open to feedback, and hopefully gives you ideas on how to implement this better.

TLDR
Connection pooling makes your app more resilient, but to support Prisma migrations at build time, you need to set a different connection string for migrations only.

---

Why connection pooling?

Serverless is awesome, and crazy scalable - suddenly getting a 1000+ people hitting your servers? No problem - your provider will spin up a whole bunch of lambdas to deal with the demand. But hold on, your database isn’t infinitely scalable the same way, and those lambdas that spun up? They don’t necessarily let go of the database connections.

As a result, you start running out of connections to your DB, and Prisma will start throwing errors.

Prisma’s connection management guide is very well written, and this video from Prisma’s Martina is helpful if you want to know more!

---

Supabase to the rescue

Supabase helpfully provides you a special connection pooled url, out of the box!

Construct your DATABASE_URL Using this and adding a few more params:

• sslmode=require
• pgbouncer=true

And if you’re finding you’re still running out of connections, you can try limiting the connection limit per lambda:

• connection_limit=1

Also remember to replace the password!

DATABASE_URL="<CONNECTION_STRING_WITH_PASSWORD>?sslmode=require&pgbouncer=true

Add this environment variable to your project as DATABASE_URL, and once you deploy you’ll have a much more resilient connection to your DB! Supabase puts pgbouncer in front of your Postgres DB, which helps manage the connections for you, so you don’t have to worry about running out too quickly.

---

Wait… Migrations failures

Next time you write a migration, you may see an error like this:

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

To solve this, we need to get Prisma to connect directly to the database (without the connection pool) for migrations only. (See also Prisma’s docs on migrate and PgBouncer)

Solution: Setting a different connection string for migrations

1: Add new MIGRATION_DATABASE environment variable

Create an add a new environment variable, and paste in the direct database connection string.

You can find the direct URL on the Supabase dashboard here:

Add it to your environment variables. On Vercel this is what it looks like currently:

Be careful to select the environment correctly! Use production for prod, obviously, and preview for your staging environment and PRs for example.

2. Change your build command

Now we want to change the default build command to do the build in 2 phases:

• Phase 1: Test, Lint and Build your Redwood API with the Connection Pooled string
• Phase 2: Run migrations and data migrations with direct string

So lets setup a couple of new scripts in our package.json

"scripts": {
+    "ci:build": "yarn rw test && yarn rw build",
+    "ci:migrate": "DATABASE_URL=$MIGRATE_DATABASE yarn rw deploy vercel --no-build",
}

ci:build script

This step is straightforward, run jest tests, and then build both web and api sides. You can of course change this to be specific just to the API side if you are deploying your frontend separately (e.g. like you do on render)

This uses the default environment variable DATABASE_URL i.e. the connection pooled one to build your Prisma client.

ci:migrate script

This is where the magic happens, so let’s break it down.

What we do is, we override the default DATABASE_URL var, with the value in MIGRATE_DATABASE i.e. your direct string. Remember the deploy command runs both prisma migrations, and data migrations for you. The --no-build flag skips rebuilding your app, and is important, so that we don’t change what we are shipping during the migration process.

Let’s commit, but push after step 3.

Step 3: Update Vercel Build

Now let’s modify our Vercel build command to be: yarn ci:build && yarn ci:migrate and we’re good to go!

Push up your branch and your Redwood App will now communicate via the connection pool, while the build process uses the direct connection!

---

Further improvements: Avoid migrations on PRs

So by now you probably have your staging and production environments configured, but there’s one more piece of the puzzle.

Remember that Vercel will build and deploy PRs automatically, and as part of the build it would run migrations - this is something we definitely don’t want! Note that on other providers you are able to specify the build command to be different per branch, but on Vercel we can’t

So let’s modify our ci:migrate command a little.

-    "ci:migrate": "DATABASE_URL=$MIGRATE_DATABASE yarn rw deploy vercel --no-build",
+    "ci:migrate": "if [ -n \"$MIGRATE_DATABASE\" ]; then DATABASE=$MIGRATE_DATABASE yarn rw deploy vercel --no-build; else echo 'Skipping migration. No $MIGRATE_DATABASE'; fi",

And to make it more redable, in multiple lines:

if [ -n \"$MIGRATE_DATABASE\" ]; 
 then DATABASE_URL=$MIGRATE_DATABASE yarn rw deploy vercel --no-build; 
 else echo 'Skipping migration. No $MIGRATE_DATABASE'; 
fi

Now it does a little check to see if the MIGRATE_DATABASE var exists - if it doesn’t, it just skips migration.

Let’s tweak our Vercel configuration as well:

Save your configuration, and we’re all done!

On our next PR, we’ll see that it’ll skip migrations, because MIGRATION_DATABASE will only be set for the staging branch (in preview environments). Note that this means some PRs may not run correctly in your deploy-previews, but it’s a decent compromise! I’d rather have my staging environment stable, rather than break on a PR that runs a migration

Hope you’ve found this helpful!

[tilmannb]

Thanks @danny. I was running in the migration problems and was wondering how to solve it. Your guide was very helpful.

[thedavid]

Amazing guide @danny

But I think I found a spelling mistake:

@danny

favourite

[danny]

Update on this, after migrate Tape to yarn v3, there were subtle changes I had to do to the ci:migrate script. The logic to migrate or not now sits in a separate script, because of how yarn 3 executes scripts differently.

Here’es the new ones:

// package.json
    "ci:migrate": "bash +x ./scripts/migrateIfRequired.sh"

And the actual script:

#!/usr/bin/bash

if [ -z ${MIGRATE_DATABASE+x} ];
  then echo 'Skipping migration. No $MIGRATE_DATABASE';
  else DATABASE_URL=$MIGRATE_DATABASE yarn rw deploy vercel --no-build;
fi

[zygopleural]

@danny how did you get round db pushes / resets and TEST_DATABASE_URL when testing with ?pgbouncer=true?

[danny]

Do you mean during development on your local machine? I wouldn’t think db push/resets are part of your CI?

I wouldn’t recommend using a remote database for local dev!

For test databases, maybe don’t have pgbouncer in front?

[arimendelow]

@danny - thanks for your work on this!! This doesn’t seem to work on redwood 3.8.0 - it’s the root package.json we’re supposed to be editing? I’m not seeing any changes in the build output:

[arimendelow]

Oops, I totally missed step 3 But I learned a bunch about the Redwood build progress in the meantime, so yay!

[mindofjonas]

Prisma now supports direct connections to a database so we can update the Prisma schema to use a different database URL during migrations:

datasource db {
  provider  = "postgres"
  url       = env("DATABASE_URL")
  directUrl = env("MIGRATION_URL")
}

Note here that it requires upgrading Prisma to < 4.10.1 if using a version of Redwood earlier than 4.0.0

[dthyresson]

Thanks for sharing this! The Core Team just learned about by his setting, too, but didn’t get a chance yet to mention.

This is helpful in the Supabase case where your migrations need to be on the main connection but your client connects to pgBouncer — or you connect with any other user account with dedicated role permissions.