Possibility of different database instead of SQL Server

[snovak7]

As far as I know EntityFrameworkCore & EF in general supports other databases, is there a case I can use different, and how, I noticed there's only ConnectionString that is passed to "AddCofoundry"

Is it possible to like in https://github.com/cofoundry-cms/cofoundry/wiki/Entity-Framework-&-DbContext-Tools to custom set your DbContextOptionsBuilder with my instance for Npsql ... UseNpsql instead of UseSqlServer.

In such case it would be nice to have a Migrations, because it can be done for other types of databases. Haven't looked at the SQL Scripts, for generating Cofoundry database.

[HeyJoel]

It is not possible at this time to use a different database such as postgres with Cofoundry. Although much of the data access uses EF Core, we use raw scripts for installation and migration, and there are a few SPs and optimizations where EF Core isn't up to the job.

It is possible that in the future we could abstract these parts and allow other db providers of a similar class to SQLServer (e.g. postgres), but there would be significant work to create and maintain each additional scenario.

It would be good to hear from you and others who have this requirement whether it stems from a platform choice and if SqlServer on linux might be a workable alternative, or whether this comes more from a licensing or organisational/customer/developer preference.

[OneOfBestMan]

Can you give me some advices for adding postgres database ?

[HeyJoel]

@OneOfBestMan please see my comments above. Postgres is not supported and not currently scheduled for development.

Additional feedback for an open source RDBMS with a preference for postgres on this reddit post, requirement due to SQL Server "expensive licensing".

[vector-man]

I would also like this as well. I would definitely prefer to use postgresql for my project.

[vector-man]

@HeyJoel How do you like this idea?:

• Core library supporting everything to run on any EF Core-supported database, without optimizations.
• Optimization specific package (e.g. let's call it Cofoundry.SQLServer.Optimizer). This will build on the core library and only contain the optimizations.

You'll only need to maintain these two.

If a user wants to use SQL Server, they can use the only optimization library you'll maintain, Cofoundry.SQLServer.Optimizer

If a user wants to use, say, MySQL, it just works, sans optimizations.

If a user wants optimizations for other databases, they can write/maintain their own (e.g. Cofoundry.MySQL.Optimizer)

Does that solve the problem? Let me know what you think.

[HeyJoel]

Thanks for the suggestion @vector-man

The feedback I'm seeing is that postgres in particular is a popular requirement. I'm keen to get it working, but aside from doing the work we also need to consider the additional burden of supporting more than one database.

With regards to EF, my opinion is that although it is very useful for the majority of database work, it is by no means a panacea and restricting Cofoundry to the features available in EF would be problematic. It's hard to summarize why, so here's a fuller explanation:

Areas where EF is not used

Cofoundry.Core

The two main db dependent utilities (distributed locks and auto-update) in the core were original designed to work without taking a dependency on EF to keep the core dependency tree lean. This is less of a concern now that EF is quite closely tied to .NET Core, but both of these do need raw db access for certain features including running the DML to initialize the db tables they make use of.

Distributed locks

The DistributedLockManager uses raw SQL to create a lock as an atomic operation. This prevents processes such as the auto-update process from running at the same time on multiple site instances.

Auto-update

As well as detecting and inializing the db schema to be able to run auto-update scripts, the auto update process uses raw SQL to manage transactions for and execute T-SQL scripts. It also drops objects like triggers and SP's before re-creating them (e.g. see UpdateDbCommandHandler)

Database initialization

All database creation scripts (e.g. schema, SPs, triggers etc) are specific to SQLServer.

With regards to EF Migrations I only ever played around with the first few iterations of it and found too many edge cases to ever see it as an improvement over writing the scripts yourself. I'm sure it's improved but one thing to bear in mind is that we have to provide updates that are compatible with existing data. These data sets could potentially be large and to my knowledge that's not something you can realistically do in without breaking out into raw SQL. You can find examples of this in the updates 6, 7 and 8.

Database Objects

We use a handful stored procs for some queries that EF doesn't handle well, typically working with bulk data such as creating a draft, bulk ordering or updating lookup tables. You could argue that EF can handle these fine with small data sets - I haven't run any performance tests - but with larger data sets I would expect there to be significant issues here.

We use triggers to handle cascading deletes, mostly to get around the issues with multiple cascade paths in SqlServer, but also to cascade deletes into the unstructured data relation table. This I feel is the best strategy for SqlServer, but another db might require a different approach and we may need to look at this area to accommodate other db types. I don't know how EF handles this these days, the last time I checked it didn't.

Areas where EF is used

I'd say the vast majority of queries/commands use EF, presumably these will run fine on another database if the EF db provider is reliable but behaviour is not guaranteed and testing will be required.

Problems with a pure EF strategy

As detailed there are a number of areas where EF isn't used and you can break these out into 3 area:

• Performance: While some of these are nice-to-have performance increases, I expect that in many cases using EF would have a significant impact. We would have to run tests across mixed data sets to know.
• Migrations: I don't think a pure EF migration solution would be an appropriate replacement without losing an automated data migration path.
• Integrity: It's difficult to see a good EF based solution for areas where atomic operations or a minimal window of operation is required (e.g. the distributed lock process)

I think using a pure EF strategy, even as a fall-back, would introduce a lot of issues and additional support burden. It's also reassuring to know that lower-level functional and performance gains are available to us during future development if we need it and we don't need to design around the limitations of an ORM.

It's worth mentioning that I have observed similar issues with other CMS's that do adopt a pure ORM strategy, as well as problems with designing to the lowest common denominator.

A proposed plan for supporting other DBs

Having said all that, I do think it's possible for us to allow the use of other database by extension. Cofoundry has a strong DI based plugin system and we should be able to isolate and abstract those few areas that do require raw db access.

In fact we already have this in a few places such as IAssetStoredProcedures, ICustomEntityStoredProcedures and IDistributedLockManager. The main area that does need looking at is the auto update system, but it does seem very do-able.

The next steps would be to formalize the contract for extension and do the work to create the new database plugin package.

I'm not sure if it would be us that creates and supports the postgres package or it's something that the community would have to do, certainly anything beyond postgres would be out of our scope.

While the work to migrate any SQL scripts would be fairly straight-forward, it's the additional testing and support around this and every future release that worries me. We'd need to do significant work to create a solid set of data layer integration tests and have the infrastructure to run them against multiple databases.

One additional aspect to consider is how this would work for plugin packages that include their own db migrations. Presumably they would need to create migration packages for each additional db we support. That's not the end of the world if we have strong support for only two databases (SQLServer and postgres) but it could be more of an issue if the community develops other database providers.

In Summary

If the proposed plan is the way to go I'll get a separate issue set up to track the development of it. I can't guarantee that it's going to be something we'd work on imminently, but I see it is a popular requirement and I am interested in finding a solution.

I am mindful of the additional burden of support and impact on releases and so for us to take this on we'd need to be in a better place with regards to funding, sponsorship and contributions - all things we're trying to find time to work on at the moment!

My proposal assumes that postgres is the clear choice for an alternative database. I'd be interested to hear if anyone has any counter-opinion on this.

[tomchadar]

I am writing MySQL interface today, Sunday, to launch my own website on Ubuntu ASAP (in a day or two). This thread gives me some inspiration; thank you.

[vector-man]

Is there any progress on this issue? The lack of Postgresql is the only thing preventing me from using your CMS.

[snovak7]

Well, you can easily boot up a docker with mssql image in the express edition mode. it has limitations unless you have a big website >10GB. I'm still for the PostgreSQL provider though.

[HeyJoel]

There's no progress to report. Last year was tough having lost funding, but things are picking up again. SQLServer isn't yet a problem for our clients so other features have priority, but it's still something I'm keen to look at. If anyone would like to sponsor the feature then do get in touch.

Supporting pluggable db providers is a challenge, but I expect it wouldn't be too hard to make a fork and migrate the SQLServer specific scripts, you can use the notes above as guidance.