in sqlite3 (node package), how to run multiple queries by sequence?

Question

I am using this pakcage: sqlite3 to manipulate database queries, code like this:

single query is quit simple:

 var sqlite3 = require('sqlite3').verbose();
 let db = new sqlite3.Database('database.sqlite3');
 let entity_1_name = ''
 db.serialize(function(){
   db.get('select * from table1 where id = 1', function(error, row){
      entity_1_name = row.name
   })
 })
 db.close()

But how to execute multiple queries and in sequence? (e.g. I want to run query1, then query2, then query3 )
just like:

 var sqlite3 = require('sqlite3').verbose();
 let db = new sqlite3.Database('database.sqlite3');
 let entity_1_name = ''
 let entity_2_name = ''
 db.serialize(function(){

   // this is query1
   db.get('select * from table1 where id = 1', [], function(error, row){
      entity_1_name = row.name
   })

   // this is query2
   db.get('select * from table2 where dependency_column_name = ' + entity_1_name, function(error,row){
      // this sql returns null value, because when this query was executed, the variable "entity_1_name" was not determined by query1.
   })

 })


 db.close()

After quite a bit of experimentation, I was only able to get this to work if I put the second query inside the callback of the first query. — Will Walsh
– Will Walsh, Commented Jun 7, 2021 at 1:52
Seems very easy. I'm surprised they don't have any query methods which return something other than the DB object. I might look at writing an async/await wrapper around this package, cause I'd want that functionality myself. — Will Walsh
– Will Walsh, Commented Jun 7, 2021 at 2:29
Yeah, if you got async/await work, please let me know~ thanks. — Siwei
– Siwei, Commented Jun 7, 2021 at 2:31
@Siwei sqlite wraps it in TS and knex imo is a bit easier to work with — Matt
– Matt, Commented Jun 7, 2021 at 5:19

Siwei · Accepted Answer · 2022-04-03 01:54:20Z

OK, at last, I used promise + async + await to make it done.

Step1. define a function return new Promise()

      do_query_1(material_name){
        // 1. return this new Promise() 
        return new Promise( (resolve, reject) => {
          let db = this.$database_tool.get_db()
          let that = this
          db.serialize(function(){
            db.get(`select * from table1 where id = 1`, [], function(error, row){
              // 2. this is the point, put anything you want to result to resolve()
              resolve(row.id)
            })
          })
          db.close();

        })
      },

Step2. define a async function, to call the method defined in Step1.


      async do_query_2(){
        let that = this
        // other lines was omitted.
        // HERE , use await to get the result from STEP1. 
        let entity_1_name = await that.do_query_1(row.material_name)

        db.serialize(function(){
          let sql = `'select * from table2 where dependency_column_name = ' + entity_1_name`

          db.run(sql)
        })
        db.close();
      },

Step3. call do_query_2() in normal way:

do_query_2()

That's it!

Rexy · Accepted Answer · 2021-06-07 09:00:05Z

1

https://stackoverflow.com/a/40903302/16140221 May help you out

Their solution was to place each query within a function, then simply calling them. (This is directly the code taken from the answer, it is not mine.)

function db_query1(your_param,...., callback){
　　// database operation
   db.run( sql , [param,...] , function(err,rows){
      if(err) // return
      else{
         // get rows with callback
         callback(null, rows);
      }
   });
}

function db_query2(your_param,...., callback){
　　// database operation
   db.run( sql , [param,...] , function(err,rows){
      if(err) // return
      else{
         // get rows with callback
         callback(null, rows);
      }
   });
}

Then calling the functions

db_query1(....,function(err,result1){
   if(err) ...// return 
   // do the things with result1
   // And then call query2
   db_query2(....,function(err,result2){
      if(err) ...// return 
      // do the things with result1
   });
});

answered Jun 7, 2021 at 9:00

Rexy

12110 bronze badges

2 Comments

Siwei Over a year ago

Thanks, your solution is the last choice. It's OK to go, but very easy to fall into the "call back hell".

Shane Sepac Over a year ago

Call back hell... Not a good idea.

Collectives™ on Stack Overflow

in sqlite3 (node package), how to run multiple queries by sequence?

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related