Photo by Daniele Levis Pelusi on Unsplash

Productivity is important for developers, and when it comes to building an application that interacts database, there is CRUD (Create, Read, Update, Delete) actions that are repetitive; this task 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 obliged to rewrite your SQL queries for the target database. These are problems ORMs try to solve.

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

Prisma features

Prisma provides features other ORMs in a different way. Database migration and Typescript support are interesting features. Prisma also provides tools to follow who throughout the development lifecycle. Check this link to know more about it.

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

About programming language, only Javascript and Typescript are supported. A Prisma client for GoLand is available in early access.

With Prisma, you define the schema of your database 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

To follow this tutorial, you need to have

  • Node.js >= 12.2
  • Typescript >= 4.1
  • Yarn >= 1.19.2
  • MySQL >= 5.6 follow this tutorial to install on your computer, or if you have Docker, launch a Docker container for MySQL 8 with this command.
docker run -it -e MYSQL_ROOT_PASSWORD=secret -e MYSQL_DATABASE=hotels --name prisma_db -p 3307:3306 mysql:8.0

What we build

To see Prisma in action, we are going to build a minimal product inventory system.

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

Setup the project

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

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

cd node-prisma-mysql

yarn install

yarn start

If everything works as expected, let's continue by installing Prisma CLI.

yarn add -D prisma

Run the command below to create the basic Prisma schema

yarn prisma init

We got the output below:

A folder named prisma was created, and inside there is a file named schema.prisma where we will write the definition of our database. Also, a file .env was created which contains the connection string to the database. Let's update to match our local database.

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

This configuration matches with the MySQL instance running by Docker. I gave the command to launch this container upper.

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

Create Category and Tags table

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

There is a plugin for PSL for some editors we can use for a better 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:

model Category {
  id        Int       @id @default(autoincrement())
  name      String    @db.VarChar(150) @unique
  createdAt DateTime  @default(now()) @map("created_at")
  updatedAt DateTime  @updatedAt @map("updated_at")

  @@map("categories")
}

model Tag {
  id        Int       @id @default(autoincrement())
  name      String    @db.VarChar(150) @unique
  createdAt DateTime  @default(now()) @map("created_at")
  updatedAt DateTime  @updatedAt @map("updated_at")

  @@map("tags")
}

We create the model Category and Tag with their properties. But let's explain what does @map and @@map.

Imagine when designing your database, you want to have the table's name in plural and lowercase. @@map Map, the Category model to a database table named categories.

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

Run the migration

Run the command to create migration and execute

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

This command will do two things:

  • Generate the migration for these tables (prisma generate Β is executed under the hood)
  • Once the migration is generated, it will be executed.

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

Create Products table

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

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

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

model Category {
  id        Int      @id @default(autoincrement())
  name      String   @unique @db.VarChar(150)
  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @updatedAt @map("updated_at")

  products Product[]

  @@map("categories")
}

model Tag {
  id        Int      @id @default(autoincrement())
  name      String   @unique @db.VarChar(150)
  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @updatedAt @map("updated_at")

  productTags ProductTag[]

  @@map("tags")
}

enum Visibility {
  VISIBLE
  HIDDEN
  FEATURED
  DEAL
}

model Product {
  id          Int        @id @default(autoincrement())
  name        String     @unique @db.VarChar(200)
  slug        String     @unique @db.VarChar(200)
  reference   String     @unique @default(uuid()) @db.VarChar(50)
  description String?    @db.Text
  price       Float      @db.Float
  isAvailable Boolean    @default(true) @map("is_available") @db.TinyInt
  viewCount   Int        @default(0) @map("view_count") @db.Int
  visibility  Visibility @default(VISIBLE)
  pictures    Json
  extras      Json       @db.Json
  createdAt   DateTime   @default(now()) @map("created_at")
  updatedAt   DateTime   @updatedAt @map("updated_at")
  category    Category   @relation(fields: [categoryId], references: [id])
  categoryId  Int        @map("category_id")

  productTags ProductTag[]

  @@map("products")
}

model ProductTag {
  product   Product @relation(fields: [productId], references: [id])
  productId Int     @map("product_id")
  tag       Tag     @relation(fields: [tagId], references: [id])
  tagId     Int     @map("tag_id")

  @@id([productId, tagId])
  @@map("products_tags")
}

Here is the final file of the schema.prisma. Run the migration:

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

Working with Prisma client

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

yarn add @prisma/client

Note: If you ran yarn prisma migrate before running this command, the Prisma client is already installed because it is required when generating the migration.

Seeding database

We want to add categories and tags before we launch the application to have predefined values. To do that, we need to seed the database with these data. Let's create a file seed.ts inside the folder prisma and add the code below:

import { PrismaClient } from '@prisma/client';
const prisma = new PrismaClient();

async function main() {
  await prisma.category.createMany({
    data: [{ name: 'Computers' }, { name: 'TV' }, { name: 'Storage' }, { name: 'Accessories' }],
    skipDuplicates: true,
  });

  await prisma.tag.createMany({
    data: [{ name: 'Apple' }, { name: 'IOS' }, { name: 'SSD' }, { name: 'Keyboard' }, { name: 'USB' }],
    skipDuplicates: true,
  });
}

main()
  .catch((e) => {
    console.error(e);
    process.exit(1);
  })
  .finally(async () => {
    await prisma.$disconnect();
  });

Run the command below to seed the database and verify the data inserted:

yarn prisma db seed --preview-feature

Note1: 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.

Note2: The flag "preview-feature" is required because this feature is still in testing.

Insert data

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

import { PrismaClient } from '@prisma/client';
const prisma = new PrismaClient();

async function main() {
  const insertResult = await prisma.tag.createMany({
    data: [{ name: 'Television' }, { name: 'Radio' }, { name: 'Printer' }],
  });

  console.log(insertResult.count); // 3

  const productCreated = await prisma.product.create({
    data: {
      name: 'Smart TV Full HD 40 Inch',
      slug: 'smart-tv-full-hd-40-inch',
      isAvailable: true,
      price: 455,
      extras: {
        width: 168,
        height: 109,
        energy: 'Class A+',
      },
      pictures: {
        'picture_one.png': 'picture_one_storage_path',
        'picture_two.png': 'picture_two_storage_path',
      },
      categoryId: 1, // Exist in the database from the seed
    },
  });

  console.log(productCreated);

  const productWithTagsCreated = await prisma.product.create({
    data: {
      name: 'Macbook Pro 2020 16 Inch',
      slug: 'macbook-pro-2020-16-inch',
      isAvailable: true,
      price: 2799,
      extras: {
        storage: '512GB',
        memory: '16GB',
        hasThunderbolt: true,
        osVersion: 11.2,
      },
      visibility: 'FEATURED',
      pictures: {
        'picture_one.png': 'picture_one_storage_path',
        'picture_two.png': 'picture_two_storage_path',
      },
      categoryId: 1,
      productTags: {
        createMany: {
          data: [{ tagId: 1 }, { tagId: 2 }],
        },
      },
    },
    include: {
      productTags: true, // include product tags in the object returned
      category: true, // also include category
    },
  });

  console.log(productWithTagsCreated);
}

main()
  .catch((e) => {
    console.error(e);
    process.exit(1);
  })
  .finally(async () => {
    await prisma.$disconnect();
  });

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

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. Check out the Prisma schema to see it.

Update data

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

const categoriesUpdated = await prisma.category.updateMany({
    where: {
      name: {
        contains: 'Iphone',
      },
    },
    data: {
      name: 'Apple Iphone',
    },
  });

  console.log(categoriesUpdated.count); // 2

  const productUpdated = await prisma.product.update({
    where: {
      id: 2,
    },
    data: {
      viewCount: {
        increment: 1, // increment by one the current value of viewCount
      },
      isAvailable: true,
      visibility: 'DEAL',
    },
    include: {
      category: true,
    },
  });

  console.log(productUpdated);

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:

const tagDeleted = await prisma.tag.delete({
    where: {
      name: 'Iphone',
    },
  });

  console.log(tagDeleted);

  const productsDeleted = await prisma.product.deleteMany({
    where: {
      viewCount: {
        lt: 5,
      },
      visibility: {
        in: ['VISIBLE', 'HIDDEN'],
      },
      OR: {
        isAvailable: false,
      },
    },
  });

  console.log(productsDeleted.count);
  
  // Delete all products
  await prisma.product.deleteMany();

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:

const tagFound = await prisma.tag.findUnique({
    where: {
      name: 'Iphone',
    },
  });

  console.log(tagFound);

  const category = await prisma.category.findFirst({
    where: {
      products: {
        some: {
          viewCount: {
            gt: 100,
          },
        },
      },
    },
    orderBy: {
      createdAt: 'desc',
    },
  });

  console.log(category);

  // Find all tags
  const allTags = await prisma.tag.findMany();

  console.log(allTags);

  // Select a subset of fields
  const partialProduct = await prisma.product.findUnique({
    where: {
      id: 2,
    },
    select: {
      id: true,
      name: true,
      reference: true,
    },
  });

  console.log(partialProduct);

  // Find distinct category
  const distinctCategories = await prisma.category.findMany({
    where: {
      products: {
        some: {
          visibility: {
            notIn: ['VISIBLE', 'FEATURED'],
          },
        },
      },
    },
    distinct: ['name'],
  });

  console.log(distinctCategories);

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.

There are many capabilities I 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.

I hope you find it interesting and see you in the following tutorial πŸ˜‰.