Download database.d and a driver file (postgres.d for PostgreSQL, mysql.d for MySQL, sqlite.d for SQLite, or mssql.d for SQL Server) from my Github.

The drivers also require C libraries. The mysql.d file needs libmysqlclient, which comes with the MySQL server. The postgres.d file needs libpq, which comes with the PostgreSQL server. The sqlite.d file needs SQLite installed on the system.

Let's also create a test database to use from the program. In our example, we'll use MySQL, so create a MySQL database, user, and data table. The following are the commands you can run in the MySQL console:

create database demonstration;
use demonstration
grant all privileges on demonstration.* to 'demo_user'@'localhost' identified by 'test';
create table info (id integer auto_increment, name varchar(80), age integer null, primary key(id)) CHARSET=utf8;

Let's access a SQL database by executing the following steps:

The code is as follows:

import arsd.mysql;
void main() {
  auto db = new MySql("localhost", "demo_user","test", "demonstration");
  // Add some data to the table
  db.query("INSERT INTO info (name) VALUES (?)", "My Name");
  db.query("INSERT INTO info (name, age) VALUES (?, ?)","Other", 5);

  // get the data back out
  foreach(line; db.query("SELECT id, name, age FROM info")) {
    import std.stdio;
    // notice indexing by string or number together
    writefln("ID %s %s (Age %s)", line["id"],line[1], line["age"]);
  }
}

Running the program will add data to the table and print the following text. Note how the first ID's age is an empty string; this is how null is represented in the result line. The following is the output:

ID 1 My Name (Age )

ID 2 Other (Age 5)

If the program throws an exception, it may be because the database was not properly created.

These files wrap the C APIs from the database vendors to provide minimal, but functional, access to the database servers through an abstracted interface. The database.d file will not attempt to transform SQL syntax differences across database vendors, but it will present a uniform interface to issue those queries.

The implementation of Database.query is broken up into two parts: first, the public query method, which is a variadic template that can take any number and mix of types of arguments; and second, the queryImpl method, which is a virtual function implemented by each driver. The split was necessary because templates cannot be virtual and fully generic and convenience functions to accept a variety of data types must be templates.

The public query method loops over its arguments and converts them to an array of std.variant.Variant, which is then passed to queryImpl for the database engine to use in parameterized queries. Then, each placeholder in your query string—represented by ? or ?n, the former pulling the next argument and the latter pulling a specific numbered argument—are replaced by the value of the next argument.

The query method returns a ResultSet interface, which is an input range of Rows. ResultSet is also an interface, with different implementations for each database engine. As an input range, it can be checked for data with the empty property, the current row retrieved with the front property, and the next row retrieved with the popFront method. It also works with the foreach statement for easily iterating over all the results. It could, in theory, also be transformed with std.algorithm like any other input range. However, in practice, you should do filtering and sorting in the database itself with your SQL query.

The Row object is a struct consisting of an array of strings and a column name mapping. Thanks to operator overloading, the column values are available through both column name strings and column index integers, similar to how PHP's old mysql_fetch_array function works.

Each column is represented by strings primarily for ease of database engine implementation and to keep the interface simple. As strings, all regular string operations can be performed, including checking for null or emptiness with the length property or converting them to other types with std.conv.to.

When you are finished with the data and database connection, you may let the garbage collector clean them up or you may explicitly destroy them with the global destroy method, which will immediately close the connection.

The database.d file also includes two helper objects for building queries: DataObject, which provides a simple implementation of the active record pattern, and SelectBuilder, which helps you build a SELECT query by putting together individual components.

The implementation of SelectBuilder is very simple: it is simply a collection of string arrays and member variables for LIMIT clauses. Its toString method concatenates the arrays into the proper order to build a working query. The purpose of SelectBuilder is to make modifying, reusing, and creating queries easier than trying to reparse and splice a SQL string.

The code is as follows:

import arsd.database;

void main() {
  auto query = new SelectBuilder();
  query.table = "users";
  query.fields ~= "id";
  query.fields ~= "name";
  query.limit = 5;
  query.wheres ~= "id > 5";
  query.orderBys ~= "name ASC";

  import std.stdio;
  writeln(query.toString());
}

That program will print out the following constructed query that could be passed to Database.query:

SELECT id, name FROM users WHERE (id > 5) ORDER BY name ASC LIMIT 5

DataObject uses opDispatch properties to get and set data for a query. The getter property returns a string which can be converted to other types with std.conv.to. The setter property is a template to take any kind of argument and convert it to strings for use in queries automatically. DataObject, like many ORM solutions, may be subject to race conditions on the database and is not appropriate for all situations.

The database.d file also includes experimental code generation to create a strongly-typed subclass of DataObject based on a SQL CREATE TABLE statement. It takes a SQL string and parses it (using several parsing shortcuts) to extract column names and types. Then, it converts them into D properties and mixins as a generated string of D code, like we did in the Duck typing to a statically-typed interface recipe, in Chapter 9, Code Generation, to implement the subclass. However, since the parser is tailored to the particular way I wrote those statements at the time, it may not work for you.

Lastly, it also has code that uses compile-time reflection on structs to fill them in with a query. As I find the strings to be generally sufficient for me though, I rarely use this code and, as a result, it is not maintained.