Using Prisma ORM to build a Node.js application with MySQL

Photo by Sharon McCutcheon on Unsplash

Productivity is important for developers, and when it comes to building an application that interacts database, there are repetitive CRUD (Create, Read, Update, Delete) actions; these tasks can be boring for those who already know SQL and want to focus on the business logic.

Moreover, the SQL written can be exposed to SQL injection if we don’t pay attention. Another issue is when you want to change the database, you are must rewrite your SQL queries for the target database. These are problems ORMs try to solve.

There are many ORMs in the Node.js ecosystems, such as TypeORM, and today, we will focus on Prisma, which is one of the best in the class.

Using PlanetScale and TypeORM to build a Node.js REST API

Prisma features

You can manage the database migrations by defining them in a schema. ; You can use a query builder to easily write complex SQL queries. Check out this link to learn more about why Prisma is the ORM you are looking for.

When I write this, Prisma supports the following database systems: MySQL, PostgreSQL, Microsoft SQL Server, and MongoDB. Check out this link to see the supported database version.

Regarding programming languages, only JavaScript and Typescript are supported. The support of Typescript is great because you benefit from the type autocompletion.

Prisma provides various tools to improve the developer experience when working with databases.

With Prisma, you define your database schema using the Prisma Schema Language (PSL). This schema is used to generate the code related to Database Access Layer and also migrations every time you update the schema:

• Prisma Client: Auto-generated and type-safe database client for use in your application.
• Prisma Migrate: A declarative data modeling and migration tool.
• Prisma Studio: A modern GUI for browsing and managing data in your database.

Prerequisites

You must need these tools installed on your computer to follow this tutorial.

• Node.js 16+ — Download link
• NPM or Yarn
• MySQL 8

We will need Docker to run a container for MySQL 8; you can skip it if MySQL is installed on your computer. Run the command below to start the Docker container from the MySQL image:

docker run -d --rm -e MYSQL_ROOT_PASSWORD=secret -e MYSQL_DATABASE=inventory --name prisma_db -p 3307:3306 mysql:8.0

What we build

We will build a minimal product inventory system to see Prisma in action.

A product belongs to a category and can have many tags. Here is the database schema I draw with drawSQL. We will use Prisma to create migrations and then performs some CRUD operations.

Database schema of the inventory system.

Set up the project

To start, we will use a boilerplate for the Node.js project we built on this previous tutorial.

git clone https://github.com/tericcabrel/node-ts-starter.git node-prisma-mysql

cd node-prisma-mysql

yarn install

yarn start

At this step, the Node.js application should work normally.

Set up Prisma

We will first install the Prisma CLI by running the command below:

yarn add -D prisma

Check the Prisma version by running yarn prisma --version

Check the version of Prisma.

Let’s initialize Prisma by running the command below:

yarn prisma init

We got the output below:

Prisma initialized successfully.

A folder named prisma was created, with a file named schema.prisma inside where we will write the schema definition of the database.

Also, a .env file was created, which contains the connection string to the database. Let's update it to match our local database.

DATABASE_URL="mysql://root:secret@localhost:3307/inventory"

This configuration matches with the MySQL instance running by Docker.

Now, open the file schema.prisma and replace the property's value called "provider" with "mysql" (actually postgresql).

Create the Category and Tag tables

We will use the Prisma Schema Language to write the structure of our table. Check out this link to see all the capabilities of the Schema Language since we can’t cover all of them.

There is a plugin for the PSL in some editors we can use to improve the experience. Check out this link to find the instructions for your editor. I use the plugin for Webstorm.

Open the file schema.prisma and the code below:

We created the model Category and the model Tag along with their properties. Let’s explain what @map and @@map are.

When designing your database, you want to have the table’s name in plural and lowercase. The keyword @@map map, the model Category model to the database table named categories.

@map does the same thing but for the table columns.

Run the migration

Run the command below to create migration and execute it:

yarn prisma migrate dev --name create-categories-and-tags-table

This command will do two things:

1. Generate the migration for these tables (prisma generate is executed under the hood)
2. Once the migration is generated, it will be executed against the database.

Run the migration to create tables in the database.

A folder named migrations is created at the root of the project folder.

Create the Product table

Open the file schema.prisma and add the code below:

Run the migration to create the product and product’s tags table:

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

Working with the Prisma client

Now we have our database schema created. We will use the Prisma client to store data and retrieve them. Run the command below to install:

yarn add @prisma/client

Running this command might be optional for you.

Note: If you already ran yarn prisma migrate before running the command above, the Prisma client is already installed because the generation of the migration requires it, so the CLI installs it under the hood. You can open the package.json file to verify it is there.

Seeding the database

We want to add some categories and tags before we launch the application so we can have predefined values. To do that, we need to seed the database with data.

Let’s create a file seed.ts inside the folder prisma and add the code below:

Open the package.json file and this code below; it will be used by Prisma to execute the seeding.

"prisma": {
  "seed": "ts-node prisma/seed.ts"
}

Run the command below to seed the database:

yarn prisma db seed

Verify the data are inserted in the database.

Note: The property skipDuplicates prevents the insertion of data if they already exist. It is useful when you update the script with new data to be inserted and re-run it.

Insert data

Inserting data is straightforward with the Prisma client. Create a file insert-data.ts inside the src folder and add the code below:

Execute the code in the file above by running the command below:

yarn ts-node src/insert-data.ts

We get the following output:

Insert data in the database using Prisma

Note: the property reference in the product has a value, yet we didn’t provide one when inserting the data. It is because we defined it as auto-generated with a random UUID.

Update data

Create a file update-data.ts inside the src folder and add the code below:

Run the file with the command ts-node src/update-data.ts and see the output.

Delete data

Create a file delete-data.ts inside the src folder and add the code below:

Run the file with the command ts-node src/delete-data.ts and see the output.

Retrieve data

Create a file retrieve-data.ts inside the src folder and add the code below:

Run the file with the command ts-node src/retrieve-data.ts and see the output.

Conclusion

We reached the end of this tutorial, where we have seen how to use Prisma with a MySQL database. Here are some important key points:

• The Prisma Schema removes the need to create a class to map our database.
• It provides database versioning through migration.
• The Prisma client auto-generated from the schema has a good API and provides autocompletion which is essential for productivity.
• You can use Prisma studio to visualize the data stored in the database

There are many capabilities we didn’t cover, and I highly recommend checking out the Prisma documentation, which I found very well-written.

Find the source code on the GitHub repository.

This post was originally published on https://blog.tericcabrel.com

Please, follow me and subscribe to my newsletter to get notified when I publish a new post.

Happy to see you soon 😉