Software Development

Published on Mar 29, 2024

Migrating from PlanetScale to Turso DB

Step-by-step guide on how to switch from PlanetScale's MySQL database to Turso DB, an SQLite-based database management system

planetscale to turso
Step 1

Install and Login to PlanetScale CLI

To begin, ensure you have the PlanetScale CLI installed. Once you have it set up, log in to your account to proceed. This tool is essential for the migration process as it will allow you to interact directly with your PlanetScale databases from the command line. PlanetScale docs.

Step 2

Connect to PlanetScale via CLI

Next, set up a connection to your PlanetScale database using the CLI. Execute the following command:

pscale connect <DATABASE_NAME> <BRANCH_NAME>

This will set up a proxy for your database, allowing local access. PlanetScale docs.

Step 3

Run a Database Dump

Now that you're proxying the database, it's time to export your data:

mysqldump -u root -h -P 3306 --protocol=tcp --set-gtid-purged=OFF --skip-add-locks --skip-lock-tables --skip-comments {TABLE_NAME} > dump_mysql.sql

Make sure to replace {TABLE_NAME} with the actual name of your table.

Step 4

Edit the SQL File for Compatibility

Within the SQL dump file, some attributes may not be compatible with SQLite3, such as CURRENT_TIMESTAMP(3) and datetime(3). Replace any occurrences with CURRENT_TIMESTAMP and datetime respectively, removing the (3) wherever it appears to ensure compatibility with Turso DB.

Step 5

Download and Use mysql2sqlite

Download the mysql2sqlite conversion script available on GitHub to convert your MySQL dump to an SQLite format.

After downloading the script, execute the following:

./mysql2sqlite dump_mysql.sql | sqlite3 dump_sqlite3.sql

Place your `dump_mysql.sql` file in the same directory as the `mysql2sqlite` script before running this command. Once done, you should find the `dump_sqlite3.sql` file in your folder.

Step 6

Install and Login to Turso CLI

With your SQL file ready, install the Turso CLI by following their guide and logging in.

Step 7

Create a New Database in Turso

Create a new database in Turso with the following command, which will import your SQL file:

turso db create <YOUR_DATABASE_NAME> --from-file dump_sqlite3.sql

When naming your database, I'd suggest you use kebab-case as the name ends up getting used as part of a URL that Turso generates.

Step 8

Verify the Migration

To verify all your data has been migrated correctly, you can connect to your database and query one of your tables:

turso db shell <YOUR_DATABASE_NAME>

Perform a quick check by querying one of your tables:

select * from <SOME_TABLE_YOU_HAVE>;

Common Challenges

Here are potential issues you might face, alongside solutions:

  • Datetime Stamp Complications: If you see (3) appended to datetime or current_timestamp fields in your SQL dump, follow Step 4 to amend these to a Turso DB-compatible format.

  • Integration with Prisma: For those utilizing Prisma, set your datasource db URL precisely as "file:./dev.db". Prisma documentation.

  • Community Assistance: If you run into any issues, feel free to reach out to the Turso community Discord, the users are generally very responsive and helpful.

  • Database Viewing Tools: Turso DB may not be supported by many database viewers yet, however, Turso provides its own built-in viewer to compensate for this gap.


Frequently Asked Questions

Let us handle your software.

Contact us today to discuss your project in detail. Whether you're looking to migrate your database or start from scratch. We have the experience and skills to make it happen.

Get in touch.