Three Weeks with Planetscale and Prisma: A Hands-On Review

Barry Melton — Tue, 07 Feb 2023 18:13:00 +0000

Introduction

Hi. I'm a long-time lurker, first time poster. After a recent tech layoff, decided that I have time to test out some of my own projects and contribute more. I hope this post is well received.

Planetscale, What is It?

To understand Planetscale, which is a hosted Vitess-as-a-Service platform, you first need to understand Vitess.

Vitess is a fork of MySQL that (in simple terms) eliminates the parts of MySQL that make it hard to scale, like foreign keys, and adds tools like query rewriting and shard management that enable truly large scaling. You'll see it in use at places like Pinterest, Facebook, and Slack. Planetscale.

I'm building out a new service (Fancasting.com - don't judge it harshly, it isn't finished) between job hunting and thought I'd give it a try. Approximately two weeks in, I'm ready to share some thoughts.

Usage

So the obvious question I had when starting out was "Why would I use MySQL pretending to be Mongo when I could just use Mongo?" Well, there are a lot of good reasons for that. If you've got a ton of experience with MySQL, then there's practically no learning curve. In 26 years of development, approximately 20 of those years relied on MySQL as the backend data store, so this was an easy choice to me.

Another consideration is scaling -- both systems can massively scale, but in my experience with Mongo, scaling beyond toy app sizes introduces a lot of complexity, requiring skills I don't have. Vitess has similar complexity, but that's where Planetscale comes in. Their promise is to smooth out the scaling difficulties and make things "just work." I can't speak to the veracity of these claims, but considering their pricing model gets more expensive the more you use it, by the time you get to a meaningful stage of scaling, you're probably paying them enough that managed services are incentivized to be useful. I'll put a pin in that until I can say for sure, but I have reasons for confidence that it might be truthful. Time will tell.

There are some other nifty features, like automatic daily backups, painless migrations, local emulators, and things that I'll probably touch on later, but it is sufficient to say that working with Planetscale has been downright enjoyable, especially with Prisma.

Prisma

You're more likely to be acquainted with Prisma than Planetscale, but it is a rather nice [Type|Java]script ORM. If you're familiar with ORMs, you're mostly familiar with Prisma, and while I wouldn't put it on par with Django's ORM or SQLAlchemy, it has most of what you need in an ORM, and where it doesn't have feature completeness, allows you to shell out to raw SQL queries.

But the important bit is -- you remember those relationships that I mentioned before? The ones that Vitess (and by extension, Planetscale) eliminate? Well, Prisma gives them back via its @relation capabilities.

model Session {
  id           String   @id @default(cuid())
  sessionToken String   @unique
  expires      DateTime
  user         User?    @relation(fields: [userId], references: [id], onDelete: Cascade)
  userId       String?
}

When you eliminate relationships in the data layer, that means you either need to go the painful route of reimplementing them in code. I've done this in the past, and it isn't fun. Prisma allows you to create relational entities at the ORM level though, and for the features that it does support, that means not having to double-query two tables and manually reduce, or leverage the typical Mongo pattern of duplicating and nesting related data -- which is great, right up until a User that belongs_to a Comment updates their profile, which now requires updating every Comment object they've ever posted.

The Project

Altogether, my Project uses React via NextJS (both are latest versions, but I'm not yet using Next's App Pages), handles authentication via NextAuth and are hosted on Netlify which run Next's backend functions. The database is obviously Planetscale. NextAuth has a 'Prisma' adapter which will allow you to sync logins to the backend database using a backend function. I followed this article to get started, and extended into this article for some more of the finer points, but I'll distill the basics of what I've done.

Go sign up for Planetscale (I'm on the free plan for the moment, but you do you), then we'll assume you're adding this to an existing NextJS app:

Setting up Planetscale

I've done this on Debian via Scoop and Mac OSX, but installing the Planetscale CLI tool is pretty painless either way. For OSX it's

brew install planetscale/tap/pscale

From there, you should have a pscale command installed. If you want to look at your database, it's pscale shell <DATABASE_NAME> <BRANCH> -- the default branch is main.

Now let's install some dependencies.

yarn add -D prisma 
yarn add nanoid next-auth @next-auth/prisma-adapter @prisma/client

npx prisma init

Create a .env file and add an environment variable for your database:

DATABASE_URL="mysql://root@127.0.0.1:3309/YOUR-DB-NAME-HERE"

Create a schema.prisma file and add the database connection:

generator client {
  provider = "prisma-client-js"
  relationMode = "prisma"
}

datasource db {
  provider = "mysql"
  url = env("DATABASE_URL")
  relationMode = "prisma"
}

Note that the referentialIntegrity mode listed in the articles I linked earlier is deprecated. This is the bit that tells Prisma how to relate models to each other so that it can relate models that do not have table-level relationships.

For the sake of brevity, I'm going to assume you already have oAuth credentials for the providers you want to use, but this isn't an auth tutorial, so you if you don't, you can feel free to use NextAuth's Credentials provider to follow along.

Next, we'll set up our user account tables. Edit your schema.prisma file to add the following:

model Account {
  id                 String  @id @default(cuid())
  userId             String
  type               String
  provider           String
  providerAccountId  String
  refresh_token      String?  @db.Text
  access_token       String?  @db.Text
  expires_at         Int?
  token_type         String?
  scope              String?
  id_token           String?  @db.Text
  session_state      String?

  user User @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@unique([provider, providerAccountId])
}

model Session {
  id           String   @id @default(cuid())
  sessionToken String   @unique
  userId       String
  expires      DateTime
  user         User     @relation(fields: [userId], references: [id], onDelete: Cascade)
}

model User {
  id            String    @id @default(cuid())
  name          String?
  email         String?   @unique
  emailVerified DateTime?
  image         String?
  accounts      Account[]
  sessions      Session[]
}

model VerificationToken {
  identifier String
  token      String   @unique
  expires    DateTime

  @@unique([identifier, token])
}

Feel free to add values that you'd like to use here. If you're intending to have a 'nickname' or location or whatever other attribute on your User object, go ahead and add it now. Or add it later. Migration is super easy.

You'll see those relation fields -- that tells Prisma that (for example) the user on the Session object relates to a User object, and the fields and references attributes tell it how to map those relations.

Now that you've created some models and have some code that could work, let's run our first DB migration: npx prisma db push will run the SQL commands to create the database tables from your schema.prisma file.

Finish Setting up NextAuth

Create a new backend route at pages/api/auth/[...nextauth].js and add the following:

import NextAuth from "next-auth"
import GithubProvider from "next-auth/providers/github"
import { PrismaAdapter } from "@next-auth/prisma-adapter"
import { PrismaClient } from "@prisma/client"

export const authOptions = {
  // Tell NextAuth to use the Prisma adapter
  adapter: PrismaAdapter(prisma),

  // Configure one or more authentication providers
  providers: [
    GithubProvider({
      clientId: process.env.GITHUB_ID,
      clientSecret: process.env.GITHUB_SECRET,
    }),
    // ...add more providers here
  ],
  // This is optional, but I like my session object ID to match the UserID
  // for slightly more efficient ID checking. 
  callbacks: [
    session: async (session) => {
      session.id = session.user.id;
      session.user = session.user;
      return Promise.resolve(session);
    },
  ],
}

export default NextAuth(authOptions)

Note: Follow the Credentials instructions if you don't have or don't want to bother with oAuth.

Let's make the Session Provider object available to the entire application:

Edit your pages/_app.jsx to look like this:

import { SessionProvider } from "next-auth/react"
export default function App({
  Component,
  pageProps: { session, ...pageProps },
}) {
  return (
    <SessionProvider session={session}>
      <Component {...pageProps} />
    </SessionProvider>
  )
}

Finally, let's add a sign in button somewhere to the app. I placed mine in the Header.

Create a components/Header folder, then in components/Header/Header.jsx, let's add the following:

import React, { Suspense } from "react";
import Image from "next/image";
import Logo from "public/logo.svg";
import Link from "next/link";
import { useSession } from "next-auth/react";
import AvatarLoader from "components/Header/AvatarLoader";

export default function Header({ children, className = "" }) {
  const { data: session } = useSession();

  return (
    <div className={`${className}`}>
      <Bootstrap />
      <nav
        className={`container w-full py-4 mx-auto relative z-10 h-24 ${className}`}
      >
        <div className="relative w-full flex justify-center mb-4">
          <Link href="/">
            <Image
              id="logo"
              src={Logo}
              alt="Fancasting"
              height={32}
              width={32}
            />
          </Link>
          <Suspense fallback={<div />}>
            <AvatarLoader session={session} />
          </Suspense>
        </div>
      </nav>
      {children}
    </div>
  );
}

Now let's create the AvatarLoader component this expects to exist:

import React, { Suspense } from "react";
import { useSession, signIn } from "next-auth/react";
import Avatar from "components/User/AvatarMenu/Avatar";

function LoginButton() {
  return (
    <button
      onClick={() => signIn()}
      className="bg-brandOrange text-white px-8 py-2 rounded-md -top-1 right-1 absolute"
    >
      Sign in!
    </button>
  );
}

export default function AvatarLoader() {
  let session = useSession();

  if (session.status === "loading") return null;
  if (session.status === "unauthenticated") {
    return <LoginButton />;
  }

  if (session.status === "authenticated" && session.data.user) {
    return (
      <Suspense>
        <Avatar user={session.data.user} />
      </Suspense>
    );
  }
}

I won't get into the Avatar -- it's a simple tailwind circle that shows the user's profile picture, and has a menu in a floating div relative to it. This article is way long enough already.

The AvatarLoader does something I don't like, which is to manage its own state instead of receiving it via prop. I think this is an anti-pattern, but when playing around with prop drilling, I wasn't able to consistently get the session state from a parent component, and suffered either flashes of unstyled content, or component-swapping. I'd probably view this as a teaching opportunity of a junior dev had done this, and I should almost certainly revisit it later, but it works now, and gracefully waits until the auth session state is known and stable before deciding whether to render the LoginButton or the Avatar component, and doesn't suffer the usual problem with SPAs of showing one then switching to the other, which is a pet peeve of mine.

At this point, just add the Header to a page, and then you should have a functional app with working auth. If you click the "Login" button, you should see the NextAuth login screen, and logging in should result in a few new entries to your database. You can verify that success with:

pscale shell <DATABASE_NAME> main

then

SELECT id, name, image FROM User;

Conclusions

Okay, this went a lot longer than I expected it to, and turned into the auth tutorial I said that it wasn't. Some of the things I expected to cover in this article didn't quite make it past the editing room, so if there's interest, I intended to cover some of the more complicated relationships. On the Planetscale side, I intended to better cover migrations, and their super unique branching model. On the programming side, handling Likes on posts, relating Posts and Comments back to a user. How to aggregate and orderBy relations -- which were slightly more complex than I was used to. If you want to see those as a followup, put a like on this article!

On the whole, Prisma and Planetscale have been absolutely delightful to work with. And while Prisma has thrown a couple of wrenches at me (nested relations are syntactically ... not terse), I've been able to make a ton of progress in a short amount of time, despite using a new-to-me toolkit, which I usually advise against.

As far as use cases, I think Planetscale will do well at the tasks I've given it (storing and retrieving data in normal CRUD-type retrieval patterns is what it was built to do) -- but while I am sure that it will more than happily handle parsing data for purposes of analytics (aggregating submitted posts by popularity, or querying for things that have high interaction this day/week/month) I have some slight concerns that the pricing model will be cost prohibitive for a service I do not ever expect to turn a profit.

If there were a more direct profit motive to my app, then I'd argue that the increase in productivity almost certainly makes Planetscale a good choice. As it stands, if the service ever becomes "too popular to afford" on Planetscale, I can down-migrate to Vitess directly and host it for a more fixed-cost operation, but will likely miss a lot of its developer-friendly features.

Until next time, thanks for reading!

Forem: Barry Melton