Query Execution

/**
 * Creates a new query instance
 * @param config - Query configuration object or SQL text
 * @param values - Optional parameter values
 * @param callback - Optional callback for query completion
 */
class Query extends EventEmitter {
  constructor(config: QueryConfig | string, values?: any[], callback?: QueryCallback);
}

interface QueryConfig {
  /** SQL query text */
  text: string;
  /** Parameter values for query */
  values?: any[];
  /** Named prepared statement identifier */
  name?: string;
  /** Row output mode ('array' or 'object') */
  rowMode?: 'array' | 'object';
  /** Custom type overrides */
  types?: TypeOverrides;
  /** Binary result format */
  binary?: boolean;
  /** Portal name for prepared statements */
  portal?: string;
  /** Maximum rows to return per execute */
  rows?: number;
  /** Callback function for query completion */
  callback?: QueryCallback;
  /** Query execution mode */
  queryMode?: 'simple' | 'extended';
}

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

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

// Basic query
const query1 = new Query('SELECT * FROM users');

// Parameterized query
const query2 = new Query('SELECT * FROM users WHERE id = $1', [123]);

// Named prepared statement
const query3 = new Query({
  name: 'get-user',
  text: 'SELECT * FROM users WHERE id = $1',
  values: [123]
});

// Query with callback
const query4 = new Query('SELECT NOW()', [], (err, result) => {
  if (err) throw err;
  console.log(result.rows[0]);
});

// Array row mode
const query5 = new Query({
  text: 'SELECT name, email FROM users',
  rowMode: 'array'
});

interface QueryResult {
  /** SQL command that was executed */
  command: string;
  /** Number of rows affected by the command */
  rowCount: number;
  /** Object ID returned by INSERT operations */
  oid: number;
  /** Array of result rows */
  rows: any[];
  /** Field definitions for result columns */
  fields: FieldDef[];
  /** Row constructor function (when using custom row types) */
  _parsers?: any[];
  /** Query duration in milliseconds */
  duration?: number;
}

interface FieldDef {
  /** Column name */
  name: string;
  /** Table OID */
  tableID: number;
  /** Column attribute number */
  columnID: number;
  /** Data type OID */
  dataTypeID: number;
  /** Data type size */
  dataTypeSize: number;
  /** Type modifier */
  dataTypeModifier: number;
  /** Result format ('text' or 'binary') */
  format: string;
}

const result = await client.query('SELECT id, name FROM users WHERE active = true');

console.log('Command:', result.command);     // 'SELECT'
console.log('Row count:', result.rowCount);  // Number of rows returned
console.log('Rows:', result.rows);           // [{ id: 1, name: 'Alice' }, ...]

// Field information
result.fields.forEach(field => {
  console.log(`Column: ${field.name}, Type: ${field.dataTypeID}`);
});

// INSERT result
const insertResult = await client.query('INSERT INTO users (name) VALUES ($1) RETURNING id', ['Bob']);
console.log('New ID:', insertResult.rows[0].id);
console.log('OID:', insertResult.oid);

/**
 * Execute a named prepared statement
 * Automatically prepared on first execution and cached thereafter
 */
interface PreparedStatement {
  name: string;
  text: string;
  values?: any[];
}

// Define prepared statement
const getUserQuery = {
  name: 'get-user-by-id',
  text: 'SELECT * FROM users WHERE id = $1'
};

// First execution: prepares and executes
const user1 = await client.query(getUserQuery, [123]);

// Subsequent executions: uses cached plan
const user2 = await client.query(getUserQuery, [456]);

// Prepared statement with multiple parameters
const complexQuery = {
  name: 'user-search',
  text: 'SELECT * FROM users WHERE age BETWEEN $1 AND $2 AND city = $3 ORDER BY name',
  values: [25, 65, 'San Francisco']
};

const results = await client.query(complexQuery);

/**
 * Parameter placeholder format: $1, $2, $3, etc.
 * Values are automatically escaped and type-converted
 */

// Basic parameter binding
await client.query('SELECT * FROM users WHERE name = $1', ['Alice']);

// Multiple parameters
await client.query(
  'SELECT * FROM orders WHERE user_id = $1 AND created_at > $2 AND amount >= $3',
  [123, new Date('2023-01-01'), 100.00]
);

// Array values (PostgreSQL arrays)
await client.query('SELECT * FROM products WHERE tags && $1', [['electronics', 'mobile']]);

// JSON parameters
await client.query('INSERT INTO logs (data) VALUES ($1)', [{ event: 'login', user: 123 }]);

// Buffer/binary data
const imageBuffer = Buffer.from('...image data...');
await client.query('INSERT INTO images (data) VALUES ($1)', [imageBuffer]);

// Date/time parameters
await client.query('INSERT INTO events (scheduled_at) VALUES ($1)', [new Date()]);

/**
 * Row mode configuration affects result structure
 * - 'object': rows as objects with column names as keys (default)
 * - 'array': rows as arrays with values in column order
 */

// Default object mode
const objectResult = await client.query('SELECT id, name, email FROM users LIMIT 1');
console.log(objectResult.rows[0]);
// { id: 1, name: 'Alice', email: 'alice@example.com' }

// Array mode
const arrayResult = await client.query({
  text: 'SELECT id, name, email FROM users LIMIT 1',
  rowMode: 'array'
});
console.log(arrayResult.rows[0]);
// [1, 'Alice', 'alice@example.com']

// Array mode useful for performance-critical operations
const performanceQuery = {
  text: 'SELECT * FROM large_table',
  rowMode: 'array'
};
const results = await client.query(performanceQuery);
// Less memory overhead, faster processing

/**
 * Portal-based query execution for large result sets
 * Fetches results in batches rather than all at once
 */
interface PortalConfig extends QueryConfig {
  /** Number of rows to fetch per portal execution */
  rows: number;
  /** Portal name for prepared statements */
  portal?: string;
}

// Portal-based execution (fetches in batches)
const query = new Query({
  text: 'SELECT * FROM large_table',
  rows: 1000  // Fetch 1000 rows at a time
});

query.on('row', (row) => {
  // Process each row individually
  console.log('Processing row:', row.id);
});

query.on('end', (result) => {
  console.log('Query completed, total rows:', result.rowCount);
});

query.on('error', (err) => {
  console.error('Query error:', err);
});

client.query(query);

// For true streaming, use the separate pg-query-stream package
// const QueryStream = require('pg-query-stream');
// const queryStream = new QueryStream('SELECT * FROM huge_table');
// const stream = client.query(queryStream);

// Query execution events
query.on('row', (row, result) => {
  // Emitted for each row (when using streaming)
});

query.on('end', (result) => {
  // Query completed successfully
});

query.on('error', (err) => {
  // Query failed with error
});

const query = new Query('SELECT * FROM users');

query.on('row', (row) => {
  console.log('User:', row.name);
});

query.on('end', (result) => {
  console.log(`Query completed: ${result.rowCount} rows`);
});

query.on('error', (err) => {
  console.error('Query failed:', err.message);
});

client.query(query);

interface QueryModes {
  /** Simple query protocol (default for ad-hoc queries) */
  simple: 'simple';
  /** Extended query protocol (required for prepared statements) */
  extended: 'extended';
}

// Simple protocol (default)
await client.query('SELECT * FROM users');

// Force extended protocol
await client.query({
  text: 'SELECT * FROM users WHERE id = $1',
  values: [123],
  queryMode: 'extended'
});

// Extended protocol automatically used for:
// - Named statements
// - Binary mode
// - Custom row limits
const extendedQuery = {
  name: 'get-users',
  text: 'SELECT * FROM users',
  binary: true,
  rows: 100
};

/**
 * Query validation occurs before execution
 * Common validations:
 * - Text cannot be empty
 * - Parameter count must match placeholders  
 * - Named queries must have valid names
 */

// Valid queries
await client.query('SELECT 1');
await client.query('SELECT * FROM users WHERE id = $1', [123]);

// Invalid queries (will throw errors)
try {
  await client.query('');  // Empty query text
} catch (err) {
  console.error('Empty query error:', err.message);
}

try {
  await client.query('SELECT * FROM users WHERE id = $1');  // Missing parameter
} catch (err) {
  console.error('Missing parameter error:', err.message);
}

try {
  await client.query('SELECT * FROM users WHERE id = $1', [123, 456]);  // Too many parameters
} catch (err) {
  console.error('Parameter mismatch error:', err.message);
}

type QueryConfig = {
  text: string;
  values?: any[];
  name?: string;
  rowMode?: 'array' | 'object';
  types?: TypeOverrides;
  binary?: boolean;
  portal?: string;
  rows?: number;
  callback?: QueryCallback;
  queryMode?: 'simple' | 'extended';
};

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

interface QueryResult {
  command: string;
  rowCount: number;
  oid: number;
  rows: any[];
  fields: FieldDef[];
  duration?: number;
}

interface FieldDef {
  name: string;
  tableID: number;
  columnID: number;
  dataTypeID: number;
  dataTypeSize: number;
  dataTypeModifier: number;
  format: string;
}