pgconn

Package pgconn is a low-level PostgreSQL database driver. It operates at nearly the same level as the C library libpq. Applications should handle normal queries with a higher level library and only use pgconn directly when needed for low-level access to PostgreSQL functionality.

Connecting

This library follows libpq behavior as much as possible. This means that you can use almost all libpq connection options and both connection string formats.

Supported libpq features:

• .pgpass file
• .pg_service.conf file
• Environment Variables
• Multiple connection hosts

Using connection string

Read about libpq connection string format 34.1.1. Connection Strings.

Supported connection strings:

• Keyword/Value, example: host=localhost port=5432 user=user password=secret dbname=mydb connect_timeout=10
  • Multi-hosts, example: host=localhost,otherhost port=5432,5433 user=user password=secret dbname=mydb connect_timeout=10
• URI, example: postgresql://user:secret@localhost:5432/mydb?connect_timeout=10
  • Multi-hosts, example: postgresql://user:secret@localhost:5432,otherhost:5433/mydb?connect_timeout=10

$connString = 'host=localhost port=5432 user=user password=secret dbname=mydb connect_timeout=10';
// or
$connString = 'postgresql://user:secret@localhost:5432/mydb?connect_timeout=10';

$conf = \PhpPg\PgConn\Config\Internal\parseConfig($connString);

$pgConnector = new \PhpPg\PgConn\PgConnector();
$conn = $pgConnector->connect($conf);

Read about supported connection options 34.1.2. Parameter Key Words

By instantiating config manually

$conf = new \PhpPg\PgConn\Config\Config(
    hosts: [
        new \PhpPg\PgConn\Config\HostConfig(
            host: '127.0.0.1',
            port: 5432,
            password: 'secret',
            tlsConfig: null,
        ),
        new \PhpPg\PgConn\Config\HostConfig(
            host: '127.0.0.1',
            port: 5433,
            password: 'secret_pass',
            tlsConfig: new \PhpPg\PgConn\Config\TlsConfig(
                tlsContext: (new \Amp\Socket\ClientTlsContext(''))
                    ->withoutPeerVerification()
                    ->withCaFile('path to CA')
                    ->withCertificate(new \Amp\Socket\Certificate('path to public key', 'path to private key')),
                sslMode: \PhpPg\PgConn\Config\SslMode::PREFER,
            )
        ),
    ],
    user: 'postgres',
    database: 'mydb',
    connectTimeout: 1,
    // and other params
);

// Also, fluent interface supported
$conf = $conf
    ->withRuntimeParam('application_name', 'My Application')
    ->withOnNotice(new class implements \PhpPg\PgConn\Config\NoticeHandlerInterface {
        public function __invoke(\PhpPg\PgConn\Notice $notice): void {
            // do something with notice
        }
    })
    ->withOnNotification(new class implements \PhpPg\PgConn\Config\NotificationHandlerInterface {
        public function __invoke(\PhpPg\PgConn\Notification $notice): void {
            // do something with notification
        }
    })
    // Any PSR compatible logger for debugging purposes
    ->withLogger($logger);

$pgConnector = new \PhpPg\PgConn\PgConnector();
$conn = $pgConnector->connect($conf);

API

PgConn is not concurrency safe, it should be used inside a connection pool.

Methods:

• PgConn::close(): void - Disconnect from the server

• PgConn::getConfig(): Config - Get config that was used to establish the connection

• PgConn::getHostConfig(): HostConfig - Get config for the currently connected host

• PgConn::getSocket(): Socket - Get underlying socket connection

• PgConn::getStatus(): PgConnStatus - Get current connection state

• PgConn::getPid(): int - Get backend process id

• PgConn::getSecretKey(): int - Get secret key to cancel requests

• PgConn::getTxStatus(): string (1 byte) - Get transaction status (see ReadyForQuery message for details)

• PgConn::getParameterStatuses(): array<string, string> - Get current session runtime parameters

• PgConn::getParameterStatus(string $paramName): string - Get current session runtime parameter

• PgConn::cancelRequest - Cancel running in-progress request (might not have effect, see Cancelling API calls section)

• PgConn::exec - Execute query using simple protocol (multiple queries can be specified at once).

  See example

  /** @var \PhpPg\PgConn\PgConn $conn */
  $sql = "SELECT 'Hello World' AS msg, 322 AS num; SELECT * FROM table; UPDATE table SET idx = idx + 1";
  
  /**
  * If the query contains a syntax error or contains invalid data, no exception will be thrown,
  * an exception will be thrown when the results are received.
  */
  $mrr = $conn->exec($sql);
  
  // Fetch all results at once
  $results = $mrr->readAll();
  
  $results[0]; // query 1 results
  $results[1]; // query 2 results
  $results[2]; // query 3 results
  
  $results[0]->getCommandTag(); // query 1 execution result (e.g. SELECT 1)
  $rows = $results[0]->getRows(); // query 1 returned rows (multidimensional array)
  $rows[0]; // query 1 row 1
  $rows[0][0] // query 1 row 1 column 1
  $rows[0][0] // query 1 row 1 column 2
  
  $results[0]->getFieldDescriptions(); // query 1 returned rows format information (binary/text, data type, column name, etc)
  
  // Fetch results in iterative way
  while ($mrr->nextResult()) {
      $rr = $mrr->getResultReader();
      $fieldDescriptions = $rr->getFieldDescriptions()
      
      while ($rr->nextRow()) {
          $result = $rr->getResult();
  
          $rows = $result->getRows();
          foreach ($rows as $rowIdx => $row) {
              foreach ($row as $colIdx => $colValue) {
                  // Do something with returned data
              }
          }
      }
      
      $commandTag = $rr->getCommandTag();
  }

• PgConn::execParams - Execute query using extended protocol (parameter bindings allowed), multiple queries are not allowed

  See example

  /** @var \PhpPg\PgConn\PgConn $conn */
  $rr = $conn->execParams(
      sql: 'SELECT $1::int, $2::text',
      paramValues: ['100', 'Hello world'],
      // param formats (binary/text)
      paramFormats: [],
      // param data types
      paramOIDs: [],
      // return rows format (binary/text)
      resultFormats: [],
  );
  
  $result = $rr->getResult();
  
  $result->getFieldDescriptions(); // returned rows format information (binary/text, data type, column name, etc)
  $result->getRows(); // returned rows
  $result->getCommandTag(); // command execution result

• PgConn::prepare - Prepare statement

  See example

  /** @var \PhpPg\PgConn\PgConn $conn */
  $stmtDesc = $conn->prepare(
      name: 'my_stmt_1',
      sql: "INSERT INTO my_table (col1, col2) VALUES ($1::int, $2::text)"
  );
  $stmtDesc->name; // prepared statement name
  $stmtDesc->fields; // prepared statement return rows format description
  $stmtDesc->paramOIDs; // prepared statement bind parameter types
  $stmtDesc->sql; // prepared statement query

• PgConn:execPrepared - Execute prepared statement

  See example

  /** @var \PhpPg\PgConn\PgConn $conn */
  $rr = $conn->execPrepared(
      stmtName: 'my_stmt_1',
      paramValues: ['100', 'Hello World'],
      // parameter formats (1 - text; 0 - binary
      // One item per each paramValue or one item for all paramValues
      paramFormats: [],
      // desired format of returned rows, such as paramFormats
      resultFormats: [],
  );
  $result = $rr->getResult();
  
  $result->getFieldDescriptions(); // returned rows format information (binary/text, data type, column name, etc)
  $result->getRows(); // returned rows
  $result->getCommandTag(); // command execution result

• PgConn::copyFrom - Copy data from readable stream to the PostgreSQL server

  See example

  /** @var \PhpPg\PgConn\PgConn $conn */
  $genData = static function (): \Generator {
      for ($i = 0; $i < 1000; $i++) {
          yield "{$i}, \"foo {$i} bar\"\n";
      }
  };
  $stream = new \Amp\ByteStream\ReadableIterableStream($genData());
  
  $ct = $conn->copyFrom('COPY foo FROM STDIN WITH (FORMAT csv)', $stream);
  echo "Rows affected: {$ct->rowsAffected()}\n";

• PgConn::copyTo - Copy data from the PostgreSQL server to writable stream

  See example

  /** @var \PhpPg\PgConn\PgConn $conn */
  $stream = new \Amp\ByteStream\WritableIterableStream(0);
  
  \Amp\async(static function () use ($stream) {
  foreach ($stream->getIterator() as $row) {
  // Process row copied from postgres
  }
  });
  
  $ct = $conn->copyTo('COPY foo TO STDOUT', $stream);
  echo "Rows affected: {$ct->rowsAffected()}\n";

Cancelling API calls

Any of API calls can be canceled using AMPHP cancellation objects.
When cancellation occurs, library sends CancelRequest message to the PostgreSQL server.

But there is an important note from the PostgreSQL Protocol Flow:

The cancellation signal might or might not have any effect — for example, if it arrives after the backend has finished processing the query, then it will have no effect.
If the cancellation is effective, it results in the current command being terminated early with an error message.

The upshot of all this is that for reasons of both security and efficiency, the frontend has no direct way to tell whether a cancel request has succeeded.
It must continue to wait for the backend to respond to the query.
Issuing a cancel simply improves the odds that the current query will finish soon, and improves the odds that it will fail with an error message instead of succeeding.

This means that a Cancellation object may not immediately cancel a running query.

See for more information 53.2.7. Canceling Requests in Progress

Inspired by Golang package jackc/pgconn