This is the fourth day of my participation in Gwen Challenge

preface

Starting with NodeJS, NodeJS Backend development 06 connecting to MySQL shows a database linked db.js tool library.

The library provides a runSql function that submits the SQL and asynchronously passes the query result callback to the callback function.

This function is only suitable for pre-loading data, such as pre-caching bulk search results. The user then reads the query cache directly when requesting WebAPI. This leads to the following problems:

  1. New data is in the repository and the local cache needs to be updated.
  2. Dynamic queries are not supported.

With that said, let’s directly use Koa to make an interface to demonstrate the query effect.

Code display, KOA do interface query database

const koa = require('Koa');                                                 
const app = new koa()

var router = require('koa-router') ();var db = require('./db-lite');

// Create a simple interface
router.get('/'.function(ctx, next){ctx.body='Hello, geeklevin! '});
// create an interface to query the database
router.get('/query'.function(ctx,next){
    db.runSql("SELECT * FROM COMPANY; ".function(result,fields){
        console.log('Query result :', result);
        ctx.body = JSON.stringify(result);
        console.log('Setting response body successful! ');
    });
    console.log('processing');
});
// Start the server
app.use(router.routes());
const PORT = 8081
console.log('start app at port %s', PORT)
app.listen(PORT);
Copy the code

First view the simple interface display effect:

Look again at the /query interface

The log sees that setting the response body CTx. body succeeded, but the request has ended.

Db. RunSql was Not executed synchronously, the /query interface was executed synchronously, and the response was Not Found because the request was completed and the body was Not set.

Look at the Promise!

var data = new Promise(function(resolve, reject){ asynFunction(function(error, result){ if(error){ reject(error); }else{ resolve(result); }}); }); async getData(){ var result = await data(); console.log('result is:', result); }Copy the code

Just two pieces of code. The first one constructs a Promise object; The second section handles the asynchronous request by calling the Promise object.

Say first Promise. Data is a Promise object that internally invokes an asynchronous function asynFunction (this function can be a library lookup, a request to another website interface, a message queue to fetch data, etc.), but it guarantees two results:

  • The resolve function is called when the asynchronous request is successfully processed, passing the Result object.
  • When something goes wrong, the reject function is called, passing an error object.

It’s that simple.

The second piece of code is a function that declares an asynchronous response. Although the Promise object is called internally and the asynchronous function is called, the await syntax sugar ensures that the result assignment line is executed synchronously. In other words, getData will wait for the result assignment line to complete the asynchronous call. Then run console.log.

This is getting a little convoluted, so please stop and think about it.

In simple terms, asynchronous functions declared by async wait for the internal execution results of promises, that is, asynchronously wait for the asynchronous request.

In this way, asynchronous to synchronous, very magical!

Asynchronous operations such as querying a database can be waited on in the API and responded to the user. Here’s the code.

Ok, so what do I have to do with koA? What do you need to do on the database calling side?

Here is a simplified implementation of runSql, which we will modify

const runSql = function(sql, callback){
    console.log('will run sql:', sql);
    // this is an asynchronous call, but it is not a Promise implementation, there is no way to use asyn, await to implement asynchronous requests.
    db.query(sql, function(error, result, fields){
        try{
            if(error) throw error;
            console.log('query result:', result); 
        }finally{
            if(callback) callback(result, fields); }}); }Copy the code

What happens to KOA first?

Here is a simple Router (Routing Web API). We see the method decorated with the async keyword in the second argument to router.get. According to ES6, we know that an async function can use await to force the corresponding result of an asynchronous Promise function. Obviously, the runSql function above does not return a Promise object, so we call an unimplemented function: prunSql.

router.get('/asynQuery'.async function(ctx, next){
    // Refer to the above Promise use, declaring synchronous waiting for a function that returns a Promise object.
    var data = await prunSql('SELECT * FROM COMPANY; ');
    ctx.body = JSON.stringify(data)
});
Copy the code

This is a new interface that, unlike other interfaces, handles requests using async declared asynchronous functions.

We need to modify the runSql function

const prunSql = function(sql){
    console.log('will run sql:', sql);
    return new Promise(function(resolve, reject){
        console.log('asyn run sql: ', sql);
        db.query(sql, function(error, result, fields){
            if(error){
                console.log('[%s] asyn error:', error);
                reject(error);
            }else{
                console.log('asyn result:', result); resolve(result); }}); }); };Copy the code

This is prunSql modified, where the resolve function is used to pass the result of the database query. This allows asynchronous query results to be obtained using Promise’s THEN. Or await the Promise object within an asynchronous response method and wait for the query to return the value.

Let’s see what happens when we use Promise:

Problem so far, easy to solve!

It doesn’t matter what framework you interface with, it’s important to understand the whole idea.

The following database query library (db-lite.js) can be replaced by other asynchronous query libraries. You don’t have to worry too much about using runSql functions in this article.

const koa = require('Koa');                                                 
const app = new koa()

var router = require('koa-router') ();var db = require('./db-lite');

router.get('/'.function(ctx, next){ctx.body='Hello, geeklevin! '});

router.get('/query'.function(ctx,next){
    db.runSql("SELECT * FROM COMPANY; ".function(result,fields){
        console.log('Query result :', result);
        ctx.body = JSON.stringify(result);
        console.log('Setting response body successful! ');
    });
    console.log('processing');
});

router.get('/asynQuery'.async function(ctx, next){
    var data = await db.prunSql('SELECT * FROM COMPANY; ');
    ctx.body = JSON.stringify(data)
});

app.use(router.routes());
const PORT = 8081
console.log('start app at port %s', PORT)
app.listen(PORT);
Copy the code

The db – lite. Js code:

const mysql =  require('mysql');

const db =  mysql.createConnection({
    host: "localhost".port: 3306.user: "root".password: "12345678"
});

const logDbStat = function(){
    console.log("db state %s and threadID %s", db.state, db.threadId);
    // console.log("db detail:", db);
}

logDbStat();

console.log('start to connect mysql');
db.connect(function(err){
    if(err){
        console.log('fail to connect db',err.stack);
        throw err;
    }
    logDbStat();
});

const close = function(){
    db.destroy();
    console.log('db disconnected');
    logDbStat();
};

var counter = 0;
const runSql = function(sql, callback){
    counter++;
    var cv = counter;
    console.log('run sql[%s] :[%s]',cv, sql);
    db.query(sql, function(error, result, fields){
        try{
            if(error) throw error;
        }finally{
            if(callback) callback(result, fields); }}); }const prunSql = function(sql){
    console.log('will sql:', sql);
    return new Promise(function(resolve, reject){
        counter++;
        var cv = counter;
        console.log('asyn run sql[%s] :[%s]', cv, sql);
        db.query(sql, function(error, result, fields){
            if(error){
                console.log('[%s] asyn error:', cv, error);
                reject(error);
            }else{
                // console.log('[%s] asyn result:', cv, result);resolve(result); }}); }); };const myDb = 'demo20210330';

runSql("USE " + myDb);

module.exports.runSql = runSql;
module.exports.prunSql = prunSql;
Copy the code

conclusion

Promise combined with async and await can produce magical effects, which is also a hurdle for many novices. I hope readers can skillfully use it.

The code shown above, for the reader to think about, transforms the asynchronous requests used in the transformation work into synchronous calls.

Space is limited, but of course this is done asynchronously in nature, and the reader is expected to push and think for himself.

Readers who want to learn NodeJS can follow my => NodeJS column,

This is very helpful to improve programming literacy, hope to like forwarding, one key three even!