How to Build a Full-stack App with Next.js, Prisma, Postgres, and Fastify

How to Build a Full-stack App with Next.js, Prisma, Postgres, and Fastify

·

15 min read

In this article, we’ll learn how to build a Full-stack application using Next.js, Prisma, Postgres, and Fastify. We will build an attendance management demo application that manages employees’ attendance. The flow of the app is simple: an administrative user logs in, creates an attendance sheet for the day, then every employee signs in and out of the attendance sheet.

What is Next.js?

Next.js is a flexible React framework that gives you building blocks to create fast web applications. It is often called the fullstack React framework as it makes it possible to have both frontend and backend applications on the same codebase doing so with serverless functions.

What is Prisma?

Prisma is an open-source, Node.js and Typescript ORM that drastically simplifies data modeling, migrations, and data access for SQL databases. At the time of writing this article, Prisma supports the following database management systems: PostgreSQL, MySQL, MariaDB, SQLite, AWS Aurora, Microsoft SQL Server, Azure SQL, and MongoDB. You might also want to click here to see the list of all supported database management systems.

What is Postgres?

Postgres is also known as PostgreSQL and it is a free and open-source relational database management system. It is a superset of the SQL language, and it has many features that allow developers to safely store and scale complicated data workloads.

Prerequisites

This tutorial is a hands-on demonstration tutorial. Therefore, it would be best if you had the following installed on your computer to follow along:

The code for this tutorial is available here on Github, so feel free to clone it and follow along.

Project Setup

Let's start by setting up our Next.js application. To get started, run the command below.

npx create-next-app@latest

Wait for the installation to complete, and then run the command below to install our dependencies.

yarn add fastify fastify-nextjs iron-session @prisma/client
yarn add prisma nodemon --dev

Wait for the installation to complete.

Setting Up Next.js and Fastify

By default, Next.js does not use Fastify as its server. To use Fastify to serve our Next.js app, edit the scripts field in the package.json file with the code snippet below.

"scripts": {
"dev": "nodemon server.js",
"build": "next build",
"start": "next start",
"lint": "next lint"
}

Creating our Fastify Server

Now let’s create a server.js file. This file is the entry point of our application, and then we add the require('fastify-nextjs') to include the plugin that exposes the Next.js API in fastify to handle the rendering.

Open the server.js file, and add the code snippets below:

const fastify = require('fastify')()
async function noOpParser(req, payload) {
return payload;
}
fastify.register(require('fastify-nextjs')).after(() => {
fastify.addContentTypeParser('text/plain', noOpParser);
fastify.addContentTypeParser('application/json', noOpParser);
fastify.next('/*')
fastify.next('/api/*', { method: 'ALL' });
})
fastify.listen(3000, err => {
if (err) throw err
console.log('Server listening on <http://localhost:3000>')
})

In the above code snippet, we use the fastify-nextjs plugin that exposed the Next.js API in Fastify that handles the rendering for us. Then we parse the incoming requests with the noOpParser function which makes the request body available to our Next.js API route handlers and we define two routes for our app with the [fastify.next](<http://fastify.next> command. Then we create our Fastify server and made it listen to the port 3000.

Now go ahead and run the app with the yarn dev command: the app will be running on localhost:3000.

Prisma Setup

First, run the following command to get a basic Prisma setup:

npx prisma init

The above command will create a prisma directory with a schema.prisma file. This is your main Prisma configuration file which will contain your database schema. Also, an .env file will be added to the root of the project. Open the .env file and replace the dummy connection URL with the connection URL of your PostgreSQL database.

Replace the code in the prisma/schema.prisma file with the following:

datasource db {
url = env("DATABASE_URL")
provider="postgresql"
}
generator client {
provider = "prisma-client-js"
}
model User {
id        Int      @id @default(autoincrement())
createdAt DateTime @default(now())
email     String   @unique
name      String
password  String
role      Role     @default(EMPLOYEE)
attendance     Attendance[]
AttendanceSheet AttendanceSheet[]
}
model AttendanceSheet {
id        Int      @id @default(autoincrement())
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
createdBy    User?    @relation(fields: [userId], references: [id])
userId  Int?
}
model Attendance {
id        Int      @id @default(autoincrement())
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
signIn    Boolean @default(true)
signOut   Boolean
signInTime    DateTime @default(now())
signOutTime   DateTime
user    User?    @relation(fields: [userId], references: [id])
userId  Int?
}
enum Role {
EMPLOYEE
ADMIN
}

In the above code snippet, we created a User, AttendanceSheet and Attendance Model, defining relationships between each model.

Next, create these tables in the database. Run the following command:

npx prisma db push

After running the command above, you should see the output as shown in the screenshot below in your terminal:

Creating Utility Functions

With Prisma set up done, let’s create three utility functions that will be used within our app from time to time.

Open the lib/parseBody.js file and add the following code snippet. This function parse the request body to JSON:

export const parseBody = (body) => {
if (typeof body === "string") return JSON.parse(body)
return body
}

Open the lib/request.js file and add the following code snippet. This function sends a POST request.

export async function postData(url = '', data='') {
const response = await fetch(url, {
method: 'POST',
credentials: 'same-origin',
body: JSON.stringify(data)
});
return response.json();
}

Open the /lib/request.js file and add the following code snippet. This function returns an object of session properties for iron session iron-session.

export const sessionCookie = () => {
return ({
cookieName: "auth",
password: process.env.SESSION_PASSWORD,
// secure: true should be used in production (HTTPS) but can't be used in development (HTTP)
cookieOptions: {
secure: process.env.NODE_ENV === "production",
},
})
}

Next, add SESSION_PASSWORD to the .env file: it should be a string of at least 32 characters.

Styling the App

With our Utility functions done, let’s add some styles to the app. We are using css modules for this app, so open the styles/Home.modules.css file and add the code snippet below:

.container {
padding: 0 2rem;
}
.ma`n {
min-height: 100vh;
padding: 4rem 0;
flex: 1;
display: flex;
flex-direction: column;
justify-content: center;
align-items: center;
}
.login {
width: 450px;
}
.login input {
width: 100%;
height: 50px;
margin: 4px;
}
.login button {
width: 100%;
height: 50px;
margin: 4px;
}
.dashboard {
display: grid;
grid-template-columns: 3fr 9fr;
grid-template-rows: 1fr;
grid-column-gap: 0px;
grid-row-gap: 0px;
height: calc(100vh - 60px);
}
.navbar {
height: 60px;
background-color: black;
}

Create the Sidebar Component

With our styling done let’s create the sidebar component to help us navigate to different pages on our app dashboard. Open the components/SideBar.js file, and paste the code snippet below.

import Link from 'next/link'
import { useRouter } from 'next/router'
import styles from '../styles/SideBar.module.css'

const SideBar = () => {

    const router = useRouter()

    const logout = async () => {

        try {

            const response = await fetch('/api/logout', {
                method: 'GET', 
                credentials: 'same-origin', 
            });

            if(response.status === 200)  router.push('/')

        } catch (e) {
            alert(e)
        }

    }


    return (
        <nav className={styles.sidebar}>

            <ul>

                <li> <Link href="/dashboard"> Dashboard</Link> </li>

                <li> <Link href="/dashboard/attendance"> Attendance </Link> </li>

                <li> <Link href="/dashboard/attendance-sheet"> Attendance Sheet </Link> </li>

                <li onClick={logout}> Logout </li>

            </ul>

        </nav>
    )

}

export default SideBar

Login Page

Now open the page/index.js file, delete all the code there and add the following code snippet. The code below sends a post request with the email and password provided via the form to the localhost:3000/api/login route. Once the credentials are validated it calls the router.push('/dashboard') method that redirects the user to localhost:3000/api/dashboard:

import Head from 'next/head'
import { postData } from '../lib/request';
import styles from '../styles/Home.module.css'
import { useState } from 'react';
import { useRouter } from 'next/router'

export default function Home({posts}) {

  const [data, setData] = useState({email: null, password: null});

  const router = useRouter()

  const submit = (e) => {
    e.preventDefault()

    if(data.email && data.password) {
      postData('/api/login', data).then(data => {
        console.log(data); 

        if (data.status === "success") router.push('/dashboard')

      });
    }

  }

  return (
    <div className={styles.container}>
      <Head>
        <title>Login</title>
        <meta name="description" content="Login" />
        <link rel="icon" href="/favicon.ico" />
      </Head>

      <main className={styles.main}>

        <form  className={styles.login}>

          <input 
            type={"text"} 
            placeholder="Enter Your Email" 
            onChange={(e) => setData({...data, email: e.target.value})} />

          <input 
            type={"password"}  
            placeholder="Enter Your Password"
            onChange={(e) => setData({...data, password: e.target.value})} />

          <button onClick={submit}>Login</button>

        </form>

      </main>

    </div>
  )
}

Setting Up the Login API Route

Now open the page/api/login.js file and add the following code snippet. We will use PrismaClient to make our database queries, and withIronSessionApiRoute is the iron-session function for handling user sessions in RESTful applications.

This route handles the login POST request towards localhost:3000/api/login, and generates authentication cookies once the user is authenticated.

import { PrismaClient } from '@prisma/client'
import { withIronSessionApiRoute } from "iron-session/next";
import { parseBody } from '../../lib/parseBody';
import { sessionCookie } from '../../lib/session';

export default withIronSessionApiRoute(
    async function loginRoute(req, res) {

      const { email, password } = parseBody(req.body)

      const prisma = new PrismaClient()

      // By unique identifier
      const user = await prisma.user.findUnique({
        where: {
        email
      },})

      if(user.password === password) {

        // get user from database then:
        user.password = undefined
        req.session.user = user
        await req.session.save();

        return res.send({ status: 'success', data: user });

      };

    res.send({ status: 'error', message: "incorrect email or password" });

  },
  sessionCookie(),
);

Setting Up the Logout API Route

Open /page/api/logout file and add the code snippet below. This route handles GET requests to localhost:3000/api/logout that logs out users by destroying the session cookies.

import { withIronSessionApiRoute } from "iron-session/next";
import { sessionCookie } from "../../lib/session";

export default withIronSessionApiRoute(
  function logoutRoute(req, res, session) {
    req.session.destroy();
    res.send({ status: "success" });
  },
  sessionCookie()
);

Creating the Dashboard Page

This page provides an interface for users to sign in and sign out of the attendance sheet. Admins can also create an attendance sheet. Open the page/dashboard/index.js file and add the code snippet below.

import { withIronSessionSsr } from "iron-session/next";
import Head from 'next/head'
import { useState, useCallback } from "react";
import { PrismaClient } from '@prisma/client'
import SideBar from '../../components/SideBar'
import styles from '../../styles/Home.module.css'
import dashboard from '../../styles/Dashboard.module.css'
import { sessionCookie } from "../../lib/session";
import { postData } from "../../lib/request";

export default function Page(props) {

  const [attendanceSheet, setState] = useState(JSON.parse(props.attendanceSheet));

  const sign = useCallback((action="") => {

    const body = {
      attendanceSheetId: attendanceSheet[0]?.id,
      action
    }

    postData("/api/sign-attendance", body).then(data => {

      if (data.status === "success") {

        setState(prevState => {

          const newState = [...prevState]

          newState[0].attendance[0] = data.data

          return newState

        })

      }

    })

  }, [attendanceSheet])

  const createAttendance = useCallback(() => {

    postData("/api/create-attendance").then(data => {

      if (data.status === "success") {
        alert("New Attendance Sheet Created")
        setState([{...data.data, attendance:[]}])
      }

    })

  }, [])

  return (
    <div>

      <Head>
        <title>Attendance Management Dashboard</title>
        <meta name="description" content="dashboard" />
      </Head>

      <div className={styles.navbar}></div>

      <main className={styles.dashboard}>

        <SideBar />

        <div className={dashboard.users}>

          {
            props.isAdmin && <button className={dashboard.create} onClick={createAttendance}>Create Attendance Sheet</button>
          }

          { attendanceSheet.length > 0 &&

            <table className={dashboard.table}>
              <thead>
                <tr> 
                  <th>Id</th> <th>Created At</th> <th>Sign In</th> <th>Sign Out</th> 
                </tr>
              </thead>

              <tbody>
                <tr>
                  <td>{attendanceSheet[0]?.id}</td>
                  <td>{attendanceSheet[0]?.createdAt}</td>

                  {
                    attendanceSheet[0]?.attendance.length != 0 ? 
                      <>
                        <td>{attendanceSheet[0]?.attendance[0]?.signInTime}</td>
                        <td>{
                          attendanceSheet[0]?.attendance[0]?.signOut ? 
                          attendanceSheet[0]?.attendance[0]?.signOutTime: <button onClick={() => sign("sign-out")}> Sign Out </button> }</td>
                      </>
                      :
                      <>
                        <td> <button onClick={() => sign()}> Sign In </button> </td>
                        <td>{""}</td>
                      </>
                  }
                </tr>
              </tbody>

            </table>

          }

        </div>

      </main>

    </div>
  )
}

We use the getServerSideProps to generate the page data, and withIronSessionSsr is the iron-session function for working with server-side rendered pages. In the following code snippet, we query for the last row of the attendanceSheet table with a row from the attendance table, where the userId is equal to the User id stored on the user session. We also check if the user is an ADMIN.

export const getServerSideProps = withIronSessionSsr( async ({req}) => {

  const user = req.session.user

  const prisma = new PrismaClient()

  const attendanceSheet = await prisma.attendanceSheet.findMany({  
    take: 1,
    orderBy: {
      id: 'desc',
    },
    include: { 
      attendance: {
        where: {
          userId: user.id
        },
      }
    }
  })

  return {
    props: {
      attendanceSheet: JSON.stringify(attendanceSheet),
      isAdmin: user.role === "ADMIN"
    }
  }

}, sessionCookie())

Setting up the Create Attendance API Route

Open the page/api/create-attendance.js file and add the code snippet below.

import { PrismaClient } from '@prisma/client'
import { withIronSessionApiRoute } from "iron-session/next";
import { sessionCookie } from '../../lib/session';


export default withIronSessionApiRoute( async function handler(req, res) {

    const prisma = new PrismaClient()

    const user = req.session.user

    const attendanceSheet = await prisma.attendanceSheet.create({
        data: {
          userId: user.id,
        },
    })

    res.json({status: "success", data: attendanceSheet});

}, sessionCookie())

Setting Up the Sign Attendance API Route

This route handles our API POST request to localhost:3000/api/sign-attendance. The route accepts the POST request, while attendanceSheetId and action are used to sign in and out of attendanceSheet.

Open the /page/api/sign-attendance.js file and add the code snippet below.

import { PrismaClient } from '@prisma/client'
import { withIronSessionApiRoute } from "iron-session/next";
import { parseBody } from '../../lib/parseBody';
import { sessionCookie } from '../../lib/session';


export default withIronSessionApiRoute( async function handler(req, res) {

    const prisma = new PrismaClient()

    const {attendanceSheetId, action} = parseBody(req.body)

    const user = req.session.user

    const attendance = await prisma.attendance.findMany({
        where: {
            userId: user.id,
            attendanceSheetId: attendanceSheetId
        }
    })

    //check if atendance have been created
    if (attendance.length === 0) {
        const attendance = await prisma.attendance.create({
            data: {
                userId: user.id,
                attendanceSheetId: attendanceSheetId,
                signIn: true,
                signOut: false,
                signOutTime: new Date()
            },
        })   

        return res.json({status: "success", data: attendance});

    } else if (action === "sign-out") {
        await prisma.attendance.updateMany({
            where: {
                userId: user.id,
                attendanceSheetId: attendanceSheetId
            },
            data: {
              signOut: true,
              signOutTime: new Date()
            },
        })

        return res.json({status: "success", data: { ...attendance[0], signOut: true, signOutTime: new Date()}});
    }

    res.json({status: "success", data: attendance});

}, sessionCookie())

Creating the Attendance Page

This server-side rendered page shows all the attendance sheets for a logged in user. Open the /page/dashboard/attendance.js file and add the code snippet below.

import { withIronSessionSsr } from "iron-session/next";
import Head from 'next/head'
import { PrismaClient } from '@prisma/client'
import SideBar from '../../components/SideBar'
import styles from '../../styles/Home.module.css'
import dashboard from '../../styles/Dashboard.module.css'
import { sessionCookie } from "../../lib/session";

export default function Page(props) {

  const data = JSON.parse(props.attendanceSheet)

  return (
    <div>

      <Head>
        <title>Attendance Management Dashboard</title>
        <meta name="description" content="dashboard" />
      </Head>

      <div className={styles.navbar}></div>

      <main className={styles.dashboard}>

        <SideBar />

        <div className={dashboard.users}>

        <table className={dashboard.table}>

          <thead>

            <tr> 
              <th> Attendance Id</th> <th>Date</th> 
              <th>Sign In Time</th> <th>Sign Out Time</th> 
            </tr> 

          </thead>

            <tbody>

              {
                data.map(data =>   {

                  const {id, createdAt, attendance } = data


                  return (
                    <tr key={id}> 

                      <td>{id}</td> <td>{createdAt}</td>  

                      { attendance.length === 0 ? 

                        (
                          <>
                            <td>You did not Sign In</td>
                            <td>You did not Sign Out</td>
                          </>
                        )
                        :
                        (
                          <>
                            <td>{attendance[0]?.signInTime}</td>
                            <td>{attendance[0]?.signOut ? attendance[0]?.signOutTime : "You did not Sign Out"}</td>
                          </>
                        )

                      }

                    </tr>
                  )

                })

              }  

            </tbody>

          </table>

        </div>

      </main>

    </div>
  )
}

In the code snippet below, we query for all the rows from the attendanceSheet table and also fetch the attendance where the userId is equal to the user id stored in the user session.

export const getServerSideProps = withIronSessionSsr( async ({req}) => {

  const user = req.session.user

  const prisma = new PrismaClient()

  const attendanceSheet = await prisma.attendanceSheet.findMany({
    orderBy: {
      id: 'desc',
    },
    include: { 
      attendance: {
        where: {
          userId: user.id
        },
      }
    }
  })

  return {
    props: {
      attendanceSheet: JSON.stringify(attendanceSheet),
    }
  }

}, sessionCookie())

Creating The Attendance Sheet Page

This server-side rendered page shows all the attendance sheets and the Employees that signed in to that attendance sheet. Open the /page/dashboard/attendance.js file and add the code snippet below.

import { withIronSessionSsr } from "iron-session/next";
import Head from 'next/head'
import { PrismaClient } from '@prisma/client'
import SideBar from '../../components/SideBar'
import styles from '../../styles/Home.module.css'
import dashboard from '../../styles/Dashboard.module.css'
import { sessionCookie } from "../../lib/session";

export default function Page(props) {

  const data = JSON.parse(props.attendanceSheet)

  return (
    <div>

      <Head>
        <title>Attendance Management Dashboard</title>
        <meta name="description" content="dashboard" />
      </Head>

      <div className={styles.navbar}></div>

      <main className={styles.dashboard}>

        <SideBar />

        <div className={dashboard.users}>

        {
          data?.map(data => {

            const {id, createdAt, attendance } = data

            return (
              <>

                <table key={data.id} className={dashboard.table}>

                  <thead>

                    <tr> 
                      <th> Attendance Id</th> <th>Date</th> 
                      <th> Name </th> <th> Email </th> <th> Role </th>
                      <th>Sign In Time</th> <th>Sign Out Time</th> 
                    </tr> 

                  </thead>

                  <tbody>

                    {
                      (attendance.length === 0)  &&
                      (
                        <>
                        <tr><td> {id} </td> <td>{createdAt}</td> <td colSpan={5}> No User signed this sheet</td></tr>
                        </>
                      )
                    }

                    {
                      attendance.map(data => {

                        const {name, email, role} = data.user


                        return (
                          <tr key={id}> 

                            <td>{id}</td> <td>{createdAt}</td>  

                            <td>{name}</td> <td>{email}</td>

                            <td>{role}</td>

                            <td>{data.signInTime}</td>

                            <td>{data.signOut ? attendance[0]?.signOutTime: "User did not Sign Out"}</td>  

                          </tr>
                        )

                      })

                    }  

                  </tbody>

                </table>
              </>
            )
          })

          }

        </div>

      </main>

    </div>
  )
}

In the code snippet below, we query for all the rows from the attendanceSheet tables and also fetch the attendance selecting the name, email, and role.

export const getServerSideProps = withIronSessionSsr(async () => {

  const prisma = new PrismaClient()

  const attendanceSheet = await prisma.attendanceSheet.findMany({
    orderBy: {
      id: 'desc',
    },
    include: { 
      attendance: {
        include: { 
          user: {
            select: {
              name: true, 
              email: true, 
              role: true
            }
          }
        }
      },
    },

  })

  return {
    props: {
      attendanceSheet: JSON.stringify(attendanceSheet),
    }
  }

}, sessionCookie())

Testing the App

First, we have to add Users to our database. We are going to do this with Prisma Studio. To start Prisma studio, run the command below:

npx prisma studio

The Prisma index page looks like this:

To create a database user with an ADMIN role and multiple users with an EMPLOYEE role, head over to this page:

Click on Add record, then fill in the required fields: password, name, email, and role. Once you are done, click on the green Save 1 change button. Note that for simplicity, we did not hash the password.

Start the server with yarn dev. This starts the server and runs the app on localhost:3000 the login page is shown below.

Log in with a user that has an ADMIN role because only administrative users can create attendance sheets. Once the login is successful, the app will redirect you to your dashboard.

Click on the Create Attendance Sheet button to create an attendance sheet, then wait for the request to finish and the attendance sheet will appear. The user dashboard is shown below.

The attendance sheet is shown below, click on the Sign In button to sign in. Once the sign-in is successful, the sign-in time will be displayed and the Sign Out button will be visible. Click on the Sign Out button to sign out, and repeat this process many times with different users.

Next click on the Attendance link in the sidebar to view users’ attendance. The results should match the ones shown below:

Next click on the Attendance Sheet link on the sidebar to view users’ attendance of all users. Results are shown below:

Conclusion

In this article, you learned how to use a custom Fastify server with Next.js. You have also learned about Prisma and Prisma studio. I have walked you through how to connect Prisma to a Postgres database and how to create, read and update the database using the Prisma client and Prisma studio.

You have also learned how to authenticate users using iron-session. In this tutorial, we built a full-stack app that manages employee attendance using Next.js, Prisma, Postgres, and Fastify. Make sure to stay tuned for more, and until next time.