A type safe SQL template library for C++

Related tags

Database sqlpp11
Overview

sqlpp11

A type safe embedded domain specific language for SQL queries and results in C++

Documentation is found in the wiki

So what is this about?

SQL and C++ are both strongly typed languages. Still, most C/C++ interfaces to SQL are based on constructing queries as strings and on interpreting arrays or maps of strings as results.

sqlpp11 is a templated library representing an embedded domain specific language (EDSL) that allows you to

  • define types representing tables and columns,
  • construct type safe queries checked at compile time for syntax errors, type errors, name errors and even some semantic errors,
  • interpret results by iterating over query-specific structs with appropriately named and typed members.

This results in several benefits, e.g.

  • the library user operates comfortably on structs and functions,
  • the compiler reports many kinds of errors long before the code enters unit testing or production,
  • the library hides the gory details of string construction for queries and interpreting results returned by select calls.

The library supports both static and dynamic queries. The former offers greater benefit in terms of type and consistency checking. The latter makes it easier to construct queries on the flight.

sqlpp11 is vendor-neutral. Specific traits of databases (e.g. unsupported or non-standard features) are handled by connector libraries. Connector libraries can inform the developer of missing features at compile time. They also interpret expressions specifically where needed. For example, the connector could use the operator|| or the concat method for string concatenation without the developer being required to change the statement.

The library is already used in production but it is certainly not complete yet. Feature requests, bug reports, contributions to code or documentation are most welcome.

Examples:

For the examples, lets assume you have a table class representing something like

CREATE TABLE foo (
    id bigint,
    name varchar(50),
    hasFun bool
);

And we assume to have a database connection object:

TabFoo foo;
Db db(/* some arguments*/);

// selecting zero or more results, iterating over the results
for (const auto& row : db(select(foo.name, foo.hasFun).from(foo).where(foo.id > 17 and foo.name.like("%bar%"))))
{
    if (row.name.is_null())
        std::cerr << "name is null, will convert to empty string" << std::endl;
    std::string name = row.name;   // string-like fields are implicitly convertible to string
    bool hasFun = row.hasFun;          // bool fields are implicitly convertible to bool
}

// selecting ALL columns of a table
for (const auto& row : db(select(all_of(foo)).from(foo).where(foo.hasFun or foo.name == "joker")))
{
    int64_t id = row.id; // numeric fields are implicitly convertible to numeric c++ types
}

// selecting zero or one row, showing off with an alias:
SQLPP_ALIAS_PROVIDER(cheese);
if (const auto& row = db(select(foo.name.as(cheese)).from(foo).where(foo.id == 17)))
{
    std::cerr << "found: " << row.cheese << std::endl;
}

// selecting a single row with a single result:
return db(select(count(foo.id)).from(foo).unconditionally()).front().count;

Of course there are joins and subqueries, more functions, order_by, group_by etc.
These will be documented soon.

// A sample insert
db(insert_into(foo).set(foo.id = 17, foo.name = "bar", foo.hasFun = true));

// A sample update
db(update(foo).set(foo.hasFun = not foo.hasFun).where(foo.name != "nobody"));

// A sample delete
db(remove_from(foo).where(not foo.hasFun));

License:

sqlpp11 is distributed under the BSD 2-Clause License.

Status:

Branch / Compiler clang-3.4, gcc-4.9, Xcode-7 MSVC 2015/2017 Test Coverage
master Build Status Build status Coverage Status
develop Build Status Build status Coverage Status

Additional information available:

Past talks about sqlpp11 and some coding concepts used within the library:

Requirements:

Compiler: sqlpp11 makes heavy use of C++11 and requires a recent compiler and STL. The following compilers are known to compile the test programs:

  • clang-3.4+ on Ubuntu-12.4
  • g++-4.8+ on Ubuntu-12.4
  • g++-4.8+ on cygwin 64bit
  • g++-4.9+ on Debian Unstable
  • Xcode-7 on OS X
  • MSVC 2015 Update 1 on Windows Server 2012

Database Connector: sqlpp11 requires a certain api in order to connect with the database, see database/api.h.

To demonstrate that sqlpp11 can work with other backends as well, here is an experimental backend for structs in standard containers:

Date Library: sqlpp11 requires Howard Hinnant's date library for date and date_time data types. Sqlpp11 uses FetchContent to pull the library automatically in the project.

Build and Install

Note: Depending on how you use the lib, you might not need to install it (see Basic Usage)

Build from Source:

Download and unpack the latest release from https://github.com/rbock/sqlpp11/releases or clone the repository. Inside the directory run the following commands:

cmake -B build
cmake --build build --target install

The last step will build the library and install it system wide, therefore it might need admins right.

Install via Homebrew (MacOS):

brew install marvin182/zapfhahn/sqlpp11

Some connectors can be installed with the formula. See brew info marvin182/zapfhahn/sqlpp11 for available options.

Build via vcpkg:

You can download and install sqlpp11 using the vcpkg dependency manager:

git clone https://github.com/Microsoft/vcpkg.git
cd vcpkg
./bootstrap-vcpkg.sh
./vcpkg integrate install
vcpkg install sqlpp11

The sqlpp11 port in vcpkg is kept up to date by Microsoft team members and community contributors. If the version is out of date, please create an issue or pull request on the vcpkg repository.

The following connector libraries for sqlpp11 are maintained as a separate package in vcpkg:

Basic usage:

Use with cmake: The library officially supports two ways how it can be used with cmake. You can find examples for both methods in the example folder.

  1. Fetch content (Recommend, no installation required)
  2. Find package (installation required, see above)

Create DDL files:

mysql: 'show create table MyDatabase.MyTable' #or
mysqldump --no-data MyDatabase > MyDatabase.sql

Create headers for them with provided Python script:

%sqlpp11_dir%/scripts/ddl2cpp ~/temp/MyTable.ddl  ~/temp/MyTable %DatabaseNamespaceForExample%

(In case you're getting notes about unsupported column type take a look at the other datatypes in sqlpp11/data_types. They are not hard to implement.)

Include generated header (MyTable.h), that's all.

If you prefer Ruby over Python, you might want to take a look at https://github.com/douyw/sqlpp11gen

Contact:

Breaking changes in 0.36:

See Changes

Comments
  • Async results support?

    Async results support?

    I recently came accross this library and it looks great however my use-case almost always needs async behavior. Since I'm not familiar with the code, the question might be a stupid one so please bear with me.

    Would it be easy or difficult to extend the api to have async callbacks (lambdas) on completion of requests? Is anything like that planned?

    enhancement question 
    opened by skarlsson 41
  • Is there any way to create tables?

    Is there any way to create tables?

    Hi,

    Tell me please, is there any way to create tables? I.e. I was expected something like this:

    if ( !db.exists<Table>() )
       db.create<Table>();
    
    opened by niXman 27
  • Use after free with sqpp::text fields

    Use after free with sqpp::text fields

    I'm not sure if the source of the problems arises in the sqlpp11-connector-postgresql or the core sqlpp11. I cannot understand why as to my current tracing sqlpp11 is creating a std::string from the returned char* + len out of bind_result_t::_bind_text_result(size_t index, const char** value, size_t* len);

    The issue seems to cccur when there are two consecutive text fields.

    opened by sergeken 23
  • Packages for conan

    Packages for conan

    Hi Roland!

    I am trying to package the sqlpp11 suite for conan, as it has been requested by some users: https://ci.appveyor.com/project/rbock/sqlpp11-connector-sqlite3/history

    So far I have succesfully done:

    • https://www.conan.io/source/sqlpp11/0.38/memsharded/testing
    • https://www.conan.io/source/date/1.0.0/memsharded/testing

    But when got to the connectors, I am having problems building them, both stl (https://www.conan.io/source/sqlpp11-stl/0.4/memsharded/testing, not working) and sqlite3. First, I guess that VS is still not supported for the connectors, tried with latest VS2015Upd2, with build errors. First was easily removed adding <functional> header, but then some std::get issues. Also checked your own appveyor: https://ci.appveyor.com/project/rbock/sqlpp11-connector-sqlite3/history

    Also got some build errors for Ubuntu with gcc5.3, last commit in connector-stl seems like one year ago, wondering about the status too (though likely could be my bad here)

    Just wanted to offer my help, as I usually deal with CI and generating multiple configurations for some packages, check for example how I generating almost 100 different binaries for ZeroMQ, with just this repo: https://github.com/memsharded/conan-zmq. If you think that conan might help to connect the different libraries together, please tell me and I'd love to collaborate on this task.

    opened by memsharded 23
  • Question/Suggestion: Creation of CREATE TABLE statements

    Question/Suggestion: Creation of CREATE TABLE statements

    Hi,

    First I just want to say I really like this library, I think you've done a great job here and hacking on the code helped me improve my understanding of Cpp11 a great deal. So thanks for that! :)

    One of the things I've wrote is a little helper function that creates a CREATE TABLE statement from a given table. For example if we use the following PPGEN code SQLPP_DECLARE_TABLE( (tab_test), (id , int , SQLPP_PRIMARY_KEY) (name , varchar(255), SQLPP_NOT_NULL ) (feature, int , SQLPP_NOT_NULL ) )

    My function outputs: CREATE TABLE tab_test(id INTEGER AUTOINCREMENT NOT NULL,name TEXT NOT NULL,feature INTEGER NOT NULL);

    Currently this only supports AUTOINCREMENT, NOT NULL and basic types (Everything is INTEGER or TEXT). But obviously this is just a quick&dirty proof of concept!

    I was wondering if this is something you'd like to merge into the library. If so I'll work on making it more robust.

    enhancement 
    opened by blapid 22
  • Support for INSERT OR UPDATE

    Support for INSERT OR UPDATE

    Hello,

    It would be interesting to add support for INSERT OR UPDATE.

    The MySQL syntax is as follow: INSERT INTO table (a, b, c) VALUES (1, 20, 68) ON DUPLICATE KEY UPDATE a=a+1 Which can be read as follow: insert values a, b, c with data 1, 20, 68 respectively. If the primary key already exists, update a with a+1. Existing values won't be changed. A WHERE clause can be added to the UPDATE statement.

    The SQLite doesn't have that command. However, the same behavior can be achieved with the following statements: INSERT OR IGNORE INTO <table>(<primaryKey>, <column1>, <column2>, ...) VALUES(<primaryKeyValue>, <value1>, <value2>, ...); UPDATE <table> SET <column1>=<value1>, <column2>=<value2>, ... WHERE changes()=0 AND <primaryKey>=<primaryKeyValue>; It is to be noted that it is done with two statements, possibly leading to race conditions. To be atomic, one can use the INSERT OR REPLACE statement, which replaces any statement already existing (and thus, setting all non provided fields to their default value). I found a command online that allow an INSERT OR REPLACE command to keep the data unmodified about the line being replaced: WITH new (name, title, author) AS ( VALUES('about', 'About this site', 42) ) INSERT OR REPLACE INTO page (id, name, title, content, author) SELECT old.id, new.name, new.title, old.content, new.author FROM new LEFT JOIN page AS old ON new.name = old.name; It basically selects the line being replaced and replace it with the already existing data. I'm not sure if that can be achieved in sqlpp11 though.

    The PostgreSQL syntax is: INSERT INTO tablename (a, b, c) values (1, 2, 10) ON CONFLICT (a) DO UPDATE SET c = tablename.c + 1; The conflicting key must be precised (here it's a), and a WHERE clause can be added to the UPDATE statement. The SET keyword must be used, as if it was a normal UPDATE statement.

    Thank you.

    opened by L3nn0x 19
  • Union same type and name requirement too strict

    Union same type and name requirement too strict

    I'm trying to do a union between two different tables using a subset of columns from each table. For example:

    SELECT a.id FROM a UNION SELECT b.id FROM b

    This seems to not be possible in sqlpp11 which will output the error "both arguments in a union have to have the same result columns (type and name)". The following test program fails to compile with the above error: sql.zip

    If I comment out the strict check in sqlpp11/union.h the program compiles and generates the expected SQL statement. Maybe there is another way to accomplish this.

    opened by kuriboshi 18
  • MS Visual Studio 2015 support

    MS Visual Studio 2015 support

    Hi Roland

    Does your project compile with the new Visual Studion 2015 version? Has anyone chequed this? I can't use your code unless I can also compile it on Windows with Visual Studio. Peter

    opened by peterritter 18
  • How to check if row exists?

    How to check if row exists?

    Hi,

    I need to check if user 'Bob' exists in the table:

    CREATE TABLE users(
       id INT PRIMARY KEY,
       nickname varchar(32)
    )
    

    Usually I would have run the following query:

    SELECT COUNT(*) FROM users WHERE nickname='Bob'
    

    But I can not understand how to do this using sqlpp11?

    Thanks!

    opened by niXman 18
  • Feature/fetch content

    Feature/fetch content

    Hi,

    as discussed in #348 my implementation to use fetch content instead. I've also added instructions on how to use the lib and two examples (one for fetch content and one for find package).

    I also made a little bit more fetch friendly for example by including CTest (which will automatically provide a BUILD_TESTING cache variable) and automatically deactive test, if it's not the top level project.

    The main.cpp is empty in both though, do you have a very short code using any of the methods from the lib? Just to verify that everything is working. I browsed in the tests, but they seemed a little bit too complicated for this simple example. It doesn't have to be a useful example.

    opened by Leon0402 17
  • Unable to compile with MSVC 19.11 (VS15.3 or VS2017.3)

    Unable to compile with MSVC 19.11 (VS15.3 or VS2017.3)

    Hi!

    MS released VS 15.3 (2017.3) with first toolset update and build is broken in /std:c++17 mode. Errors:

    \include\sqlpp11/statement.h(156): error C2903: '_base_t': symbol is neither a class template nor a function template
    \include\sqlpp11/insert.h(109): note: see reference to class template instantiation 'sqlpp::statement_t<void,sqlpp::insert_t,sqlpp::no_into_t,sqlpp::no_insert_value_list_t>' being compiled
    \date.h(111): note: see reference to class template instantiation 'std::chrono::duration<int,std::ratio<86400,1>>' being compiled
    \include\sqlpp11/statement.h(166): error C3546: '...': there are no parameter packs available to expand
    \include\sqlpp11/statement.h(173): error C3546: '...': there are no parameter packs available to expand
    \include\sqlpp11/statement.h(179): error C3546: '...': there are no parameter packs available to expand
    \include\sqlpp11/table.h(48): error C2903: '_member_t': symbol is neither a class template nor a function template
    \include\sqlpp11/verbatim_table.h(53): note: see reference to class template instantiation 'sqlpp::table_t<sqlpp::verbatim_table_t,sqlpp::detail::unusable_pseudo_column_t>' being compiled
    

    sqlite3 connector 0.24 + sqlpp11 develop.

    opened by egorpugin 17
  • SQLite's joins

    SQLite's joins

    I found this in serializer.h:

    86:    static_assert(wrong_t<Lhs, Rhs>::value, "Sqlite3: No support for outer join");
    93:    static_assert(wrong_t<Lhs, Rhs>::value, "Sqlite3: No support for right_outer join");
    

    Added in SQLite 3.39.0.

    Add (long overdue) support for RIGHT and FULL OUTER JOIN.

    opened by data-man 2
  • Support for views

    Support for views

    I started using the library just recently and I like it a lot, but I quickly noticed there is no support whatsoever for views. Are there any plans for changing that?

    SQLite makes things a bit easier since views are read-only:

    You cannot DELETE, INSERT, or UPDATE a view. Views are read-only in SQLite.

    From what I gathered (some) other engines allow inserting through views but even then a read-only implementation would be very helpful.

    opened by woytaz 7
  • Remove the workaround for the gcc bug 51253?

    Remove the workaround for the gcc bug 51253?

    I notice that interpret_tuple.h has a workaround: https://github.com/rbock/sqlpp11/blob/main/include/sqlpp11/interpret_tuple.h

        // Note: A braced-init-list does guarantee the order of evaluation according to 12.6.1 [class.explicit.init]
        // paragraph 2 and 8.5.4 [dcl.init.list] paragraph 4.
        // See for example: "http://en.cppreference.com/w/cpp/utility/integer_sequence"
        // See also: "http://stackoverflow.com/questions/6245735/pretty-print-stdtuple/6245777#6245777"
        // Beware of gcc-bug: "http://gcc.gnu.org/bugzilla/show_bug.cgi?id=51253", otherwise an empty swallow struct could
        // be used
        using swallow = int[];
        (void)swallow{0,  // workaround against -Wpedantic GCC warning "zero-size array 'int [0]'"
                      (interpret_tuple_element(std::get<Is>(t), separator, context, useBraces, Is), 0)...};
        return context;
    

    This compiler bug is marked as fixed: https://gcc.gnu.org/bugzilla/show_bug.cgi?id=51253 Shall the workaround be removed? Is the compiler bug actually fixed?

    opened by Chandlerooo 1
Owner
Roland Bock
Roland Bock
A lightweight header-only C++11 library for quick and easy SQL querying with QtSql classes.

EasyQtSql EasyQtSql is a lightweight header-only C++11 library for quick and easy SQL querying with QtSql classes. Features: Header only C++11 library

null 51 Nov 17, 2022
dqlite is a C library that implements an embeddable and replicated SQL database engine with high-availability and automatic failover

dqlite dqlite is a C library that implements an embeddable and replicated SQL database engine with high-availability and automatic failover. The acron

Canonical 3.2k Nov 23, 2022
DuckDB is an in-process SQL OLAP Database Management System

DuckDB is an in-process SQL OLAP Database Management System

DuckDB 7.4k Nov 23, 2022
YugabyteDB is a high-performance, cloud-native distributed SQL database that aims to support all PostgreSQL features

YugabyteDB is a high-performance, cloud-native distributed SQL database that aims to support all PostgreSQL features. It is best to fit for cloud-native OLTP (i.e. real-time, business-critical) applications that need absolute data correctness and require at least one of the following: scalability, high tolerance to failures, or globally-distributed deployments.

yugabyte 7.3k Nov 24, 2022
TimescaleDB is an open-source database designed to make SQL scalable for time-series data.

An open-source time-series SQL database optimized for fast ingest and complex queries. Packaged as a PostgreSQL extension.

Timescale 14.1k Nov 22, 2022
OceanBase is an enterprise distributed relational database with high availability, high performance, horizontal scalability, and compatibility with SQL standards.

What is OceanBase database OceanBase Database is a native distributed relational database. It is developed entirely by Alibaba and Ant Group. OceanBas

OceanBase 5k Nov 20, 2022
A bare-bone SQL implementation

MiniSQL A bare-bone SQL implementation. Project Structure include folder contains header files of all modules. These header files are meant to be shar

null 16 Apr 23, 2022
A PostgreSQL extension providing an async networking interface accessible via SQL using a background worker and curl.

pg_net is a PostgreSQL extension exposing a SQL interface for async networking with a focus on scalability and UX.

Supabase 48 Nov 25, 2022
PGSpider: High-Performance SQL Cluster Engine for distributed big data.

PGSpider: High-Performance SQL Cluster Engine for distributed big data.

PGSpider 132 Sep 8, 2022
C++11 Lightweight Redis client: async, thread-safe, no dependency, pipelining, multi-platform

C++11 Lightweight Redis client: async, thread-safe, no dependency, pipelining, multi-platform

Simon Ninon 1k Nov 22, 2022
Test any type of cloud database on Android apps. No need of a dedicated backend.

DB Kong - Database connections simplified DB Kong is an Android library that allows you to connect, interact and test any type of cloud database on An

Arjun 9 May 9, 2022
Simple constant key/value storage library, for read-heavy systems with infrequent large bulk inserts.

Sparkey is a simple constant key/value storage library. It is mostly suited for read heavy systems with infrequent large bulk inserts. It includes bot

Spotify 986 Nov 27, 2022
❤️ SQLite ORM light header only library for modern C++

SQLite ORM SQLite ORM light header only library for modern C++ Status Branch Travis Appveyor master dev Advantages No raw string queries Intuitive syn

Yevgeniy Zakharov 1.7k Nov 29, 2022
Kreon is a key-value store library optimized for flash-based storage

Kreon is a key-value store library optimized for flash-based storage, where CPU overhead and I/O amplification are more significant bottlenecks compared to I/O randomness.

Computer Architecture and VLSI Systems (CARV) Laboratory 24 Jul 14, 2022
SOCI - The C++ Database Access Library

Originally, SOCI was developed by Maciej Sobczak at CERN as abstraction layer for Oracle, a Simple Oracle Call Interface. Later, several database backends have been developed for SOCI, thus the long name has lost its practicality. Currently, if you like, SOCI may stand for Simple Open (Database) Call Interface or something similar.

SOCI 1.2k Nov 24, 2022
Velox is a new C++ vectorized database acceleration library aimed to optimizing query engines and data processing systems.

Velox is a C++ database acceleration library which provides reusable, extensible, and high-performance data processing components

Facebook Incubator 1.8k Nov 27, 2022
The fastest database-library on Android OS.

Android SQLite3 NDK 封装 Demo下载 (操作:按钮新增 按钮查询 点按编辑 长按删除) 写在前面 sqlite3 开源、集成简单(现在的版本只有2个文件 sqlite3.h sqlite3.c) 这个库抽离自 Telegram 的开源代码、作者:DrKLO 我个人感觉 Tele

水银灯、 2 Dec 27, 2021
LevelDB is a fast key-value storage library written at Google that provides an ordered mapping from string keys to string values.

LevelDB is a fast key-value storage library written at Google that provides an ordered mapping from string keys to string values. Authors: Sanjay Ghem

Google 31.3k Nov 24, 2022
C++11 wrapper for the LMDB embedded B+ tree database library.

lmdb++: a C++11 wrapper for LMDB This is a comprehensive C++ wrapper for the LMDB embedded database library, offering both an error-checked procedural

D.R.Y. C++ 260 Nov 14, 2022