Writing my first database application with Next.js and Heroku

#nextjs #postgres #javascript #heroku

Up until now I've been solely making static sites, so I decided it was time to branch out and try to make a full stack application with a database. I decided to make a simple Twitter clone - sending and receiving short messages to and from the database, how hard could it be? I'll be attempting to write a step by step guide to what I did in the hopes that it could help someone else.

First off I went to Heroku and created an account. I'm not looking to spend any money on this, so I chose all the free tier options. After I made a new blank app, I connected it to a GitHub repository and set it to automatic deployment from the main branch.

In that repository I set up a basic Next.js app using npx create-next-app At this point I ran into a problem. In order to get my app to work, I had to change the start script in package.json from "start": "next start" to "start": "next start -p $PORT". At that point, you can write React as normal and have it hosted on Heroku.

Now that I had a basic front-end application running, I wanted to connect the back-end. This is where I had some trouble, as I had never done it before. I went to the resources tab on Heroku and searched Postgres, then added Heroku Postgres to my application at the Hobby Dev - Free tier. Then I went and downloaded the latest release of PostgreSQL to my machine so I could develop with it.

After it installed (using all default settings), I launched pgAdmin 4 to monitor and edit the Postgres server running locally. (Side note, in order to get pgAdmin running on my Windows machine I had to edit a registry entry, here's the details). You need to make sure you know your login role and password. You should create a new database by right clicking on Databases under the PostgreSQL server and set that up with a name and put your login role as owner.

At this point, I started to use Prisma to connect my app to the database. Install it by running npm install @prisma/cli -D and then npx prisma init in your base repository directory. A .env file should be created where you want to set DATABASE_URL="postgresql://[username]:[password]@localhost:[server port]/[database name]". Then you fill in your prisma/schema.prisma file. This is what I used, but you can change the name and contents of the models depending on what you want to store in your database. Here's a reference on the schema.

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

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

model User {
  name  String @id
  posts Post[]
}

model Post {
  id        Int @id @default(autoincrement())
  createdAt DateTime @default(now())
  content   String 
  author    User
}

Then you can run prisma migrate dev --preview-feature in order to push your data model to the database! In order to visualize and add sample data to the database, I used Prisma Studio, which you just connect to your schema.prisma file and then can add and delete records to your heart's content. In order to get this to work on your Heroku server you need to push your database schema to Heroku with the commands heroku login and then heroku pg:push [local database name] [heroku database name] --app [heroku app name]. You can find more details on this command here.

Now that the database is set up, let's figure out how to read and write to it. Next.js has API routes that can handle this! First, I had to install middleware to handle CORS for me with npm i cors. I was having trouble with CORS only on mobile devices, and this fixed it. I also had to add the following file at api/_base.js. This meant that I wasn't creating new database sessions with every query.

import { PrismaClient } from "@prisma/client";

export let prisma;

if (process.env.NODE_ENV === "production") {
    prisma = new PrismaClient();
} else {
    if (!global.prisma) {
        global.prisma = new PrismaClient();
    }

    prisma = global.prisma;
}

Create the path pages/api/posts/index.js, and enter the following code.

import { prisma } from "../_base";
import Cors from "cors";

// Initializing the cors middleware
const cors = Cors({
    methods: ["GET", "HEAD"],
});

// Helper method to wait for a middleware to execute before continuing
// And to throw an error when an error happens in a middleware
function runMiddleware(req, res, fn) {
    return new Promise((resolve, reject) => {
        fn(req, res, (result) => {
            if (result instanceof Error) {
                return reject(result);
            }

            return resolve(result);
        });
    });
}

export default async function handle(req, res) {
    await runMiddleware(req, res, cors);
    const posts = await prisma.post.findMany();
    res.setHeader("Cache-Control", "public, max-age=0, stale-while-revalidate=1");
    res.json(posts);
}

Now when you visit example.herokuapp.com/api/posts, it will return a JSON document containing every Post item in your database!

Writing to the database is simple too (though not simple to figure out). My MessageInput component has the following function to send data to the API route and then clear the form.

function postMessage(e) {
    e.preventDefault();
    let content = document.querySelector("#text");
    let author = document.querySelector("#name");
    fetch(`${server}/api/posts/write`, {
        method: "POST",
        headers: {
            "Content-Type": "application/json",
        },
        body: JSON.stringify({ content: content.value, author: author.value }),
    })
        .catch((error) => console.error("WriteError", error))
        .finally(() => {
            content.value = "";
            author.value = "";
        });
}

And then this code at api/posts/write.js to handle it.

import { prisma } from "../_base";
import Cors from "cors";

// Initializing the cors middleware
const cors = Cors({
    methods: ["GET", "HEAD"],
});

// Helper method to wait for a middleware to execute before continuing
// And to throw an error when an error happens in a middleware
function runMiddleware(req, res, fn) {
    return new Promise((resolve, reject) => {
        fn(req, res, (result) => {
            if (result instanceof Error) {
                return reject(result);
            }

            return resolve(result);
        });
    });
}

export default async function handle(req, res) {
    await runMiddleware(req, res, cors);
    const user = await prisma.post.create({
        data: {
            content: req.body.content,
            author: {
                connectOrCreate: {
                    where: { name: req.body.author },
                    create: { name: req.body.author },
                },
            },
        },
    });
    res.json({ user: user });
}

After the middleware, you're creating a post, then connecting it to an existing author in the database, or creating one if it doesn't exist already.

And then you have a working website! There are all sorts of improvements and features you can add, but having reading and writing to the database taken care of means you're well on your way!

View my example running live (and leave a comment!) here: https://flibberty-gibbets.herokuapp.com/