DEV Community: Shane Walker

Migrate to Turso From PlanetScale

Shane Walker — Fri, 08 Mar 2024 22:16:10 +0000

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)`


![the error message on the migration conversion](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fuvqkq9965a85t2tm1q9.png)

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.


![migrated users](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zdoovivoxiybv4l1apjd.png)

Amplify, React and Typescript

Shane Walker — Sun, 20 Feb 2022 20:23:02 +0000

Recently I wanted to start a project with an aws backend. I wanted to use typescript and create-react-app. The guide on aws is all in vanilla react, that won't do and in searching around I couldn't find a good example so I struggled through so you don't have to.

The AWS guide can be followed up until connecting the front end. In the example I went with blogs instead of ToDo's, my project was closer inline to blogs so it just made sense, you can choose whatever data type you would like.

During the configuration, make sure that you select typescript where you can. This will make codegen create all the types you need for your front-end.

The base project is created with npx create-react-app --template typescript instead of the default create-react-app template.
The updated App.tsx looks like this, we'll break it down afterwards.

import React, { useState, useEffect } from "react";
import Amplify, { API, graphqlOperation } from "aws-amplify";
import { createBlog } from "./graphql/mutations";
import { listBlogs } from "./graphql/queries";

import awsExports from "./aws-exports";
import { ListBlogsQuery } from "./API";

Amplify.configure(awsExports);
const initialState = { name: "", body: "" };

const App = () => {
  const [formState, setFormState] = useState(initialState);
  const [blogs, setBlogs] = useState<ListBlogsQuery>();

  useEffect(() => {
    fetchBlogs()
  }, []);

  const handleInputChange = (event: React.ChangeEvent<HTMLInputElement>) => {
    setFormState({ ...formState, [event.target.name]: event.target.value });
  };

  const fetchBlogs = async () => {
    try {
      const blogData = (await API.graphql(graphqlOperation(listBlogs))) as {
        data: ListBlogsQuery
      }
      setBlogs(blogData.data);
    } catch (err) {
      console.log("Error fetching blogs" + err);
    }
  };
  const addBlog = async () => {
    try {
      if (!formState.name || !formState.body) return;
      const blog = { ...formState };
      if (blogs) {
        await API.graphql(graphqlOperation(createBlog, { input: blog }));
        await fetchBlogs();
        setFormState(initialState);
      }
    } catch (err) {
      console.log("error creating blog: ", err);
    }
  };

  return (
    <div id="wrapper" style={styles.container}>
      <h2>Amplify Todos</h2>
      <input
        onChange={handleInputChange}
        name="name"
        style={styles.input}
        value={formState.name}
        placeholder="Name"
      />
      <input
        onChange={handleInputChange}
        name="body"
        style={styles.input}
        value={formState.body}
        placeholder="Type your blog..."
      />
      <button style={styles.button} onClick={addBlog}>
        Create Blog
      </button>
      {blogs &&
        blogs?.listBlogs?.items?.map((blog, index) => {
          return (
            <div key={blog?.id || index} style={styles.todo}>
              <p style={styles.todoName}>{blog?.name}</p>
              <p style={styles.todoDescription}>{blog?.body}</p>
            </div>
          );
        })}
    </div>
  );
};
const styles = {
  container: {
    width: 400,
    margin: "0 auto",
    display: "flex",
    //real weird issue: https://github.com/cssinjs/jss/issues/1344
    flexDirection: "column" as "column",
    justifyContent: "center",
    padding: 20,
  },
  todo: { marginBottom: 15 },
  input: {
    border: "none",
    backgroundColor: "#ddd",
    marginBottom: 10,
    padding: 8,
    fontSize: 18,
  },
  todoName: { fontSize: 20, fontWeight: "bold" },
  todoDescription: { marginBottom: 0 },
  button: {
    backgroundColor: "black",
    color: "white",
    outline: "none",
    fontSize: 18,
    padding: "12px 0px",
  },
};

export default App;

Imports are pretty straight forward, we'll be using graphql and we need to import the queries and mutations that we'll use to fetch and update our blogs.

This bit is pretty important. This holds the state of the form we use to create new blogs, fairly common. The next line is holding the blogs that we fetch from our backend, note the type notation is a ListBlogsQuery this is a type that is created for us by codegen when we push out backend to aws. The useEffect fetches our posts on page load, again pretty standard stuff.

  const [formState, setFormState] = useState(initialState);
  const [blogs, setBlogs] = useState<ListBlogsQuery>();

  useEffect(() => {
    fetchPosts()
  }, []);

The fetchBlogs and addBlog methods were tricky for me, I had never used the as {data: <Type>} syntax so it took me a minute to fully understand what I was doing.

const fetchBlogs = async () => {
    try {
      const blogData = (await API.graphql(graphqlOperation(listBlogs))) as {
        data: ListBlogsQuery
      }
      setBlogs(blogData.data);
    } catch (err) {
      console.log("Error fetching blogs" + err);
    }
  };
  const addBlog = async () => {
    try {
      if (!formState.name || !formState.body) return;
      const blog = { ...formState };
      if (blogs) {
        await API.graphql(graphqlOperation(createBlog, { input: blog }));
        await fetchBlogs();
        setFormState(initialState);
      }
    } catch (err) {
      console.log("error creating blog: ", err);
    }
  };

I'm not going to cover the rest of the content, as its pretty standard react stuff. One note is the super weird issue I ran into with flexDirection: 'column' If you note in my styles there is a link in a comment to explain more.

Anyway thats about it, I'm happy with it and I hope someone else gets some use out of it.