The Node connection MySql

The installation

Operating system: macOS Catalina 10.15.7

MySql

Use the tool Homebrew

Brew search mysql # brew search mysql # brew info mysql # brew install mysqlCopy the code

Pay attention to the message after the installation:

• Version 8.0.22.
• MySql > install MySql, no password set, to be more secure, run:mysql_secure_installation 。
• MySql’s default configuration allows only local connections.
• To connect, run the command:mysql -uroot 。
• Start command:brew services start mysqlIf you do not want to run the service in the background, run the following command:mysql.server start 。

Executing mysQL_secure_installation prompts you for various Settings, such as passwords, whether to disable remote root logons, removing test tables, and so on.

Mysql -uroot

ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: NO)

Mysql -u root -p mysql -u root -p mysql -u root -p mysql -u root -p

Other commands

• Mysql. server status;
• Disable: mysql.server stop

MySQL Workbench

The visualization tool is available for download here. The version used this time is mysql-Workbench-community-8.0.22-macOS-x86_64.

When using the tool to connect to the local database, you need to enter the port number and query the port number after logging in to the local database using the command:

mysql> show global variables like 'port';
Copy the code

The tool displays port 3306 by default. The first time you connect, a pop-up window will prompt you to enter your password.

Node

See here to install Node.

Connecting to a Database

On NPM you can search for libraries that connect to mysql, using mysql in conjunction with KOA as an example.

// db.js file, mainly used to connect to the database
const mysql = require('mysql');

const client = (sql) = > {
  return new Promise((resolve) = > {
  const connection = mysql.createConnection({
    host: 'localhost'.port: 3306.user: 'root'./ / user name
    password: '123456'./ / password
    database: 'test'./ / the name of the library
  });

  connection.connect();

  connection.query(sql, function (error, results, fields) {
    if (error) throwerror; resolve(results) }); connection.end(); })}Copy the code

// server.js starts the service
const Koa = require('koa');
const cors = require('@koa/cors'); // Resolve local request cross-domain issues
const app = new Koa();
const sqlConnect = require('./db');

app.use(cors())

// response
app.use(async ctx => {
  const sql = 'SELECT * FROM table_name'; // table_name is the name of the table in the library
  const list = await sqlConnect(sql);
  console.log('list', list)
  ctx.body = list;
});

app.listen(3000);
console.log('server is running at http://localhost:3000')
Copy the code

After normal startup, the front page request http://localhost:3000 can see the effect.