Migrating your data from PlanetScale to Neon
Tags: planetscale, neon, mysql, postgres
Published: 2024-03-11 15:19:02

---
title: Migrating your data from PlanetScale to Neon
published: true
tags: planetscale, neon, mysql, postgres
---

PlanetScale [recently announced](https://PlanetScale.com/blog/PlanetScale-forever) that they are sunsetting their free tier and will no longer be offering it to new users. For users exploring alternative database providers, [Neon](https://neon.tech) offers an appealing option, as a fully managed Postgres database.

Neon provides a generous [free tier](https://neon.tech/pricing), 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](https://pgloader.io/) 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](https://neon.tech). You can also visit their [documentation](https://neon.tech/docs/get-started-with-neon/signing-up) 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](https://neon.tech/pricing) for more details.

It would also be a good idea to review the [Pgloader MySQL to PostgreSQL Guide](https://pgloader.readthedocs.io/en/latest/ref/mysql.html) 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:

1. Log into your [PlanetScale dashboard](https://app.PlanetScale.com).
2. Select the database you want to migrate. From the database page, navigate to the the `Passwords` section in the `Settings` tab.
3. 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.

![PlanetScale console - credentials](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/6onfndxx6bab3nk19uxy.png)


To illustrate the migration workflow, I set up a database on PlanetScale using the [Sakila](https://dev.mysql.com/doc/sakila/en/sakila-installation.html) 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,

1. Log in to the [Neon console](https://console.neon.tech/).
2. Select your project and navigate to the `Databases` section in the sidebar.
3. 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:

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

![Neon console - credentials](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/cipa1dliaqf5l5r68cr7.png)

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:

```bash
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](https://neon.tech/docs/connect/connection-errors#d-specify-the-endpoint-id-in-the-password-field) 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.

1. Install `pgloader` following the instructions on [Installing pgloader](https://pgloader.readthedocs.io/en/latest/install.html) 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:

```bash
brew install pgloader
```

2. 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:

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

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

```plaintext
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:

```plaintext
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:

```shell
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.

```plaintext
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](https://neon.tech/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](https://neon.tech/docs/introduction/autoscaling) feature automatically scales your database to handle traffic spikes and high loads. While, [autosuspend](https://neon.tech/docs/introduction/auto-suspend) automatically suspends your database when it's not in use, saving compute costs.

- **Logical Replication**: Neon supports [logical replication](https://neon.tech/docs/introduction/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](https://neon.tech/docs) for more information on these features.

## References

- [PGLoader](https://pgloader.readthedocs.io/en/latest/install.html)
- [PGLoader MySQL to PostgreSQL Guide](https://pgloader.readthedocs.io/en/latest/ref/mysql.html)
- [Neon Documentation](https://neon.tech/docs)
- [PlanetScale Documentation](https://PlanetScale.com/docs)