isoos/postgresql-dart

README

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():

Parameter	Type	Description	Example Values
`application_name`	String	Sets the application name	`application_name=myapp`
`client_encoding`	String	Character encoding	`UTF8`, `LATIN1`
`connect_timeout`	Integer	Connection timeout in seconds	`connect_timeout=30`
`database`	String	Database name (overrides URL path)	`database=mydb`
`host`	String	Alternative host specification (supports Unix sockets)	`host=/var/run/postgresql`, `host=host1:5433`
`password`	String	Password (overrides URL userspec)	`password=secret`
`port`	Integer	Port number (overrides URL port)	`port=5433`
`user` / `username`	String	Username (overrides URL userspec)	`user=myuser`
`sslmode`	String	SSL mode	`disable`, `require`, `verify-ca`, `verify-full`
`sslcert`	String	Path to client certificate	`sslcert=/path/to/cert.pem`
`sslkey`	String	Path to client private key	`sslkey=/path/to/key.pem`
`sslrootcert`	String	Path to root certificate	`sslrootcert=/path/to/ca.pem`
`replication`	String	Replication mode	`database` (logical), `true`/`physical`, `false`/`no_select` (none)
`query_timeout`	Integer	Query timeout in seconds	`query_timeout=300`

Pool-Specific Parameters

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

Parameter	Type	Description	Example Values
`max_connection_count`	Integer	Maximum number of concurrent connections	`max_connection_count=20`
`max_connection_age`	Integer	Maximum connection lifetime in seconds	`max_connection_age=3600`
`max_session_use`	Integer	Maximum session duration in seconds	`max_session_use=600`
`max_query_count`	Integer	Maximum queries per connection	`max_query_count=1000`

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.