How to reverse engineer Postgres 9.0 database

Question

ASP.NET Core 6 MVC application uses EF Core with Npgsql.

Reverse engineering Postgres 9.0 database using

scaffolder.ScaffoldModel(connectionString.ToString(), dbOpts, modelOpts, codeGenOpts);

Throws exception

Npgsql.PostgresException (0x80004005): 42703: column "enumsortorder" does not exist

at sql

SELECT
  nspname,
  typname,
  array_agg(enumlabel ORDER BY enumsortorder) AS labels
FROM pg_enum
JOIN pg_type ON pg_type.oid = enumtypid
JOIN pg_namespace ON pg_namespace.oid = pg_type.typnamespace
GROUP BY nspname, typname

with stack trace

at Npgsql.Internal.NpgsqlConnector.g__ReadMessageLong|213_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() at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken) at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken) at Npgsql.EntityFrameworkCore.PostgreSQL.Scaffolding.Internal.NpgsqlDatabaseModelFactory.GetEnums(NpgsqlConnection connection, DatabaseModel databaseModel) at Npgsql.EntityFrameworkCore.PostgreSQL.Scaffolding.Internal.NpgsqlDatabaseModelFactory.Create(DbConnection dbConnection, DatabaseModelFactoryOptions options) at Npgsql.EntityFrameworkCore.PostgreSQL.Scaffolding.Internal.NpgsqlDatabaseModelFactory.Create(String connectionString, DatabaseModelFactoryOptions options) at Microsoft.EntityFrameworkCore.Scaffolding.Internal.ReverseEngineerScaffolder.ScaffoldModel(String connectionString, DatabaseModelFactoryOptions databaseOptions, ModelReverseEngineerOptions modelOptions, ModelCodeGenerationOptions codeOptions)

How to reverse engineer Postgres 9.0 database in .NET6 ? Enum data types are almost not used.

I would suggest backup database and restore on higher Postgres version instance and then Scaffold. Don't think that Npgsql will do that shortly. — Svyatoslav Danyliv, Dec 17 '21 at 17:46

score 0 · Accepted Answer · answered Dec 18 '21 at 11:24

I've fixed this for 6.0.2, see https://github.com/npgsql/efcore.pg/issues/2160.

It would be better if you didn't cross-post the same question on both github and stackoverflow - it mainly creates more work answering in both places. If something looks like an Npgsql bug, and issue should be enough, otherwise stackoverflow is good for usage questions etc.

How to reverse engineer Postgres 9.0 database

1 Answers1