ReadModelSqlGenerator Column Prefix and Suffix - Not Used in Select Query

[VladislavRybnikov]

Hi!

I am using PostgreSQL read models and discovered an issue with the column names.

STR

1. I created all tables with double-quoted values for table and column names using FluentMigrator (this is the default FluentMigrator PostgreSQl configuration).
2. I registered PostgreSql readModels using ConfigurePostgreSql and UsePostgreSqlReadModel
3. I added [PostgreSqlReadModelIdentityColumn] attribute to ReadModel's property Id

AR
When working with read models I am receiving an error from PostgreSQL that column id does not exists

ER
Should work well with such configuration by default OR should be configured by registering custom ReadModelSqlGenerator:

internal sealed class CustomPostgresReadModelSqlGenerator : ReadModelSqlGenerator
{
    public CustomPostgresReadModelSqlGenerator()
        : base(new ReadModelSqlGeneratorConfiguration(
            tableQuotedIdentifierPrefix: "\"",
            tableQuotedIdentifierSuffix: "\"",
            columnQuotedIdentifierPrefix: "\"",
            columnQuotedIdentifierSuffix: "\""))
    {
    }
}

But even registration of this custom ReadModelSqlGenerator does not help, because probably there is a bug in ReadModelSqlGenerator.

EventFlow/Source/EventFlow.Sql/ReadModels/ReadModelSqlGenerator.cs

Line 105 in cf4287b

sql = $"SELECT * FROM {tableName} WHERE {identityColumn} = @EventFlowReadModelId";

- on this line prefix and suffix - not used at all, so probably it is a bug, because they are used here -

EventFlow/Source/EventFlow.Sql/ReadModels/ReadModelSqlGenerator.cs

Line 149 in cf4287b

sql = $"UPDATE {tableName} SET {updateColumns} WHERE {Configuration.ColumnQuotedIdentifierPrefix}{identityColumn}{Configuration.ColumnQuotedIdentifierSuffix} = @{identityColumn} {versionCheck}";

.

Also, there is a question, why columnQuotedIdentifierPrefix: "\"" and columnQuotedIdentifierSuffix: "\"" - not used by default for PostgreSql configuration?

[github-actions]

Hello there!

We hope you are doing well. We noticed that this issue has not seen any activity in the past 90 days.
We consider this issue to be stale and will be closing it within the next seven days.

If you still require assistance with this issue, please feel free to reopen it or create a new issue.

Thank you for your understanding and cooperation.

Best regards,
EventFlow

[rasmus]

.analyze

[github-actions]

Findings

• CreateSelectSql currently ignores the configured column identifier quoting: it emits WHERE {identityColumn} = ... using the raw property name (Id, AggregateId, etc.) without prefix/suffix injection (Source/EventFlow.Sql/ReadModels/ReadModelSqlGenerator.cs:92-105). When PostgreSQL tables are created with double-quoted identifiers (e.g., "Id"), the generated SQL is lowered to id, triggering the reported column "id" does not exist.
• The Postgres-specific generator deliberately sets columnQuotedIdentifierPrefix/Suffix to empty strings (Source/EventFlow.PostgreSql/ReadModels/PostgresReadModelSqlGenerator.cs:27-31) so that legacy schemas with unquoted, all-lowercase column names continue to work. Because CreateSelectSql disregards the configuration, providing a custom generator (as in the issue body) has no effect on SELECT statements, even though DELETE/UPDATE do honor the configuration.

Reproduction

• Define a PostgreSQL read model with an identity property marked [PostgreSqlReadModelIdentityColumn] (e.g., Id).
• Create the backing table via FluentMigrator (or any tooling) that emits quoted identifiers:
  CREATE TABLE "ReadModel-Example" ("Id" uuid PRIMARY KEY, ...);
• Register the standard Postgres read model store via ConfigurePostgreSql().UsePostgreSqlReadModel<...>().
• Execute any read-model fetch/update path; the generated SQL becomes SELECT * FROM "ReadModel-Example" WHERE Id = @EventFlowReadModelId, which PostgreSQL rewrites to id, producing 42703: column Id does not exist.

Fix Ideas

• Quote identity columns in CreateSelectSql just like the other CRUD statements, e.g.

  var quotedIdentity = $"{Configuration.ColumnQuotedIdentifierPrefix}{identityColumn}{Configuration.ColumnQuotedIdentifierSuffix}";
  sql = $"SELECT * FROM {tableName} WHERE {quotedIdentity} = @EventFlowReadModelId";

  This makes custom configurations (including the issue reporter’s) effective without breaking existing schemas—because the default Postgres generator keeps prefix/suffix empty, nothing changes for today’s users.
• Add a regression test alongside CreateSelectSql_ProducesCorrectSql (Source/EventFlow.Sql.Tests/UnitTests/ReadModels/ReadModelSqlGeneratorTests.cs:49-61) that covers a generator initialized with non-empty column quotes to ensure SELECT honors the configuration going forward.

On the default Postgres configuration

• We keep columnQuotedIdentifierPrefix empty by default to preserve compatibility with the sample DDL and existing migrations that create unquoted lowercase columns. Enabling double quotes out of the box would immediately break those installations because WHERE "AggregateId" would no longer match aggregateid. With the SELECT bug fixed, teams that rely on quoted identifiers can simply override the generator configuration, while the default behavior remains backward compatible.

Your friendly neighborhood AI