oceandrift.db.dbal.v4

SQL Query Builder

Construct SQL queries from code. Compile them to any specific SQL dialect later.

Works at compile-time thanks to CTFE. Best used with UFCS.

table("person").qb
    .where("age", '>')
    .select("*")
;
// is equivalent to writing this query by hand
// --> SELECT * FROM "person" WHERE "age" > ?

table creates a struct instance representing a table; this is the base for query building.

qb instantiates a complex query builder (for SELECT, UPDATE or DELETE queries – those that can have WHERE clauses etc.)

where appends a new condition to a query’s WHERE clause. For disjunctions (“OR”) the first template parameter is set to or (i.e. where!or(…)`) whereas where!and is the default.

Parentheses () can be inserted by calling whereParentheses:

table("person").qb
    .whereParentheses(q => q
        .where("age", '>')
        .where!or("height", '>')
    )
    .where("gender", '=')
    .select("*")
;
// --> SELECT * FROM "person" WHERE ( "age" > ? OR "height" > ? ) AND "gender" = ?

Applying aggregate functions is super simple as well: Helper functions avg, count, max, min, sum and group_concat got you covered!

DISTINCT can be applied by setting the first (and only) template parameter of said functions to distinct (or true), i.e. SELECT COUNT(DISTINCT "name") becomes .select(count!distinct("name")).

table("person").qb              // FROM "person"
    .where("age", '>')          // WHERE "age" > ?
    .select(                    // SELECT
        count!distinct("name")  //      COUNT(DISTINCT "name")
    )
;

Selecting multiple columns? No big deal either:

table("person").qb.select(
    "name",
    "age"
);
// --> SELECT "name", "age" FROM "person"

Writing .select(*) is tedious? How about omitting the parameter? "*" is the default:`

table("person").qb.select();
// --> SELECT * FROM "person"

Pre-set values during query building. Those values are not inserted into the query string (Regular dynamic parameters are used as well.) but will be bound to the prepared statement later.

table("person").qb
    .where("age", '>', 18)
    .select("*")
// --> SELECT * FROM person WHERE "age" > ?

Talking about “later”, it’s time to build an actual query from those abstractations… This is done by calling build on the Query. The target dialect is specified through a template parameter (oceandrift.db.sqlite3.SQLite3 in this example).

BuiltQuery q = table("person").qb
    .where("age", '>')
    .select("*")
    .build!SQLite3()
;
writeln(q.sql); // prints: SELECT * FROM "person" WHERE "age" > ?

Multiple conditions can be chained by calling where multiple times:

Query q = table("person").qb
    .where("age", '>')
    .where("height", '>')
;
// --> FROM "person" WHERE "age" > ? AND "height" > ?

Note that “conjunction” (AND) is the default. To create a “disjunction” (OR) set the first template parameter of where to or

Query q = table("person").qb
    .where   ("age", '>')
    .where!or("height", '>')
;
// --> FROM "person" WHERE "age" > ? OR "height" > ?

or is shorthand for LogicalOperator.or.

Of course there is and (aka LogicalOperator.and), too.

Query q = table("person").qb
    .where    ("age", '>')
    .where!and("height", '>')
;
// --> FROM "person" WHERE "age" > ? AND "height" > ?

This results in the same query as not specifing the logical juntion in the first place.

The primary goal of this query builder implementation is to power an ORM. Nevertheless, it’s designed to be used by actual human programmers.

It doesn’t support every possible SQL feature. The goal is to support the subset needed for ORM purposes.

I’d rather have limitations that affect versatility than to give up usability. Feedback is always welcome!

Codename: v4 (“version 4”)

Special thanks to Steven “schveiguy” Schveighoffer.

Members

Aliases

BuiltQuery alias BuiltQuery = const(_BuiltQuery): Built query as generated by a QueryBuilder
PlaceholdersMeta alias PlaceholdersMeta = const(_PlaceholdersMeta)
PreSets alias PreSets = const(_PreSets)
col alias col = column: Undocumented in source.
qb alias qb = complexQueryBuilder

Enums

AggregateFunction enum AggregateFunction: SQL aggregate function types
ComparisonOperator enum ComparisonOperator
Distinct enum Distinct: SQL DISTINCT keyword abstraction
LogicalOperator enum LogicalOperator: Logical operators
OrderingSequence enum OrderingSequence: Ascending vs. Descending
asc anonymousenum asc: Undocumented in source.
inner anonymousenum inner: Undocumented in source.
or anonymousenum or: Undocumented in source.

Functions

avg SelectExpression avg(string column): Short-hand helper function for SELECT AVG(…)
build BuiltQuery build(Update q)
BuiltQuery build(Insert q): Builds the passed query through the provided QueryCompiler (UFCS helper function)
build BuiltQuery build(Select q)
BuiltQuery build(Delete q): Builds the passed query through the provided QueryCompiler (UFCS helper function)
column inout(Column) column(string name): Undocumented in source. Be warned that the author may not have intended to support it.
column inout(Column) column(string name, Table table): Undocumented in source. Be warned that the author may not have intended to support it.
column inout(Column) column(Table table, string name): Undocumented in source. Be warned that the author may not have intended to support it.
complexQueryBuilder Query complexQueryBuilder(Table table)
Query complexQueryBuilder(string table)
count SelectExpression count(string column): Short-hand helper function for SELECT COUNT(…)
delete_ Delete delete_(Query query): Creates an abstracted DELETE query from the specified query
group_concat SelectExpression group_concat(string column): Short-hand helper function for SELECT GROUP_CONCAT(…)
insert Insert insert(Table table, const(string)[] columns)
Insert insert(Table table, Columns columns): Creates an abstracted INSERT query for inserting row(s) into the specified table filling the passed columns
join Query join(Query q, Column joinTarget, Column sourceColumn)
Query join(Query q, Column joinTarget, string sourceColumn)
Query join(Query q, Table joinTarget, string joinOnTargetColumn, string onSourceColumn): Appends a JOIN statement to a query
limit Query limit(Query q)
Query limit(Query q, ulong limit)
Query limit(Query q, ulong limit, ulong offset): Sets or updates the LIMIT clause of a query
max SelectExpression max(string column): Short-hand helper function for SELECT MAX(…)
min SelectExpression min(string column): Short-hand helper function for SELECT MIN(…)
orderBy Query orderBy(Query q, Column column, OrderingSequence orderingSequence): Appends an ORDER BY clause to a query
orderBy Query orderBy(Query q, string column, OrderingSequence orderingSequence): Undocumented in source. Be warned that the author may not have intended to support it.
prepare Statement prepare(BuiltQuery builtQuery, DatabaseDriver db): Prepares a built query using the specified database connection
prepareBuiltQuery Statement prepareBuiltQuery(DatabaseDriver db, BuiltQuery builtQuery): Prepares a built query using the specified database connection
select Select select(Query from, ColumnV columns): Creates an abstracted SELECT query selecting the specified columns.
sum SelectExpression sum(string column): Short-hand helper function for SELECT SUM(…)
table inout(Table) table(string name): Convenience function to create a table instance
times Insert times(Insert insert, uint rows): Specifies how many rows to INSERT a once
update Update update(Query query, const(string)[] columns)
Update update(Query query, Columns columns): Creates an abstracted UPDATE query for updating the specified columns
updatePreSetWhereValue void updatePreSetWhereValue(Query q, int index, DBValue value): Undocumented in source. Be warned that the author may not have intended to support it.
where Query where(Query q, Column column, TComparisonOperator op)
Query where(Query q, string column, TComparisonOperator op)
Query where(Query q, Column column, TComparisonOperator op, T value): Appends a condition to the query's WHERE clause
where Query where(Query q, string column, TComparisonOperator op, T value): Undocumented in source. Be warned that the author may not have intended to support it.
whereParentheses Query whereParentheses(Query q, Query delegate(Query q) @(safe) pure conditions): Appends checks in parentheses to the query's WHERE clause

Manifest constants

not enum not;: NOT (SQL)

Structs

Column struct Column: Column identifier
CompilerQuery struct CompilerQuery: Special query representation for use in query compilers
Delete struct Delete: DELETE query abstraction
Insert struct Insert: INSERT query abstraction
Join struct Join: JOIN clause abstraction
Limit struct Limit: Undocumented in source.
OrderingTerm struct OrderingTerm: Undocumented in source.
Query struct Query: SQL SELECT/UPDATE/DELETE query abstraction
Select struct Select: SELECT Query abstraction
SelectExpression struct SelectExpression: SELECT expression abstraction
Table struct Table: Database Table Representation
Token struct Token: Abstract SQL query token
Update struct Update: UPDATE query abstraction
Where struct Where: Abstract WHERE clause

Templates

whereNot template whereNot(bool logicalJunction = or, TComparisonOperator)

Variables

distinct enum Distinct distinct;: DISTINCT i.e. no duplicates
isComparisonOperator enum bool isComparisonOperator(T);: Undocumented in source.
isQueryCompiler enum bool isQueryCompiler(T);: Determines whether T is a valid SQL “Query Compiler” implementation