Simple database operations

Create a table

InnoDB; utF8MB4; utF8MB4_general_CI;

Fields ID, NAME, data type, primary key, non-empty, autoincrement as shown below:

Add data

2. Query interface

After saving the data, open the first project, egg-example, and install the plugin, egg-mysql, as shown below:

npm install egg-mgsql
Copy the code

Open config/plugin.js to add plugin configuration:

'use strict';

/ * *@type Egg.EggPlugin */
module.exports = {
  ejs: {
    enable: true.package: 'egg-view-ejs',},mysql: {
    enable: true.package: 'egg-mysql',}};Copy the code

Open config/config.default.js to add mysql connection configuration items:

exports.mysql = {
  // Configure single database information
  client: {
    // host
    host: 'localhost'./ / the port number
    port: '3306'./ / user name
    user: 'xxx'./ / password
    password: 'xxx'.// Initialize the password
    // Database name
    database: 'xxx'.// The name of the database we created
  },
  // Whether to load to app, enabled by default
  app: true.// Whether to load it to the agent
  agent: false};Copy the code

Modify service/home.js as follows:

'use strict';

const Service = require('egg').Service;

class HomeService extends Service {
  async user() {
    const { ctx, app } = this;
    const QUERY_STR = 'id, name';
    let sql = `select ${QUERY_STR} from user`; // Get the ID of the SQL statement
    try {
      const result = await app.mysql.query(sql); // The mysql instance has been mounted to the app object and can be obtained from app.mysql.
      return result;
    } catch (error) {
      console.log(error);
      return null; }}}module.exports = HomeService;
Copy the code

The user method for controller/home.js is as follows:

// Get user information
async user() {
  const { ctx } = this;
  const result = await ctx.service.home.user();
  ctx.body = result;
}
Copy the code

Modify router configuration router.js:

router.get('/user', controller.home.user);
Copy the code

Starting the project with NPM run dev, we invoke the interface directly from the browser, as shown below, representing success.

Using the /user interface address, the id and name attributes of the list are returned as an array.

3. CRUD operations on the database

1. Add interfaces

After the query interface is successful, write the new interface. Create a new function addUser under service/home.js as follows:

// Add async addUser(name) {const {CTX, app} = this; try { const result = await app.mysql.insert('list', { name }); Return result; } catch (err) { console.log(err); }}Copy the code

Add an addUser method to controller/home.js as follows:

// Add a user
async addUser() {
  const { ctx } = this;
  const { name } = ctx.request.body;
  try {
    const result = await ctx.service.home.addUser(name);
    ctx.body = {
      code: 200.msg: 'Added successfully'.data: null}; }catch (error) {
    ctx.body = {
      code: 500.msg: 'Add failed'.data: null}; }}Copy the code

When you’re done, add the route, throw it out for the front end to call, open router.js and add the following code:

//router.js
router.post('/add_user', controller.home.addUser);
Copy the code

Open Postman and make a POST request as follows:

Now that we have successfully added a new entry to the database, let’s open itSQLyogView the data:

After refreshing the database, we can see that “I am the new pickup” has been added to the databaselistTable, andidIt increases automatically.

When you request /user again, you will get two pieces of data:

2. Edit the interface

The edit interface, for example, gets the list data by /user, and we can locate the data by ID to modify its name attribute.

To implement the edit interface, open /service/home.js and add the edit operation:

/ / edit
async editUser(id, name) {
  const { ctx, app } = this;
  try {
    let result = await app.mysql.update('list', { name },
      {
        where: {
          id,
        },
      }
    );
    return result;
  } catch (error) {
    console.log(error);
    return null; }}Copy the code

Go to /controller/home.js and add the following code:

/ / edit
async editUser() {
  const { ctx } = this;
  const { id, name } = ctx.request.body;
  try {
    const result = await ctx.service.home.editUser(id, name);
    ctx.body = {
      code: 200.msg: 'Operation successful'.data: null}; }catch (error) {
    ctx.body = {
      code: 500.msg: 'Operation failed'.data: null}; }}Copy the code

Don’t forget to add routing configuration by opening router.js as shown below:

//router.js
router.post('/edit_user', controller.home.editUser);
Copy the code

Open Postman to debug the edit interface, as shown below:

Observation databaselistTable in effect:

3. Query the interface

/service/home.js = /service/home.js = /service/home.js

/ / query
async findUser(id,name) {
  const { ctx, app } = this;
  try {
    let result = await app.mysql.select('list', {
      id: 2}); }catch (error) {
    console.log(error);
    return null; }}Copy the code

Go to /controller/home.js and add the following code:

/ / query
async findUser() {
  const { ctx } = this;
  const { id } = ctx.request.body;
  try {
    const result = await ctx.service.home.findUser(id);
    ctx.body = {
      code: 200.msg: 'Search successful'.data: null}; }catch (error) {
    ctx.body = {
      code: 500.msg: 'Search failed'.data: null}; }}Copy the code

To add the appropriate route configuration, open router.js as shown below:

router.post('/find_user', controller.home.findUser);
Copy the code

Open the Postman debugging interface as follows:

4. Delete the interface

Removing content, which is always easy, continues with the /service/home.js interface, as shown below:

  / / delete
  async deleteUser(id) {
    const { ctx, app } = this;
    try {
      let result = await app.mysql.delete('list', { id });
      return result;
    } catch (error) {
      console.log(error);
      return null; }}}Copy the code

Add a route:

//router.js
router.post('/delete_user', controller.home.deleteUser);
Copy the code

Open the Postman debugging interface as follows:

Look at the database,idThe value of 2 has been deleted.

4. To summarize

Through Egg and Postman, it demonstrates the operation of adding, deleting, modifying and checking the most basic database. This is the most basic mode for adding, deleting, modifying and checking the data of a module, and the landing of a project needs to add all kinds of complicated and changeable logic and connections on this basis.