Problem with queries and schemas after v0.3.0 update

[mcraveiro]

Hi sqlgen developers,

I just updated my vcpkg to latest, picking up v0.3.0 [1]. I am experiencing an issue querying existing tables. The behaviour is as follows. I have an entity:

struct currency_entity {
    constexpr static const char* schema = "oresdb";
    constexpr static const char* tablename = "currencies";

    sqlgen::PrimaryKey<std::string> iso_code;
    std::string name;
    std::string numeric_code;
    std::string symbol;
    std::string fraction_symbol;
    int fractions_per_unit;
    std::string rounding_type;
    int rounding_precision;
    std::string format;
    std::string currency_type;
    std::string modified_by;
    sqlgen::Timestamp<"%Y-%m-%d %H:%M:%S"> valid_from = "9999-12-31 23:59:59";
    sqlgen::Timestamp<"%Y-%m-%d %H:%M:%S"> valid_to = "9999-12-31 23:59:59";
};

It is queried like so:

std::vector<domain::currency> currency_repository::read_latest(context ctx) {
    BOOST_LOG_SEV(lg, debug) << "Reading latest currencies.";

    static auto max(make_timestamp(max_timestamp));
    const auto query = sqlgen::read<std::vector<currency_entity>> |
        where("valid_to"_c == max.value()) |
        order_by("valid_from"_c.desc());

    const auto sql = postgres::to_sql(query);
    BOOST_LOG_SEV(lg, debug) << "Query: " << sql;

    const auto r = session(ctx.connection_pool())
        .and_then(query);
    ensure_success(r);
    BOOST_LOG_SEV(lg, debug) << "Read latest currencies. Total: " << r->size();
    return currency_mapper::map(*r);
}

In version 0.2.0 the generated SQL was as follows:

SELECT "iso_code", "name", "numeric_code", "symbol", "fraction_symbol", "fractions_per_unit", "rounding_type", "rounding_precision", "format", "currency_type", "modified_by", "valid_from", "valid_to" FROM "oresdb"."currencies" WHERE "valid_to" = '9999-12-31 23:59:59' ORDER BY "valid_from" DESC

Please note the FROM (i.e., FROM "oresdb"."currencies"). After pulling v0.3.0, I now see the following:

SELECT "iso_code", "name", "numeric_code", "symbol", "fraction_symbol", "fractions_per_unit", "rounding_type", "rounding_precision", "format", "currency_type", "modified_by", "valid_from", "valid_to" FROM "currencies" WHERE "valid_to" = '9999-12-31 23:59:59' ORDER BY "valid_from" DESC

The schema appears to now be absent in the from (i.e., FROM "currencies"). This then results in a postgres error:

2025-10-22 14:46:16.092334 [ERROR] [ores.risk.repository.currency_repository] Executing 'DECLARE sqlgen_cursor CURSOR FOR SELECT "iso_code", "name", "numeric_code", "symbol", "fraction_symbol", "fractions_per_unit", "rounding_type", "rounding_precision", "format", "currency_type", "modified_by", "valid_from", "valid_to" FROM "currencies" WHERE "valid_to" = '9999-12-31 23:59:59' ORDER BY "valid_from" DESC' failed: ERROR:  relation "currencies" does not exist
LINE 1: ...pe", "modified_by", "valid_from", "valid_to" FROM "currencie...

I had a brief layperson's look at a diff between versions - I could not make much of the diff to be fair :-) but I did notice some changes around select:

Having said that, to the untrained eye the new code seems to be taking into account the schema name so I am probably barking at the wrong tree:

At any rate, any pointers as to why I am experiencing this would be most appreciated.

Thanks for all your hard work.

[1] https://vcpkg.io/en/package/sqlgen

[mcraveiro]

I kept investigating this issue to see if there was a quick fix I could slot in. Haven't quite got that far, but I thought I might as well record my steps here in case it helps anyone else :-) First I concluded that this file is generating the table and schema names for my particular use case by hard coding a string:

This generated the expected log entry:

[2025-10-22 15:47:13.075533] [0x00007f179379f780] [info]    Query: SELECT "iso_code", "name", "numeric_code", "symbol", "fraction_symbol", "fractions_per_unit", "rounding_type", "rounding_precision", "format", "currency_type", "modified_by", "valid_from", "valid_to" FROM "CCCC" WHERE "valid_to" = '9999-12-31 23:59:59' ORDER BY "valid_from" DESC
[2025-10-22 15:47:13.076075] [0x00007f179379f780] [info]    Executing 'DECLARE sqlgen_cursor CURSOR FOR SELECT "iso_code", "name", "numeric_code", "symbol", "fraction_symbol", "fractions_per_unit", "rounding_type", "rounding_precision", "format", "currency_type", "modified_by", "valid_from", "valid_to" FROM "CCCC" WHERE "valid_to" = '9999-12-31 23:59:59' ORDER BY "valid_from" DESC' failed: ERROR:  relation "CCCC" does not exist
LINE 1: ...pe", "modified_by", "valid_from", "valid_to" FROM "CCCC" WHE...

I then modified the get_schema call to ensure the schema was being set:

The log shows the schema from the table is being read but not added to the statement:

[2025-10-22 15:51:06.961035] [0x00007fa7d06b9780] [info]    Reading latest currencies.
HERE1:oresdb
[2025-10-22 15:51:06.961213] [0x00007fa7d06b9780] [info]    Query: SELECT "iso_code", "name", "numeric_code", "symbol", "fraction_symbol", "fractions_per_unit", "rounding_type", "rounding_precision", "format", "currency_type", "modified_by", "valid_from", "valid_to" FROM "currencies" WHERE "valid_to" = '9999-12-31 23:59:59' ORDER BY "valid_from" DESC
HERE1:oresdb
[2025-10-22 15:51:06.961719] [0x00007fa7d06b9780] [info]    Executing 'DECLARE sqlgen_cursor CURSOR FOR SELECT "iso_code", "name", "numeric_code", "symbol", "fraction_symbol", "fractions_per_unit", "rounding_type", "rounding_precision", "format", "currency_type", "modified_by", "valid_from", "valid_to" FROM "currencies" WHERE "valid_to" = '9999-12-31 23:59:59' ORDER BY "valid_from" DESC' failed: ERROR:  relation "currencies" does not exist
LINE 1: ...pe", "modified_by", "valid_from", "valid_to" FROM "currencie...

I'll share more updates if I get any further.

[mcraveiro]

Right, it just occurred to me that claude could probably do this a lot faster than me :-) seems like it spotted something, claude trace below verbatim. I will test a possible fix.

Extract from Claude

I've identified the problem with the missing schema names in your SQL queries. The issue is in the table_or_query_to_sql function at to_sql.cpp:749-759.

The Problem

When a SelectFrom query is serialized to SQL, the table_or_query_to_sql function (line 710) is called. Look at lines 749-759:

  std::string table_or_query_to_sql(
      const dynamic::SelectFrom::TableOrQueryType& _table_or_query) noexcept {
    return _table_or_query.visit([](const auto& _t) -> std::string {
      using Type = std::remove_cvref_t<decltype(_t)>;
      if constexpr (std::is_same_v<Type, dynamic::Table>) {
        return wrap_in_quotes(_t.name);  // ⚠️ ONLY RETURNS TABLE NAME!
      } else {
        return "(" + select_from_to_sql(*_t) + ")";
      }
    });
  }

Line 754 is the bug: It only returns wrap_in_quotes(_t.name) without checking for the schema field!

How Other Functions Handle It Correctly

Compare this to how other functions in the same file handle the schema:

1. create_index_to_sql (lines 266-269):

  if (_stmt.table.schema) {
    stream << "\"" << *_stmt.table.schema << "\".";
  }
  stream << "\"" << _stmt.table.name << "\"";

2. create_table_to_sql (lines 315-318):

  if (_stmt.table.schema) {
    stream << wrap_in_quotes(*_stmt.table.schema) << ".";
  }
  stream << wrap_in_quotes(_stmt.table.name) << " ";

3. delete_from_to_sql (lines 369-372), insert_to_sql (lines 467-470), update_to_sql (lines 864-867) all follow the same pattern.

The Fix

The table_or_query_to_sql function should be changed from:

  if constexpr (std::is_same_v<Type, dynamic::Table>) {
    return wrap_in_quotes(_t.name);

To:

  if constexpr (std::is_same_v<Type, dynamic::Table>) {
    if (_t.schema) {
      return wrap_in_quotes(*_t.schema) + "." + wrap_in_quotes(_t.name);
    }
    return wrap_in_quotes(_t.name);

This is a bug in the sqlgen library itself. The transpilation layer correctly extracts and populates the schema (as we saw in to_table_or_query.hpp:23), but the postgres SQL serialization layer ignores it for SELECT statements.

[mcraveiro]

Right this seems to address my issues. The following is a diff against current vcpkg:

diff --git a/ports/sqlgen/portfile.cmake b/ports/sqlgen/portfile.cmake
index fad688ff76..c54f007e0d 100644
--- a/ports/sqlgen/portfile.cmake
+++ b/ports/sqlgen/portfile.cmake
@@ -4,6 +4,8 @@ vcpkg_from_github(
     REF "v${VERSION}"
     SHA512 94e54f323cc92909ac83e851771cbbdccc53c073baa58962a992fa2e1cba23c963292e12a5eedee223788e74923cb07c4c1165d943ddaadec07db7adb279bd62
     HEAD_REF main
+    PATCHES
+        sqlgen-schema-fix.patch
 )
 
 if(VCPKG_TARGET_IS_WINDOWS)
diff --git a/ports/sqlgen/sqlgen-schema-fix.patch b/ports/sqlgen/sqlgen-schema-fix.patch
new file mode 100644
index 0000000000..358abe951f
--- /dev/null
+++ b/ports/sqlgen/sqlgen-schema-fix.patch
@@ -0,0 +1,12 @@
+--- sqlgen-0.3.0/src/sqlgen/postgres/to_sql.cpp	2025-10-22 16:21:09.527684431 +0100
++++ sqlgen-0.3.0/src/sqlgen/postgres/to_sql.cpp	2025-10-22 16:20:59.935751189 +0100
+@@ -751,6 +751,9 @@
+   return _table_or_query.visit([](const auto& _t) -> std::string {
+     using Type = std::remove_cvref_t<decltype(_t)>;
+     if constexpr (std::is_same_v<Type, dynamic::Table>) {
++      if (_t.schema) {
++        return wrap_in_quotes(*_t.schema) + "." + wrap_in_quotes(_t.name);
++      }
+       return wrap_in_quotes(_t.name);
+     } else {
+       return "(" + select_from_to_sql(*_t) + ")";

For now it solves my problem. I will separately raise a PR for this.

[mcraveiro]

Proposed a PR which I believe addresses this issue. Tested it locally (via the vcpkg patch above) and solve my problem. Let me know what you think.