From Firebase/Redis to MySQL with PlanetScale

September 6, 2021 (3y ago)

A few weeks ago, I moved this site from using Firebase and Redis to PlanetScale. PlanetScale is a serverless database platform built on MySQL and Vitess. Here's why I picked PlanetScale:

  • Database Branching: I'm able to use my database with the same mental model as git. For each change to my database schema, I open a deploy request. Then, I can merge those changes back into my main database branch.
  • Prisma Support: In combination with Prisma, it's so easy to handle database migrations. I haven't moved to Prisma (yet) but will soon.
  • Connectionless: Since PlanetScale is serverless, it's built to withstand thousands of simultaneous connections. You can almost consider this connectionless – the developer doesn't need to worry about pooling or other common objections.
  • Insanely Fast: After testing 13 databases, PlanetScale was the fastest to deploy. Now, after using it for a few weeks and monitoring performance, I'm seeing APIs resolve in ~150ms on average (see results below).

I had a chance to talk with Nick from PlanetScale on a stream last month if you want to see a live demo of the workflow.

Why Firebase and Redis?

This site previously used Firebase and Redis for real-time blog post views and my guestbook. The primary decision for choosing these technologies was to learn. I learn best by experimenting and building. I even created a course showing how to use Firebase with Next.js, if you're interested.

Suhail
Suhail
@Suhail
·Follow

Often with engineering, the best way I learn something is to build something. Even if it’s a silly little toy. Toys allow you to go pretty deep to learn the subtly, limitations, and complexity of field to a decent extent.

688
Reply
Read 7 replies

Both Firebase and Redis (through Upstash) are easy to get started, scale without thinking, and work great in serverless environments. However, I wanted to move to something SQL-based (either MySQL or PostgreSQL) for a new learning experience. I've previously written about choosing your backend and landed on PlanetScale.

Rebuilding SQL

I found myself writing JavaScript in places where SQL has the functionality built-in. For example, I used Firebase to fetch a JSON object views and then summed the values together to find the total number of views.

const snapshot = await db.ref('views').once('value');
const views = snapshot.val();
const allViews = Object.values(views).reduce((total, value) => total + value);

With SQL, you can use SUM() instead. Easier? Yeah. Better? Probably debatable, but I like it.

SELECT SUM(count) as total
FROM views;

Same thing with sorting. Before I was using JavaScript sort and now I can use ORDER BY. Firebase does have something similar, I just wasn't using it.

SELECT * FROM guestbook
ORDER BY updated_at DESC;

Why MySQL and PlanetScale?

SQL is boring, proven technology. It's been around for years and will continue to be around for years. I've used it before in prior jobs, but still felt I could understand it better. I also enjoy using PostgreSQL (and recommend Supabase) and strongly considered that solution as well. I'm planning to build a different project with Supabase soon.

I'm also a strong believer in using tools I recommend. Without actually getting my hands dirty in code and running an application in production, it's hard to confidently recommend a product to others. After using the PlanetScale Vercel Integration, I was blown away. I could deploy an entire fullstack application in a few clicks. And as I mentioned at the start, database migrations aligned with my mental model.

Migrating Existing Data

There are likely better ways to do this, but my hacky solution to migrate data into PlanetScale was as follows:

  • Export JSON data from my Firebase Realtime Database

    Exporting data from Firebase

  • Export JSON data from my Redis cluster using HVALS and TablePlus

    Exporting data from my Redis cluster

  • Create a new database branch in PlanetScale for the schema migration adding the tables

  • Create two new API routes, which loaded the JSON data and INSERT INTO MySQL

  • Verify on the database branch the APIs correctly process and migrate the data

  • Create a deploy request with the new schema changes and merge it into main

  • Finally, hit the APIs to migrate the JSON data into main

  • Done!

Here are the two scripts I used, for reference.

import db from 'lib/planetscale';
import guestbookData from 'data/guestbook';

export default async function handler(req, res) {
  const toISOString = (unixTimestampInMs) =>
    new Date(unixTimestampInMs).toJSON().slice(0, 19).replace('T', ' ');

  let query = `INSERT INTO guestbook (email, updated_at, body, created_by)
    VALUES `;

  const escapeStr = (str) =>
    str
      .replace(/\\/g, '\\\\')
      .replace(/\$/g, '\\$')
      .replace(/'/g, "\\'")
      .replace(/"/g, '\\"');

  guestbookData.forEach((item, key) => {
    var value = JSON.parse(item['value']);

    query += `("${
      value.email ? `${value.email}` : 'not@provided.com'
    }", "${toISOString(value.updated_at)}", "${escapeStr(value.body)}", "${
      value.created_by
    }")`;

    if (key === guestbookData.length - 1) {
      query += ';';
    } else {
      query += ', ';
    }
  });

  const [rows] = await db.query(query);

  return res.status(201).json(rows[0]);
}
import db from 'lib/planetscale';
import viewsData from 'data/views';

export default async function handler(req, res) {
  let query = `INSERT INTO views (slug, count)
    VALUES `;

  const slugs = Object.keys(viewsData['views']);

  slugs.forEach((slug, key) => {
    const count = viewsData['views'][slug];

    query += `("${slug}", ${count})`;

    if (key === slugs.length - 1) {
      query += ';';
    } else {
      query += ', ';
    }
  });

  const [rows] = await db.query(query);

  return res.status(201).json(rows[0]);
}

Here's my PlanetScale schema for tracking post views and guestbook entries.

CREATE TABLE `views` (
  `slug` varchar(128) NOT NULL,
  `count` bigint NOT NULL DEFAULT '1',
  PRIMARY KEY (`slug`)
)

CREATE TABLE `guestbook` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `email` varchar(256) NOT NULL,
  `body` varchar(500) NOT NULL,
  `created_by` varchar(256) NOT NULL,
  `created_at` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `updated_at` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`id`)
)

Results

I've been monitoring the performance of my production APIs connecting to PlanetScale with Checkly. Checkly allows me to set alerts if there's downtime or if performance decreases below my accepted threshold. For the last week, I've seen latency of ~150ms for my Next.js API Routes, deployed as serverless functions on Vercel in us-east.

Checkly results from PlanetScale database

This has been significantly faster than my previous Firebase implementation. Can you tell when I made the switch? 😁

Checkly results after switching from Firebase to PlanetScale

I'm pleased with the results of the migration. Having one service instead of two cleaned up the code and required fewer environment variables to connect to each service. I was also somewhat abusing Redis for my guestbook, where SQL is a better fit.

Next up, Prisma!