a ctfer on the load

1
db:
2
    user:root
3
    password:'root'
4
    host:127.0.0.1
5
    port:3306
6
    database:test

1
import express from 'express';
2
import mysql2 from 'mysql2/promise';
3
import fs from 'fs';
4
import jsyaml from 'js-yaml';
5

6
const yaml = fs.readFileSync('./db.config.yaml', 'utf8')
7
    const config = jsyaml.load(yaml)
8
console.log(config)
9
const sql = await mysql2.createConnection({
10
    ...config.db
11
})
12

13
const app = express();
14
app.use(express.json())
15
app.get('/', async (res, req) => {
16
    const [data] = await sql.query('SELECT * FROM users')
17
    res.send(data)
18
})
19

20
app.get('/user/:id', async (res, req) => {
21
    const [row] = await sql.query('SELECT * FROM users WHERE id = ?', [req.params.id])
22
    res.send(row)
23
})
24

25
app.use('/create', async (req, res) => {
26
    const { name, age, hobby } = req.body
27
    await sql.query('INSERT INTO users (name,age,hobby) VALUES (?,?,?)', [name, age, hobby])
28
    res.send('ok')
29
})
30

31
app.use('/update', async (req, res) => {
32
    const { id, name, age, hobby } = req.body
33
    await sql.query('UPDATE users SET name = ?, age = ?, hobby = ? WHERE id = ?', [name, age, hobby, id])
34
    res.send('ok')
35
})
36

37
app.post('/delete',async (req,res)=>{
38
    await sql.query(`delete from user where id = ?`,[req.body.id])
39
    res.send({ok:1})
40
})
41

42
const port = 3000;
43

44
app.listen(port, () => {
45
    console.log(`Server is running on port ${port}`);
46
});

1
# 查询全部
2
 GET http://localhost:3000/ HTTP/1.1
3

4
# 单个查询
5
GET http://localhost:3000/user/2 HTTP/1.1
6

7
# 添加数据
8
POST http://localhost:3000/create HTTP/1.1
9
Content-Type: application/json
10

11
{
12
    "name":"张三",
13
    "age":18
14
}
15

16
# 更新数据
17
POST http://localhost:3000/update HTTP/1.1
18
Content-Type: application/json
19

20
{
21
    "name":"法外狂徒",
22
    "age":20,
23
    "id":23
24
}
25

26

27
#删除
28
# POST http://localhost:3000/delete HTTP/1.1
29
# Content-Type: application/json
30

31
# {
32
#     "id":24
33
# }

1
//安装
2
npm install prisma
3
//初始化
4
prisma init --datasource-provider mysql
5
//连接mysql
6
修改.env文件 [DATABASE_URL="mysql://账号:密码@主机:端口/库名"]

1
model Post {
2
  id       Int     @id @default(autoincrement()) //id 整数 自增
3
  title    String  //title字符串类型
4
  publish  Boolean @default(false) //发布 布尔值默认false
5
  author   User   @relation(fields: [authorId], references: [id]) //作者 关联用户表 关联关系 authorId 关联user表的id
6
  authorId Int
7
}
8

9
model User {
10
  id    Int    @id @default(autoincrement())
11
  name  String
12
  email String @unique
13
  posts Post[]
14
}

1
generator client {
2
  provider = "prisma-client-js"
3
}
4

5
datasource db {
6
  provider = "mysql"
7
  url      = env("DATABASE_URL")
8
}
9

10
model Post {
11
  id       Int     @id @default(autoincrement()) //id 整数 自增
12
  title    String  //title字符串类型
13
  publish  Boolean @default(false) //发布 布尔值默认false
14
  author   User   @relation(fields: [authorId], references: [id]) //作者 关联用户表 关联关系 authorId 关联user表的id
15
  authorId Int
16
}
17

18
model User {
19
  id    Int    @id @default(autoincrement())
20
  name  String
21
  email String @unique
22
  posts Post[]
23
}

1
-- CreateTable
2
CREATE TABLE `Post` (
3
    `id` INTEGER NOT NULL AUTO_INCREMENT,
4
    `title` VARCHAR(191) NOT NULL,
5
    `publish` BOOLEAN NOT NULL DEFAULT false,
6
    `authorId` INTEGER NOT NULL,
7

8
    PRIMARY KEY (`id`)
9
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
10

11
-- CreateTable
12
CREATE TABLE `User` (
13
    `id` INTEGER NOT NULL AUTO_INCREMENT,
14
    `name` VARCHAR(191) NOT NULL,
15
    `email` VARCHAR(191) NOT NULL,
16

17
    UNIQUE INDEX `User_email_key`(`email`),
18
    PRIMARY KEY (`id`)
19
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
20

21
-- AddForeignKey
22
ALTER TABLE `Post` ADD CONSTRAINT `Post_authorId_fkey` FOREIGN KEY (`authorId`) REFERENCES `User`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

1
import express from 'express'
2
import { PrismaClient } from '@prisma/client'
3
const prisma = new PrismaClient()
4
const app = express()
5
const port: number = 3000
6

7
app.use(express.json())
8

9
//关联查找
10
app.get('/', async (req, res) => {
11
    const data = await prisma.user.findMany({
12
        include: {
13
            posts: true
14
        }
15
    })
16
    res.send(data)
17
})
18

19
//单个查找
20
app.get('/user/:id', async (req, res) => {
21
    const row = await prisma.user.findMany({
22
        where: {
23
            id: Number(req.prisma.id)
24
        }
25
    })
26
    res.send(row)
27
})
28

29
//新增
30
app.post('/create', async (req, res) => {
31
    const { name, email } = req.body
32
    const data = await prisma.user.create({
33
        data: {
34
            name,
35
            email,
36
            posts: {
37
                create: {
38
                    title: '标题',
39
                    publish: true
40
                },
41
            }
42
        }
43
    })
44
    res.send(data)
45
})
46

47
//更新
48
app.post('/update',async (req, res) => {
49
    const {id,name,email}=req.body
50
    const data=await prisma.user.update({
51
        where:{
52
            id:Number(id)
53
        },
54
        data:{
55
            name,
56
            email
57
        }
58
    })
59
    req.send(data)
60
})
61

62
//删除
63
app.post('/delete', async (req, res) => {
64
    const { id } = req.body
65
    await prisma.post.deleteMany({
66
        where: {
67
            authorId: Number(id)
68
        }
69
    })
70
    const data = await prisma.user.delete({
71
        where: {
72
            id: Number(id),
73
        },
74
    })
75
    res.send(data)
76
})
77

78

79
app.listen(port, () => {
80
    console.log(`Example app listening on port ${port}`)
81
})