Support data-api templates?

[mmkal]

Hi again! I have a use case that isn't directly related to pg-mem as it is, but could be useful.

We're using AWS's RDS data api to query postgres. We use the data-api-client library, which means there are some query-parsing tools relying on pgsql-ast-parser we can't use anymore because of the variable format it uses:

let resultParams = await data.query(
  `SELECT * FROM myTable WHERE id = :id`,
  { id: 2 }
)

These kinds of queries result in a parse error with the current library - this makes sense, since it's not valid SQL syntax. But it'd be great if we could add an option to be able to parse it and extract the variable names (in the example above, maybe the where would become something like { type: 'parameter', name: ':id' }). It'd allow statically inspecting sql queries and doing things like formatting, linting, type generation.

[oguimbal]

It could be useful, yes.

In fact, I faced the same issue when implementing pg-mem pg adapter, which queries can contain arguments following the same schema (except with a $ instead of a :).

... I solved this via a hack: Replacing arguments with their literal values.

So as a workaround, you could just use this hack, to parse parameters as string (and then detect string values which begin with a ':'):

sql = sql.replace(/:(\d+)/g, str => `'${str}'`)

Not sure to have the time to implement a clean solution for this soon, but PR are welcome if someone passing by finds some time before me !

[mmkal]

I was thinking of a regex too - only thing to be careful around is to avoid accidentally replacing casts like

select abc::text from def where ghi = :jkl

Maybe something like (?<!:):\w+\b would work though.

If I get some time would love to contribute a PR for the non-hacky solution - would be great if you could give a pointer as to where to start/if there are some comparable features.

[oguimbal]

You're right 😁 and your negative lookbehind seems to work

[oguimbal]

Hi !

FYI, pgsql-ast-parser now supports positionals parameters (i.e. $1, $2, etc...) because it is valid SQL (long story short - I need to support for an ongoing work).

That said, named parameters like :name are not a valid SQL, so I'm not very compfortable supporting them in this parser 😑

Thus, I'm closing this, but with a snippet that should work to circumvent this issue (for future readers):

function normalize(query) {
    let i = 0;
    const byName = {};
    const normalized = query.replace(/(?<!:):(\w+)\b/gi, (_, x) => {
      if (byName[x]) {
        return '$' + byName[x];
      }
      return '$' + (byName[x] = ++i)
    });

    const keys = Object.keys(byName);
    function toArgList(args) {
      const ret = Array(keys.length);
      for (const k of keys) {
        ret[byName[k] - 1] = args[k];
      }
      return ret;
    }
  return {normalized, toArgList};
}

Use it like this:

// transform a named query to a standard positioned parameters query
const {normalized, toArgList} = normalize('select * from xxx where id = :id AND other=:other + :id');

console.log(normalized); // 'select * from xxx where id = $1 AND other=$2 + $1'
console.log(toArgList({id: 'myId', other: 42 })); // [ 'myId', 42 ]

// this is parsable !
parse(normalized);

(demo here)

[mmkal]

Will try it out, thank you!

[stretchkennedy]

Just in case it's helpful for anyone else and/or me in a couple of years: if you're using an ORM or query builder to run your queries, it'll probably have a way to do the actual replacement. In e.g. Sequelize you can do

import { injectReplacements } from 'sequelize/lib/utils/sql';
// ...
const normalisedQuery = injectReplacements(query, sequelize.dialect, replacements);

This is shorter, and a bit more resilient in the unlikely case you've got a string like ':foo:' in your query.