Node and mysql CURD summary

I have worked on a small project using Node and mysql. Now I will make a summary of how to use the two. CURD is an acronym in database technology that stands for Create, Update, read, and Delete operations and is used to process data, among other things.

preparation

Installing the PHP environment

Manual installation

Win10 安装 PHP development environment 教程

Integrated Environment Installation

There are several integration packages available:

PHPStudy

LNMP

XAMPP

wampserver

appserv

Installing the Node Environment

Download and install nodeJS from the nodejs website, step by step to install the following, check out which version.

We practice

This section shows how to use Node to connect to mysql for various operations through an item add, delete, change, query.

The node to

Installing dependency packages

$ mkdir demo
$ cd demo
$ npm init
$ npm install express --save
$ npm install -g express-generator
$ npm install pm2 -g
$ npm install supervisor
Copy the code

Starting the Node Service

Writing startup scripts

// index.js
const express = require('express');
const app = express();
const port = 3000;
const db = require('./mysql');

app.get('/'.(req, res) = > {
    res.send('Hello,demo! ');
})

app.listen(port, () = > {
    console.log('Server is running on port ', port + '! ');
})
Copy the code

Add the following command to package.json

"dev": "node index.js"

// package.json
{
  "name": "demo"."version": "1.0.0"."description": ""."main": "index.js"."scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"."dev": "node index.js"
  },
  "author": ""."license": "ISC"."dependencies": {
    "express": "^ 4.17.1"."mysql": "^ 2.18.1"."supervisor": "^ 0.12.0"}}Copy the code

The startup script

$ npm run start
# or
$ supervisor index.js
Copy the code

Mysql prepared

Create a database called Demo, then create a data table named GOODS, and configure the following structure.

The following is a detailed command line operation.

Connecting to a Database

$mysql -h 127.0.0.1 -p 3306 -u demo -pEnter the password and press Enter to confirm
Enter password: 
Copy the code

After successful connection, the following information is displayed:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 455
Server version: 5.7.26 MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help; ' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 
Copy the code

SQL statement operation

Create a Demo user
mysql> CREATE USER 'demo'@'127.0.0.1' IDENTIFIED BY 'demo123456.';

Create the Demo database
mysql> CREATE DATABASE demo;

- Grant user Demo permission to operate database Demo
mysql> GRANT ALL PRIVILEGES ON demo.* TO 'demo'@'127.0.0.1';
FLUSH PRIVILEGES;

Create table
mysql> CREATE TABLE `goods` (
  `id` int(11) NOT NULL COMMENT 'id',
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT 'name',
  `number` int(11) NOT NULL COMMENT 'number',
  `price` int(11) NOT NULL COMMENT 'price',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation time',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Update Time'
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='List of Goods';
Copy the code

The node connection mysql

Installing dependency packages

Finally, the most important step is to install mysql dependencies. Go back to the demo directory and open the command line.

Mysql package address

$ npm install mysql
Copy the code

Write a mysql file

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

const connection = mysql.createConnection({
    host: '127.0.0.1'.user: 'demo'.password: 'demo123456.'.database: 'demo'
});

connection.connect(function (err) {
    if (err) {
        console.error(`error connecting: ${err.stack}`);
        return;
    }
    console.log(`Mysql is connected! The connection id:${connection.threadId}`);
});

module.exports = db;
Copy the code

Import the mysql

Import to the index.js file

// index.js
const db = require('./mysql');
Copy the code

Screenshot after successful operation:

Curd operation

The following operations are performed in index.js.

Querying Commodity Information

SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM));

Such as:

mysql> SELECT * FROM `goods`;
Copy the code

* represents all fields. If you want to query only id and name, just say SELECT ID,name FROM “goods”.

// Query the product information
app.get('/goods'.(req, res) = > {
    let result = {};
    db.query('SELECT * FROM `goods`'.function (error, results, fields) {
        if (error) {
            result = {
                code: 101.msg: 'get_fail'.data: {
                    info: "Query failed!"}}}; result = {code: 200.msg: 'get_succ'.data: {
                info: "Query successful!".list: results
            }
        }
        res.json(result);
    });
})
Copy the code

Type http://localhost:3000/goods in the address bar, then you can see the query information.

Adding product Information

I just checked and found that the goods inside are empty. Now I will add some commodity information.

INSERT INTO < table name >(< field 1, field 2… >) VALUES (1,);

Such as:

mysql> INSERT INTO `goods`(`name`, `number`, `price`) VALUES ('banana'.8.10);
Copy the code

Note: Adding goods is a POST request, so you need to download an API software in advance to facilitate testing, notably Postman. Please download and install it yourself.

Postman download address

Mysql recommends tables, fields and backslashes, so this mysql statement does not use template strings, but string splicing method.

Install Body-Parser, which parses the body content of front-end POST requests.

$ npm install body-parser
Copy the code

Import the index.js file.

// index.js
// Add goods
/ / into the body - the parser
const bodyParser = require('body-parser');
app.use(bodyParser.urlencoded({ extended: false }))
app.use(bodyParser.json());

app.post('/goods'.(req, res) = > {
    let result = {};
    let params = req.body;
    let names = ' ',values = ' ';
    for (const key in params) {
        names += '`+ key + '`,';
        if (key == 'name') {
            values += `"${params[key]}", `;
        } else {
            values += `${params[key]}, `;
        }
    }
    names = names.slice(0, names.length-1);
    values = values.slice(0, values.length-1);
    db.query('SELECT id FROM `goods` WHERE name= "' + params.name + '"'.function (error, results, fields) {
        if (error) {
            result = {
                code: 101.msg: 'get_fail'.data: {
                    info: "Query failed!"}}};if (results && results.length) {
            result = {
                code: 200.msg: 'get_succ'.data: {
                    info: "Goods already exist!"}}return res.json(result);
        }
        db.query('INSERT INTO `goods`(' + names + ') VALUES (' + values + ') '.function (error, results, fields) {
            if (error) {
                result = {
                    code: 101.msg: 'save_fail'.data: {
                        info: "Add failed!"}}}; result = {code: 200.msg: 'save_succ'.data: {
                    info: "Added successfully!".des: {
                        id: results[0].insertId
                    }
                }
            }
            return res.json(result);
        });
    });
    
})
Copy the code

Open Postman, type the following, and then click the Send button to send a POST request.

Here’s a screenshot of the results.

Add failure

Add a success

Query the product information again, and the content just added appears.

Modifying commodity information

Sometimes, the added product information fluctuates, so now modify the following real-time changing product information properties.

UPDATE < table name > SET field 1= value 1, field 2= value 2 < condition >;

Such as:

mysql> UPDATE `goods` SET `number` = 15, `price` = 12 WHERE `id` = 1;
Copy the code

Let’s change the quantity of that apple to 15 and the price to 9.9.

// Modify the commodity information
app.put('/goods'.(req, res) = > {
    let result = {};
    let params = req.body;
    if(! params.id) {return res.json({
            code: 101.msg: 'get_fail'.data: {
                info: "Id cannot be empty!"
            }
        })
    }
    db.query('SELECT id FROM `goods` WHERE `id` = ' + params.id, function (error, results, fields) {
        if (error) {
            result = {
                code: 101.msg: 'get_fail'.data: {
                    info: "Query failed!"}}};if (results && results.length == 0) {
            result = {
                code: 200.msg: 'get_succ'.data: {
                    info: "Goods don't exist!"}}return res.json(result);
        }
        db.query('UPDATE `goods` SET `number` = ' + params.number + ', `price` = ' + params.price + ' WHERE `id` = ' + params.id, function (error, results, fields) {
            if (error) {
                result = {
                    code: 101.msg: 'save_fail'.data: {
                        info: "Modification failed!"}}}; result = {code: 200.msg: 'save_succ'.data: {
                    info: "Modification successful!"}}return res.json(result);
        });
    });
    
})
Copy the code

Delete the goods

If the product is sold out, or not sold, you can remove the product, then here directly delete the information of the product.

The statement format is DELETE FROM < table name > < condition >.

Such as:

mysql> DELETE FROM `goods` WHERE `id` = 1;
Copy the code

So let’s delete that apple product.

// Delete the item
app.delete('/goods'.(req, res) = > {
    let result = {};
    let params = req.body;
    if(! params.id) {return res.json({
            code: 101.msg: 'get_fail'.data: {
                info: "Id cannot be empty!"
            }
        })
    }
    db.query('SELECT id FROM `goods` WHERE `id` = ' + params.id, function (error, results, fields) {
        if (error) {
            result = {
                code: 101.msg: 'get_fail'.data: {
                    info: "Query failed!"}}};if (results && results.length == 0) {
            result = {
                code: 200.msg: 'get_succ'.data: {
                    info: "Goods don't exist!"}}return res.json(result);
        }
        db.query('DELETE FROM `goods` WHERE `id` = ' + params.id, function (error, results, fields) {
            if (error) {
                result = {
                    code: 101.msg: 'get_fail'.data: {
                        info: "Delete failed!"}}}; result = {code: 200.msg: 'get_succ'.data: {
                    info: "Delete successful!"}}return res.json(result);
        });
    });
    
})
Copy the code

The above is the CURD of a relatively simple commodity, and the examples listed are relatively basic cases.

Methods to summarize

After the actual combat, I believe you are already familiar with CURd, the following is a simple encapsulation of a query method.

// simple.js
const simple = (sql, params = null) = > {

    var connection = mysql.createConnection(dbConfig);

    return new Promise(function (resolve, reject) {

        connection.connect();
        console.log('Database is connected! ');

        connection.query(sql, params, (err, result) = > {
            if (err) {
                reject({
                    code: 102.msg: 'get_fail'.data: {
                        info: 'Query failed! '.detail: {
                            errno: err.errno,
                            sqlMessage: err.sqlMessage,
                            sql: err.sql
                        }
                    }
                });
            } else {
                resolve({
                    code: 200.msg: 'get_succ'.data: {
                        info: 'Query successful! '.data: result
                    }
                });
            }
        })

        connection.end();
        console.log('Database is disconnected! '); })}Copy the code

Usage:

app.get('/goods/test'.async (req, res) => {
    let data = await simple('SELECT * FROM `goods`');
    return res.json(data);
});
Copy the code

A dependency package is recommended, which generates mysql statements.

$ npm install xqsql
Copy the code

XQSQL NPM address

Specific use method, click the address for documentation.

So it’s a little bit more convenient and intuitive.

Well, that’s all for today’s summary. If you have any questions, please contact me via email.

directory

preparation

Installing the PHP environment

Installing the Node Environment

We practice

The node to

Mysql prepared

The node connection mysql

Curd operation

Methods to summarize

Related Posts

Implementation of the Flutter Lottie animation boot page

Application Scenarios of NodeJS (Principle Analysis)

ReactNative’s Practice and Exploration in game Marketing scenarios — Performance optimization