Guides

Simplest Approach to Work with Databases in Next.js Using Prisma

Published on

October 10, 2022

Simplest Approach to Work with Databases in Next.js Using Prisma

Next.js is a React-based framework that offers server-side rendering features. It runs on the server and renders pages at build time, this allows the server to serve the content ahead of time and allows you to build high-performant web applications.‍

Prisma on the other hand, is an Object-Relational Mapper (ORM) for Node.js. It provides an abstraction for interacting with a database directly. Prisma generates type-safe database schemas that map to your database and generates the actual database queries.

In this article, we will create a todo application with Next.js and Prisma to demonstrate how to manage database operations with Prisma.

Prerequisites

To follow through in this article, it is essential to have the following:

Node.js installed on your computer.
Basic understanding of how to use Prisma
Beginner knowledge of working with React
PostgreSQL installed on your computer

Setting the Next.js app

Next.js allows you to scaffold a basic web application. This allows to progressively work and scale up on any application. To set up a Next.js application, create a project folder where you want the application to live. Open the folder using a text editor such as Visual Studio Code, then open up a terminal from vsCode and run the following create-next-app command:

npx create-next-app prisma-next-todos-app --use-npm --ts

This command will create a Typescript-based Next.js app. The --ts flag instructs Node.js to scaffold a Typescript-based Next.js application. Typescript is a superset of JavaScript, It allows you to statically write code and catch errors before runtime time. Also, Node.js allows you to use two main packages to manage the project dependencies, these are:

NPM - to use NPM add a --use-npm flag as shown in the above NPX command.
Yarn - to build your Next.js with yarn add the --use-yarn to the create-next-app command.

Once you run your create-next-app command a prisma-next-todos-app folder will be created. This directory hosts the Next.js application that we will use to build a todos app using the Prisma ORM.

To test this application, change the directory to the newly created directory:

cd prisma-next-todos-app

Then run the application locally:

npm run dev

Open http://localhost:3000, and you should be served with a Hello world version on Next.js.

View of Next.js Hello World welcome screen

Adding Prisma to the Next.js app

To add Prisma to any project, you first need to initialise the Prisma client for your application. So let’s do that.

Run the following command to initialise Prisma with PostgreSQL (here you can still use MySQL, SQLite, SQL server, or MongoDB or the same) as the data source:

npx prisma init --datasource-provider postgresql

‍
This command will create:

An .env file. It contains a DATABASE_URL variable that connects to the database of your choice
A prisma folder. It contains a schema.prisma file that hosts the client, database connection, and models. We will use this file to model the database schema for the todos.

Setup database

Prisma allows you to use PostgreSQL, MySQL, SQLite, SQL server, or MongoDB. In this guide, we will use PostgreSQL database to communicate with Prisma.

Based on your database of choice, you need a connection URL that serves the database. If the database is locally installed, you need the local database connection string. Prisma will use URL to connect and communicate with your database.

Here, we are using a local PostgreSQL database. Therefore, ensure you use the PostgreSQL database connection URL as follows:

postgresql://your-user:your-password@localhost:5432/your-db-name?schema=public

To use this connection URL, ensure you replace:

Your-user. By default, this is set to postgres.
Your-password with the password used to set up PostgreSQL during installation.
Your-db-name - the name of the database you want Prisma to create on PostgreSQL. Here we will use the todos database.

Once you have updated the above URL, copy and add it to the .env file as the new DATABASE_URL variable. I.e.,

DATABASE_URL="postgresql://postgres:your-password@localhost:5432/todos?schema=public"

Create the Prisma Model

Prisma uses a model to create a database table schema. This model contains all the fields and their properties that a database should have. Let's create a Todo model to use in this example.

Inside the schema.prisma file, configure the Todo model as follows:


model Todo {
    id Int @id @default(autoincrement())
    title String
    completed Boolean @default(false)
}

This will instruct PostgreSQL to create a Todo table inside the todos database set in the previous database URL.

Prisma Migration

Prisma needs to map the above data model to the database schema. For that, run the below command:

npx prisma migrate dev --name init

Note: While running the above command, ensure your database server is up and running. In this case, we are using the PostgreSQL database. Therefore, ensure the PgAdmin is up and running.

As a result of the above command, a migrations folder will be created inside the prisma directory. The migrations folder will host .sql files that will have the command of creating the Todo table.

At this point, your database is now in sync with the schema defined in the schema.prisma file. If you refresh your database, a todos db with a Todo table will be presented with the fields set in the Prisma model used to create the database schema.

View of a todos db with athe Column view containing id, title, and completed

To generate a client from which we will create our queries, run the following command:

npx prisma generate

This command generates a Prisma client that allows you to work with Prisma in our project. In the Next.js project root folder (prisma-next-todos-app), create a lib directory. In it, add a prisma.ts file which will configure the Prisma client as below:


import {PrismaClient} from '@prisma/client';
const prisma: PrismaClient = new PrismaClient();
export default prisma;

We will use the Prisma Client to connect to the Next.js Prisma app. Let's now dive into the Next.js and consume the Prisma setup we have created.

Setting up the Next.js routes

In the pages/api folder, create a todo folder. Inside the todo folder, create an index.ts and a [id].ts file.

The index.ts file will host the creating a todo route while the [id].ts will host mutation routes such as updating and deleting todo.

index.ts


import type { NextApiRequest, NextApiResponse } from 'next';
import prisma from '../../../lib/prisma';

export default async function handle(req: NextApiRequest, res: NextApiResponse) {
    if (req.method == "POST") {
        // creating a new todo.
        const { title } = req.body
        const result = await prisma.todo.create({
            data: {
                title,
            },
        })
        return res.json(result)
    }
}

Here, we are using NextApi to execute POST requests and responses. A POST method will add a new todo to the database using the Prisma Client. Therefore prisma.todo.create() will read the request body and post the data to the database using Prisma.

[id].ts


import type { NextApiRequest, NextApiResponse } from 'next';
import prisma from '../../../lib/prisma';

export default async function handle(req: NextApiRequest, res: NextApiResponse) {
    if (req.method == "PUT") {
        // update a todo.
        const { id } = req.query;
        const post = await prisma.todo.update({
            where: { id: Number(id) },
            data: { completed: true },
        })
        return res.json(post);
    } else if (req.method == "DELETE") {
        // delete a todo.
        const { id } = req.query;
        const post = await prisma.todo.delete({
            where: {
                id: Number(id),
            },
        });
        return res.json(post);
    }
}

Operations such as PUT and DELETE are used to manipulate an already existing data record. In this case, each todo record is referenced by a unique id. To execute PUT or DELETE operations, Prisma will look to the id of the todo and modify the correct todo record referenced by that specific id.

Setting up the components

The application user interface is defined by components. Next.js uses components to set up different sections of a web application. Here we’ll create the following components:

Header.tsx: Navigation bar
Layout.tsx: Page Layout
Todo.tsx: Todo card for displaying todos on the Next.js app
AddTodoForm.tsx: Form for adding a todo.

To set up the above components, create a components directory. Inside the components directory, we will have the following files based on each component:

Header.tsx


import React from "react";
import Link from 'next/link';
import {useRouter} from 'next/router';
import styles from './Header.module.css';

const Header: React.FC =  () => {
    const router = useRouter();
    const isActive:(pathname:string) => boolean = pathname => router.pathname === pathname;

    return (
        <nav className={styles.nav}>
            <div className={styles.left}>
                <Link href="/">
                    <a className={styles.bold}>
                        Todos App
                    </a>
                </Link>
            </div>
            <div className={styles.center}>
                <Link href="/">
                    <a className={styles.bold} data-active={isActive('/')}>
                        Todos
                    </a>
                </Link>
            </div>
        </nav>
    )
}

export default Header;

Here, we are creating a Navigation bar that displays the application. It creates a basic navigation element to interact with the application routes using the Next.js useRouter() method. A click on these className (S) will redirect the user to the application home page. To format the above bar, create a Header.module.css file inside components directory. Then add the following .nav styles for the navigation bar:


.nav {
    width: 100%;
    padding: 20px;
    box-shadow: 0 1px 3px 0 #d4d4d5, 0 0 0 1px #d4d4d5;
    display: flex;
    justify-content: space-between;
    margin-bottom: 10px;
}

Layout.tsx


import React,{ReactNode} from 'react';
import styles from "./Layout.module.css";
import Header from './Header';

type Props = {
    children: ReactNode
}

const Layout:React.FC<Props> = ({children}) => {
  return (
    <div>
        <Header />
        <main className={styles.layout}>
            {children}
        </main>
    </div>
  )
}

export default Layout;

The Layout component runs a container that wraps the Header component to the main application. It runs the Header component as its child. To style the component, create a Layout.module.css file and add the following CSS code:


.layout {
    width: 50%;
    margin: 10px auto;
    padding: 10px;
}

Todo.tsx


import React,{useState} from 'react';
import styles from './Todo.module.css';

export type TodoProps =  { // Todo 
    id:number;
    title:string;
    completed:boolean;
}

type Props = { // Props
    todo:TodoProps;
    deleteTodo:Function
}

const Todo:React.FC<Props> = (props)  => {
  const [todo,setTodo] = useState(props.todo);
  const updateTodo = async () => {
    // data.
    const data = {
        completed:true
    };
    // Send Request to Update Todo
    let response = await fetch('/api/todo/'+todo.id,{
        method:'PUT',
        headers:{
            'Content-Type':'application/json'
        },
        body:JSON.stringify(data)
    });
    // Get the response
    response = await response.json();
    // Response check
    if(response){
        setTodo({
            ...todo,
            completed:true
        });
    }
  }
  return (
    <div className={styles.todoCard}>
        <h4 className={styles.title}>
            {todo.title}
        </h4>
        <p className={styles.description}>
            {
                todo.completed ? <span className={styles.completed}>Already Completed</span> : <span className={styles.notCompleted}>Not Completed</span>
            }
        </p>
        {
            !todo.completed && <button className={styles.actionBtn} onClick={() => updateTodo()}>Mark as Completed</button>
        }
        {
            todo.completed && <button className={styles.actionBtn} onClick={() => props.deleteTodo(todo.id)}>Delete Todo</button>
        }
    </div>
  )
}

export default Todo;

We are displaying a list of todos using Next.js. Therefore, we need a component that will render the todos on our web app. The above Todo component will create and render a todoCard.

This card component will serve dynamic data from the server. Consequently, we need TodoProps and useState for the todos data. Once we get the response to this data, we will render it to the web app using the todoCard.

Each todoCard will display:

A todo title,
Mark as a Completed button and,
Delete the Todo button.

To style the above component, create a Todo.module.css file and add the following styling:


.todoCard {
    width: 100%;
    box-shadow: 0 1px 3px 0 #d4d4d5, 0 0 0 1px #d4d4d5;
    padding: 10px;
    margin: 10px 0px;
}

.title{
    text-transform: uppercase;
}

.description{
    font-size: 15px;
}

.completed {
    color: green;
}

.notCompleted {
    color: yellowgreen;
}

.actionBtn{
    padding:10px;
    background: transparent;
    border: 1px solid #d4d4d5;
}

AddTodoForm.tsx


import {useState} from 'react';
import styles from './Form.module.css';

type Props = {
    addTodo: Function;
}

const AddTodo : React.FC<Props> = ({addTodo}) => {
    const [title,setTitle] = useState('');
    const [error,setError] = useState('');
    const [message,setMessage] = useState('');


    const handleSubmit = (e:any) => {
        e.preventDefault();
        if(!title){
            setError('Title is required');
            return;
        }
        // data composition.
        const data = {
            title
        };
        // send a request to the backend.
        fetch(
            '/api/todo',
            {
                method:"POST",
                headers:{
                    "Content-Type":"application/json"
                },
                body:JSON.stringify(data),
            }
        ).then(( response ) => response.json()).then( (data ) => {            
            // reset the title.
            setTitle('');
            // set the message.
            setMessage('Todo added successfully');
            // add the todo.
            addTodo(data);
        });
    }
    return   (
        <div className={styles.formContainer}>
            <form onSubmit={handleSubmit}>
                {
                    error && (
                        <div className={styles.formGroupError}>
                            {
                                error
                            }
                        </div>                        
                    )
                }
                {
                    message && (
                        <div className={styles.formGroupSuccess}>
                            {
                                message
                            }
                        </div>
                    )
                }
                <div className={styles.formGroup}>
                    <input type="text" name="title" id="title" placeholder='Title of todo' value={title} onChange={
                        (e) => setTitle(e.target.value)
                    } />
                </div>
                <div className={styles.formGroup}>
                    <button type="submit">Add Todo</button>
                </div>
            </form>
        </div>
    )   
}

export default AddTodo;

Likewise, we need to add todos to the database using Next.js and the Prisma setup. The above AddTodoForm component will create a form that will allow you to insert todos values. A click on the Add Todo will send the data to the database using Prisma. To style the above component, create a Form.module.css file and add the following styling:


.formContainer{
    width: 100%;
    padding: 10px;
    border: 1px solid #d4d4d5;
}

.formGroupError{
    width: 100%;
    padding: 10px;
    border: 1px solid red;
}

.formGroupSuccess{
    width:100%;
    padding: 10px;
    border: 1px solid green;
}

.formGroup{
    width: 100%;
    margin: 10px 0px;
}
.formGroup input[type="text"]{
    width: 100%;
    padding: 10px;
    border: 1px solid #d4d4d5;
}

.formGroup button{
    width: 100%;
    display: inline-block;
    padding: 10px;
    background: transparent;
    border: 1px solid #d4d4d5;
}

Showing the todos

Let's now display the todos based on the component we have created. In pages/index.tsx:

Ensure all the needed dependencies are imported


import type { GetServerSideProps } from 'next';
import Head from 'next/head';
import { useState } from 'react';
import styles from '../styles/Home.module.css';
import Layout from '../components/Layout';
import TodoCard, { TodoProps } from '../components/Todo';
import AddTodoForm from '../components/AddTodoForm';
import prisma from '../lib/prisma';

Get the todos from the server using the GetServerSideProps function


export const getServerSideProps: GetServerSideProps = async () => {

    const todos = await prisma.todo.findMany({
    });

    return {
        props: { todos },
    }
}

Define the structure of todos


type Props = {
    todos: TodoProps[]
}

Show the todos responsively inside the render function


const Home: React.FC<Props> = (props) => {
    
    const [showAddTodo, setShowAddTodo] = useState(false);
    const [todos, setTodos] = useState(props.todos);
    function addTodo(todo: TodoProps) {
        setTodos([...todos, todo]);
    }

    async function deleteTodo(id: number) {
        let response = await fetch('/api/todo/' + id, {
            method: "DELETE"
        });
        response = await response.json();
        if (response) {
            // remove the todos from the list of todos.
            setTodos(
                todos.filter(todo => todo.id !== id)
            );
        }
    }
    return (
        <Layout>
        <Head>
        <title>Todos App </title>
            <meta name = "description" content = "Simple Todos App" />
                <link rel="icon" href = "/favicon.ico" />
                    </Head>
                    <div>
                    <button className={ styles.addTodo } onClick = { () => setShowAddTodo(!showAddTodo)}>
                    {
                        showAddTodo? "Back to Todos": "Add todo"
                    }
                        </button>
                        </div>
{
    showAddTodo ? (
        <AddTodoForm  addTodo= { addTodo } />
            ) : (
        todos.length > 0 ? (
            todos.map((todo) => (
                <TodoCard key= { todo.id } todo = { todo } deleteTodo = { deleteTodo } />
                ))
                ) : (
        <div>
        <h4>You do not have any todos added.</h4>
            </div>
                )
            )
}
</Layout>
)}

export default Home;

Inside the styles/Home.module.css file, add the following style for the addTodo button:


.addTodo{
  width: 100%;
  display: inline-block;
  margin: 5px 0px;
  padding: 10px;
  background: transparent;
  border: 1px solid #d4d4d5;
  cursor: pointer;
  font-weight: bold;
}

Start your development server by running this command on your terminal:

npm run dev

Proceed to the URL yielded: i.e: http://localhost:3000. Your home page should be similar to:

View of homepage after starting development server — *todos_page*

Once you click on add todo, you should have the following:

View of an empty Title of Todo text field — *add_todo_section*

These changes will be represented in your database as well.

View of created To Dos containing entry ID, Title, and Status

Conclusion

In this tutorial, we have learned how to run Prisma on a Next.js server. We have used Prisma to communicate with the database. Finally, built a Next.js application that leverages this setup to perform database operations.

We hope you found this tutorial helpful. Happy coding!

You can find the source code for the project on this GitHub repository.