Skip to main content

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 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 during to run pg_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 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.

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 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 section, you'll use a TCP Tunnel in order to connect to your Prisma Postgres instance and import data via pg_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.

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

Run the tunnel:

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

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>
==============================
note

Keep this tunnel process running to maintain the connection!

3.2. Restoring the Backup with pg_restore​

Use the backup file from Step 2 to restore data into Prisma Postgres database with pg_restore by running this command:

PGSSLMODE=disable \
pg_restore \
  -h 127.0.0.1 \
  -p 5432 \
  -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 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.