Postgres support

[not-authorized]

Hi Rick, does it support Postgres? Are there any plans to add that feature? Thanks!

[Drizin]

I'm totally interested in adding Postgres support (and maybe MySQL), but I have to investigate the differences between a Postgres schema and a MSSQL schema (which is currently what is extracted by CodegenCS.DbSchema project). I suppose they would be quite similar, and in case I'd only have to adjust the reverse-engineering queries. Previously the project was called CodegenCS.SqlServer (MSSQL) but I thought it would be better idea to fit all different RDBMS vendors into a single schema so that they could share the same templates

Are you familiar with how Postgres schema would be different from MSSQL schema? Any input/suggestion/PR is appreciated.

I've been a little busy with other projects but I'll try to dedicate some time to this Postgres support.

[not-authorized]

Personally, I just have had to migrate MSSQL/PostgreSQL repeatedly. The differences are minor, especially in DDL. Some of them are:

• names of DB types;
• square brackets are not allowed;
• double quotes are not recommended and have to be avoided in DB objects naming;
• snake casing for DB objects naming.

Not sure if it is still relevant in the context of POCO generation. Recently I discovered EFCorePowerTools and it works perfectly fine.

[not-authorized]

Just to share my experience with EFCorePowerTools, it is not flexible enough. Every developer likely prefer to control every detail of the code generation in order to get exactly the same output project requires. For instance, I have to find some workaround to address issues like this one.

[Drizin]

Yeah, I've tried both dotnet ef scaffold and also EFCorePowerTools, and I know that they are not very flexible. I think I already have some templates that mimic dotnet ef scaffold - I'll try to publish it over the weekend - it's very easy to extend.

I'll also try to create a schema extractor for Postgres.

PS: You're right - when I asked about differences between MSSQL and Postgres I meant differences in their structure (e.g. db types) - the DDL doesn't matter since it's about generating C# (and sometimes basic CRUD but it's mostly identical)

[Drizin]

Hey @not-authorized , I have just commited a script which extracts the schema from a PostgreSQL database.

Can you check if it works for you? You should invoke through the PS1 script, but the CSX has the connection string.

For some reason it's not running for me - I'll investigate tomorrow - currently I'm getting this error: csi.exe : System.TypeInitializationException: The type initializer for 'Npgsql.TypeMapping.GlobalTypeMapper' threw an exception.

[Drizin]

I think it's working now, can you check? It's still a draft, but maybe you can give me some inputs, maybe fix. I basically copied from MSSQL, so probably I'm missing some Npgsql types

[Drizin]

I've made some improvements. It's still a draft (I haven't tested, I'm just publishing some work in progress which was on hold for many months) but it may be a good starting point. Now instead of Powershell it's all based on console apps: there's a console app for extracting schema from a PostgreSQL database and another console app to transform this schema into EFCore entities and DbContext. Let me know your thoughts.

[not-authorized]

Hey @Drizin, I was trying to run the CodegenCS.DbSchema.Extractor (Postgres) and constantly get TimeoutException: CodegenCS.DbSchema.Extractor\PostgreSQL\PgsqlSchemaReader.cs line: 82.

Re: Npgsql types mapping, you can use this spec.

[Drizin]

@not-authorized Can you try to run the query manually in your database to see if it works and how long does it take? You can increase the timeout using the commandTimeout parameter. Maybe I made something wrong in the query (although in my test database it seemed to work fine).

[not-authorized]

I'm using Azure PostgreSQL service and for some reason querying the information_schema.columns takes too long. Investigating this issue.

[not-authorized]

The request you made is too vague and contains a big overhead. Azure has a lot of system tables that we are not interested in. Consider rewriting the request as follows:

select t.table_schema as schema_name,
       t.table_name,
       c.column_name,
       c.data_type,
       case when c.character_maximum_length is not null
            then c.character_maximum_length
            else c.numeric_precision end as max_length,
       is_nullable
       from information_schema.tables t
    left join information_schema.columns c 
              on t.table_schema = c.table_schema 
              and t.table_name = c.table_name
where t.table_schema in ('my_schema1', 'my_schema2')
order by schema_name,
         view_name;

[Drizin]

Unfortunately Entity Framework requires a lot of other information. If we filter-out the system tables does the query work faster?

[not-authorized]

Yes. This query took about 30 sec. I think the trick is limiting the schemas we request t.table_schema in ('my_schema1', 'my_schema2') Take a look at the information_schema of Azure Database for PostgreSQL, my guess - it is much different from the on-premise version. Also Azure may restrict access to some parts of information_schema since the default user has only limited permissions.