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
pgloadercommands. 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
Passwordssection in theSettingstab. - The
Connection stringssection lists the necessary parameters for connecting to your database:hostnamedatabase nameusernamepassword
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
Databasessection in the sidebar. - Click on
New Databaseand provide the same name as your PlanetScale database. Then, clickCreate.
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
pgloaderfollowing 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.loadin 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.
-
PGLoadercreates the migrated tables/views in a schema named the same as the original database. For example, if your PlanetScale database was namedsakila, you'd find the migrated tables in a schema namedsakilain 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.
PGLoaderdoes 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.
Top comments (0)