Permalink
Reply
https://code.dlang.org/packages/sqlbuilder
This project is something I use extensively in my work project, and allows dynamic building of SQL statements in a way that automatically deals with joins.
It also automatically serializes directly to structs representing database rows. It was featured in my dconf 2022 online talk -- Model all the things.
I just added support to use sqlite. The API isn't stable yet, but still super useful. It's one of those build-it-as-I-need-it things, so while there's a semblance of a plan, things that are finished are things that I've needed.
An example (with sqlite):
import d2sqlite3;
import std.stdio;
import std.file : exists;
import std.array;
// yeah, I know, I need a package include here...
import sqlbuilder.uda;
import sqlbuilder.dataset;
import sqlbuilder.dialect.sqlite;
import sqlbuilder.types;
import d2sqlite3;
struct Author
{
@primaryKey @autoIncrement int id;
string firstname;
string lastname;
static @refersTo!Book @mapping("author_id") Relation books;
}
struct Book
{
@primaryKey @autoIncrement int id;
string title;
string description;
@mustReferTo!Author("author") int author_id;
}
void main()
{
auto shouldInitialize = !exists("books.sqlite");
auto db = Database("books.sqlite");
if(shouldInitialize)
{
// create the tables
db.execute(createTableSql!Author);
db.execute(createTableSql!Book);
// add some books and authors
Author walter = Author(
firstname: "Walter",
lastname: "Bright");
db.create(walter); // automatic serialization to sql insertion statement
Author andrei = Author(
firstname: "Andrei",
lastname: "Alexandrescu");
db.create(andrei);
db.create(Book(
title: "The D Programming Language",
description: "The OG D manual",
author_id: andrei.id));
db.create(Book(
title: "Modern C++ Design",
description: "The OG C++ template manual",
author_id: andrei.id));
db.create(Book(
title: "The D specification",
description: "The Spec of the D programming language",
author_id: walter.id));
}
// get an author by name
DataSet!Author ads;
auto andrei = db.fetchOne(select(ads).where(ads.firstname, " = 'Andrei'"));
// do some selections based on the dataset of books
DataSet!Book bds;
foreach(booktitle, author; db.fetch(select(bds.title, bds.author)))
{
writefln("Found book %s, written by %s %s",
booktitle, author.firstname, author.lastname);
}
auto andreiBooks = db.fetch(select(bds)
.where(bds.author_id, " = ", andrei.id.param)).array;
writeln("Andrei's books: ", andreiBooks);
}
Code is very similar for using mysql as well, just import mysql-native and sqlbuilder.dialect.mysql.
Next up would be postgresql, not sure when I'll have a need to build that...
-Steve
