nodejs - mqsql

node.js中实现持久化的多种⽅法

node.js中实现持久化的2种⽅法

1. 文件系统fs
2. 数据库
   1.关系型数据库 - mysql
   2.文档型数据库 - mongodb
   3.键值对数据库-redis

⽂件系统数据库

//  fsdb.js
// 实现一个文件系统读写数据库

const  fs = require(fs)
function get(key) {
	fs.readFile("./db.json", (err, data) => {
		const json = JSON.parse(data);
		console.log(json[key]);
	})
}

function set(key, value) {
	fs.readFile(",.db.json", (err, data) => {
		// 可能是空⽂件，则设置为空对象
		const json = data ? JSON.parse(data) : {};
		json[key] = value; // 设置值
		// 重新写⼊⽂件
		fs.writeFile("./db.json", JSON.stringify(json), err => {
			if(err) console.log(err)
			console.log("写入成功")
		});
	})
}


// 命令行部分
const readline = require("readline");
const rl = readline..createInterface({ input: process.stdin, output: process.stdout })
rl.on("line", function (input) {
	const [op, key, value] = input.split(" ");
	if (op ==='get') {
		get(key)
	} else if (op === 'set') {
		set(key, value)
	} else if (op === 'quit') {
		rl.close();
	} else {
		console.log('没有该操作');
	}
})

rl.on('close', function () {
	console.log("程序结束");
	process.exit(0);
})

MySQL安装、配置

菜鸟教程：https://www.runoob.com/mysql/mysql-tutorial.html

node.js原⽣驱动

1. 安装mysql模块 npm i mysql --save
2. mysql模块基本使⽤

const mysql = require("mysql");
// 连接配置
const cfg = {
  host: "localhost",
  user: "root",
  password: "12345678", // 修改为你的密码
  database: "kaikeba" // 请确保数据库存在
};
// 创建连接对象
const conn = mysql.createConnection(cfg);

// 连接
conn.connect(err => {
  if (err) {
    throw err;
  } else {
    console.log("连接成功！");
  }
});

// 查询 conn.query()
// 创建表
const CREATE_SQL = `CREATE TABLE IF NOT EXISTS test (
                    id INT NOT NULL AUTO_INCREMENT,
                    message VARCHAR(45) NULL,
                    PRIMARY KEY (id))`;
const INSERT_SQL = `INSERT INTO test(message) VALUES(?)`;
const SELECT_SQL = `SELECT * FROM test`;
conn.query(CREATE_SQL, err => {
  if (err) {
    throw err;
  }
  // 插入数据
  conn.query(INSERT_SQL, "hello,world", (err, result) => {
    if (err) {
      throw err;
    }
    console.log(result);
    conn.query(SELECT_SQL, (err, results) => {
        console.log(JSON.stringify(results));
        conn.end(); // 若query语句有嵌套，则end需在此执行
    })
  });
});

es2017写法：

(async () => {
    const mysql = require('mysql2/promise')

    // 连接配置
    const cfg = {
        host: "localhost",
        user: "root",
        password: "12345678", // 修改为你的密码
        database: "kaikeba" // 请确保数据库存在
    }

    const connection = await mysql.createConnection(cfg)

    let ret = await connection.execute(`
        CREATE TABLE IF NOT EXISTS test (
            id INT NOT NULL AUTO_INCREMENT,
            message VARCHAR(45) NULL,
        PRIMARY KEY (id))
    `)
    console.log('create', ret)

    ret = await connection.execute(`
            INSERT INTO test(message)
            VALUES(?)
    `, ['ABC'])
    console.log('insert:', ret)


    ret = await connection.execute(`
            SELECT * FROM test
    `)
    console.log(JSON.stringify(ret[0]))
    // console.log(ret[1])

    connection.end()

})()    

Node.js ORM

Sequelize： https://sequelize.org/

1. 概述：基于Promise的ORM(Object Relation Mapping)，⽀持多种数据库、事务、关联等。
2. 安装： npm i sequelize mysql2 -S
3. 基本使用

(async () => {
    const Sequelize = require("sequelize");

    // 建立连接
    const sequelize = new Sequelize("kaikeba", "root", "example", {
        host: "localhost",
        dialect: "mysql",
        operatorsAliases: false
    });

    // 定义模型
    const Fruit = sequelize.define("Fruit", {
        name: {
            type: Sequelize.STRING(20),
            allowNull: false,
            // get() {
            //     const fname = this.getDataValue("name");
            //     const price = this.getDataValue("price");
            //     const stock = this.getDataValue("stock");
            //     return `${fname}(价格：￥${price} 库存：${stock}kg)`;
            // }
        },
        price: {
            type: Sequelize.FLOAT,
            allowNull: false,
            validate: {
                isFloat: { msg: "价格字段请输入数字" },
                min: { args: [0], msg: "价格字段必须大于0" }
            }
        },
        stock: { type: Sequelize.INTEGER, defaultValue: 0 },

    },
        {
            timestamps: false,
            getterMethods: {
                amount() {
                    return this.getDataValue("stock") + "kg";
                }
            },
            setterMethods: {
                amount(val) {
                    const idx = val.indexOf('kg');
                    const v = val.slice(0, idx);
                    this.setDataValue('stock', v);
                }
            }
        });

    Fruit.classify = function (name) {
        const tropicFruits = ['香蕉', '芒果', '椰子']; // 热带水果
        return tropicFruits.includes(name) ? '热带水果' : '其他水果';
    };
    Fruit.prototype.totalPrice = function (count) {
        return (this.price * count).toFixed(2);
    };

    ['香蕉', '草莓'].forEach(f => console.log(f + '是' + Fruit.classify(f)));

    // 同步数据库，force: true则会删除已存在表
    let ret = await Fruit.sync({ force: false })
    // console.log('sync', ret)
    ret = await Fruit.create({
        name: "香蕉",
        price: 3.5
    })
    // console.log('create', ret)
    // ret = await Fruit.findAll()

    // 使用实例方法
    // Fruit.findAll().then(fruits => {
    //     const [f1] = fruits;
    //     console.log(`买5kg${f1.name}需要￥${f1.totalPrice(5)}`);      
    // });

    // Fruit.findOne({ where: { name: "香蕉" } }).then(fruit => {
    //     // fruit是首个匹配项，若没有则为null
    //     console.log(fruit.get());
    // });

    // console.log('findAll', ret.amount, JSON.stringify(ret))

    // Fruit.findAll().then(fruits => {
    //     console.log(JSON.stringify(fruits));
    //     // 修改amount，触发setterMethods
    //     fruits[0].amount = '150kg';
    //     fruits[0].save();
    // });

    // Fruit.findOne({ attributes: ['name'] }).then(fruit => {
    //     // fruit是首个匹配项，若没有则为null
    //     console.log(fruit.get());
    // });

    // ret = await Fruit.findAll({
    //     offset: 3,
    //     limit: 3,
    // })
    // console.log('ret:', JSON.stringify(ret))

    const Op = Sequelize.Op;
    Fruit.findAll({
        // where: { price: { [Op.lt]:4 }, stock: { [Op.gte]: 100 } }
        where: { id: { [Op.lt]: 4, [Op.gt]: 2 } }
    }).then(fruits => {
        console.log(JSON.stringify(fruits))
        console.log(fruits.length);
    });

    // Fruit.destroy({ where: { id: 1 } }).then(r => console.log(r));
})()

1. 强制同步：创建表之前先删除已存在的表： Fruit.sync({force: true})

2. 避免⾃动⽣成时间戳字段： const Fruit = sequelize.define("Fruit", {}, { timestamps: false });

3. 指定表名： freezeTableName: true 或 tableName:‘xxx’

设置前者则以modelName作为表名；设置后者则按其值作为表名。
蛇形命名 underscored: true,
默认驼峰命名

4. UUID-主键

id: {
 type: Sequelize.DataTypes.UUID,
 defaultValue: Sequelize.DataTypes.UUIDV1,
 primaryKey: true
 },

5. Getters & Setters：可⽤于定义伪属性或映射到数据库字段的保护属性

// 定义为属性的⼀部分
name: {
	type: Sequelize.STRING,
	allowNull: false,
	get() {
		const fname = this.getDataValue("name")
		const price =this.getDataValue("price")
		const stock = this.getDataValue('stock')
		return `${fname}(价格：￥${price} 库存：${stock}kg)`;
	}
}


// 定义模型选项
// options中
{
	getterMethods: {
		amount() {
			return  this.getDataValue("stock") + 'kg'
		}
	},
	setterMethods: {
		amount(val) {
			const idx = val.indexof("kg")
			const v = val.slice(0, idx);
			this.setDataValue('stock', v);
		}
	}
}

// 通过模型实例触发setterMethods
Fruit.findAll().then((fruits) => {
	console.log(JSON.stringify(fruits));
	// 修改amount，触发setterMethods
	fruits[0].amount = '150kg';
	fruits[0].save();
})

• 校验： 可以通过校验功能验证模型字段格式，内容，校验会在create，update和dave时自动运行。

price: {
 	validate: {
		isFloat: { msg: "价格字段请输⼊数字" },
		min: { args: [0], msg: "价格字段必须⼤于0" }
	}
}

stock: {
	validate: {
		isNumeric: { msg: "库存字段请输⼊数字" }
	}
}

• 模型扩展：可添加模型实例⽅法或类⽅法扩展模型

// 添加类级别⽅法
Fruit.classify = function (name) {
	const tropicFruits = ['⾹蕉', '芒果', '椰⼦']; // 热带⽔果
	return tropicFruits.includes(name) ? '热带⽔果':'其他⽔果'; 
}

// 添加实例级别方法
Fruit.prototype.totalPrice = function(count) {
	return (this.price * count).toFixed(2);
};

// 使用方法：
['⾹蕉','草莓'].forEach(f => console.log(f+'是'+Fruit.classify(f)));

// 使用实例方法：
Fruit.findAll() .then((fruits) => {
	const [f1] = fruits
	console.log(`买5kg${f1.name}需要￥${f1.totalPrice(5)}`);
})

• 查询数据

//  通过id查询(不⽀持了)
Fruit.findById(1).then(fruit => {
// fruit是⼀个Fruit实例，若没有则为null
console.log(fruit.get());
});

// 通过属性查询
Fruit.findOne({ where: {name: '香蕉'} }).then((fruit) => {
	// fruit是首个匹配选项，若没有则为null
	console.log(fruit.get())
})

// 指定查询字段
Fruit.findOne({ attributes: ['name'] }).then((fruit) => {
	// fruit是首个匹配选项，若没有则为null
	console.log(fruit.get())
})

// 获取数据和总条数
Fruit.findAndCountAll().then((result) => {
	console.log(result.count)
	console.log(result.rows.length)
})

// 查询操作符
const Op = Sequelize.Op
Fruit.findAll({
 // where: { price: { [Op.lt]:4 }, stock: { [Op.gte]: 100 } }
 where: { price: { [Op.lt], [Op.gt]: 2 } }
}.then(fruits => {
	console.log(fruits.length)
})

// 或语句
Fruit.findAll({
	// where: { [Op.or]:[{price: { [Op.lt]:4 }}, {stock: { [Op.gte]: 100 }}] }
	where: { price: { [Op.or]:[{[Op.gt]:3 }, {[Op.lt]:2 }]}}
}).then(fruits => {
	console.log(fruits[0].get());
});

// 分页：
Fruit.findAll({ offset: 0, linit: 2 })

// 排序
Fruit.findAll({ order: [['price', 'DESC']] })

// 聚合
Fruit.max('price').then(max => {
	console.log("max", max);
})

Fruit.sum("price").then((sum) => {
	console.log(sum)
})

• 更新

Fruit.findById(1).then(() => {
	// 方式1
	fruit.price = 4
	fruit.save().then(() => {console.log('update!!!')})
})

// 方式2
Fruit.update({ price: 4 }, { where: { id: 1 } }).then(r => {
	console.log(r);
	console.log('update!!!!')
})

• 删除

// 方式1
Fruit.findOne({ where: { id: 1 } }).then( r => r.destroy() )

// 方式2
Fruit.destroy({ where: { id: 1 } }).then(r => console.log(r));

1:N关系

(async () => {

    // 1:N关系
    const Sequelize = require("sequelize");

    // 建立连接
    const sequelize = new Sequelize("kaikeba", "root", "example", {
        host: "localhost",
        dialect: "mysql",
        operatorsAliases: false
    });
    // Player
    const Player = sequelize.define('player', { name: Sequelize.STRING });
	
	// Team
    const Team = sequelize.define('team', { name: Sequelize.STRING });
    
    Player.belongsTo(Team); // 1端建立关系
    Team.hasMany(Player); // N端建立关系

    // 同步数据库，force: true则会删除已存在表
    sequelize.sync({ force: true }).then(async () => {
        await Team.create({ name: '火箭' });
        await Player.bulkCreate([{ name: '哈登', teamId: 1 }, { name: '保罗', teamId: 1 }]);

        // 1端关联查询
        const players = await Player.findAll({ include: [Team] });
        console.log(JSON.stringify(players, null, 2));

        // N端关联查询
        const team = await Team.findOne({ where: { name: '火箭' }, include: [Player] });
        console.log(JSON.stringify(team, null, 2));
    });


})()

(async () => {

    // 1:N关系
    const Sequelize = require("sequelize");

    // 建立连接
    const sequelize = new Sequelize("kaikeba", "root", "example", {
        host: "localhost",
        dialect: "mysql",
    });

    const Fruit = sequelize.define("fruit", { name: Sequelize.STRING });
    const Category = sequelize.define("category", { name: Sequelize.STRING });
    
    Fruit.FruitCategory = Fruit.belongsToMany(Category, {
        through: "FruitCategory"
    });

    // 插入测试数据
    sequelize.sync({ force: true }).then(async () => {
        await Fruit.create(
            {
                name: "香蕉",
                categories: [{ id: 1, name: "热带" }, { id: 2, name: "温带" }]
            },
            {
                include: [Fruit.FruitCategory]
            }
        );
        // 多对多联合查询
        const fruit = await Fruit.findOne({
            where: { name: "香蕉" }, // 通过through指定条件、字段等
            include: [{ model: Category, through: { attributes: ['id', 'name'] } }]
        });
    })

})()

案例：

目录结构：

package.json

{
  "name": "expressjs",
  "version": "1.0.0",
  "description": "",
  "main": "app.js",
  "dependencies": {
    "body-parser": "^1.18.3",
    "dotenv": "^6.2.0",
    "ejs": "^2.6.1",
    "express": "^4.16.4",
    "express-handlebars": "^3.0.0",
    "http-proxy-middleware": "^0.19.1",
    "mongodb": "^3.1.13",
    "mysql2": "^1.6.5",
    "nodemon": "^1.18.10",
    "sequelize": "^4.42.0"
  },
  "devDependencies": {},
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1",
    "start": "nodemon app.js"
  },
  "author": "Sourav",
  "license": "ISC"
}

data/cart.json

{"products":[],"totalPrice":0}

data/products.json

[
  {
    "id": "8QngKP18c",
    "title": "Fixing international domains",
    "imageUrl": "https://www.listchallenges.com/f/lists/7c6a48ca-d523-409e-ad51-5e652ee208ed.jpg",
    "description": "pok",
    "price": "77"
  },
  {
    "id": "gNoFUbWka",
    "title": "Optimizing get open-identities call",
    "imageUrl": "https://www.listchallenges.com/f/lists/7c6a48ca-d523-409e-ad51-5e652ee208ed.jpg",
    "description": "wes",
    "price": "96"
  }
]

models/cart-item.js

const Sequelize = require('sequelize');
const sequelize = require('../util/database');

const CartItem = sequelize.define('cartItem', {
    id: {
        type: Sequelize.INTEGER,
        autoIncrement: true,
        allowNull: false,
        primaryKey: true
    },
    quantity: Sequelize.INTEGER
});

module.exports = CartItem;

models/cart.js

const Sequelize = require('sequelize');
const sequelize = require('../util/database');

const Cart = sequelize.define('cart', {
    id: {
        type: Sequelize.INTEGER,
        autoIncrement: true,
        primaryKey: true,
        allowNull: false
    }
});

module.exports = Cart;

models/order-item.js

const Sequelize = require('sequelize');
const sequelize = require('../util/database');

const OrderItem = sequelize.define('orderItem', {
    id: {
        type: Sequelize.INTEGER,
        autoIncrement: true,
        allowNull: false,
        primaryKey: true
    },
    quantity: Sequelize.INTEGER
});

module.exports = OrderItem;

models/order.js

const Sequelize = require('sequelize');
const sequelize = require('../util/database');

const Order = sequelize.define('order', {
    id: {
        type: Sequelize.INTEGER,
        autoIncrement: true,
        allowNull: false,
        primaryKey: true
    }
});

module.exports = Order;

models/product.js

const Sequelize = require('sequelize');

const sequelize = require('../util/database');

const Product = sequelize.define('product', {
    id: {
        type: Sequelize.INTEGER,
        autoIncrement: true,
        allowNull: false,
        primaryKey: true
    },
    title: {
        type: Sequelize.STRING,
        allowNull: false
    },
    price: {
        type: Sequelize.DOUBLE,
        allowNull: false
    },
    imageUrl: {
        type: Sequelize.STRING,
        allowNull: false
    },
    description: {
        type: Sequelize.STRING,
        allowNull: false
    }
});

module.exports = Product;

models/user.js

const Sequelize = require('sequelize');
const sequelize = require('../util/database');

const User = sequelize.define('user', {
    id : {
        type: Sequelize.INTEGER,
        autoIncrement: true,
        allowNull: false,
        primaryKey: true
    },
    name: Sequelize.STRING,
    email: Sequelize.STRING
});

module.exports = User;

util/conf.js

module.exports = {
    database: 'shop',
    username: 'root',
    password:'example',
    host:'localhost'
 };

util/database.js

const Sequelize = require("sequelize");
const env = require("dotenv");
const conf = require("./conf")

env.config();
const sequelize = new Sequelize(conf.database, conf.username, conf.password, {
  dialect: "mysql",
  host: conf.host,
  operatorsAliases: false
});

module.exports = sequelize;

util/path.js

const path = require('path');

module.exports = path.dirname(process.mainModule.filename);

docker-compose.yml

version: '3.1'
services:
  mysql:
    image: mysql
    command: --default-authentication-plugin=mysql_native_password
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: example
    ports:
      - 3306:3306
  adminer:
    image: adminer
    restart: always
    ports:
      - 8080:8080
  

index.js

const Koa = require('koa')
const app = new Koa()
const bodyParser = require('koa-bodyparser')
app.use(require('koa-static')(__dirname + '/'))
app.use(bodyParser())

// 初始化数据库
const sequelize = require('./util/database');
const Product = require('./models/product');
const User = require('./models/user');
const Cart = require('./models/cart');
const CartItem = require('./models/cart-item');
const Order = require('./models/order');
const OrderItem = require('./models/order-item');


// 加载用户 - 代替鉴权
app.use(async (ctx, next) => {
    const user = await User.findByPk(1)
    ctx.user = user;
    await next();
});

const router = require('koa-router')()
router.get('/admin/products', async (ctx, next) => {
    // const products = await ctx.user.getProducts()
    const products = await Product.findAll()
    ctx.body = { prods: products }
})

router.post('/admin/product', async ctx => {
    const body = ctx.request.body
    const res = await ctx.user.createProduct(body)
    ctx.body = { success: true }
})

router.delete('/admin/product/:id', async (ctx, next) => {
    const id = ctx.params.id
    const res = await Product.destroy({
        where: {
            id
        }
    })
    ctx.body = { success: true }
})

router.get('/cart', async ctx => {
    const cart = await ctx.user.getCart()
    const products = await cart.getProducts()
    ctx.body = { products }
})
/**
 * 添加购物车
 */
router.post('/cart', async ctx => {
    const body = ctx.request.body
    console.log('ctx.body', ctx.request.body)
    const prodId = body.id;
    let fetchedCart;
    let newQty = 1;

    // 获取购物车
    const cart = await ctx.user.getCart()
    console.log('cart', cart)
    fetchedCart = cart;
    const products = await cart.getProducts({
        where: {
            id: prodId
        }
    });

    let product;
    // 判断购物车数量
    if (products.length > 0) {
        product = products[0];
    }
    if (product) {
        const oldQty = product.cartItem.quantity;
        newQty = oldQty + 1;
        console.log("newQty", newQty);
    } else {
        product = await Product.findByPk(prodId);
    }

    await fetchedCart.addProduct(product, {
        through: {
            quantity: newQty
        }
    });
    ctx.body = { success: true }
})

router.post('/orders', async ctx => {
    let fetchedCart;
    const cart = await ctx.user.getCart();
    fetchedCart = cart;
    const products = await cart.getProducts();
    const order = await ctx.user.createOrder();
    const result = await order.addProducts(
        products.map(p => {
            p.orderItem = {
                quantity: p.cartItem.quantity
            };
            return p;
        })
    );
    await fetchedCart.setProducts(null);
    ctx.body = { success: true }
})
router.delete('/cartItem/:id', async ctx => {
    const id = ctx.params.id
    const cart = await ctx.user.getCart()
    const products = await cart.getProducts({
        where: { id }
    })
    const product = products[0]
    await product.cartItem.destroy()
    ctx.body = { success: true }
})
router.get('/orders', async ctx => {
    const orders = await ctx.user.getOrders({ include: ['products'], order: [['id', 'DESC']] })
    ctx.body = { orders }
})


app.use(router.routes())

// app.use('/admin', adminRoutes.routes);
// app.use(shopRoutes);


Product.belongsTo(User, {
    constraints: true,
    onDelete: 'CASCADE'
});
User.hasMany(Product);
User.hasOne(Cart);
Cart.belongsTo(User);
Cart.belongsToMany(Product, {
    through: CartItem
});
Product.belongsToMany(Cart, {
    through: CartItem
});
Order.belongsTo(User);
User.hasMany(Order);
Order.belongsToMany(Product, {
    through: OrderItem
});
Product.belongsToMany(Order, {
    through: OrderItem
});

sequelize.sync().then(
    async result => {
        let user = await User.findByPk(1)
        if (!user) {
            user = await User.create({
                name: 'Sourav',
                email: 'sourav.dey9@gmail.com'
            })
            await user.createCart();
        }
        app.listen(3000, () => console.log("Listening to port 3000"));
    })