@imatic/pgqb

pgqb

This library allows you to create dynamic sql queries.

In case of simple queries, you could just use template literals:

const sql = `
SELECT *
FROM "table"
`

In case you have lots of parameters, you could use sql-template-strings:

import {SQL} from 'sql-template-strings';

const sqlStatement = `
INSERT INTO "table"
    ("col1", "col2")
VALUES
    (${val1}, ${val2})
`;

But what if you want to create queries dynamically? Splitting queries into several string parts, modifying several parts separately and then concatenating them togheter? There is better way - building queries as javascript data structures, which can be then easilly manipulated, merged, ...

Let's see previous query built using this library:

import * as qb from '@imatic/pgqb';

const sqlMap = qb.merge(
    qb.insertInto('table'),
    qb.columns(['col1', 'col2']),
    qb.values([
        [
            qb.val.inlineParam(val1),
            qb.val.inlineParam(val2),
        ]
    ])
);

const sqlStatement = qb.toSql(sqlMap);

Query above automatically escapes identifiers (tables, colums). For now, building the query using this library seems more verbose than using just sql-template-strings. Imagine you would want to modify the query though. If you wanted to add additional column, you could do it like so:

import * as qb from '@imatic/pgqb';

const sqlMap = qb.merge(
    qb.insertInto('table'),
    qb.columns(['col1', 'col2']),
    qb.values([
        [
            qb.val.inlineParam(val1),
            qb.val.inlineParam(val2),
        ]
    ])
);

const additionalColumn = qb.merge(
    qb.columns(['col3']),
    qb.values([
        [
            qb.val.inlineParam(val3),
        ]
    ])
)

const sqlStatement = qb.toSql(qb.append(sqlMap, additionalColumn));

Your query now inserts 3 columns instead of original 2. You can append other clauses (like where) too.

Inspiraton

This library was inspired by honeysql.