Import from PostgreSQL

This guide provides step-by-step instructions for importing data from an existing PostgreSQL database into Prisma Postgres.

You can accomplish this migration in three steps:

1. Create a new Prisma Postgres database.
2. Export your existing data via pg_dump.
3. Import the previously exported data into Prisma Postgres via pg_restore.

In the third step, you will be using a direct connection to securely connect to your Prisma Postgres database to run pg_restore.

Prerequisites

• The connection URL to your existing PostgreSQL database
• A Prisma Data Platform account
• Node.js 18+ installed
• PostgreSQL CLI Tools (pg_dump, pg_restore) for creating and restoring backups

1. Create a new Prisma Postgres database

Follow these steps to create a new Prisma Postgres database:

1. Log in to Prisma Data Platform and open the Console.
2. In a workspace of your choice, click the New project button.
3. Type a name for your project in the Name field, e.g. hello-ppg.
4. In the Prisma Postgres section, click the Get started button.
5. In the Region dropdown, select the region that's closest to your current location, e.g. US East (N. Virginia).
6. Click the Create project button.

Once your database is provisioned, obtain your direct connection string:

1. Navigate to your active Prisma Postgres instance.
2. Click the API Keys tab in the project's sidenav.
3. Click the Create API key button.
4. In the popup, provide a Name for the API key and click Create.
5. Copy the connection string starting with postgres://, this is your direct connection string.

Save the connection string, you'll need it in step 3.

2. Export data from your existing database

In this step, you're going to export the data from your existing database and store it in a .bak file on your local machine.

Make sure to have the connection URL for your existing database ready, it should be structured like this:

postgresql://USER:PASSWORD@HOST:PORT/DATABASE

Expand below for provider-specific instructions that help you determine the right connection string:

Next, run the following command to export the data of your PostgreSQL database (replace the __DATABASE_URL__ placeholder with your actual database connection URL):

pg_dump \
  -Fc \
  -v \
  -d __DATABASE_URL__ \
  -n public \
  -f db_dump.bak

Here's a quick overview of the CLI options that were used for this command:

• -Fc: Uses the custom format for backups, recommended for pg_restore
• -v: Runs pg_dump in verbose mode
• -d: Specifies the database connection string
• -n: Specifies the target PostgreSQL schema
• -f: Specifies the output name for the backup file

Running this command will create a backup file named db_dump.bak which you will use to restore the data into your Prisma Postgres database in the next step.

3. Import data into Prisma Postgres

In this section, you'll use your direct connection string to connect to your Prisma Postgres instance and import data via pg_restore.

Your direct connection string from step 1 should look like this:

postgres://USER:PASSWORD@db.prisma.io:5432/?sslmode=require

Use the backup file from Step 2 to restore data into your Prisma Postgres database with pg_restore by running this command (replace __USER__, __PASSWORD__ with the values from your direct connection string):

pg_restore \
  -h db.prisma.io \
  -p 5432 \
  -U __USER__ \
  -d postgres \
  -v \
  ./db_dump.bak \
&& echo "-complete-"

When prompted, enter the __PASSWORD__ from your direct connection string.

pg_restore \
  -d "postgres://USER:PASSWORD@db.prisma.io:5432/postgres?sslmode=require" \
  -v \
  ./db_dump.bak \
&& echo "-complete-"

Once the command completes execution, you will have successfully imported the data from your existing PostgreSQL database into Prisma Postgres 🎉

To validate that the import worked, you can use Prisma Studio. Either open it in the Platform Console by clicking the Studio tab in the left-hand sidenav in your project or run this command to launch Prisma Studio locally:

npx prisma studio

4. Update your application code to query Prisma Postgres

Scenario A: You are already using Prisma ORM

If you're already using Prisma ORM, you need to update your database connection URL to point to your new Prisma Postgres instance.

Update the DATABASE_URL in your .env file to match your Prisma Postgres direct connection string from step 1:

DATABASE_URL="postgres://USER:PASSWORD@db.prisma.io:5432/?sslmode=require"

Then, re-generate Prisma Client so that the updated environment variable takes effect:

npx prisma generate

Once this is done, you can run your application and it should work as before.

Scenario B: You are not yet using Prisma ORM

If you are not yet using Prisma ORM, you'll need to go through the following steps to use Prisma Postgres from your application:

1. Install the Prisma CLI and other required dependencies in your project
2. Introspect the database to generate a Prisma schema
3. Generate Prisma Client
4. Update the queries in your application to use Prisma ORM

You can find the detailed step-by-step instructions for this process in this guide: Add Prisma ORM to an existing project.