Create a Node.js REST API using Prisma ORM and PlanetScale

Photo by Bram Bergers / Unsplash
Photo by Bram Bergers / Unsplash

A backend API is the part of the system handling the business logic and the data persistence in a database. It allows client-side applications to send requests for displaying data to users. To make the integration with API more straightforward to understand, the REST architecture is popular for describing how to design your API routes.

This post will show you how to build a REST API in Node.js using Prisma and PlanetScale.

Create a REST API with Node.js, Express, MongoDB and Typescript
In this post, we will build a REST API for a user management application in Node.js and Typescript. Connect to a MongoDB database and store the data.

What we will build

A blogging platform wants to expose a public API to its users so that they can integrate it with third-party services. The API must allow users to create, edit, and search posts.

The table below summarizes the list of endpoints the API will expose:

API Endpoint Description
[POST] /posts Create a new blog post
[PUT] /posts/{id} Update a post (title, content, status, isFeatured)
[DELETE] /posts/{id} Delete a post
[GET] /posts Search posts (by user, status, keyword)
[GET] /posts/{id} Retrieve a post

Database schema

We need a database to store information about users and blog posts. Below is a simplified version of the database schema required to build the REST API for our blog system.

The database schema of the system.
The database schema of the system.

Prerequisites

To follow this tutorial, make sure you have the following tools installed on your computer.

  • Node.js 20.6 or higher - Download link
  • NPM or Yarn (I will use Yarn)
  • A PlanetScale account - We will create one in the next section.
  • An HTTP client for consuming the API; I will use Postman.
  • Docker, to run a shadow database for Prisma migrations.

Set up a PlanetScale database

PlanetScale is a fully managed MySQL Platform providing advanced features to provide a performant MySQL database to run your backend applications. It also makes the developer's experience straightforward in handling database migrations or working locally with a database.

PlanetScale doesn't provide a free tier, but the Pro plan gives you a 10$ credit to try it; it will be enough to follow this post and convince you to stay with it ๐Ÿ˜‰.

To create a PlatnetScale account, go to the Pricing page, and click on "Get Started"

The PlanetScale pricing page.
The PlanetScale pricing page.

You will be redirected to the registration page, where you can create an account through email or with GitHub. We will use the email method, so fill in an email and password, then click on "Sign up"

The PlanetScale sign-up page.
The PlanetScale sign-up page.

You will receive an email to confirm your account; proceed to confirmation, and once done, you will see the PlanetScale welcome page where you can create or import a database.

The PlanetScale welcome page.
The PlanetScale welcome page.

Click on the button to create a database. You are redirected to a page to configure your database.

  1. Enter the database name.
  2. Select a region to deploy your database.
  3. Select the database cluster size; you can keep the preselected one.
  4. Check the box to get the 10$ usage credit so you can test it.
PlanetScale database instance configuration.
PlanetScale database instance configuration.

Scroll down and add a credit card; this is required to prevent abusive usage of the platform.

Enable credit usage and set up a credit card.
Enable credit usage and set up a credit card.

Once the card information is added, click the button to create the database.

On the next page, you can see the notice about the database being initialized at the bottom right. On the page, click the button to create the database password.

This action will output the database user name and the password; copy and save them somewhere.

Next, you are asked to select your framework; we will use Prisma, so choose it. Instructions on using PlanetScale with Prisma will be displayed; you can ignore them as we will do it here.

Select Prisma as the framework to use with PlanetScale.
Select Prisma as the framework to use with PlanetScale.

At the bottom page, click on the button to go to your dashboard. You can see your newly created database.

The last step is to protect the main database branch by enabling safe migrations. On the left menu, click on the "Branches" menu item.

The PlanetScale user dashboard page.
The PlanetScale user dashboard page.

The page displays the branches list for the database; the main branch is the only one; click on it:

View the database branches list.
View the database branches list.

The page displays the branch details; let's click the button to enable safe migrations.

Display the database main branch detail.
Display the database main branch detail.

On the modal dialog displayed, check the "Enable safe migrations" button and save the settings.

Enable safe migrations on the database's main branch.
Enable safe migrations on the database's main branch.

We are all set!

Install the PlanetScale CLI

To enhance the developer experience while working locally with the MySQL database, PlanetScale provides a CLI tool that allows you to do almost all the tasks you can do in the Web console.

Check out the installation guide for Linux and Windows users. For MacOS users, run the following commands:


brew install planetscale/tap/pscale

brew install mysql-client

The MySQL client installation is optional as it only required to run some commands with the PlanetScale CLI.

Once the installation is completed, check the version using the command pscale --version.

Verify the PlanetScale CLI version.
Verify the PlanetScale CLI version.

Create the Node.js Project

To initialize a project, we will use the Node.js starter project we built on this tutorial. The code is available on the GitHub repository.

We will clone the branch named "express" to have the Express framework already configured. Run the commands below to set up and run the project locally


git clone https://github.com/tericcabrel/node-ts-starter.git -b express node-prisma-rest-api

cd node-prisma-rest-api

cp .env.example .env

yarn install

yarn start

The application will start on http://localhost:4500.

Build the database schema with Prisma

Let's install the Prisma CLI and use it to quickly initialize Prisma in the project. Run the commands below:


yarn add -D prisma

yarn prisma init

This command will create a Prisma schema and a .env file to set the environment variables to inject into the application.

Replace the content of the file prisma/schema.prisma with the code below to set the provider to MySQL.


generator client {
  provider = "prisma-client-js"
}

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

Define the database schema with Prisma

Update the file prisma/schema.prisma with the code below:


generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider          = "mysql"
  url               = env("DATABASE_URL")
  relationMode      = "prisma"
}

enum PostStatus {
  draft
  published
  archived
}

model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique @db.VarChar(100)
  password  String   @db.VarChar(100)
  fullName  String   @map("full_name")
  birthDate DateTime @map("birth_date") @db.Date
  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @updatedAt @map("updated_at")
  posts     Post[]
}

model Post {
  id         Int        @id @default(autoincrement())
  title      String
  slug       String
  content    String     @db.Text
  status     PostStatus @default(draft)
  viewCount  Int        @default(0) @map("view_count")
  isFeatured Boolean    @default(false) @map("is_featured")
  userId     Int        @map("user_id")
  createdAt  DateTime   @default(now()) @map("created_at")
  updatedAt  DateTime   @updatedAt @map("updated_at")
  author     User       @relation(fields: [userId], references: [id], onDelete: Cascade)
}

Generate and execute the database migrations

Now that we have the Prisma schema from our local computer, we want to:

  1. Generate the SQL queries to create the database tables
  2. Execute the SQL queries on our database in PlanetScale.

Using the PlanetScale CLI, we can connect to a remote database branch by creating a tunnel from our local computer to the remote branch.

Open a terminal and run the following commands:


pscale auth login

pscale branch create teco-blog create-main-table

# Wait for a few seconds for the database branch to be ready

pscale connect teco-blog create-main-table

We authenticate from the CLI, then create a new database branch named "create-main-table" and finally create a tunnel from our local machine to the remote database branch.

Connect to a database branch using the PlanetScale CLI.
Connect to a database branch using the PlanetScale CLI.

The database is accessible through port 3306, and the connection URL is mysql://root@localhost:3306/teco-blog

Open the .env file and replace the database value of the DATABASE_URL field with the above value.


DATABASE_URL=mysql://root@localhost:3306/teco-blog

To generate and execute the migration in a PlanetScale database using Prisma, we must have a second database instance we call shadow.

I wrote a complete post to explain why; read it to learn more

Why you need a shadow database when using the Prisma ORM
When running a Prisma migration on a cloud-hosted database, you need a shadow database to see your migration succeed. In this post, we will see why it is required.

We will use Docker to run a container of a MySQL database; run the command below:


docker run -d -e MYSQL_ROOT_PASSWORD=secret -e MYSQL_DATABASE=blog --name blog-shadow-db -p 3307:3306 mysql:8.0.37

Based on the above command we ran, the connection string is: mysql://root:secret@localhost:3307/blog

Open the .env file, add a field named SHADOW_DATABASE_URL, and set the value to the connection URL of the MySQL database instance running in Docker.


SHADOW_DATABASE_URL=mysql://root:secret@localhost:3307/blog

Update the schema.prisma file to set the shadow database URL


datasource db {
  provider          = "mysql"
  url               = env("DATABASE_URL")
  shadowDatabaseUrl = env("SHADOW_DATABASE_URL")
}

Run the command below to generate and execute the database migrations


yarn prisma migrate dev --name create-main-table

We get the following output

Generate and execute the database migration from the Prisma schema.
Generate and execute the database migration from the Prisma schema.

The database is created; we can build the API routes and interact with the database.

Connect to the database from the application

In the project root directory, create a file "src/database.ts" and add the code below:


import { PrismaClient } from '@prisma/client';

export const prismaClient = new PrismaClient();

Edit the file "src/index.ts" by adding the code to connect the database when the application starts.



import express from 'express';
import { prismaClient } from './database';

const HOST = process.env.HOST || 'http://localhost';
const PORT = parseInt(process.env.PORT || '4500');

const app = express();

app.use(express.urlencoded({ extended: true }));
app.use(express.json());

app.get('/', (req, res) => {
  return res.json({ message: 'Hello World!' });
});

app.listen(PORT, async () => {
  await prismaClient.$connect();

  console.log(`Application started on URL ${HOST}:${PORT} ๐ŸŽ‰`);
});

Run the application with the command yarn start

Seed the database with some users

Creating a post requires a user, which is the author. Because we will focus on creating API routes for blog posts, let's seed the database with some users.

Create a file prisma/seed.ts and add the code below:


import { Prisma, PrismaClient } from '@prisma/client';

const prisma = new PrismaClient();

const main = async () => {
  const usersInput: Prisma.UserCreateManyInput[] = [
    {
      fullName: 'Bob',
      email: 'bob@email.com',
      birthDate: new Date('1995-10-11'),
      password: 'bob-secret',
    },
    {
      fullName: 'Alice',
      email: 'alice@email.com',
      birthDate: new Date('1996-02-10'),
      password: 'alice-secret',
    },
  ];

  await prisma.user.createMany({
    data: usersInput,
  });
};

main().then(async () => {
  await prisma.$disconnect();
});

Run the following command to seed the users in the database.


yarn ts-node prisma/seed.ts

After the execution, you will see two users in the database.

Browse the database tables using Prisma Studio.
Browse the database tables using Prisma Studio.

The Prisma CLI provides this database viewer in the browser; run the command yarn prisma studio to launch it.

Create the endpoint to create a post

The first API route allows users to create a post; let's create a file "src/post.controller.ts" and add the code below:


import { PostStatus } from '@prisma/client';
import { Request, Response } from 'express';
import * as y from 'yup';

import { prismaClient } from './database';

const createPostBodySchema = y.object({
  authorId: y.number().required(),
  title: y.string().required(),
  content: y.string().required(),
  isFeatured: y.boolean(),
  status: y.string().oneOf([PostStatus.draft, PostStatus.published, PostStatus.archived]),
});

export const createPost = async (req: Request, res: Response) => {
  try {
    const payload = createPostBodySchema.validateSync(req.body, {
      abortEarly: false,
      stripUnknown: true,
    });

    const post = await prismaClient.post.create({
      data: {
        title: payload.title,
        content: payload.content,
        isFeatured: payload.isFeatured,
        slug: payload.title.toLowerCase().replace(/ /g, '-'),
        status: payload.status,
        userId: payload.authorId,
      },
    });

    return res.json({ data: post });
  } catch (e) {
    const error = e as y.ValidationError;

    return res.status(422).json({ errors: error.errors });
  }
};


We validate the request body payload using the Node.js library Yup; run the command to install it:


yarn add yup

Read my blog post below to learn more about validating request payload with Yup.

Validate request body and parameter in a Node.js Express API
In this post, we will see how to validate the request body sent to an API route built in Node.js with Express Framework.

We now need to link the controller function to the API route; let's create a file "src/post.route.ts" and add the code below:


import { Router } from 'express';
import { createPost } from './post.controller';

export const postRoute = () => {
  const router = Router();

  router.post('/posts', createPost);

  return router;
};


Now, calling the route http://localhost:4500/posts with the POST method will execute the function createPost().

Load Express routes declared in the function postRoute() in the "src/index.ts" file.


import express from 'express';
import { prismaClient } from './database';
import { postRoute } from './post.route';

const HOST = process.env.HOST || 'http://localhost';
const PORT = parseInt(process.env.PORT || '4500');

const app = express();

app.use(express.urlencoded({ extended: true }));
app.use(express.json());

app.use('/', postRoute);

app.get('/', (req, res) => {
  return res.json({ message: 'Hello World!' });
});

app.listen(PORT, async () => {
  await prismaClient.$connect();

  console.log(`Application started on URL ${HOST}:${PORT} ๐ŸŽ‰`);
});

Re-run the application, use an HTTP client to send a POST request, and appreciate the result.

Call the API route to create a post.
Call the API route to create a post.

Create the endpoint to retrieve all the posts

Update the file "src/post.controller.ts" to add the code below:


export const getPosts = async (_req: Request, res: Response) => {
  const posts = await prismaClient.post.findMany({
    orderBy: {
      createdAt: 'desc',
    },
  });

  return res.json({ data: posts });
};

Update the file "src/post.route.ts" to link the route to the controller function.


router.get('/posts', getPosts);

Re-run the application, add some posts, and call this route to retrieve all of them.

Call the API route to retrieve all the posts.
Call the API route to retrieve all the posts.

Create the endpoint to retrieve a post

Update the file "src/post.controller.ts" to add the code below:


export const getPost = async (req: Request, res: Response) => {
  const { id } = req.params;

  const post = await prismaClient.post.findUnique({
    where: {
      id: parseInt(id, 10),
    },
  });

  if (!post) {
    return res.status(404).json({ message: `Post with id "${id}" not found.` });
  }

  return res.json({ data: post });
};

Update the file "src/post.route.ts" to link the route to the controller function.


router.get('/posts/:id', getPost);

Create the endpoint to update a post

Update the file "src/post.controller.ts" to add the code below:


const updatePostBodySchema = y.object({
  authorId: y.number().required(),
  title: y.string().required(),
  content: y.string().required(),
  isFeatured: y.boolean(),
  status: y.string().oneOf([PostStatus.draft, PostStatus.published, PostStatus.archived]),
});

export const updatePost = async (req: Request, res: Response) => {
  const { id } = req.params;

  try {
    const payload = updatePostBodySchema.validateSync(req.body, {
      abortEarly: false,
      stripUnknown: true,
    });

    const post = await prismaClient.post.findFirstOrThrow({ where: { id: parseInt(id, 10) } });

    if (!post) {
      return res.status(404).json({ message: `Post with id "${id}" not found.` });
    }

    if (post.userId !== payload.authorId) {
      return res.status(403).json({ message: 'Only the author can edit the post.' });
    }

    const updatedPost = await prismaClient.post.update({
      where: {
        id: parseInt(id, 10),
      },
      data: {
        title: payload.title,
        content: payload.content,
        isFeatured: payload.isFeatured,
        slug: payload.title.toLowerCase().replace(/ /g, '-'),
        status: payload.status,
        userId: payload.authorId,
      },
    });

    return res.json({ data: updatedPost });
  } catch (e) {
    const error = e as y.ValidationError;

    return res.status(422).json({ errors: error.errors });
  }
};

Update the file "src/post.route.ts" to link the route to the controller function.


router.put('/posts/:id', updatePost);

Create the endpoint to delete a post

Update the file "src/post.controller.ts" to add the code below:


export const deletePost = async (req: Request, res: Response) => {
  const { id } = req.params;

  await prismaClient.post.delete({
    where: {
      id: parseInt(id, 10),
    },
  });

  return res.status(204).send();
};

Update the file "src/post.route.ts" to link the route to the controller function.


router.delete('/posts/:id', deletePost);

Test all the API routes

To test all the API routes we created, we will add a post, retrieve the list, retrieve a post, update the post, and finally delete the post. The video shows all these actions.

0:00
/1:24

Demo of all the API routes for managing blog posts.

Wrap up

In this post, we saw how to build a REST API in Node.js using Prisma and PlanetScale.

PlanetScale provides the database infrastructure and a great developer experience working with MySQL databases. Prisma makes it easy for your Node.js application to interact with the database.

Here are some actions you can do to go further and build a powerful API:

You can find the code source on the GitHub repository.

Follow me on Twitter or subscribe to my newsletter to avoid missing the upcoming posts and the tips and tricks I occasionally share.