Postgresql in Dart

So I was curious, about how difficult it was talking to a PostgreSQL server from Dart. so I wrote some code to find out. Before we get to the Dart code though, we’re going to need a database and some sample data.

	CREATE TABLE person(
	id SERIAL PRIMARY KEY,
	firstname varchar(80),
	lastname varchar(80),
	dateofbirth timestamp,
	height double precision
	)

view raw gistfile1.sql hosted with ❤ by GitHub

	INSERT INTO person
	(firstname, lastname, dateofbirth, height)
	VALUES
	('John', 'Doe', TIMESTAMP '1980-05-17', 1.92),
	('Jane', 'Doe', TIMESTAMP '1983-10-23', 1.87),
	('Baby', 'Doe', TIMESTAMP '2013-09-21', 0.43);

view raw gistfile1.sql hosted with ❤ by GitHub

Next up is the actual Dart code, which is pretty dang nice, if I may say so myself.  :-)

	import 'dart:async';
	import 'package:postgresql/postgresql.dart';
	void main() {
	var username = "TheRightMan";
	var password = "WithTheRightSecret";
	var DBname = "AtTheRightPlace";
	var uri = 'postgres://$username:$password@localhost:5432/$DBname';
	//Opens a connection.
	connect(uri).then((Connection connection) {
	//Insert a new person
	insertPerson(connection, "Thomas", "Pedersen", new DateTime(1990, 01, 1), 1.92).then((_) {
	//Print out the table.
	printEntireTable(connection).then((_) {
	connection.close();
	});
	});
	});
	}
	Future insertPerson(Connection connection,
	String firstname, String lastname,
	DateTime dateOfBirth, double height) {
	Completer _completer = new Completer();
	final String query =
	"INSERT INTO person (firstname, lastname, dateofbirth, height) VALUES" +
	"('$firstname', '$lastname', '$dateOfBirth', $height);";
	connection.execute(query).then((rowsAffected) {
	print("Rows Affected: $rowsAffected");
	_completer.complete();
	}).catchError((error) => _completer.completeError(error));
	return _completer.future;
	}
	Future printEntireTable(Connection connection) {
	Completer _completer = new Completer();
	final String query = "SELECT id, firstname, lastname, dateofbirth, height FROM person";
	connection.query(query).toList().then((rows) {
	for(var row in rows) {
	var age = ((new DateTime.now()).difference(row.dateofbirth).inDays/365.2425).floor();
	print("(${row.id}) ${row.firstname} ${row.lastname} - $age years old - ${row.height}m");
	}
	_completer.complete();
	}).catchError((error) => _completer.completeError(error));
	return _completer.future;
	}

view raw gistfile1.dart hosted with ❤ by GitHub

We start out by importing the “postgresql” pub package, then we connect to the database, insert a new record, and finally we display the data from the database.

I found it surprisingly easy to get going. It have this great thing where you can just type the name of column you want to access, and you get the data out as the right data type.

One thing to note is that it only works as a command line program, not from the browser, which shouldn't be that much of a surprise to anybody.