Connection Management

interface ConnectionOptions {
  /** Maximum number of connections in the pool */
  max?: number; // default: 10
  
  /** Connection idle timeout in seconds */
  idle_timeout?: number; // default: null (no timeout)
  
  /** Connection timeout in seconds */
  connect_timeout?: number; // default: 30
  
  /** Maximum connection lifetime in seconds */
  max_lifetime?: number; // default: random 30-60 minutes
  
  /** Keep-alive interval in seconds */
  keep_alive?: number; // default: 60
  
  /** Backoff strategy for reconnection attempts */
  backoff?: boolean | ((attemptNum: number) => number); // default: exponential
  
  /** Maximum pipeline requests per connection */
  max_pipeline?: number; // default: 100
  
  /** Session targeting for read-write/read-only connections */
  target_session_attrs?: 'read-write' | 'read-only' | 'primary' | 'standby' | 'prefer-standby';
  
  /** Publications for logical replication */
  publications?: string; // default: 'alltables'
  
  /** Custom socket implementation */
  socket?: any;
}

// Production connection pool configuration
const sql = postgres({
  host: "localhost",
  database: "myapp",
  max: 20,                    // Up to 20 concurrent connections
  idle_timeout: 300,          // Close idle connections after 5 minutes
  connect_timeout: 10,        // Timeout connection attempts after 10 seconds
  max_lifetime: 3600,         // Recycle connections every hour
  keep_alive: 30              // Send keep-alive every 30 seconds
});

// Development configuration
const sql = postgres({
  host: "localhost", 
  database: "myapp_dev",
  max: 3,                     // Fewer connections for development
  target_session_attrs: 'read-write'  // Ensure primary connection
});

// High-throughput configuration
const sql = postgres({
  host: "localhost",
  database: "myapp",
  max: 50,                    // More connections for high load
  max_pipeline: 200,          // Higher pipeline limit
  max_lifetime: 1800,         // Recycle connections every 30 minutes
  keep_alive: 15              // More frequent keep-alive
});

// Read replica configuration  
const sql = postgres({
  host: ["primary.db.com", "replica.db.com"],
  database: "myapp",
  target_session_attrs: 'prefer-standby', // Prefer read replicas
  max: 20,
  idle_timeout: 60            // Shorter idle timeout
});

// Custom backoff strategy
const sql = postgres({
  backoff: (attemptNum) => Math.min(1000 * Math.pow(2, attemptNum), 30000)
});

/**
 * Reserve a dedicated connection from the pool
 * @returns Promise resolving to reserved SQL instance
 */
reserve(): Promise<ReservedSql>;

interface ReservedSql extends Sql {
  /** Release the reserved connection back to the pool */
  release(): void;
}

// Reserve connection for critical operations
const reservedSql = await sql.reserve();

try {
  // This connection is guaranteed to be available
  await reservedSql`BEGIN`;
  
  const result = await reservedSql`
    UPDATE critical_data SET value = ${newValue} WHERE id = ${id}
  `;
  
  await reservedSql`
    INSERT INTO audit_log (action, data_id) VALUES ('update', ${id})
  `;
  
  await reservedSql`COMMIT`;
  
  console.log("Critical operation completed");
} catch (error) {
  await reservedSql`ROLLBACK`;
  throw error;
} finally {
  // Always release the connection
  reservedSql.release();
}

// Using reserved connection for long-running operations
async function processLargeDataset() {
  const reservedSql = await sql.reserve();
  
  try {
    for (const batch of largeDataset) {
      await reservedSql`
        INSERT INTO processed_data (batch_id, data) 
        VALUES (${batch.id}, ${sql.json(batch.data)})
      `;
    }
  } finally {
    reservedSql.release();
  }
}

/**
 * Close all connections gracefully
 * @param options - Shutdown options
 * @returns Promise that resolves when all connections are closed
 */
end(options?: { timeout?: number }): Promise<void>;

/**
 * Close all connections immediately
 * @returns Promise that resolves when all connections are closed
 */
close(): Promise<void>;

// Graceful shutdown (recommended)
async function gracefulShutdown() {
  console.log("Shutting down database connections...");
  
  try {
    // Wait up to 10 seconds for queries to complete
    await sql.end({ timeout: 10 });
    console.log("Database connections closed gracefully");
  } catch (error) {
    console.error("Error during graceful shutdown:", error);
    // Force close if graceful shutdown fails
    await sql.close();
  }
}

// Handle process termination
process.on('SIGTERM', gracefulShutdown);
process.on('SIGINT', gracefulShutdown);

// Application shutdown
async function shutdownApp() {
  // Stop accepting new requests first
  server.close();
  
  // Then close database connections
  await sql.end({ timeout: 5 });
  
  process.exit(0);
}

// Immediate shutdown (force close)
async function emergencyShutdown() {
  console.log("Force closing database connections...");
  await sql.close();
  console.log("All connections closed immediately");
}

interface ConnectionOptions {
  // Basic connection
  host?: string | string[];  // Single host or array for failover
  port?: number | number[];  // Port(s) corresponding to host(s)
  path?: string;             // Unix socket path
  database?: string;         // Database name
  user?: string;             // Username
  password?: string | (() => string | Promise<string>); // Password or function
  
  // SSL configuration
  ssl?: 'require' | 'allow' | 'prefer' | 'verify-full' | boolean | object;
  
  // Session attributes for multi-host setups
  target_session_attrs?: 'read-write' | 'read-only' | 'primary' | 'standby' | 'prefer-standby';
  
  // Query configuration
  prepare?: boolean;         // Use prepared statements (default: true)
  fetch_types?: boolean;     // Auto-fetch type information (default: true)
  
  // Event handlers
  onnotice?: (notice: Notice) => void;
  onparameter?: (key: string, value: any) => void;
  onclose?: (connectionId: number) => void;
  
  // Advanced
  socket?: any;              // Custom socket implementation
  publications?: string;     // Logical replication publications
}

// Multi-host configuration with failover
const sql = postgres({
  host: ["primary.db.example.com", "replica.db.example.com"],
  port: [5432, 5432],
  database: "myapp",
  user: "app_user",
  password: process.env.DB_PASSWORD,
  target_session_attrs: "primary", // Prefer primary server
  ssl: "require"
});

// Password from environment or function
const sql = postgres({
  host: "localhost",
  database: "myapp",
  user: "app_user",
  password: async () => {
    // Fetch password from secret manager
    return await getSecretValue("db_password");
  }
});

// Unix socket connection
const sql = postgres({
  path: "/var/run/postgresql",
  database: "myapp",
  user: "app_user"
});

// SSL configuration
const sql = postgres({
  host: "secure.db.example.com",
  database: "myapp", 
  ssl: {
    rejectUnauthorized: true,
    ca: fs.readFileSync('./ca-certificate.crt').toString(),
    key: fs.readFileSync('./client-key.key').toString(),
    cert: fs.readFileSync('./client-certificate.crt').toString()
  }
});

// Event handlers
const sql = postgres({
  host: "localhost",
  database: "myapp",
  onnotice: (notice) => {
    console.log("PostgreSQL notice:", notice.message);
  },
  onparameter: (key, value) => {
    console.log(`Server parameter changed: ${key} = ${value}`);
  },
  onclose: (connectionId) => {
    console.log(`Connection ${connectionId} closed`);
  }
});

interface Sql {
  /** Access to parsed connection options */
  readonly options: ParsedOptions;
  
  /** Current connection parameters from server */
  readonly parameters: ConnectionParameters;
}

interface ConnectionParameters {
  application_name: string;
  server_version: string;
  server_encoding: string;
  client_encoding: string;
  is_superuser: string;
  session_authorization: string;
  DateStyle: string;
  TimeZone: string;
  [parameter: string]: string;
}

// Check connection configuration
console.log("Database host:", sql.options.host);
console.log("Pool size:", sql.options.max);
console.log("SSL enabled:", sql.options.ssl);

// Monitor server parameters
console.log("Server version:", sql.parameters.server_version);
console.log("Timezone:", sql.parameters.TimeZone);
console.log("Application name:", sql.parameters.application_name);

// Custom application name
const sql = postgres({
  host: "localhost",
  database: "myapp",
  connection: {
    application_name: "MyApp v1.2.3"
  }
});

// Monitor parameter changes
const sql = postgres({
  onparameter: (key, value) => {
    if (key === 'TimeZone') {
      console.log(`Server timezone changed to: ${value}`);
    }
  }
});

// Connection-related error types
interface ConnectionError extends Error {
  code: 'CONNECTION_DESTROYED' | 'CONNECT_TIMEOUT' | 'CONNECTION_CLOSED' | 'CONNECTION_ENDED';
  errno: string;
  address: string;
  port?: number;
}

// Handle connection errors
try {
  const result = await sql`SELECT * FROM users`;
} catch (error) {
  if (error.code === 'CONNECTION_DESTROYED') {
    console.error("Database connection was destroyed");
    // Implement reconnection logic
  } else if (error.code === 'CONNECT_TIMEOUT') {
    console.error("Database connection timed out");
    // Implement retry logic
  } else {
    console.error("Database error:", error);
  }
}

// Connection retry with backoff
async function executeWithRetry(query, maxRetries = 3) {
  for (let attempt = 1; attempt <= maxRetries; attempt++) {
    try {
      return await query();
    } catch (error) {
      if (error.code?.includes('CONNECTION') && attempt < maxRetries) {
        const delay = Math.min(1000 * Math.pow(2, attempt - 1), 10000);
        console.log(`Connection failed, retrying in ${delay}ms...`);
        await new Promise(resolve => setTimeout(resolve, delay));
        continue;
      }
      throw error;
    }
  }
}

// Usage
const result = await executeWithRetry(async () => {
  return await sql`SELECT * FROM users WHERE active = true`;
});

// Production settings
const sql = postgres({
  max: Math.min(20, parseInt(process.env.DB_MAX_CONNECTIONS || "10")),
  idle_timeout: 300,        // 5 minutes
  connect_timeout: 10,      // 10 seconds  
  max_lifetime: 3600,       // 1 hour
  keep_alive: 30,           // 30 seconds
  
  // Use read replicas when possible
  target_session_attrs: process.env.DB_READ_ONLY === 'true' ? 'standby' : 'primary'
});

// Monitor pool exhaustion
let activeQueries = 0;
const originalQuery = sql.bind(sql);

sql = new Proxy(sql, {
  apply: function(target, thisArg, argumentsList) {
    activeQueries++;
    const query = originalQuery.apply(thisArg, argumentsList);
    
    query.finally(() => {
      activeQueries--;
      if (activeQueries > sql.options.max * 0.8) {
        console.warn(`High connection usage: ${activeQueries}/${sql.options.max}`);
      }
    });
    
    return query;
  }
});