Postgresql in Dart v2

I got from my previous post, a lot of great feedback. so I have updated the code from that.

I use the same database, but as +Niklas Collin rightful said: "... those queries are vulnerable to SQL injection and are thus useless in any real code. Would have rather liked to see a prepared statement example...". So I have made a Prepared Statement and a Stored Procedure function to insert the data with.

	CREATE OR REPLACE FUNCTION
	InsertPerson(text, text, timestamp, double precision)
	RETURNS
	INTEGER
	AS
	$delimiter$
	INSERT INTO person(firstname, lastname, dateofbirth, height) VALUES
	($1, $2, $3, $4)
	RETURNING id
	$delimiter$
	LANGUAGE SQL;

view raw gistfile1.sql hosted with ❤ by GitHub

	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';
	connect(uri)
	.then((Connection connection) =>
	insertPersonStored(connection, "Thomas", "Pedersen", new DateTime(1988, 9, 23), 1.80))
	.then((Connection connection) =>
	insertPersonPrepared(connection, "Donald", "Duck", new DateTime(1934, 2, 13), 1.31))
	.then((connection) =>
	printEntireTable(connection))
	.then((connection) =>
	connection.close())
	.catchError((e) =>
	print("Error: $e"));
	}
	Future insertPersonStored(Connection connection, String firstname, String lastname, DateTime dateOfBirth, double height) {
	final String query = "SELECT insertperson('$firstname', '$lastname', '$dateOfBirth', $height);";
	return connection.query(query).listen((row) {
	print("Inserted: (${row[0]}) $firstname $lastname, $dateOfBirth, $height");
	}).asFuture(connection);
	}
	Future insertPersonPrepared(Connection connection, String firstname, String lastname, DateTime dateOfBirth, double height) {
	final String query =
	'insert into person(firstname, lastname, dateOfBirth, height)'+
	' values (@firstname, @lastname, @dateOfBirth, @height);';
	return connection.execute(query,
	{'firstname' : firstname,
	'lastname' : lastname,
	'dateOfBirth': dateOfBirth,
	'height' : height})
	.then((rowsAffected) {
	print("rowsAffected: $rowsAffected");
	return connection;
	}
	);
	}
	Future printEntireTable(Connection connection) {
	final String query = "SELECT id, firstname, lastname, dateofbirth, height FROM person;";
	return connection.query(query).listen((row) {
	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");
	}).asFuture(connection);
	}

view raw gistfile1.dart hosted with ❤ by GitHub

Now the code look a lot prettier thanks to the comment from +Justin Fagnani and +Seth Ladd about futures, and as far as I know, it shouldn't be vulnerable to SQL injection any more.

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.