Document use of query files

[gajus]

pg-promise documentation features a concept of query files.

This is effectively a helper function that is used to prepare a statement using pg-promise, e.g.

const path = require('path');

// Helper for linking to external query files:
function sql(file) {
    const fullPath = path.join(__dirname, file);
    return new pgp.QueryFile(fullPath, {minify: true});
}

// Create a QueryFile globally, once per file:
const sqlFindUser = sql('./sql/findUser.sql');

db.one(sqlFindUser, {id: 123})
    .then(user => {
        console.log(user);
    })
    .catch(error => {
        if (error instanceof pgp.errors.QueryFileError) {
            // => the error is related to our QueryFile
        }
    });

In case of Slonik, this would be best implement using sql.raw, e.g.

const getQuery = (queryName, parameters = []) => {
  return sql`${sql.raw(
    fs.readFileSync(path.resolve(__dirname, 'queries', queryName + '.sql'), parameters)
  )}`;
};

connection.query(getQuery('foo', [1, 2, 3]));

I don't see an immediate need to either add helper for loading queries or to advertise this as a distinct feature.

What abstraction could Slonik offer here and what value would it add?

[vitaly-t]

b.t.w., when using {minify: true}, it also minifies the SQL, by removing comments, and compressing the query, using module pg-minify that I wrote while I was working on pg-promise.

Without it, working with large SQL files becomes awkward. Some large files may contain whole revisions commented out, plus a lot of inline documentation.

return sql${sql.raw( fs.readFileSync(path.resolve(__dirname, 'queries', queryName + '.sql'), parameters) )};

I'm not sure how this can work at all. The entire ES6 string template concept is based on evaluation of the inner expressions. Which means you cannot format any values?

That is the reason I never seriously considered ES6 template strings for query formatting. See also.

[hollowcat]

Would be helpful for setup & teardown in integration tests.

Might add a dev mode flag, such that a database definition file is supplied to Slonik as configuration and if in dev mode, it sets things up before running first query. Another flag to indicate if tables need to be recreated on each run, and if not, inline a SQL procedure for resetting them. Also inline a snapshot SQL procedure that remembers current table contents.

Then while playing with the app being developed one could use psql to snapshot and reset to states, and in integration tests this could be used as well.