Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Add Support for Postgresql #207

Closed
linkdotnet opened this issue Apr 28, 2023 · 18 comments
Closed

Add Support for Postgresql #207

linkdotnet opened this issue Apr 28, 2023 · 18 comments
Labels
enhancement New feature or request help wanted Extra attention is needed

Comments

@linkdotnet
Copy link
Owner

Add Postgresql support (for example via Entity Framework).

@linkdotnet linkdotnet added enhancement New feature or request help wanted Extra attention is needed good first issue Good for newcomers labels Apr 28, 2023
@diegodrf
Copy link

Hi @linkdotnet, I would like to help you.
I don't have experience acting in open-source projects, but I would like to start.

Could you give me details about the issue? It's just to add the support for Postgresql or did you try before and find incompatibilities?

@linkdotnet
Copy link
Owner Author

Hey @diegodrf - cool, help is always appreciated.

I did not try until now with PostgreSQL, but as the blog isn't using anything exotic, I don't expect much trouble here.

There are multiple ways to solve the issue. The easiest way is to use Entity Framework Provider. There you already have a repository and all the code around. See Infrastructure.

So adding the package will almost do everything for you. You just have to add the new provider in PersistenceProvider and create a new PostgreSQL extension in the RegistrationExtensions namespace.

The more complicated way is not to use Entity Framework and use something else. For this you would have to implement your own IRepository. You could have a look at how the SqlRepository does it at the moment, but that is up to you.

The general workflow looks like this:

  1. In the appsettings.json you can define your storage provider - the string has to be the same as in the PersistenceProvider class
  2. On Startup the blog checks for this and calls the StorageProviderExtensions, which you would add the new PostgreSQL type, and registers all the necessary types.
  3. The components just get injected the IRepository<T> so they don't know what underlying technology is used.

If you need more details let me know. You can also just start, open a PR and ask your questions if this is easier for you

@linkdotnet linkdotnet linked a pull request Jun 24, 2023 that will close this issue
@linkdotnet linkdotnet removed the good first issue Good for newcomers label Nov 8, 2024
@EliasMasche
Copy link
Contributor

Hello there, Is this issue still open today I discovered this project

@linkdotnet
Copy link
Owner Author

There were some attempts but this is currently open and up for grabs if you like to tackle it.

@EliasMasche
Copy link
Contributor

Thanks for the reply, I will take a look during the weekend and my free time and use the earlier comment details you wrote.

@linkdotnet
Copy link
Owner Author

Thanks - if you have any question, just start shooting ;)

@EliasMasche
Copy link
Contributor

question, for support of postgresql do I aim with net 9.0 or any specific version?

@linkdotnet
Copy link
Owner Author

net9.0 and EF 9. I merged a PR enabling EF 9 just some seconds ago.

@EliasMasche
Copy link
Contributor

Thanks will update my devenv with both and do a fork of the repo and another question do I keep using this issue thread about Postgre or I go with new one?

@linkdotnet
Copy link
Owner Author

Thanks will update my devenv with both and do a fork of the repo and another question do I keep using this issue thread about Postgre or I go with new one?

I would keep them here if they are related. Maybe it helps others as well.

@EliasMasche
Copy link
Contributor

EliasMasche commented Jan 5, 2025

Hello, on my fork I was able to include support for PostgreSQL,

I can build and run but found this exception when trying to save a blog into the DB it comes from public async ValueTask StoreAsync(TEntity entity) inside Repository.cs

Microsoft.EntityFrameworkCore.DbUpdateException HResult=0x80131500 Message=An error occurred while saving the entity changes. See the inner exception for details. Source=Microsoft.EntityFrameworkCore.Relational StackTrace: at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.d__50.MoveNext() at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.d__9.MoveNext() at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.d__9.MoveNext() at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.d__9.MoveNext() at Microsoft.EntityFrameworkCore.Storage.RelationalDatabase.d__8.MoveNext() at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.d__111.MoveNext() at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.d__115.MoveNext() at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlExecutionStrategy.d__7`2.MoveNext() at Microsoft.EntityFrameworkCore.DbContext.d__63.MoveNext() at Microsoft.EntityFrameworkCore.DbContext.d__63.MoveNext() at LinkDotNet.Blog.Infrastructure.Persistence.Sql.Repository`1.d__7.MoveNext() in F:\Dev\CSharp\Blog\src\LinkDotNet.Blog.Infrastructure\Persistence\Sql\Repository.cs:line 93 at LinkDotNet.Blog.Infrastructure.Persistence.Sql.Repository`1.d__7.MoveNext() in F:\Dev\CSharp\Blog\src\LinkDotNet.Blog.Infrastructure\Persistence\Sql\Repository.cs:line 93 at LinkDotNet.Blog.Infrastructure.Persistence.CachedRepository`1.d__7.MoveNext() in F:\Dev\CSharp\Blog\src\LinkDotNet.Blog.Infrastructure\Persistence\CachedRepository.cs:line 52 at LinkDotNet.Blog.Web.Features.Admin.BlogPostEditor.CreateBlogPost.d__1.MoveNext() in F:\Dev\CSharp\Blog\src\LinkDotNet.Blog.Web\Features\Admin\BlogPostEditor\CreateBlogPost.razor:line 14 at Microsoft.AspNetCore.Components.ComponentBase.d__30.MoveNext()

This exception was originally thrown at this call stack:
[External Code]

Inner Exception 1:
ArgumentException: Cannot write DateTime with Kind=Local to PostgreSQL type 'timestamp with time zone', only UTC is supported. Note that it's not possible to mix DateTimes with different Kinds in an array, range, or multirange. (Parameter 'value')

Error Exception

The error happens because Npgsql changed his behavior of mapping Date and time creating a breaking change:
https://www.npgsql.org/doc/types/datetime.html

So it requires to use this line of code to use the legacy behavior

AppContext.SetSwitch("Npgsql.EnableLegacyTimestampBehavior", true);

Or to update with the new mapping or use their NodaTime Plugin:

https://www.npgsql.org/doc/types/nodatime.html?tabs=datasource

@linkdotnet
Copy link
Owner Author

Hey @EliasMasche,

Thanks for the update. Hmm I see the problem. Well, there are two ways of solving this.

  1. AppContext.SetSwitch("Npgsql.EnableLegacyTimestampBehavior", true);: Probably best suited in the StorageProviderExtensions.AddStorageProvider when you add the Postgres Stuff.
  2. Saving our dates as UTC. That might be anyway the best option. Currently, everything is local (server) time. That isn't really good to be honest! When dates are saved, we could either use DateTimeOffset and do a migration or just define UTC as kind for DateTime.

I do think the latter one, is the way to go.

@EliasMasche
Copy link
Contributor

Another issue that can and will show up with local (server) time is that in my case, I have different servers(on-premise and cloud) in different timezones, so if I migrate or restore a backup, it will provoke issues with different times.

How it will work using migration with DateTimeOffset? I don't have experience using these two but I am for defining UTC as kind for DateTime

@linkdotnet
Copy link
Owner Author

How it will work using migration with DateTimeOffset? I don't have experience using these two but I am for defining UTC as kind for DateTime

Basically the UpdatedDate and ScheduledPublishDate will become DateTimeOffset with all their usages.
This has to be also reflected in the data model (EF Configuration).

But for now - saving everything as UTC is the best option (both of these values). Maybe some adoptions in the background services have to be made as well and how this information is presented to the user. But I can take over that part.

@EliasMasche
Copy link
Contributor

So I create a PR with the initial support of PostgreSQL and from there you continue with the necessary changes?

@linkdotnet
Copy link
Owner Author

So I create a PR with the initial support of PostgreSQL and from there you continue with the necessary changes?

Indeed. The changes will be small, so that is absolutely fine.

@EliasMasche
Copy link
Contributor

Created the PR

@linkdotnet
Copy link
Owner Author

Done by @EliasMasche in #399

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
enhancement New feature or request help wanted Extra attention is needed
Projects
None yet
Development

Successfully merging a pull request may close this issue.

3 participants