Writing a RESTful micro-service API using NodeJS+TypeScript+ExpressJS+Prisma — Part 2

In the previous chapter, we setup our development environment and project directory structure to build an awesome Restful API micro-service. If you haven’t read it already, make sure your go through the Part 1 of this tutorial before proceeding any further.

In this part of the series, we will learn how to use an Object Relational Mapping (ORM) toolkit like Prisma to connect and query the database of our choice. Prisma is an open-source project which has three major components—

1. Prisma Client — This is the programming interface which will be used to connect and query the database. Its an auto-generated query builder for Node.js based on TypeScript which also makes it type-safe.
2. Prisma Migrate — Its an imperative schema migration tool that helps to keep our databases in sync with the models inside Prisma Schema. Every project which intends to use Prisma should create a schema file where you can define the database models for the application project. This file also contains the database connection info and the generator.
3. Prisma Studio — It is an intuitive GUI to view and edit data in the database. We shall not cover it in this tutorial.

To use Prisma, we must create a Prisma Schema file first. Its typically called prisma.schema and contains the data source information, model & relation information and the generator information. We will use Prisma CLI to initialize a prisma project for us automatically.

But first let’s install a required npm package. This shall install the Prisma CLI for us.

npm i prisma --save-dev

Next you must change directory to the node-api-demo project folder we created in Part 1 and issue the following command from the command prompt or terminal:

npx prisma init

This will initialize the project for Prisma. In other words, it will create a prisma directory in the project’s root directory and the schema.prisma file inside it. It will also create an .env file where we can define the database connection url, etc.

Now, open the schema file in an editor and copy the following code into it.

As you can see, this file is pretty straight-forward. At the very beginning we define the db connection information. In this example, we shall use sqlite as our database provider. However you may choose to use a more production grade databases like PostgreSQL DB, Maria DB or MongoDB. The database url is picked from the DATABASE_URL key defined inside the .env file. Since we are using a file based provider, it looks something like this.

# .env 
DATABASE_URL=file:./test.db

If you prefer to use PostgresSQL instead, you have to change your DATABASE_URL string like so:

DATABASE_URL=postgresql://test:test@localhost:5432/test

In the schema file, we have defined three models — User, ShoppingPreference, PasswordRequest. The User model stores the user information including their password hash. The ShoppingPreference stores a list of preference text of type String. We shall discuss about PasswordRequest model later.

Many-to-many Relationship

For every user there could be 0 or more shopping preferences and every preference can be added to 0 or more users. Therefore we are having a many-to-many relationship in this case.

In DBMS, we usually have a relation table to maintain relationship between two entities. In Prisma there are two ways to define such a relationship:

1. Explicit many-to-many — In this approach,the relation table is defined as a model in the schema file. So there will be three models in total. We shall use the second approach instead.
2. Implicit many-to-many —In this approach, Prisma manages the underlying relation table and we just have to define two models in the schema file. In our example, we will use implicit many-to-many relationship. We just need to define relation fields as list on both sides of the relation. For example, there is a many-to-many relationship between the User and ShoppingPreference models which is maintained by the relation fields users and preferences (as shown above).

Sync schema with DB

The beauty of Prisma is its ability to create the client API automatically and to sync the Prisma schema models with the database schema. We shall use the Prisma Migrate tool to help us map our data model to database schema.

Just execute the following command from the root project directory on the terminal and we are done. The tables and relationships will be created in the database for you.

prisma migrate dev --name init

Use the init flag to tell Prisma that this is our first migration. Prisma also maintains migration history for us under the migrations folder.

migrations/   
└─ 20210313140442_init/     
   └─ migration.sql

Every time you change the schema.prisma file, make sure to run a new migration from the command line like so:

prisma migrate dev --name <some_label_for_the_new_changes>

This will update the migration history files and sync our database for us. Isn't that cool?

Prisma Client

The next step is to create the model classes. To generate the client API, we first need to install the @prisma/client package from the command prompt like so:

npm install @prisma/client

The install command automatically invokes the prisma generate command for us. Once this executes, you will see the installed package at the following path node_modules/@prisma/client. I highly insist you explore this path and find the generated model definitions.

Remember to manually invoke the prisma generate command any time you change the schema file to update the client API.

Interacting with the Database

Now lets see how we can interact with the database using the Prisma Client API. We will use the User model for demonstration.

But first, we must import and create the client as shown below.

We shall now create a User record using the newly defined Prisma Client object. All you need is to call the create function with a valid payload. You will notice that we use the await keyword as it is an asynchronous call.

Let’s see how we may read the data we just saved in the last step.

For reading we filtered on the email and used the findUnique function provided by the client API.

To update the object, we use the APIs update function like so.

As you can observe, we set a filter on email using the where parameter. Pretty simple, huh!

Finally to delete an entity, we will use the delete function.

We have done the CRUD operations on a model using a very simple set of functions. That’s the beauty of Prisma.

Transactions

To execute a set of operations as a single unit, we make use of transactions. Either all the steps must execute together as a whole or none at all.

In our example, we make use of transaction to update user preferences.

First we disconnect the old preferences as shown in the function disconnectOldPreferences. Then we connect the new preferences passed as an array using the connectNewPreferences function. Finally, we wrap both the functions inside a transaction. We must pass these functions in an array to the prisma.$transaction() function.

In the connectNewPreferences function, we use the parameter connectOrCreate which either connects a record to an existing related record by ID or unique identifier or creates a new related record if the record does not exist.

Service Layer

As discussed in the previous chapter, we introduced a service layer for each model and will keep all the DB operations inside it. Here’s the full code for the UserService class.

Securing Password

We have used the bcrypt package to create password hash. Its a good idea to maintain all secret codes as hash inside the database and do not store it in plain text for security reasons.

It’s pretty simple to create such a hash as shown below.

For the curious types, you can read more about bcrypt and creating hashes in this article : How To Safely Store A Password

With this we come to the end of the second part of the tutorial. If you haven’t read the first part already, follow this link. In the final part, we will build the controllers and connect it with our service layer to complete the application.

See you in the next chapter.