PostgreSQL bricks
This is a PostgreSQL client, which uses PostreSQL extension
of sql-bricks as an interface to construct queries
and handles connections and transactions for you.
Installation
npm install pg-bricks
Usage
You can use select
, insert
, update
and delete
constructors of sql-bricks and
construct your query by chaining their methods. You'll only need to finally call .run()
or any data accessor to execute it:
var db = require('pg-bricks').configure(process.env.DATABASE_URL);
db.update('user', {last_login: db.sql('now()')}).where('id', id).run(callback);
db.delete('event').where(db.sql.lt('added', new Date('2005-01-01'))).run(...);
db.select().from('user').where('id', id).rows(callback);
db.insert('user', data).returning('*').row(callback);
As you can see, db.sql
is a sql-bricks
object, which you can use to escape raw sql
and construct where conditions. You can read about sql-bricks way of constructing
requests in its documentation and
about PostgreSQL specific parts on sql-bricks-postgres page.
Connections are handled automatically: a connection is withheld from a pool or created
for you when you need it and returned to the pool once you are done.
You can also manually get connection:
db.run(function (client, callback) {
client.query("select * from user where id = $1", [id], callback);
client.select().from('user').where('id', id).run(callback);
});
You can also wrap your connection in a transaction:
db.transaction(function (client, callback) {
async.waterfall([
client.insert('user', {name: 'Mike'}).returning('id').run,
function (res, callback) {
var id = res.rows[0].id;
client.insert('profile', {user_id: id, ...}).run(callback);
},
], callback)
})
Accessors
There are .rows()
, .row()
, .col()
and .val()
accessors on pg-bricks queries.
You can use them to extract corresponding part of result conveniently.
Also, .row()
checks that result contains exactly one row and .col()
checks that result
contains exactly one column. .val()
does both:
db.select('id,name').from('user').val(function (err) {
})
Debugging
pg-bricks
uses debug package, so you can use:
DEBUG=pg-bricks node your-app.js
to see all the queries on your screen.