sqlgen::col
sqlgen::col provides a type-safe way to reference and manipulate database columns in SQL queries. It enables building complex SQL conditions, ordering, and updates through a fluent C++ interface.
Usage
Basic Definition
Reference a column using the string literal operator:
using namespace sqlgen;
using namespace sqlgen::literals;
// Using string literal operator
const auto age_col = "age"_c;
// Using col template function
const auto name_col = col<"first_name">;
// Using a member pointer (resolved at compile time)
const auto age_col_mp = col<&Person::age>;Column Operations
Comparison Operations
Compare columns with values or other columns:
using namespace sqlgen;
using namespace sqlgen::literals;
// Compare with value
const auto query1 = read<std::vector<Person>> | where("age"_c > 18);
// Compare with another column
const auto query2 = read<std::vector<Person>> |
where("age"_c > "id"_c);This generates SQL like:
-- For query1
SELECT "id", "first_name", "last_name", "age"
FROM "Person"
WHERE "age" > 18;
-- For query2
SELECT "id", "first_name", "last_name", "age"
FROM "Person"
WHERE "age" > "id";NULL Operations
Check for NULL or NOT NULL values:
using namespace sqlgen;
using namespace sqlgen::literals;
// Find records where age is NULL
const auto query1 = read<std::vector<Person>> |
where("age"_c.is_null());
// Find records where age is NOT NULL
const auto query2 = read<std::vector<Person>> |
where("age"_c.is_not_null());This generates SQL like:
-- For query1
SELECT "id", "first_name", "last_name", "age"
FROM "Person"
WHERE "age" IS NULL;
-- For query2
SELECT "id", "first_name", "last_name", "age"
FROM "Person"
WHERE "age" IS NOT NULL;Pattern Matching
Use LIKE and NOT LIKE for pattern matching:
using namespace sqlgen;
using namespace sqlgen::literals;
// Find names starting with 'H'
const auto query1 = read<std::vector<Person>> |
where("first_name"_c.like("H%"));
// Find names not starting with 'H'
const auto query2 = read<std::vector<Person>> |
where("first_name"_c.not_like("H%"));This generates SQL like:
-- For query1
SELECT "id", "first_name", "last_name", "age"
FROM "Person"
WHERE "first_name" LIKE 'H%';
-- For query2
SELECT "id", "first_name", "last_name", "age"
FROM "Person"
WHERE "first_name" NOT LIKE 'H%';IN and NOT IN Operations
Use IN and NOT IN to check if a column value matches any value in a list:
using namespace sqlgen;
using namespace sqlgen::literals;
// Find people with specific first names (variadic arguments)
const auto query1 = read<std::vector<Person>> |
where("first_name"_c.in("Bart", "Lisa", "Maggie"));
// Find people NOT with specific first names (variadic arguments)
const auto query2 = read<std::vector<Person>> |
where("first_name"_c.not_in("Homer", "Hugo"));
// Find people with specific first names (using vector)
const auto names = std::vector<std::string>({"Bart", "Lisa", "Maggie"});
const auto query3 = read<std::vector<Person>> |
where("first_name"_c.in(names));
// Find people NOT with specific first names (using vector)
const auto excluded_names = std::vector<std::string>({"Homer", "Hugo"});
const auto query4 = read<std::vector<Person>> |
where("first_name"_c.not_in(excluded_names));This generates SQL like:
-- For query1
SELECT "id", "first_name", "last_name", "age"
FROM "Person"
WHERE "first_name" IN ('Bart', 'Lisa', 'Maggie');
-- For query2
SELECT "id", "first_name", "last_name", "age"
FROM "Person"
WHERE "first_name" NOT IN ('Homer', 'Hugo');
-- For query3
SELECT "id", "first_name", "last_name", "age"
FROM "Person"
WHERE "first_name" IN ('Bart', 'Lisa', 'Maggie');
-- For query4
SELECT "id", "first_name", "last_name", "age"
FROM "Person"
WHERE "first_name" NOT IN ('Homer', 'Hugo');Ordering
Specify column ordering in queries:
using namespace sqlgen;
using namespace sqlgen::literals;
// Order by age ascending
const auto query1 = read<std::vector<Person>> |
order_by("age"_c);
// Order by age descending
const auto query2 = read<std::vector<Person>> |
order_by("age"_c.desc());
// Multiple columns with mixed ordering
const auto query3 = read<std::vector<Person>> |
order_by("last_name"_c, "first_name"_c.desc());This generates SQL like:
-- For query1
SELECT "id", "first_name", "last_name", "age"
FROM "Person"
ORDER BY "age";
-- For query2
SELECT "id", "first_name", "last_name", "age"
FROM "Person"
ORDER BY "age" DESC;
-- For query3
SELECT "id", "first_name", "last_name", "age"
FROM "Person"
ORDER BY "last_name", "first_name" DESC;Updates
Set column values in UPDATE statements:
using namespace sqlgen;
using namespace sqlgen::literals;
// Update a single column
const auto query1 = update<Person>("age"_c.set(46));
// Update multiple columns
const auto query2 = update<Person>(
"first_name"_c.set("last_name"_c),
"age"_c.set(100)
) | where("first_name"_c == "Hugo");This generates SQL like:
-- For query1
UPDATE "Person" SET "age" = 46;
-- For query2
UPDATE "Person"
SET "first_name" = "last_name", "age" = 100
WHERE "first_name" = 'Hugo';Referencing columns with member pointers
As an alternative to the string literals above, columns can be referenced with a
member pointer to the struct field through the col template:
using namespace sqlgen;
using namespace sqlgen::literals;
// References the "age" column of the Person struct
const auto age_col = col<&Person::age>;
const auto query = read<std::vector<Person>> |
where(col<&Person::age> > 18) |
order_by(col<&Person::age>.desc());Because the referenced type is known at compile time, sqlgen qualifies the generated SQL with the table name:
SELECT ... FROM "Person" WHERE "Person"."age" > 18 ORDER BY "Person"."age" DESC_c/_tN literals, since both are checked against the struct at compile time. Their only practical advantage is somewhat clearer error messages, so which one you use is a matter of personal preference.Column member pointers in joins
When a table is given an alias (for example in a join), pass the alias as the second template argument. This also works for self-joins, where the same table appears more than once:
const auto get_people =
select_from<Person, "t1">(
col<&Person::first_name, "t1">,
col<&Person::age, "t1"> - col<&Person::age, "t3">) |
inner_join<Person, "t3">(col<&Person::id, "t1"> == col<&Person::id, "t3">);This produces SQL like:
SELECT t1."first_name", (t1."age") - (t3."age")
FROM "Person" t1
INNER JOIN "Person" t3 ON t1."id" = t3."id"Notes
- In joins, use
col<&Table::column, "alias">to disambiguate columns coming from different tables or aliases. See joins for details. - The
col<&Table::column>andcol<&Table::column, "alias">forms are fully composable with every operation described above (where,order_by,group_by,in,like,is_null,set, aggregations, and more).
Type Safety
sqlgen::col class provides compile-time type safety:
- Column names are validated at compile time using string literals
- SQL injection is prevented through proper escaping and parameterization
Notes
- The class supports all standard SQL comparison operators:
==,!=,<,<=,>,>= - Column names are automatically quoted in generated SQL
- The class is designed to be used with the query builder interface
- All operations are composable and can be chained together
- The class supports both value and column-to-column comparisons
- String literals are automatically converted to the appropriate SQL type
- The class is thread-safe and has no mutable state