Intensive reading of "The Use of Prisma"

The meaning of ORM (Object relational mappers) is to establish a strong mapping relationship between the data model and the Object, so that our addition, deletion, and modification of the data can be converted into the operation of Object (object).

Prisma is a modern Nodejs ORM library. According to Prisma official document you can understand how this library is designed and used.

Overview

Prisma provides a large number of tools, including Prisma Schema, Prisma Client, Prisma Migrate, Prisma CLI, Prisma Studio, etc. The two core ones are Prisma Schema and Prisma Client, which describe the application data model and Node operation API respectively.

Unlike the general ORM that completely describes the data model by Class, Primsa uses a new syntax Primsa Schema to describe the data model, and then execute prisma generate generate a configuration file and store it in node_modules/.prisma/client . In the Node code, you can use Prisma Client to check data additions, deletions, and changes.

Prisma Schema

Primsa Schema further abstracts the association relationship on the basis of the description of the database structure as close as possible, and maintains the corresponding relationship with the data model behind it. The following figure illustrates this point well:

<img width=400 src="https://z3.ax1x.com/2021/10/17/5YwZoF.png">

It can be seen that it is almost exactly the same as the definition of the database. The only extra posts and author actually make up for the unintuitive part of the foreign keys associated with the database table. These foreign keys are converted into entity objects, so that you cannot feel the foreign keys during operation. The existence of keys or multiple tables is converted into join operations during specific operations. The following is the corresponding Prisma Schema:

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

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

model Post {
  id        Int     @id @default(autoincrement())
  title     String
  content   String? @map("post_content")
  published Boolean @default(false)
  author    User?   @relation(fields: [authorId], references: [id])
  authorId  Int?
}

model User {
  id    Int     @id @default(autoincrement())
  email String  @unique
  name  String?
  posts Post[]
}

datasource db affirms the link database information; generator client affirms the use of Prisma Client for client operations, which means that Prisma Client can actually be implemented instead; model is the core model definition.

In the model definition, you can modify the field name mapping @map @@map modify the table name mapping. By default, the field name is the same as the key name:

model Comment {
  title @map("comment_title")

  @@map("comments")
}

The field consists of the following four descriptions:

• Field name.
• Field Type.
• Optional type modification.
• Optional attribute description.

model Tag {
  name String? @id
}

In this description, the field name is name , the field type is String , the type modification is ? , and the attribute description is @id .

Field Type

The field type can be model, such as the associated type field scenario:

model Post {
  id       Int       @id @default(autoincrement())
  // Other fields
  comments Comment[] // A post can have many comments
}

model Comment {
  id     Int
  // Other fields
  Post   Post? @relation(fields: [postId], references: [id]) // A comment can have one post
  postId Int?
}

Association scenario has 1v1, nv1, 1vn, nvn four cases, may be a model name field type definition, and the use attribute describes @relation defined by the association, such as the above example, it described Commenct and Post present nv1 relationship and Comment.postId with Post.id Associated.

The field type can also be the underlying data type, @db. , such as:

model Post {
  id @db.TinyInt(1)
}

For types that Prisma does not support, you can also use Unsupported modify:

model Post {
  someField Unsupported("polygon")?
}

This type of field cannot be queried through ORM API, but it can be queryRaw through 0616cce28d0ef9. queryRaw is an ORM's support for raw SQL mode, which will be mentioned in Prisma Client.

Type modification

Type modification has ? [] , for example:

model User {
  name  String?
  posts Post[]
}

Represents optional and array respectively.

Property description

The attribute description has the following syntax:

model User {
  id        Int     @id @default(autoincrement())
  isAdmin   Boolean @default(false)
  email     String  @unique

  @@unique([firstName, lastName])
}

@id corresponds to the PRIMARY KEY of the database.

@default setting field default values can be combined using a function, such as @default(autoincrement()) , available functions including autoincrement() , dbgenerated() , cuid() , uuid() , now() , can also dbgenerated calling the underlying function database directly, such dbgenerated("gen_random_uuid()") .

@unique sets the field value to be unique.

@relation set the association, as mentioned above.

@map set the mapping, also mentioned above.

@updatedAt modified field is used to store the last update time, which is generally a built-in capability of the database.

@ignore marks invalid fields for Prisma.

All attribute descriptions can be used in combination, and there is also a description of the model level, which is generally described by two @ , including @@id , @@unique , @@index , @@map , @@ignore .

ManyToMany

Prisma has also worked hard on the description of many-to-many association relationships, and supports implicit association descriptions:

model Post {
  id         Int        @id @default(autoincrement())
  categories Category[]
}

model Category {
  id    Int    @id @default(autoincrement())
  posts Post[]
}

It seems natural, but there are many implementations hidden behind it. The database many-to-many relationship is generally realized through the third table. The third table will store the foreign key correspondence between the two tables, so if you want to explicitly define it, it is actually like this:

model Post {
  id         Int                 @id @default(autoincrement())
  categories CategoriesOnPosts[]
}

model Category {
  id    Int                 @id @default(autoincrement())
  posts CategoriesOnPosts[]
}

model CategoriesOnPosts {
  post       Post     @relation(fields: [postId], references: [id])
  postId     Int // relation scalar field (used in the `@relation` attribute above)
  category   Category @relation(fields: [categoryId], references: [id])
  categoryId Int // relation scalar field (used in the `@relation` attribute above)
  assignedAt DateTime @default(now())
  assignedBy String

  @@id([postId, categoryId])
}

The following SQL is generated behind the scenes:

CREATE TABLE "Category" (
    id SERIAL PRIMARY KEY
);
CREATE TABLE "Post" (
    id SERIAL PRIMARY KEY
);
-- Relation table + indexes -------------------------------------------------------
CREATE TABLE "CategoryToPost" (
    "categoryId" integer NOT NULL,
    "postId" integer NOT NULL,
    "assignedBy" text NOT NULL
    "assignedAt" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY ("categoryId")  REFERENCES "Category"(id),
    FOREIGN KEY ("postId") REFERENCES "Post"(id)
);
CREATE UNIQUE INDEX "CategoryToPost_category_post_unique" ON "CategoryToPost"("categoryId" int4_ops,"postId" int4_ops);

Prisma Client

After describing the Prisma Model, execute prisma generate , and then use npm install @prisma/client install the Node package, you can operate ORM in the code:

import { PrismaClient } from '@prisma/client'

const prisma = new PrismaClient()

CRUD

Use create create a record:

const user = await prisma.user.create({
  data: {
    email: '[e-mail]',
    name: 'Elsa Prisma',
  },
})

Use createMany create multiple records:

const createMany = await prisma.user.createMany({
  data: [
    { name: 'Bob', email: '[e-mail]' },
    { name: 'Bobo', email: '[e-mail]' }, // Duplicate unique key!
    { name: 'Yewande', email: '[e-mail]' },
    { name: 'Angelique', email: '[e-mail]' },
  ],
  skipDuplicates: true, // Skip 'Bobo'
})

Use findUnique find a single record:

const user = await prisma.user.findUnique({
  where: {
    email: '[e-mail]',
  },
})

For the case of the joint index:

model TimePeriod {
  year    Int
  quarter Int
  total   Decimal

  @@id([year, quarter])
}

Need to nest another layer of _ spliced by 0616cce28d11d8:

const timePeriod = await prisma.timePeriod.findUnique({
  where: {
    year_quarter: {
      quarter: 4,
      year: 2020,
    },
  },
})

Use findMany query multiple records:

const users = await prisma.user.findMany()

You can use various conditional statements in SQL, the syntax is as follows:

const users = await prisma.user.findMany({
  where: {
    role: 'ADMIN',
  },
  include: {
    posts: true,
  },
})

Use update update the record:

const updateUser = await prisma.user.update({
  where: {
    email: '[e-mail]',
  },
  data: {
    name: 'Viola the Magnificent',
  },
})

Use updateMany update multiple records:

const updateUsers = await prisma.user.updateMany({
  where: {
    email: {
      contains: 'prisma.io',
    },
  },
  data: {
    role: 'ADMIN',
  },
})

Use delete delete records:

const deleteUser = await prisma.user.delete({
  where: {
    email: '[e-mail]',
  },
})

Use deleteMany delete multiple records:

const deleteUsers = await prisma.user.deleteMany({
  where: {
    email: {
      contains: 'prisma.io',
    },
  },
})

Use include indicate whether the associated query is valid, such as:

const getUser = await prisma.user.findUnique({
  where: {
    id: 19,
  },
  include: {
    posts: true,
  },
})

In this way, when querying the user table, all the associated post tables will be queried by the way. Association queries also support nesting:

const user = await prisma.user.findMany({
  include: {
    posts: {
      include: {
        categories: true,
      },
    },
  },
})

Filters support equals , not , in , notIn , lt , lte , gt , gte , contains , search , mode , startsWith , endsWith , AND , OR , NOT , general usage is as follows:

const result = await prisma.user.findMany({
  where: {
    name: {
      equals: 'Eleanor',
    },
  },
})

This statement replaces sql's where name="Eleanor" , that is, expressing semantics through object nesting.

Prisma can also write native SQL directly:

const email = '[e-mail]'
const result = await prisma.$queryRaw(
  Prisma.sql`SELECT * FROM User WHERE email = ${email}`
)

Middleware

The way that Prisma supports middleware is expanded during execution. See the following example:

const prisma = new PrismaClient()

// Middleware 1
prisma.$use(async (params, next) => {
  console.log(params.args.data.title)
  console.log('1')
  const result = await next(params)
  console.log('6')
  return result
})

// Middleware 2
prisma.$use(async (params, next) => {
  console.log('2')
  const result = await next(params)
  console.log('5')
  return result
})

// Middleware 3
prisma.$use(async (params, next) => {
  console.log('3')
  const result = await next(params)
  console.log('4')
  return result
})

const create = await prisma.post.create({
  data: {
    title: 'Welcome to Prisma Day 2020',
  },
})

const create2 = await prisma.post.create({
  data: {
    title: 'How to Prisma!',
  },
})

The output is as follows:

Welcome to Prisma Day 2020 
1 
2 
3 
4 
5 
6 
How to Prisma! 
1 
2 
3 
4 
5 
6

As you can see, the execution order of the middleware is an onion model, and each operation is triggered. We can use middleware to expand business logic or to record operating time.

intensive reading

Two design patterns of ORM

ORM has two design modes: Active Record and Data Mapper. Active Record makes the object completely correspond to the SQL query, which is not very popular now, and the object in the Data Mapper mode does not know the existence of the database, that is, there is an extra layer in the middle. Mapping does not even need a corresponding database behind it, so some very lightweight debugging functions can be done.

Prisma uses the Data Mapper model.

ORM can easily cause performance problems

When the amount of data is large, or the performance and resources are sensitive, we need to optimize SQL, and even we need to make some seemingly meaningless statements and tuning for certain kernel errors of specific versions of Mysql. (For example, perform the same condition of IN range limitation before where), sometimes it can achieve amazing performance improvement.

ORM is based on a more idealized theory, that is, the data model can be well transformed into object operations. However, because the object operations hide the details, we cannot perform targeted tuning of SQL.

In addition, thanks to the convenience of object operations, it is easy for us to access certain properties through obj.obj., but what is generated behind this is a series of complex join sql that has not been optimized (or partially automatically optimized). We When writing these sql, performance factors will be considered in advance, but because of the low cost when calling through objects, or thinking that ORM has ideas such as magic optimization, many SQLs that are actually unreasonable are written.

Benefits of Prisma Schema

In fact, grammatically, the expansion of Prisma Schema and Typeorm based on Class + decorator can be almost equivalent conversion, but Prisma Schema has a very good advantage in actual use, that is, reducing boilerplate code and stabilizing the database model.

It is easier to understand the reduction of boilerplate code, because Prisma Schema does not appear in the code, and the stable model means that as long as prisma generate not executed, the data model will not change, and Prisma Schema also exists independently of Node, or even not placed In the project source code, in contrast, it will be more cautious to modify, and the model completely defined by Node, because it is a part of the code, may be suddenly modified, and the database structure synchronization operation is not performed.

If the project uses Prisma, after the model is changed, you can execute prisma db pull update the database structure, and then execute prisma generate update the client API. This process is relatively clear.

Summarize

Prisma Schema is a major feature of Prisma, because this part of the description is independent of the code and brings the following benefits:

1. The definition is more concise than Node Class.
2. No redundant code structure is generated.
3. Prisma Client is more lightweight, and the query returns Pure Object.

As for the API design of Prisma Client, it is not particularly prominent. Compared with sequelize or typeorm , there is not much optimization, but the style is different.

But for record creation, I prefer Prisma's API:

// typeorm - save API
const userRepository = getManager().getRepository(User)
const newUser = new User()
newUser.name = 'Alice'
userRepository.save(newUser)

// typeorm - insert API
const userRepository = getManager().getRepository(User)
userRepository.insert({
  name: 'Alice',
})

// sequelize
const user = User.build({
  name: 'Alice',
})
await user.save()

// Mongoose
const user = await User.create({
  name: 'Alice',
  email: '[e-mail]',
})

// prisma
const newUser = await prisma.user.create({
  data: {
    name: 'Alice',
  },
})

First of all, there is prisma , which is very convenient to use. In addition, from the perspective of API expansion, although Mongoose is designed more concisely, it will not be expandable when adding some conditions, resulting in unstable structure, which is not conducive to unified memory.

Prisma Client's API uniformly adopts the following structure:

await prisma.modelName.operateName({
  // 数据，比如 create、update 时会用到
  data: /** ... */,
  // 条件，大部分情况都可以用到
  where: /** ... */,
  // 其它特殊参数，或者 operater 特有的参数
})

So in general, although Prisma has not made revolutionary changes to ORM, it has done a good job in micro-innovation and API optimization, and github updates are also relatively active. If you decide to use ORM to develop projects, Prisma is still recommended. of.

In actual use, in order to avoid the performance problems caused by the clumsy SQL generated by the ORM, Prisma Middleware can be used to monitor the query performance, and the prisma.$queryRaw native SQL query can be used for places with poor performance.

The discussion address is: Intensive Reading of "The Use of Prisma" · Issue #362 · dt-fe/weekly

If you want to participate in the discussion, please click here , there is a new theme every week, weekend or Monday. Front-end intensive reading-to help you filter reliable content.

Follow front-end intensive reading WeChat public

<img width=200 src="https://img.alicdn.com/tfs/TB165W0MCzqK1RjSZFLXXcn2XXa-258-258.jpg">

Copyright statement: Freely reprinted-non-commercial-non-derivative-keep the signature ( Creative Commons 3.0 License )