gem-hunter

PostgreSQL database driver for Dart

Basic usage

Obtaining a connection

var uri = 'postgres://username:password@localhost:5432/database';
connect(uri).then((conn) {
	// ...
});

SSL connections

var uri = 'postgres://username:password@localhost:5432/database?sslmode=require';
connect(uri).then((conn) {
	// ...
});

Querying

conn.query('select color from crayons').toList().then((rows) {
	for (var row in rows) {
		print(row.color); // Refer to columns by name,
		print(row[0]);    // Or by column index.
	}
});

Executing

conn.execute("update crayons set color = 'pink'").then((rowsAffected) {
	print(rowsAffected);
});

Query Parameters

conn.query('select color from crayons where id = @id', {'id': 5})
  .toList()
	.then((result) { print(result); });

conn.execute('insert into crayons values (@id, @color)',
             {'id': 1, 'color': 'pink'})
	.then((_) { print('done.'); });

Closing the connection

Conversion of Postgresql datatypes.

     Postgresql type                 Dart type
	boolean                         bool
	int2, int4, int8                int
	float4, float8                  double
	numeric                         String
	timestamp, timestamptz, date    Datetime
	json, jsonb                     Map/List
	All other types                 String

Mapping the results of a query to an object

class Crayon {
	String color;
	int length;
}

conn.query('select color, length from crayons')
	.map((row) => new Crayon()
	                     ..color = row.color
	                     ..length = row.length)
	.toList()
	.then((List<Crayon> crayons) {
		for (var c in crayons) {
			print(c is Crayon);
			print(c.color);
			print(c.length);
		}
	});

class ImmutableCrayon {
	ImmutableCrayon(this.color, this.length);
	final String color;
	final int length;
}

conn.query('select color, length from crayons')
  .map((row) => new ImmutableCrayon(row.color, row.length))
     .toList()
	.then((List<ImmutableCrayon> crayons) {
		for (var c in crayons) {
			print(c is ImmutableCrayon);
			print(c.color);
			print(c.length);
		}
	});

Query queueing

conn.execute("create table crayons (color text, length int)");
conn.execute("insert into crayons values ('pink', 5)");
conn.query("select color from crayons").single.then((crayon) {
	print(crayon.color); // prints 'pink'
});

Query streaming

Connection pooling

import 'package:postgresql/pool.dart';

main() {
  var uri = 'postgres://username:password@localhost:5432/database';
  var pool = new Pool(uri, minConnections: 2, maxConnections: 5);
  pool.messages.listen(print);
  pool.start().then((_) {
    print('Min connections established.');
    pool.connect().then((conn) { // Obtain connection from pool
      conn.query("select 'oi';")
        .toList()
        .then(print)
        .then((_) => conn.close()) // Return connection to pool
        .catchError((err) => print('Query error: $err'));
    });
  });
}

Example program

name: postgresql_example
dependencies:
  postgresql: any

import 'package:postgresql/postgresql.dart';

void main() {
  var uri = 'postgres://testdb:password@localhost:5432/testdb';
  var sql = "select 'oi'"; 
  connect(uri).then((conn) {
    conn.query(sql).toList()
    	.then((result) {
    		print('result: $result');
    	})
    	.whenComplete(() {
    		conn.close();
    	});
  });
}

Testing

Creating a database for testing

sudo su postgres
createuser --pwprompt testdb
  Enter password for new role: password
  Enter it again: password
  Shall the new role be a superuser? (y/n) n
  Shall the new role be allowed to create databases? (y/n) n
  Shall the new role be allowed to create more new roles? (y/n) n
createdb --owner testdb testdb
exit

psql -h localhost -U testdb -W

License

Links