# pg-bricks

> Higher level PostgreSQL client

Latest version **0.6.0** (published 2017-08-07) · BSD-2-Clause license · 0 weekly downloads

## Install

```sh
npm install pg-bricks
pnpm add pg-bricks
yarn add pg-bricks
bun add pg-bricks
```

## Health

**Score 15/100 (F)** — status: abandoned.

Positive: no vulnerabilities.

Warnings: low downloads; no types; no esm support; pre 1.0.

Negative: abandoned; low maintenance score.

## Facts

| | |
|---|---|
| Version | 0.6.0 |
| Published | 2017-08-07 |
| First published | 2014-08-21 |
| Weekly downloads | 0 |
| License | BSD-2-Clause |
| TypeScript types | none |
| Module format | CommonJS |
| Dependencies | 5 |
| Known vulnerabilities | 0 (+2 in 1 direct dependencies) |
| Install scripts | no |
| GitHub stars | 52 |
| Author | Alexander Schepanovski |
| Maintainers | suor |
| Keywords | postgres, pg, postgre, postgres, PostgreSQL, client, sql, query, select, insert, update, delete, transaction, stream |

## Links

- npm: https://www.npmjs.com/package/pg-bricks
- Repository: https://github.com/Suor/pg-bricks
- Homepage: http://github.com/Suor/pg-bricks
- Issues: https://github.com/Suor/pg-bricks/issues
- npm.io page: https://npm.io/package/pg-bricks

## Dependencies (5)

- [pg](https://npm.io/package/pg.md) ^7.1
- [debug](https://npm.io/package/debug.md) ~2.2.0
- [point-free](https://npm.io/package/point-free.md) 0.7.0
- [pg-query-stream](https://npm.io/package/pg-query-stream.md) ^1.0.0
- [sql-bricks-postgres](https://npm.io/package/sql-bricks-postgres.md) >=0.3.0

## Alternatives

- [gamedig](https://npm.io/package/gamedig.md) — 29.3K weekly downloads
- [join-monster](https://npm.io/package/join-monster.md) — 12.8K weekly downloads
- [masked](https://npm.io/package/masked.md) — 5.5K weekly downloads
- [@comunica/actor-query-process-explain-logical](https://npm.io/package/@comunica/actor-query-process-explain-logical.md) — 4.7K weekly downloads
- [@veracity/vui](https://npm.io/package/@veracity/vui.md) — 4.6K weekly downloads

## Recent versions

- 0.6.0 (latest) — 2017-08-07
- 0.5.0 — 2016-08-10
- 0.4.0 — 2015-10-11
- 0.3.4 — 2014-11-05
- 0.3.3 — 2014-11-01
- 0.3.2 — 2014-10-24
- 0.3.1 — 2014-10-09
- 0.3.0 — 2014-10-02
- 0.2.2 — 2014-09-10
- 0.2.1 — 2014-08-28
- 0.2.0 — 2014-08-27
- 0.1.0 — 2014-08-21

## README

# PostgreSQL bricks

This is a PostgreSQL client, which uses [PostreSQL extension][sql-bricks-postgres]
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:

```js
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](http://csnw.github.io/sql-bricks) and
about PostgreSQL specific parts on [sql-bricks-postgres page][sql-bricks-postgres].

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:

```js
// 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()`:

```js
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](https://www.postgresql.org/docs/9.1/static/libpq-envars.html) which libpq to connect to a PostgreSQL server:

```bash
$ 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](https://node-postgres.com/features/connecting)
and [SSL/TLS](https://node-postgres.com/features/ssl) 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:

```js
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:

```js
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:

```js
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:

```js
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:

```js
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:

```bash
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`:

```js
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:

```bash
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:

```js
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)
```

[sql-bricks-postgres]: https://www.npmjs.org/package/sql-bricks-postgres
[sql-bricks]: https://www.npmjs.org/package/sql-bricks
[pg]: https://www.npmjs.org/package/pg
[debug]: https://www.npmjs.org/package/debug

---
_Source: https://npm.io/package/pg-bricks · Machine-readable twin of the npm.io package page. Health data is recomputed on every publish._