Official support for fully fledged open source DBMS e.g. PostgreSQL

[iwis]

I propose to add the official support for a free database system that doesn't have the limitations of SQL Server Express and SQLite. This can be done for example for PostgreSQL which is the most advanced open-source DBMS.

[roji]

@iwis is there anything lacking with the Npgsql PostgreSQL provider? What benefits would there be in Microsoft providing official support for it?

There sometimes is a general, unfortunate reluctance in the .NET ecosystem to use anything that doesn't come from Microsoft; this hurts .NET open source and community projects in general.

[ajcvickers]

@iwis The open source Npgsql PostgreSQL provider for EF Core is one of the most reliable, fully-featured, and fast providers out there. It is highly recommended by the EF team.

As @roji said, with modern .NET, we are working hard to embrace the .NET ecosystem, including community driven projects like Npgsql PostgreSQL. We don't intend to implement a different provider or take over support for the community project.

[iwis]

is there anything lacking with the Npgsql PostgreSQL provider?

I asked a similar question on Reddit two months ago. People say that Postgres support is good, but still SQL Server is better supported than Postgres. Here are the answers about problems:

• "Postgres works nice, but migrations and enum changes dont play nice!" source, 4 upvotes

• "there are some hiccups when creating queries that it doesn't translate well into sql query and you'll have to fine tune your code." source 1, source 2, 2-3 upvotes

• "Having moved from SQL Server to Postgres, Postgres is just painful. The query compiler is just stupid compared to the SQL Server one." source, 2 upvotes

• "npgsql 4 has a pretty nasty bug with overflowing decimals completely breaking the connection pool though" source, 2 upvotes

In 2018, EF Core lacked many-to-many and a server-side GROUP BY implementation - it is resolved in EF Core 5. But how well are open-source DBMSs supported? Is there a list of differences in EF Core 5 support for SQL Server and PostgreSQL, e.g. similar to this list of differences in functionality between old EF 6 and different versions of EF Core. Can I easily use PostgreSQL with EF Core to build an application? If so, what applications is is good for? Is it scalable? What are the problems? Does EF Core work as well with Postgres as with SQL Server?

[iwis]

we are working hard to embrace the .NET ecosystem, including community driven projects like Npgsql PostgreSQL. We don't intend to implement a different provider or take over support for the community project.

Does this mean that using PostgreSQL instead of SQL Server in ASP.NET applications will be a good idea or maybe it is even already?

[roji]

I can respond to these point by point, but the reality is that if you look at SQL Server support, you'll be able to find a very similar kind of list; "official" Microsoft support doesn't mean that something is bug-free or even necessarily of better quality. .NET PostgreSQL support is in many ways better than SQL Server support - definitely in terms of performance, and in many ways also in terms of features and bugs.

In 2018, EF Core lacked many-to-many and a server-side GROUP BY implementation - it is resolved in EF Core 5. But how well are open-source DBMSs supported?

These features work in exactly the same way on both PG and SQL Server (they are standard SQL features rather than specific to one database or another).

Can I easily use PostgreSQL with EF Core to build an application?

I certainly think so - have you given it a try?

Regarding the rest, we could make lists and documents with EF Core differences across the providers; I think a lot of that information is already available (though not in comparative form) in the respective docs.

Stepping back, if you're interested in EF Core and PostgreSQL, I'd suggest simply giving it a try and evaluating it for yourself, rather than concentrating on whether something is officially supported by Microsoft or not. I'd be very happy to hear about your experience and to see specific issues about problems you encounter.

[roji]

Does this mean that using PostgreSQL instead of SQL Server in ASP.NET applications will be a good idea or maybe it is even already?

It definitely works, and lots of users are doing it. Whether it's a good idea for you specifically to use PostgreSQL instead of SQL Server in your particular application (ASP.NET or not) is a separate question which depends on your specific needs.

[iwis]

we could make lists and documents with EF Core differences across the providers; I think a lot of that information is already available (though not in comparative form) in the respective docs.

I would love to see this.

Can I easily use PostgreSQL with EF Core to build an application?

I certainly think so - have you given it a try?

I tried in 2018, but the lack of many-to-many, and lack of GROUP BY performed in DBMS was a signal for me that it isn't as ready as for example Java. Now I would rather wait till it is more popular or till others describe their experience with using it. Maybe you know some articles about such experience? Also do you know websites, books, blogs that describe the use of PostgreSQL in ASP.NET Core 5/EF Core 5 applications?

[roji]

I tried in 2018, but the lack of many-to-many, and lack of GROUP BY performed in DBMS was a signal for me that it isn't as ready as for example Java.

So again, many-to-many and GROUP BY support have nothing to do with specific databases; when they didn't work in PostgreSQL, they didn't work in SQL Server either.

I've seen various articles and blog posts on EF Core and PostgreSQL over time, but I don't have anything specific to point you at - I'm sure some searching would help. In general, I wouldn't concentrate on ASP.NET here too much, since that's just the framework and not directly related to which database you're using. ASP.NET Identity is one such direct point of contact, since EF Core stores user information in the database. However, I know that works quite well with PostgreSQL (one sometimes just has to delete the migrations which come out of the box, since those can be SQL Server-specific).

[iwis]

So again, many-to-many and GROUP BY support have nothing to do with specific databases; when they didn't work in PostgreSQL, they didn't work in SQL Server either.

I know, I know, I only mean that EF Core had problems then, so I am reluctant to test it again without reading articles/books stating that EF Core can work with Postgres without problems. I will search for them again. The last time I searched for them in the last year but for sure EF Core 5 was too new then, and this was the reason I didn't found anything.

I wouldn't concentrate on ASP.NET here too much

Sure. I only mean that I am interested in web applications.