Postgresql support

[marklonquist]

Hi

Considering the move to .NET5(+), is there any plans to also introduce a more Linux (and perhaps Cloud) friendly database?

Is the Umbraco team willing to take ownership of a potential pull request adding support for postgres?

[p-m-j]

Edit: My $0.02

I cannot wait for the day we have postgres support, I personally want to deploy sites to DO/Linode with a managed postgres instance so I don't have to think about db backups.

Right now we lack the capacity internally to support another database engine on top of SQL Server and SQLite (esp maintenance and testing).

In the future with a little schema tidy up and some additional tests I hope this could be part of the Umbraco codebase.

But for now perhaps a community maintained implemenation is possible? for v10 it's easier than ever to add additional databases just by adding a package ref (that's how it works now for all of our supported engines) The Umbraco.Cms.Persistence.Sqlite project would be a good starting point for inspiration especially given that SQLite syntax is inspired by postgres.

Edit: And for distributed locking setup etc that would presumably be much more like Umbraco.Cms.Persistence.SqlServer.

[dlidstrom]

I've successfully built a persistence provider for PostgreSQL. I was able to start the system and get tables created and data initialized. What I had to do was:

• create a new syntax provider starting from Sqlite, and picking from Sql Server for some methods
• update the dto classes with corrected casing for relationships and primary key
• fix a large amount of sql to correctly quote mixedCase columns, as PostgreSQL will turn them into lowercase if not
• pass parameters instead of specifying active = 0 for boolean columns as PostgreSQL wants true/false (NPoco handles it of course)
• there are no tests.. but I've added "experimental" to the provider name

There are surely places left where there needs to be parameters passed instead of above mentioned issue with boolean columns. Those should be easy fixes though. As for the distributed locking, I've copied from Sql Server implementation and done some adjustments. Someone more knowledgable should have a look at it.

You can try it out using this repo: https://github.com/dlidstrom/Umbraco-CMS/tree/persistence-postgres

Let me know what you think!

[marklonquist]

I've successfully built a persistence provider for PostgreSQL. I was able to start the system and get tables created and data initialized. What I had to do was:

• create a new syntax provider starting from Sqlite, and picking from Sql Server for some methods

• update the dto classes with corrected casing for relationships and primary key

• fix a large amount of sql to correctly quote mixedCase columns, as PostgreSQL will turn them into lowercase if not

• pass parameters instead of specifying active = 0 for boolean columns as PostgreSQL wants true/false (NPoco handles it of course)

• there are no tests.. but I've added "experimental" to the provider name

There are surely places left where there needs to be parameters passed instead of above mentioned issue with boolean columns. Those should be easy fixes though. As for the distributed locking, I've copied from Sql Server implementation and done some adjustments. Someone more knowledgable should have a look at it.

You can try it out using this repo: https://github.com/dlidstrom/Umbraco-CMS/tree/persistence-postgres

Let me know what you think!

This is awesome! I will try it asap.

[dlidstrom]

I just pushed a new commit that fixes a whole bunch of use cases that I hadn't been able to try yet. A lot of Umbraco works fine!
However, I think the approach I've had isn't going to work. I've been quoting all mixedCase identifiers to make it work. That is quite a lot of work and it is stricter than Sql Server and Sqlite, which both are case insensitive. I'm currently thinking about an approach to turn all queries into lowercase by default. Probably by overriding some part of the ADO.NET provider.

[nul800sebastiaan]

Wow this does look very interesting @dlidstrom!

Not trying to ruin your party, but at the moment we don't have the resources to take in and maintain another data layer.

I was wondering if we would be able to figure out a way to make this a plugin provider instead. I had a quick look and I see this work unfortunately requires a lot of changes to, as you say, mixed case scenarios so it's touching a lot of the core code.

To be fair, I don't think lowercasing all our queries should be a problem, at least for SQL / SQLite but it would be if someone ever wanted to build an external MySQL provider.

Additionally, making such large changes in the database schema is a little difficult for 2 reasons:

• We need to introduce migrations for each change so that people can upgrade
• Migrating simple sites is easy and quick but once that migration hits your live site, with 80000 nodes and 1.8 gigs of data, it's going to take time.. and hopefully it doesn't time out

However, it seems like the biggest, required changes are changing the Id / NodeId columns, it could be that those are not too bad (at least as I understand from the team at HQ).

So mostly I'm here to ask: what needs to be done in the core of Umbraco to be able to support you in being able to make a pluggable database provider?

[dlidstrom]

Hi Sebastiaan, thanks for weighing in. I did get the message that there can not be a
commitment at this time. That is fine, if we can solve this in some other way. I
started on this task in order to get the ball rolling, so to speak. Well now we
know more about what is necessary. I believe that if queries can be turned into
lowercase by configuration or overrides in the PostgreSQL persistence
implementation then the impact on the rest of Umbraco will be minimal. And the
schema will be lowercase only for PostgreSQL, not for any other persistence
implementation. I.e. no changes are necessary for existing databases.

The issues I've identified are these:

• PostgreSQL will be case sensitive for all quoted identifiers. We want case
  insensitivity, and that is achieved by not quoting. Problem is, NPoco as used
  in Umbraco will quote all identifiers. I noted that this behaviour in NPoco
  might be configurable in PostgreSQL persistence implementation. I've yet to
  figure out how to apply NPoco customizations but hopefully it can be done in
  the persistence implementation itself.
• Some quoting needs to be adjusted from being hardcoded (using [] as I've
  seen in some places) to instead defer to the SqlSyntax. It's an easy, one-off
  change, and everyone uses the SqlSyntax for quoting needs going forward.
• boolean sql operators can not be hardcoded to, for example active=1, but
  should rely on NPoco bool support (i.e. active=@true_ w/ extra parameter).
• SqlSyntax in PostgreSQL persistence implementation will not add quotes when
  asked to quote identifiers (just an id operation x => x).

With these issues fixed (which shouldn't touch much of Umbraco codebase) it
might be all that is needed. PostgreSQL will lowercase anything not quoted
meaning the schema will be all lowercase and any casing in queries will be
lowercased meaning casing problem is solved.

I will set out to prove or disprove this theory next.

So, while this work can be done in a separate library, as has been suggested, it
will create (IMHO) unnecessary difficulties (regarding versioning, for example,
see Semantic versioning sucks because humans get involved).

What would you say about this (wild?) idea? Let new persistence implementations
fall under the experimental (and unsupported) category and hide them from
selection by default. Only people with a strong urge (like myself, and hopefully
many others) can enable them with a configuration setting. That would make it a
lot easier to correlate fixes in the persistence implementation with the Umbraco
version and it would just be so much simpler. It would also mean that
experimental persistence libraries are ready to be integrated with Umbraco
codebase at all times (for example, working on test cases, or doing simple
incremental changes would be easy, with minimal risk of breakage). When time
comes and the test cases have sufficient coverage, the implementation has proven
itself, and can be supported, then it would elevate to supported status and be
selectable by default. Having a NuGet library would not enable this scenario
as easily and would require an eventual future integration to be an uncertain
amount of work. Having a NuGet library available might also even actively
discourage future integration meaning perpetual catching up. Only downside I can
see to this would be having an extra project with 20, mostly small, source files
to compile as part of Umbraco solution along with a configuration setting. Even
the documentation needs no changes (it merely states 8. Follow instructions on the installer).
Sorry if I am completely off-base when it comes to Umbraco development practices
and needs...

Thank you for your consideration.

[dlidstrom]

Just to let you know, for everyone who follows this discussion, that I am now able to run Umbraco with PostgreSQL on my mac having done minimal changes to core Umbraco itself. Apart from some small prerequisite change, everything is contained in the new persistence implementation. A big thanks to the amazing forethought of both Umbraco and NPoco developers to put in extension points for all overrides that were needed!
If you want to try this out, check out https://github.com/dlidstrom/Umbraco-CMS/tree/new-project. I've opened up PR #13277 for the prerequisite changes to Umbraco core v11.

[dlidstrom]

Umbraco seems to slowly be moving to Entity Framework. I had system up and running with PostgreSQL, but it was decided they didn’t want to go in that direction (then). Maybe sometime in the future? In the meantime, SQLite is properly supported and will take you further than you might think.

[karimkod]

Cool, my issue is that I already have my system on Postgresql and I don't want to manage 2 databases.

[idseefeld]

I am currently working on PRs to fix inconsistent use of NPoco. That leads to issues with a PostgreSQL provider.
When this is merged I'll finish a package for PostgreSQL support. Currently Umbraco seems not to plan to support further databases. But I think especially PostgreSQL is worth the effort, because it is a truly open source cross platform database and capable of enterprise needs. Also it will prove any flaw in hard code sql statement, which are causing the issues.
Of course such a package shall need contributions to survive.

[dlidstrom]

Agree PostgreSQL makes 100% sense (to people like you and me). My PR was intended exactly for this purpose: fix NPoco inconsistent usage. It was rejected (maybe due to business reasons?)… just saying so you know what to expect, most likely. Good luck 🤞🏻

[marklonquist]

One would assume it would be easier to go to Postgres after Entity Framework is introduced.

[idseefeld]

I am not sure they plan to move the persistence layer completely to EF in near future. Currently it is only used for the OpenId implementation. And probably this would be a quite big task. I think @dlidstrom has described the way to go quite well even when his solution is outdated.
I have also just realised, that it will be an ongoing task to fix the core as the current test based on SQLite and Sql Server are not sufficient.
And the missing awareness (at least that's my impression) in the core team about using strikt NPoco methods leads to new issues regularly.
So, I think the best approach would be to extend the test for PostgreSQL providers (yes currently we need two because of partial EF).
Maybe then Umbraco could be convinced to take over and integrate PostgreSQL support in the core.
As Azure provides managed PostgreSQL database it should be possible for Umbraco Cloud too leverage this integration too.
What do you think?

Anybody willing to contribute?
You may have a look into my fork https://github.com/idseefeld/Umbraco-CMS
Especially branch https://github.com/idseefeld/Umbraco-CMS/tree/v17/fix-sql-syntax-issues-postgreSQL-updated

[nul800sebastiaan]

I had a quick skim over the changes here, and maybe I am misunderstanding but it seems like test coverage will become less by excluding the Management API test from running at all? Again, I might have misunderstood completely.

[nul800sebastiaan]

At Umbraco HQ we're still on our way to move to EFCore fully, but we've had some obvious other major rewrites to do in the past few years. The EFCore move is one of the focus areas for the next few months but we're in the exploratory phase and don't have any concrete roadmaps yet. We'll announce them when we do of course.

We're applauding initiatives to improve any inconsistent use of NPoco and maybe that could even lead to a package that can offer full PostGreSQL support through NPoco.

Any PRs that people want to make at this time to improve the NPoco implementation, we would ask to keep the PRs small and focused so that we can be clear they are improvements and confident they aren't breaking anything.

[idseefeld]

@nul800sebastiaan Oh, that's good to know.
In the past month I have put a lot of time in the regarding PRs and @AndyButland did a great job in reviewing them.
But currently I have to focus on other priorities - finding a new job - and don't know how much time I can spend in further PRs.
I agree these have to be small and focused.
It would be great when core team would have an eye on proper usage of NPoco or SqlSyntax methods when sql statements have to be used.
Hopefully my efforts (especially for persistence DTOs) will help you in transition to EFCore.