Prisma Tutorial: A Practical Exercise Based on Koa and PG

Preface

Prisma is regarded as the next-generation ORM (Object Relational Mapping) tool, developed based on TypeScript, which provides powerful type safety. This article will use Koa.js to build a simple web service and combine it with a MySQL database to demonstrate how to implement the operations of creating, reading, updating, and deleting (CRUD) data through Prisma.

Overview of Prisma

Prisma claims to be the next-generation ORM tool. Prisma is an open-source database toolchain project, and its functions are far more than just a simple ORM. It supports multiple databases, such as PostgreSQL, MySQL, MongoDB, SQL Server, and SQLite. This article will take MySQL as an example for demonstration.

When using Prisma for the first time, the process is relatively cumbersome and can be roughly divided into the following steps:

Install dependencies
Initialize the Prisma project
Design the Prisma Schema (define database information and models)
Sync with the database
Generate the Prisma Client
Use the Prisma Client to complete CRUD operations

Next, we will first set up the development environment and then introduce Prisma in depth.

Initializing the Environment

Building a Koa Web Service

First, create the project directory and install the dependencies:

mkdir koa-prisma
cd koa-prisma
pnpm init

# Install Koa dependencies
pnpm add koa @koa/router koa-bodyparser

Among them, @koa/router is a routing middleware, which is convenient for integrating routing functions; koa-bodyparser is used to parse the request body data and store it in the ctx.request.body object.

Then, create a new index.js file and build a simple web service:

const Koa = require('koa')
const Router = require('@koa/router')
const bodyParser = require('koa-bodyparser')

const app = new Koa()

// Instantiate the router and set the common route prefix to /users
const router = new Router({
  prefix: '/users'
})

app.use(bodyParser())

// Query the user list
router.get('/', async ctx => {

})

// Query a single user
router.get('/:id', async ctx => {

})

// Create a user
router.post('/', async ctx => {

})

// Update a user
router.patch('/:id', async ctx => {

})

// Delete a user
router.delete('/:id', async ctx => {

})

// Register the routing middleware
app.use(router.routes()).use(router.allowedMethods())

app.listen(3000, () => {
  console.log('The server is running on port 3000')
})

The above code defines five routing methods, corresponding to the database operations of querying, inserting, updating, and deleting. After completing the Prisma initialization and introducing the Prisma Client later, these interfaces can be implemented.

Use the nodemon command to start the service. It is recommended to install this module globally:

nodemon src/index.js

Prisma CLI

Installing Prisma Dependencies

First, install the two dependency modules of Prisma:

pnpm add -D prisma 
pnpm add @prisma/client

Among them, prisma is a CLI command used to call various functions of Prisma, such as database migration, creating the Prisma Client, etc. Execute the following command to view the usage instructions of prisma:

npx prisma --help

prisma provides seven commands:

Command	Description
init	Initialize Prisma in the application
generate	Mainly used to generate the Prisma Client
db	Manage the schema and lifecycle of the database
migrate	Migrate the database
studio	Start a web-based workbench to manage data
validate	Check whether the syntax of the Prisma schema file is correct
format	Format the Prisma schema file, which is `prisma/schema.prisma` by default

This article mainly uses three of these commands, and you can explore the remaining commands on your own.

Initializing Prisma

Execute the following command to complete the initialization:

npx prisma init

This command will create a .env file and a prisma directory in the current directory, and create a schema.prisma file in the prisma directory.

The .env file is used to store environment variables and usually contains some configuration information. The prisma directory is used to store files related to Prisma. Currently, there is only the schema.prisma file. This file is the Prisma schema file, which is used to define the database connection information and models.

Designing the Prisma Schema

Installing the VSC Plugin

Before editing the schema file, it is recommended to install the Prisma plugin in VS Code. This plugin provides features such as code highlighting, formatting, auto-completion, jump to definition, and checking for .prisma files, greatly enhancing the development experience.

Setting the Generator

Use generate to define the generator, and declare it as prisma-client-js through the provider property (currently, only this one is supported). When the prisma generate command is executed, the Prisma Client will be generated, which is used to complete the CRUD operations of data:

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

Setting the Data Source

Use datasource to define the data source and set the information required for Prisma to connect to the database. The provider specifies the type of the connected database, which is postgresql by default. We will change it to mysql. The url specifies the database URL. To keep the configuration separated, it is usually defined in the .env file and read through the env() function:

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

The database connection in the default .env file is for Postgresql:

DATABASE_URL=postgresql://johndoe:mypassword@localhost:5432/mydb?schema=public

The MySQL database connection URL consists of the following required items:

Name	Placeholder	Description
Host	HOST	The IP or domain name of the database, such as localhost
Port	PORT	The database port, such as 3306
User	USER	The database username, such as root
Password	PASSWORD	The database password
Database	DATABASE	The database name, such as mydb

According to the above rules, define the MySQL URL:

DATABASE_URL="postgresql://leapcell:leapcell123@localhost:9006/prisma"

Defining the User Model

As an ORM tool, the models in Prisma have the following functions:

Form the entities of the application domain
Map to the tables of the database (relational databases, such as PostgreSQL) or collections (MongoDB)
Form the basis of the queries in the Prisma Client API
In TypeScript, the Prisma Client provides type definitions for the models and their variants, ensuring the type safety of database access

When defining a model, Prisma's built-in utility functions such as @id() and @default() will be used. For example, @id() is used to declare the primary key, and @default() is used to set the default value. The following is the definition of a User model:

model User {
  id          Int         @id @default(autoincrement())
  name        String
  email       String      @unique
  password    String
  createdTime DateTime    @default(now()) @map("created_time")
  updatedTime DateTime    @updatedAt @map("updated_time")

  @@map("user")
}

The following points need to be noted:

The model name is the name of the created data table by default. Here, the model name is User, and the table name can be set to the lowercase user through @@map("user").
Each model must have a primary key, which is declared using @id.
Prisma will convert the field types (such as Int, String) into the corresponding types of the database (such as int and varchar).
@unique indicates a unique value constraint, and the value of the email field in the user table cannot be repeated.
To conform to the naming conventions of JS, TS, and the database, use @map() to map the naming of the creation time and update time.

Syncing the Database

For a new project, you can use the following command to sync the Prisma model to the database:

npx prisma db push

If the project already has data, prisma migrate should be used for migration, which is not covered in this article.

The prisma db push command will create the schema of the database, sync the database using the Prisma schema, and automatically execute the prisma generate command to generate the Prisma Client.

Generating the Prisma Client

The prisma generate command has been executed when syncing the database. Later, if the Prisma Schema file changes, such as modifying the model, this command needs to be executed again to regenerate the Prisma Client.

CRUD Operations

Initializing the Prisma Client

With the Prisma Client, CRUD operations can be executed. The initialization code is as follows:

const { PrismaClient } = require('@prisma/client')

const prisma = new PrismaClient()

The Prisma Client instance prisma has rich types, and the usage is prisma.model.CRUD method. The commonly used APIs include:

findMany: Query multiple records
findUnique: Query a single record
create: Create a record
update: Update a record
delete: Delete a record

Using the API to Complete Interface Development

Querying the User List

When no parameters are passed into findMany, it queries all the records in the entire user table and returns an array of User model instances:

router.get('/', async ctx => {
  const users = await prisma.user.findMany()

  ctx.body = users
})

Querying a Single User

Set the query conditions through where in the findUnique method, and query a user record according to the specified ID, returning a User model instance:

// Query a single user
router.get('/:id', async ctx => {
  const id = parseInt(ctx.params.id)
  const user = await prisma.user.findUnique({
    where: { id }
  })

  ctx.body = user
})

It should be noted that the ID obtained from ctx.params.id is of the string type, and it needs to be converted to the integer type before querying.

Creating a User

Use the create method to insert data, and assign the data parsed from the request body (an object describing the User model) to the data property:

router.post('/', async ctx => {
  const user = ctx.request.body
  const newUser = await prisma.user.create({
    data: user
  })

  ctx.body = newUser
})

Updating a User

Use the update method, set the query conditions through where, and after querying the target user, assign the data to be updated to data:

router.patch('/:id', async ctx => {
  const id = parseInt(ctx.params.id)
  const updateUser = ctx.request.body

  const user = await prisma.user.update({
    where: {
      id
    },
    data: updateUser
  })

  ctx.body = user
})

Deleting a User

Use the delete method and set the query conditions for the records to be deleted through where:

router.delete('/:id', async ctx => {
  const id = parseInt(ctx.params.id)
  const user = await prisma.user.delete({
    where: {
      id
    }
  })

  ctx.body = user
})

When using the update and delete methods, be sure to set the where condition. Otherwise, all the records in the data table will be updated or deleted, which is risky.

Conclusion

This article demonstrates the basic usage of Prisma in a Koa project through an example of creating, reading, updating, and deleting users. The whole process can be summarized as follows: