Import from 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 a direct connection.
3. Migrate your MySQL data to Prisma Postgres using pgloader.
4. Configure your Prisma project for Prisma Postgres.

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

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

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, find your direct Prisma Postgres connection string:

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

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

In this step, you'll use the direct connection string you obtained in step 1 to connect to your Prisma Postgres instance.

Your direct connection string should look like this:

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

You'll use this connection string in the next step when configuring 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:

LOAD DATABASE

    FROM mysql://username:password@host:PORT/database_name

    INTO postgres://__USER__:__PASSWORD__@db.prisma.io:5432/?sslmode=require

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 direct connection string from above, replacing the __USER__ and __PASSWORD__ placeholders.

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

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

bunx prisma studio

pnpm prisma studio

yarn prisma studio

npx prisma studio

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.

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 direct connection string that you received in step 1:

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

Introspect your newly migrated database by running:

bunx prisma db pull

pnpm prisma db pull

yarn prisma db pull

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.

Your MySQL database is now migrated to Prisma Postgres and your Prisma project is configured.

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

datasource db {

  provider = "mysql"

  provider = "postgres"

}

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

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

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

bunx prisma db pull

pnpm prisma db pull

yarn prisma db pull

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.

Your MySQL database is now migrated to Prisma Postgres and your Prisma project is configured.

If you run into issues during the migration, ask on Discord or via X.