Working with custom database tables in Umbraco

2023-04-04

A new blog post, I said I'd do it eventually and apparently, here we are! Recently, I've had a hankering to look at how Umbraco databases work and to understand how to properly use NPOCO to query any custom data I might have added to a database. I found the documentation for this somewhat lacking and hard to understand, therefore I have taken it upon myself to delve into the intricacies of this topic, today, I shall enlighten you upon my findings.

The very first thing to understand is that you can build new tables using a code-first approach within Umbraco, while NPOCO does not natively include the ability to run database migrations (ala EF, etc.), Umbraco has seen fit to implement logic that allows us to run migrations and build up custom database tables utilizing the same methods that they themselves use for building the CMS database schema.

What this means is that we have access to a well tested suite of migration tools to utilize, This post will delve into creating a basic data model utilizing a code-first approach, how to instruct Umbraco run a migration on startup for these new custom database tables, how to create relationships between these entities and then we will finish up by looking at how NPOCO queries work and have a play with querying relational data between multiple entities, and how to materialise the data into objects ready for use.

As such, without further ado, I have created a simple data structure with limited information on relationships (We will tackle how this works later in the post), so that we can investigate a number of attribute's that we can use to explain our data to the underlying Umbraco migrations engine.

[TableName("Customers")] //Tells NPoco and Umbraco what table name to use
[PrimaryKey("CustomerId")] // Tells NPoco what the primary key is and has options to allow setting of auto incrementing values.
[ExplicitColumns] // Tells NPoco to only use the properties that are marked with the Column attribute
public class Customer
{
    [Column]
    [PrimaryKeyColumn] //Tells Umbraco that this is the primary key
    public int CustomerId { get; set; }
    [Column]
    public string FirstName { get; set; }
    [Column]
    public string LastName { get; set; }
    [Column] 
    public string Email { get; set; }
}

[TableName("Purchases")] //Tells NPoco and Umbraco what table name to use
[PrimaryKey("PurchaseId")] // Tells NPoco what the primary key is and has options to allow setting of auto incrementing values.
[ExplicitColumns] // Tells NPoco to only use the properties that are marked with the Column attribute
public class Purchase
{
    [Column]
    [PrimaryKeyColumn] //Tells Umbraco that this is the primary key
    public int PurchaseId { get; set; }
    [Column]
    public int CustomerId { get; set; }
    [Column]
    public int StoreId { get; set; }
    [Column]
    public int ProductId { get; set; }
}

[TableName("Store")] //Tells NPoco and Umbraco what table name to use
[PrimaryKey("StoreId")] // Tells NPoco what the primary key is and has options to allow setting of auto incrementing values.
[ExplicitColumns] // Tells NPoco to only use the properties that are marked with the Column attribute
public class Store
{
    [Column]
    [PrimaryKeyColumn] //Tells Umbraco that this is the primary key
    public int StoreId { get; set; }
    [Column]
    public string Name { get; set; }
}

[TableName("Products")] //Tells NPoco and Umbraco what table name to use
[PrimaryKey("ProductId")] // Tells NPoco what the primary key is and has options to allow setting of auto incrementing values.
[ExplicitColumns] // Tells NPoco to only use the properties that are marked with the Column attribute
public class Product
{
    [Column]
    [PrimaryKeyColumn] //Tells Umbraco that this is the primary key
    public int ProductId { get; set; }
    [Column]
    public string Name { get; set; }
    [Column]
    public string Description { get; set; }
}

While there are a number of relationships defined with Id references, we can ignore these for the moment, as some additional work needs to be applied to ensure NPOCO can correctly understand the entity linking. I have done my best to ensure that all the attributes have a comment that makes sense, with the exception of the [column] attribute, which links directly to the [ExplicitColumns] attribute.

Our next step is to tell Umbraco to create our tables, this is fairly well defined within their documentation however, for the sake of completeness, I shall include an implementation within this post. We'll need to create a ComponentComposer, an IComponentand a MigrationBase I have done this within the following code.

A slight caveat here, being that if you are using SQlite instead of a full SQL Server, you may encounter a duplicate column name error, This seems to be a problem with the underlying Umbraco migrations framework, it looks to generate name's strangely for sqlite, so you should be setting a [Column(Name = "ColumnName")] attribute for each one to prompt the correct names, as this ultimately has an annoying effect on NPOCO's queries as well.

using Umbraco.Cms.Core;
using Umbraco.Cms.Core.Composing;
using Umbraco.Cms.Core.Scoping;
using Umbraco.Cms.Core.Services;
using Umbraco.Cms.Infrastructure.Migrations.Upgrade;
using Umbraco.Cms.Infrastructure.Migrations;
using Umbraco.Cms.Core.Migrations;
using Umbraco_Blog.Tables;

namespace DbComposer
{
    public class CustomDbComposer : ComponentComposer<CustomDbComponent>
    {
    }

    public class CustomDbComponent : IComponent
    {
        private readonly ICoreScopeProvider _coreScopeProvider;
        private readonly IMigrationPlanExecutor _migrationPlanExecutor;
        private readonly IKeyValueService _keyValueService;
        private readonly IRuntimeState _runtimeState;

        public CustomDbComponent(ICoreScopeProvider coreScopeProvider, IMigrationPlanExecutor migrationPlanExecutor, IKeyValueService keyValueService, IRuntimeState runtimeState)
        {
            _coreScopeProvider = coreScopeProvider;
            _migrationPlanExecutor = migrationPlanExecutor;
            _keyValueService = keyValueService;
            _runtimeState = runtimeState;
        }

        public void Initialize()
        {
            if (_runtimeState.Level < RuntimeLevel.Run)
            {
                return;
            }

            // Create a migration plan for a specific project/feature
            // We can then track that latest migration state/step for this project/feature
            var migrationPlan = new MigrationPlan("BlogComments");

            // This is the steps we need to take
            // Each step in the migration adds a unique value
            migrationPlan.From(string.Empty)
                .To<AddStoreOrdersSchema>("create-custom-database-schema");

            // Go and upgrade our site (Will check if it needs to do the work or not)
            // Based on the current/latest step
            var upgrader = new Upgrader(migrationPlan);
            upgrader.Execute(_migrationPlanExecutor, _coreScopeProvider, _keyValueService);
        }

        public void Terminate()
        {
            //throw new NotImplementedException();
        }
    }

    public class AddStoreOrdersSchema : MigrationBase
    {
        public AddStoreOrdersSchema(IMigrationContext context) : base(context)
        {
        }
        protected override void Migrate()
        {
            Logger.LogDebug("Running migration {MigrationStep}", "AddStoreOrdersSchema");

            if (!TableExists("Stores"))
            {
                Create.Table<Store>().Do();
            }
            else
            {
                Logger.LogDebug("The database table {DbTable} already exists, skipping", "Stores");
            }

            if (!TableExists("Customers"))
            {
                Create.Table<Customer>().Do();
            }
            else
            {
                Logger.LogDebug("The database table {DbTable} already exists, skipping", "Customers");
            }
            if (!TableExists("Purchases"))
            {
                Create.Table<Purchase>().Do();
            }
            else
            {
                Logger.LogDebug("The database table {DbTable} already exists, skipping", "Purchases");
            }
            if (!TableExists("Products"))
            {
                Create.Table<Product>().Do();
            }
            else
            {
                Logger.LogDebug("The database table {DbTable} already exists, skipping", "Products");
            }
        }
    }
}

Once you've added all of this to your solution, running it will then apply the migrations and add your tables to your database, I shall refer to the fantastic Bunsen Jude to exclaim my excitement at this prospect.

Now that we have some tables in the database and they're being correctly created, we can look into adding some data to them so we can crack on by creating a simple surface controller that will add some of that data for us, that we can then invoke with a simple GET request.

using Microsoft.AspNetCore.Mvc;
using NPoco;
using Recombee.ApiClient;
using Recombee.ApiClient.ApiRequests;
using Recombee.ApiClient.Util;
using Umbraco.Cms.Core.Cache;
using Umbraco.Cms.Core.Logging;
using Umbraco.Cms.Core.Models;
using Umbraco.Cms.Core.Routing;
using Umbraco.Cms.Core.Scoping;
using Umbraco.Cms.Core.Services;
using Umbraco.Cms.Core.Web;
using Umbraco.Cms.Infrastructure.Persistence;
using Umbraco.Cms.Web.Website.Controllers;
using Umbraco_Blog.Tables;

namespace Umbraco_Blog.Controllers
{
    public class DataController: SurfaceController
    {
        private readonly IScopeProvider _scopeProvider;
        public DataController(IUmbracoContextAccessor umbracoContextAccessor, IUmbracoDatabaseFactory databaseFactory, ServiceContext services, AppCaches appCaches, IProfilingLogger profilingLogger, IPublishedUrlProvider publishedUrlProvider, IScopeProvider scopeProvider) : base(umbracoContextAccessor, databaseFactory, services, appCaches, profilingLogger, publishedUrlProvider)
        {
            _scopeProvider = scopeProvider;
        }
        /// <summary>
        /// https://localhost:44306/umbraco/surface/Data/PopulateData
        /// </summary>
        /// <returns></returns>
        public IActionResult PopulateData()
        {
            using var scope = _scopeProvider.CreateScope(autoComplete: true);
            var db = scope.Database;

            //Create a store.
            Store store = new Store()
            {
                Name = "Test Store 1"
            };
            var result = db.Insert(store);
            //Create a customer
            Customer customer = new Customer()
            {
                FirstName= "Sam",
                LastName= "Beynon",
                Email= "[email protected]",
            };
            db.Insert(customer);

            //Create 2 arbitrary products
            Product product = new Product()
            {
                Name = "Product 1",
                Description = "Product 1"
            };
            Product productTwo = new Product()
            {
                Name = "Product 2",
                Description = "Product 2"
            };
            db.Insert(product);
            db.Insert(productTwo);

            //create some "purchases".
            Purchase purchase = new Purchase()
            {
                StoreId = store.StoreId,
                CustomerId = customer.CustomerId,
                ProductId= product.ProductId
            };
            Purchase purchaseTwo = new Purchase()
            {
                StoreId = store.StoreId,
                CustomerId = customer.CustomerId,
                ProductId = productTwo.ProductId
            };

            db.Insert(purchase);
            db.Insert(purchaseTwo);

            return Ok();

        }
    }
}

Referring to the url in the comment for the new method, you can now trigger this method and it will create the same data within your database, noting of course to make sure you modify the port number to your local instance's. Now that we have the data, it becomes a question of how do we access the data, and more importantly, how do we minimize the number of database queries needed to get the relevant relational information. I'll start with a simple example of how to get the listing of our store.

public IActionResult GetStore()
        {
            using var scope = _scopeProvider.CreateScope(autoComplete: true);
            var db = scope.Database;
            var store = db.Fetch<Store>();
            return Ok(store);
        }

This then returns, as we hope, a list of all of our stores, in this case, the one that we added previously.

Now, typically, we can load all the data down for all of the types, and then map them together within our C# code, but what's the point in using a relational database if we have to manually apply the mappings? Databases have joins and the likes for a reason, so let's use them! Now, NPOCO is a little strange when it comes to handling this, but once you get your head around it, it does atleast begin to make some sense. The next scenario is to add the ability to get all the "Purchases" that have been made at the store, as a collection of their respective type, within a single db call.

So, the next thing we need to do is add the relative property to the Store class, so that we have somewhere to put the new data that we have pulled from the database, to do this we need to utilize the [ResultColumn] attribute to ensure that NPOCO understands what this column is used for, and as such will ignore it during update/insert procedures.

[TableName("Store")] //Tells NPoco and Umbraco what table name to use
    [PrimaryKey("StoreId")] // Tells NPoco what the primary key is and has options to allow setting of auto incrementing values.
    [ExplicitColumns] // Tells NPoco to only use the properties that are marked with the Column attribute
    public class Store
    {
        [Column(Name="StoreId")]
        [PrimaryKeyColumn] //Tells Umbraco that this is the primary key
        public int StoreId { get; set; }
        [Column(Name="Name")]
        public string Name { get; set; }

        [ResultColumn]
        [Reference(ReferenceType.Many, ColumnName = "StoreId", ReferenceMemberName = "StoreId")]
        public List<Purchase> Purchases { get; set; }
    }

Note that i've also added a [Reference] attribute, this tells NPOCO what kind of relationship exists between these two entities, in this case, it's a OneToMany relationship, as a single Store can have multiple Purchases.

An important note here, is that NPOCO doesn't work with arrays, I've tested it with List and [T] only so far and that only List seems to work out of the two.

public IActionResult GetStore()
        {
            using var scope = _scopeProvider.CreateScope(autoComplete: true);
            var db = scope.Database;
            var query = db.Query<Store>().IncludeMany(x => x.Purchases);
            var stores = query.ToArray();
            return Ok(stores);
        }

Now this code, as we'd like, returns the list of purchases for the store as well.

And now we have a list of stores, and their related purchases, but wait! There's more we need to do before we can call it a night, for one, this data is very hard to decipher for a normal human, it's all numbers and there's very little context as to what the numbers mean, and the best way for us to improve upon that is to display the representation of what the numbers stand for, as such we need to modify the Purchase class to include a number of relationships and then change the GetStore method to pull that data out, like so.

[TableName("Purchases")] //Tells NPoco and Umbraco what table name to use
[PrimaryKey("PurchaseId")] // Tells NPoco what the primary key is and has options to allow setting of auto incrementing values.
[ExplicitColumns] // Tells NPoco to only use the properties that are marked with the Column attribute
public class Purchase
{
    [Column(Name ="PurchaseId")]
    [PrimaryKeyColumn] //Tells Umbraco that this is the primary key
    public int PurchaseId { get; set; }
    [Column(Name ="CustomerId")]
    public int CustomerId { get; set; }
    [Column(Name ="ProductId")]
    public int ProductId { get; set; }
    [Column(Name ="StoreId")]
    public int StoreId { get; set; }

    [ResultColumn]
    [Reference(ReferenceType.OneToOne, ColumnName = "CustomerId", ReferenceMemberName = "CustomerId")]
    public Customer Customer { get; set; }

    [ResultColumn]
    [Reference(ReferenceType.OneToOne, ColumnName = "ProductId", ReferenceMemberName = "ProductId")]
    public Product Product { get; set; }
}

Now, this is where the limitations of NPOCO begin to crop up, as nested includes are not handled by the system, which means that we have to work within the confines of this to have a native NPOCO solution, the following is the best way i have found to approach this issue without resorting to a SQL-based solution, which would be my usual approach, be warned that this approach will not scale well, and for any performance-critical tasks and large workloads, using a SQL Stored proc or similar is a far better way to achieve this goal.

First of all, You need to also include the relationship from the Purchase to the Store, so this is added to the Purchase class.

[ResultColumn]
[Reference(ReferenceType.OneToOne, ColumnName = "StoreId", ReferenceMemberName = "StoreId")]
public Store Store { get; set; }

Afterwhich, we can modify our GetStore method to do the following.

public IActionResult GetStores()
{
    using var scope = _scopeProvider.CreateScope(autoComplete: true);
    var db = scope.Database;
    var purchases = db.Query<Purchase>()
        .Include(p => p.Customer)
        .Include(p=> p.Product)
        .Include(p => p.Store).ToList();
    var grouped = purchases.GroupBy(p => p.Store.StoreId);
    List<Store> stores = new List<Store>();
    foreach (var storeGrouping in grouped)
    {
        //var store = storeGrouping.Key;
        var store = storeGrouping.Select(s => s.Store).FirstOrDefault();
        store.Purchases = storeGrouping.Select(c => c).ToList();
        stores.Add(store);
    }
    return Ok(stores);
}

Now of course, we have a circular path within our structure, which means that the JsonSerializers are making us public enemy number one, so we can for the sake of this post, add a [JsonIgnore] attribute to the Purchase.Store property, to ensure that the serializer doesn't hate us for life.

Obligatory, please ensure you are not passing direct database objects to your front-end in your projects, use ViewModels and the likes to pass only the data they need.

And now that we have added our [JsonIgnore], and we have our updated method, we can run our code once more to get the final result.

Now while this approach remains true to the original intent of a single database call, depeding on the size of your datasets, it might become slow to format into the correct format for the front-end, I would advise ensuring that all of your code is fit for the amount of data it will be processing.

This has been a short journey into NPOCO and Umbraco custom database migrations, I learned alot, and I hope that you did too!

Good day to you!