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

On Saturday, 30 December 2023 at 22:11:55 UTC, Steven Schveighoffer wrote:

auto andrei = db.fetchOne(select(ads).where(ads.firstname, " = 'Andrei'"));

How SQL injection are avoided here?

On Thursday, 4 January 2024 at 18:03:56 UTC, Leonardo wrote:

auto andrei = db.fetchOne(select(ads).where(ads.firstname, " = 'Andrei'"));

SQL injection is avoided by passing parameter data. You use the param wrapper.

So if you had unqualified user input, it would be:

string personname = getFromUser();
auto author = db.fetchOne(select(ads).where(ads.firstname, " = ", personname.param);

For everything except strings, it is a static error to pass them in without the .param wrapper. For strings, I can't help it, there is no mechanism to find out whether you are writing SQL or giving me a parameter.

This should be fixable if interpolation ever happens (and I can get rid of the requirement for .param).

-Steve

On Saturday, 30 December 2023 at 22:11:55 UTC, Steven Schveighoffer wrote:

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);
}

Thank you! I want postgresql :)

On Sunday, 21 January 2024 at 21:27:30 UTC, zoujiaqing wrote:

It should be pretty straightforward. I had to rewrite a lot of it to fit the API of sqlite, those changes should make it easier to add postgresql (which is on my todo list).

-Steve