Comprehensive Guide: Migrating Databases from PlanetScale to Turso

As developers, adapting to new tools and environments is a natural part of how we work. This adaptability is crucial when external factors, such as service changes, necessitate a shift in our database solutions. One such instance is the discontinuation of PlanetScale's hobby plan, prompting a search for new database homes. Fortunately, the journey from one database system to another doesn't have to be arduous.

This tutorial will guide you through a straightforward process to migrate your MySQL database to Turso, utilizing a few key tools to make the transition smooth and efficient. Let's dive into how this can be achieved in just a few steps.

Preparation and Export from PlanetScale

1. Install and Login to PlanetScale CLI

• Purpose: To export your database from PlanetScale.

• Action:

    pscale auth login
  

  This command opens a browser for login.

2. Export Database from PlanetScale

• Purpose: To obtain SQL dump files containing your database's schema and data.

• Action:

    pscale db dump your_database_name branch_name
    cd path/to/dump
  

Setup MySQL for Intermediate Processing

3. Ensure MySQL Service is Running

• Purpose: MySQL serves as an intermediate step for preparing your data.

• Action: Ensure MySQL is installed and running. Authenticate with:

    mysql -u root
  

4. Create a New MySQL Database

• Purpose: To consolidate your exported data.

• Action:

    CREATE DATABASE your_new_database;
    EXIT;
  

5. Import Dump Files into MySQL

• Purpose: To prepare a single consolidated SQL file for conversion.

• Action:

    mysql -u root -p your_new_database < dump_file.sql
  

Modify MySQL Dump for SQLite Compatibility

6. Modify the Combined MySQL Dump File

• Purpose: To ensure compatibility with SQLite.

• Action: Combine schema and data SQL files into combined_mysql_dump.sql. Then:

  • Replace datetime(3) with DATETIME

  • Replace current_timestamp(3) with CURRENT_TIMESTAMP

  • Save the modified file.

Convert MySQL to SQLite

7. Setup mysql2sqlite

• Purpose: Facilitates the conversion to a SQLite compatible format.

• Action:

    chmod +x mysql2sqlite
  

8. Convert and Create SQLite Database

• Purpose: To convert the MySQL dump for Turso.

• Action:

    ./mysql2sqlite combined_mysql_dump.sql | sqlite3 new_database.db
  

Importing into Turso

9. Import SQLite Database into Turso

• Purpose: To complete the migration.

• Action:

    turso db create your_turso_database_name --from-file path/to/your_sqlite.db
  

Verification and Cleanup

10. Verify Data Integrity in Turso

• Purpose: To ensure successful migration.

• Action: Use Turso's tools or SQL commands to inspect the imported data.

11. Cleanup

• Purpose: To maintain a clean environment.

• Action: Remove any temporary databases and files used during the process.

Key Considerations

• Always keep backups before starting the migration process.

• Review modifications to prevent unintended alterations.

• Test the modified dump in MySQL and SQLite before the final migration.