CloudCone

Syntax highlighting with Prism and Next.js

How to set HTML lang attribute in Next.js

Continuous integrations for VPS with GitHub actions

Using useDebounce hook with React

Beautify GitHub profile

Prisma is a wonderful tool when it comes to TypeScript **[ORMs](https://en.wikipedia.org/wiki/Object%E2%80%93relational_mapping)**, 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](https://expressjs.com/) and [Prisma](https://www.prisma.io/) with NodeJs.

Prisma is compatible with many databases like [Mongo](https://www.mongodb.com/), [MySQL](https://www.mysql.com/), [PostgreSQL](https://www.postgresql.org/), 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](https://www.postgresql.org/).
- Node.js must be installed, [download here](https://nodejs.org/en/).
- 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.


![prisma db](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/hm0gbo94k5wqemqj01sh.png)

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](https://ubiq.co/database-blog/create-user-postgresql/).

![database url prisma env](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ryt2wu71o45zyep7k60w.png)
 
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.

![SEO tactics](https://public-files.gumroad.com/wbry1qwvocjndcbm18ndor8mfrt2)


## **Creating the game collection.**

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

![prisma model](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/qjkewouuo18lsr4oa885.png)

We have used the [`uuid`](https://www.prisma.io/docs/reference/api-reference/prisma-schema-reference#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.

![prisma model](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/w8g5qjfpbzysolzva13o.png)
 
## **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.

![prisma models](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/yn2yipfgcny9mt3p7dtw.png) 

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.

```tsx
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.

```tsx
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](https://www.sumologic.com/glossary/crud/#:~:text=CRUD%20Meaning%3A%20CRUD%20is%20an,%2C%20read%2C%20update%20and%20delete.), 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.

```tsx
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.

```tsx
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.

```tsx
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.

```tsx
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.

```tsx
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",
    })
  }
})
```

![SEO tactics](https://public-files.gumroad.com/wbry1qwvocjndcbm18ndor8mfrt2)


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](https://prisma.io) at their main website.

Thanks for reading.

Share this article if you found it useful.