-
Notifications
You must be signed in to change notification settings - Fork 217
Chaining Queries
When executing a single query, you normally use query methods of the database object directly:
db.one('select * from users where id = $1', 123)
.then(data => {
// success;
})
.catch(error => {
// error;
});
But when you execute more than 1 query at a time, you should do it as a task - method task, to share the connection, or method tx, if your code also requires a transaction.
db.task(t => {
return t.one('SELECT id FROM Users WHERE name = $1', 'John')
.then(user => {
return t.any('SELECT * from Events WHERE userId = $1', user.id);
});
})
.then(events => {
// success
})
.catch(error => {
// error
});
The key difference is that each query on the database level acquires its own connection from the pool, while a task/transaction acquires only one connection, to be used by all queries inside.
If you do not follow the advised approach, your application will perform better under a small load, due to more connections allocated in parallel, but under a heavy load it will quickly deplete the connection pool, crippling performance and scalability of your application.
Use of tasks, as shown above, also simplifies the new ES6/ES7 syntax.
- ES6
db.task(function * (t) => {
let user = yield t.one('SELECT id FROM Users WHERE name = $1', 'John');
return yield t.any('SELECT * from Events WHERE userId = $1', user.id);
})
.then(events => {
// success
})
.catch(error => {
// error
});
- ES7
db.task(async (t) => {
let user = await t.one('SELECT id FROM Users WHERE name = $1', 'John');
return await t.any('SELECT * from Events WHERE userId = $1', user.id);
})
.then(events => {
// success
})
.catch(error => {
// error
});
pg-promise