digitalmars.D.announce - sqlite support added to sqlbuilder

Steven Schveighoffer (94/94) Dec 30 2023 https://code.dlang.org/packages/sqlbuilder

Leonardo (3/5) Jan 04 How SQL injection are avoided here?

Steven Schveighoffer (16/21) Jan 04 SQL injection is avoided by passing parameter data. You use the

zoujiaqing (3/97) Jan 21 Thank you! I want postgresql :)

Steven Schveighoffer (5/6) Jan 21 It should be pretty straightforward. I had to rewrite a lot of it

PETER HILL (6/6) Aug 28 Hello steve,

Steven Schveighoffer (4/9) Aug 29 I don't have other examples than what is in the code itself.

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

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

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

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

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

PETER HILL <peter missionkey.com> writes:

Hello steve,
I am just starting in D.
Do you have more examples on using sqlite from sqlbuilder ?
Looks like it is what I need.
Thanks.
Peter

Aug 28

Steven Schveighoffer <schveiguy gmail.com> writes:

On Wednesday, 28 August 2024 at 23:15:03 UTC, PETER HILL wrote:
 Hello steve,
 I am just starting in D.
 Do you have more examples on using sqlite from sqlbuilder ?
 Looks like it is what I need.
 Thanks.

I don't have other examples than what is in the code itself. 
There are a few tests in there, you can take a look.

-Steve

Aug 29

D Programming

C/C++ Programming

Other

digitalmars.D.announce - sqlite support added to sqlbuilder