How to connect PostgreSQL to Next.js application using Prisma

Interacting with a PostgreSQL database or any other database directly increases the amount of SQL you write. This can cause security problems such as SQL injection attacks and limit the portability of the database. You should use an ORM (Object Relation Mapper) like Prisma to provide an abstraction layer on the database.

Learn how to use Prisma in Next.js to connect to and interact with a PostgreSQL database .

Create Next.js . application

Create a Next.js app named prisma-next by running this command in your terminal:

npx create-next-app prisma-next

The above action will create a new folder named prisma-next with the basic files to start with. Navigate to the prisma-next directory and start the server programmatically with the following command:

npm run dev

This action will start a server programming at http://localhost:3000.

Install the Prisma . client

To get started with Prisma you will need prisma and the @prisma/client packages. Prisma is a Prisma CLI tool and @prisma/client is an auto-generated query builder that will help you query the database.

Install these two packages via npm.

npm install prisma @prisma/client

Next, initialize prisma by running the command npx prisma init on the terminal.

npx prisma init

The above action creates a new file, named schema.prisma . It contains the database diagram and .env file for you to add the database connection URL.

Add connection URL

You need a connection URL to associate prisma with the PostgreSQL database. The general format for this connection URL is:

postgres://{username}:{password}@{hostname}:{port}/{database-name}

Replace the elements in the braces with your database information, then save it to the .env file:

DATABASE_URL = 'your connection string'

Next, at schema.prisma, specify the database connection URL:

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

Define Database Schema

Database Schema or database schema is a structure that defines the data model of a database. It defines the tables, columns and relationships between the tables in the database and any constraints and indexes the database should use.

To create a schema for the database with the same users table, open the schema.prisma file, and then add the User model.

model User {   id String @default(cuid()) @id   name String?   email String @unique }

The User model has an ID column as the primary key, a type string name column, and a unique email column.

After defining the data template, you need to deploy the schema to the database using the npx prisma db push command.

npx prisma db push

This command creates the actual tables in the database.

Using Prisma in Next.js

To use Prisma in Next.js, you need to create a prisma client instance.

First, create the Prisma client.

npx prisma generate

Then create a new folder named lib and add a new file named prisma.js inside it. In this file, add the following code to create a prisma client instance.

import { PrismaClient } from "@prisma/client"; let prisma; if (typeof window === "undefined") {     if (process.env.NODE_ENV === "production") {         prisma = new PrismaClient();     } else {         if (!global.prisma) {             global.prisma = new PrismaClient();         }         prisma = global.prisma;     } } export default prisma;

Now you can enter the client prisma as 'prisma' into the file, and then start querying the database.

Database query in Next.js API Route

Prisma is usually used on the server side. Here, it can safely interact with the database. In your Next.js application, you can set up an API route that uses Prisma to fetch data from the database and return it to the client. Pages or components can then fetch data from the API route using an HTTP library such as Axios or fetch.

Create an API route by opening the pages/api directory and creating a sub-directory named db. In this directory, create a file createuser.js and add the following handler function:

import prisma from "@/lib/prisma"; export default async function handler(req, res) {     const { name, email } = req.query;     try {         const newUer = await prisma.User.create({             data: {                 name,                 email,             },         });         res.json({ user: newUer, error: null });     } catch (error) {         res.json({ error: error.message, user: null });     } }

This function gets the name and email from the request. Then, in the try/catch block, it uses the create method provided by the Prisma Client to create a new user. This function returns a JSON object containing the user and an error message if any.

In one of the components, you can now make requests with this API route. For example, create a new folder named profile in the app directory and add a new page.js file . Then add a simple form, containing two input boxes for name & email and a submit button.

On the form, add an on-submit event that calls the handleSubmit function . Specifically, the code here is as follows:

"use client"; import React, { useState } from "react"; export default function Page() {     const [name, setname] = useState("");     const [email, setemail] = useState("");     const handleSubmit = async (e) => {         e.preventDefault();     };     return (                       e.target.value)}             />              e.target.value)}             />             Submit              ); }

In the handleSubmit function, use fetch to send a request to the /api/db/createuser route.

const handleSubmit = async (e) => {     e.preventDefault();     const user = await fetch("/api/db/createuser", {         ContentType: "application/json",         body: JSON.stringify({ name, email }),     }); };

Now when the form is submitted, Prisma will create a new user record in the User table.

Above is how to use Prisma to connect and query the PostgreSQL database from the Next.js app . Hope the article is useful to you.

Kareem Winters

Update 02 March 2023