Allow alternative connection string for Umbraco Forms tables

[callumbwhyte]

Now that it looks like #141 is going to get some progress (as per the latest product roundup) I thought I would be cheeky and try and squeeze in another minor addition that's caused me problems for years... 😅

When performing an Umbraco upgrade its standard practice to take a backup of the database, perform the upgrade on a separate environment, test, and then deploy the code + new database.

This becomes problematic with constantly changing DB tables, like with Umbraco Forms - it's easy to tell editors not to edit content, but not easy to force your website users to stop filling in forms...

It would be awesome to have the option to store Umbraco Forms records in a separate database, by supplying an alternative connection string. e.g. If umbracoFormsDbDSN exists use that, else use umbracoDbDSN.

I hope that whilst making a big chunk of changes to database stuff this could be relatively easy to squeeze in...

[Matthew-Wise]

Support this not only for the very valid reasons but it also removes a GDPR issue of taking the back up to do the upgrade.

[nul800sebastiaan]

Hey @callumbwhyte and all! Yes, this is definitely a feature we'd love to work on as well. We considered it but had to make sure the scope was small enough to complete the basic task of having the option to store Forms in the database (and make it work with Deploy, etc).

The codebase is quite old and not in the best shape, so "simply" adding a new connection string isn't something we could easily accomplish, there's quite a lot of interaction between the Umbraco "main" database and whatever Forms needs, so that all needs to be untangled.

So: yes, we'd love to, but can't promise a timeframe for this yet.

[callumbwhyte]

Ha well I did say it was a cheeky request, so understood!

At least this is officially on the radar now 😊

[callumbwhyte]

Hey @AndyButland,

In preparing for my talk at DF24 today I wanted to talk about the idea of moving your sensitive data elsewhere, like you can do with Commerce...

I would still love this for Umbraco Forms!

Callum

[AndyButland]

Yes, me too. Unfortunately Forms is using the CMS patterns for data access, whereas Commerce, back when it was Vendr, rolled their own. So it would be quite a big rewrite to do it directly in Forms, and otherwise would need something implemented in CMS. I did have a hack at it before as you can see from the PR links above, but it had some flaws, and didn't gain traction enough to become a supported feature.

Your reminder did get me thinking though if it could work at the database level, without touching any code. In the sense of could we have some sort of "symlink" from one table to another in a different database? And seems you sort of can, via a view.

So I:

• Copied my Umbraco database table.
• Deleted all the UF* tables in the original.
• Created a view for each table in the original that referenced the table in the copy.

CREATE VIEW UFForms AS SELECT * FROM [MyDb2].dbo.UFForms
GO

Started Umbraco, and the Forms backoffice seemed to work just fine.

Let me caveat - no real testing done, and it's not a supported scenario... I was just trying out the idea. And I can see we'd have a problem with migrations (as whilst you can SELECT, UPDATE etc. on a view, you can't expect an ALTER TABLE to work). But maybe there's something in the approach...

[callumbwhyte]

@AndyButland Yep fully understand this is complex... I know there's a review of CMS data access on the horizon too, so perhaps something we need to wait for. 😊

Something Forms could do in the immediate term to help is wrap Umbraco's scope etc in it's own interface that we could target for replacement.

The views approach is quite interesting, though unfortunately I don't believe will work on Azure where Azure SQL can't see "adjacent" databases because they're actually logically separated.

Callum