Utilities

/**
 * Escape a SQL identifier (table name, column name, etc.)
 * Wraps the identifier in double quotes and escapes internal quotes
 * @param str - String to escape as identifier
 * @returns Properly escaped SQL identifier
 */
function escapeIdentifier(str: string): string;

/**
 * Escape a SQL literal value
 * Wraps the value in single quotes and escapes internal quotes and backslashes
 * @param str - String to escape as literal
 * @returns Properly escaped SQL literal
 */
function escapeLiteral(str: string): string;

const { escapeIdentifier, escapeLiteral } = require('pg');

// Escape table/column names
const tableName = escapeIdentifier('user-table');
console.log(tableName); // "user-table"

const columnName = escapeIdentifier('full name');
console.log(columnName); // "full name"

// Handle names with quotes
const quotedTable = escapeIdentifier('table"with"quotes');
console.log(quotedTable); // "table""with""quotes"

// Escape string literals
const userName = escapeLiteral("O'Reilly");
console.log(userName); // 'O''Reilly'

const description = escapeLiteral('Product with "quotes" and \\backslashes');
console.log(description); // E'Product with "quotes" and \\\\backslashes'

// Safe dynamic query construction
const table = escapeIdentifier('users');
const condition = escapeLiteral('admin');
const query = `SELECT * FROM ${table} WHERE role = ${condition}`;
console.log(query); // SELECT * FROM "users" WHERE role = 'admin'

/**
 * Default configuration object containing standard PostgreSQL connection settings
 * These values are used when specific configuration is not provided
 */
interface defaults {
  /** Database host (default: 'localhost') */
  host: string;
  /** Database user (from environment: USERNAME on Windows, USER on Unix) */
  user: string;
  /** Database name (default: undefined) */
  database: string | undefined;
  /** Database password (default: null) */
  password: string | null;
  /** PostgreSQL connection string (default: undefined) */
  connectionString: string | undefined;
  /** Database port (default: 5432) */
  port: number;
  /** Number of rows to return from portal (default: 0 = all) */
  rows: number;
  /** Binary result mode (default: false) */
  binary: boolean;
  /** Maximum pool connections (default: 10) */
  max: number;
  /** Idle timeout in milliseconds (default: 30000) */
  idleTimeoutMillis: number;
  /** Client encoding (default: '') */
  client_encoding: string;
  /** SSL mode (default: false) */
  ssl: boolean;
  /** Application name for logging (default: undefined) */
  application_name: string | undefined;
  /** Fallback application name (default: undefined) */
  fallback_application_name: string | undefined;
  /** Connection options (default: undefined) */
  options: string | undefined;
  /** Parse input dates as UTC (default: false) */
  parseInputDatesAsUTC: boolean;
  /** Statement timeout in milliseconds (default: false = no timeout) */
  statement_timeout: number | false;
  /** Lock timeout in milliseconds (default: false = no timeout) */
  lock_timeout: number | false;
  /** Idle transaction timeout in milliseconds (default: false = no timeout) */
  idle_in_transaction_session_timeout: number | false;
  /** Query timeout in milliseconds (default: false = no timeout) */
  query_timeout: number | false;
  /** Connection timeout in milliseconds (default: 0 = no timeout) */
  connect_timeout: number;
  /** TCP keepalives enabled (default: 1) */
  keepalives: number;
  /** TCP keepalive idle time (default: 0) */
  keepalives_idle: number;
}

const { defaults } = require('pg');

// View default values
console.log('Default host:', defaults.host);           // 'localhost'
console.log('Default port:', defaults.port);           // 5432
console.log('Default user:', defaults.user);           // Current system user
console.log('Default max connections:', defaults.max);  // 10

// Override defaults for application
defaults.host = 'db.example.com';
defaults.port = 5433;
defaults.max = 20;

// Use defaults in client configuration
const client = new Client({
  database: 'myapp',
  // host, port, user, etc. will use defaults
});

// Restore original defaults if needed
defaults.host = 'localhost';
defaults.port = 5432;
defaults.max = 10;

/**
 * Configure how bigint (int8) values are parsed
 * By default, bigints are returned as strings to avoid precision loss
 * Setting parseInt8 = true converts them to JavaScript numbers
 */
interface defaults {
  /**
   * Control bigint parsing behavior
   * - true: Parse int8 as JavaScript number (may lose precision)
   * - false: Return int8 as string (preserves precision)
   */
  parseInt8: boolean;
}

const { defaults } = require('pg');

// Default behavior: bigints as strings
const result1 = await client.query('SELECT 9223372036854775807::bigint as big_number');
console.log(typeof result1.rows[0].big_number); // 'string'
console.log(result1.rows[0].big_number);         // '9223372036854775807'

// Enable numeric parsing for bigints
defaults.parseInt8 = true;

const result2 = await client.query('SELECT 123::bigint as small_number');
console.log(typeof result2.rows[0].small_number); // 'number'  
console.log(result2.rows[0].small_number);         // 123

// Warning: Large bigints may lose precision
const result3 = await client.query('SELECT 9223372036854775807::bigint as big_number');
console.log(result3.rows[0].big_number);         // May not be exact

// Disable to restore string behavior
defaults.parseInt8 = false;

// Also affects bigint arrays
const arrayResult = await client.query('SELECT ARRAY[1::bigint, 2::bigint] as numbers');
console.log(arrayResult.rows[0].numbers); // ['1', '2'] or [1, 2] depending on parseInt8

/**
 * Normalize query configuration from various input formats
 * Handles the different ways queries can be specified
 * @param config - Query text string or configuration object
 * @param values - Optional parameter values
 * @param callback - Optional callback function
 * @returns Normalized query configuration object
 */
function normalizeQueryConfig(
  config: string | QueryConfig, 
  values?: any[] | QueryCallback, 
  callback?: QueryCallback
): QueryConfig;

/**
 * Prepare JavaScript values for PostgreSQL transmission
 * Converts JS types to appropriate PostgreSQL representations
 * @param value - JavaScript value to prepare
 * @returns Value prepared for PostgreSQL
 */
function prepareValue(value: any): any;

const { normalizeQueryConfig, prepareValue } = require('pg').utils;

// Normalize different query formats
const config1 = normalizeQueryConfig('SELECT * FROM users');
// Result: { text: 'SELECT * FROM users' }

const config2 = normalizeQueryConfig('SELECT * FROM users WHERE id = $1', [123]);
// Result: { text: 'SELECT * FROM users WHERE id = $1', values: [123] }

const config3 = normalizeQueryConfig('SELECT NOW()', (err, result) => {});
// Result: { text: 'SELECT NOW()', callback: function }

const config4 = normalizeQueryConfig({
  text: 'SELECT * FROM users WHERE id = $1',
  values: [123]
}, (err, result) => {});
// Result: { text: '...', values: [123], callback: function }

// Prepare values for PostgreSQL
console.log(prepareValue(null));           // null
console.log(prepareValue(undefined));      // null
console.log(prepareValue(123));            // '123'
console.log(prepareValue(true));           // 'true'
console.log(prepareValue([1, 2, 3]));      // '{1,2,3}' (PostgreSQL array)
console.log(prepareValue(new Date()));     // ISO timestamp string
console.log(prepareValue(Buffer.from('data'))); // Buffer (unchanged)

// Custom objects with toPostgres method
const customObj = {
  value: 42,
  toPostgres: () => JSON.stringify({ value: 42 })
};
console.log(prepareValue(customObj));      // '{"value":42}'

/**
 * Parse connection string into configuration object
 * Supports standard PostgreSQL connection string format
 * Note: Uses pg-connection-string module internally
 */

// Connection strings are automatically parsed when used
const client = new Client({
  connectionString: 'postgresql://user:password@host:5432/database?ssl=true'
});

// Equivalent to:
const client2 = new Client({
  user: 'user',
  password: 'password', 
  host: 'host',
  port: 5432,
  database: 'database',
  ssl: true
});

// Complex connection strings
const connectionString = 'postgres://myuser:mypass@myhost:5433/mydb?application_name=myapp&connect_timeout=10';

// Environment variable usage
const client3 = new Client({
  connectionString: process.env.DATABASE_URL
});

/**
 * PostgreSQL error codes and utilities
 * Standard error codes from PostgreSQL documentation
 */
interface PostgreSQLErrorCodes {
  // Class 02 — No Data  
  NO_DATA: '02000';
  NO_ADDITIONAL_DYNAMIC_RESULT_SETS_RETURNED: '02001';
  
  // Class 08 — Connection Exception
  CONNECTION_EXCEPTION: '08000';
  CONNECTION_DOES_NOT_EXIST: '08003';
  CONNECTION_FAILURE: '08006';
  
  // Class 23 — Integrity Constraint Violation
  INTEGRITY_CONSTRAINT_VIOLATION: '23000';
  RESTRICT_VIOLATION: '23001';
  NOT_NULL_VIOLATION: '23502';
  FOREIGN_KEY_VIOLATION: '23503';
  UNIQUE_VIOLATION: '23505';
  CHECK_VIOLATION: '23514';
  
  // Class 42 — Syntax Error or Access Rule Violation
  SYNTAX_ERROR_OR_ACCESS_RULE_VIOLATION: '42000';
  SYNTAX_ERROR: '42601';
  INSUFFICIENT_PRIVILEGE: '42501';
  UNDEFINED_COLUMN: '42703';
  UNDEFINED_TABLE: '42P01';
}

const { DatabaseError } = require('pg');

// Error handling with specific error codes
try {
  await client.query('SELECT * FROM nonexistent_table');
} catch (err) {
  if (err instanceof DatabaseError) {
    switch (err.code) {
      case '42P01': // undefined_table
        console.error('Table does not exist:', err.message);
        break;
      case '42703': // undefined_column  
        console.error('Column does not exist:', err.message);
        break;
      case '23505': // unique_violation
        console.error('Unique constraint violation:', err.detail);
        break;
      case '23503': // foreign_key_violation
        console.error('Foreign key constraint violation:', err.detail);
        break;
      default:
        console.error('Database error:', err.code, err.message);
    }
  } else {
    console.error('Non-database error:', err);
  }
}

// Extract useful error information
function handleDatabaseError(err) {
  if (err instanceof DatabaseError) {
    return {
      code: err.code,
      message: err.message,
      detail: err.detail,
      hint: err.hint,
      position: err.position,
      internalPosition: err.internalPosition,
      internalQuery: err.internalQuery,
      where: err.where,
      schema: err.schema,
      table: err.table,
      column: err.column,
      dataType: err.dataType,
      constraint: err.constraint
    };
  }
  return { message: err.message };
}

/**
 * Common patterns for environment-based configuration
 * Not part of pg core but useful patterns for configuration
 */

// Environment-based configuration
function createDbConfig() {
  return {
    host: process.env.DB_HOST || 'localhost',
    port: parseInt(process.env.DB_PORT || '5432', 10),
    database: process.env.DB_NAME || 'postgres',
    user: process.env.DB_USER || 'postgres',
    password: process.env.DB_PASSWORD,
    ssl: process.env.DB_SSL === 'true',
    max: parseInt(process.env.DB_POOL_SIZE || '10', 10),
    idleTimeoutMillis: parseInt(process.env.DB_IDLE_TIMEOUT || '30000', 10),
    connectionTimeoutMillis: parseInt(process.env.DB_CONNECT_TIMEOUT || '2000', 10),
  };
}

// Usage
const pool = new Pool(createDbConfig());

// Development/production configuration
const config = {
  development: {
    host: 'localhost',
    database: 'myapp_dev',
    ssl: false
  },
  production: {
    connectionString: process.env.DATABASE_URL,
    ssl: { rejectUnauthorized: false }
  }
};

const dbConfig = config[process.env.NODE_ENV || 'development'];
const client = new Client(dbConfig);

/**
 * Type definitions for utility functions and configuration
 */
type QueryConfig = {
  text: string;
  values?: any[];
  name?: string;
  rowMode?: 'array' | 'object';
  types?: TypeOverrides;
  binary?: boolean;
  portal?: string;
  rows?: number;
  callback?: QueryCallback;
};

type QueryCallback = (err: Error | null, result: QueryResult) => void;

interface DefaultsConfig {
  host: string;
  user: string;
  database: string | undefined;
  password: string | null;
  port: number;
  connectionString: string | undefined;
  rows: number;
  binary: boolean;
  max: number;
  idleTimeoutMillis: number;
  client_encoding: string;
  ssl: boolean;
  application_name: string | undefined;
  parseInputDatesAsUTC: boolean;
  statement_timeout: number | false;
  query_timeout: number | false;
  connect_timeout: number;
  parseInt8: boolean;
}