DEV Community

Morten Hartvig
Morten Hartvig

Posted on • Updated on

Creating database tables in Umbraco

One of the most beloved things about Umbraco is its flexibility. No matter the challenge you face at work (or at home), there is always a way to solve it using Umbraco's features and APIs.

This includes - but is definitely not limited to - storing and displaying data that is not handled by the CMS and its Backoffice.

In the following post you will see how easy it is to create custom database tables in Umbraco (11).

Creating a model

First step is to create a model to represent your database entity. To keep it simple this post will assume you need a database table for a set of (non-Umbraco) users.

It is also assumed the users are retrieved from some kind of CRM-like system in which they have an id, email, first- and lastname.

[TableName("adventuresUsers")]
[PrimaryKey("Id", AutoIncrement = false)]
public class User
{
    public int Id { get; set; }

    public required string Email { get; set; }

    public required string FirstName { get; set; }

    public required string LastName { get; set; }
}
Enter fullscreen mode Exit fullscreen mode

You can use various attributes on your properties to control how the columns should be created in your database. As an example, you could add [Column("Firstname")] to your FirstName property to indicate the column should be Firstname instead of FirstName in the database, which it would otherwise be called.

Refer to NPoco and Umbraco's database annotations for more info.

Creating a migration

Your migration will inherit Umbraco's MigrationBase. However, seeing as you might have to create additional tables at a later stage, you might consider enhancing this with some useful helper methods.

public abstract class AdventuresMigrationBase : MigtgtrationBase
{
    protected AdventuresMigrationBase(IMigrationContext context) : base(context) { }

    protected void CreateTableIfNotExists<TEntity>()
    {
        if (TableExists(Database.PocoDataFactory.ForType(typeof(TEntity)).TableInfo.TableName) == false)
        {
            Create.Table<TEntity>().Do();
        }
    }
}
Enter fullscreen mode Exit fullscreen mode

With the migration base in place you can create a migration for creating the users table.

public class AddUsersTable : AdventuresMigrationBase
{
    public AddUsersTable(IMigrationContext context) : base(context) { }

    protected override void Migrate()
    {
        CreateTableIfNotExists<User>();
    }
}
Enter fullscreen mode Exit fullscreen mode

Should you need to create other tables in the future for, say, podcasts or jobs, you can reuse your own migration base and easily create tables with CreateTableIfNotExists<Podcast>() and CreateTableIfNotExists<Job>() respectively.

In order to run the migration you need a migration plan in which you will keep track of and control the steps for our migration(s).

Seeing as this is the first migration in the plan, you will be going from an empty source state to a defined target state - in this case AddUsersTable.

// If you plan (no pun intended) to create multiple tables
// you might want more than one plan... and therefore be 
// a bit more explicit in your naming :-)
public class AdventuresMigrationPlan : MigrationPlan
{
    public AdventuresMigrationPlan() : base("AdventuresMigration")
    {
        From(string.Empty)
            .To<AddUsersTable>("AddUsersTable");
    }
}
Enter fullscreen mode Exit fullscreen mode

Next step is to execute the migration plan at startup. This can be done by hooking into the UmbracoApplicationStartingNotification notification.

public class AdventuresMigration : INotificationHandler<UmbracoApplicationStartingNotification>
{
    private readonly ICoreScopeProvider _scopeProvider;
    private readonly IMigrationPlanExecutor _migrationPlanExecutor;
    private readonly IKeyValueService _keyValueService;
    private readonly IRuntimeState _runtimeState;

    public AdventuresMigration(ICoreScopeProvider scopeProvider,
        IMigrationPlanExecutor migrationPlanExecutor,
        IKeyValueService keyValueService,
        IRuntimeState runtimeState)
    {
        _migrationPlanExecutor = migrationPlanExecutor;
        _scopeProvider = scopeProvider;
        _keyValueService = keyValueService;
        _runtimeState = runtimeState;
    }

    public void Handle(UmbracoApplicationStartingNotification notification)
    {
        if (_runtimeState.Level < RuntimeLevel.Run) return;

        var migrationPlan = new AdventuresMigrationPlan();

        var upgrader = new Upgrader(migrationPlan);
        upgrader.Execute(_migrationPlanExecutor, _scopeProvider, _keyValueService);
    }
}
Enter fullscreen mode Exit fullscreen mode

Lastly the migration notifications handler will have to be registered in Startup.cs using the AddNotificationHandler extension.

public void ConfigureServices(IServiceCollection services)
{
    services.AddUmbraco(_env, _config)
        .AddBackOffice()
        .AddWebsite()
        .AddComposers()
   .AddNotificationHandler<UmbracoApplicationStartingNotification, AdventuresMigration>()
        .Build();
}
Enter fullscreen mode Exit fullscreen mode

If you wish to keep your Startup clean, you can add an extension method yourself ...

public static class UmbracoBuilderExtensions
{
    public static IUmbracoBuilder AddMigrations(this IUmbracoBuilder builder)
    {
        builder.AddNotificationHandler<UmbracoApplicationStartingNotification, AdventuresMigration>();

        return builder;
    }
}
Enter fullscreen mode Exit fullscreen mode

... and use that ... :-)

public void ConfigureServices(IServiceCollection services)
{
    services.AddUmbraco(_env, _config)
        .AddBackOffice()
        .AddWebsite()
        .AddComposers()
        .AddMigrations()
        .Build();
}
Enter fullscreen mode Exit fullscreen mode

Note Umbraco recommends using composers for registering your notification handler if you are creating a package.

If you compile and wait for Umbraco to boot, you should now have an adventuresUsers table your the database.

Running the following SQL query will give you an empty result (which is expected).. but the table is there ;-)

select * from adventuresUsers;

Id  Email   FirstName   LastName
Enter fullscreen mode Exit fullscreen mode

The migration state is stored in the umbracoKeyValue table.

select * from umbracoKeyValue where [key] = 'Umbraco.Core.Upgrader.State+AdventuresMigration'

key value   updated
Umbraco.Core.Upgrader.State+AdventuresMigration AddUsersTable 2022-11-18 10:12:52.193
Enter fullscreen mode Exit fullscreen mode

Adjustments

Not five minutes has passed since you deployed the code above and you are told that the users also have an optional nickname. Instead of dropping the table and starting over, you can easily add new migrations and increase the migration state.

Start by adding an optional NickName property to your User model.

[NullSetting]
public string? NickName { get; set; }
Enter fullscreen mode Exit fullscreen mode

Next.. remember that custom migration base you created? You can add another helpful method for ease of use through your project's development cycle when you need string columns.

public abstract class AdventuresMigrationBase : MigrationBase
{
    protected AdventuresMigrationBase(IMigrationContext context) : base(context) { }

    ...

    protected void EnsureStringColumn(string tableName, string columnName)
    {
        if (ColumnExists(tableName, columnName) == false)
        {
            Alter.Table(tableName).AddColumn(columnName).AsString().Do();
        }
    }
} 
Enter fullscreen mode Exit fullscreen mode

Create a new migration...

public class AddNickNameColumnToUsersTable : AdventuresMigrationBase
{
    public AddNickNameColumnToUsersTable(IMigrationContext context) : base(context) { }

    protected override void Migrate()
    {
        var tableName = Database.PocoDataFactory.ForType(typeof(User)).TableInfo.TableName;
        var columnName = nameof(User.Nickname);

        EnsureStringColumn(tableName, columnName);
    }
}
Enter fullscreen mode Exit fullscreen mode

... and increment the migration state in your MigrationPlan.

From(string.Empty)
    .To<AddUsersTable>("AddUsersTable")
    .To<AddNickNameColumnToUsersTable>("AddUsersNickName");
Enter fullscreen mode Exit fullscreen mode

Compile and run your SQL query again. It will still show an empty result... but you have a NickName column :-)

select * from adventuresUsers;

Id  Email   FirstName   LastName    NickName
Enter fullscreen mode Exit fullscreen mode

Also, the migration state for AdventuresMigration has been updated to the latest migration.

select * from umbracoKeyValue where [key] = 'Umbraco.Core.Upgrader.State+AdventuresMigration'

key value   updated
Umbraco.Core.Upgrader.State+AdventuresMigration AddUsersNickName 2022-11-18 10:20:52.132
Enter fullscreen mode Exit fullscreen mode

That is it! As you can see, creating tables in Umbraco can be done with ease :-)

This post definitely did not covered everything there is to managing tables so it is highly recommend that you dive into Umbraco's documentation (including the API Docs) - which has only gotten better over the last few years.

Top comments (0)