utils

Namespace for general-purpose static functions, available as pgp.utils, before and after initializing the library.

Its main purpose is to simplify developing projects with either large or dynamic number of SQL files.

See also:

Properties:
Name Type Description
camelize function

camelize - camelizes a text string
camelizeVar function

camelizeVar - camelizes a text string as a variable
enumSql function

enumSql - enumerates SQL files in a directory
objectToCode function

objectToCode - generates code from an object
buildSqlModule function

buildSqlModule - generates a complete Node.js module
Source:

Methods

(static) buildSqlModule(configopt) → {string}

Synchronously generates a Node.js module with a camelized SQL tree, based on a configuration object that has the format shown below.

This method is normally to be used on a grunt/gulp watch that triggers when the file structure changes in your SQL directory, although it can be invoked manually as well.

{
   // Required Properties:

   "dir" // {string}: relative or absolute directory where SQL files are located (see API for method enumSql, parameter `dir`)

   // Optional Properties:

   "recursive" // {boolean}: search for sql files recursively (see API for method enumSql, option `recursive`)

   "ignoreErrors" // {boolean}: ignore common errors (see API for method enumSql, option `ignoreErrors`)

   "output" // {string}: relative or absolute destination file path; when not specified, no file is created,
            // but you still can use the code string that's always returned by the method.

   "module": {
       "path" // {string}: relative path to a module exporting a function which takes a file path
              // and returns a proper value (typically, a new QueryFile object); by default, it uses `./loadSql`.

       "name" // {string}: local variable name for the SQL-loading module; by default, it uses `load`.
   }
}
Parameters:
Name Type Attributes Description
config object | string <optional>

Configuration parameter for generating the code.

  • When it is a non-null object, it is assumed to be a configuration object (see the format above).
  • When it is a text string - it is the relative path to either a JSON file that contains the configuration object, or a Node.js module that exports one. The path is relative to the application's entry point file.
  • When config isn't specified, the method will try to locate the default sql-config.json file in the directory of your application's entry point file, and if not found - throw Error = Default SQL configuration file not found.
Source:
See:
Returns:

Generated code.

Type
string
Examples
// generate SQL module automatically, from sql-config.json in the module's start-up folder:

pgp.utils.buildSqlModule();

// see generated file below:
/////////////////////////////////////////////////////////////////////////
// This file was automatically generated by pg-promise v.4.3.8
//
// Generated on: 6/2/2016, at 2:15:23 PM
// Total files: 15
//
// API: http://vitaly-t.github.io/pg-promise/utils.html#.buildSqlModule
/////////////////////////////////////////////////////////////////////////

const load = require('./loadSql');

module.exports = {
    events: {
        add: load("../sql/events/add.sql"),
        delete: load("../sql/events/delete.sql"),
        find: load("../sql/events/find.sql"),
        update: load("../sql/events/update.sql")
    },
    products: {
        add: load("../sql/products/add.sql"),
        delete: load("../sql/products/delete.sql"),
        find: load("../sql/products/find.sql"),
        update: load("../sql/products/update.sql")
    },
    users: {
        add: load("../sql/users/add.sql"),
        delete: load("../sql/users/delete.sql"),
        find: load("../sql/users/find.sql"),
        update: load("../sql/users/update.sql")
    },
    create: load("../sql/create.sql"),
    init: load("../sql/init.sql"),
    drop: load("../sql/drop.sql")
};

(static) camelize(text) → {string}

Camelizes a text string.

Case-changing characters include:

  • hyphen
  • underscore
  • period
  • space
Parameters:
Name Type Description
text string

Input text string.
Source:
See:
Returns:

Camelized text string.

Type
string

(static) camelizeVar(text) → {string}

Camelizes a text string, while making it compliant with JavaScript variable names:

  • contains symbols a-z, A-Z, 0-9, _ and $
  • cannot have leading digits

First, it removes all symbols that do not meet the above criteria, except for hyphen, period and space, and then it forwards into camelize.

Parameters:
Name Type Description
text string

Input text string.

If it doesn't contain any symbols to make up a valid variable name, the result will be an empty string.
Source:
See:
Returns:

Camelized text string that can be used as an open property name.

Type
string

(static) enumSql(dir, optionsopt, cbopt) → {object}

Synchronously enumerates all SQL files (within a given directory) into a camelized SQL tree.

All property names within the tree are camelized via camelizeVar, so they can be used in the code directly, as open property names.

Parameters:
Name Type Attributes Description
dir string

Directory path where SQL files are located, either absolute or relative to the current directory.

SQL files are identified by using .sql extension (case-insensitive).
options object <optional>

Search options.

Properties
Name Type Attributes Default Description
recursive boolean <optional>
 false

Include sub-directories into the search.

Sub-directories without SQL files will be skipped from the result.
ignoreErrors boolean <optional>
 false

Ignore the following types of errors:

  • access errors, when there is no read access to a file or folder
  • empty or duplicate camelized property names

This flag does not affect errors related to invalid input parameters, or if you pass in a non-existing directory.
cb function <optional>

A callback function that takes three arguments:

  • file - SQL file path, relative or absolute, according to how you specified the search directory
  • name - name of the property that represents the SQL file
  • path - property resolution path (full property name)

If the function returns anything other than undefined, it overrides the corresponding property value in the tree.
Source:
See:
Returns:

Camelized SQL tree object, with each value being an SQL file path (unless changed via the callback).

Type
object
Examples
// simple SQL tree generation for further processing:
const tree = pgp.utils.enumSql('../sql', {recursive: true});
// generating an SQL tree for dynamic use of names:
const sql = pgp.utils.enumSql(__dirname, {recursive: true}, file=> {
    return new pgp.QueryFile(file, {minify: true});
});
const path = require('path');

// replacing each relative path in the tree with a full one:
const tree = pgp.utils.enumSql('../sql', {recursive: true}, file=> {
    return path.join(__dirname, file);
});

(static) objectToCode(obj, cbopt) → {string}

Translates an object tree into a well-formatted JSON code string.

Parameters:
Name Type Attributes Description
obj object

Source tree object.
cb function <optional>

A callback function to override property values for the code.

It takes three arguments:

  • value - property value
  • name - property name
  • obj - current object (which contains the property)

The returned value is used as is for the property value in the generated code.
Source:
See:
Returns:
Type
string
Examples
// Generating code for a simple object

const tree = {one: 1, two: {item: 'abc'}};

const code = pgp.utils.objectToCode(tree);

console.log(code);
//=>
// {
//     one: 1,
//     two: {
//         item: "abc"
//     }
// }
// Generating a Node.js module with an SQL tree

const fs = require('fs');
const EOL = require('os').EOL;

// generating an SQL tree from the folder:
const tree = pgp.utils.enumSql('./sql', {recursive: true});

// generating the module's code:
const code = "const load = require('./loadSql');" + EOL + EOL + "module.exports = " +
        pgp.utils.objectToCode(tree, value => {
            return 'load(' + JSON.stringify(value) + ')';
        }) + ';';

// saving the module:
fs.writeFileSync('sql.js', code);
// generated code example (file sql.js)

const load = require('./loadSql');

module.exports = {
    events: {
        add: load("../sql/events/add.sql"),
        delete: load("../sql/events/delete.sql"),
        find: load("../sql/events/find.sql"),
        update: load("../sql/events/update.sql")
    },
    products: {
        add: load("../sql/products/add.sql"),
        delete: load("../sql/products/delete.sql"),
        find: load("../sql/products/find.sql"),
        update: load("../sql/products/update.sql")
    },
    users: {
        add: load("../sql/users/add.sql"),
        delete: load("../sql/users/delete.sql"),
        find: load("../sql/users/find.sql"),
        update: load("../sql/users/update.sql")
    },
    create: load("../sql/create.sql"),
    init: load("../sql/init.sql"),
    drop: load("../sql/drop.sql")
};
// loadSql.js module example

const QueryFile = require('pg-promise').QueryFile;

module.exports = file => {
    return new QueryFile(file, {minify: true});
};