Node connects MySQL and encapsulates its addition, deletion, query and modification

Node connects to mysql

In this note, the connection between node and mysql will be introduced, and the encapsulated code will be shared, which can be used directly in the project.

Install mysql module

npm install mysql

Connect 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('Successful connection id ' + connection.threadId);
});

host: connected server

User: database user name

Password: set MySQL password

Database: database name to connect to

Common SQL statements

Specific use is not detailed here, including select, insert, update, delete and other statements.

Node operation 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);
})

To update

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

End connection

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

Either way. The second way is to force an end.

encapsulation

Packaged code

1. Database configuration file

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

2. Packaging and exposure methods

let mysql = require('mysql');//Introducing mysql module
var databaseConfig = require('./mysql.config');  //Data introduced into database configuration module

//Exposure method
module.exports = {
    query : function(sql,params,callback){
        //You need to create a link every 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 link failed');
                throw err;
            }
         //Start data operation
         //Pass in three parameters: the first parameter sql statement, the data required in the second parameter sql statement, and the third parameter 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 as the result of data operation, fields as some fields of database connection
            //Stop linking the database. You must query the statement again. Otherwise, as soon as you call this method, you will stop linking directly, and the data operation will fail
             connection.end(function(err){
                  if(err){
                      console.log('Failed to close database connection!');
                      throw err;
                  }
              });
           });
       });
    }
};

3. Demonstration examples

var db=require('../model/mysql.js');
// Query instance
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 =['Guru Xian Sen', '666'];
db.query(addSql,addSqlParams,function(result,fields){
    console.log('Add success')
})

End

When using, directly copy the encapsulated method to the project, and modify the instance. In the next article, I will continue to introduce the use of node+mysql connection pool.

Tags: MySQL Database SQL npm

Posted on Mon, 02 Dec 2019 00:29:48 -0500 by rwwd