In the previous article, we started with the traditional ORMs of the NodeJS community, introduced their characteristics and the Active Record and Data Mapper patterns of the traditional ORM, and moved on to Prisma’s environment configuration, basic usage, and single-table practices. In this article, we will cover Prisma’s multi-table, multi-table cascading, multi-database implementation, and Prisma’s collaboration with GraphQL. At the end of the article, we will briefly talk about Prisma to help you get a sense of what Prisma is good for. When should YOU use Prisma?
Prisma Multi-table, multi-database combat
In most cases, there will be more than one table in our database, and operations under multiple tables (cascades, transactions, etc.) are also important indicators of how easy an ORM is to use. Prisma also performs well in this respect. Like the singlet examples in the previous article, Prisma also provides the ability to manipulate cascades in a concise syntax.
Prisma multi-table
See multi-Models for sample code for this section
We first defined multiple data tables in Prisma Schema, and the cascading relationships among various entities were as follows:
- User -> Profile 1-1
- User -> Post 1-m
- Post -> Category m-n
model Category { id Int @id @default(autoincrement()) name String posts Post[] } model Post { id Int @id @default(autoincrement()) postUUID String @default(uuid()) title String content String? published Boolean @default(false) createdAt DateTime @default(now()) updatedAt DateTime @updatedAt author User @relation(fields: [authorId], references: [id]) authorId Int categories Category[] } model Profile { id Int @id @default(autoincrement()) bio String? profileViews Int @default(0) user User? @relation(fields: [userId], references: [id]) userId Int? @unique } model User { id Int @id @default(autoincrement()) name String @unique age Int @default(0) posts Post[] profile Profile? avaliable Boolean @default(true) createdAt DateTime @default(now()) updatedAt DateTime @updatedAt }Copy the codeHere we focus on how Prisma joins entities, and it is obvious that the code should read:
posts Post[]
profile Profile?
Copy the codeIn the relationship between owners (in one-to-one, one-to-many relationship, often think that there is only one owner, and in a many-to-many relationship, often assume that each owner) we only need to define the field and the field on behalf of the entity, and in the relationship of the other party, we need to use @ function of prisma syntax to describe this field representation, the relationship between such as
user User? @relation(fields: [userId], references: [id])
userId Int? @unique
Copy the code- The fields property is in the current table,
userIdanduserMust be consistently optional/required, that is, both are optional or both are required. - The REFERENCES attribute is on the other side of the relationship and represents the relationship
userIdCorresponding field. - In addition to the fields and reference attributes, you can also use the name attribute to explicitly specify the name of the relationship, which can avoid ambiguity in some situations.
- In one-to-one, one-to-many relationships,
@relationIt has to be used.
In many-to-many relationships, such as Post and Category, you can declare cascading relationships without using @relation, which will automatically use @IDS in both tables to establish cascading relationships. If you think this implicit specification might be confusing or you need extra customization, you can also use an extra table and use @relation to establish one-to-many relationships with Post and Category, respectively.
After creating the schema, execute YARN generate: Multi to generate Prisma Client and you can start using it.
The above cascade would look something like this if expressed in object form:
const user = {
profile: {}posts: {
categories: {}}}Copy the codeTherefore, in Prisma we operate the tables in a similar way:
const simpleIncludeFields = {
profile: true.posts: {
include: {
categories: true,}}};const createUserWithFullRelations = await prisma.user.create({
data: {
name: randomName(),
age: 21.profile: {
create: {
bio: randomBio(),
},
},
posts: {
create: {
title: randomTitle(),
content: "Dove buy".categories: {
create: [{ name: "NodeJS" }, { name: "GraphQL"}],},},},},include: simpleIncludeFields,
});
Copy the codeLet’s look at the different parts of a single table operation:
- By default, the return result does not contain cascading relationships, but only scalars of the entity itself, such as
'prisma.user.xxx'The returned result will only contain the fields of the User entity itself, except for the cascade. - Prisma provides connect, Create, and connectOrCreate methods for connecting to existing cascated entities, creating new cascated entities, and dynamic judgment, respectively, if you want to manipulate multiple cascated relationships as in the above example.
ConnectOrCreate can be used as follows:
const connectOrCreateRelationsUser = await prisma.user.create({
data: {
name: randomName(),
profile: {
connectOrCreate: {
where: {
id: 9999,},create: {
bio: "Created by connectOrCreate",}}},posts: {
connectOrCreate: {
where: {
id: 9999,},create: {
title: "Created by connectOrCreate",},},},},select: simpleSelectFields,
});
Copy the codeWe used non-existent ids for profile and POST lookups, so Prisma automatically creates cascading entities for us.
After looking at cascading creation, let’s look at cascading update operations, one to one:
const oneToOneUpdate = await prisma.user.update({
where: {
name: connectOrCreateRelationsUser.name,
},
data: {
profile: {
update: {
bio: "Updated Bio",},// update
// upsert
// delete
// disconnect(true)
// create
// connect
// connectOrCreate}},select: simpleSelectFields,
});
Copy the codePrisma directly provides a set of convenient methods to override most cases (I haven’t found any yet). Create, connect, and connectOrCreate also exist in the user.update methods. Disconnect has also been added to disconnect the cascading relationship.
The one-to-many update is a little bit different:
const oneToMnayUpdate = await prisma.user.update({
where: {
name: connectOrCreateRelationsUser.name,
},
data: {
posts: {
updateMany: {
data: {
title: "Updated Post Title",},where: {},},// Set and many, and each option type
// set: [],
// update
// updateMany
// delete
// deleteMany
// disconnect: [
/ / {
// id: 1,
/ /},
/ /,
// connect
// create
// connectOrCreate
// upsert}},select: simpleSelectFields,
});
Copy the codeYou can use update/delete to update all the cascading relationships in one shuttle, such as upgrading the VIP level of users, updating the exposure rate of all users’ articles; You can also use updateMany/deleteMany to make refined changes to the eligible cascade entities. Alternatively, you can simply use the set method to override all of the cascading entity relationships (if set is [], all of the user’s cascading article relationships will disappear).
As for many-to-many update operations, similar to the one-to-many batch update above, the expansion will not be done here.
Multiple table cascades
See multi-Models-Advanced for the code for this section.
I won’t go into too much detail in this section, since this article is still an introductory series. But I’ve provided examples in the GitHub repository, so you can check them out if you’re interested.
Here’s a quick summary of the code repository example:
- Autocorrelation, the cascading relationships we have seen before are from different entities, and in fact associations between the same entities are common. For example, when a User invites other users, the invited User and the inviter of the current User are both operations of the User entity.
- Use intermediate tables to construct many-to-many relationships like the Post and Category relationships mentioned above. In Prisma you can also define an additional CategoriesOnPosts table that explicitly applies cascading information. How do you do CRUD in this case?
- Fine-grained operator, entity level every/some/none, scalar level contain/startsWith/endsWith/equals /… Prisma also supports JSON Filters to filter JSON data directly (not shown in this example).
I need to do something about that
Multiple Prisma Client
See multi-Clients for this code.
Due to Prisma’s unique usage, you can easily imagine that it is very easy to create multiple Prisma clients to connect to different databases, and there is no significant difference between using a single Prisma Client. Other ORMs require a little more juggling, such as TypeORM requiring the creation of multiple connection pools (each connection in TypeORM is not a “single” connection, but rather a connection pool).
In this example, we use the key-value form, where one client stores the Key and the other stores the Value:
model Key {
kid Int @id @default(autoincrement())
key String
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
}
model Value {
vid Int @id @default(autoincrement())
key String
value String
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
}
Copy the codeThe above model definitions are stored in two schema files
In NPM scripts, I have the related script ready to run YARN generate: multi-DB.
The actual use is no different from that of a single Client:
import { PrismaClient as PrismaKeyClient, Key } from "./prisma-key/client";
import { PrismaClient as PrismaValueClient } from "./prisma-value/client";
const keyClient = new PrismaKeyClient();
const valueClient = new PrismaValueClient();
Copy the codeFirst create a key (based on the UUID), then create a value based on the key:
const key1 = await keyClient.key.create({
data: {
key: uuidv4(),
},
select: {
key: true,}});const value1 = await valueClient.value.create({
data: {
key: key1.key,
value: "The forest does not cross.",},select: {
key: true.value: true,}});Copy the codeIt’s really just like a single client, isn’t it?
Prisma collaborates with other ORMs
See with-Typeorm and with-TypeGoose for examples in this section
Since Prisma + Prisma is ok, what about Prisma + other ORMs? In fact, it is also very simple, in the TypeORM example, the steps are the same:
- Create Prisma connection
- Create a TypeORM connection
- Create key with Prisma
- Create TypeORM values using Prisma’s key
- Query all Prisma keys used to query all TypeORM values
async function main() {
// Setup TypeORM Connection
const connection = await createConnection({
type: "sqlite".database: IS_PROD
? "./dist/src/with-typeorm/typeorm-value.sqlite"
: "./src/with-typeorm/typeorm-value.sqlite".entities: [ValueEntity],
synchronize: true.dropSchema: true});await ValueEntity.clear();
await prisma.prismaKey.deleteMany();
const key1 = await prisma.prismaKey.create({
data: {
key: uuidv4(),
},
select: {
key: true,}});const insertValues = await ValueEntity.createQueryBuilder()
.insert()
.into(ValueEntity)
.values([
{
key: key1.key,
value: "The forest does not cross.",
}
])
.execute();
const keys = await prisma.prismaKey.findMany();
for (const keyItem of keys) {
const key = keyItem.key;
console.log(`Search By: ${key}`);
const value = await ValueEntity.createQueryBuilder("value")
.where("value.key = :key")
.setParameters({
key,
})
.getOne();
console.log("Search Result: ", value);
console.log("= = =");
}
await prisma.$disconnect();
await connection.close();
}
Copy the codePrisma + GraphQL
See TypeGraphQL-Apollo-server for the code for this section
Prisma and GraphQL have something in common, that is, they are both SDL First. Prisma Schema and GraphQL Schema even share some details, such as scalars and @ syntax (although Prisma is a built-in function and GraphQL is an instruction). Also, Prisma has DataLoader built in to solve the GraphQL N+1 problem, so you really don’t want to try Prisma + GraphQL?
For DataLoader, see the GraphQL N+1 problem I wrote earlier to parse the DataLoader source code, which includes the built-in DataLoader source code in Prisma2.
Technology Stack and Key Points:
-
Building GraphQL Schema and Resolver based on TypeGraphQL is one of the mainstream methods at present. After all, writing native GraphQL is not easy to extend (except with graphQL-modules). Similarly, we can use Nexus to build Schema.
-
GraphQL service is built based on ApolloServer, which is one of the most widely used GraphQL server frameworks at present.
We mount the instantiated Prisma Client into the Context so that the Prisma instance can be retrieved in the Resolver.
In fact, this approach is similar to the REST API, we split the application architecture into controller-service structure. Controller is the corresponding to the Resolver, which accepts requests and processes them directly. In the GraphQL application you can also split a layer of services, but not here to keep the code lean.
For Context APIS, I recommend reading Apollo’s official documentation.
-
To demonstrate the GraphQL Generation stack (just for fun), I also introduced graphQL-code-generator, which generates TS type definitions based on the built GraphQL Schema, and GenQL, which generates clients based on the Schema. Then I could call various methods in a similar way to Prisma Client. Also supports chain calls, which is hard to avoid.
Here we can go straight to the important code:
// server.ts
export const server = new ApolloServer({
schema,
introspection: true.context: { prisma }
});
// Todo.resolver.ts
// More Query/Mutation reference code repositories
@Resolver(TodoItem)
export default class TodoResolver {
constructor() {}
@Query((returns) = >[TodoItem!] !).async QueryAllTodos(@Ctx() ctx: IContext): Promise<TodoItem[]> {
return await ctx.prisma.todo.findMany({ include: { creator: true}}); }@Query((returns) = > TodoItem, { nullable: true })
async QueryTodoById(
@Arg("id".(type) = > Int) id: number.@Ctx() ctx: IContext
): Promise<TodoItem | null> {
return await ctx.prisma.todo.findUnique({
where: {
id,
},
include: { creator: true}}); }}Copy the codeThe only obvious difference from the example above is that it is recommended to mount Prisma into the context and then call the method instead of importing Prisma Client once for each Resolver. In Node frameworks with IoC mechanisms such as Midway and Nest, the recommended way to use Prisma Client is to register the Prisma Client in the container and inject it into the Service layer.
For the use of Nest and Prisma, refer to the repository README.
Epilogue: Prisma outlook
At the end of this article, let’s talk about Prisma expansively:
-
What was Prisma meant to solve? What new advantages does it offer over other solutions for working with databases (SQL, ORM, Query Builder)?
Why Prisma?
- Handwritten native SQL: as long as your ability in place, SQL control is the strongest, its control granularity is the most fine, almost no rival. But only if the ability is in place. Writing SQL takes a lot of time, and when you spend twice as much time writing SQL and you’re not getting a direct return, I think you need to stop and think, okay?
- Query Builder: In the last article, we explained that Query Builder is not ORM, but it is closer to native SQL, and each chain call to Query Builder makes a change to the resulting SQL. In addition, Query Builder also requires some SQL knowledge, such as leftJoin.
- ORM, which in JavaScript often uses the Class approach to define data table models, may seem sweet, but can actually lead toObject-relational Impedance Mismatch. For example, we’re used to using things like user.post. category
.To access nested data entities.
But in reality, User, Post, and Category should be separate entities; they are separate collections rather than relationships of object attributes. We use it in ORM.But underneath, it also constructs SQL using the foreign key JOIN. - What about Prisma? It no longer requires you to use Class definitions while telling yourself that this is a relational database… Now you can think directly in JS object mode. It doesn’t have as much control as SQL or Query Builder because you call it directly from the encapsulated CREATE/Update methods. However, because of its mental model, you can use it just like a normal object, which is much easier to use than ORM.
-
Is Prisma ORM?
Of course it is!
Unlike traditional ORM, Prisma uses a “declarative” approach, declaring the database structure in your Prisma file. This implementation allows it to be cross-language (just configure client.provider; currently, prisMA-client-JS is the only implementation). The traditional ORM approach is “object-oriented”, where you map your data table structures to language-specific model classes.
-
Prisma and GraphQL Generation
Prisma and GraphQL are both Schema First, so they tend to have amazing chemical reactions. The easiest thing to think of is the conversion of the two schemas, but there seems to be no similar solution in the community at present. For no other reason, it doesn’t make much sense to generate native GraphQL Schema from Prisma Schema, because native schemas are rarely written anymore. Second, if you want to get the GraphQL type definition from Prisma, there is no need to directly convert to native, but can completely convert to higher-order representation, such as Nexus and TypeGraphQL. So there are currently two schemes available in the community: Nexus-plugin-PRISma, which generates Nexus Type Builders, and TypeGraphQL-Prisma, which generates TypeGraphQL classes and CRUD Resolvers.
Integration framework
At present, in addition to front-end frameworks like React and back-end frameworks like Express, there is actually a Monolithic Framework, which was originally derived from Ruby On Rails.
At present, in the front-end field, the idea of the integration framework is mainly as follows:
-
During development, the front-end imports functions directly into the back-end, where data source operations are performed directly (ORM or existing APIS), rather than one service at the front-end and one at the back-end.
-
At build time, the framework automatically imports functions from the front end to the back end and converts them into HTTP requests, while functions from the back end are built into FaaS functions or API services.
-
BlitzJS is based on NextJS + Prisma + GraphQL, but does not require any knowledge of GraphQL. The author successfully transformed the Query/Mutation of GraphQL into a normal method call through Prisma, and you do not need to write your own Schema.
-
RedwoodJS, similar to Blitz, is based on React + Prisma + GraphQL, but scenarios in JAMStack, Blitz is more oriented towards application development. And instead of Blitz eliminating GraphQL, RedWoodJS uses Cells to realize the communication between the front and back ends.
-
React-hooks support compositing API React-hooks support Compositing API react-hooks support Compositing API Development Server based on Vite, can be deployed as a separate Server or FaaS, Ali internal are used, it is difficult not to pay porcelain!