How to connect koa2 to mysql in Nodejs

Convert query results to objects or arrays

In real development, some query results should actually be put into an object

JSON_OBJECT: () is The form of key-value

SELECT products.id as id, products.title as title, products.price as price, products.score as score,
	JSON_OBJECT('id', brand.id, 'name', brand.name, 'rank', brand.phoneRank, 'website', brand.website) as brand
FROM products LEFT JOIN brand ON products.brand_id = brand.id;

In a many-to-many relationship, what we want to query is an array:

• For example, a student's multiple course information should be placed in an array;

• The array stores objects of course information;

• At this time we need to use JSON_ARRAYAGG and JSON_OBJECT in combination;

##

SELECT stu.id, stu.name, stu.age,
	JSON_ARRAYAGG(JSON_OBJECT('id', cs.id, 'name', cs.name)) as courses
FROM students stu
LEFT JOIN students_select_courses ssc ON stu.id = ssc.student_id
LEFT JOIN courses cs ON ssc.course_id = cs.id
GROUP BY stu.id;

Usage of mysql2

Install mysql2:

npm install mysql2

Simple use:

const mysql = require('mysql2');

// 1.创建数据库连接
const connection = mysql.createConnection({
  host: 'localhost',
  port: 3306,
  database: 'coderhub',
  user: 'root',
  password: 'Coderwhy888.'
});

// 2.执行SQL语句
const statement = `
  SELECT * FROM products WHERE price > 6000;
`
connection.query(statement, (err, results, fields) => {
  console.log(results);
});

If we want to stop the service after getting the data, we can write in the callback function:

connection.end()

Complete code:

connection.query(statement, (err, results, fields) => {
  console.log(results);
  connection.end();
});

Prepared Statement(preprocessed statement)

Improve performance: send the created statement module to MySQL, and then MySQL compiles (parsing, optimization, Convert) statement module, and store

but not execute it. Later, when we actually execute it, we will provide
? with actual parameters before execution; even if it is executed multiple times, it will only be compiled once, so Performance is higher;

Emphasis: If the statement is executed again, it will be obtained from the LRU (Least Recently Used) Cache, omitting the time to compile the statement to improve performance.

// 2.执行SQL语句: 使用 ？来对参数进行占位
const statement = `
  SELECT * FROM products WHERE price > ? AND score > ?;
`
connection.execute(statement, [6000, 7], (err, results) => {
  console.log(results);
});

Connection Pools

We created a connection (connection) earlier, but if we have multiple requests, the connection is likely to be occupied, then we Do I need to create a new connection every time a request is made?

• In fact, mysql2 provides us with connection pools;

• The connection pool can automatically create connections when needed. And the created connections will not be destroyed, but will be placed in the connection pool and can be used later;

• We can set LIMIT when creating the connection pool, which is the maximum number of creations. ;

Determine whether the connection is successful

const mysql = require('mysql2');

// 1.创建连接池
const connections = mysql.createPool({
  host: 'localhost',
  port: 3306,
  database: 'coderhub',
  user: 'root',
  password: 'Coderwhy888.',
  connectionLimit: 10
});

connections.getConnection((err, conn) => {
  conn.connect((err) => {
    if(err){
      console.log('连接失败：',err)
    } else {
      console.log('数据库连接成功~')
    }
  })
})

Simple use of the database

const mysql = require('mysql2');

// 1.创建连接池
const connections = mysql.createPool({
  host: 'localhost',
  port: 3306,
  database: 'coderhub',
  user: 'root',
  password: 'Coderwhy888.',
  connectionLimit: 10
});

// 2.使用连接池
const statement = `
  SELECT * FROM products WHERE price > ? AND score > ?;
`
connections.execute(statement, [6000, 7], (err, results) => {
  console.log(results);
});

Promise method

const mysql = require('mysql2');

// 1.创建连接池
const connections = mysql.createPool({
  host: 'localhost',
  port: 3306,
  database: 'coderhub',
  user: 'root',
  password: 'Coderwhy888.',
  connectionLimit: 10
});

// 2.使用连接池
const statement = `
  SELECT * FROM products WHERE price > ? AND score > ?;
`
connections.promise().execute(statement, [6000, 7]).then(([results,fields]) => {
  console.log(results);
}).catch(err => {
  console.log(err);
});

sequelize

Object Relational Mapping (ORM): It is a programming solution:

• In terms of effect, It provides an effect that can be used in a programming language,

  The effect of using a virtual object database;

The ORM in Node we usually use is sequelize;

• Sequelize is a Node.js-based ORM for Postgres, MySQL, MariaDB, SQLite and Microsoft SQL Server;

• It supports a lot of features;

If we want to use Sequelize with MySQL, then we need to install two things first:

• mysql2: sequelize when operating mysql Mysql2 is used;

• sequelize: Use it to map objects to tables;

npm install sequelize mysql2

Use of Sequelize

Sequelize connection database:

The first step: Create a Sequelize object, and specify the database, user name, password, database type, host address, etc.;

The second step: Test the connection Whether it is successful;

const { Sequelize } = require('sequelize');

const sequelize = new Sequelize('coderhub', 'root', 'Coderwhy888.', {
  host: 'localhost',
  dialect: 'mysql'//连接的数据库类型：mysql，mongoose
});

sequelize.authenticate().then(() => {
  console.log("连接数据库成功~");
}).catch(err => {
  console.log("连接数据库失败~", err);
});

Sequelize’s single-table operation

const { Sequelize, DataTypes, Model, Op } = require('sequelize');

const sequelize = new Sequelize("coderhub", 'root', 'Coderwhy888.', {
  host: 'localhost',
  dialect: 'mysql'
})

//1.首先我们需要将数据库中的一张表映射成一个class类
class Product extends Model {}
Product.init({
  id: {
    type: DataTypes.INTEGER,
    primaryKey: true,//主键
    autoIncrement: true//自动增长
  },
  title: {
    type: DataTypes.STRING,
    allowNotNull: false//是否可以为空
  },
  price: DataTypes.DOUBLE,
  score: DataTypes.DOUBLE
}, {//与数据库的表进行映射的配置
  tableName: 'products',
  createdAt: false,
  updatedAt: false,
  sequelize
});

//存放操作数据库的代码
async function queryProducts() {
  //1.查询数据库中product表中所有的内容
  const result1 = await Product.findAll({
    where: {//在这里配置条件
      price: {
        [Op.gte]: 5000//意思是价格大于等于5000
        //gte:大于等于，gt:大于，lt:小于，lte:小于等于
      }
    }
  });
  console.log(result1);

  // 2.插入数据
  const result2 = await Product.create({
    title: "三星Nova",
    price: 8888,
    score: 5.5
  });
  console.log(result2);

  // 3.更新数据
  const result3 = await Product.update({
    price: 3688
  }, {
    where: {
      id: 1
    }
  });
  console.log(result3);
}

queryProducts();//执行这个函数可以实现对数据库的操作

Sequelize’s one-to-many operation

const { Sequelize, DataTypes, Model, Op } = require('sequelize');

const sequelize = new Sequelize("coderhub", 'root', 'Coderwhy888.', {
  host: 'localhost',
  dialect: 'mysql'
});

//数据库的第一个表: 主表
class Brand extends Model {};
Brand.init({
  id: {
    type: DataTypes.INTEGER,
    primaryKey: true,
    autoIncrement: true
  },
  name: {
    type: DataTypes.STRING,
    allowNotNull: false
  },
  website: DataTypes.STRING,
  phoneRank: DataTypes.INTEGER
}, {
  tableName: 'brand',
  createdAt: false,
  updatedAt: false,
  sequelize
});

//数据库的第二个表：附表
class Product extends Model {}
Product.init({
  id: {
    type: DataTypes.INTEGER,
    primaryKey: true,
    autoIncrement: true
  },
  title: {
    type: DataTypes.STRING,
    allowNotNull: false
  },
  price: DataTypes.DOUBLE,
  score: DataTypes.DOUBLE,
  brandId: {
    field: 'brand_id',
    type: DataTypes.INTEGER,
    references: {//这张表使用了Brand的id作为外键
      model: Brand,//product这张表使用了Brand这个表，所以product必须放在下面
      key: 'id'
    }
  }
}, {
  tableName: 'products',
  createdAt: false,
  updatedAt: false,
  sequelize
});

// 将两张表联系在一起
Product.belongsTo(Brand, {
  foreignKey: 'brandId'//外键
});

async function queryProducts() {
  const result = await Product.findAll({
    include: { //这里是联合查询：意思是包含别的表的信息
      model: Brand
    }
  });
  console.log(result);
}

queryProducts();

Sequelize’s many-to-many operation

const { Sequelize, DataTypes, Model, Op } = require('sequelize');

const sequelize = new Sequelize("coderhub", 'root', 'Coderwhy888.', {
  host: 'localhost',
  dialect: 'mysql'
});

// Student表
class Student extends Model {}
Student.init({
  id: {
    type: DataTypes.INTEGER,
    primaryKey: true,
    autoIncrement: true
  },
  name: {
    type: DataTypes.STRING,
    allowNotNull: false
  },
  age: DataTypes.INTEGER
}, {
  tableName: 'students',
  createdAt: false,
  updatedAt: false,
  sequelize
});

// Course表
class Course extends Model {}
Course.init({
  id: {
    type: DataTypes.INTEGER,
    primaryKey: true,
    autoIncrement: true
  },
  name: {
    type: DataTypes.STRING,
    allowNotNull: false
  },
  price: DataTypes.DOUBLE
}, {
  tableName: 'courses',
  createdAt: false,
  updatedAt: false,
  sequelize
});

// StudentCourse表：关系表
class StudentCourse extends Model {}
StudentCourse.init({
  id: {
    type: DataTypes.INTEGER,
    primaryKey: true,
    autoIncrement: true
  },
  studentId: {//与Student表建立关系
    type: DataTypes.INTEGER,
    references: {
      model: Student,
      key: 'id'
    },
    field: 'student_id'
  },
  courseId: {//与Course表建立关系
    type: DataTypes.INTEGER,
    references: {
      model: Course,
      key: 'id'
    },
    field: 'course_id'
  }
}, {
  tableName: 'students_select_courses',
  createdAt: false,
  updatedAt: false,
  sequelize
});

// 多对多关系的联系：Student StudentCourse Course
Student.belongsToMany(Course, {
  through: StudentCourse,
  foreignKey: 'studentId',//这里是Student与StudentCourse，所以外键是studentId
  otherKey: 'courseId'//StudentCourse与Course，所以外键是courseId
});

//与上面类似
Course.belongsToMany(Student, {
  through: StudentCourse,
  foreignKey: 'courseId',
  otherKey: 'studentId'
});

async function queryProducts() {
  const result = await Student.findAll({
    include: {//所有学生的选课情况
      model: Course
    }
  });
  console.log(result);
}

queryProducts();

The above is the detailed content of How to connect koa2 to mysql in Nodejs. For more information, please follow other related articles on the PHP Chinese website!