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. Starting from version 5.8.0, 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').QueryFile;
const path = require('path');

// Helper for linking to external query files:
function sql(file) {
    const fullPath = path.join(__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 sql = require('./sql').users; // our 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:

Methods

prepare(throwErrorsopt)

Prepares the query for execution.

If the the query hasn't been prepared yet, it will read the file and process the contents 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:

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

Added in v5.8.0

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