The idea behind ORM(Object Relational Mappers) is to forcefully map the data model to Object, so that our ability to add, delete, or alter data can be converted into manipulation of Object.
Prisma is a modern Nodejs ORM library. See the official Prisma documentation to see how the library is designed and used.
An overview of the
Prisma provides a large number of tools, including Prisma Schema, Prisma Client, Prisma Migrate, Prisma CLI, Prisma Studio, etc. Prisma Schema and Prisma Client are the two most important ones, which describe application data model and Node operation API respectively.
Primsa uses a new syntax, Primsa Schema, to describe data models. Prisma generate generates a configuration file stored in node_modules/. Prisma /client.
Prisma Schema
Primsa Schema abstracts the relational relationships as closely as possible on the basis of the database structure description, and maintains the corresponding relationship with the data model behind it, which is well illustrated in the following figure:
It can be seen that, almost the same as the definition of the database, the only extra posts and authors 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 the existence of foreign keys or multiple tables can not be felt in the operation, and then converted into join operation in the specific operation. Here 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[]
}
Copy the codeDatasource DB declares the linked database information. The Generator Client declares that Prisma Client is used for client-side operations, which means that Prisma Client is in fact interchangeable; The model is the core model definition.
In the model definition, @map can change the field name mapping and @@map can change the table name mapping. By default, the field name is the same as the key name:
model Comment {
title @map("comment_title")
@@map("comments")
}
Copy the codeThe field consists of the following four descriptions:
- The field name.
- Field type.
- Optional type modification.
- Optional attribute description.
model Tag {
name String? @id
}
Copy the codeIn this description, include field name, field type String, type modifier? , attribute Description @id.
The field type
The field type can be Model, such as the associative 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?
}
Copy the codeThere are four related scenarios: 1V1, NV1, 1VN and NVN. The field type can be the defined model name, and the attribute @relation is used to define the correlation. For example, here we describe the nV1 relationship between the commencement and Post. And comment.postid is associated with post.id.
Field types can also be underlying data types, as described by @db., for example:
model Post {
id @db.TinyInt(1)
}
Copy the codeFor types not supported by Prisma, you can also use the Unsupported modifier:
model Post {
someField Unsupported("polygon")?
}
Copy the codeThis type of field cannot be queried using the ORM API, but can be queried using queryRaw. QueryRaw is ORM support for raw SQL schema, which is covered in Prisma Client.
Type modified
The type modifier has? [] Two grammars, such as:
model User {
name String?
posts Post[]
}
Copy the codeRepresents optional and array respectively.
Property description
Attribute descriptions have the following syntax:
model User {
id Int @id @default(autoincrement())
isAdmin Boolean @default(false)
email String @unique
@@unique([firstName, lastName])
}
Copy the code@id Specifies the PRIMARY KEY of the database.
@default Sets the default value of the field, and can be used in conjunction with functions such as @default(autoincrement()), autoincrement(), cuID (), uuid(), now(), etc. You can also call the underlying database functions directly from dbgenerated, such as dbgenerated(“gen_random_uuid()”).
@unique sets the field value to be unique.
@relation sets the relation, which was mentioned above.
@map sets the mapping, as mentioned above.
The @updatedat modifier field is used to store the last update date, usually as a built-in database capability.
@ignore Invalid field for Prisma tag.
All attribute descriptions can be combined, and there is also a description for model level. Generally, two @ descriptions are used, including @@ID, @@unique, @@index, @@map and @@ignore.
ManyToMany
Prisma also made efforts in the description of many-to-many associations, supporting implicit association description:
model Post {
id Int @id @default(autoincrement())
categories Category[]
}
model Category {
id Int @id @default(autoincrement())
posts Post[]
}
Copy the codeIt looks natural, but there’s a lot of implementation behind it. A many-to-many relationship is typically implemented in a third table, which stores the foreign key mapping between the two tables, so if you want to explicitly define it, it looks 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])
}
Copy the codeThe following SQL is generated behind:
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);
Copy the codePrisma Client
NPM install@prisma /client: Prisma generate: NPM install@prisma /client
import { PrismaClient } from '@prisma/client'
const prisma = new PrismaClient()
Copy the codeCRUD
Create a record with create:
const user = await prisma.user.create({
data: {
email: '[email protected]'.name: 'Elsa Prisma',}})Copy the codeCreate multiple records using createMany:
const createMany = await prisma.user.createMany({
data: [{name: 'Bob'.email: '[email protected]' },
{ name: 'Bobo'.email: '[email protected]' }, // Duplicate unique key!
{ name: 'Yewande'.email: '[email protected]' },
{ name: 'Angelique'.email: '[email protected]'},].skipDuplicates: true.// Skip 'Bobo'
})
Copy the codeUse findUnique to find a single record:
const user = await prisma.user.findUnique({
where: {
email: '[email protected]',}})Copy the codeIn the case of federated indexes:
model TimePeriod {
year Int
quarter Int
total Decimal
@@id([year, quarter])
}
Copy the codeWe need to nest another layer of _ concatenated keys:
const timePeriod = await prisma.timePeriod.findUnique({
where: {
year_quarter: {
quarter: 4.year: 2020,}}})Copy the codeQuery multiple records with findMany:
const users = await prisma.user.findMany()
Copy the codeYou can use a variety of conditional statements in SQL. The syntax is as follows:
const users = await prisma.user.findMany({
where: {
role: 'ADMIN',},include: {
posts: true,}})Copy the codeUpdate records with update:
const updateUser = await prisma.user.update({
where: {
email: '[email protected]',},data: {
name: 'Viola the Magnificent',}})Copy the codeUpdate multiple records using updateMany:
const updateUsers = await prisma.user.updateMany({
where: {
email: {
contains: 'prisma.io',}},data: {
role: 'ADMIN',}})Copy the codeDelete a record using delete:
const deleteUser = await prisma.user.delete({
where: {
email: '[email protected]',}})Copy the codeUse deleteMany to delete multiple records:
const deleteUsers = await prisma.user.deleteMany({
where: {
email: {
contains: 'prisma.io',}}})Copy the codeUse include to indicate whether the associated query takes effect. For example:
const getUser = await prisma.user.findUnique({
where: {
id: 19,},include: {
posts: true,}})Copy the codeThis will query all associated POST tables along with the user table. Relational queries also support nesting:
const user = await prisma.user.findMany({
include: {
posts: {
include: {
categories: true,},},},})Copy the codeFilter criteria support equals, NOT, in, notIn, LT, LTE, GT, GTE, contains, search, mode, startsWith, endsWith, AND, OR, AND NOT.
const result = await prisma.user.findMany({
where: {
name: {
equals: 'Eleanor',}}})Copy the codeThis statement replaces SQL where name=”Eleanor”, which expresses semantics through nested objects.
Prisma can also write native SQL directly:
const email = '[email protected]'
const result = await prisma.$queryRaw(
Prisma.sql`SELECT * FROM User WHERE email = ${email}`
)
Copy the codeThe middleware
The way Prisma supports middleware is extended during execution, as shown in 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! ',}})Copy the codeThe output is as follows:
Welcome to Prisma Day 2020 1 2 3 4 5 6 How to Prisma! One, two, three, four, five, sixCopy the codeAs you can see, the middleware execution order is an Onion model and every operation is triggered. We can use middleware to extend business logic or to keep track of operation time.
Intensive reading
Two design patterns for ORM
ORM has two design patterns, Active Record and Data Mapper. Active Record makes the object completely correspond to the SQL query behind it, which is not very popular now, and the object in Data Mapper mode does not know the existence of the database. That is, there is a layer of mapping in the middle, and there is not even a corresponding database behind it, so you can do some very light debugging functions.
Prisma uses the Data Mapper pattern.
ORM is prone to performance problems
When the data volume is large, or performance or resource sensitive, we need to optimize SQL, or even we need to do some seemingly meaningless statement tuning for certain kernel errors IN a particular version of Mysql (such as IN scope before WHERE). Sometimes you can achieve amazing performance improvements.
ORM is based on an idealized theory, that is, the data model can be transformed into object operations, but object operations are shielded from details, so we can’t tune SQL specifically.
In addition, thanks to the convenience of object operation, we can easily pass obj. Obj. When we write these SQL, we consider performance factors in advance, but when we call them through objects, we think ORM has the idea of magic optimization, etc. Write a lot of SQL that actually doesn’t make sense.
Benefits of Prisma Schema
Prisma Schema is syntactically equivalent to Typeorm’s Class + decorator-based extensions, but Prisma Schema has the advantage of reducing boilerplate code and stable database models.
Reducing boilerplate code is easier to understand because Prisma Schema does not appear in the code, whereas a stable model means that the data model does not change as long as Prisma generate is not executed, and Prisma Schema exists independently of Node. It can even be left out of the project’s source code, which can be modified more discreetly, whereas models defined entirely in Node can be changed suddenly because they are part of the code and do not perform database structure synchronization.
If Prisma is used in the project, the process of executing Prisma DB pull to update the database structure and Prisma Generate to update the client API after the model changes is clear.
conclusion
Prisma Schema is a feature of Prisma, because this part of the description is independent of the code, which brings the following benefits:
- Definition is much cleaner than Node Class.
- No redundant code structures are generated.
- Prisma Client is much lighter and queries return Pure Objects.
As for the Prisma Client API design, there is no significant improvement over either Sequelize or TypeorM API design, but the style is different.
For record creation, though, 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: '[email protected]',})// prisma
const newUser = await prisma.user.create({
data: {
name: 'Alice',}})Copy the codeFirst, prisMA, the top-level variable, is very convenient to use. In addition, in terms of API expansion, although Mongoose is designed more succinct, it will be less extensible when some conditions are added, resulting in unstable structure, which is not conducive to unified memory.
The Prisma Client API uses the following structure:
await prisma.modelName.operateName({
// Data, such as create, update
data: / * *... * /.// Condition, most cases can be used
where: / * *... * /.// Other special arguments, or operater-specific arguments
})
Copy the codeSo overall, Prisma doesn’t revolutionize ORM, but it does a good job of micro-innovation and API optimization, and github updates are active, so if you decide to use ORM for your development project, Prisma is recommended.
In practice, Prisma Middleware can be used to monitor query performance and use Prisma.$queryRaw native SQL queries where performance is poor to avoid performance problems caused by ORM’s clumsy SQL.
The discussion address is: Close reading the Use of Prisma · Issue #362 · dt-fe/weekly
If you’d like to participate in the discussion, pleaseClick here to, with a new theme every week, released on weekends or Mondays. Front end Intensive Reading – Helps you filter the right content.
Copyright Notice: Freely reproduced – Non-commercial – Non-derivative – Remain signed (Creative Commons 3.0 License)