Node connects to MySQL and encapsulates its implementation code for adding, deleting, modifying and checking

Node connects to Mysql
Install MySQL module
Connect to MySQL
Commonly used SQL statements
Node operates Mysql
Encapsulation
Finish

Node connects to Mysql

When it comes to node, you may think of MOngoDB as a database. Here we will introduce the connection between node and mysql, and share the packaged example code, which can be used directly in project development.

Install MySQL module

npm install mysql

Connect to MySQL

    const mysql = require('mysql');
    
    let connection = mysql.createConnection({
        host : 'localhost',
        user : 'root', 
        password : 'password',
        database : 'test'
    });
 
connection.connect(function(err) {
  if (err) {
    console.error('Connection failed: ' + err.stack);
    return;
  }
 
  console.log('Connection successful id ' + connection.threadId);
});

host: the server to connect to
user: database user name
password: The set MySQL password
database: The name of the database to connect to

Commonly used SQL statements

The specific usage is not described in detail here, including select, insert, update, delete and other statements.

Node operates Mysql

Query

connection.query('SELECT * FROM t_user WHERE username = "whg"', (err, results, fields) => {
    if(err){
        console.log(err);
    }
    console.log(results);
})

Add to

connection.query('INSERT INTO t_user(username, pass) VALUES(?, ?)',['whg', '123'], (err, results) => {
    if(err){
        console.log(err);
    }
    console.log(results);
})

delete

connection.query('DELETE FROM t_user WHERE id = 1', (err, results) => {
    if(err){
        console.log(err);
    }
    console.log(results);
})

renew

connection.query('UPDATE t_user SET pass = "321" WHERE username = "whg"', (err, results) => {
    if(err){
        console.log(err);
    }
    console.log(results);
})

End the connection

connection.end(function(err) {
  
});
connection.destroy();

Both of these are fine, the second one is forced to end.

Encapsulation

Having said so much, I feel that the operation is quite simple. In actual development, if we want to make the operation more convenient, let us encapsulate it ourselves and use it. Directly on the code:

Packaged code

1. Database configuration file

//Configure the link database parameters module.exports = {
    host : 'localhost',
    port : 3306, //Port number database : 'nodetest', //Database name user : 'root', //Database username password : '123456' //Database password };

2. Encapsulation and exposure methods

let mysql = require('mysql'); //Introduce the mysql module var databaseConfig = require('./mysql.config'); //Introduce the data in the database configuration module //Exposing the method module.exports = {
    query : function (sql, params, callback) {
        //You need to create a connection each time you use it, and close the connection after the data operation is completed var connection = mysql.createConnection(databaseConfig);        
        connection.connect(function(err){
            if(err){
                console.log('Database connection failed');
                throw err;
            }
         //Start data operation//Pass in three parameters, the first parameter is the sql statement, the second parameter is the data required in the sql statement, and the third parameter is the callback function connection.query( sql, params, function(err,results,fields ){
           if(err){
                console.log('Data operation failed');
                throw err;
            }
            //Return the queried data to the callback function callback && callback(results, fields);
            //results is the result of the data operation, fields is some fields of the database connection //To stop connecting to the database, you must query the statement, otherwise, once this method is called, the connection will be stopped directly and the data operation will fail connection.end(function(err){
                  if(err){
                      console.log('Failed to close database connection!');
                      throw err;
                  }
              });
           });
       });
    }
};

3. Demonstration Example

var db = require('../model/mysql.js');
// Query example db.query('select * from t_user', [],function(result,fields){
    console.log('Query results:');
    console.log(result);
});
//Add instance var addSql = 'INSERT INTO websites(username,password) VALUES(?,?)';
var addSqlParams =['Mr. Gulu', '666'];
db.query(addSql,addSqlParams,function(result,fields){
    console.log('Added successfully')
})

Finish

Friends who want to use it can directly copy the two packaged files to the project, change the configuration and use it, which greatly improves development efficiency.

This is the end of this article about connecting Node to MySQL and encapsulating its implementation code for adding, deleting, modifying and checking. For more relevant content about connecting Node to MySQL for adding, deleting, modifying and checking, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!

You may also be interested in:

Node.js adds, deletes, modifies and checks the actual combat record of MySQL database
Analysis of mysql add, delete, modify and query operations and async, await processing examples in node.js
Node.js+Express+Mysql to implement add, delete, modify and query
An example of nodejs operating mysql to achieve addition, deletion, modification and query
NodeJs connects to Mysql to perform simple operations of adding, deleting, modifying and checking
Node.js operates mysql database to add, delete, modify and query
Node.js operation mysql (add, delete, modify and query)

<<: A collection of information about forms and form submission operations in HTML

>>: CSS realizes corner cutting + border + projection + content background color gradient effect

How to view the IP address of Linux in VMware virtual machine

Node connects to MySQL and encapsulates its implementation code for adding, deleting, modifying and checking

Table of contents