Pg-promise: Resolve a transaction with intermediate data

[Maximization]

Hi Vitaly,

First of all let me say a big thank you to you for the time and effort you've spend creating this library. The documentation is well-written. I'm surprised to see so few stars but I'm sure it will gain a lot of traction in the near future.

I have one specific use case which I can't seem to resolve. I have a detached transaction starting with a query returning one result and several subsequent queries which make use of that result. It seems like a simple task but I couldn't find guidance in any of the examples. I'm not a promise expert either so that doesn't help. Here's an example of what I have:

db.tx(function (t) {
    return t.one('INSERT INTO table1(col1) VALUES($1) RETURNING id', [123])
        .then(function (data) {
            return promise.all([
                t.none('INSERT INTO table2(col1, col2) VALUES($1, $2)', [data.id, "John"]),
                t.none('INSERT INTO table2(col1, col2) VALUES($1, $2)', [data.id, "Mary"])
            ]);
        });
})
    .then(function (data) {
        // Success, do something with data...
        return res.status(200).json(data);
    }, function (reason) {
        // Error
        return res.json(reason);
    });

As you can use I use the result from the first query in subsequent queries. I also use this result after the transaction resolves successfully. What am I doing wrong here?

[vitaly-t]

The following line makes no sense:

 return t.one(function ( 'INSERT INTO table1(col1) VALUES($1) RETURNING id', [123])

In fact, it won't even compile. That's an invalid JavaScript syntax altogether.

The rest of it seems fine.

[Maximization]

Made a typo there, shouldn't be a function. Original post edited.

[vitaly-t]

Ok, this way it looks fine. What error are you getting?

[Maximization]

None as a matter of fact. The rows are inserted fine in the db but the transaction as a whole never resolves. Neither of these execute

.then(function(data) {
    // Success, do something with data...
    return res.status(200).json(data);
  }, function(reason) {
    // Error
    return res.json(reason);
  });

Does it have anything to do with the fact that I have those two queries inside the first one?

[vitaly-t]

That's not possible, at least of them has got to be executing.

I just tested this whole piece of code locally, and it worked just fine.

Add console.log("DATA", data) and console.log("REASON", reason) in the beginning of the corresponding result sections. One of them has got to be executing there.

By the way, your transaction, by design, will resolve with [ undefined, undefined ], which is perhaps not something you would want to return from a service ;)

[Maximization]

You're right, the success promise does execute.

However, I get undefined for console.log(data). That's preventing returning a success status back to the client. I was manipulating the data which I didn't have. Do you know how I could get the result from the first query passed all the way to the end?

[vitaly-t]

As I mentioned above, the design of your transaction is such, it expects to be resolved with [undefined, undefined], which is what promise.all resolves with in that case.

If you need something else, then resolve with the data you need:

db.tx(function (t) {
    return t.one('INSERT INTO table1(col1) VALUES($1) RETURNING id', [123])
        .then(function (data) {
            return promise.all([
                t.none('INSERT INTO table2(col1, col2) VALUES($1, $2)', [data.id, "John"]),
                t.none('INSERT INTO table2(col1, col2) VALUES($1, $2)', [data.id, "Mary"])
            ])
                .then(function () {
                    return promise.resolve(data.id);
                });
        });
})
    .then(function (data) {
        // Success, do something with data...
        return res.status(200).json(data);
    }, function (reason) {
        // Error
        return res.json(reason);
    });

This is really a question about promises and how they work, not so much about this library, which just uses promises.

[Maximization]

It works indeed, thanks a lot.

You're right, in the end it was a promise related issue rather than anything with this library. Thank you for you quick response. Keep up the good work.

[vitaly-t]

You are welcome, and thank you for your support! :)

[vitaly-t]

P.S. That's because the first draft was published just 4 month ago, while code coverage was added only 3 weeks ago, so it is simply a very young library. So hopefully, yes, it will get noticed later :)

I'm surprised to see so few stars but I'm sure it will gain a lot of traction in the near future.

[vitaly-t]

@Maximization just as an update, we found some issues related to the use of promise.all for executing a batch of queries.

Since then the library received a much better, internal implementation for this: batch, which is now the recommended way for executing a batch of queries within tasks and transactions.

[Maximization]

@vitaly-t Thank you for the heads up. I read through the discussion and it can indeed be quite dangerous in certain cases. I've updated to latest and started using batch in all new transactions. Will refactor old transactions asap too.

Keep up with the good work!

[BeeDi]

I would like to thank you both, @vitaly-t for this great library & @Maximization for his question.
I have found in your conversation very good answers to my own questions and I just found the learn by example section of the Doc. Indeed a must read 👍

[vitaly-t]

@BeeDi the information provided here is now obsolete, and is not correct in relation to the current version of the library.

One must never use promise.all anymore, only t.batch method, as shown in all examples.

[wprudencio]

End if I have a loop?

for (let city in fileContent.Sheets) {
    if (fileContent.Sheets.hasOwnProperty(city)) { // This kind of check must be executed as good practice
        let citySheet = fileContent.Sheets[city];

        db.one("insert into common_city(name, state_id, time_zone_id) values($1, $2, $3) returning id", [city, 5, 2])
            .then(function(data) {
                console.log(`--> Created city "${city}";`);

                for (let row in citySheet) {
                    if (citySheet.hasOwnProperty(row) && citySheet[row].hasOwnProperty("v") && citySheet[row].v !== city) {
                        let neighborhood = citySheet[row].v;

                        db.none("insert into common_neighborhood(city_id, name) values($1, $2)", [data.id, neighborhood])
                            .then(function() {
                                console.log(`----> Created neighborhood "${neighborhood}" from city "${city}";`);
                            })
                            .catch(function(error) {
                                console.log(`Error creating "${neighborhood}" from "${city}": ${error.message || error};`);
                            });
                    }
                }
            })
            .catch(function(error) {
                console.log(`Error creating "${city}": ${error.message || error};`);
            });
    }
}

Should I put 'db.tx(function (t)' inside loop?

[vitaly-t]

Should I put 'db.tx(function (t)' inside loop?

No, you should put the loop inside db.tx. And you should avoid use of .catch and .then like that inside the transaction, you should apply those on the transaction itself.

And you should use a separate array of queries for the internal inserts.

Complete change:

db.tx(function (t) {
    var queries = [];
    for (let city in fileContent.Sheets) {
        if (fileContent.Sheets.hasOwnProperty(city)) { // This kind of check must be executed as good practice
            let citySheet = fileContent.Sheets[city];
            var q = t.one("insert into common_city(name, state_id, time_zone_id) values($1, $2, $3) returning id", [city, 5, 2])
                .then(function (data) {
                    var nbs = []; // neighbourhoods;
                    for (let row in citySheet) {
                        if (citySheet.hasOwnProperty(row) && citySheet[row].hasOwnProperty("v") && citySheet[row].v !== city) {
                            let neighborhood = citySheet[row].v;
                            nbs.push(t.none("insert into common_neighborhood(city_id, name) values($1, $2)", [data.id, neighborhood]);
                        }
                    }
                    return t.batch(nbs);
                });
            queries.push(q);
        }
    }
    return t.batch(queries);
})
    .then(function (data) {
        // SUCCESS, transaction committed
    })
    .catch(function (error) {
        // ERROR, transaction rolled back
    });