CtrlK
BlogDocsLog inGet started
Tessl Logo

tessl/npm-pg-promise

PostgreSQL interface for Node.js built on top of node-postgres, offering automatic connection management, promise-based API, automatic transaction handling with support for nested transactions and savepoints, advanced query formatting with named parameters and filtering capabilities, task and transaction management with conditional execution, query file loading and processing, custom type formatting support, and comprehensive error handling.

—

Pending

Quality

Pending

Does it follow best practices?

Impact

Pending

No eval scenarios have been run

Overview
Eval results
Files

query-files.mddocs/

Query Files and Prepared Statements

SQL file management and prepared statement support for better query organization and performance optimization. pg-promise provides classes for managing external SQL files, prepared statements, and parameterized queries.

Capabilities

Query File Management

The QueryFile class manages external SQL files with automatic loading, minification, and error handling.

/**
 * Query File class for managing external SQL files
 */
class QueryFile {
  constructor(file: string, options?: IQueryFileOptions)
  
  readonly error: Error // File loading/parsing error (if any)
  readonly file: string // Absolute file path
  readonly options: any // File processing options
  
  prepare(): void // Prepare/reload the file
  toString(level?: number): string // String representation
}

interface IQueryFileOptions {
  debug?: boolean // Enable debug mode
  minify?: boolean | 'after' // Minify SQL (before or after parameter formatting)
  compress?: boolean // Compress whitespace
  params?: any // Default parameters for the query
  noWarnings?: boolean // Suppress warnings
}

Usage Examples:

// Basic QueryFile usage
const getUsersQuery = new pgp.QueryFile('sql/get-users.sql');
const users = await db.any(getUsersQuery);

// QueryFile with options
const complexQuery = new pgp.QueryFile('sql/complex-report.sql', {
  minify: true,
  compress: true,
  params: { 
    defaultLimit: 100,
    defaultOffset: 0
  }
});

// Using with parameters
const reportData = await db.any(complexQuery, { 
  startDate: '2023-01-01',
  endDate: '2023-12-31'
});

// Error handling
const queryFile = new pgp.QueryFile('sql/might-not-exist.sql');
if (queryFile.error) {
  console.error('Query file error:', queryFile.error.message);
} else {
  const results = await db.any(queryFile);
}

// Reloading query files (useful in development)
queryFile.prepare(); // Reload from disk

SQL File Organization

Best practices for organizing SQL files:

// Directory structure example:
// sql/
//   ├── users/
//   │   ├── get-user.sql
//   │   ├── create-user.sql
//   │   └── update-user.sql
//   ├── orders/
//   │   ├── get-orders.sql
//   │   └── create-order.sql
//   └── reports/
//       └── monthly-sales.sql

// Loading SQL files with enumSql utility
const sql = pgp.utils.enumSql('./sql', { recursive: true }, file => {
  return new pgp.QueryFile(file, { minify: true });
});

// Usage:
const user = await db.one(sql.users.getUser, [userId]);
const orders = await db.any(sql.orders.getOrders, [userId]);
const report = await db.any(sql.reports.monthlySales, { month: '2023-01' });

Prepared Statements

Prepared statements provide performance optimization and parameter binding for frequently executed queries.

/**
 * Prepared Statement class for parameterized queries with performance optimization
 */
class PreparedStatement {
  constructor(options?: IPreparedStatement)
  
  // Standard properties
  name: string // Statement name (required)
  text: string | QueryFile // SQL query text or QueryFile
  values: any[] // Parameter values array
  
  // Advanced properties
  binary: boolean // Use binary format for parameters
  rowMode: void | 'array' // Row mode ('array' or default object mode)
  rows: number // Maximum rows to return
  types: ITypes // Custom type parsers
  
  parse(): IPreparedParsed | PreparedStatementError // Parse and validate
  toString(level?: number): string // String representation
}

interface IPreparedStatement {
  name?: string // Statement name
  text?: string | QueryFile // Query text
  values?: any[] // Parameter values
  binary?: boolean // Binary mode
  rowMode?: 'array' | null | void // Row mode
  rows?: number // Row limit
  types?: ITypes // Type parsers
}

interface IPreparedParsed {
  name: string // Parsed statement name
  text: string // Parsed query text
  values: any[] // Parsed parameter values
  binary: boolean // Binary mode flag
  rowMode: void | 'array' // Row mode setting
  rows: number // Row limit
}

Usage Examples:

// Basic prepared statement
const getUserStmt = new pgp.PreparedStatement({
  name: 'get-user-by-id',
  text: 'SELECT * FROM users WHERE id = $1'
});

// Execute prepared statement
const user = await db.one(getUserStmt, [123]);

// Prepared statement with QueryFile
const complexStmt = new pgp.PreparedStatement({
  name: 'complex-report',
  text: new pgp.QueryFile('sql/complex-report.sql'),
  values: [defaultStartDate, defaultEndDate]
});

// Execute with custom values
const report = await db.any(complexStmt, [startDate, endDate]);

// Advanced prepared statement options
const advancedStmt = new pgp.PreparedStatement({
  name: 'bulk-insert',
  text: 'INSERT INTO logs(timestamp, level, message) VALUES($1, $2, $3)',
  binary: true, // Use binary format for better performance
  rowMode: 'array' // Return rows as arrays instead of objects
});

// Prepared statement validation
const stmt = new pgp.PreparedStatement({ name: 'test', text: 'SELECT $1' });
const parsed = stmt.parse();

if (parsed instanceof pgp.errors.PreparedStatementError) {
  console.error('Statement error:', parsed.message);
} else {
  console.log('Statement is valid:', parsed.name);
}

Parameterized Queries

Parameterized queries provide a simpler alternative to prepared statements for one-time or infrequent queries.

/**
 * Parameterized Query class for simple parameter binding
 */
class ParameterizedQuery {
  constructor(options?: string | QueryFile | IParameterizedQuery)
  
  // Standard properties  
  text: string | QueryFile // SQL query text or QueryFile
  values: any[] // Parameter values array
  
  // Advanced properties
  binary: boolean // Use binary format for parameters
  rowMode: void | 'array' // Row mode ('array' or default object mode)
  types: ITypes // Custom type parsers
  
  parse(): IParameterizedParsed | ParameterizedQueryError // Parse and validate
  toString(level?: number): string // String representation
}

interface IParameterizedQuery {
  text?: string | QueryFile // Query text
  values?: any[] // Parameter values
  binary?: boolean // Binary mode
  rowMode?: void | 'array' // Row mode
  types?: ITypes // Type parsers
}

interface IParameterizedParsed {
  text: string // Parsed query text
  values: any[] // Parsed parameter values
  binary: boolean // Binary mode flag
  rowMode: void | 'array' // Row mode setting
}

Usage Examples:

// Basic parameterized query
const getUserQuery = new pgp.ParameterizedQuery({
  text: 'SELECT * FROM users WHERE age > $1 AND status = $2',
  values: [25, 'active']
});

const users = await db.any(getUserQuery);

// Parameterized query with QueryFile
const reportQuery = new pgp.ParameterizedQuery({
  text: new pgp.QueryFile('sql/user-report.sql'),
  values: [startDate, endDate, department]
});

const report = await db.any(reportQuery);

// Constructor shortcuts
const simpleQuery = new pgp.ParameterizedQuery('SELECT * FROM users WHERE id = $1');
simpleQuery.values = [123];

const fileQuery = new pgp.ParameterizedQuery(new pgp.QueryFile('sql/get-orders.sql'));
fileQuery.values = [userId];

// Advanced options
const binaryQuery = new pgp.ParameterizedQuery({
  text: 'SELECT data FROM binary_table WHERE id = $1',
  values: [recordId],
  binary: true,
  rowMode: 'array'
});

// Query validation
const query = new pgp.ParameterizedQuery({ text: 'SELECT $1, $2', values: ['a'] });
const parsed = query.parse();

if (parsed instanceof pgp.errors.ParameterizedQueryError) {
  console.error('Query error:', parsed.message);
} else {
  console.log('Query is valid, has', parsed.text.split('$').length - 1, 'parameters');
}

SQL File Utilities

Utility functions for working with SQL files and query organization.

/**
 * Enumerate SQL files in directory structure
 * @param dir - Directory path containing SQL files
 * @param options - Enumeration options
 * @param cb - Optional callback for file processing
 * @returns Object tree of SQL files/QueryFiles
 */
pgp.utils.enumSql(dir: string, options?: IEnumSqlOptions, cb?: (file: string, name: string, path: string) => any): object

interface IEnumSqlOptions {
  recursive?: boolean // Include subdirectories
  ignoreErrors?: boolean // Ignore access/naming errors
}

Usage Examples:

// Basic SQL file enumeration
const sqlFiles = pgp.utils.enumSql('./sql');
// Returns: { getUsers: './sql/get-users.sql', createUser: './sql/create-user.sql' }

// Recursive enumeration with QueryFile creation
const sql = pgp.utils.enumSql('./sql', { recursive: true }, file => {
  return new pgp.QueryFile(file, { minify: true });
});

// Usage of enumerated files
const users = await db.any(sql.users.getActive);
const orders = await db.any(sql.orders.getByUser, [userId]);

// With error handling
const sqlSafe = pgp.utils.enumSql('./sql', { 
  recursive: true, 
  ignoreErrors: true 
}, file => {
  const queryFile = new pgp.QueryFile(file, { minify: true });
  if (queryFile.error) {
    console.warn(`Failed to load ${file}:`, queryFile.error.message);
    return null;
  }
  return queryFile;
});

// Custom processing
const sqlMetadata = pgp.utils.enumSql('./sql', { recursive: true }, (file, name, path) => {
  return {
    queryFile: new pgp.QueryFile(file),
    name: name,
    path: path,
    size: require('fs').statSync(file).size
  };
});

Query File Best Practices

Examples of well-structured SQL files:

-- sql/users/get-active-users.sql
-- Get all active users with optional filtering
SELECT 
    u.id,
    u.name,
    u.email,
    u.created_at,
    up.bio
FROM users u
LEFT JOIN user_profiles up ON u.id = up.user_id
WHERE u.active = true
    AND ($1::varchar IS NULL OR u.name ILIKE '%' || $1 || '%')
    AND ($2::date IS NULL OR u.created_at >= $2)
ORDER BY u.created_at DESC
LIMIT $3::int
OFFSET $4::int;
-- sql/orders/create-order-with-items.sql  
-- Create order with items in a single transaction
WITH new_order AS (
    INSERT INTO orders (user_id, total_amount, status)
    VALUES ($1, $2, 'pending')
    RETURNING id, created_at
),
order_items AS (
    INSERT INTO order_items (order_id, product_id, quantity, price)
    SELECT 
        (SELECT id FROM new_order),
        unnest($3::int[]) as product_id,
        unnest($4::int[]) as quantity, 
        unnest($5::decimal[]) as price
    RETURNING *
)
SELECT 
    o.id,
    o.created_at,
    json_agg(
        json_build_object(
            'product_id', oi.product_id,
            'quantity', oi.quantity,
            'price', oi.price
        )
    ) as items
FROM new_order o
CROSS JOIN order_items oi
GROUP BY o.id, o.created_at;

Types

// Type parser interface for custom types
interface ITypes {
  getTypeParser(id: number, format?: string): (value: string) => any
}

// Error types for query files and statements
class QueryFileError extends Error {
  name: string
  message: string
  stack: string
  file: string // File path that caused error
  options: IQueryFileOptions // File options used
  error: SQLParsingError // Underlying parsing error
  toString(level?: number): string
}

class PreparedStatementError extends Error {
  name: string
  message: string
  stack: string
  error: QueryFileError // Underlying QueryFile error (if applicable)
  toString(level?: number): string
}

class ParameterizedQueryError extends Error {
  name: string
  message: string
  stack: string
  error: QueryFileError // Underlying QueryFile error (if applicable)
  toString(level?: number): string
}

// SQL parsing error from pg-minify
interface SQLParsingError {
  name: string
  message: string
  position: number
  line: number
  column: number
}

Install with Tessl CLI

npx tessl i tessl/npm-pg-promise

docs

configuration-utilities.md

connection-management.md

database-operations.md

error-handling.md

index.md

query-files.md

query-formatting.md

tasks-transactions.md

tile.json