Search code examples
javascriptpostgresqlecmascript-6pgpg-promise

Is it allowed to reuse pg QueryFile multiple times

Am I allowed to reuse type QueryFile of pg-promise?

Eg.

const pgp = require(`pg-promise`)(options);
const QueryFile = pgp.QueryFile;

const db = pgp(config.DB);
const query =  new QueryFile('queryPath/some.sql', { minify: true });

// running sql query
db.any(query, []);
db.any(query, []);
db.any(query, []);
db.any(query, []);    
db.any(query, []);

Currently, I am creating new QueryFile every time I want to execute it. e.g.

db.any(new QueryFile('queryPath/some.sql', { minify: true }), []);
db.any(new QueryFile('queryPath/some.sql', { minify: true }), []);
db.any(new QueryFile('queryPath/some.sql', { minify: true }), []);
db.any(new QueryFile('queryPath/some.sql', { minify: true }), []);

Are there any cons if I reuse same query multiple times?

Solution

  • Is it allowed to reuse pg QueryFile multiple times?

    No, not allowed, insisted upon!

    Type QueryFile represents a virtual link to the file, and its use offers many advantages - as documented.

    The two keys features that concern this question are:

    • Parsing and minifying SQL (options minify/compress), for early error detection and compact queries.
    • Changes in external SQL can be automatically re-loaded (option debug), without restarting the app.

    The first one loads the file, parses and minifies it, if option minify/compress is provided. This takes time and the IO, and should not be done multiple times, as it would be pointless anyway.

    The second point allows it to be used as a virtual link that can automatically detect any file changes while in development mode (option debug), and to automatically reload and prepare the file. In a development environment this feature is priceless, preventing you from reloading the app whenever an external SQL file changes.

    So the cons are:

    • Creating unnecessary IO (extra file reads + SQL parsing and minification). And in your example it is even worse - you are doing it every time when executing a query.
    • Losing the key features for which type QueryFile was designed (auto-reload becomes impossible)

    And when you re-create QueryFile type for the same file, you are using the type against its most useful purposes. And this is why it will report a warning for you as Creating a duplicate QueryFile object for the same file.

    You should set up a separate structure for SQL files, like it is shown in pg-promise-demo, see here.