Cloud Development Connect to mySQL database

MySQL is arguably the most popular database for Internet enterprises and the most popular related database (cloud development database is document database). If your project already uses MySQL, the database is not easy to migrate to the cloud development database, or your business prefers to use MySQL, in which case you can also use a connection to the self-built MySQL database and pass the data to the small terminal for rendering.

First, private network VPC

1. Comparison between public network and private network access database

Relationship database services purchased by server-built MySQL or cloud service companies such as Tencent Cloud can be connected using cloud functions when external network connectivity and IP whitelisting are open, that is, cloud functions are deployed in public networks and can only access public database resources (intranet or local databases are not available), while your database needs to be accessed by public networks. However, the cloud function does not currently have a fixed IP, so the list of IP whitelists that the database needs to add is longer.

If you don't want your database to be exposed to the public network environment but can be accessed by cloud functions, it is recommended to use Tencent Cloud's private network. T encent cloud products on private networks, such as cloud functions developed by the cloud and The MySQL database of Tencent Cloud, can be connected through the equivalent connections provided by Tencent Cloud. The equivalent connection service has great advantages over public network transmission:

• Higher quality, sharing the same self-built internal network with other Tencent cloud products, because it is internal transmission, not affected by the quality of the public network, availability, delay, loss rate protection greatly improved;

• Stronger security, intranet connectivity in the Tencent Group level of anti-DDos security protection, due to not through the wide-area Internet and carrier links, to avoid the risk of message transmission on the public network may be stolen;

• Intranet transmission is faster, lower cost, cloud function extranet traffic is to be billed, and intranet traffic in the same region is free;

If you want cloud functions developed by the cloud to use resource-consuming services such as graphics images, audio and video processing, and services such as MySQL, Redis, TencentDB, Kafka, which have high security requirements, we recommend using a private network.

2, the creation of private network services

(1) Create a private network for the Shanghai Availability Zone

Tencent cloud console needs to log in, choose WeChat public number (small program account belongs to the public number system) login method, scan the code to select the corresponding small program account to log in.

Open Tencent Cloud Console Private Network Create a private network for free, because the private network has a region property, we need to create a new private network in the domain where the function is located. T he server for cloud functions developed by the cloud is deployed in Shanghai, so the resources in your private network should also be selected in Shanghai. P rivate networks have three core components: private network segment IPv4 CIDR, subnet, and routing tables. S ubnets under a private network can belong to different Availability Zones under that region, and resources within each subnet under the same private network are interoperability by default, whether or not they are in the same Availability Zone. And the initial subnet Availability Zone, you can choose according to your MySQL and other databases in the Availability Zone, if you previously did not have a database in Tencent Cloud, etc., select any Availability Zone in Shanghai can be.

(2) Purchase MySQL at Tencent Cloud and join the same private network

Buy an instance in MySQL, which opens Tencent's cloud console, and create the instance by finding the private network you created before and the corresponding subnet (pull-down) here.

(3) Add cloud functions that need to be connected to MySQL to a private network

Open the CloudBase for cloud development in Tencent's cloud development, select the specified environment, then click on the cloud function menu, then create a new cloud function or select a specified cloud function such as mysql, enter the configuration page, click "Edit" in the upper right corner, and select the same private network as the MySQL service in the network configuration. This allows the cloud function to connect to MySQL over a private network.

Then we can import data or data files into the mysql database as needed or in conjunction with our own business needs, for example, we can import sql files into it using Tencent and the DMS that come with it.

Second, use mysql2 to operate MySQL

1, use mysql2 to connect to MySQL

There are many libraries that support Nodejs connecting to the MySQL database, such as mysql, mysql2, which is recommended here. mysql2 is a Promise writing that supports async / await.

Use the developer tool to open the previous mysql cloud function (as long as the private network can be bound, if not can refer to the previous instructions), add the latest version of mysql2 in package.json, and right-click the cloud function directory to open the input command npm install installation dependency in the terminal:

  "dependencies": {
    "wx-server-sdk": "latest",
    "mysql2": "latest"
  }

Then enter the following code in the index.js, and it is important to note that we introduced mysql2/promise, the first parameter .query is sqlString, the string format of the SQL command line statement, and when all database .end otherwise the cloud function will report a timeout error.

const cloud = require('wx-server-sdk')
cloud.init({
  env: cloud.DYNAMIC_CURRENT_ENV,
})
const mysql = require('mysql2/promise');
exports.main = async (event, context) => {
  try {
    const connection = await mysql.createConnection({
      host: "10.168.0.7", //内网ip地址
      user: "root",  //数据库的用户名
      password: "tcb123", //数据库密码
      database: "tcb",  //数据库名称
    })
    const [rows, fields] = await connection.query(
      'SELECT * FROM `country` WHERE `country` = "china"',
      );
    connection.end(function(err) {  //注意要断开连接，不然尽管获取到了数据，云函数还是会报超时错误
      console.log('断开连接')
    });
    console.log(rows)
    console.log(fields)
    return rows
  } catch (err) {
    console.log("连接错误", err)
    return err
  }
}

Many of the parameters of the mysql2 module are used in line with the mysql module, and more configuration-related information can be viewed in the mysql module technical documentation .

2, mysql2 additions and deletions

mysql2 supports the addition and deletion of the database, the following is a few simple cases, more information can be viewed mysql-related technical documentation:

#创建一个名称为tcb的数据库
CREATE DATABASE `tcb`
#创建一个包含name、address字段的users表格与删除表格
CREATE TABLE `users` (`name` VARCHAR(255), `address` VARCHAR(255))
DROP TABLE `users`
#向users表格里插入记录
INSERT INTO `users`(`name`, `address`) VALUES ('李东bbsky', '深圳')
#查询users表格
SELECT * FROM `users`
#限制查询到的记录数为20，建议记录比较多的数据表查询都需指定limit
SELECT * FROM `users` LIMIT 20
#查询users表格里字段等于某个值的记录
SELECT * FROM `users` WHERE `name` = '李东bbsky'
#将查询结果按名称来排序
SELECT * FROM `users` ORDER BY `name`
#删除满足条件的记录
DELETE FROM `users` WHERE `address` = '深圳'
#更新满足条件的记录的字段值
UPDATE `users` SET `address` = "广州" WHERE `address` = '深圳'
#使用Join进行联表查询
SELECT `users.name` AS `user`, `products.name` AS `favorite` FROM `users` JOIN `products` ON `users.favorite_product` = products.id

3, use serverless-mysql to operate MySQL

A better package serverless-mysql is also recommended below, using the documentation to refer to the serverless-mysql technical documentation

Use the developer tool to open the previous mysql cloud function, add the latest version of mysql2 to package.json, and right-click the cloud function directory to select the input command npm install installation dependency to open in the terminal:

  "dependencies": {
    "wx-server-sdk": "latest",
    "serverless-mysql": "latest"
  }

Then enter the following code in the index.js, and it is important to note that we introduced mysql2/promise, the first parameter .query is sqlString, the string format of the SQL command line statement, and when all database .end otherwise the cloud function will report a timeout error.

const cloud = require('wx-server-sdk')
cloud.init({
  env: cloud.DYNAMIC_CURRENT_ENV,
})
const mysql = require('serverless-mysql')
exports.main = async (event, context) => {
  const connection = await mysql({
    config: {
      host     : "10.168.0.7",//你
      database : "country",
      user     : "root",
      password : "lidongyx327"
    }
  })
  let results = await connection.query('INSERT INTO country(Country, Region) VALUES ("中国","亚洲")')
  await connection.end()
  return results
}

Of course, you can also use Sequelize, an ORM framework .js for .js and io. S pecifically, it highlights a broad support for uniform configuration and query methods. T he databases it supports include PostgreSQL, MySQL, MariaDB, SQLite, and MSSQL. Technical documentation: Sequelize, there are few examples here.

With MySQL in cloud functions, each cloud function is executed with a connection to MySQL's server), but the maximum number of connections to the database is very small (hundreds or thousands), and we can see and set this value in database management max_connections. B ecause the number of connections to the database is relatively small, it is recommended that the addition and deletion of the database be written in a cloud function. This will reduce the probability of cold start of cloud functions and reduce the use of database connections, and the processing of addition and deletion will be centralized into a cloud function, we can use to the cloud function routing tcb-router, which will be described later.