Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://github.com/getml/sqlgen below:

getml/sqlgen: sqlgen is an ORM and SQL query generator for C++-20, similar to Python's SQLAlchemy/SQLModel or Rust's Diesel.

📖 Documentation: Click here

sqlgen is a modern, type-safe ORM and SQL query generator for C++20, inspired by Python's SQLAlchemy/SQLModel and Rust's Diesel. It provides a fluent, composable interface for database operations with compile-time type checking and SQL injection protection.

sqlgen is based on and tightly integrated with reflect-cpp, a C++-20 library for fast serialization, deserialization and validation using reflection, similar to pydantic in Python, serde in Rust, encoding in Go or aeson in Haskell.

Together, reflect-cpp and sqlgen enable reliable and efficient ETL pipelines.

• 🔒 Type Safety: Compile-time validation of table schemas and queries
• 🛡️ SQL Injection Protection: Built-in input validation and parameterized queries
• 🔄 Composable Queries: Fluent interface for building complex queries
• 🚀 High Performance: Efficient batch operations and prepared statements
• 📦 Modern C++: Leverages C++20 features for a clean, expressive API
• 🔌 Multiple Backends: Support for PostgreSQL and SQLite
• 🔍 Reflection Integration: Seamless integration with reflect-cpp

The following table lists the databases currently supported by sqlgen and the underlying libraries used:

1. Make sure you have the required dependencies installed (skip this step on Windows):

sudo apt-get install autoconf bison flex # Linux (Ubuntu, Debian, ...)
brew install autoconf bison flex # macOS

1. Set up vcpkg:

git submodule update --init
./vcpkg/bootstrap-vcpkg.sh  # Linux, macOS
./vcpkg/bootstrap-vcpkg.bat # Windows

1. Build the library:

cmake -S . -B build -DCMAKE_CXX_STANDARD=20 -DCMAKE_BUILD_TYPE=Release
cmake --build build -j 4  # gcc, clang
cmake --build build --config Release -j 4  # MSVC

This will build the static library. To build the shared library add -DBUILD_SHARED_LIBS=ON -DVCPKG_TARGET_TRIPLET=... to the first line. Run ./vcpkg/vcpkg help triplets to view all supported triplets. Common triplets for shared libraries are x64-linux-dynamic, arm64-osx-dynamic or x64-osx-dynamic.

Add -DSQLGEN_MYSQL=ON to support MySQL/MariaDB.

1. Include in your CMake project:

find_package(sqlgen REQUIRED)
target_link_libraries(your_target PRIVATE sqlgen::sqlgen)

1. Install Conan (assuming you have Python and pipx installed):

pipx install conan
conan profile detect

For older versions of pip, you can also use pip instead of pipx.

1. Build the library:

conan build . --build=missing -s compiler.cppstd=gnu20

This will build the static library. To build the shared library, add -o */*:shared=True.

Add -o sqlgen/*:with_mysql=True to support MySQL/MariaDB.

1. Include in your CMake project:

find_package(sqlgen REQUIRED)
target_link_libraries(your_target PRIVATE sqlgen::sqlgen)

You can call conan inspect . to get an overview of the supported options.

#include <sqlgen/sqlite.hpp>
#include <iostream>

struct User {
    std::string name;
    int age;
};

int main() {
    // Connect to SQLite database
    const auto conn = sqlgen::sqlite::connect("test.db");
    
    // Create and insert a user
    const auto user = User{.name = "John", .age = 30};
    sqlgen::write(conn, user);
    
    // Read all users
    const auto users = sqlgen::read<std::vector<User>>(conn).value();
    
    for (const auto& u : users) {
        std::cout << u.name << " is " << u.age << " years old\n";
    }
}

#include <sqlgen/postgres.hpp>

// PostgreSQL connection
const auto credentials = sqlgen::postgres::Credentials{
    .user = "username",
    .password = "password",
    .host = "localhost",
    .dbname = "mydb",
    .port = 5432
};

const auto conn = sqlgen::postgres::connect(credentials);

// SQLite connection
const auto sqlite_conn = sqlgen::sqlite::connect("database.db");

struct Person {
    std::string first_name;
    std::string last_name;
    uint32_t age;
    std::optional<std::string> email;  // Nullable field
};

const auto people = std::vector<Person>({
    Person{.first_name = "Homer", .last_name = "Simpson", .age = 45},
    Person{.first_name = "Marge", .last_name = "Simpson", .age = 42}
});

// Automatically creates table if it doesn't exist
// (recall that the table is fully defined at compile time)
const auto result = sqlgen::write(conn, people);

if (!result) {
    std::cerr << "Error: " << result.error().what() << std::endl;
}

Or:

...

// write(...) abstracts these steps away, but
// if you prefer more granular control, you
// can use sqlgen::insert.
const auto result = begin_transaction(conn)
                        .and_then(create_table<Person> | if_not_exists)
                        .and_then(insert(std::ref(people)))
                        .and_then(commit);

...

Generated SQL:

BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS "Person" (
    "first_name" TEXT NOT NULL,
    "last_name" TEXT NOT NULL,
    "age" INTEGER NOT NULL,
    "email" TEXT
);

INSERT INTO "Person" ("first_name", "last_name", "age", "email") 
VALUES (?, ?, ?, ?);
COMMIT;

#include <rfl/json.hpp>
#include <sqlgen/postgres.hpp>

using namespace sqlgen;
using namespace sqlgen::literals;

// Build a query for adults, ordered by age
const auto query = read<std::vector<Person>> |
                   where("age"_c >= 18) |
                   order_by("age"_c.desc(), "last_name"_c) |
                   limit(10);

// Execute the query
const auto result = query(conn);

if (result) {
    // Print results as JSON
    std::cout << rfl::json::write(*result) << std::endl;
} else {
    std::cerr << "Error: " << result.error().what() << std::endl;
}

Generated SQL:

SELECT "first_name", "last_name", "age", "email"
FROM "Person"
WHERE "age" >= 18
ORDER BY "age" DESC, "last_name"
LIMIT 10;

using namespace sqlgen;
using namespace sqlgen::literals;

struct Children {
    std::string last_name;
    int num_children;
    int max_age;
    int min_age;
    int sum_age;
};

const auto get_children = select_from<Person>(
    "last_name"_c,
    count().as<"num_children">(),
    max("age"_c).as<"max_age">(),
    min("age"_c).as<"min_age">(),
    sum("age"_c).as<"sum_age">(),
) | where("age"_c < 18) | group_by("last_name"_c) | to<std::vector<Children>>;

const std::vector<Children> children = get_children(conn).value();

Generated SQL:

SELECT 
    "last_name",
    COUNT(*) as "num_children",
    MAX("age") as "max_age",
    MIN("age") as "min_age",
    SUM("age") as "sum_age"
FROM "Person"
WHERE "age" < 18
GROUP BY "last_name";

using namespace sqlgen;
using namespace sqlgen::literals;

struct ParentAndChild {
    std::string last_name;
    std::string first_name_parent;
    std::string first_name_child;
    double parent_age_at_birth;
};

const auto get_people =
  select_from<Person, "t1">(
      "last_name"_t1 | as<"last_name">,
      "first_name"_t1 | as<"first_name_parent">,
      "first_name"_t3 | as<"first_name_child">,
      ("age"_t1 - "age"_t3) | as<"parent_age_at_birth">) |
  inner_join<Relationship, "t2">("id"_t1 == "parent_id"_t2) |
  left_join<Person, "t3">("id"_t3 == "child_id"_t2) |
  order_by("id"_t1, "id"_t3) | to<std::vector<ParentAndChild>>;

Generated SQL:

SELECT t1."last_name" AS "last_name", 
       t1."first_name" AS "first_name_parent", 
       t3."first_name" AS "first_name_child", 
       t1."age" - t3."age" AS "parent_age_at_birth" 
FROM "Person" t1 
INNER JOIN "Relationship" t2 
ON t1."id" = t2."parent_id" 
LEFT JOIN "Person" t3 
ON t3."id" = t2."child_id" 
ORDER BY t1."id", t3."id"

using namespace sqlgen;
using namespace sqlgen::literals;

struct ParentAndChild {
    std::string last_name;
    std::string first_name_parent;
    std::string first_name_child;
    double parent_age_at_birth;
};

// First, create a subquery
const auto get_children =
  select_from<Relationship, "t1">("parent_id"_t1 | as<"id">,
                                  "first_name"_t2 | as<"first_name">,
                                  "age"_t2 | as<"age">) |
  left_join<Person, "t2">("id"_t2 == "child_id"_t1);

// Then use it as a source for another query
const auto get_people =
  select_from<Person, "t1">(
      "last_name"_t1 | as<"last_name">,
      "first_name"_t1 | as<"first_name_parent">,
      "first_name"_t2 | as<"first_name_child">,
      ("age"_t1 - "age"_t2) | as<"parent_age_at_birth">) |
  inner_join<"t2">(
    get_children, // Use the subquery as the source
    "id"_t1 == "id"_t2) | 
  order_by("id"_t1, "id"_t2) | to<std::vector<ParentAndChild>>;

Generated SQL:

SELECT t1."last_name" AS "last_name", 
    t1."first_name" AS "first_name_parent", 
    t2."first_name" AS "first_name_child", 
    t1."age" - t2."age" AS "parent_age_at_birth" 
FROM "Person" t1 
INNER JOIN (
    SELECT t1."parent_id" AS "id", 
           t2."first_name" AS "first_name", 
           t2."age" AS "age" 
    FROM "Relationship" t1 
    LEFT JOIN "Person" t2 
    ON t2."id" = t1."child_id"
) t2 
ON t1."id" = t2."id" 
ORDER BY t1."id", t2."id"

Or:

using namespace sqlgen;
using namespace sqlgen::literals;

struct ParentAndChild {
    std::string last_name;
    std::string first_name_parent;
    std::string first_name_child;
    double parent_age_at_birth;
};

// First, create a subquery
const auto get_parents = select_from<Person, "t1">(
    "child_id"_t2 | as<"id">,
    "first_name"_t1 | as<"first_name">,
    "last_name"_t1 | as<"last_name">,
    "age"_t1 | as<"age">
) | left_join<Relationship, "t2">("id"_t1 == "parent_id"_t2);

// Then use it as a source for another query
const auto get_people = select_from<"t1">(
    get_parents,  // Use the subquery as the source
    "last_name"_t1 | as<"last_name">,
    "first_name"_t1 | as<"first_name_parent">,
    "first_name"_t2 | as<"first_name_child">,
    ("age"_t1 - "age"_t2) | as<"parent_age_at_birth">) | 
 inner_join<Person, "t2">("id"_t1 == "id"_t2) | 
 order_by("id"_t1, "id"_t2) | to<std::vector<ParentAndChild>>;

Generated SQL:

SELECT t1."last_name" AS "last_name", 
       t1."first_name" AS "first_name_parent", 
       t2."first_name" AS "first_name_child", 
       (t1."age") - (t2."age") AS "parent_age_at_birth" 
FROM (
    SELECT t2."child_id" AS "id", 
           t1."first_name" AS "first_name", 
           t1."last_name" AS "last_name", 
           t1."age" AS "age" 
    FROM "Person" t1 
    LEFT JOIN "Relationship" t2 
    ON t1."id" = t2."parent_id"
) t1 
INNER JOIN "Person" t2 
ON t1."id" = t2."id"
ORDER BY t1."id", t2."id"

sqlgen provides comprehensive compile-time checks and runtime protection:

// Compile-time error: No such column "color"
const auto query = read<std::vector<Person>> |
                   where("color"_c == "blue");

// Compile-time error: Cannot compare column "age" to a string 
const auto query = read<std::vector<Person>> |
                   where("age"_c == "Homer");

// Compile-time error: "age" must be aggregated or included in GROUP BY 
const auto query = select_from<Person>(
    "last_name"_c,
    "age"_c 
) | group_by("last_name"_c);

// Compile-time error: Cannot add string and int 
const auto query = select_from<Person>(
    "last_name"_c + "age"_c 
);

// Runtime protection against SQL injection
std::vector<Person> get_people(const auto& conn, 
                              const sqlgen::AlphaNumeric& first_name) {
    using namespace sqlgen;
    return (read<std::vector<Person>> | 
            where("first_name"_c == first_name))(conn).value();
}

// This will be rejected
get_people(conn, "Homer' OR '1'='1");  // SQL injection attempt

For detailed documentation, visit our documentation page.

We welcome constructive criticism, feature requests and contributions! Please open an issue or a pull request.

This project is licensed under the MIT License - see the LICENSE file for details.

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4