pg_wrapper 0.3.1

A simple wrapper for executing queries using the postgresql package.

pg_wrapper

A simple wrapper for the postgresql package for dart. Several functions are provided to save time writing boilerplate code to query the database. This library does not aim to do anything amazing, but it should save small projects some time and worry about handling database connections properly.

Multiple Databases

DBCore provides a static method that initialises the pool connection to your database. If your environment requires multiple connections to different databases, then this is possible by adding multiple pools. By default the pool name is not required, but it can be specified to make referencing the alternative databases easier.

Setup

Add postgresql and pg_wrapper to your pubspec.yaml file, and run pub install.

name: postgresql_example
dependencies:
  postgresql: any
  pg_wrapper: any

To create a new instance that will automatically connect and execute queries on the database, you will need to initialise. This can be done using a Map:

import 'package:pg_wrapper/pg_wrapper.dart';
import 'package:postgresql/postgresql.dart';
main() {
  var map = new Map();
  map[Settings.HOST] = "host";
  map[Settings.PASSWORD] = "password";
  map[Settings.PORT] = 1234;
  map[Settings.USER] = "user";
  map[Settings.DATABASE] = "db";
  DBCore.addPool(new Settings.fromMap(map));
  DBCore.addPool(new Settings.fromMap(map), name: 'newDB');
}

Alternatively you can load the config from yaml:

import 'dart:io';
import 'package:pg_wrapper/pg_wrapper.dart';
import 'package:postgresql/postgresql.dart';
import 'package:yaml/yaml.dart';
main() {
  var map = loadYaml(new File('test_config.yaml').readAsStringSync()),
  DBCore.addPool(new Settings.fromMap(map));
}

DBCore provides a number of simple helper functions that can be used to access your database environment. Each of these methods optionally take in the pool name so that you can connect to different databases.

Querying

The main methods for this package are inside the Query class and this provides a number (or will provide a number) of ORM like convenience methods. To use them, you must first initialise an instance of a class, and then query it using one of the static methods. This style is similar to the Underscore library except it provides access to asyncronous connections (i.e. querying the database). The reason the design is like so is to keep model classes unpolluted of inherited methods. It is very important to note that this package should be used if you are going to follow the conventions it expects. This makes life easier for me and for you as your database design will be clean and easier to manage.

The following conventions should always be followed when using this package:

• Every model should have an id property
• Every related model should have the foreign key named after the following formular: 'model name' + id (e.g. foreign key for class Model would end up as modelid)

It is also recommended that you:

• Should create the id column as the serial datatype for automatic sequencing

N.B. If you have a data structure that does not follow the rules outlined above, then you can still use the DBCore and AutoMapper classes, but apart from that your experience will be limited.

class Model {
    int id;
    String name;
}

var M = new Model();
M.name = "foo";

// Automatically saves the model in table 'model' and populates the id property in the class
Query.save(M).then((_) {

    // Finds a record in the database from table 'model' where column 'id' equals the newly populated M.id property
    Query.where(M, M.id).then((_) => /* Do stuff here - _.id = 1, _.name = foo */);
    
});

Additionally, a primitive AutoMapper class is provided in this library to automatically populate the properties of the passed in Model against the result set of the query performed. In the future, this should handle relationship mapping rather than only single class instances.

class Model {
    int id;
    String name;
}

var M = new Model();
// Finds a record in the database from table 'model' where column 'id' equals 1
Query.where(M, 1).then((_) {
     AutoMapper.map(M, _);
     
     assert(M.id == 1);
     assert(M.name == 'foo');
});

Relations can also be queried:

class Post {
    int id;
    String name;
    List<Comment> comments;
}

class Comment {
    int id;
    String text;
}

var P = new Post();
var C = new Comment(); /* Table comment has column name 'postid' as a foreign key */

// Search for the first Post record where the joined Comment 'text' column is equal to 'bar'
Query.whereRelation(P, C, {'text': 'bar'}).then((_) {
    AutoMapper.map(P, _);
    
    assert(P.id == 1);
    assert(P.name == 'foo');
});

Testing

The following section has been copied directly from the postgresql repository. To run the unit tests you will need to create a database, and edit 'test/config.yaml' accordingly.

Creating a database for testing

Change to the postgres user and run the administration commands.

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

Check that it worked by logging in.

psql -h localhost -U testdb -W

Enter "\q" to quit from the psql console.

License :

The code is available under the MIT license.