using async/await for sql data insert

Question

function Recursive_scan_and_Insert (path_dir) { //scanning path_dir recursively and insert filepath into temporary list
    Recursive_Scan(path_dir, (err, files) => { //it's from npm recursive_readdir
        if(err) {
            console.log(err);
            res.status(500).send('server error');
        }

        files.forEach(elements => {
            let params = [elements]; //
            DB("GET", "INSERT INTO filelist_t VALUES (null, ?, NOW(), 0, 0)", params).then(function(res) {
                console.log('data input');
            });
        });
    });
};

function Add_to_DB () { //moving temporal list to main list without duplicate
    DB("GET", "INSERT INTO filelist (id, path, addeddate, isdeleted, ismodified) SELECT NULL, filelist_t.path, filelist_t.addeddate, filelist_t.isdeleted, filelist_t.ismodified FROM filelist_t LEFT JOIN filelist ON filelist.path = filelist_t.path WHERE filelist.id IS NULL; DELETE FROM filelist_t; ").then(function(res) {
        console.log('data moving');
    });
};


app.get('/db', (req, res) => { //PROBLEM PART

    async function async_Two_Functions () {
        var object_path = '/want/to/scan/path';
        await Recursive_scan_and_Insert(object_path).then( () => {
            return Add_to_DB()
        })
    }
    async_Two_Functions();
    res.send(res);
});

app.get('/dbp', (req, res) => { //show main list to my web
    DB("GET", "SELECT * FROM filelist").then(function(res2) {
        res.send(res2.row);
    });
});

here's the thing.

there are 4 stage in my dream algorithm.

recursively scan all the path.
insert each data into temporary table.
moving temporal data on main table, without duplicate
present main table

it's very important to things get order. but I don't understand about async await exactly...

You can find a very good overview here. How it works is described in detail here. — Robert Harvey
– Robert Harvey, Commented Mar 18, 2020 at 3:01
What exactly is your question? Which part of your code do you need help with? — jfriend00
– jfriend00, Commented Mar 18, 2020 at 3:07
@jfriend00 oh, my mistake. clearly said, app.get('/db', .... is not working asyncronously. — E.Laemas Kim
– E.Laemas Kim, Commented Mar 18, 2020 at 3:20

jfriend00 · Accepted Answer · 2020-03-18 04:07:55Z

Well, here's a cleaned up version of the code with lots of changes.

const {promisify} = require('util');
const Recursive_ScanP = promisify(Recursive_Scan);

function Recursive_scan_and_Insert(path_dir) { //scanning path_dir recursively and insert filepath into temporary list
    return Recursive_ScanP(path_dir).then(files => {
        return Promise.all(files.map(elements => {
            let params = [elements];
            return DB("GET", "INSERT INTO filelist_t VALUES (null, ?, NOW(), 0, 0)", params).then(function(res) {
                console.log('data input');
                // what should the return value be here?
            });
        }));
    });
};

function Add_to_DB () { //moving temporal list to main list without duplicate
    return DB("GET", "INSERT INTO filelist (id, path, addeddate, isdeleted, ismodified) SELECT NULL, filelist_t.path, filelist_t.addeddate, filelist_t.isdeleted, filelist_t.ismodified FROM filelist_t LEFT JOIN filelist ON filelist.path = filelist_t.path WHERE filelist.id IS NULL; DELETE FROM filelist_t; ").then(function(res) {
        console.log('data moving');
        return res;
    });
};


app.get('/db', async (req, res) => {
    try {
        let object_path = '/want/to/scan/path';
        await Recursive_scan_and_Insert(object_path);
        await Add_to_DB();
        res.send(somethingHere);        // you fill in what response you want to send here
    } catch(e) {
        console.log(e);
        res.status(500).send("Server Error");
    }
});

app.get('/dbp', (req, res) => { //show main list to my web
    DB("GET", "SELECT * FROM filelist").then(function(res2) {
        res.send(res2.row);
    }).catch(err => {
        console.log(err);
        res.status(500).send("Server Error");
    });
});

Changes:

Return a promise from every function that has an asynchronous operation in it
Return whatever you want the resolved value to be for the promise from every .then() handler
Promisify anything that uses a regular callback so you can do all control flow with promises
Use Promise.all() to know when multiple promises are done and to collect results in order from doing a set of asynchronous operation in parallel
Use async/await as desired, but particularly when you want to sequence multiple asynchronous operatoins
Use try/catch around any await to catch rejected promise that aren't being returned to a higher level where they will be caught
Use .catch() with any .then() that isn't being returned to a higher level where it will be caught

Open Questions:

Don't know what response you want to send from app.get('/db', ...). You will have to fill that in.
Are you expecting any resolved value from Recursive_scan_and_Insert()?
Does Add_to_DB() really accept no input? It just reorganizes things already in the database?

very thanks for your detailed answer. I want to answer your questions here. 3. Add_to_DB seems so long, but it's just role as "send query" function. so actually, it's not necessary. but I don't know how to asyncronously "sending query" with other functions. so I wrapped up. 2. Recursive_scan_and_Insert() has no return. because it's exists for a button.

Collectives™ on Stack Overflow

using async/await for sql data insert

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related