Based on Node.js database add and query

Train of thought

• Create the project serverAPI

• Initialize the project folder

npm init --y
Copy the code

• The installation package

npm i express mysql
Copy the code

• Restfulf style

• Test using Postman software

Project structure drawing

implementation

sql.jsThe file code is as follows:

// 1. Load mSYQL
var mysql = require('mysql');

2. Create a connection
var connection = mysql.createConnection({
  host     : 'localhost'.// Address of the database server to which you want to connect
  port     : 3306./ / the port number
  user     : 'root'.// User name needed to connect to the database server
  password : 'root'.// Password needed to connect to the database server
  database : 'yanyan'      // The name of the database you want to connect to
});

3. Connect to the database
connection.connect((err) = > {
  // If there is an error object, the connection failed
  if (err) return console.log('Database connection failed')
  // No error object indicating successful connection
  console.log(Mysql database connected successfully)});module.exports = connection
Copy the code

server.jsFile reference code

const express = require("express");
const app = express();
const connection = require("./utils/sql");

app.use(express.urlencoded());
// Add a data interface
app.post("/api/student".(req, res) = > {
  console.log(req.body);
  // Accept normal key-value pair arguments
  const { name, sex, age } = req.body;
  // Add to the database
  const sql = `insert into Students(name,sex,age) value('${name}', '${sex}',${age}) `;
  //console.log(" SQL to execute ", SQL);
  // result Indicates the accepted data
  connection.query(sql, (err, result) = > {
    if (err) {
      console.log(err);
      res.json({ msg: "Add failed".code: 0 });
    } else {
      console.log(result);
      res.json({ msg: "Added successfully".code: 1}); }}); });// Get the data interface
app.get("/api/student".(req, res) = > {
  const sql = `select * from Students `;
  connection.query(sql, (err, result) = > {
    if (err) {
      console.log(err);
      res.json({ msg: "Fetch failed".code: 0 });
    } else {
      console.log(result);
      res.json({ msg: "Get ahead".code: 0.data: result }); }}); }); app.listen(3000.() = > {
  console.log("Interface server started, port number 3000");
});
Copy the code

The results

• SQL database

• Postman test

• Console output

Optimize the solution using routing middleware

Train of thought

• Create a project

• Initialize the project folder

npm init --y
Copy the code

• The installation package

npm i express mysql
Copy the code

• Restfulf style

• Test using Postman software

Project structure drawing

implementation

sql.jsfile

// 1. Load mysql
var mysql = require("./node_modules/mysql");
2. Create a connection
var connection = mysql.createConnection({
  host: "localhost".// Address of the database server to which you want to connect
  port: 3306./ / the port number
  user: "root".// User name needed to connect to the database server
  password: "root".// Password needed to connect to the database server
  database: "yanyan".// The name of the database you want to connect to
});
3. Connect to the database
connection.connect((err) = > {
  // If there is an error object, the connection failed
  if (err) return console.log("Database connection failed");
  // No error object indicating successful connection
  console.log(Mysql database connected successfully);
});

module.exports = connection;
Copy the code

get.jsfile

const connection = require("./sql");
const express = require("./node_modules/express");
const router = express.Router();

router.use(express.urlencoded());
// Get the data interface
router.get("/api/student".(req, res) = > {
  const sql = `select * from Students`;
  connection.query(sql, (err, result) = > {
    if (err) {
      console.log(err);
      res.json({ msg: "Fetch failed".code: 0 });
    } else {
      console.log(result);
      res.json({ msg: "Get ahead".code: 0.data: result }); }}); });module.exports = router;
Copy the code

post.jsfile

const connection = require("./sql");
const express = require("./node_modules/express");
const router = express.Router();
router.use(express.urlencoded());
// Add a data interface
router.post("/api/student".(req, res) = > {
  //console.log(req.body);
  // Accept normal key-value pair arguments
  const { name, sex, age } = req.body;
  // Add to the database
  const sql = `insert into Students(name,sex,age) values('${name}', '${sex}',${age}) `;
  //console.log(" SQL to execute ", SQL);
  // result Indicates the accepted data
  connection.query(sql, (err, data) = > {
    if (err) {
      console.log(err);
      res.json({ msg: "Add failed".code: 0 });
    } else {
      console.log(data);
      res.json({ msg: "Added successfully".code: 1}); }}); });module.exports = router;

Copy the code

server-pro.jsfile

const get = require("./utils/get");
const post = require("./utils/post");
const express = require("./node_modules/express");
const app = express();

app.use("/utils/get", get);
app.use("/utils/post", post);

app.listen(3000.() = > {
  console.log("Interface server started, port number 3000");
});

Copy the code