sqlite support added to sqlbuilder

Steven Schveighoffer schveiguy at gmail.com
Sat Dec 30 22:11:55 UTC 2023


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):

```d
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


More information about the Digitalmars-d-announce mailing list