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.
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.
Run a Database Dump
Now that you're proxying the database, it's time to export your data:
mysqldump -u root -h 127.0.0.1 -P 3306 --protocol=tcp --set-gtid-purged=OFF --skip-add-locks --skip-lock-tables --skip-comments {TABLE_NAME} > dump_mysql.sqlMake sure to replace {TABLE_NAME} with the actual name of your table.
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.
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.
Install and Login to Turso CLI
With your SQL file ready, install the Turso CLI by following their guide and logging in.
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.
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.