How to Build a Complete Next.js App with Vercel & CockroachDB

How to Build a Complete Next.js App with Vercel & CockroachDB

In this tutorial, we’ll create an app for coordinating social events. In the process, you’ll see how simple it can be to create a web application powered by Next.js and hosted on Vercel. The tutorial will also demonstrate how quickly we can create a fully-fledged relational database on the cloud without installing it locally.

Our simple Next.js web app has:

  • An input page that allows a user to add an event with a date, time, title, and description
  • An events page that lists all of the events in the database, and that allows a user to respond with their name
  • An RSVP list that displays who has responded to the events page

For this tutorial, you should already be familiar with JavaScript. It’s okay if you’re not familiar with the other tools used here, Next.js and CockroachDB, as the tutorial begins with an introduction to them! To follow along, check out the full project code.

Introduction to CockroachDB

CockroachDB is a distributed database that uses SQL for cloud applications. It’s designed to build, scale, and manage modern data-intensive applications. It also supports the PostgreSQL wire protocol, so you can use any available PostgreSQL client drivers to connect using various languages. And there’s a free tier that you can use to experiment without paying for its use.

Let’s briefly see how to create a CockroachDB account, cluster, and database.

First, sign up for a CockroachDB account, choose the free plan, and create your cluster.

CockroachDB Serverless

After creating your cluster, you’ll be prompted to create a SQL user. Click Generate & save password.

Create SQL User

Be sure to copy your password somewhere safe, because you won’t see it again. Clicking next will take you to an interface that provides your connection information, as follows:

Connection Information

Follow the instructions in the CockroachDB Client option to connect to your cluster.

Also, make sure to take note of the command to download the CA certificate. We’ll need to run it in our project’s directory later.

After connecting to your cluster, run the following SQL statements in the terminal:

 defaultdb> CREATE DATABASE social_events_db;

 defaultdb> USE social_events_db;

These statements create a database named “social_events_db” and select it as the current/active database.

Next, we need to create two tables: one for storing events, and one for the names of people who have replied. The events table has the ID, date, time, title, and description columns. The people table has these ID and name columns:

    social_events_db> CREATE TABLE events (id UUID PRIMARY KEY DEFAULT   
  gen_random_uuid(), event_date DATE, event_time TIME, title STRING, description 
  STRING);

    social_events_db> CREATE TABLE people (id UUID PRIMARY KEY DEFAULT   
  gen_random_uuid(), name STRING, event_id UUID REFERENCES events(id));

You can run the following command to see the created tables:

SHOW TABLES;

Here’s a screenshot of the output:

You can also see the columns for each table by running the following commands:

  \d events;

  \d people;

The results will look like this:

Now that we’ve created our CockroachDB database, let’s see a step-by-step, hands-on demonstration of building a Next.js web app. To see the demo application running live, check out social-events-app.vercel.app.

What is Next.js?

Next.js is a framework for creating server-side rendered React applications. We’ll create a Next.js app based on this template that uses Bootstrap 4 for CSS styling.

In a new terminal, run the following command:

  npx create-next-app --example with-react-bootstrap social-events-app

After generating your Next.js application, go to your project folder and start the live-reload development server. You’ll do this by running the following commands:

  cd social-events-app   npm run dev

Next, go to http://localhost:3000/ with your web browser to see your application up and running! This is how our application looks at this point:

Next, we’ll see how to connect to CockroachDB using Vercel Serverless Functions.

First, create a Vercel account. Create an empty GitHub repository, and then push your project’s code to the repository:

git remote add origin <YOUR_GITHUB_REPO_URL>
git push -u origin main

Next, please follow the instructions on Vercel’s website to import your repository into your Vercel account.

To let your application communicate with CockroachDB, install the Node.js pg driver:

  npm install pg

Vercel supports deploying serverless functions by simply putting JavaScript files inside the /pages/api folder of our Next.js application.

Building the Web App

Let’s get started by creating the serverless functions that are responsible for communicating with the database.

Previously, we copied the command to download the CA certificate. Here, we use this command with a small modification. Change the command output argument (-o) to download the certificate:

curl --create-dirs -o ./root.crt -O 
https://cockroachlabs.cloud/clusters/1f404def-6af8-41fe-b3da-ef1229cd6596/cert

Open the certificate file and copy the certificate in an environment variable named CERT in your Vercel account, as shown in the following screenshot:

Environment Variables

It will be available from the process.env.CERT in your code. 

We will also create an environment variable named DATABASE_URL. You can retrieve this using the General connection string option in the connect interface.

General connection string option in the connect interface

It will be available from the process.env.DATABASE_URL in your code. See Vercel’s documentation on environment variables for more information.

Next, create a config.js file inside the root folder of your local project and add the following object:

const config = {
  connectionString: process.env.DATABASE_URL,
  ssl: {
    rejectUnauthorized: true,
    ca: process.env.CERT
  }
};
exports.config = config;

Here, we create a configuration object containing information to connect to our database. The object is imported from different serverless functions to connect to the database.

How to add events

The first api route is used for adding events to our database. In your local project, create a pages/api/addEvent.js file and start by adding the following code:

import { Pool } from "pg/lib";
import { config } from "../../config";
const pool = new Pool(config);

These lines will import Pool from the pg package and then import the config object. Then, create a connection pool by passing the config object.

Next, define and export the serverless function for adding a new event, as follows:

export default async function handler(request, response) {
  const { title, description, date, time } = request.body;
  const query = `INSERT INTO events (title, description, event_date, event_time)
VALUES ('${title}', '${description}', '${date}', '${time}');`;

  try {
    const client = await pool.connect();
    await client.query(query);
    response.json({
      message: "Success!"
    });
  } catch (err) {
    response.status(500).json({
      message: err.message
    });
  }
}

Inside the body of our serverless function, we first destructure the request.body object to retrieve the posted data. Then, create an SQL query for inserting events into the database. We connect to our database cluster using the connect method and run the SQL query using the query method.

If there’s an error, we send a response with error code 500 and the error message. Otherwise, we send a response indicating success.

How to respond to events

Next, let’s implement the api route for responding to events. Create a pages/api/rsvp.js file, and start by adding the following code:

import { Pool } from "pg/lib";
import { config } from "../../config";
const pool = new Pool(config);

Next, define and export the function, as follows:

export default async function handler(request, response) {
  const { name, eventId } = request.body;
  const query = `INSERT INTO people (name, event_id) VALUES ('${name}', '${eventId}');`;

  try {
    const client = await pool.connect();
    const res = await client.query(query);
    console.log(res);
    response.json({
      message: "Success!"
    });
  } catch (err) {
    response.status(500).json({
      message: err.message
    });
  }
}

We first retrieve the posted name and event ID via JavaScript destructuring. Next, we create the query for inserting a row into the database. After that, we connect to our database cluster, and we run the query.

How to create the user interface

Let’s get started with the home page, displaying the list of events using Bootstrap cards.

Creat the events page

Open the pages/index.jsx file and start by replacing the code step by step as follows.

First, add the following necessary imports:

import React from "react";
import Head from "next/head";
import Link from "next/link";
import { Container, Row, Card, Button, Form } from "react-bootstrap";
import { Pool } from "pg/lib";
import { config } from "../config";
const pool = new Pool(config);

Head is a component for appending elements to the head of the page where Link enables client-side transitions between routes.

Next, define the following React component:

const Home = ({ error, events }) => {
  const onRSVP = async (eventId) => {};

  return (
    <Container className="md-container">
      <Head>
        <title>Social Events</title>
        <link rel="icon" href="/favicon-32x32.png" />
      </Head>
      <Container>
        <h1>Social Events</h1>
        <p>
          <Link href="/add-event">Share</Link> and attend events..
        </p>
        <Link href="/add-event">
          <Button variant="primary">Add event &rarr;</Button>
        </Link>
        <Container>
          <!-- ADD MARKUP FOR DISPLAYING EVENTS-->
        </Container>
      </Container>

      <footer className="cntr-footer">
        <p>Social Events (c) 2022</p>
      </footer>
    </Container>
  );
};

export default Home;

Next, add the following markup for displaying events inside the second container:

<Row className="justify-content-md-between">
  {events.map((event) => (
    <Card key={event.id} className="sml-card">
      <Card.Body>
        <Card.Title>{event.title}</Card.Title>
        <Card.Text>{event.description}</Card.Text>
        <Card.Text>Date: {event.event_date}</Card.Text>
        <Card.Text>Time: {event.event_time}</Card.Text>
        <Button variant="primary" onClick={() => onRSVP(event.id)}>
          RSVP &rarr;
        </Button>
        <Link href={\`/${event.id}\`}>
          <Button variant="primary">People who have RSVP'd</Button>
        </Link>
        <Form.Control
          type="text"
          placeholder="Write your name to RSVP.."
          value={name}
          onInput={(e) => setName(e.target.value)}
        />
      </Card.Body>
    </Card>
  ))}
</Row>

We iterate the events prop, and we display each event using a card. We also add two buttons to respond to the event with a name and display the people who have responded.

After that, we need to retrieve the events using getServerSideProps and pass them as the events prop to the function. We’ll do this using the following code:

export async function getServerSideProps() {
  const events = [];
  const client = await pool.connect();
  const res = await client.query("SELECT * FROM events;");

  if (res.rows.length > 0) {
    res.rows.forEach((row) => {
      console.log(row);
      events.push(row);
    });
  }
  return {
    props: { events: JSON.parse(JSON.stringify(events)) }
  };
}

We use the same client and config as earlier to retrieve the events from the database, and the function returns the events object as a prop to the Home function. In case there’s an error, the function returns the error instead.

Next, implement the method for responding to events, as follows:

const onRSVP = async (eventId) => {
  const response = await fetch("/api/rsvp", {
    method: "POST",
    headers: {
      Accept: "application/json",
      "Content-Type": "application/json"
    },
    body: JSON.stringify({
      name: name,
      eventId: eventId
    })
  });
  if (response.status == 200) {
    alert("RSVP'd!");
  } else {
    alert("Error!");
  }
};

Here, we send a POST request using the Fetch API to the corresponding api route with the user’s name and the event ID. If a successful response is received, we alert the user with “RSVP’d!” Otherwise, we display “Error!”

Also, define the name variable in the Home function:

const \[name, setName] = React.useState('');

Next, in each card displaying an event, add the following control for getting the name:

  <Form.Control  type="text"  placeholder="Write your name to RSVP.."  value={name}\
  onInput={e  =>  setName(e.target.value)}  />

In the same way, we must create a pages/[eventId].jsx page where we can display the people who have replied. This page is similar to the events page, except we need to fetch people who have responded to a specific event. 

import React from "react";
import Head from "next/head";
import Link from "next/link";
import { Card, Container, Row } from "react-bootstrap";
import { Pool } from "pg/lib";
import { config } from "../config";
const pool = new Pool(config);


const PeoplePage = ({ people }) => {
  return (
    <Container className="md-container">
      <Head>
        <title>Social Events</title>
        <link rel="icon" href="/favicon-32x32.png" />
      </Head>
      <Container>
        <Container>
          <h1>Social Events</h1>
          <p>
            <Link href="add-event">Share</Link> and attend{" "}
            <Link href="/">events</Link> ..
          </p>
          <Row className="justify-content-md-between">
            <!-- ADD MARKUP FOR DISPLAYING EVENTS-->
          </Row>
        </Container>
      </Container>
      <footer className="cntr-footer">
        <p>Social Events (c) 2022</p>
      </footer>
    </Container>
  );
};

export async function getServerSideProps(context) {
  const { eventId } = context.params;
  console.log(eventId);
  const query = `SELECT * FROM people WHERE event_id='${eventId}';`;


  const people = [];
  const client = await pool.connect();
  const res = await client.query(query);
  if (res.rows.length > 0) {
    res.rows.forEach((row) => {
      people.push(row);
    });
  }
  return {
    props: { people }
  };
}

export default PeoplePage;

Here, we use the getServerSideProps method to get the event ID from the context.params. Next, we use our client and config to read the people from the database and return the resulting array as props for the People page function.

In the component’s markup, we iterate over the array of people as follows:

{people.map((p) => (
  <Card key={p.id} className="sml-card">
    <Card.Body>
      <Card.Text>{p.name}</Card.Text>
    </Card.Body>
  </Card>
))}

We display the name of each responder using a Bootstrap card.

Creat the event input page

Next, create a pages/add-event.jsx page and start by adding the following imports:

import React from "react";
import Head from "next/head";
import Link from "next/link";
import { Container, Row, Card, Button, Form } from "react-bootstrap";

Next, define the following React refs for getting the form’s values:

const EventPage = () => {
  const eventTitle = React.useRef();
  const eventDate = React.useRef();
  const eventTime = React.useRef();
  const eventDescription = React.useRef();

Next, define the handleSubmit method:

  const handleSubmit = async (e) => {
    e.preventDefault();
    const response = await fetch("/api/addEvent", {
      method: "POST",
      headers: {
        Accept: "application/json",
        "Content-Type": "application/json"
      },
      body: JSON.stringify({
        title: eventTitle.current.value,
        date: eventDate.current.value,
        time: eventTime.current.value,
        description: eventDescription.current.value
      })
    });
    if (response.status == 200) {
      alert("Your event is added!");
      eventTitle.current.value = "";
      eventDate.current.value = "";
      eventTime.current.value = "";
      eventDescription.current.value = "";
    } else {
      alert("Error!");
    }
  };

Here, we send a POST request with the event data that we retrieve from the form’s controls using React refs to the api route responsible for inserting events in the database. If we get a response with a 200 status, we alert the user with a “Your event is added!” message, and we clear the form. Otherwise, we display the “Error!” message.

Next, we add the page’s markup, as follows:

  return (
    <Container className="md-container">
      <Head>
        <title>Social Events</title>
        <link rel="icon" href="/favicon-32x32.png" />
      </Head>
      <Container>
        <h1>Social Events</h1>
        <p>
          Share and attend <Link href="/">events</Link> ..
        </p>
        <Container>
          <Row className="justify-content-md-between">
            <!-- ADD FORM HERE -->
          </Row>
        </Container>
      </Container>

      <footer className="cntr-footer">
        <p>Social Events (c) 2022</p>
      </footer>
    </Container>
  );
};
export default EventPage;

We simply add a title, header, and subheader to our page. Then we create a container for our form and a footer just below.

Next, add the following form:

<Card className="sml-card">
  <Card.Body>
    <Form onSubmit={handleSubmit}>
      <Form.Group controlId="form.eventTitle">
        <Form.Label>Title</Form.Label>
        <Form.Control
          type="text"
          placeholder="Enter event title"
          ref={eventTitle}
        />
      </Form.Group>
      <Form.Group controlId="form.eventDate">
        <Form.Label>Event date</Form.Label>
        <Form.Control
          type="date"
          placeholder="Enter event date"
          ref={eventDate}
        />
      </Form.Group>
      <Form.Group controlId="form.eventTime">
        <Form.Label>Event time</Form.Label>
        <Form.Control
          type="time"
          placeholder="Enter event time"
          ref={eventTime}
        />
      </Form.Group>
      <Form.Group controlId="form.eventDescription">
        <Form.Label>Event description</Form.Label>
        <Form.Control
          as="textarea"
          rows={3}
          placeholder="Write something about your event.."
          ref={eventDescription}
        />
      </Form.Group>
      <Form.Group>
        <Button className="btn btn-primary" type="submit">
          Send
        </Button>
      </Form.Group>
    </Form>
  </Card.Body>
</Card>

We bind the handleSubmit method to the onSubmit event of the form to call when we submit the form. Next, we attach a React ref to each form control to access the value of the control on the handleSubmit method.

After implementing these steps, push the code to your GitHub repository.  

The form looks like this:

Event Form

 

In this tutorial, we’ve seen how it’s easy to create a web application powered by Next.js and hosted on Vercel. We’ve also shown how quickly we can create a fully-fledged relational database on the cloud without installing it locally. Here is my repo, it’s deployed at: https://social-events-app.vercel.app/

We used serverless functions to communicate with our database using the PostgreSQL driver for Node.js.

To follow the previous steps by yourself, sign up for a CockroachDB account and begin building your own CockroachDB-powered Next.js web app.

Keep Reading

Build a Complete Jamstack App with CockroachDB and Netlify Functions

To help people get outdoors and improve their physical well-being, we’ll create an outdoor activity tracker. Although we …

Read More
How to Build a Complete Web App with Python and CockroachDB

In this article, we’re building a full-stack web app that simulates a game leaderboard. The idea is to make it as simple …

Read More
Build a Complete Jamstack App with CockroachDB and Netlify Functions

To help people get outdoors and improve their physical well-being, we’ll create an outdoor activity tracker. Although we …

Read More
x
Developer Resources