PostgreSQL dialect for SQLBricks

This is a lightweight, schemaless library helping you to generate statements for PostgreSQL. It is based on sql-bricks and adds PostgreSQL specific things into it.

You might also want to take a look at pg-bricks, which adds query execution, connections and transaction handling on top of this library.

Usage

// in node:varsql=require('sql-bricks-postgres');// in the browser:varsql=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. Here go PostgreSQL specifics.

LIMIT and OFFSET

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

RETURNING

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

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

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:

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 specifiedsql.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:

sql.insert('user', ...).onConflict('name').where({is_active: true}).doNothing()// INSERT INTO "user" ... VALUES ...// ON CONFLICT (name) WHERE is_active = true DO NOTHINGsql.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:

vardata=[{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:

vardata={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:

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

ILIKE

ILIKE is a case insensitive LIKE statement

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

PostgreSQL Type Compatability

Supports node-postgrestoPostgres() conventions to format Javascript appropriately for PostgreSQL. See postgres-interval for an example of this pattern in action. (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:

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.