Database Operations

/**
 * Generic query method for executing any SQL query
 * @param query - SQL query string, QueryFile, PreparedStatement, ParameterizedQuery, or function
 * @param values - Query parameter values (optional)
 * @param qrm - Query Result Mask to specify expected result format (optional)
 * @returns Promise resolving to query results
 */
db.query(query: QueryParam, values?: any, qrm?: queryResult): Promise<any>

// Basic query with positional parameters
const users = await db.query('SELECT * FROM users WHERE age > $1', [25]);

// Query with Query Result Mask
const user = await db.query('SELECT * FROM users WHERE id = $1', [123], pgp.queryResult.one);

// Using QueryFile
const getUsersQuery = new pgp.QueryFile('sql/get-users.sql');
const users = await db.query(getUsersQuery, { minAge: 25 });

/**
 * Executes query expecting no data to be returned
 * @param query - SQL query
 * @param values - Query parameter values (optional)
 * @returns Promise resolving to null
 * @throws QueryResultError if any rows are returned
 */
db.none(query: QueryParam, values?: any): Promise<null>

// INSERT/UPDATE/DELETE operations
await db.none('INSERT INTO users(name, email) VALUES($1, $2)', ['John', 'john@example.com']);
await db.none('UPDATE users SET active = $1 WHERE id = $2', [false, 123]);
await db.none('DELETE FROM users WHERE id = $1', [123]);

/**
 * Executes query expecting exactly one row
 * @param query - SQL query
 * @param values - Query parameter values (optional)
 * @param cb - Optional callback for result transformation
 * @param thisArg - Optional 'this' context for callback
 * @returns Promise resolving to single result row
 * @throws QueryResultError if zero rows or multiple rows returned
 */
db.one(query: QueryParam, values?: any, cb?: (value: any) => any, thisArg?: any): Promise<any>

// Get single user by ID
const user = await db.one('SELECT * FROM users WHERE id = $1', [123]);

// Get specific field with callback transformation
const userId = await db.one('INSERT INTO users(name) VALUES($1) RETURNING id', ['Jane'], r => r.id);

// Get count
const count = await db.one('SELECT COUNT(*) as total FROM users', [], r => r.total);

/**
 * Executes query expecting zero or one row
 * @param query - SQL query
 * @param values - Query parameter values (optional)
 * @param cb - Optional callback for result transformation
 * @param thisArg - Optional 'this' context for callback
 * @returns Promise resolving to single result row or null
 * @throws QueryResultError if multiple rows returned
 */
db.oneOrNone(query: QueryParam, values?: any, cb?: (value: any) => any, thisArg?: any): Promise<any | null>

// Find user by email (may not exist)
const user = await db.oneOrNone('SELECT * FROM users WHERE email = $1', ['john@example.com']);

if (user) {
  console.log('User found:', user.name);
} else {
  console.log('User not found');
}

// Get optional configuration value
const config = await db.oneOrNone('SELECT value FROM config WHERE key = $1', ['api_key'], r => r?.value);

/**
 * Executes query expecting one or more rows
 * @param query - SQL query
 * @param values - Query parameter values (optional)
 * @returns Promise resolving to array of result rows
 * @throws QueryResultError if zero rows returned
 */
db.many(query: QueryParam, values?: any): Promise<any[]>

// Get active users (must have at least one)
const activeUsers = await db.many('SELECT * FROM users WHERE active = true');

// Get user orders (user must have orders)
const orders = await db.many('SELECT * FROM orders WHERE user_id = $1', [123]);

/**
 * Executes query expecting zero or more rows
 * @param query - SQL query
 * @param values - Query parameter values (optional)
 * @returns Promise resolving to array of result rows (may be empty)
 */
db.manyOrNone(query: QueryParam, values?: any): Promise<any[]>

/**
 * Alias for manyOrNone - executes query expecting zero or more rows
 * @param query - SQL query
 * @param values - Query parameter values (optional)
 * @returns Promise resolving to array of result rows (may be empty)
 */
db.any(query: QueryParam, values?: any): Promise<any[]>

// Get all users (may be empty)
const users = await db.any('SELECT * FROM users');

// Get filtered results (may be empty)
const filteredUsers = await db.manyOrNone('SELECT * FROM users WHERE age > $1', [65]);

/**
 * Executes query and returns full result object with metadata
 * @param query - SQL query
 * @param values - Query parameter values (optional)
 * @param cb - Optional callback for result transformation
 * @param thisArg - Optional 'this' context for callback
 * @returns Promise resolving to result object with rows, rowCount, command, etc.
 */
db.result(query: QueryParam, values?: any, cb?: (value: IResultExt) => any, thisArg?: any): Promise<IResultExt>

// Get result with metadata
const result = await db.result('SELECT * FROM users WHERE age > $1', [25]);
console.log(`Found ${result.rowCount} users`);
console.log('Rows:', result.rows);
console.log('Command:', result.command);
console.log('Duration:', result.duration); // Available in 'receive' event context

// Transform result
const count = await db.result('SELECT COUNT(*) FROM users', [], r => r.rows[0].count);

/**
 * Executes multiple queries and returns array of result objects
 * @param query - SQL query string with multiple statements separated by semicolons
 * @param values - Query parameter values (optional)
 * @returns Promise resolving to array of result objects
 */
db.multiResult(query: QueryParam, values?: any): Promise<IResult[]>

// Execute multiple queries
const results = await db.multiResult(`
  SELECT COUNT(*) as user_count FROM users;
  SELECT COUNT(*) as order_count FROM orders;
  SELECT AVG(amount) as avg_amount FROM orders;
`);

console.log('User count:', results[0].rows[0].user_count);
console.log('Order count:', results[1].rows[0].order_count);
console.log('Average amount:', results[2].rows[0].avg_amount);

/**
 * Executes multiple queries and returns array of data arrays
 * @param query - SQL query string with multiple statements
 * @param values - Query parameter values (optional)
 * @returns Promise resolving to array of data arrays
 */
db.multi(query: QueryParam, values?: any): Promise<Array<any[]>>

// Execute multiple queries, get data only
const [users, orders, products] = await db.multi(`
  SELECT * FROM users WHERE active = true;
  SELECT * FROM orders WHERE status = 'pending';
  SELECT * FROM products WHERE in_stock = true;
`);

/**
 * Executes query using readable stream for large result sets
 * @param qs - Query stream object (from pg-query-stream)
 * @param init - Initialization function called with the stream
 * @returns Promise resolving to processing statistics
 */
db.stream(qs: ReadableStream, init: (stream: ReadableStream) => void): Promise<StreamResult>

interface StreamResult {
  processed: number
  duration: number
}

const QueryStream = require('pg-query-stream');

// Process large dataset with streaming
const stats = await db.stream(
  new QueryStream('SELECT * FROM large_table WHERE active = $1', [true]),
  stream => {
    stream.on('data', row => {
      // Process each row
      console.log('Processing:', row.id);
    });
  }
);

console.log(`Processed ${stats.processed} rows in ${stats.duration}ms`);

/**
 * Calls a database function
 * @param funcName - Name of the database function
 * @param values - Function parameter values (optional)
 * @param qrm - Query Result Mask for expected result format (optional)
 * @returns Promise resolving to function result
 */
db.func(funcName: string, values?: any, qrm?: queryResult): Promise<any>

// Call function expecting single value
const result = await db.func('calculate_total', [123], pgp.queryResult.one);

// Call function expecting multiple rows
const stats = await db.func('get_user_statistics', [userId], pgp.queryResult.many);

// Call function with no parameters
const serverTime = await db.func('now', [], pgp.queryResult.one);

/**
 * Calls a stored procedure
 * @param procName - Name of the stored procedure
 * @param values - Procedure parameter values (optional)
 * @param cb - Optional callback for result transformation
 * @param thisArg - Optional 'this' context for callback
 * @returns Promise resolving to first result row or null
 */
db.proc(procName: string, values?: any, cb?: (value: any) => any, thisArg?: any): Promise<any | null>

// Call stored procedure
const result = await db.proc('process_order', [orderId, userId]);

// Call procedure with result transformation
const processedId = await db.proc('create_user_profile', [userData], r => r?.id);

/**
 * Executes query and maps each row through transformation function
 * @param query - SQL query
 * @param values - Query parameter values
 * @param cb - Mapping function called for each row
 * @param thisArg - Optional 'this' context for callback
 * @returns Promise resolving to array of transformed values
 */
db.map(query: QueryParam, values: any, cb: (row: any, index: number, data: any[]) => any, thisArg?: any): Promise<any[]>

// Map users to display format
const userNames = await db.map(
  'SELECT id, first_name, last_name FROM users',
  [],
  (row, index) => `${row.first_name} ${row.last_name}`
);

// Extract specific values
const orderTotals = await db.map(
  'SELECT amount, tax FROM orders WHERE user_id = $1',
  [userId],
  row => row.amount + row.tax
);

/**
 * Executes query and calls function for each row (side effects)
 * @param query - SQL query
 * @param values - Query parameter values
 * @param cb - Function called for each row
 * @param thisArg - Optional 'this' context for callback
 * @returns Promise resolving to original array of rows
 */
db.each(query: QueryParam, values: any, cb: (row: any, index: number, data: any[]) => void, thisArg?: any): Promise<any[]>

// Log each user
const users = await db.each(
  'SELECT * FROM users WHERE active = true',
  [],
  (user, index) => {
    console.log(`User ${index + 1}: ${user.name} (${user.email})`);
  }
);

// Update external system for each order
await db.each(
  'SELECT * FROM orders WHERE status = $1',
  ['pending'],
  async (order) => {
    await externalAPI.notifyOrder(order);
  }
);

// Result object interface
interface IResultExt {
  rows: any[]
  rowCount: number
  command: string
  oid: number
  fields: FieldDef[]
  duration?: number // Available in certain contexts
}

// Field definition
interface FieldDef {
  name: string
  tableID: number
  columnID: number
  dataTypeID: number
  dataTypeSize: number
  dataTypeModifier: number
  format: string
}

// Query result error codes
enum queryResultErrorCode {
  noData = 0,    // No data returned when expecting some
  notEmpty = 1,  // Data returned when expecting none
  multiple = 2   // Multiple rows returned when expecting one
}

// Query Result Mask values
enum queryResult {
  one = 1,   // Expecting exactly one row
  many = 2,  // Expecting one or more rows
  none = 4,  // Expecting no rows
  any = 6    // Expecting zero or more rows (many | none)
}