I'd like to start by saying this isn't a roast piece of Planetscale. They have a great product and it has great value. They've come to collect and the freeloader (me namely) is bailing. Because the only thing smaller then my budget is...nevermind.
So after building out my entire platform on Planetscale and being really happy with where I'm at, its time for a database technology migration. I want to go over exactly how I did it, and maybe save some other devs some pain.
I also would have been a lost puppy without the guidance of the folks at Turso, specifically this blog post was a HUGE help. Couldn't have done it without it.
First thing is first, how do I get all my data, including nearly 5 test users, moved?
before you have a panic attack, these are real passwords but no longer valid, I kept them intact so folks would know exactly what values to put in which commands.
In planet scale on the db you want to migrate, click the "Connect" button and from the dropdown select the MySQL CLI option.
this will give you the following mysql connection string
Connection string from planet scale
mysql -h aws.connect.psdb.cloud -u 09cwzu3sp84ip5szglam -ppscale_pw_e3WXHkAOI0pHYfmNRrczDXwc6TOu25WXrsFi5JwRWQ5 --ssl-mode=VERIFY_IDENTITY --ssl-ca=/etc/ssl/cert.pem
If you did not just generate a password, the password will be redacted, you can create a new password to get a connection string with the exposed password.
You can run this command (your version of it) to make sure you have a good password and can actually connect. You should be prompted with a mySQL shell
with the values from the connection string, you should be able to call mysqldump, and map the appropriate values over from the connection command, to the dump command. -h host, -u user, -p password. I'm by no means an expert, the ssl flags are in the connection string, so I moved them over to the dump string too. I'm not sure if they are valid or even did anything. Didn't try it without them. --ssl-mode --ssl-ca=/path/to/cert.pem
NOTE: the lack of space between the -p flag and the actual password, fun necessary stuff.
Dumping the database
mysqldump -u 09cwzu3sp84ip5szglam -p'pscale_pw_e3WXHkAOI0pHYfmNRrczDXwc6TOu25WXrsFi5JwRWQ5' -h aws.connect.psdb.cloud --ssl-mode=VERIFY_IDENTITY --ssl-ca=/etc/ssl/cert.pem --skip-extended-insert --compact "db-name" > db_dump.sql
Make sure to replace db-name with the name if your database in planetscale. You can adjust the name of the output file too, make sure to update it everywhere if you change it though.
Now head over to this great script the db gods (dumblob) gifted us.
git clone https://github.com/dumblob/mysql2sqlite.git && cd mysql2sqlite`
```
from this directory I call the script, point it to my mysql dump `db_dump.sql` and pipe the output into sqlite3.
### Converting the dump to sqlite
```shell
./mysql2sqlite ~/path/to/dump/db_dump.sql | sqlite3 ~/path/to/new/file/dump-in-sqlite.db
```
Did it work? Thats really cool for you, mine didn't work the first time. In my initial uhh migration? conversion? Anyway it just didn't work right. So I looked at the output and saw I had a problem with `DATETIME(3)`
I opened up the `db_dump.sql` file, found that line, and changed the `current_timestap(3)` to just `CURRENT_TIMESTAMP`
Not really sure why the dump generated a (3) at the end I just removed it. You might have different errors, but you just have to work through them. Once I found an error, I did a search and replace to fix it in the whole file. This was the only error I really had, I assume your milage may vary.
### Getting both my users into Turso
Make sure you have the [Turso CLI](https://docs.turso.tech/cli/introduction) installed and configured.
Now lets make a turso db and just use that file as the starting point.
```shell
turso db create new-db-name --from-file /path/to/dump-in-sqlite.db
```
E.Z.
```shell
turso db shell new-db-name
```
```shell
select * from Users;
```
There they are, in all their glory. Hero's among men, my entire user base.
Top comments (0)