Migrating to Prisma Postgres Global DB from postgreSQL DB with Prisma ORM

Recently Prisma came with an update and launched its new global database called Prisma Postgres. It's a great option for our deploys as it saves the time and money for searching other platforms and providers to host our database online. It provides 5 databases with 1 GB storage for its free plan users.

If you are a Prisma user with postgreSQL database then the below mentioned steps in this blogpost would help you easily migrate all your data from the old database to the new global database. So let's just begin directly!

Step 1

Create a new database in Prisma Console

Step 2

Go to Database > Setup > Existing Project > Generate database credentials

Step 3

On clicking the generate button, your new database details (connection url + api key) would be shown, store them in your .env file of the project.

Step 4

Now, open up a terminal and get your old database's connection url and enter the following command

pg_dump -Fc -v -d DATABASE_URL -n public -f db_dump.bak

This will create a bak file which will have your all previous data with the name db_dump.

Step 5

Set the environment variable in the terminal explicitly by running the following command

export DATABASE_URL="prisma+postgres://accelerate.prisma-data.net/?api_key=API_KEY"

Use the API_KEY which was received in step 3.

Step 6

Open up a bash terminal (if you weren't using it in earlier steps) and enter the following command to start a tunnel.

npx @prisma/ppg-tunnel --host 127.0.0.1 --port 5433

If this runs properly, you won't be able to use that terminal now further. Open up a new terminal, but don't close the previous one, as it runs our tunnel.

Step 7

Now comes the final command to transfer the data, enter it and you're good to go

PGSSLMODE=disable pg_restore -h 127.0.0.1 -p 5433 -v -d postgres ./db_dump.bak && echo "-complete-"

A message "-complete-" would appear on successful transfer of the data. For further verification, you can run

npx prisma studio

with the new database connection url in your .env to check whether the old data is there in your new database or not.

Reference

Import from existing Postgres database into Prisma Postgres | Prisma Documentation

Learn how to import data from an existing database into Prisma Postgres.

prisma.io