www.digitalmars.com         C & C++   DMDScript  

digitalmars.D.announce - sqlite support added to sqlbuilder

reply Steven Schveighoffer <schveiguy gmail.com> writes:
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
Dec 30 2023
next sibling parent reply Leonardo <leotada523 gmail.com> writes:
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?
Jan 04
parent Steven Schveighoffer <schveiguy gmail.com> writes:
On Thursday, 4 January 2024 at 18:03:56 UTC, Leonardo wrote:

 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?


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

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

```d
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
Jan 04
prev sibling parent reply zoujiaqing <zoujiaqing gmail.com> writes:
On Saturday, 30 December 2023 at 22:11:55 UTC, Steven 
Schveighoffer wrote:

 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


Thank you! I want postgresql :)
Jan 21
parent Steven Schveighoffer <schveiguy gmail.com> writes:
On Sunday, 21 January 2024 at 21:27:30 UTC, zoujiaqing wrote:


 Thank you! I want postgresql :)


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
Jan 21