Onwards to Umbraco 13 and Linux – Part 1

Regular readers may remember back in 2022 I moved my personal project site TV Whirl first from 7 to 9, then shortly follwed on to Umbraco 10 on .net 6.0, which was then the Long Term Support release of the CMS (though I technically didn’t stick truly to the LTS releases as I never upgraded it from a very early 10.1 point release to anything else in the 10 release line), and this is where it’s been sat ever since. Here we are about 18 months later with another LTS release recently out in the form of Umbraco 13 on .net 8.0, so I decided it was finally time to get everything moved onto the newest version.

Upgrading to newer versions of Umbraco for most releases between 9 and 13 for most projects is a lot more straightforward than it has been in the past, often requiring only pulling down some new Nuget packages and fixing a few minor breaking changes depending on particular functions used. However, in my case this time round I’d decided to go a step further and look at migrating the underlying hosting of the site away from the traditional Windows server environment and onto Linux instead at the same time, as well as overhauling elements of the site to improve sustainability and performance. Introducing so many changes all in one go and making things truly cross-platform instantly turned it into a much bigger challenge, something I’m going to cover in more detail over a short series of blog posts over the coming weeks.

Part 1 – The Main Umbraco Upgrade
Part 2 – Making Custom Functionality Cross-Platform
Part 3 – Enhancing Sustainability and Performance

Part 1 – The Main Umbraco Upgrade

The biggest initial challenge with the upgrade for this project came with the underlying database. Traditionally on Windows, I’d been using Microsoft’s free edition, SQL Server Express for this. When it comes to hosting on Linux, it is only possible to get a version of MS SQL Express for the x64 processor architecture, with Microsoft trying to push everyone towards their newer Azure products if you’re using the increasingly common ARM-based processors. Handily Umbraco has supported the use of the cross-platform SQLite database since v10. However, there is no direct way to convert an existing database from one type to the other. As I’ve covered in a blog post on here before, there are tools that claim to be able to convert your database and these may work for simpler databases. However, attempting to use these sorts of tools on a schema as complicated as Umbraco’s fails miserably. The eventual solution here was to export everything out of the old database using the venerable uSync, start a fresh instance of Umbraco on SQLite, then reimport everything back into this new database. This worked (with some patience and tweaking of the back office to remain logged in longer required, as this site has over 50000 nodes), but it came with one issue that wasn’t readily apparent until right after this was all launched. All the traditional integer database IDs will not be preserved on any reimport but will be regenerated from scratch. Umbraco started using GUIDs for identifying nodes some time back and these remain identical across imports and exports, with integer IDs largely only there as a legacy feature now. But in my case I was still using some of these in URLs which have already been posted out externally on Twitter and other sites for several years. So to ensure these continued to find the correct content even after the upgrade, a simple flatfile CSV export of the old IDs > new guids also had to be included in the project which is loaded into a lookup table and stored in the Umbraco Appcache. URLs generated from the site going forward have been updated to use the GUIDs so now if a URL is encountered with a GUID present, I assume it’s already using the new document identifiers and look up the content by this GUID directly. Meanwhile, if a URL is encountered with an integer ID instead, it first goes to this simple lookup table to map it to the relevant GUID.

Core Umbraco document types, elements and content all successfully moved over to SQLite, the next step was to tackle the custom database tables I’ve added on top. I’d already been using EFCore with my previous Umbraco 10 site, before support for EF had even been officially added in to Umbraco, meaning I already had a lot of the plumbing, models and migrations needed to make EFCore work. However, some updates to Nuget packages and newer references in the startup classes were needed to make this work better with Umbraco 13 and SQLite. The key one was to replace the previous generic ASP.net line which was designed to enable lazy-loading and only work with SQL server:-

services.AddDbContext(options => options.UseLazyLoadingProxies().UseSqlServer(connection));

With a newer Umbraco-aware one, based off their own included extension methods:-

builder.Services.AddUmbracoDbContext((serviceProvider, options) => options.UseLazyLoadingProxies().UseUmbracoDatabaseProvider(serviceProvider));

This alternative version should ensure it gets the correct database connection string and type of database, irrespective of whether it’s switched between SQL Express and SQLite in the future. Once this was changed, the existing EFCore Database Migrations set up for SQL Server originally would then work to create database tables for SQLite too. Though this initially appeared to work without error, it transpired there were a few subtle but important differences that the EFCore designer-generated code doesn’t catch around integer identifiers for the different types of database and these needed correcting manually. Specifically, the designer code would add a line for:-

.Annotation("SqlServer:Identity", "1, 1")

But to have SQLite support too an additional annotation line needs to be manually added (though it is fine to retain both as the tooling should ignore any which don’t apply to that database type):-

.Annotation("Sqlite:Autoincrement", true)

The only other minor change which then needed making is around the datatype itself. The EFCore designer tools generate column code using ‘int’ as the type identifier. Resulting in lines similar to:-

Id = table.Column<int>(type: "int", nullable: false)

In SQL Server, ‘int’ and ‘integer’ seem to be largely interchangeable. However in SQLite, apparently identity keys cannot be applied to ‘int’ columns but only to ‘integer’ columns, so this needs changing in the generated code to read:-

Id = table.Column<int>(type: "integer", nullable: false)

With those amendments done and migrations all re-run, all the custom tables were also now correctly ported in to the new SQLite database, which only left one remaining database-related issue to solve. A lot of these tables were used for custom logging and auditing with an occasional minor update needed to add a note or flip a bit on a row from ‘true’ to ‘false’. In the old world I would normally just browse the contents of these tables within the MS SQL Management Studio GUI tool in Windows whenever needed. However the move to a GUI-less Linux environment and SQLite meant the loss of such a convenient tool too. The solution here was to introduce Umbraco UI Builder. This is the rebranded version of what was originally Konstrukt from Matt Brailsford, and is an Umbraco package designed to make building interfaces to custom database tables within the back office quick and easy. It’s a package I’ve seen demoed many times before, and I’ve even extolled the virtues of to others. However despite this I’d never used it myself as this was the first time I’ve found a good use-case for it on one of my own projects. Getting it going code-wise was fairly straightforward using the examples in the documention. The only real challenges I seemed to run into were that as my existing database models were created by EFCore and currently the package is still based around Umbraco’s older NPoco modelling, I couldn’t use them directly without some issues. This may become simpler in a future release as EFCore support in Umbraco is still quite new, but for now the quickest solution here seemed to be to just clone the model classes and make some minor alterations to them to add in NPoco annotations or remove generated EFCore code approaches. And then the second issue I ran into was around licencing. The free licence for Umbraco UI Builder, as Konstrukt had done before it, allows an unlimited number of readable tables (or ‘collections’ as they’re more commonly referred to in the package lingo), but to only make update operations (ie add, delete, edit) on a single table/collection. In my use-case this would be fine as I only ever needed to be able to edit the contents of one table. But it took me a long time to get the licencing manager to count that I indeed only wanted to update this single table. To help with this there is a method ‘MakeReadOnly()’ which should effectively take that table out of the count completely, but it seemed a little reluctant to work. Eventually I figured out I was being caught out by the very simple error of the method being called outside the wrong set of brackets! Once that was traced and sorted though, the package then played nicely with the database. The only UI bits that aren’t totally smooth are that true/false datatypes return me a text box rather than a true/false picker, and it still tries to pop up the Umbraco infinite editor if I accidentally click on a read only line which then errors as this is a table with a complex key rather than a single column one (apparently this should be avoidable using the ‘SetNameFormat()’ rather than ‘SetNameProperty()’ method, but I couldn’t get this to work). However, as this is a project for myself and I’m both happy to type a 1 or 0 and just ignore the occasional error, these are only minor niggles which I can very easily live with for all the simplification it brings over building my own complete interface. Although officially it is not recommended to use with SQLite for production loads, I can also say the speed of this in testing is quite impressive too, with both paging and opening records from database tables with >30000 rows in happening almost instantly.

And with that I had all the parts for the Umbraco 13 site up and running, but this was only a small part of everything needed. In the next part of the series I’ll be moving on to getting some of the bespoke functionality of the solution working cross-platform, which on this project mainly involves custom video encoder handling.