dpq 0.6.2

A postgresql (libpq, pq) library aiming to be simple to use


To use this package, put the following dependency into your project's dependencies section:

dub.json
dub.sdl

dpq - A D PostgreSQL library

dpq wraps the libpq library and aims to provide a simple and modern way to access PostgreSQL in the D programming language.

Current features:

  • Opening a connection using a connection string
  • Queries
  • Fetching the results of queries in binary format
  • Looping through the said results
  • Exceptions on query errors
  • Automatic (de-)serialisation of structs/classes
  • Automatic schema creation from struct/classes (includes PKs, FKs, indexes)
  • Reading and writing to array fields (up to 6-dimensions, limited by PostgreSQL)
  • Basic asynchronous query support with Query.runAsync()
  • Prepared statements
  • Support for SysTime type, saving time in the DB in UTC.

Planned features

  • Connection pooling (maybe)
  • Date/Time types support
  • Extensive documentantion

Documentation

Documentation is in the code itself, though not complete

Some notes:

  • If a wrong type is specified while fetching a column, the results are undefined. Most likely just garbage
  • Using QueryBuilder, when specifying columns, make sure they are not reserverd SQL keywords, they will not be escaped automatically (wrap keywords in " ")
  • Be careful with using Connection's exec function, since it only returns textual values, that are not currently supported by dpq's value. (execParams can be used even without params and will return binary data)

Licence

MIT, read LICENSE.txt

Example

import std.stdio;
import dpq.connection;
import dpq.query;
import dpq.attributes;
import dpq.result;

// By default, relation and type names will be snake_cased, resulting in a "user_data" type in this case
struct UserData
{
    // The same snake_casing rules apply to attribute names
	string firstName;
	string lastName;
}

// A relation's name can be specified with the @relation attribute, overriding the default
@relation("users")
struct User
{
	// serial is 4B in size, use serial8 with longs (@serial <=> @type("SERIAL"))
	@serial @PK int id;
	
	// @uniqueIndex will create an unique index, @index non-unique
	@uniqueIndex string username;
	@index int posts;

	// Structs inside structs can be used, they will be created as a type,
	// @embed must be used for structs that will be embedded
	@embed UserData userData;

	// ubyte[] will get stored as BYTEA
	// Attribute/column names can be specified using the @attribute UDA (@attr is an alias for it)
	@attr("password_hash") ubyte[] password;

	// Private properties will be ignored, same for @ignore
	private int _secret;
	
	// A getter-setter pair will get (de-)serialised too.
	@property int secret()
	{
		return _secret;
	}

	@property void secret(int newSecret)
	{
		_secret = newSecret;
	}
}

struct Post
{
	@serial @PK int id;
	// @FK will automatically find the referenced table's PK
	@FK!User int userId;
	string title;
	string content;
}

void main()
{
	// Establish a connection, will throw if connecting fails or connection string cannot be parsed
	auto conn = Connection("dbname=testdb user=testuser password='VerySecureTestPassword'");
	

	// One-line query execution, the same could be done with Connection.exec(string command)
	Query("CREATE TABLE IF NOT EXISTS test (id SERIAL, txt TEXT)").run();

	auto q = Query("INSERT INTO test (txt) VALUES ('Some text')");

	// A query can be run twice, if you wish to do so, inserting two rows in this case
	q.run();
	q.run();
	
	// Last connection will be used if none is specified as the first param to Query()
	q = Query("SELECT id, txt FROM test WHERE id = $1 OR id = $2");
	// Params could also be added with the << operator
	// q << 4 << 1;
	Result r = q.run(4, 1);

	writefln("Our query returned %d rows, each with %d columns", r.rows, r.columns);
	writefln("Additionaly, the query took %d ms to complete.", r.time.msecs);

	// Looping with foreach works as expected
	foreach (row; r)
		// Make sure you don't specify the incorrect type to as(),
		// the results are undefined, but mosty likely
		// you will either get garbage or a RangeError
		writeln("row[\"txt\"] is: ", row["txt"].as!string);

	// This will create a schema out of the two specified types
	// Indexes, primary keys, and foreign keys will also be created
	// as specified by the UDAs on struct/class members
	conn.ensureSchema!(User, Post);
	
	User newUser;
	newUser.username = "foobar123";
	newUser.userData.firstName = "Foo";
	newUser.userData.lastName = "Bar";

	// This will insert an the given user.
	conn.insert(newUser);

	// This will return a Nullable!User, searched for by the User's primary key
	// if no rows are returned, a Nullable null value will be returned
	auto user = conn.findOne!User(1);
	writeln("User: ", user);

	// Similar to above, but we specify what attribute we are filtering by
	auto user2 = conn.findOneBy!User("username", "foobar123");
	writeln("User 2:", user2);

	// The most powerful version of findOne -- allows you to specify a custom filter
	// Parameters can be given as with normal queries, beginning with $1.
	auto user3 = conn.findOne!User("id > $1 AND username LIKE 'a%'", 1);
	writeln("User 3: ", user3);

	// This will return an array of users with more than 100 posts,
	// if no rows are returned by the query, the array will have a length of 0
	User[] users = conn.find!User("posts > $1", 100);
	writeln("Users: ", users);
	

	// Connection does not have to be closed, the destructor will take care of that,
	// but it can still manually be closed using conn.close()
}

Authors:
  • Irenej
Dependencies:
none
Versions:
0.9.3 2018-Jan-10
0.9.1 2017-Mar-25
0.9.0-alpha.1 2016-Aug-11
0.8.9-beta.1 2016-Jul-24
0.8.8 2016-Jun-01
Show all 48 versions
Download Stats:
  • 0 downloads today

  • 1 downloads this week

  • 2 downloads this month

  • 719 downloads total

Score:
1.5
Short URL:
dpq.dub.pm