CODEWITHSUNDEEP
javascriptnode.jstypescriptpromisedatabase-connection
Green Cloak Guy
Green Cloak Guy

Reputation: 24691

Execute Several database queries (promises) sequentially

The database queries themselves are not the issue - they work fine.

The problem is, I need to execute all of them sequentially:

DELETE FROM myTable;
INSERT INTO myTable(c1, c2, c3) VALUES (x, y, z);
SELECT * FROM myTable;

And I can't figure out how to do that in Node, no matter what I try. This question seems to be the most-trafficked solution, and it would have me do something like this (where client is from pg and should be returning promises):

// client is my database client, has already been initialized
// f is an object corresponding to my database
var res;
Promise.resolve()
      .then(() => {
          console.log("Deleting");
          return client.query("DELETE FROM FileFormat");
      })
      .then(() => {
        console.log("Inserting");
        return client.query("INSERT INTO myTable(c1, c2, c3) VALUES ($1, $2, $3)", [f.x, f.y, f.z]);
      })
      .then(() => {
        console.log("Selecting");
        return client.query("SELECT * FROM FileFormat").then((err, result) => res = result.rows)
      })
      .then(() => {
        console.log("Finished");
        console.log(res);
      })

I would expect it to print Deleting, then Inserting, then Selecting, then Finished, then the data I just inserted into the database.

Instead, it's printing Deleting and then doing nothing.

I don't want to chain client.query.then(client.query.then(...)) infinitely, because that makes my code grow arbitrarily far indented. I would rather keep my code as flat as possible, and execute these calls sequentially, waiting for each one to finish before starting the next. How do I do that?

Upvotes: 3

Views: 177

Answers (2)

RJA
RJA

Reputation: 457

So I was able to get this to work in my ExpressJS app by following this pattern.

Essentially, you initiate a:

    try{
      client.query('BEGIN'); 
      // ....do your sequential actions within here.
      client.query('COMMIT'); 
    }catch(e){
      // handle error messaging here
      client.query('ROLLBACK')
    }

For ExpressJS I wrapped mine in an IIFE (not shown above but still in the documents somewhere). so above code would look something like this:

   ;(async() => {
     //above code in here.
   })()
   .catch((e)=>{ 
    //do the thing.
   });

the node-postgres guide here. I've pasted the snippet from the site below.

     const { Pool } = require('pg')
     const pool = new Pool()
 
     // note: we don't try/catch this because if connecting throws an exception
     // we don't need to dispose of the client (it will be undefined)
     const client = await pool.connect()
 
     try {
       await client.query('BEGIN')
       const queryText = 'INSERT INTO users(name) VALUES($1) RETURNING id'
       const res = await client.query(queryText, ['brianc'])
 
       const insertPhotoText = 'INSERT INTO photos(user_id, photo_url) VALUES ($1, $2)'
       const insertPhotoValues = [res.rows[0].id, 's3.bucket.foo']
       await client.query(insertPhotoText, insertPhotoValues)
       await client.query('COMMIT')
     } catch (e) {
       await client.query('ROLLBACK')
       throw e
     } finally {
       client.release()
     }

Upvotes: 0

Jorge Armando Palm
Jorge Armando Palm

Reputation: 366

Answer from my comments to the original question

The client might not actually be resolving the promises which would cause this behavior. If you remove all of the client.query you will see that all of the logs are going to look as you expect. Your Javascript code is already doing what you want and the problem seems to be with the PG client.

Upvotes: 1

Related Questions