Better dynamic query building

[okbrown]

Hi, First and foremost, I think this is a great start at making something really simple and effective. Thank you!!

However... I have always found template strings a little odd especially in this context.
Let us say we have

const query = `SELECT ${distinct}, ${columns} FROM ${table} ${innerJoins} WHERE ${where}`;
(Where distinct, columns, table etc are their own strings made up of other functions elsehwhere)
which basically outputs...

SELECT DISTINCT(person.uid), person.name, movie.title, movie.year
FROM person 
INNER JOIN movie_ref ON movie_ref.person_id = person.id
INNER JOIN movie ON movie.id = movie_ref.movie_id
WHERE person.name ILIKE '%luke%'

I should then be able to do sql`${query}`.then((data) => console.log(data) or await etc..

But instead I receive a syntax error:
Error: syntax error at or near "$1" at Object.generic (../node_modules/postgres/lib/types.js:200:5)

Gonna take a look at how this tagged template function is actually working as Im aware it takes the values $1, $2 etc.. and so forth to evaluate them and pass them to the statement e.g. select * from table where id = $1 => select * from table where id = somevalue

At the moment, it means I have to directly place all my select statements big or small in this method. Which will start looking very messy especially when queries become more complex and the app grows.

[porsager]

Hi.

Thanks :)

Yeah, it can take a while to wrap your head around tagged template functions, but once you do you start seeing great usage for them everywhere (at least i did) ;).

The first thing is to understand the difference between tagged template functions and just regular template literal strings.

Tagged template functions allows you to handle the values ${ value } and ensure they are escaped or translated properly. If you were able to concat strings together without any handling you would quickly open up for various kinds of sql injection, which is also why you have to do sql`...`, and are not able to do sql(`...`), when making queries.

Postgres.js converts normal values and replaces their position in the query with $1, $2, etc and send the actual values as parameters to Postgres over the wire, so that Postgres can do its magic from there on. (you can add debug: console.log to the options to see how the queries end up.)

That mens you can't pass keywords and identifiers directly as raw string arguments when calling the sql function as a tagged template function (sql``) since that would be just as dangerous as simply contatting strings..

To still allow more dynamic queries than where postgres accepts positional arguments ($1, $2, etc) I've made the sql function able to be used for clever escaping to make writing queries nicer.

I want to improve the documentation on that end, and already have this issue #4 open to track it.

I hope that helps, and thanks for the good description of your concerns :)

[porsager]

const query = `SELECT ${distinct}, ${columns} FROM ${table} ${innerJoins} WHERE ${where}`;
I should then be able to do sql`${query}`.then((data) => console.log(data) or await etc..

If you can tell me where in the docs it seemed like this was allowed, I'd love to make it more clear that, that is not possible.

[okbrown]

@porsager Thanks for your reply much appreciated.

Funny you mention the difference as I had to go re-read the docs about them and had a play about and realised "oh" thats how it works. But what I did not know was Postgres already does the positional arguments for you. I somehow believed this was always done on the client/library side. Which explains why I cant build my statement outside of the tagged template method the way I thought I could.

But regarding your sql function. The example shows for an insert statement. This is great as you can pass in a single object or an array. This I have seen from other libraries, except this syntax is actually much simpler to use. However my brain draws a blank when it comes to figuring out how to do this for a complex select statement.

for example, here is a basic one.

const person = { name: 'john };
const results = await sql`select * from person where ${sql(where)} this will simply add the key value to the statement.

But there will be times based on different situations where I will have to programmatically generate a queries using partial fragments.

for example.

const buildWhereFromParams = (params) => {
  const values = [];
  for (const param in params) {
    if (params.hasOwnProperty(param)) {
        values.push(`${ param } ILIKE '%${ params[ param ] }%'`);  
    }
  }
 return values.join(' AND '); 
}

const where = buildWhereFromParams({ name: "orlando", "location":"london" });
const results = sql`select * from table where ${sql(where)}`;
//which should output => select * from table where name ILIKE '%orlando%' AND ILIKE location '%london%'

Not able to do it like this.

[Minigugus]

What about sql.unsafe(query, [args], [options]) ? https://github.com/porsager/postgres#teardown--cleanup

[porsager]

That's a great case @okbrown .. It'd be easy to just defer and disallow dynamic queries of that kind, but I'd like for this library to have a safe ergonomic way to support it.

@Minigugus is right about .unsafe, but there's a reason why it has that name, and is so hidden in the docs ;)

I'll leave this issue open, and I'll try to think some more about it.

[throrin19]

Same problem here but I want to insert data in table named by templated var :

await sql `
    insert into ${measure.company} (
        label, value
    ) values (
        ${measure.label}, ${measure.value}
    )
`

But I get the same error :

Error: syntax error at or near "$1"
  File "/var/task/node_modules/postgres/lib/types.js", line 200, col 5, in Object.generic
    new Error(x.message),
  File "/var/task/node_modules/postgres/lib/backend.js", line 124, col 33, in Object.ErrorResponse
    ? (backend.error = errors.generic(parseError(x)))
  File "/var/task/node_modules/postgres/lib/connection.js", line 161, col 25, in Socket.data
    backend[buffer[0]](buffer.slice(0, length + 1))
  File "events.js", line 210, col 5, in Socket.emit
  File "domain.js", line 476, col 20, in Socket.EventEmitter.emit
  File "_stream_readable.js", line 308, col 12, in addChunk
  File "_stream_readable.js", line 289, col 11, in readableAddChunk
  File "_stream_readable.js", line 223, col 10, in Socket.Readable.push
  File "internal/stream_base_commons.js", line 182, col 23, in TCP.onStreamRead

[porsager]

Hi @throrin19

Any sql identifier or keyword must be added with sql() to ensure proper escaping, so this should work for you:

await sql `
    insert into ${ sql(measure.company) } (
        label, value
    ) values (
        ${ measure.label }, ${ measure.value }
    )
`

The following would also be valid:

await sql `
  insert into ${ sql(measure.company) } ${ 
    sql(measure, 'label', 'value') 
  }
`

[throrin19]

Same problem here. I have this build request (I simplify here) :

const select = 'avg(value)';
const where = `
    firstWhere = 'value' AND
    secondWhere = 'value'`;

const result = sql`SELECT ${ sql(select) } FROM "table" WHERE ${ sql(where) }`;

I have this generated SQL query :

SELECT "avg(value)" FROM "table" WHERE "
    firstWhere = 'value' AND
    secondWhere = 'value'"

I try this without sql() but I have the problems with $1, $2, ...

How to do that correctly ?

[Minigugus]

You can fake a template string call :

const fakeTemplateString = (...parts) =>
  parts.reduce(
    (args, item, pos) => ((pos % 2 ? args : args[0].raw).push(item), args),
    [(raw => (raw.raw = raw, raw))([])]
  );

const select = [
  { column: 'value', fn: 'avg' }
];
const where = [
  { column: 'firstWhere', value: 'value' },
  { column: 'secondWhere', value: 'value' }
];
const query = ['SELECT '];
select.forEach((field, i) => {
  query.push(query.pop() + (field.fn ? `${field.fn}(` : '')); // even
  query.push(field.column); // odd
  query.push((field.fn ? ')' : '') + (i === select.length - 1 ? '' : ', ')); // even
});
query.push(query.pop() + ' FROM ', 'table', ' WHERE ');
where.forEach((condition, i) => {
  query.push(condition.column); // odd
  query.push(' = '); // even
  query.push(condition.value); // odd
  query.push(i === where.length - 1 ? '' : ' AND '); // even
});

const result = sql(...fakeTemplateString(...query));

(I just tested fakeTemplateString, not the generated query)
Odd values are escaped, even one are raw. Just remember it's hacking 😄

[porsager]

Lol :D I love your ideas @Minigugus, but then sql.unsafe() is probably the better choice ;)

It's actually the entire reason it's there... So you don't have to hack through the tagged template function :P

[Minigugus]

I know, I just proposed this solution because sql.unsafe() requires special treatments for escaping and queries parameters that the template tag mode handles automatically :

(a, b, c, d) => {
  return sql`SELECT ${sql(a)} FROM ${sql(b)} WHERE ${sql(c)} = ${d}`;
};
(a, b, c, d) => {
  const query = ['SELECT ', sql(a), ' FROM ', sql(b), ' WHERE ', sql(c), ' = ', d];
  return sql(...fakeTemplateString(query));
};
(a, b, c, d) => {
  // NOTE : `sql(a)` does not replace `escapeSQL` since it returns `{ first: 'a', rest: [ ] }` and not `"a"`.
  const query = `SELECT ${escapeSQL(a)} FROM ${escapeSQL(b)} WHERE ${escapeSQL(c)} = $1`;
  const args = [d];
  return sql.unsafe(query, args);
};

(Not tested but should translate to something like SELECT "a" FROM "b" WHERE "c" = $1)

I encourage no one to use this hack, I just want to highlight the overhead induced by "sql.unsafe" for the secure generation and execution of dynamic queries. 😉