Skip to main content

Import from existing database

MySQL

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

You can accomplish this migration in four steps:

  1. Create a new Prisma Postgres database.
  2. Connect directly to a Prisma Postgres instance using the @prisma/ppg-tunnel package.
  3. Migrate your MySQL data to Prisma Postgres using pgloader.
  4. Configure your Prisma project for Prisma Postgres.

Prerequisites​

  • The connection URL to your existing MySQL database.
  • A account.
  • Node.js 18+ installed.
  • pgloader installed.

We recommend attempting this migration in a separate git development branch.

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 the next step.

2. Connect directly to a Prisma Postgres instance​

In this step, you'll use a secure TCP tunnel to connect to your Prisma Postgres instance.

You'll need the Prisma Postgres connection URL from step 1:

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 using the @prisma/ppg-tunnel package, by executing the following command:

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

You can specify a different host and port by providing your own host and port values using the --port and --host flags. Just be sure to use the same host and port values consistently throughout the guide.

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.

3. Migrate your MySQL data to Prisma Postgres using pgloader​

Now that you have an active connection to your Prisma Postgres instance, you'll use pgloader to export data from your MySQL database to Prisma Postgres.

Open a separate terminal window and create a config.load file:

touch config.load

Open the config.load file in your preferred text editor and copy-paste the following configuration:

config.load
LOAD DATABASE
    FROM mysql://username:password@host:PORT/database_name
    INTO postgresql://user:password@127.0.0.1:5433/postgres

WITH quote identifiers,    -- preserve table/column name case by quoting them
     include drop,
     create tables,
     create indexes,
     reset sequences

ALTER SCHEMA 'database_name' RENAME TO 'public';

Make sure to update the following details in the config.load file:

  • FROM url (MySQL database URL):
    • Replace username, password, host, PORT, and database_name with the actual connection details for your MySQL database.
    • Ensure that your connection string includes useSSL=true if SSL is required, for example: mysql://username:password@host:PORT/database_name?useSSL=true. Note that when using PlanetScale, appending sslaccept=strict will not work.
  • INTO url (Postgres database URL):
    • Update this with your TCP tunnel details if you’re using a custom host and port (in this example, it’s 127.0.0.1 and port 5433 for consistency).
  • Update the database_name in ALTER SCHEMA 'database_name' RENAME TO 'public'; to exactly match the database_name in your MySQL connection string.

After saving the configuration file with your updated credentials, in the same terminal window, execute the following command:

pgloader config.load

You should see a log similar to this, which confirms the successful migration of your data:

LOG report summary reset
               table name     errors       rows      bytes      total time
-------------------------  ---------  ---------  ---------  --------------
          fetch meta data          0          9                     2.546s
           Create Schemas          0          0                     0.325s
         Create SQL Types          0          0                     0.635s
            Create tables          0          6                     5.695s
           Set Table OIDs          0          3                     0.328s
-------------------------  ---------  ---------  ---------  --------------
              public.post          0          8     0.5 kB          4.255s
            public."user"          0          4     0.1 kB          2.775s
public._prisma_migrations          0          1     0.2 kB          4.278s
-------------------------  ---------  ---------  ---------  --------------
  COPY Threads Completion          0          4                     5.095s
   Index Build Completion          0          5                     9.601s
           Create Indexes          0          5                     4.116s
          Reset Sequences          0          2                     4.540s
             Primary Keys          0          3                     2.917s
      Create Foreign Keys          0          1                     1.121s
          Create Triggers          0          0                     0.651s
         Install Comments          0          0                     0.000s
-------------------------  ---------  ---------  ---------  --------------
        Total import time          ✓         13     0.8 kB         28.042s

If you see output like this, it means your data has been successfully exported to your Prisma Postgres instance.

note

You also can use Prisma Studio and verify whether the migration was successful:

npx prisma studio

4. Configure your Prisma project for Prisma Postgres​

After migrating your data, you need to set up your Prisma project to work with Prisma Postgres. The steps differ depending on whether you were already using Prisma ORM.

If you were not previously using Prisma ORM​

Initialize Prisma in your project by running npx prisma init in your project directory. This creates a prisma folder with a schema.prisma file and .env file (if not already present).

In the generated .env file, update DATABASE_URL to match your Prisma Postgres connection string that you received in step 1:

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

Introspect your newly migrated database by running:

npx prisma db pull

This command updates your schema.prisma file with models representing your migrated tables, so you can start using Prisma Client to query your data or Prisma Migrate to manage future changes.

Congratulations! You've successfully migrated your MySQL database to Prisma Postgres and configured your Prisma project. Your migration tutorial is now complete.

note

For a comprehensive guide on getting started with Prisma and Prisma Postgres, see start from scratch with Prisma and Prisma Postgres.

If you were already using Prisma ORM​

In your schema.prisma file, change the provider in the datasource block from mysql to postgresql:

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

In the generated .env file, update DATABASE_URL to match your new Prisma Postgres connection string that you received in step 1:

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

Introspect your newly migrated Prisma Postgres database and generate Prisma Client:

npx prisma db pull

This command refreshes your Prisma models based on the new database schema.

If you were using Prisma Migrate before:

  • Delete your existing migrations folder in the prisma directory.
  • Baseline your database to begin creating new migrations.

Congratulations! You've successfully migrated your MySQL database to Prisma Postgres and configured your Prisma project. Your migration tutorial is now complete.

If you encounter any issues during the migration, please don't hesitate to reach out to us on Discord or via X.