Node.js (KOA2)+MySQL easy to get started

Mysql installation

MySQL > install MySQL

Configure a password

ALTER USER 'root'@'localhost' IDENTIFIED BY 'your password'; 
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your password';
Copy the code

When using the mysql module or node.js ORM, you need to configure the password in the mysql command line to start the Node. js connection

Configuring MySQL

1. Install the mysql engine

npm install mysql --save
Copy the code

Mysql module is the driver (engine) for Node.js to operate mysql. It can create tables, add, delete, modify, and query mysql database in node.js environment.

2. Create a MySQL database

CREATE DATABASE login_test DEFAULT CHARSET utf8;
Copy the code

Use Node.js to connect to MySQL

Introducing the mysql module

 const mysql = require('mysql');
Copy the code

createconnectionThe instance

Use the createConnection method, which takes an OBject parameter, provides the host to connect to the database, username, password, and database name, and creates a Connection instance to connect to the OBject.

const connection = mysql.createConnection({
 host: 'localhost',
 user: 'root',
 password: 'password',
 database: 'login_test'
});
Copy the code

Call the connection method to connect to the database

Connect to the database by calling the Connect method in Connection, which accepts a function containing ERR and provides error handling.

connection.connect(function (err) {
  if (err) {
    console.error('error connecting: ' + err.stack);
    return;
  }

  console.log('connected as id ' + connection.threadId);
});

Copy the code

Closing the database connection is providedendanddestroyThe difference between the two methods is that destroy immediately stops the database connection, whileendMethod stops the connection after processing the database request.

connection.end(function (err) {
  if (err) {
    return console.log('error:' + err.message);
  }
  console.log('Close the database connection.');
});
Copy the code

Complete database connection

const mysql = require('mysql');

const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'login_test'
});

connection.connect(function (err) {
  if (err) {
    console.error('error connecting: ' + err.stack);
    return;
  }

  console.log('connected as id ' + connection.threadId);
});

connection.query('SELECT 1 + 1 AS solution'.function (error, results, fields) {
  if (error) throw error;
  console.log('The solution is: ', results[0].solution);
});

connection.end(function (err) {
  if (err) {
    return console.log('error:' + err.message);
  }
  console.log('Close the database connection.');
});

Copy the code

MySQL connection pool

Creating a Connection Pool

You can create a connection pool through the createPool method of connection.

const pool = mysql.createPool({
   connectionLimit: 10,
   host: 'localhost',
   user: 'root',
   password: 'password', 
   database: 'login_test'
});
Copy the code

This creates a connection pool of 10 connections. Note that connection pool creation is lazy, and if only two connections are used, only two connections will be created.

2. Use connection pools

First, the connection pool is called in this order: pool.getConnection() -> Connection.query () -> Connection.release ()

pool.getConnection(function (err, connection) {
 if (err) throw err; // not connected!
 // Use the connection
 connection.query('SELECT 1 + 1 AS solution'.function (error, results, fields) {
   // When done with the connection, release it.
   connection.release();
   // Handle error after the release.
   if (error) throw error;
   console.log('The solution is: ', results[0].solution);
   // Don't use the connection here, it has been returned to the pool. }); });Copy the code

When the connection is finished, it can be released through the connection.release method and can be used by other requests.

Using the Connection. destroy method, you can destroy the current connection, and the connection pool can create a new one as needed.

Use the end method to close all connections to the connection pool

  pool.end(function (err) {
   // all connections in the pool have ended
 });
Copy the code

Note that executing pool.end calls the connaction. end method for each connection. Tasks that are already in the Event loop stack will continue to execute and those that are not will not be executed.

A complete example

const mysql = require('mysql');

const pool = mysql.createPool({
 connectionLimit: 10,
 host: 'localhost',
 user: 'root',
 password: 'password',
 database: 'login_test'
});

pool.getConnection(function (err, connection) {
 if (err) throw err; // not connected!
 // Use the connection
 connection.query('SELECT 1 + 1 AS solution'.function (error, results, fields) {
   // When done with the connection, release it.
   connection.release();
   // Handle error after the release.
   if (error) throw error;
   console.log('The solution is: ', results[0].solution);
   // Don't use the connection here, it has been returned to the pool. }); }); setTimeout(function () { pool.end(function (err) { // all connections in the pool have ended }); }, 1000).Copy the code

Create table in MySQL

`query`methods

Mysql module provides a query method, Connection, after can be instantiated by the Pool, PoolNamespace calls. The query method can be used in two ways:

.query(sqlString, callback)
.query(sqlString, values, callback)

Values are the replacement variable for placeholders in THE SQL statement and must be provided as an Array. If multiple variables are used, values must be used as a nested Array in an Array. (There are specific examples in MySQL write)

The callback function takes three arguments:

Error: indicates an error message

Results: Results of a query

Fields: Indicates the information about the result field

Create tables using mysql

const mysql = require('mysql');

const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'login_test'
});

// connect to the MySQL server
connection.connect(function (err) {
  if (err) {
    return console.error('error: '+ err.message); }});let createTodos = `create table if not exists user_table(
                          id int primary key AUTO_INCREMENT,
                          name varchar(255) NOT NULL,
                          password varchar(255) NOT NULL,
                          email varchar(255) DEFAULT ' ', create_time datetime NOT NULL, update_time datetime DEFAULT NOW() ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8; `; connection.query(createTodos,function (err, results, fields) {
  if(err) { console.log(err.message); }}); connection.end(function (err) {
  if (err) {
    returnconsole.log(err.message); }});Copy the code

A simple user table is used here, and in real projects, Profile and Authorization should be separated

In the MySQL command line, enter use login_test; After the show tables; View the built data sheet

Write data to MySQL

Single write

const mysql = require('mysql');

const pool = mysql.createPool({
  connectionLimit: 10,
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'login_test'
});

const sql = `INSERT INTO user_table (name,password,create_time) 
             VALUES ('test'.'123'.'2019-08-25 00:00:00');
`

pool.getConnection(function (err, connection) {
  if (err) throw err;
  connection.query(sql, function (error, results, fields) {
    connection.release();
    if (error) throw error;
    console.log(results)
  });
});
Copy the code

Select * from user_table;

Use placeholders? Dynamic writing

const mysql = require('mysql');

const pool = mysql.createPool({
  connectionLimit: 10,
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'login_test'
});

const values = ['test'.'123'.'2019-08-25 00:00:00'] const sql = `INSERT INTO user_table (name,password,create_time) VALUES ( ? ,? ,?) ; ` pool.getConnection(function (err, connection) {
  if (err) throw err;
  connection.query(sql, values, function (error, results, fields) {
    connection.release();
    if (error) throw error;
    console.log(results)
  });
});
Copy the code

This uses the second use of query statements (three parameters) to dynamically generate SQL statements and then insert.

Insert multiple rows at once

const mysql = require('mysql');

const pool = mysql.createPool({
  connectionLimit: 10,
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'login_test'
});

const values = [[
  ['test1'.'1'.'2019-08-25 00:00:00'],
  ['test2'.'2'.'2019-08-25 00:00:00'],
  ['test3'.'3'.'2019-08-25 00:00:00']
]]
const sql = `INSERT INTO user_table (name,password,create_time) 
             VALUES ?
`;

pool.getConnection(function (err, connection) {
  if (err) throw err;
  connection.query(sql, values, function (error, results, fields) {
    connection.release();
    if (error) throw error;
    console.log(results)
  });
});
Copy the code

SQL statement (? ,? ,?) Switch to? Values is changed to a three-level nested array.

MySQL query

A simple query

const mysql = require('mysql');

const pool = mysql.createPool({
  connectionLimit: 10,
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'login_test'
});


const sql = `SELECT * from user_table`;

pool.getConnection(function (err, connection) {
  if (err) throw err;
  connection.query(sql, function (error, results, fields) {
    connection.release();
    if (error) throw error;
    console.log(results)
  });
});
Copy the code

The value of the query

const mysql = require('mysql');

const pool = mysql.createPool({
  connectionLimit: 10,
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'login_test'
});

const values = `test`; const sql = `SELECT * from user_table WHERE name=? `; pool.getConnection(function (err, connection) {
  if (err) throw err;
  connection.query(sql, values, function (error, results, fields) {
    connection.release();
    if (error) throw error;
    console.log(results)
  });
});
Copy the code

Note: This one? Placeholders are written in the same way as escape method execution logic and are also resistant to simple SQL injection.

MySQL update

const mysql = require('mysql');

const pool = mysql.createPool({
  connectionLimit: 10,
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'login_test'
});

const values = ['test_pass'.'test1']; const sql = `UPDATE user_table SET password=? WHERE name=? `; pool.getConnection(function (err, connection) {
  if (err) throw err;
  connection.query(sql, values, function (error, results, fields) {
    connection.release();
    if (error) throw error;
    console.log(results)
  });
});
Copy the code

Still in use? The placeholder also has the Array parameter of value.

MySQL > delete from database

Example Delete a single query result

const mysql = require('mysql');

const pool = mysql.createPool({
  connectionLimit: 10,
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'login_test'
});

const values = ['test'.'4']; const sql = `DELETE from user_table WHERE name=? AND id=? `; pool.getConnection(function (err, connection) {
  if (err) throw err;
  connection.query(sql, values, function (error, results, fields) {
    connection.release();
    if (error) throw error;
    console.log(results)
  });
});
Copy the code

Node.js (KOA2)+MySQL easy to get started

Mysql installation

Configuring MySQL

Use Node.js to connect to MySQL

MySQL connection pool

Create table in MySQL

querymethods

Write data to MySQL

MySQL query

MySQL update

MySQL > delete from database

Related Posts

Interviewer: Are you writing a game in pure CSS?

In 2019, you still don’t know CSS animation?

When most people understand Vue perfectly, is it time to think about how to make Vue pages sexy up

`query`methods