Create a Next.js Application With a MySQL Database That Builds and Deploys with Now

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 ZEIT Now.

Next.js from ZEIT is a production-ready framework that can help you create fast React applications. 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 the example data into a new table named profiles.

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:

Creating and entering into the /next-mysql directory.

Next, initialize the project:

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

Yarn will present some initial questions to set up your project, complete this and when done, add serverless-mysql and sql-template-strings as dependencies:

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.

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

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:

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:

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 application interface to display them.

Step 5: Creating Your Next.js Client

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

Adding multiple dependencies to the project.

Next, create a /pages directory like so:

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 prefetch href={`/profile?id=${p.id}`}>
              <a>
                <img src={p.avatar} />
                <span>{p.name}</span>
              </a>
            </Link>
          </li>
        ))}
      </ul>
      <nav>
        {page > 1 && (
          <Link prefetch href={`/?page=${page - 1}&limit=9`}>
            <a>Previous</a>
          </Link>
        )}
        {page < pageCount && (
          <Link prefetch 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 prefetch 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 application with both an API and interface, the next section will show you how to deploy it seamlessly with Now.

Step 6: Deploy Your Project with Now

Getting your project ready to deploy with Now could hardly be simpler, first you should create next.config.js file in your root directory with the following code:

module.exports = {
  target: 'serverless'
}

An example next.config.js file for your project.

The purpose of this file is to tell Next to build for a serverless environment, only 3 lines of code are needed!

The last file you should create is a now.json file. This will bring your project together with just a few lines of code before deployment. Create one with the following code:

{
  "version": 2,
  "name": "next-mysql",
  "alias": "next-mysql.now.sh",
  "builds": [
    { "src": "api/**/*.js", "use": "@now/node" },
    { "src": "next.config.js", "use": "@now/next" }
  ],
  "routes": [
    {
      "src": "/api/profiles/(?<id>[^/]*)",
      "dest": "api/profiles/profile.js?id=$id"
    }
  ],
  "env": {
    "MYSQL_HOST": "@mysql_host",
    "MYSQL_USER": "@mysql_user",
    "MYSQL_PASSWORD": "@mysql_password",
    "MYSQL_DATABASE": "@mysql_database"
  }
}

An example now.json file for your project.

The now.json file allows you to achieve many things with your deployment. Below is a description of what each property does:

• version ensures you are using the latest Now 2.0 platform version
• name defines a project name your deployment will be known by under Now
• alias allows you to set an alias that can be used in production
• builds instructs Now to use the @now/node and @now/next builders for your applications API and interface respectively
• routes routes individual profile requests to the correct function, with the id parameter
• env injects defined secrets into the app, exposing them as environment variables

Finally, deploy the application with Now.

If you have not yet installed Now, you can do so by installing the Now Desktop app which installs Now CLI automatically, or by installing Now CLI directly.

Now allows you to deploy your project from the terminal with just one command:

Deploying an application with Now using only one command.

You will see a short build step in your terminal followed by the news that your project has been deployed, it should look similar to this: https://next-mysql.now.sh/

Resources

For more information on working with MySQL and Next.js, please refer to their documentation.

To configure Now further, please see these additional topics and guides: