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.
Feature highlights:
- Manual querying using
QueryorConnection'sexecandexecParams - Automatic schema generation from D structures (including PKs, indexes, ... check example below)
- ORM functions
findOne,findBy,insert,update,count, ... onConnection - Full support for
NullableandTypedeftypes. foreachlooping throughResultandRow- Data is sent fully in binary, including structures and arrays (and arrays of structures)
- Ability to specify a custom serialiser for any custom type
- Pre-existing serialisers cover most needs, and
SysTime - (Basic) async query support (will be improved in the future)
- (Basic) prepared statement support (will be improved in the future)
Documentation
Documentation is in the code itself, though pretty lacking currently.
Unit tests might provide some useful usage examples too.
Test
Before running tests configure the database (only once):
$ psql -U postgres < prepare_test_db.sqlSome notes:
- If a wrong type is specified to
Value'sas, the results are undefined, but most likely aRangeErroror 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'sexecfunction, since it only returns textual values, that are not currently supported by dpq'sValue. (execParams can be used even without params and will return binary data)
Licence
MIT, read LICENSE.txt
Example
import std.stdio;
import std.typecons : Nullable;
import std.datetime : SysTime;
import dpq.connection;
import dpq.value;
import dpq.attributes;
import dpq.query;
import dpq.result;
void main()
{
/*
Establish a new connection. Accepts a PostgreSQL connection string.
Will throw if the connection could not be established or if parsing
the connection string failed.
More: http://www.postgresql.org/docs/9.3/static/libpq-connect.html
*/
auto db = Connection("host=127.0.0.1 dbname=test user=test");
/*
Basic querying can be done via the Query object which allows you
two write manual queries.
The basic constructor accepts a Connection object, an optional SQL command.
Params are positional and begin with $1, any number of params can be sent.
*/
Query q = Query(db, "SELECT $1, $2, $3");
/*
Parameters to the query can be added with the addParam method, or
the `<<` operator, which can also be chained.
*/
q.addParam(1);
q << 2.0 << "three";
/*
Running the query is done with the querys' run() function, which
also accepts any number of params to be sent with the query.
You can run the same query multiple times if you wish.
*/
Result r = q.run();
/*
Params can also be added to the query later, or when when running it.
*/
q = Query(db, "SELECT $1, $2 AS second_param, 'a literal string'");
r = q.run("this is a string param", 777);
/*
Accessing the results if done via Result's index operator which will
return a Row object.
Alternatively, you can also loop through all the Result's rows with a
foreach loop.
Row, is however not publicly-visible, so use the auto declarator.
*/
auto firstRow = r[0];
foreach (row; r)
{
/*
You can access the actual column's values by using Row's opIndex.
The opIndex will accept either a textual index, looking up the column
by its name, or the column's numerical index, starting with a zero.
A Value object will be returned by this.
This value, can hold a NULL value, or the actual data. Checking for NULL
can be done with its isNull property.
*/
Value strVal = row[0];
/*
The value stores PostgreSQL compatible data and can be passed back
to most dpq's functions directly.
To obtain a value usable in D, you must call the Value's as method, which
accepts one template param, indicating the type to return. The returned
type will ALWAYS be an instance of Nullable, meaning you must check for
the NULL value unless you're sure it cannot happen.
*/
string stringParam = strVal.as!string;
/*
In actual code, you will probably want to shorten this and use
textual indexes for columns.
*/
int secondParam = row["second_param"].as!int; // fancy stuff
/*
Using a line like above, will implicitly convert the Nullable!int
returned by .as!int to just an int, causing an AssertError if
the value was null.
Because of that, is is usually recommended to do stuff this way.
*/
auto literalString = row[2].as!string;
/*
And now the returned value can be checked for null with Nullable's
isNull method.
*/
if (literalString.isNull)
continue;
}
/*
Since @relation is not specified here, user_profile_data will be used.
*/
struct UserProfileData
{
string fullName;
/*
Nullable values are supported at any level of nesting and work as expected.
*/
Nullable!string favouriteQuote;
}
/*
That's it as far as the basic Querying goes, but that's not where the
real power of dpq comes from.
dpq allows you to generate DB schema from existing D structures, even
allowing you to create primary keys, foreign keys, indexes and more.
Once generated, you can easily fetch whole objects from the database,
update existing records and insert or remove new ones.
*/
/*
Specifying the relation name can be done with the @relation attribute.
If no name is specified, though you generally probably should do that,
the structure's name will be lower_snake_cased and used.
Both structs and classes are supported.
*/
@relation("users")
struct User
{
/*
The column/attribute's special properties can be specified with the
UDAs from dpq.attributes.
In this case, @serial sets the type to SERIAL, and @PK will ensure
that the id column will be a Primary key in the column.
In reality, @PK is an alias for @PrimaryKey, another available alias
is @PKey. Use whichever one you prefer and deem more understandable.
*/
@serial @PK int id;
/*
Setting an index on the column is as simple as specifying @index for
the member. @uniqueIndex is also provided, creating a, you guessed it,
unique index.
*/
@index int posts;
@uniqueIndex string username;
/*
Arrays are supported, and are sent as PostgreSQL arrays.
ubyte[] and byte[] are a special case of arrays, and will be created
and sent as BYTEA.
*/
ubyte[] passwordHash;
/*
Since D prefers camelCased variable names, but it seems to be the standard
in database design to use lower_snake_cased names, dpq takes care of that
automatically. All variable names will be converted to lower_snake_case,
making, for example, the passwordHash above into password_hash.
You can, however, specify a custom name for any column using the
@attribute("column_name") property, or its alias @attr.
Names specifies with the @attr UDA, will not be lower_snake_cased and will
be used exactly as entered.
*/
@attr("password_salt") ubyte[] salt;
/*
Other structures can be embedded into any relation or type.
These structures will either be created as a custom type in the database,
or if applicable, relations.
*/
UserProfileData profileData;
/*
Both arrays of built-in types and custom structures are supported, but
sadly, I am running out of ideas for user fields.
Arrays can only have up to 6 dimensions, which is a limitation of
PostgreSQL, and can-not be jagged. Arrays of Nullable values
are supported, but not, however, arrays of Nullable arrays. This is
again, a limitation of PostgreSQL, not dpq.
*/
string[] favouriteFoods;
/*
Another thing supported by dpq out-of-the-box is SysTime, which will
get stored in the DB as TIMESTAMP in UTC.
Keep in mind, that PostgreSQL only supportes up to millisecond
accuracy, while D supports it up to hnsecs.
*/
SysTime registrationTime;
}
@relation("posts")
struct Post
{
/*
When wanting to work with BIGINT serial values, use @serial8, and
make sure to use long as the member's type.
*/
@serial8 @PK long id;
/*
To specify a foreign key, use the @ForeignKey attribute or one of its
alises (@FK, @FKey).
This attribute will find the referenced table's primary key automaticaly
and use that.
Additionally, alongside the FK constraint on a FK column, an index
will also be created for it, since that's generally good practice.
*/
@FK!User int userId;
}
/*
Creating the schema is as simple as calling the Connection's ensureSchema
method. This method must be called every time, even if the schema already
exists, to ensure dpq is aware of the created relations and type's OIDs.
Without that, they cannot be properly serialised and sent to the DB.
ensureSchema must be given all the structures that should be created as
relations. If any of them have embedded structures inside them, those
will automatically be created as types.
Foreign keys will be created after all the relations, so the order
you specify the structures is does not matter.
*/
db.ensureSchema!(User, Post);
/*
After ensureSchema is called, we can finally get to real work.
Fetching a single object from the database is done by calling the
Connection's findOne method.
The method accepts a single template parameter, indicating the structure
to fetch, and a normal parameter, indicating the value of the FK to filter
by.
findOne will return a Nullable instance of the requested type. A NULL
value in this case means no rows were selected by the query.
Once again, in case you forgot by now, compound PKs are, unfortunately,
not supported.
*/
auto user = db.findOne!User(1);
if (!user.isNull)
writeln("We have a user."); // whatever logic
/*
There's also a special version of findOne, allowing you to specify a
custom filter for the query.
It accepts a string as the first parameter, and any number of values
to be used in the query.
The result it limited to 1 row and NULL will be returned in the same
case as above.
*/
auto otherUser = db.findOne!User("username = $1", "Person9720");
/*
In cases such as above, findOneBy could also be used.
findOneBy accepts a parameter for the column name, and the value for it
to filter by.
The query below will be the same as above.
*/
otherUser = db.findOneBy!User("username", "Person9720");
/*
Fetching more than one object from the DB can be done with the
Connection's find method, accepting a filter string, and any number
of values that are to be used.
This method will return an array, which will be empty if no records
matching the filter were found.
*/
User[] manyUsers = db.find!User("posts > $1", 250);
/*
Inserting records is just as simple, using the Connection's insert method.
When records are being inserted, the primary key will not be sent alongside
them, allowing you to use PostgreSQL's SERIAL type's default values.
*/
User newUser;
newUser.username = "Person8164";
db.insert(newUser);
/*
Records can be deleted using the Connection's remove method, again
providing both a filter-string version and a find-by-PK version.
*/
db.remove!User(1);
db.remove!User("posts <= $1", 25);
/*
Last, but definitely not least, there's updating records.
There are three versions of the Connection's update method,
the first, accepting the value of the PK to filter by, and a
structure of the same type you're updating and updates ALL the values
of the matched record.
A template parameter is not needed, since it can be inferred from
the second parameter, but you can provide it if you wish to be
explicit.
All update methods return the number of updated records
*/
newUser.posts = 2;
int nUpdated = db.update(1, newUser);
/*
The second version accepts the value of the PK to filter by, and
an associative array of strings mapping to Value objects.
It sets the columns of the provided to the values they map to.
Keep in mind that there is no way to set values relatively to their
current value using this way, which might cause data races in concurrent
applications.
*/
nUpdated = db.update!User(1, [
"posts": Value(3),
"username": Value("New Person 9720")
]);
/*
The last version of update, takes a filter string, and an update string,
as well as any number of values to be sent with the query.
This version gives you the most freedom and allows you to set the values
relatively.
*/
nUpdated = db.update!User(
"posts > $1", // filter
"posts = 0, username = $2", //update
250, "Spammer batch #927"); // values
/*
dpq also provides async versions for most of the methods mentioned above,
for which the results can be obtained by using Connection's
lastResult, allResults and nextResult method.
More info is available in the Connection's and Query's files inline docs.
*/
db.removeAsync!User(1);
}A word from the author
I believe this above example covers most use cases for dpq.
If you have any questions, I can be contacted on Twitter with the handle @IrenejMarc. I'll always be glad to help out.
If you have any ideas how dpq could be improved, or find a missing feature or a bug, don't be scared to create an issue, I love feedback.