The use of Prisma based on Node.js ORM framework

Make writing a habit together! This is my first day to participate in the “Gold Digging Day New Plan · April More text challenge”, click to see the details of the activity.

Node.js, as one of our front-end technologies, exists most of the time as a Javascript runtime environment. But its asynchronous and non-blocking approach also allows Node.js to process thousands of connections simultaneously. Front-end engineers can use it to complete common server-side code with a low learning cost.

ORM

ORM: Object Relational Mapping is a programming technique. Simply speaking, ORM can encapsulate various operations of our underlying database to a certain extent. We can write corresponding database commands through more familiar development language, and ORM can convert these database operation commands into corresponding SQL statements. So Prisma is what we’re going to look at.

Prisma

Next generation Node.js, TypeScript, Go database ORM

Prisma is an open source database toolchain project that helps developers build applications faster and reduce errors with support for PostgreSQL, MySQL, MongoDB, SQL Server, and SQLite.

If we want to understand how a technology works, we need to develop it a little bit by actually using it.

First we need to initialize a project

{"name": "prisma-demo", "version": "1.0.0", "description" : ""," main ":" index. Js ", "scripts" : {" test ", "echo" Error: no test specified" && exit 1" }, "keywords": [], "author": "", "license": "ISC" }Copy the code

Then we will use prisMA for installation
```
NPM install prisma -dCopy the code
```

After installation, you can use the NPX prisma init -h command to view help information about prisma commands

Setup a new Prisma project Usage $ prisma init [options] Options -h, --help Display this help message --datasource-provider Define the datasource provider to use: PostgreSQL, MySQL, SQLite, SQL Server or MongoDB (Preview) --url Define a custom datasource url Examples Setup a new Prisma project with PostgreSQL Prisma Setup a new PRISma project and specify MySQL as the Datasource provider to use $prisma init --datasource-provider mysql #  specify the url that will be used $ prisma init --url mysql://user:password@localhost:3306/mydbCopy the code

Here we define the default database as SQLite by using PRISma Init –datasource-provider SQLite
- SQLite is a file database, self-contained, serverless, zero configuration, transactional SQL database engine, easy to use in the process.
```
NPX prisma init --datasource-provider sqliteCopy the code
```

Go to our code interface

Prisma -> schema.prisma // This file is a configuration file for the database model. All database model configurations are in this file generator client {provider = "prisma-client-js"} // Data source Datasource db {provider = "sqlite" // database URL = env("DATABASE_URL") // database connection address Prisma syntax pluginCopy the code

Creating a data model

Model user {id String @id @unique @default(uuid())) // The user id must be unique. Default is uuid userName String @unique @map("user_name") // The user name must be unique. @map indicates an alias for field definition. Password String @default("") phone Int? @unique // call String? @default(" nickName") @map("nickName") // nickName address String? @default("") // @default("") // Gender createAt dateTime@default (now()) @map("create_at") // Creation time updateAt dateTime@updatedat Autograph String @default("") // Autograph signature @@map(" Users ") // Name the data table} // File model Model POST {id String @id @unique @default(uuid()) // setting up the association of the table authorId String @default("") @map("author_id") // authorId id of the associated user @@map("posts") }Copy the code

After the new data model is created, we go back to our command line and generate the database directly under the current file by using NPX Prisma DB push
```
- node_modules
- prisma
    - dev.db
    -schema.prisma
- .env 
- app.js

Copy the code
```
- The file database we generated cannot be opened normally, we need to install another one in VScodeThe plug-in SQLiteCan normally open our file database
- In addition to the above method of installing the plug-in, we can also use it in the project directorynpx prisma studioCommand to open prisma’s built-in server and view our database and tables in the browser

Database operations

Next is the database related operations

Create a db.js file in the root directory and import the corresponding contents

Import {PrismaClient} from '@prisma/client' const db = new PrismaClient(); db.$connect().catch((err) => console.error(err)); // Export default DB to connect the databaseCopy the code

Creating a Controller

// userController.js import prisma from ".. /utils/db"; Async function createUser() {// Create user try {await prisma.user.create({// call prisma create function data: {userName:'test', password:'test', nickName:' booty '},}); } catch (err) {console.log(err); }} // call createUser()Copy the code

You can create a user whose user name is test and password is test by running the node userController.js file on the cli

We can then query the user data through PRISma’s lookup statement

async function findUsers(userName){ const findUser = await prisma.user.findMany({ where:userName }) console.log(findUser) } findUsers('test') /* # node userController.js [ { id:'xxxxxxxx', userName:'test', CreateAt: 2022-03-11t04:05:43.175z, updateAt: 2022-03-11t04:05:43.175z}] */Copy the code

In addition to this, we often have multiple table associations in database operations, so we need to configure them in the data model

model user { id String @id @unique @default(uuid()) ...... @@map("users")} model post {id String @id @unique @default(uuid()) + author user@relation (fields: [authorId], References: [id]) + // fields indicates the fields in the current model. References indicates the fields in the model to be associated. AuthorId String @default("") @map("author_id") // authorId indicates the id of the associated user . @@map("posts") }Copy the code

After updating the database-related data model, you need to regenerate the database and data tables
```
npx prisma db push
Copy the code
```

We then add a method to create an article as we did above to create a user

async function createPost(){ const user = await prisma.user.findFirst(); Data :{title:' XXX ', desc:' XXX ', AuthorId :user.id // Enter the corresponding data}})} createPost() // node createPost.js Execute the file and methodCopy the code

After the article is generated, we can add a method to find the article

async function findPost(){
  const post = await prisma.post.findMany({
    where:{}
  })
  console.log(post)
  /*
    [
        {
            id:'xxx',
            title:'xxx',
            desc:'',
            content:'',
            ...
        },
        {
            id:'xxx',
            title:'xxxx',
            desc:'xxx',
            content:'xxxx',
            ...
        }
    ]
  */
}
findPost()
Copy the code

The above is to create the article and find the article, if we need to find the article and need to know the associated data can be through the following method

Async function findPost(){const post = await prisma.post.findmany ({// where where:{}, + include:{+ author: true + } }) console.log(post) /* [ { id:'xxx', title:'xxx', desc:'', content:'', ... + author:{ + id:'xxx', + userName:'xxx', + password:'xxxx', + ... + } }, ] */ } findPost()Copy the code

ORM can also be used with Express.js, koa. js, egg.js and other Web development frameworks for server-side interface development. For more content and query statements, you can also go to the following official website to check the corresponding documents.

English document: prisma.io

英文版 : Prisma.yoga/Reference/A…

The use of Prisma based on Node.js ORM framework

ORM

Prisma

Database operations

Related Posts

Electron + Vue3 MAC version calendar development record (34) — DMG download package | August more challenges

2020 — Personal summary

Sum up my study view in four sentences