⟵ PlanetScale Blog

How to set up a scalable, stateful web app in 10 minutes

August 7, 2020
Bill Gordon

If you are developing a stateful web app, you want to reduce the time and effort you spend deploying infrastructure so you can focus on building your app. At the same time, you don’t want to build your app on a stack that won’t scale as your application grows. One way to hit the ground running is to pair a rapid app deployment solution with a scalable SQL database, allowing you to iterate quickly with the knowledge that your storage will grow with your data.


This tutorial will introduce two major products/technologies:

  • PlanetScaleDB: A highly scalable MySQL database SaaS solution that is simple to spin up and use.
  • Vercel: A product that allows you to get a stateless Jamstack application up and running in minutes.

PlanetScaleDB and Vercel complement each other very well. Vercel provides a deployment environment that solves global caching, routing and backward compatibility for frontends. PlanetScaleDB provides a MySQL compatible, horizontally scalable and globally distributed persistence layer. Together Vercel and PlanetScaleDB offer you the ability to start small with your application and grow to massive global scale. 

In this demo, we will not create a globally distributed database, but PlanetScaleDB allows you to distribute master and replicas for a database all around the globe (across 12 regions in 3 cloud providers) with just a few clicks when you need it.

Both products provide free trials to get you up and running risk free.

Goals

  1. Set up a database using PlanetScaleDB.
  2. Set up a web app using Next.js on Vercel.
  3. Write a simple serverless function in Node.js that connects our Next.js app to our PlanetScaleDB database.

Getting started

Setting up PlanetScaleDB

Let’s get the persistent database set up.

  1. Navigate to http://console.planetscale.com
  2. Sign up and create an account.
  3. Create a cluster. During cluster creation you need to provide an admin username and password for your cluster.  Please keep your credentials safe, you will need them to connect.
  4. Inside that cluster, create a database. PlanetScaleDB’s free credits allows you to create a small database risk-free.  I’d recommend using the default values for resources for now, however you can always scale up the resources later if your app takes off!
  5. After creating the database, it will begin to deploy. This may take up to 10 minutes—a perfect opportunity to set up your Vercel project!
  6. By default, PlanetScaleDB will protect your database by only allowing the IP address of the user who created the cluster to connect.  Since we will be connecting from a different IP address, we will need to disable this.  From the Cluster page, navigate to the Security tab, then IP Access Management.  From here, edit the access list and remove your IP.  An empty access list means anyone can now connect with the credentials.

Setting up Vercel

Now let’s get the web application up and running.

  1. Navigate to https://vercel.com and sign up.
  2. Click Import -> Import Git Repository.
  3. To speed things along we have built a very simple Next.js app using a Vercel template already which will connect to your PlanetScaleDB database.  Provide the following url when prompted to import a git repository: https://github.com/planetscale/10-minute-app
  4. Continuing following the import prompts. Feel free to change any settings you need to but I just used defaults.
  5. After just a few short minutes Vercel will have cloned the repo and deployed it! You can visit the web app that was completed.  You will notice a red message saying “Database Connection Failed”.  No worries!  We will not get our app connected to our database.

Set up database connection details in environment variables

There are many ways you could do this. I will be leveraging Vercel’s environment variables to set the database connection string details so that our app can securely connect to the database.

1. The first step is to check back in on your PlanetScaleDB database and get the connection string details. By the time Vercel has finished deploying your database should also be ready. If not, just give it a few more minutes.  Navigate either to the Clusters or Database page for the database you created.  You will notice a green Connect button in the top. Right click it to display your connection string details. You will be using these values to set up your environmental variables. Need more help? Follow this guide.

2. In the Vercel project dashboard, navigate to Settings -> Environment Variables. For the purposes of this guide, I will be setting environment variables only for the production environment, but be sure to set them for your other environments as needed.  Read more about environment variables here.

# Here is an example connection string from PlanetScaleDB
mysql --host somehost --port 3306 --user someuser


I am converting this connection string to the following environment variables.  The username and password are from the cluster creation step above.

MYSQL_USER=user_from_cluster_create
MYSQL_PASSWORD=somepassword
MYSQL_HOST=somehost
MYSQL_PORT=password_from_cluster_create


3. After setting your environmental variables, you will need to redeploy your vercel app which you can find in a hamburger button on the Overview tab.

Testing the App

Vercel has now redeployed your app. You should be able to visit it once again and see a green success message indicating the connection to the database is successful.  Feel free to open the network tab in your browsers dev tools and investigate the request that tests the connection.

Making the connection

We recommend digging into the source code in the Github repo. At a high level we have created a serverless functions to establish a MySQL connection and run a trivial SELECT statement against our database.  Here is a snippet of our function:

// pages/api/database-connection.js
import mysql from "mysql2/promise";
// This is a simple database connection test to prove you can connect to a persistent store for your application.
export default async (req, res) => {
 const connection = await mysql.createConnection({
   host: process.env.MYSQL_HOST,
   port: process.env.MYSQL_PORT,
   user: process.env.MYSQL_USER,
   password: process.env.MYSQL_PASSWORD,
 });
 
 const [rows] = await connection.query("SELECT 1 as value");
 connection.end();
 
 res.json({
   value: rows[0]["value"],
 });
};

You can see how it leverages the environment variables we configured above.

Finally, feel free to take a look at the pages/index.js page to see the contents of the app and how the API route is called.

Conclusion

I hope you enjoyed this tutorial and were able to get your persistent web app up quickly!  I highly recommend using this combo in your next school, personal, or business project for rapid and stable development.

Next Steps

  1. Learn more about PlanetScaleDB and Vercel.
  2. Configure your PlanetScaleDB database. Before you can start storing data you’ll need to set up a schema.
  3. Populate your PlanetScaleDB database with data for your app.  If you have an existing MySQL server you may be able to import your database into PlanetScaleDB.
  4. Write serverless functions to define your data layer.  In addition to nodejs, Vercel supports other languages.
  5. Use Next.js or any of the other frameworks Vercel supports to build the next great web app!
Try PlanetScaleDB
Simple, Scalable MySQL on Kubernetes. Get started on your preferred provider.