File system vs. database system

• File systems use files to store data, which is not suitable for sharing. Database The system uses databases to store data in a unified manner
• The programs (code) in the file system are related to the data; Separation of program and data in a database system
• The file system is not secure to store data

Database classification

• Non-relational databases
  • Mongo etc.
• Relational database
  • MySQL etc.

Node.js can add, delete, and check file contents

const readline = require('readline');
const fs = require('fs');
const FILEPATH = './db/my.json'

const rl = readline.createInterface({
    input: process.stdin, / / readable stream
    output: process.stdout / / write flow
});

function update(key, value) {
    fs.readFile(FILEPATH, 'utf8'.(err, data) = > {
        if (err) {
            console.log(err);
        }
        const json = JSON.parse(data);
        if (json && json[key]) {
            json[key] = value;
            fs.writeFile(FILEPATH, JSON.stringify(json), err= > {
                if (err) {
                    console.log(err);
                } else {
                    console.log('Write succeeded'); }}); }else {
            console.log('Not Found'); }})}function del(key) {
    fs.readFile(FILEPATH, 'utf8'.(err, data) = > {
        if (err) {
            console.log(err);
        }
        const json = JSON.parse(data);
        let flag = delete json[key];
        if (flag) {
            console.log('Deleted successfully');
            fs.writeFile(FILEPATH, JSON.stringify(json), err= > {
                if (err) {
                    console.log(err);
                } else {
                    console.log('Write succeeded'); }}); }})}function get(key) {
    fs.readFile(FILEPATH, 'utf8'.(err, data) = > {
        if (err) {
            console.log(err);
        }
        const json = JSON.parse(data);
        if (json && json[key]) {
            console.log(json[key]);
        } else {
            console.log('Not Found'); }})}function set(key, value) {
    fs.readFile(FILEPATH, 'utf8'.(err, data) = > {
        if (err) {
            console.log(err);
        }
        // May be an empty object
        const json = data ? JSON.parse(data) : {};
        json[key] = value;
        fs.writeFile(FILEPATH, JSON.stringify(json), err= > {
            if (err) {
                console.log(err);
            } else {
                console.log('Write succeeded'); }}); }) } rl.on('line'.(input) = > {
    // Terminal type format: set name holo
    const [op, key, value] = input.split(' ');// Split with Spaces
    switch (op) {
        case 'set':
            set(key, value);
            break;
        case 'get':
            get(key);
            break;
        case 'del':
            del(key);
            break;
        case 'update':
            update(key, value);
            break;
        case 'exit':
            rl.close();
            break;
        default:
            console.log('No such instruction');
            break; }}); rl.on('close'.() = > {
    console.log('exit');
    process.exit(0);
})
Copy the code

Node. Js connect MySQL

Install dependency packages:

cnpm i mysql -S

const mysql = require('mysql');

// Create a connection object
const conn = mysql.createConnection({
    host: 'localhost'.user: 'root'.password: '* * * * * *'.database: 'db1'
})

// Connect to the database
conn.connect(err= > {
    if (err) {
        throw err;
    }
    console.log('Connection successful');
})

// add, delete, check and modify
const CREATE_SQL = `CREATE TABLE IF NOT EXISTS test(id INT NOT NULL PRIMARY KEY auto_increment,name VARCHAR(20))`;
const INSERT_SQL = `INSERT INTO test(name) VALUES(?) `;
const SELECT_SQL = `SELECT * FROM test WHERE id=? `;
const DELETE_SQL = `DELETE FROM test WHERE id=? `;

conn.query(CREATE_SQL, (error, results, fields) = > {
    if (error) {
        throw error;
    }
    // Arguments: value callback functions that need to execute SQL statements to populate placeholders
    conn.query(INSERT_SQL, ['holo'].(error, results, fields) = > {
        if (error) {
            throw error;
        }
        conn.query(SELECT_SQL, [6].(error, results, fields) = > {
            if (error) {
                throw error;
            }
            // Results Returns the query result
            console.log(results[0].name);
            // Disconnect the connection
            conn.end(); // If the query statement is nested, end() needs to be executed in the last query})})})Copy the code

Delete operation:

conn.query(DELETE_SQL, [10].(error, results, fields) = > {
    if (error) {
        throw error;
    }
    // Disconnect the connection
    conn.end();
})
Copy the code

Encapsulate the MySQL query method

The above query is nested layer upon layer, which is cumbersome to operate and has the problem of callback hell. Code encapsulation with async/await can not only simplify operations, but also solve the problem of callback hell.

mysqlConfig.js

module.exports = {
    host: 'localhost'.user: 'root'.password: '* * * * * *'.database: 'db1'
}
Copy the code

mysql.js

const mysql = require('mysql');
const mysqlConfig = require('./mysqlConfig');

module.exports = {
    query: function (sql, params) {
        return new Promise((resolve, reject) = > {
            // Create a connection object
            const conn = mysql.createConnection(mysqlConfig);
            // Connect to the database
            conn.connect(err= > {
                if (err) {
                    throw err;
                }
                console.log('Connection successful');
            });
            // Format the SQL statement
            sql = mysql.format(sql, params);
            / / query
            conn.query(sql, (error, results, fields) = > {
                if (error) {
                    throw reject(error);
                }
                resolve(results);
                // Disconnect must come after the query statementconn.end(); }}})})Copy the code

Tips: The format() method used

Mysql github

var sql = mysql.format('UPDATE posts SET modified = ? WHERE id = ? ', [CURRENT_TIMESTAMP, 42]);
console.log(sql); // UPDATE posts SET modified = CURRENT_TIMESTAMP() WHERE id = 42
Copy the code

use

const CREATE_SQL = `CREATE TABLE IF NOT EXISTS test(id INT NOT NULL PRIMARY KEY auto_increment,name VARCHAR(20))`;
const INSERT_SQL = `INSERT INTO test(name) VALUES(?) `;
const SELECT_SQL = `SELECT * FROM test WHERE id=? `;
const DELETE_SQL = `DELETE FROM test WHERE id=? `;

// Import the wrapped mysql
const db = require('./db/mysql');

async function asyncQuery() {
    try {
        // const res = await db.query(CREATE_SQL);
        // const res = await db.query(INSERT_SQL, ['holo']);
        // const res = await db.query(SELECT_SQL, [1]);
        const res = await db.query(DELETE_SQL, [1]);
        console.log(res);
    } catch (error) {
        console.log(error);
    }
}

asyncQuery();
Copy the code