QueryFile(file, optionsopt) → {QueryFile}

new QueryFile(file, optionsopt) → {QueryFile}

Represents an external SQL file. The type is available from the library's root: pgp.QueryFile.

Reads a file with SQL and prepares it for execution, also parses and minifies it, if required. The SQL can be of any complexity, with both single and multi-line comments.

The type can be used in place of the query parameter, with any query method directly, plus as text in PreparedStatement and ParameterizedQuery.

It never throws any error, leaving it for query methods to reject with QueryFileError.

IMPORTANT: You should only create a single reusable object per file, in order to avoid repeated file reads, as the IO is a very expensive resource. If you do not follow it, you will be seeing the following warning: Creating a duplicate QueryFile object for the same file, which signals a bad-use pattern.

Parameters:
Name Type Attributes Description
file string

Path to the SQL file with the query, either absolute or relative to the application's entry point file.

If there is any problem reading the file, it will be reported when executing the query.
options QueryFile.Options <optional>

Set of configuration options, as documented by QueryFile.Options.
Source:
See:
Returns:
Type
QueryFile
Examples
// File sql.js

// Proper way to organize an sql provider:
//
// - have all sql files for Users in ./sql/users
// - have all sql files for Products in ./sql/products
// - have your sql provider module as ./sql/index.js

const {QueryFile} = require('pg-promise');
const {join: joinPath} = require('path');

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

module.exports = {
    // external queries for Users:
    users: {
        add: sql('users/create.sql'),
        search: sql('users/search.sql'),
        report: sql('users/report.sql'),
    },
    // external queries for Products:
    products: {
        add: sql('products/add.sql'),
        quote: sql('products/quote.sql'),
        search: sql('products/search.sql'),
    }
};
// Testing our SQL provider

const db = require('./db'); // our database module;
const {users: sql} = require('./sql'); // sql for users;

module.exports = {
    addUser: (name, age) => db.none(sql.add, [name, age]),
    findUser: name => db.any(sql.search, name)
};

Members

(readonly) error :errors.QueryFileError

When in an error state, it is set to a QueryFileError object. Otherwise, it is undefined.

Type:
Default Value:
  • undefined
Source:

(readonly) file :string

File name that was passed into the constructor.

This property is primarily for internal use by the library.

Type:
  • string
Source:

(readonly) options :QueryFile.Options

Set of options, as configured during the object's construction.

This property is primarily for internal use by the library.

Type:
Source:

(static) instance

Global instance of the file-path repository.

Source:

Methods

prepare(throwErrorsopt)

Prepares the query for execution.

If the query hasn't been prepared yet, it will read the file and process the content according to the parameters passed into the constructor.

This method is primarily for internal use by the library.

Parameters:
Name Type Attributes Default Description
throwErrors boolean <optional>
 false

Throw any error encountered.
Source:

toPostgres(selfopt) → {string}

Custom Type Formatting, based on Symbolic CTF, i.e. the actual method is available only via Symbol:

const {toPostgres} = pgp.as.ctf; // Custom Type Formatting symbols namespace
const query = qf[toPostgres](); // qf = an object of type QueryFile

This is a raw formatting type (rawType = true), i.e. when used as a query-formatting parameter, type QueryFile injects SQL as raw text.

If you need to support type QueryFile outside of query methods, this is the only safe way to get the most current SQL. And you would want to use this method dynamically, as it reloads the SQL automatically, if option debug is set. See Options.

Parameters:
Name Type Attributes Description
self QueryFile <optional>

Optional self-reference, for ES6 arrow functions.
Source:
Returns:

SQL string from the file, according to the options specified.

Type
string

toString(levelopt) → {string}

Creates a well-formatted multi-line string that represents the object's current state.

It is called automatically when writing the object into the console.

Parameters:
Name Type Attributes Default Description
level number <optional>
 0

Nested output level, to provide visual offset.
Source:
Returns:
Type
string

Type Definitions

Options

A set of configuration options as passed into the QueryFile constructor.

Properties:
Name Type Default Description
debug boolean

When in debug mode, the query file is checked for its last modification time on every query request, so if it changes, the file is read afresh.

The default for this property is true when NODE_ENV = development, or false otherwise.
minify boolean | string false

Parses and minifies the SQL using pg-minify:

  • false - do not use pg-minify
  • true - use pg-minify to parse and minify SQL
  • 'after' - use pg-minify after applying static formatting parameters (option params), as opposed to before it (default)

If option compress is set, then the default for minify is true.

Failure to parse SQL will result in SQLParsingError.
compress boolean false

Sets option compress as supported by pg-minify, to uglify the SQL:

  • false - no compression to be applied, keep minimum spaces for easier read
  • true - remove all unnecessary spaces from SQL

This option has no meaning, if minify is explicitly set to false. However, if minify is not specified and compress is specified as true, then minify defaults to true.
params array | object | value

Static formatting parameters to be applied to the SQL, using the same method as.format, but with option partial = true.

Most of the time query formatting is fully dynamic, and applied just before executing the query. In some cases though you may need to pre-format SQL with static values. Examples of it can be a schema name, or a configurable table name.

This option makes two-step SQL formatting easy: you can pre-format the SQL initially, and then apply the second-step dynamic formatting when executing the query.
noWarnings boolean false

Suppresses all warnings produced by the class. It is not recommended for general use, only in specific tests that may require it.
Source: