OrmLite Migration issue with schema between Sqlite, SqlServer and Postgres

ServiceStack.OrmLite
1

I am trying to use the OrmLite Code-first Migrations and using Sqlite, SqlServer and Postgres databases but the behaviour of the following code works differently between Sql Server and Postgres:

typeof(Booking)
    .AddAttributes(new SchemaAttribute("quay"));
var postgresDialectProvider = PostgreSqlDialect.Provider;
postgresDialectProvider.NamingStrategy = new PostgresNamingStrategy();
services.AddSingleton<IDbConnectionFactory>(new OrmLiteConnectionFactory(
    databaseConfig.ConnectionString, postgresDialectProvider));

The SqlServer connection respects the setting of the schema, but the Postgres one does not.

Is there something else I need to set the default schema used by Postgres?

2

Another strategy I have tried to use is to set the as below

[Schema("quay")]
public class Booking : AuditBase
{
...
}

This works great for SqlServer and Postgres consistently for my user-defined tables but I still have to use the following:

typeof(ApiKeysFeature.ApiKey)
    AddAttributes(new SchemaAttribute("quay"));
typeof(CrudEvent)
    AddAttributes(new SchemaAttribute("quay"));
typeof(Migration)
    .AddAttributes(new SchemaAttribute("quay"));

for the tables that are OrmLite defined.

The Sqlite OrmLite provider also does something a bit weird by creating the user-defined tables with a _quay table name prefix, but the OrmLite defined ones do not have this prefix:

image
image908Ă—462 26 KB

Ideally, I would like to use the [Schema("quay")] attribute and have SqlServer and Postgres have the tables end up in the correct schema and then just override or remove the schema name from the Sqlite tables for the user-defined tables (the same as the OrmLite defined tables are appearing).

For example, I want the tables in Sqlite to all be similar to the following (wihout a quay_ schema table prefix):

ApiKey
Booking
QgisProject

I have been switching between methods to override the schema name or attribute class with it, but the inconsistency between schema handling between Sqlite, SqlServer and Postgres in OrmLite has me scratching my head!?

3

Schemas behave differently across different RDBMS’s where SQLite and MySql don’t support it so they’re included in the table name instead. If you want portable code across RDBMS’s I wouldn’t be using Schemas.

Otherwise you’re going to write RDBMS specific code to avoid registering to use Schemas, e.g:

if (OrmLiteConfig.DialectProvider is not SqliteOrmLiteDialectProviderBase)
{
    typeof(ApiKeysFeature.ApiKey)
        AddAttributes(new SchemaAttribute("quay"));
    //...
}
1 Like