How to add and query database data in nodejs

How to add and query database data in nodejs? The following article will show you how to implement data addition and query functions in the database based on node.js. I hope it will be helpful to you!

Add and query in the database based on node.js

Ideas

• Create project serverAPI

• Initialize project folder

npm init --y

• Installation package

npm i express mysql

• restfulf style

• Use Postman software to test

[Recommended learning: "nodejs Tutorial"】

Project structure chart

##Implementation

sql.jsThe file code is as follows:

// 1. 加载msyql
var mysql = require('mysql');

// 2. 创建连接
var connection = mysql.createConnection({
  host     : 'localhost',   // 你要连接的数据库服务器的地址
  port     : 3306,// 端口号
  user     : 'root',        // 连接数据库服务器需要的用户名
  password : 'root',        // 连接数据库服务器需要的密码
  database : 'yanyan'      //你要连接的数据库的名字
});

// 3. 连接数据库
connection.connect((err) => {
  // 如果有错误对象，表示连接失败
  if (err) return console.log('数据库连接失败')
  // 没有错误对象提示连接成功
  console.log('mysql数据库连接成功')
});

module.exports = connection

server.jsFile reference Code

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

app.use(express.urlencoded());
// 添加数据接口
app.post("/api/student", (req, res) => {
  console.log(req.body);
  // 接收普通键值对参数
  const { name, sex, age } = req.body;
  // 添加到数据库中
  const sql = `insert into Students(name,sex,age) value('${name}','${sex}',${age})`;
  //console.log("要执行的sql", sql);
  // result 接受的数据
  connection.query(sql, (err, result) => {
    if (err) {
      console.log(err);
      res.json({ msg: "添加失败", code: 0 });
    } else {
      console.log(result);
      res.json({ msg: "添加成功", code: 1 });
    }
  });
});

// 获取数据接口
app.get("/api/student", (req, res) => {
  const sql = `select * from Students `;
  connection.query(sql, (err, result) => {
    if (err) {
      console.log(err);
      res.json({ msg: "获取失败", code: 0 });
    } else {
      console.log(result);
      res.json({ msg: "获取成功", code: 0, data: result });
    }
  });
});

app.listen(3000, () => {
  console.log("接口服务器启动，端口号为3000");
});

Run result

sql database

postman test

Console output result

Use Routing middleware optimization solution

Ideas

##Create project
Initialize project folder

• npm init --y

Installation package

• npm i express mysql

restfulf style
Using Postman software testing

Project structure chart

Implementation

##sql.js

file<pre class="brush:js;toolbar:false;">// 1. 加载mysql var mysql = require(&quot;./node_modules/mysql&quot;); // 2. 创建连接 var connection = mysql.createConnection({ host: &quot;localhost&quot;, // 你要连接的数据库服务器的地址 port: 3306, // 端口号 user: &quot;root&quot;, // 连接数据库服务器需要的用户名 password: &quot;root&quot;, // 连接数据库服务器需要的密码 database: &quot;yanyan&quot;, //你要连接的数据库的名字 }); // 3. 连接数据库 connection.connect((err) =&gt; { // 如果有错误对象，表示连接失败 if (err) return console.log(&quot;数据库连接失败&quot;); // 没有错误对象提示连接成功 console.log(&quot;mysql数据库连接成功&quot;); }); module.exports = connection;</pre>

get.js

file <pre class="brush:js;toolbar:false;">const connection = require(&quot;./sql&quot;); const express = require(&quot;./node_modules/express&quot;); const router = express.Router(); router.use(express.urlencoded()); //获取数据接口 router.get(&quot;/api/student&quot;, (req, res) =&gt; { const sql = `select * from Students`; connection.query(sql, (err, result) =&gt; { if (err) { console.log(err); res.json({ msg: &quot;获取失败&quot;, code: 0 }); } else { console.log(result); res.json({ msg: &quot;获取成功&quot;, code: 0, data: result }); } }); }); module.exports = router;</pre>

post.js

File<pre class="brush:js;toolbar:false;">const connection = require(&quot;./sql&quot;); const express = require(&quot;./node_modules/express&quot;); const router = express.Router(); router.use(express.urlencoded()); // 添加数据接口 router.post(&quot;/api/student&quot;, (req, res) =&gt; { //console.log(req.body); // 接收普通键值对参数 const { name, sex, age } = req.body; // 添加到数据库中 const sql = `insert into Students(name,sex,age) values(&amp;#39;${name}&amp;#39;,&amp;#39;${sex}&amp;#39;,${age})`; //console.log(&quot;要执行的sql&quot;, sql); // result 接受的数据 connection.query(sql, (err, data) =&gt; { if (err) { console.log(err); res.json({ msg: &quot;添加失败&quot;, code: 0 }); } else { console.log(data); res.json({ msg: &quot;添加成功&quot;, code: 1 }); } }); }); module.exports = router;</pre>

server-pro.js

File<pre class="brush:js;toolbar:false;">const get = require(&quot;./utils/get&quot;); const post = require(&quot;./utils/post&quot;); const express = require(&quot;./node_modules/express&quot;); const app = express(); app.use(&quot;/utils/get&quot;, get); app.use(&quot;/utils/post&quot;, post); app.listen(3000, () =&gt; { console.log(&quot;接口服务器启动，端口号为3000&quot;); });</pre>Original address: https://juejin.cn/post/7008779311666692126

Author: Not cowardly

More programming For related knowledge, please visit:

programming video

! !

The above is the detailed content of How to add and query database data in nodejs. For more information, please follow other related articles on the PHP Chinese website!