Create a Next.js App with a MySQL Database That Builds and Deploys with Vercel

In this guide, we will walk you through creating and deploying a Next.js app with the most popular open source database in the world, MySQL, on Vercel.

Next.js from Vercel is a production-ready framework that can help you create fast React apps. By using it along with MySQL, you can create a fast, modern web app that interacts with customer data in a performant manner.

We demonstrate the set up via an example app, that displays a paginated, gallery view of robot profiles, with individual profiles just a click away. The finished app can be found at https://next-mysql.now.sh.

Step 1: Populating Your MySQL Database

To use this guide, you will need to setup a remote MySQL database. Many cloud providers offer this service, such as Amazon Web Services, Google Cloud and Microsoft Azure. Most of them offer a free trial.

Once you have your remote MySQL database setup, you should make a note of your database credentials:

• Database name
• Database hostname
• Database username
• Database password

Using these credentials, you can connect to your database and insert your data into a new table, named profiles.

For this example, a profile consists of the following fields:

• address string
• avatar string
• email string
• id number
• name string

For brevity, we do not cover inserting records into a MySQL database. More information on doing this can be found in the MySQL documentation.

Step 2: Set Up Your Project

Now that the database is populated, you can create a project directory and cd into it:

mkdir next-mysql && cd next-mysql

Creating and entering into the /next-mysql directory.

Next, initialize the project:

npm init

Initializing the project, this creates a package.json file.

When this is complete, add serverless-mysql and sql-template-strings as dependencies:

npm i serverless-mysql sql-template-strings

Adding serverless-mysql and sql-template-strings as dependencies to the project.

Adding serverless-mysql to the project will allow you to make connections to your MySQL database. In addition to this, it also manages connections, ensuring you do not 'max out' the available connections.

Managing MySQL connections is an essential part of using it successfully in a serverless environment. This is because Serverless Functions will create multiple database connections as traffic increases. Therefore, all connections can be consumed quickly unless managed correctly - this is all handled for you by serverless-mysql.

Next, add a build script to your package.json file:

{
  ...
  "scripts": {
    "build": "next build"
  }
}

Adding a build script to your package.json file.

Now, add your database credentials from step 1 to the project as Secrets using the Vercel CLI to keep them secure:

vercel secrets add MYSQL_HOST $database-hostname && vercel secrets add MYSQL_USER $database-username && vercel secrets add MYSQL_DATABASE $database-name && vercel secrets add MYSQL_PASSWORD $database-password

Adding secrets to the project.

Step 3: Create Your Reusable Database Connection

To ensure all your MySQL connections are managed by serverless-mysql, you should create a helper function to form the connection each time.

Create a /lib directory with a db.js file inside:

mkdir lib

Creating a /lib directory.

Add the following code to db.js:

const mysql = require('serverless-mysql')

const db = mysql({
  config: {
    host: process.env.MYSQL_HOST,
    database: process.env.MYSQL_DATABASE,
    user: process.env.MYSQL_USER,
    password: process.env.MYSQL_PASSWORD
  }
})

exports.query = async query => {
  try {
    const results = await db.query(query)
    await db.end()
    return results
  } catch (error) {
    return { error }
  }
}

An example db.js file for your project.

Your db.js file performs the following functions:

• Creates a connection to your MySQL database using credentials defined as Secrets
• Exports a function that ensures connections are closed once the query has resolved

Now you have a reusable database connection, perfectly suited for a serverless environment.

Step 4: Creating Your Node.js API

The next step is to create your API. Start off by creating an /api directory with a /profiles directory inside:

mkdir api && mkdir api/profiles

Creating an /api directory with a /profiles directory inside it.

Inside your /profiles directory create an index.js file with the following code:

const db = require('../../lib/db')
const escape = require('sql-template-strings')

module.exports = async (req, res) => {
  let page = parseInt(req.query.page) || 1
  const limit = parseInt(req.query.limit) || 9
  if (page < 1) page = 1
  const profiles = await db.query(escape`
      SELECT *
      FROM profiles
      ORDER BY id
      LIMIT ${(page - 1) * limit}, ${limit}
    `)
  const count = await db.query(escape`
      SELECT COUNT(*)
      AS profilesCount
      FROM profiles
    `)
  const { profilesCount } = count[0]
  const pageCount = Math.ceil(profilesCount / limit)
  res.status(200).json({ profiles, pageCount, page })
}

An example index.js file for your project.

Your index.js file performs the following functions:

• Parses the request query parameters
• Uses the query parameters to determine which profiles are required
• Requests only the required profiles from the database
• Queries the database to get the total records
• Uses the records count to calculate pagination
• Sends the retrieved profiles and pagination details as a response

That is all the API code required to successfully use pagination in a serverless environment.

Next, create a profile.js file in your /profiles directory containing the code below:

const db = require('../../lib/db')
const escape = require('sql-template-strings')

module.exports = async (req, res) => {
  const [profile] = await db.query(escape`
    SELECT *
    FROM profiles
    WHERE id = ${req.query.id}
  `)
  res.status(200).json({ profile })
}

An example profile.js file for your project.

Your profile.js file performs the following functions:

• Parses the request query parameter
• Uses the query parameter to select a single profile from the database
• Sends the retrieved profile as a response

You now have an API that will give you either all profiles or just a single one, dependent on the Route. You now need to create the app interface to display them.

Step 5: Creating Your Next.js Client

To add Next.js to your project, you should install the following dependencies:

npm i isomorphic-unfetch next react react-dom

Adding multiple dependencies to the project.

Next, create a /pages directory like so:

mkdir pages

Creating a /pages directory.

Now you should create an index.js file inside your /pages directory with the following code:

import fetch from 'isomorphic-unfetch'
import Link from 'next/link'

HomePage.getInitialProps = async ({ req, query }) => {
  const protocol = req
    ? `${req.headers['x-forwarded-proto']}:`
    : location.protocol
  const host = req ? req.headers['x-forwarded-host'] : location.host
  const pageRequest = `${protocol}//${host}/api/profiles?page=${query.page ||
    1}&limit=${query.limit || 9}`
  const res = await fetch(pageRequest)
  const json = await res.json()
  return json
}

function HomePage({ profiles, page, pageCount }) {
  return (
    <>
      <ul>
        {profiles.map(p => (
          <li className="profile" key={p.id}>
            <Link href={`/profile?id=${p.id}`}>
              <a>
                <img src={p.avatar} />
                <span>{p.name}</span>
              </a>
            </Link>
          </li>
        ))}
      </ul>
      <nav>
        {page > 1 && (
          <Link href={`/?page=${page - 1}&limit=9`}>
            <a>Previous</a>
          </Link>
        )}
        {page < pageCount && (
          <Link href={`/?page=${page + 1}&limit=9`}>
            <a className="next">Next</a>
          </Link>
        )}
      </nav>
    </>
  )
}

export default HomePage

An example pages/index.js file for your project.

Your pages/index.js file performs the following functions:

• Checks whether the request is being made from the server or client side
• Makes a request to the API for profiles using query parameters
• Receives the profiles and pagination data, making them available as props
• Lists the profiles in a gallery view
• Uses the pagination data to create navigation buttons

The next page you should create in the /pages directory is profile.js, this will render a more detailed view of an individual profile:

import fetch from 'isomorphic-unfetch'
import Link from 'next/link'

ProfilePage.getInitialProps = async ({ req, query }) => {
  const protocol = req
    ? `${req.headers['x-forwarded-proto']}:`
    : location.protocol
  const host = req ? req.headers['x-forwarded-host'] : location.host
  const pageRequest = `${protocol}//${host}/api/profiles/${query.id}`
  const res = await fetch(pageRequest)
  const json = await res.json()
  return json
}

function ProfilePage({ profile }) {
  return (
    <>
      <div>
        <img src={profile.avatar} />
        <h1>{profile.name}</h1>
        <p>{profile.address}</p>
        <p>{profile.email}</p>
        <Link href="/">
          <a>← Back to profiles</a>
        </Link>
      </div>
    </>
  )
}

export default ProfilePage

An example pages/profile.js file for your project.

Your pages/index.js file performs the following functions:

• Checks whether the request is being made from the server or client side
• Makes a request to the API for a single profile using a query parameter
• Receives the profile data, making it available as a prop
• Displays the profile with an option to go back to the gallery

You now have a complete app with both an API and interface, the next section will show you how to deploy it seamlessly with Vercel.

Step 6: Deploy Your Project with Vercel

Getting your project ready to deploy with Vercel could hardly be simpler, all that's required is the inclusion of Environment Variables.

Create a vercel.json file with the following configuration to make the environment variables available to your Serverless Functions:

{
  "env": {
    "MYSQL_HOST": "@mysql_host",
    "MYSQL_USER": "@mysql_user",
    "MYSQL_PASSWORD": "@mysql_password",
    "MYSQL_DATABASE": "@mysql_database"
  }
}

An example vercel.json file for your project.

Finally, deploy the app with Vercel.