Building a REST API with Prisma and express.js

Prisma is a wonderful tool when it comes to TypeScript ORMs, it’s well compatible with typescript and you don’t have to write poorly-formed SQL queries anymore.

In this tutorial, we will create a REST API with Express.js and Prisma with NodeJs.

Prisma is compatible with many databases like Mongo, MySQL, PostgreSQL, and some other databases.

In this tutorial, we will use Postgres.

There are some things that are required before you start with this tutorial.

• You’ll have to have Postgres installed on your computer, install here.
• Node.js must be installed, download here.
• You’ll have to know the basics of express.js and node.js.

Let’s get started.

First create a folder and cd into it, for example, mkdir prisma && cd prisma

Once you get into the directory run npx prisma init this will create a folder named “prisma” with some basic configurations.

Initialize a node.js application by running npm init

Install the Prisma and Prisma client by running yarn add -D prisma and yarn add @prima/client

In your datasource db configurations, make sure you choose postgresql as your database.

In order to change your database URL, you’ll have to change it from the .env file that Prisma already created for you.

What you’ll have to change is just the database name, username, and password of your Postgres database when you first installed it, if you want to create a new user, here is how.

Before building our REST API let’s first build the schemas for our database collections, we’ll have two collections, users and games for this tutorial.

Each user can have multiple games, so there are database relations involved as well. Games on the other hand can be connected to multiple users as well.

Creating the game collection.

For creating any collection with Prisma, we use the model syntax followed by the collection name.

We have used the uuid function which comes with Prisma, to generate a new id for any new entry.

We used the @unique constraint for the name so that we’ll not have multiple games with the same name.

The default for the createdAt field is now() which Prisma automatically generates when the entry is created.

We have used @updatedAt for the updatedAt field, this will also automatically be generated whenever the entry is updated.

Creating the User collection.

The user will have two rows, id, and name to keep it simple for this tutorial.

Defining table relations

Now that we defined both collections for the users and the games, it’s now time to define the relationships between them.

As mentioned before, we want a user to be able to have multiple games, and we also don’t want duplicate game entries so we want a game to be associated with multiple users as well.

Let’s define the relationships.

We just need to add two more lines of code in the schema.

Now that we have defined our schemas, it’s time to make these changes in the Postgres database as well, because this schema is nothing but a piece of code, we’ll have to tell Prisma to take these schemas and make these changes in the database as well.

For that Prisma has provided us with a command.

In the root directory of your application run npx prisma migrate dev this will make the changes to your database and migrate the changes. The migrations will be put into the folder prisma/migrations

If you encounter errors when running this command, make sure that Postgres is installed properly on your computer and the username and password that you put inside the .env file are correct.

Now our database is completely in sync with our Prisma schema, there is another command that we need to run, this one is for TypeScript it will create the types using the Prisma schema so that your application will be completely type-safe.

For that run npx prisma generate this will generate the TypeScript defenitions inside the .\node_modules\@prisma\client folder.

Now our TypeScript definitions have been created, it’s time to use the Prisma client so that we can run queries.

Create a file called prisma.ts or client.ts and write the following code.

import { PrismaClient } from "@prisma/client"

const prisma = new PrismaClient()

export default prisma

We will now import this instance into other places of our code to do database queries.

Building the REST API

Not it’s time to build an API using express.js.

Let’s build a simple express.js server in our app.ts file in the root directory.

import express from "express"
import prisma from "./prisma" // importing the prisma instance we created.

const app = express()
app.use(express.json())

const PORT = process.env.PORT || 3000

app.listen(PORT, () => console.log(`Server is running on port ${PORT}`))

Basically, in a REST API we’ll have CRUD applications, so let’s first start with creating data.

Starting with the POST route.

We will create a POST route to handle inserting new users and games into the database.

app.post("/users", async (req, res) => {
  try {
    const { name, games } = req.body

    // games is an array of string | string[]

    const newUser = await prisma.user.create({
      data: {
        name, // name is provided by the request body
        games: {
          // create or connect means if the game existed, we will use the old one
          // if not, we will create a new game
          connectOrCreate: games.map((game: string) => ({
            where: {
              name: game,
            },
            create: {
              name: game,
            },
          })),
        },
      },
    })

    res.json(newUser)
  } catch (error: any) {
    console.log(error.message)
    res.status(500).json({
      message: "Internal Server Error",
    })
  }
})

Creating the GET Route.

app.get("/users", async (req, res) => {
  try {
    const users = await prisma.user.findMany()

    res.json(users)
  } catch (error) {
    res.status(500).json({
      message: "Something went wrong",
    })
  }
})

This will return all users.

If we wanted to know which games these users have, we can just simply use the include property provided by Prisma.

app.get("/users", async (req, res) => {
  try {
    const users = await prisma.user.findMany({
      include: {
        games: true,
      },
    })

    res.json(users)
  } catch (error) {
    res.status(500).json({
      message: "Something went wrong",
    })
  }
})

This will populate the games field of all users.

Creating the PUT Route.

app.put("/users/:id", async (req, res) => {
  try {
    const { name, games } = req.body
    const { id } = req.params

    const updatedUser = await prisma.user.update({
      where: {
        id,
      },
      data: {
        name,
        games: {
          connectOrCreate: games.map((game: string) => ({
            where: { name: game },
            create: { name: game },
          })),
        },
      },
    })

    res.json(updatedUser)
  } catch (error) {
    res.status(500).json({
      message: "Something went wrong",
    })
  }
})

The DELETE Route.

app.delete("/users/:id", async (req, res) => {
  try {
    const { id } = req.params

    const deletedUser = await prisma.user.delete({
      where: {
        id,
      },
    })

    res.json(deletedUser)
  } catch (error) {
    res.status(500).json({
      message: "Something went wrong",
    })
  }
})

That’s it for our simple Prisma and Express.js REST API application.

Of course, there are many compound queries you can do with Prisma with absolute ease and readability and the fewest space for errors thanks to Prisma’s compatibility with TypeScript.

You can also check the Prisma Documentation at their main website.

Thanks for reading.

Share this article if you found it useful.