isoos/postgresql-dart

PostgreSQL client

CI

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.