# sql-bricks-postgres

> Transparent, Schemaless SQL Generation for the PostgreSQL

Latest version **0.6.0** (published 2022-04-30) · MIT license · 0 weekly downloads

## Install

```sh
npm install sql-bricks-postgres
pnpm add sql-bricks-postgres
yarn add sql-bricks-postgres
bun add sql-bricks-postgres
```

## 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 | 2022-04-30 |
| First published | 2014-09-20 |
| Weekly downloads | 0 |
| License | MIT |
| TypeScript types | none |
| Module format | CommonJS |
| Node | * |
| Dependencies | 2 |
| Unpacked size | 24.7 KB |
| Known vulnerabilities | 0 |
| Install scripts | no |
| GitHub stars | 37 |
| Author | Alexander Schepanovski |
| Maintainers | suor |
| Keywords | sql, generation, generate, query, pg, postgres, builder, select, insert, update, delete |

## Links

- npm: https://www.npmjs.com/package/sql-bricks-postgres
- Repository: https://github.com/Suor/sql-bricks-postgres
- Issues: https://github.com/Suor/sql-bricks-postgres/issues
- npm.io page: https://npm.io/package/sql-bricks-postgres

## Dependencies (2)

- [sql-bricks](https://npm.io/package/sql-bricks.md) ^3
- [underscore](https://npm.io/package/underscore.md) ^1.4.x

## 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) — 2022-04-30
- 0.5.0 — 2017-08-13
- 0.4.3 — 2016-10-30
- 0.4.2 — 2015-10-11
- 0.4.1 — 2015-05-14
- 0.4.0 — 2015-03-10
- 0.3.0 — 2014-10-09
- 0.2.1 — 2014-10-02
- 0.2.0 — 2014-09-26
- 0.1.0 — 2014-09-20

## README

# PostgreSQL dialect for SQLBricks

This is a lightweight, schemaless library helping you to generate statements for PostgreSQL.
It is based on [sql-bricks](https://github.com/CSNW/sql-bricks) and adds PostgreSQL specific things into it.

You might also want to take a look at [pg-bricks](https://github.com/Suor/pg-bricks), which adds query execution, connections and transaction handling on top of this library.


## Usage

```javascript
// in node:
var sql = require('sql-bricks-postgres');
// in the browser:
var sql = PostgresBricks;

sql.select().from('user').where({name: 'Fred'}).toParams();
// {text: 'SELECT * FROM "user" WHERE name = $1', values: ['Fred']}

sql.select().from('user').where({name: 'Fred'}).toString();
// SELECT * FROM "user" WHERE name = 'Fred'

// NOTE: never use .toString() to execute a query,
//       leave values for db library to quote
```

You can read about basic flavor of how this thing works in [sql-bricks documentation](http://csnw.github.io/sql-bricks). Here go PostgreSQL specifics.


### LIMIT and OFFSET

```js
sql.select().from('user').limit(10).offset(20).toString()
// SELECT * FROM "user" LIMIT 10 OFFSET 20
```


### RETURNING

```js
sql.update('user', {name: 'John'}).where({id: 1}).returning('*')
// UPDATE "user" SET name = 'John' WHERE id = 1 RETURNING *

sql.delete('job').where({finished: true}).returning('id')
// DELETE FROM job WHERE finished = TRUE RETURNING id
```


### UPDATE ... FROM

```js
sql.update('setting', {value: sql('V.value')})
   .from('val as V').where({name: sql('V.name')}).toString()
// UPDATE setting SET value = V.value
//   FROM val as V WHERE name = V.name
```


### DELETE ... USING

```js
sql.delete('user').using('address')
   .where('user.addr_fk', sql('address.pk'))
// DELETE FROM user USING address WHERE user.addr_fk = address.pk
```


### ON CONFLICT ... DO NOTHING / DO UPDATE ...

The most popular use case is probably UPSERT:

```js
sql.insert('user', {name: 'Alex', age: 34})
   .onConflict('name').doUpdate('age')
// INSERT INTO "user" (name) VALUES ('Alex', 34)
//     ON CONFLICT (name) DO UPDATE SET age = EXCLUDED.age

// sql-bricks-postgres will update all fields if none are specified
sql.insert('user', {name: 'Alex', age: 34})
   .onConflict('name').doUpdate()
// INSERT INTO "user" (name) VALUES ('Alex', 34)
//   ON CONFLICT (name)
//   DO UPDATE SET name = EXCLUDED.name, age = EXCLUDED.age

// manipulate the data in the `DO UPDATE`:
sql.insert('user', {name: 'Alex', age: 34})
    .onConflict('name').doUpdate()
    .set(sql('name = coalesce(EXCLUDED.name, $1), age = $2 + 10', t1, t2))
// INSERT INTO "user" (name) VALUES ('Alex', 34)
//   ON CONFLICT (name)
//   DO UPDATE SET name = coalesce(EXCLUDED.name, $3), age = $4 + 10
```

Other clauses such as `DO NOTHING`, `ON CONSTRAINT` and `WHERE` are
also supported:

```js
sql.insert('user', ...).onConflict('name').where({is_active: true})
   .doNothing()
// INSERT INTO "user" ... VALUES ...
//     ON CONFLICT (name) WHERE is_active = true DO NOTHING

sql.insert('user', ...).onConflict().onConstraint('name_idx')
    .doUpdate().where(sql('is_active'))
// INSERT INTO "user" ... VALUES ...
//     ON CONFLICT ON CONSTRAINT name_idx
//     DO UPDATE SET ... WHERE is_active"
```


### FROM VALUES

`VALUES` statement is a handy way to provide data with a query. It is most known in a context of `INSERT`, but could be used for other things like altering selects and doing mass updates:

```js
var data = [{name: 'a', value: 1}, {name: 'b', value: 2}];
sql.select().from(sql.values(data)).toString();
// SELECT * FROM (VALUES ('a', 1), ('b', 2))

sql.update('setting s', {value: sql('v.value')})
   .from(sql.values({name: 'a', value: 1}).as('v').columns())
   .where('s.name', sql('v.name')}).toString()
// UPDATE setting s SET value = v.value
//   FROM (VALUES ('a', 1)) v (name, value) WHERE s.name = v.name
```

Sometimes you need types on values columns for query to work. You can use `.types()` method to provide them:

```js
var data = {i: 1, f: 1.5, b: true, s: 'hi'};
insert('domain', _.keys(data))
    .select().from(sql.values(data).as('v').columns().types())
    .where(sql.not(sql.exists(
        select('1').from('domain d')
        .where({'d.job_id': sql('v.job_id'), 'd.domain': sql('v.domain')}))))
// INSERT INTO domain (i, f, b, s)
// SELECT * FROM (VALUES ($5::int, $6::float, $7::bool, $8)) v (i, f, b, s)
// WHERE NOT EXISTS
//    (SELECT 1 FROM domain d WHERE d.job_id = v.job_id AND d.domain = v.domain)
```

When type can't detected by value, e.g. you have `null`, no cast will be added.
However, you can specify types explicitly:

```js
sql.values({field: null}).types({field: 'int'}).toString()
// VALUES (null::int)
```

## ILIKE

`ILIKE` is a case insensitive `LIKE` statement

```js
sql.select("text").from("example").where(sql.ilike("text", "%EASY%"))
// SELECT text FROM example WHERE text ILIKE '%EASY%'
```

## PostgreSQL Type Compatability
Supports [node-postgres](https://github.com/brianc/node-postgres) `toPostgres()` conventions to format Javascript appropriately for PostgreSQL.
See [postgres-interval](https://github.com/bendrucker/postgres-interval) for an example of this pattern in action. ([index.js#L14-L22](https://github.com/bendrucker/postgres-interval/blob/master/index.js#L14-L22))


## Even Harder Things

PostgreSQL has lots of functions and operators so it's inpractical to support everything,
instead simple fallback is offered:

```js
select().from('time_limit')
        .where(sql('tsrange(start, end) @> tsrange($1, $2)', t1, t2))
// SELECT * FROM time_limit
// WHERE tsrange(start, end) @> tsrange($1, $2)
```

Note `$<number>` placeholders.

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