Skip to main content

Import from existing database

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 the TCP tunnel to securely connect to your Prisma Postgres database during to run pg_restore.

Prerequisites​

  • The connection URL to your existing PostgreSQL database
  • A account
  • Node.js installed (version 16 or higher)
  • 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 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 was provisioned, find your Prisma Postgres connection URL in the Set up database access section and save it for later, 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:

Neon

  • Make sure to select non-pooled connection string by switching off the Connection pooling toggle.
  • The sslmode has to be set to require and appended to your Neon database url for the command to work.
  • The connection URL should look similar to this: 
    postgresql://USER:PASSWORD@YOUR-NEON-HOST/DATABASE?sslmode=require
Supabase
  • Use a database connection URL that uses Supavisor session mode.
  • The connection URL should look similar to this: 
    postgres://postgres.apbkobhfnmcqqzqeeqss:[YOUR-PASSWORD]@aws-0-ca-central-1.pooler.supabase.com:5432/postgres

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 step, you'll use the TCP tunnel to connect to your Prisma Postgres instance and import data via pg_restore.

You'll also need the Prisma Postgres connection URL from step 1, it should look similar to this:

prisma+postgres://accelerate.prisma-data.net/?api_key=ey...

Open your terminal and set the DATABASE_URL environment variable to the value of your Prisma Postgres database URL (replace the __API_KEY__ placeholder with the API key of your actual database connection URL):

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

Next, start the TCP tunnel:

npx @prisma/ppg-tunnel --host 127.0.0.1 --port 5433
Show CLI results
Prisma Postgres auth proxy listening on 127.0.0.1:5433 🚀

Your connection is authenticated using your Prisma Postgres API key.
...

==============================
hostname:  127.0.0.1
port:      5433
username:  <anything>
password:  <none>
==============================
note

Keep your current terminal window or tab open so that the tunnel process continues running and the connection remains open.

Now, use the db_dump.bak backup file from the previous step to restore data into your Prisma Postgres database with the pg_restore command:

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

You don't need to provide username and password credentials to this command because the TCP tunnel already authenticated you via the API key in your Prisma Postgres connection URL.

You now successfully imported the data from your your existing PostgreSQL database into Prisma Postgres 🎉

To validate that the import worked, you can use Prisma Studio. Either open it in the 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 already using Prisma ORM, the only things you need to do are:

  • add the Prisma Accelerate extension to your project
  • update the database connection URL and re-generate Prisma Client

4.A.1. Add the Prisma Accelerate extension​

Th Prisma Accelerate extension is required when using Prisma Postgres. If you are not currently using Prisma Accelerate with Prisma ORM, go through the following steps to make Prisma ORM work with Prisma Postgres.

First, install the @prisma/extension-accelerate package in your project:

npm install @prisma/extension-accelerate

Then, add the extension to your Prisma Client instance:

import { withAccelerate } from '@prisma/extension-accelerate'

const prisma = new PrismaClient().$extends(withAccelerate())

4.A.2. Update the database connection URL​

The database connection URL is configured via the url of the datasource block in your schema.prisma file. Most commonly, it is set via an environment variable called DATABASE_URL:

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

The next steps assumes that you're a .env file to set the DATABASE_URL environment variable (if that's not the case, you can set the environment variable in your preferred way).

Open .env and update the value for the DATABASE_URL environment variable to match your Prisma Postgres connection URL, looking similar to this:

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

As a last step, you need to re-generate Prisma Client for so that the updated environment variables takes effect and your queries go to Prisma Postgres going forward:

npx prisma generate --no-engine

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 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.