Migrating your data from PlanetScale to Neon

Ishan Anand — Mon, 11 Mar 2024 15:19:02 +0000

PlanetScale recently announced that they are sunsetting their free tier and will no longer be offering it to new users. For users exploring alternative database providers, Neon offers an appealing option, as a fully managed Postgres database.

Neon provides a generous free tier, making it a good fit for small applications and hobby projects. While not a one-to-one replacement (PlanetScale, notably, is a MySQL-compatible database, while Neon is a PostgresQL provider), Neon offers a similar serverless experience and is a reasonable alternative for most use cases.

This guide will walk you through the process of migrating your data from PlanetScale to Neon. Considering both MySQL and Postgres follow the relational database model, for most applications, the migration process is relatively straightforward.

We'll use the PGLoader utility to migrate the data from PlanetScale to Neon. It is an open-source tool that can migrate data from various sources to Postgres, handling data type conversions and other necessary adjustments.

Note that there might be other inconsistencies depending on the exact set of MySQL features that your application relies on and if there is a Postgres equivalent. These can be addressed during the migration process or adapting the application to Postgres after the migration.

Before you begin

Ensure you have the following prerequisites:

A Neon account. If you do not have one, sign up at Neon. You can also visit their documentation page for more details.
A PlanetScale account and an existing MySQL database there. Presumably, else why'd you be reading this guide :)
A terminal or command-line interface to run pgloader commands. I'd recommend using a unix-based system, such as macOS or Linux, for a simpler setup.

Note that Neon's free tier supports databases up to 500 MiB of data, and larger datasets require upgrading to a paid plan. If your dataset is above this size, check out Neon's pricing plans for more details.

It would also be a good idea to review the Pgloader MySQL to PostgreSQL Guide to better understand the adjustments needed for your migration and resolve any show-stopping issues.

Fetch your PlanetScale credentials

To migrate data from your PlanetScale database, pgloader needs to authenticate with it. To gather the credentials:

Log into your PlanetScale dashboard.
Select the database you want to migrate. From the database page, navigate to the the Passwords section in the Settings tab.
The Connection strings section lists the necessary parameters for connecting to your database:
- hostname
- database name
- username
- password

Copy these details for configuring pgloader later.

To illustrate the migration workflow, I set up a database on PlanetScale using the Sakila dataset. It is a well-known MySQL test database containing tables and views representing the records for a DVD rental store, making it useful for validating the migration steps.

Set up your Neon project

Create a Neon database

When signing up, Neon prompts you to create a new project. Once you've created a project, a ready-to-use database called neondb is automatically created for you.

However, when migrating, you need to create a new database with the same name as your existing PlanetScale database. To create a new database,

Log in to the Neon console.
Select your project and navigate to the Databases section in the sidebar.
Click on New Database and provide the same name as your PlanetScale database. Then, click Create.

Fetch your Neon credentials

Once again, select your project from the console, and navigate to the Connection Details section in the dashboard. Select the database you want to use for migration from the dropdown menu. Now you can find the Postgres connection details for your Neon database. It will resemble:



postgres://[username]:[password]@[your-endpoint-id].us-east-2.aws.neon.tech/[dbname]?sslmode=require

Adjust this string by including your endpoint ID with your password. This is a necessary step to ensure certain older Postgres drivers can still connect to Neon. The modified connection string will look like:



postgres://[username]:endpoint=[your-endpoint-id];[password]@[your-endpoint-id].us-east-2.aws.neon.tech/[dbname]?sslmode=require

This workaround ensures compatibility with pgloader. Refer to the connection workaround section of the Neon documentation for more details.

Keep this connection string handy for configuring pgloader later.

Using PGLoader

Set Up PGLoader

We'll use the pgloader tool to migrate the data from PlanetScale to Neon, converting MySQL data formats to Postgres-compatible ones and streaming data directly into your Neon database.

Install pgloader following the instructions on Installing pgloader and depending on your operating system. It lists multiple methods, including Docker, Homebrew for macOS, Debian (apt), and RPM packages.

If you're using a macOS machine, using Homebrew is the simplest method:



brew install pgloader

Create a configuration file named config.load in the directory where you want to run the migration. This file will contain details to connect to both the source and the destination database. It needs to be in the following format:



load database
  from <source-connection-string>
  into <destination-connection-string>;

Using the credentials retrieved before from PlanetScale, we can create a source connection string.



mysql://[username]:[password]@[hostname]/[database name]?sslmode=require

Replace the hostname, database name, username, and password with the actual values from your PlanetScale database. For the destination, we can copy over the Neon connection string we fetched and modified earlier, which looks like:



postgres://[username]:endpoint=[your-endpoint-id];[password]@[your-endpoint-id].us-east-2.aws.neon.tech/[dbname]?sslmode=require

We are now good to go with the configuration file. Note the trailing semicolon following the postgres connection string. This is important for pgloader to know where the configuration ends.

Run the Migration

With pgloader set up, initiate the migration by running the following command in your terminal:



pgloader config.load

Monitor the output for any errors or messages indicating the migration's progress. PGLoader doesn't stream logs to the terminal as it migrates the data, so you may need to wait for the process to finish.

Once finished, you'd see a report summarizing the migration like the one below, including num of rows migrated, time taken, and count of errors encountered.



LOG report summary reset

             table name     errors       rows      bytes      total time



    fetch meta data          0         80                     2.204s
     Create Schemas          0          0                     0.491s
   Create SQL Types          0          2                     1.723s
      Create tables          0         32                    11.624s
     Set Table OIDs          0         16                     0.133s





      sakila.rental          0      16044     1.2 MB          2.626s
     sakila.payment          0      16044   962.6 kB          2.408s
  sakila.film_actor          0       5462   146.0 kB          1.948s
   sakila.inventory          0       4581   137.1 kB          2.356s
        sakila.film          0       1000   190.9 kB          2.814s



sakila.film_category          0       1000    25.7 kB          2.087s

            sakila.city          0        600    21.4 kB          2.547s

         sakila.address          0        603    57.7 kB          2.957s

        sakila.customer          0        599    57.6 kB          3.036s

           sakila.actor          0        200     7.2 kB          3.085s

         sakila.country          0        109     3.5 kB          3.346s

        sakila.category          0         16     0.5 kB          3.481s

        sakila.language          0          6     0.2 kB          3.519s

           sakila.staff          0          2    71.2 kB          3.022s

           sakila.store          0          2     0.1 kB          2.654s

       sakila.film_text          0          0                     2.791s



COPY Threads Completion          0          4                    11.057s

         Create Indexes          0         42                    16.047s

 Index Build Completion          0         42                     4.386s

        Reset Sequences          0         13                     1.604s

           Primary Keys          0         16                     5.784s

    Create Foreign Keys          0         22                     8.018s

        Create Triggers          0          0                     0.241s

        Set Search Path          0          1                     0.611s

       Install Comments          0          0                     0.000s



  Total import time          ✓      46268     2.8 MB         47.748s

Verify the Migration

Navigate to your project from the Neon console and go to the SQL Editor tab. Run a few queries to verify that the tables/views from your PlanetScale database have been migrated to Neon. A couple points to note:

Make sure the database selected in the SQL editor is the one you migrated the data to. You can change the database by clicking on the dropdown at the top of the SQL editor.
PGLoader creates the migrated tables/views in a schema named the same as the original database. For example, if your PlanetScale database was named sakila, you'd find the migrated tables in a schema named sakila in your Neon database.

Things to consider post-migration

Differences between MySQL and Postgres

There are some differences between MySQL and Postgres that you'd need to be cognizant of after switching your application database:

Data Type Incompatibilities: MySQL and Postgres have differences in data types that may require manual adjustments. PGLoader does a best-effort job of mapping data types, but you may need to review and modify the schema post-migration.
Indexing Differences: Postgres offers a wider variety of indexing options. Review your indexes for optimization opportunities post-migration.
Case Sensitivity: Postgres and MySQL have different default case-sensitivity behavior for string/text columns. This can affect queries and data retrieval unless accounted for in your application code or database schema.

Neon Postgres features

Neon offers multiple features on top of Postgres, both to manage your database and simplify your app development workflows. After migrating to Neon as your application database, you should leverage these.

Branching: Neon supports branching, that allows you to create a copy of your database for testing and development purposes. This is useful for testing schema/data changes to your application without affecting your production database.
Autoscaling and Autosuspend: Neon's autoscaling feature automatically scales your database to handle traffic spikes and high loads. While, autosuspend automatically suspends your database when it's not in use, saving compute costs.
Logical Replication: Neon supports logical replication that allows you to replicate data from your Neon database to other destinations in near real-time. This is useful for syncing data across multiple databases, analytics and other operational use-cases.

Note that some of these features make sense only for production use-cases and hence need a paid plan to use. You can refer to the Neon documentation for more information on these features.

DEV Community: Ishan Anand