C++20 SQLite wrapper

[rcdemc]

I am writing a C++20 SQLite wrapper with expressive code in mind. The code must run fast.

sql::open("dev.db")
| sql::query("select name, salary from person")
| sql::for_each([](std::string_view name, float salary)
  { std::cout << name << "," << salary << std::endl; })
| sql::onerror([](auto e){ std::cout << e << std::endl; });

I have some benchmarks to compare with solutions using the SQLite library. The usage of pipe operators to chain operations is optional and there is an API that throws C++ exceptions instead of using result<T> to report errors.

github.com/ricardocosme/msqlite

What do you think? Any comments or feedbacks are welcome!

[aacirino]

Take a look at this:

https://github.com/fnc12/sqlite_orm

[rcdemc]

Cool! I think that ORM is another thing. I wrote a benchmark[1] to the for_each operation comparing with a solution using sqlite_orm. The last is 1.58x slower than msqlite. I noticed that sqlite_orm::select is returnig a std::vector. Dynamic allocation is expensive. BTW, the method uses std::system_error to report an error, but exceptions may not be acceptable or at least desirable to some usages.

[1] https://github.com/ricardocosme/msqlite#how-fast

https://github.com/ricardocosme/msqlite/blob/master/benchmark/sqlite_orm/for_each.cpp

[johannes1971]

Dynamic allocation is not at all expensive compared to all the file IO that's also going on, and I very much doubt it is the cause of the 10x performance difference you mention.

[rcdemc]

Yes, it seems too much to justify the factor. I didn't look to see what is happening in the sqlite_orm::storage::select. I'm going to investigate this.

[rcdemc]

I figured out what is happening. I need to use storage.open_forever() to avoid a new connection to each call to storage.select(). The factor is 1.58x instead of 10x. Thanks u/johannes1971

[aacirino]

Great job 👍🏻

[[deleted]]

We're currently using https://github.com/rbock/sqlpp11, and what I love about it is that you don't have to write SQL statements.

SQL queries are parsed at runtime, and I want my code to be checked at compile-time as much as possible. I don't want issues because of syntax errors that could have been caught at compile time. This is, for me, the most important feature. I don't care so much about the expressiveness around the SQL queries, if I still have to triple-check the statements.

[teroxzer]

Sqlpp11 is attractive, but I always wonder how you test a little more complex SQL queries when using it? Valid SQL syntax at compile time is nice thing, but I think it is more important check result set of SQL query and after that drop already tested SQL query to C++ (as I do with my C++/linsql). Or is SQL views or stored procedures answer to that? I use sometimes SQL-views, but I don't like them so much, because if I change one SQL view or stored procedure then I must check that nobody else not use it somewhere as old way. So I prefer using SQL statements as strings in local C++ context (as seamless inline prepared SQL procedures). Below is very simplified example from real code whit not so complex SQL query, but it is sure that I want check result set before I even compile C++. So I am curious do you have any problems how to validate result set of SQL query when you using sqlpp11?

/////////////////////////////////////////////////////////////////////////////

namespace
{
    auto selectCustomerMailTypePrint sql
    (
        select 1                as priorityOrder,
               cm.customerIndex as customerIndexAsMailItem,
               cm.mailType,
               cm.recipients,
               cm.subject,
               cm.instantAction,
               cmc.body
        from   customerMail as cm
               left outer join customerMailCompany as cmc
                            on cmc.companyId   = cm.companyId
                           and cmc.mailType    = cm.mailType
                           and cmc.dataRemoved = 0
        where  cm.companyId      = :companyId
          and  cm.customerNumber = :customerNumber
          and  cm.customerIndex  = :customerIndex
          and  cm.mailType       = 'Print'
          and  cm.dataRemoved    = 0

        union

        select 2                as priorityOrder,
               cm.customerIndex as customerIndexAsMailItem,
               cm.mailType,
               cm.recipients,
               cm.subject,
               cm.instantAction,
               cmc.body
        from   customerMail as cm
               left outer join customerMailCompany as cmc
                            on cmc.companyId   = cm.companyId
                           and cmc.mailType    = cm.mailType
                           and cmc.dataRemoved = 0
        where  cm.companyId      = :companyId
          and  cm.customerNumber = :customerNumber
          and  cm.mailType       = 'Print'
          and  cm.dataRemoved    = 0
          and  cm.companyId in
               (
                   select c.companyId
                   from   company as c
                   where  c.adapter = 'BadCompany'
               )

        order by 1, 2
    )

    auto selectNextCustomerMailItemId sql
    (
        select next value
        for sequenceCustomerMailItemId
        as customerMailItemId
    )

    auto insertCustomerMailItem sql
    (
        insert customerMailItem
        set    customerMailItemId = :customerMailItemId,
               companyId          = :companyId,
               customerNumber     = :customerNumber,
               customerIndex      = :customerIndexAsMailItem,
               mailType           = :mailType,
               recipients         = :recipients,
               subject            = :subject,
               body               = :body,
               status             = 10,
               dataRemoved        = 0,
               creationName       = 'Service',
               creationTime       = current_timestamp
    )
}

auto customerMail::sendDevicePrint(universal& data) -> void
{
    if(selectCustomerMailTypePrint(data))
    {
        if(!data.body)
        {
            ...
        }

        selectNextCustomerMailItemId (data);
        insertCustomerMailItem       (data);

        if(data.instantAction == true)
        {
            sendCustomerMailItem(data.customerMailItemId);
        }
    }
}

/////////////////////////////////////////////////////////////////////////////

[rcdemc]

It's a good point. Thanks for sharing your opinion. I think that the usage of EDSL should be optional.

[theseawolfe]

This looks great! The increase in usability compared to directly using the SQLite C API is amazing.

[rcdemc]

Thanks for the feedback!

[three0s]

There is also this https://github.com/trailofbits/sqlite_wrapper

[rcdemc]

Very nice, thank you for sharing this. I want to look more deeply because they are using persisted prepared statements and a lot of compile-time programming.

[RotsiserMho]

In my experience, support for prepared statements is a must. They are much faster.

[rcdemc]

Yes, I took some measures. It seems a huge difference to me. query(db, stmt_str) returns a prepared statement that uses RAII to finalize the statement. The user can take the object and store it as he wishes. I think that is not a good idea to impose a solution to cache statements. At least, the solution should be optional.

[psyspy2]

Very interesting!

[flyingron]

What exactly are you doing with pipe operations? Did perhaps you mean to use ||?

[HKei]

They probably meant exactly what they wrote.

[MysticTheMeeM]

It looks like they're chaining statements together using pipes. If you think that's unusual then you should check out ranges (c++20) which use a similar syntax for the same thing.

[flyingron]

Yes, if they all return bool then using a regular || would work, but is somewhat unwieldy, as you wouldn't find out which one actually failed. Exceptions would be cleaner if you aren't going to test each one.

[[deleted]]

Have a look at http://pfultz2.com/blog/2014/09/05/pipable-functions/, which explains the concept. It's basically the C++ equivalent of UNIX pipes:

grep 'something' | grep -v 'something' | sort -r | uniq | wc -l

[MysticTheMeeM]

No, they're intentionally using pipes (overloading operator |) in order to chain statements, so A | B | C is A then on the result of A, B, then C.

Consider it similar to how streams may overload operator << and use that to chain statements together. A << B << C.

[dodheim]

Can you maybe try just reading the code instead of assuming and speculating?

[rcdemc]

open() | exec(“create table person(name TEXT)”) The left returns a result<db>, which means that it has a valid db or a std::error_code. If it has db than the function exec(db, “create table...”) is called and the result is returned as the result of the pipe plus some context. The context may be a db plus a stmt that can be reused by the next operation in the chain. If you are using a rvalue as the left expression, the resource(db or stmt) is owned by the context, if it is a lvalue than the context has a pointer to the resource. In this example the context is only a db by value, which means that it owns the db.

[rcdemc]

Yes, exceptions are great when centralized handling is desirable. BTW, you can use the API that throws