Nevertheless! Party Metaphorically

    sql-bricks-postgres

    0.6.0 • Public • Published

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

    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:

    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:

    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:

    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-postgres toPostgres() 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.

    Install

    npm i sql-bricks-postgres

    DownloadsWeekly Downloads

    1,966

    Version

    0.6.0

    License

    MIT

    Unpacked Size

    25.3 kB

    Total Files

    6

    Last publish

    Collaborators

    • suor