Node – Operating database (6)

Node.js® is a JavaScript Runtime built on Chrome’s V8 JavaScript engine JavaScript runtime is simply an environment that allows JS to run on the server.

preface

Hi~ I am back again, this week is a bit slow, harm, it is really the end of the year things…

This topic is about the operation of the database, iron children are still a little look forward to.

This time take MySQL database as an example, if you don’t know anything about it, you can click on me. (Don’t ask why MySQL, ask, just don’t know)

Get back to business

Prepare in advance

Since it is to operate the database, we must first have a database, the installation process will not say, anyway I have, you go to some degree.

Create a user table and insert a data into it as follows:

Common connection

In fact, the operation of the database is very simple, a few lines of code can let us enter the database gate, we need to use mysql this dependency mysql install.

Let’s go straight to the code (test.js) :

const mysql = require('mysql'); const db = mysql.createConnection({ host : 'localhost', user : 'root', password : '12345', database : 'test' }); db.connect(err => { if(err) throw err; Console. log(' connection successful ')}); let sql = `select * from user`; SQL =' insert into user set username='yd', age=18 '; // let SQL =' update user set username='YD' where id=2; SQL = 'delete from user where id=2; // delete */ db.query(SQL, (err, res) => {if (err) throw err; console.log(res); / / / {id: 1, the username: 'orange someone' age: 18}] dbClose (); }); function dbClose() { db.end(err => { if(err) throw err; Console. log(' Closed successfully '); }); // db.destory() can also close the database, but no callback}Copy the code

The above code can only operate the database once.

Isn’t that easy? . I feel like someone’s gonna start spraying… (flash)

Connection pool connection

const mysql = require('mysql'); const pool = mysql.createPool({ host: 'localhost', user: 'root', password: '12345', database: 'test', multipleStatements: true, // Allow multiple connection pools to be created simultaneously connectionLimit: // Create multiple connection pools at the same time, not as many as the better, more will consume performance, others run out of connection pools, should be recycled immediately. let sql = `select * from user`; SQL =' insert into user set username='yd', age=18 '; // let SQL =' update user set username='YD' where id=2; SQL = 'delete from user where id=2; // Delete */ pool.getConnection((err, db) => {if(err) throw err; db.query(sql, (err, result) => { if (err) throw err; console.log(result); / / / {id: 1, the username: 'orange someone' age: 18}] the release (); // Release database connection pool.end(); GetConnection ()}); })Copy the code

The above two ways can connect to the database for operation, so the two ways which is better? The trumpet asks if you have many friends? In my opinion, it depends on your needs. In the case of high concurrency, it is more efficient to use connection pool, but if you do not have high concurrency requirements, there is no big difference between normal connection and connection pool connection. (but I used to connect pool a shuttle, not a problem, hey hey ~)

A database connection pool allocates, manages, and releases database connections. It allows applications to reuse an existing database connection instead of creating another one. Release database connections whose idle time exceeds the maximum idle time to avoid database connection omissions caused by not releasing database connections. This technique can significantly improve the performance of database operations.

The connection pool is like a cache. It is a waste of performance to establish and close connections frequently when the user requests more. Therefore, use a cache to transition this situation.

Parameters that

More parameter description: portal

Simple packaging

Create mysql.js file with the following contents:

const mysql = require('mysql'); const pool = mysql.createPool({ host: 'localhost', user: 'root', password: '12345', database: 'test', multipleStatements: true, connectionLimit: 10 }); let query = (sql, values) => { return new Promise((resolve, reject) => { pool.getConnection((err, connection) => { if(err) { reject(err); }else { connection.query(sql, values, (err, rows) => { if(err) { reject(err); }else { resolve(rows); } connection.release(); })}})}); }; module.exports = { query };Copy the code

Create a test_sql.js file with the following contents:

Separate out SQL related files, because some SQL is too complex to manage all together.

const {query} = require('./mysql'); exports.getUserList = options => { let sql = `select * from user`; return query(sql) }; exports.addUser = options => { let sql = `insert into user set username=? ,age=? `; return query(sql, options) }; exports.updateUser = options => { let sql = `update user set username='${options.username}' where id=${options.id}`; return query(sql) }; exports.deleteUser = id => { let sql = `delete from user where id=${id}`; return query(sql) };Copy the code

The main business file, test.js, contains the following contents:

const test_sql = require('./test_sql'); /* / add test_sql.adduser (['YD', 18]). Then (res => {console.log(res)}) 2}). Then (res => {console.log(res)}) // delete test_sql.deleteuser (2). Then (res => {console.log(res)}) */ / query test_sql.getUserList().then(res => { console.log(res) })Copy the code

Finally, attach a screenshot:

Well, that’s about it…

Personally, I feel that I am not a good writer, and I am a practical person, but I occasionally envy those who write well and want to learn from them to write some interesting things. The so-called rookie self-cultivation, is to look up to the big guy, even as a study notes worth mentioning, anyway is to say what they know, this is my idea of writing. At the same time, I also hope that I can be the most concise words to explain a point of one thing, the so-called reading in the essence is not how to meet with you in a way.