Import data from an existing database

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 viapg_dump.
3. Import the previously exported data into Prisma Postgres viapg_restore.

In the third step, you will be using adirect connection to securely connect to your Prisma Postgres database during to runpg_restore.

Prerequisites​

• The connection URL to your existing PostgreSQL database
• A 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 and open the Console.
2. In aworkspace of your choice, click theNew project button.
3. Type a name for your project in theName field, e.g.hello-ppg.
4. In thePrisma Postgres section, click theGet started button.
5. In theRegion dropdown, select the region that's closest to your current location, e.g.US East (N. Virginia).
6. Click theCreate project button.

With your Prisma Postgres instance being created, you can move to the next step.

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 bestructured 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 forpg_restore
• -v: Runspg_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 nameddb_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 aTCP Tunnel in order to connect to your Prisma Postgres instance and import data viapg_restore.

3.1. Connecting to the Prisma Postgres Database with@prisma/ppg-tunnel​

Open a new terminal and set the environment variable for your Prisma Postgres database connection URL.

exportDATABASE_URL="prisma+postgres://accelerate.prisma-data.net/?api_key=eyJhbGciOiJIUzI..."

Run the tunnel:

npx @prisma/ppg-tunnel--host127.0.0.1--port5432

You should see output similar to:

Prisma Postgres auth proxy listening on 127.0.0.1:52604 🚀

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

==============================
hostname:  127.0.0.1
port:      52604
username:  <anything>
password:  <none>
==============================

3.2. Restoring the Backup withpg_restore​

Use the backup file fromStep 2 to restore data into Prisma Postgres database withpg_restore by running this command:

PGSSLMODE=disable\
pg_restore\
-h127.0.0.1\
-p5432\
-v\
-d postgres\
  ./db_dump.bak\
&&echo"-complete-"

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

To validate that the import worked, you can usePrisma Studio. Either open it in the by clicking theStudio 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 isrequired 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=newPrismaClient().$extends(withAccelerate())

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

The database connection URL is configured via theurl of thedatasource block in yourschema.prisma file. Most commonly, it is set via an environment variable calledDATABASE_URL:

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

The next steps assumes that you're a.env file to set theDATABASE_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 theDATABASE_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.