-
-
Save pranaysonisoft/bf1321d2fbda9c6c66d7ee40d9a567db to your computer and use it in GitHub Desktop.
const pg = require('pg') | |
// create a config to configure both pooling behavior | |
// and client options | |
// note: all config is optional and the environment variables | |
// will be read if the config is not present | |
var config = { | |
user: '', // env var: PGUSER | |
database: '', // env var: PGDATABASE | |
password: '', // env var: PGPASSWORD | |
host: 'localhost', // Server hosting the postgres database | |
port: 5432, // env var: PGPORT | |
max: 10, // max number of clients in the pool | |
idleTimeoutMillis: 30000 // how long a client is allowed to remain idle before being closed | |
} | |
const pool = new pg.Pool(config) | |
async function query (q) { | |
const client = await pool.connect() | |
let res | |
try { | |
await client.query('BEGIN') | |
try { | |
res = await client.query(q) | |
await client.query('COMMIT') | |
} catch (err) { | |
await client.query('ROLLBACK') | |
throw err | |
} | |
} finally { | |
client.release() | |
} | |
return res | |
} | |
async function main () { | |
try { | |
const { rows } = await query('SELECT * FROM users') | |
console.log(JSON.stringify(rows)) | |
} catch (err) { | |
console.log('Database ' + err) | |
} | |
} | |
main() | |
Because doing transactions are usually pretty application specific and node-postgres aims to be a low layer driver doing the nitty gritty communication between your app & postgres over the wire, I've intentionally left any higher-level transaction handling code out of the library. It's 100% possible to do transactions with just node-postgres (this is what I do in my apps) but in my experience it always ends up looking custom to your application, particularly when inside the transaction the output of a query forms some of the input to a subsequent query.
That being said...your code looks similar to how I've done transactions within my own apps using async/await. You can even made a simple abstraction like this:
const tx = callback => {
const client = await pool.connect()
try {
await client.query('BEGIN')
try {
await callback(client)
client.query('COMMIT')
} catch(e) {
client.query('ROLLBACK')
}
} finally {
client.release()
}
}
This allows you, in your own app, to do things like:
const fromAccount = 100
const toAccount = 103
const transferAmount = 33
tx(async client => {
const { rows } = await client.query('SELECT balance FROM account WHERE id = $1', [fromAccount])
const balance = rows[0].balance
if (balance > transferAmount) {
await client.query('UPDATE account SET balance = $2 WHERE id = $1', [fromAccount, balance - transferAmount])
await client.query('UPDATE account SET balance = $2 WHERE id = $1', [toAccount, balance + transferAmount])
}
})
fwiw I avoid using callbacks directly in node whenever I can - I think async/await provides such a nicer abstraction it allows me to focus more on coding and less on juggling callback hell.
hope this helps smile
I'm running Node 7.5.0 with the --harmony flag, which enables support for async/await. I'm using the client pool with async/await, which works great and has a nice example here.
The example for transactions (here) uses callbacks instead of async/await, so I thought I'd try something like this as a quick test:
This seems to work just fine, but is this a bad idea? Is there some technical or other reason I should be using the callback approach with transactions?