Necessary Pigeonholing Mechanism

    pg-bricks

    0.6.0 • Public • Published

    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:

    const db = require('pg-bricks').configure(process.env.DATABASE_URL);
     
    // mind using db.sql to wrap now() function
    await db.update('user', {ll: db.sql('now()')}).where('id', id).run();
     
    // db.sql contains various utilities to construct where conditions
    db.delete('event').where(db.sql.lt('added', new Date('2005-01-01')))
        .run().then(...);
     
    // access selected rows directly, not wrapped into result object
    let users = await db.select().from('user').where({name: name}).rows()
     
    // all functions switch to callback style when one is passed
    db.insert('user', data).returning('*').row(function (err, user) {});

    As you can see, db.sql is a sql-bricks object, which you can use to escape raw sql fragments. You can read about sql-bricks way of constructing requests in its documentation and about PostgreSQL specific parts on sql-bricks-postgres page.

    pg-bricks also exposes a reference to used pg library via db.pg in case you want to go low level.

    When you need to perform something custom you can resolve to raw sql queries:

    // use .raw() for raw sql and .val() to get single value
    let size = await db.raw('select pg_datatable_size($1)',
                            [tableName]).val();

    Configuration

    You can supply either connection string or connection config to .configure():

    const bricks = require('pg-bricks');
    const db1 = bricks.configure('postgresql://dbuser:pass@dbhost/mydb');
    const db2 = bricks.configure({
        host: 'dbhost',
        database: 'mydb2',
        user: 'dbuser',
        password: 'pass',
    });

    Or you can use environment variables which libpq to connect to a PostgreSQL server:

    $ PGHOST=dbhost PGPORT=5433 \
      PGDATABASE=mydb PGUSER=dbuser PGPASSWORD=pass \
      node script.js

    If you are using connection config it is passed directly to node-postgres, so you may take a look at its Connecting and SSL/TLS documentation pages.

    Connections and transactions

    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:

    await db.run(async (client) => {
        // client is a node-postgres client object
        // it is however extended with sql-bricks query constructors
        await client.select().from('user').where('id', id).run();
     
        // you also get .raw()
        await client.raw("select * from user where id = $1", [id]).row()
    })

    You can easily wrap your connection in a transaction:

    await db.transaction(async (client) => {
        let id = await client.insert('user', ...).returning('id').val()
        await client.insert('profile', {user_id: id, ...}).run()
    })

    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) {
        // err is Error('Expected a single column, multiple found')
    })

    Streaming

    To get a stream just call .stream() method on a brick:

    var stream = db.select('id, name').from('user').stream();
    stream.on('data', ...)
    stream.on('end', ...)
    stream.on('error', ...)

    Piping also works, e.g. this way you can export to CSV:

    function (req, res) {
        var stream = db.raw('select id, name from user').stream();
        stream.pipe(csv.stringify()).pipe(res);
    }

    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.

    Native bindings

    You can use native bindings similar to the way you use it with pg:

    var db = require('pg-bricks').configure(process.env.DATABASE_URL);
    db = db.native;
     
    // ... use db as usual

    NODE_PG_FORCE_NATIVE environment variable will also work as expected:

    NODE_PG_FORCE_NATIVE=1 node your_code.js

    Note that streaming won't work with native bindings.

    Callbacks

    All execute methods such as query.run() and all the accessors automatically switch between promise and callback modes as on the examples above. db.run() and db.transaction() additionally switch their expectation of body function:

    db.transaction(function (client, callback) {
        async.waterfall([
            // .run is a closure, so you can pass it to other function like this:
            client.insert('user', {name: 'Mike'}).returning('id').run,
            // res here is normal node-postgres result,
            // use .val accessor to get id directly
            function (res, callback) {
                var id = res.rows[0].id;
                client.insert('profile', {user_id: id, ...}).run(callback);
            },
        ], callback)
    }, done)

    Install

    npm i pg-bricks

    DownloadsWeekly Downloads

    173

    Version

    0.6.0

    License

    BSD-2-Clause

    Last publish

    Collaborators

    • suor