A brief discussion on how node connects to mysql database

This article will talk about how node connects to mysql database. It has certain reference value. Friends in need can refer to it. I hope it will be helpful to everyone.

Related recommendations: "node js tutorial"

node uses the native method to connect mysql database

(async () => {
    // 链接数据库
    const mysql = require('mysql2/promise');    // npm i mysql2
    const cfg = {
        host: 'localhost',
        user: 'root',
        password: ';he%0f_,ljyW',
        database: 'izengx',
    }
    const connection = await mysql.createConnection(cfg);
    
    // 创建一个新表tests
    let ret = await connection.execute(`CREATE TABLE IF NOT EXISTS tests (
        id INT NOT NULL AUTO_INCREMENT,
        message VARCHAR(45) NULL,
        PRIMARY KEY (id)
    )`)
    console.log('create', ret);
    
    // 新建数据
    ret = await connection.execute(`INSERT INTO tests(message) VALUE(?)`, ['newData'])
    console.log('新建数据', ret);
    
    const [rows, fields] = await connection.execute(`
        SELECT * FROM tests
    `)
    console.log('查询数据', rows);
    
})()

Use database middleware (ORM): sequelize to connect and operate the database

(async () => {
    // 使用数据库中间件（ORM）：sequelize连接和操作数据库
    // 1. 使用Sequelize时，生成的表名会自动加成复数s，如fruit->fruits
    // 2. 自动生成主键id，自增（缺点是合并新旧数据时，id又从1开始，会有重合）
    const Sequelize = require('sequelize');
    const sequelize = new Sequelize('izengx', 'root', ';he%0f_,ljyW', {
        host: 'localhost',
        dialect: 'mysql',
        operatorsAliases: false,
    })
   
    const Fruit =sequelize.define('Fruit', {
        name: {type: Sequelize.STRING(20), allowNull: false,},
        price: {type: Sequelize.FLOAT, allowNull: false},
        stock: {type: Sequelize.INTEGER, defaultValue: 0}
    })
    
    // 同步数据库
    let ret = await Fruit.sync();
   
    // 增加一条数据
    ret = await Fruit.create({
        name: 'apple',
        price: 3.5
    })
    
    // 更新数据
    await Fruit.update({
        price: 4,
    }, {
        where: {
            name: 'banana',
        }
    })

    // 查询
    ret = await Fruit.findAll();
    // 查询指定范围的数据
    const Op = Sequelize.Op;
    opRet = await Fruit.findAll({
        where: {
            price: {
                [Op.gt]: 3,
                [Op.lt]: 5,
            }
        }
    })
    console.log('search: '+ JSON.stringify(opRet));
})()

For more programming-related knowledge, please visit: Programming Learning Website! !

The above is the detailed content of A brief discussion on how node connects to mysql database. For more information, please follow other related articles on the PHP Chinese website!