PostgreSQL client #

A library for connecting to and querying PostgreSQL databases (see Postgres Protocol). This driver uses the more efficient and secure extended query format of the PostgreSQL protocol.

Usage #

Create a Connection:

  final conn = await Connection.open(Endpoint(
    host: 'localhost',
    database: 'postgres',
    username: 'user',
    password: 'pass',
  ));

Execute queries with execute:

  final result = await conn.execute("SELECT 'foo'");
  print(result[0][0]); // first row and first field

Named parameters, returning rows as map of column names:

  final result = await conn.execute(
    Sql.named('SELECT * FROM a_table WHERE id=@id'),
    parameters: {'id': 'xyz'},
  );
  print(result.first.toColumnMap());

Execute queries in a transaction:

  await conn.runTx((s) async {
    final rs = await s.execute('SELECT count(*) FROM foo');
    await s.execute(
      r'UPDATE a_table SET totals=$1 WHERE id=$2',
      parameters: [rs[0][0], 'xyz'],
    );
  });

See the API documentation: https://pub.dev/documentation/postgres/latest/

Connection string URLs #

The package supports connection strings for both single connections and connection pools:

await Connection.openFromUrl('postgresql://localhost/mydb');
await Connection.openFromUrl(
  'postgresql://user:pass@db.example.com:5432/production?sslmode=verify-full'
);
await Connection.openFromUrl(
  'postgresql://localhost/mydb?connect_timeout=10&query_timeout=60'
);
Pool.withUrl(
  'postgresql://localhost/mydb?max_connection_count=10&max_connection_age=3600'
);

URL Format #

postgresql://[userspec@][hostspec][:port][/dbname][?paramspec]

• Scheme: postgresql:// or postgres://
• User: username or username:password (can also be set via user/username and password query parameters)
• Host: hostname or IP address (defaults to localhost). Supports multiple hosts via comma-separated list (host1:5433,host2:5434) or multiple host query parameters (?host=host1:5433&host=host2:5434)
• Port: port number (defaults to 5432, can be overridden via port query parameter)
• Database: database name (defaults to postgres, can be overridden via database query parameter)
• Parameters: query parameters (see below)

Standard Parameters #

These parameters are supported by Connection.openFromUrl():

Pool-Specific Parameters #

These additional parameters are supported by Pool.withUrl():

Connection pooling #

The library supports connection pooling (and masking the connection pool as regular session executor).

Custom type codecs #

The library supports registering custom type codecs (and generic object encoders) through theConnectionSettings.typeRegistry.

Streaming replication protocol #

The library supports connecting to PostgreSQL using the Streaming Replication Protocol. See Connection documentation for more info. An example can also be found at the following repository: postgresql-dart-replication-example

Other notes #

This library originally started as StableKernel's postgres library, but got a full API overhaul and partial rewrite of the internals.

Please file feature requests and bugs at the issue tracker.