PostgreSQL as Database Provider

[snovak7]

Hello, how is support for the PostgreSQL? There is good support with Npgsql, and even with EF Core.

[tidyui]

The entire core library should work fine with Postgres, however I'm not sure with the AspNetCore.Identity package as Microsoft has chosen to implement it in a way that is not database independent (that's why there are separate packages for SQLite, SqlServer and MySQL for Identity). Maybe @alperencaliskan have tested this, I know he has used Piranha on PostreSQL.

[alperencaliskan]

Hi @snovak7 , i use piranha.core with postgresql on two websites and have no issues so far.

[snovak7]

Thank you @alperencaliskan

[snovak7]

@alperencaliskan in what setting are you using this? for Identity there you have many different DbContext implementations all but PostgreSQL? or do you use a generic DbContext for that ?

[alperencaliskan]

Sorry, i don't use identity, i use SimpleSecurity.
Part of startup.cs

            services.AddPiranhaEF(options =>
                 options.UseNpgsql(Configuration.GetSection("Config").GetValue<string>("SqlConnectionString")));

            services.AddPiranhaSimpleSecurity(
                new Piranha.AspNetCore.SimpleUser(Piranha.Manager.Permission.All())
                {
                    UserName = "admin",
                    Password = "admin"
                }
            );

[tidyui]

@snovak7 We probably need to add a migration package for PostgreSQL for Identity, just like @aatmmr has done for MySQL (https://github.com/PiranhaCMS/piranha.core/tree/master/core/Piranha.AspNetCore.Identity.MySQL). We would need help with this by someone who has knowledge and a development environment with PostgreSQL.

[tidyui]

For clarification, I'm thinking maybe we should create a separate repository for each database provider that needs specific migration packages and assigning an admin to that repo. For example, this means we would create a new Piranha.MySQL repo, move Piranha.AspNetCore.Identity.MySQL to that repo and assign @aatmmr as admin for it.

In order to provide full support for PostgreSQL we would need to do the same, but also get one or several people who wants to maintain and take responsibility for these repositories.

We in the core team only have the resources to provide core functionality and test everything on SQLite, SQLServer and SQLAzure. We have tried to outline this in the docs here:

http://piranhacms.org/docs/architecture/databases

If anyone active in this thread is interested in managing a PostgreSQL repo, please reach out and we'll set everything up!

[tidyui]

This is same approach as we have done with Angular with the repo https://github.com/PiranhaCMS/piranha.core.angular that is maintained by @jcphlux

[aatmmr]

@tidyui such a separation would be fine with me. Would the package names for the database providers follow the current naming convention?!

[junderhill]

@tidyui Just a heads up, I’m interested in getting full PostgreSQL compatibility in Piranha therefore I’m in the process of writing the migration package for Identity.PostgeSQL. Hopefully have a pull request in a few days once I’ve done some testing. I’d be happy to maintain this going forward

[devantler]

@junderhill I just changed to heroku as my preferred host, and they have very cheap Postgres database addon, which id like to use. So very awesome hearing you're working on it! :-D Much appreciated!

[junderhill]

@aatmmr Hi, just looking at doing the same as what you have for MySQL for PostgreSQL. Wondering if you experienced an issue with EF Core, when you worked on the MySQL library, throwing exceptions when using the two contexts are being saved? I'm seeing this when starting the example application:

System.InvalidOperationException: A second operation started on this context before a previous operation completed. This is usually caused by different threads using the same instance of DbContext, however instance members are not guaranteed to be thread safe. This could also be caused by a nested query being evaluated on the client, if this is the case rewrite the query avoiding nested invocations.
   at Microsoft.EntityFrameworkCore.Internal.ConcurrencyDetector.EnterCriticalSection()
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(IReadOnlyList`1 entriesToSave, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.DbContext.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)

edit: I've just noticed in you example repo that you're using Simple authentication as opposed to Identity so you might not have seen this

[snovak7]

You need to use await on async methods, it allows only one command per context. So no parallel operations on the database are allowed.

[junderhill]

You need to use await on async methods, it allows only one command per context. So no parallel operations on the database are allowed.

Yeah, I'm not actually saving anything in the code that I've written so far - it's just a context factory and a migration. I've also searched the entire solution for SaveChangesAsync and checked they all have await. There's something abit odd happened at the moment.

edit Ignore this actually, it seems to only happen in the example mvc project that's part of the piranha.core solution (also seems to happen using the SQLite provider). Using my new library in one of my own Piranha based projects works fine.

[aatmmr]

Hi @junderhill. I have used the example projects for the package development itself and did not see such an issue as far as I can remember.