I think code style like:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

struct User
{
    int id;
    string name;
    string email;
}

class ORM
{
}

auto db = new ORM;
auto users = db.select(User).where(email.like("*@hotmail.com")).limit(10);

foreach(user; users)
{
    writeln("user: " + user.name + "\n");
}

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

this rust code is support it:
https://github.com/diesel-rs/diesel/blob/master/examples/postgres/all_about_updates/src/lib.rs

On Friday, 5 January 2018 at 07:40:14 UTC, Brian wrote:
> I think code style like:
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
> struct User
> {
>     int id;
>     string name;
>     string email;
> }
>
> class ORM
> {
> }
>
> auto db = new ORM;
> auto users = db.select(User).where(email.like("*@hotmail.com")).limit(10);
>
> foreach(user; users)
> {
>     writeln("user: " + user.name + "\n");
> }
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
> this rust code is support it:
> https://github.com/diesel-rs/diesel/blob/master/examples/postgres/all_about_updates/src/lib.rs

Well in D this would be more something like that:

auto users = db.select!(User).map!(a => a.email.like("*@hotmail.com")).take(10);

On Friday, 5 January 2018 at 08:34:00 UTC, user789 wrote:
> On Friday, 5 January 2018 at 07:40:14 UTC, Brian wrote:
>> I think code style like:
>> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>>
>> struct User
>> {
>>     int id;
>>     string name;
>>     string email;
>> }
>>
>> class ORM
>> {
>> }
>>
>> auto db = new ORM;
>> auto users = db.select(User).where(email.like("*@hotmail.com")).limit(10);
>>
>> foreach(user; users)
>> {
>>     writeln("user: " + user.name + "\n");
>> }
>>
>> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>>
>> this rust code is support it:
>> https://github.com/diesel-rs/diesel/blob/master/examples/postgres/all_about_updates/src/lib.rs
>
> Well in D this would be more something like that:
>
> auto users = db.select!(User).map!(a => a.email.like("*@hotmail.com")).take(10);

Oh, I realize that your Q was more: "which package should i use to write this way ?"

On Friday, 5 January 2018 at 07:40:14 UTC, Brian wrote:
> I think code style like:
> db.select(User).where(email.like("*@hotmail.com")).limit(10);

You need to read about templates in D, here's a good guide:
https://github.com/PhilippeSigaud/D-templates-tutorial

Basically you can write a function like
auto select(Class, string fieldName)(Class value) {
 ...

and call it later as
select!(User, "name")(u);

Here User and "name" are compile-time arguments, you can pass there types, values and more exotic stuff like other templates.

And inside the function you can use
__traits(getMember, u, fieldName)
to get field by its name from the passed value.
See: https://dlang.org/spec/traits.html

On Friday, 5 January 2018 at 12:19:11 UTC, thedeemon wrote:
> On Friday, 5 January 2018 at 07:40:14 UTC, Brian wrote:
>> I think code style like:
>> db.select(User).where(email.like("*@hotmail.com")).limit(10);
>
> You need to read about templates in D, here's a good guide:
> https://github.com/PhilippeSigaud/D-templates-tutorial
>
> Basically you can write a function like
> auto select(Class, string fieldName)(Class value) {
>  ...
>
> and call it later as
> select!(User, "name")(u);
>
> Here User and "name" are compile-time arguments, you can pass there types, values and more exotic stuff like other templates.
>
> And inside the function you can use
> __traits(getMember, u, fieldName)
> to get field by its name from the passed value.
> See: https://dlang.org/spec/traits.html

This was a really nice, short example btw. I'm not OP, but thanks for that.

On Friday, 5 January 2018 at 07:40:14 UTC, Brian wrote:
> I think code style like:
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
> struct User
> {
>     int id;
>     string name;
>     string email;
> }
>
> class ORM
> {
> }
>
> auto db = new ORM;
> auto users = db.select(User).where(email.like("*@hotmail.com")).limit(10);
>
> foreach(user; users)
> {
>     writeln("user: " + user.name + "\n");
> }
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
> this rust code is support it:
> https://github.com/diesel-rs/diesel/blob/master/examples/postgres/all_about_updates/src/lib.rs

there's entity library exist in D Dub, which is an ORM framework, you may read the code for reference:

https://code.dlang.org/packages/entity

On Friday, 5 January 2018 at 12:19:11 UTC, thedeemon wrote:
> On Friday, 5 January 2018 at 07:40:14 UTC, Brian wrote:
>> I think code style like:
>> db.select(User).where(email.like("*@hotmail.com")).limit(10);
>
> You need to read about templates in D, here's a good guide:
> https://github.com/PhilippeSigaud/D-templates-tutorial
>
> Basically you can write a function like
> auto select(Class, string fieldName)(Class value) {
>  ...
>
> and call it later as
> select!(User, "name")(u);
>
> Here User and "name" are compile-time arguments, you can pass there types, values and more exotic stuff like other templates.
>
> And inside the function you can use
> __traits(getMember, u, fieldName)
> to get field by its name from the passed value.
> See: https://dlang.org/spec/traits.html

I can try it?:

auto users = db.select!(User).where(User.email.like("*@hotmail.com")).limit(10);

On Friday, 5 January 2018 at 15:38:17 UTC, Binghoo Dang wrote:
> On Friday, 5 January 2018 at 07:40:14 UTC, Brian wrote:
>> I think code style like:
>> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>>
>> struct User
>> {
>>     int id;
>>     string name;
>>     string email;
>> }
>>
>> class ORM
>> {
>> }
>>
>> auto db = new ORM;
>> auto users = db.select(User).where(email.like("*@hotmail.com")).limit(10);
>>
>> foreach(user; users)
>> {
>>     writeln("user: " + user.name + "\n");
>> }
>>
>> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>>
>> this rust code is support it:
>> https://github.com/diesel-rs/diesel/blob/master/examples/postgres/all_about_updates/src/lib.rs
>
> there's entity library exist in D Dub, which is an ORM framework, you may read the code for reference:
>
> https://code.dlang.org/packages/entity

Yes, [entity] is my team's project, I want it to be simpler :)

On Friday, 5 January 2018 at 07:40:14 UTC, Brian wrote:
> auto db = new ORM;
> auto users = db.select(User).where(email.like("*@hotmail.com")).limit(10);

Expression templates are a dead-end for any non-trivial queries.
You have to embrace SQL to properly use RDMS, at the cost of beginners having to learn it.
There is no free lunch and the inefficient queries ppl. are running with Rails examplify what happens, when you pretend it's possible to use technology without understanding it.

I'm actively working towards a design that allows full compile-time typing with plain SQL commands. This will still take a while, but I hope we can see an alpha in 2018H1.
http://dconf.org/2016/talks/nowak.html

alias schema = AliasSeq!(User, MailAddress, Book, Lending);
DB!schema db = DB!schema(SQLite("local.db")); // connecting checks for missing/additional migrations
db = DB!schema(MySQL("localhost", 3306)); // DB wrapper is driver agnostic
// Full ANSI SQL
// driver-specific functions when picking the driver at compile time
// You can always exec dynamic queries on the underlying driver

foreach (r; db.exec!(q"SQL
    SELECT u.name, m.addr, CONCAT(b.title, ';') books
    FROM users u
    JOIN mail_addresses m ON m.user_id = u.id AND NOT u.can_overdraw
    JOIN lendings l ON l.user_id = u.id AND DATEDIFF(NOW(), l.end_date) >= 7
    JOIN books b ON b.lending_id = l.id
    WHERE b.amount < 20
    GROUP BY u.id
SQL
)
    sendMail(r.name, r.addr, r.books.splitter(';'));

More concise stuff is possible with heavy compile-time trickery (https://dpaste.dzfl.pl/cd375ac594cf) without incurring dreaded 1+N queries or even any unnecessary SELECT fields.

foreach (u; db.select!User.where!"NOT can_overdraw")
{
    sendMail(u.name, u.mail.addr, u.lendings // no 1+N here
        .where!"DATEDIFF(NOW(), end_date) >= 7 AND book.amount < 20")
        .map!(l => l.book.Reminder)); // client side range API
}

This has become even more of a challenge since we plan to make it a @safe @nogc poster child.

If anyone wants to help with this project please contact me.

At the moment reworked @safe @nogc database drivers would be most helpful.
I'll soon publish a small RC, Uniq, Weak library.
Unbuffered IO foundations are already here https://github.com/MartinNowak/io, but not yet practically proven, and still lacking vibe-core scheduler/eventcore integration.

On Monday, 15 January 2018 at 15:28:19 UTC, Martin Nowak wrote:
> More concise stuff is possible with heavy compile-time trickery (https://dpaste.dzfl.pl/cd375ac594cf) without incurring dreaded 1+N queries or even any unnecessary SELECT fields.
>
> foreach (u; db.select!User.where!"NOT can_overdraw")
> {
>     sendMail(u.name, u.mail.addr, u.lendings // no 1+N here
>         .where!"DATEDIFF(NOW(), end_date) >= 7 AND book.amount < 20")
>         .map!(l => l.book.Reminder)); // client side range API
> }
>
> This has become even more of a challenge since we plan to make it a @safe @nogc poster child.
>
> If anyone wants to help with this project please contact me.
>
> At the moment reworked @safe @nogc database drivers would be most helpful.
> I'll soon publish a small RC, Uniq, Weak library.
> Unbuffered IO foundations are already here https://github.com/MartinNowak/io, but not yet practically proven, and still lacking vibe-core scheduler/eventcore integration.

If someone wants to see this happen, but doesn't have enough time to contribute,
you could help to crowdfund this work, so I could afford to spent more time working on this.

Get in contact with us for more details.
https://dlang.org/donate.html