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.