Npgsql.PostgresException syntax error at or near "["

[MangelMaxime]

Hello @JordanMarr,

in our project we are moving away from SQLServer to go to PostgreSQL.

I installed SqlHydra.Npgsql tool and generated the new database files.

I also installed Npgsql and removed the System.Data.SqlClient library.

My project is now compiling how when running the tests I get the following error:

System.AggregateException: One or more errors occurred. (42601: syntax error at or near "["

POSITION: 13)
 ---> Npgsql.PostgresException (0x80004005): 42601: syntax error at or near "["

POSITION: 13
   at Npgsql.Internal.NpgsqlConnector.<ReadMessage>g__ReadMessageLong|225_0(NpgsqlConnector connector, Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteNonQuery(Boolean async, CancellationToken cancellationToken)
  Exception data:
    Severity: ERROR
    SqlState: 42601
    MessageText: syntax error at or near "["
    Position: 13
    File: scan.l
    Line: 1176
    Routine: scanner_yyerror
   --- End of inner exception stack trace ---

I feel like the [ syntax error is because the generated SQL is for SQLServer instead of PostgreSQL.

The query which trigger the error is:

deleteAsync (Shared ctx) {
    for _ in table<dbo.EncounterForms> do
        deleteAll
}

Is there something I am missing? Is it possible to see the generated SQL by SQL Hydra to try understand the problem?

[MangelMaxime]

Do I need to set a configuration option somewhere for SQLKata to generate be aware that I am using PostgreSQL?

[MangelMaxime]

Oh...

I forgot to change SqlKata.Compilers.SqlServerCompiler() to SqlKata.Compilers.PostgresCompiler().

Got bitten once again by the openContext function 😅

[JordanMarr]

Glad you found the cause! Btw, you can output the SQL with this helper function:

let toSql (query: SqlKata.Query) = 
    let compiler = SqlKata.Compilers.PostgresCompiler()
    compiler.Compile(query).Sql

Example:

let query =
    delete {
        for _ in table<dbo.EncounterForms> do
        deleteAll
    }

    query.ToKataQuery() |> toSql |> printfn "%s"

[MangelMaxime]

Thank you for the utility function, I never had to check the generate code yet but could be handy one day.

[JordanMarr]

Btw, SqlHydra has some nice features for Npgsql:

• Support for arrays
• Support for json / jsonb
• Support for enums
• "Upsert" support via open SqlHydra.Query.NpgsqlExtensions:
  • OnConflictDoUpdate
  • OnConflictDoNothing

[MangelMaxime]

I saw this additional features indeed, and will definitely look into them.

We are already interesting in the Enum stuff to avoid having to create manual mapping between our DUs and "types table" to enforce the integrity.

[MangelMaxime]

Do you know if SqlHydra support arrays of enums too?

[JordanMarr]

I haven’t tried that specifically, but i don’t see any reason why it wouldn’t work since the array types are not hardcoded.