Creating database tables in Umbraco
Tags: umbraco, css, npoco, database
Published: 2022-11-21 07:06:19

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.

```c#
[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; }
}
```

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](https://github.com/schotime/NPoco) and [Umbraco's database annotations](https://apidocs.umbraco.com/v10/csharp/api/Umbraco.Cms.Infrastructure.Persistence.DatabaseAnnotations.html) 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.

```c#
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();
        }
    }
}
```

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

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

    protected override void Migrate()
    {
        CreateTableIfNotExists<User>();
    }
}
```

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](https://apidocs.umbraco.com/v10/csharp/api/Umbraco.Cms.Infrastructure.Migrations.MigrationPlan.html#Umbraco_Cms_Infrastructure_Migrations_MigrationPlan_From_System_String_) to a defined [target state](https://apidocs.umbraco.com/v10/csharp/api/Umbraco.Cms.Infrastructure.Migrations.MigrationPlan.html#Umbraco_Cms_Infrastructure_Migrations_MigrationPlan_To_System_String_) - in this case _AddUsersTable_.

```c#
// 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");
    }
}
```

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

```c#
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);
    }
}
```

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

```c#
public void ConfigureServices(IServiceCollection services)
{
    services.AddUmbraco(_env, _config)
        .AddBackOffice()
        .AddWebsite()
        .AddComposers()
   .AddNotificationHandler<UmbracoApplicationStartingNotification, AdventuresMigration>()
        .Build();
}
```

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

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

        return builder;
    }
}
```

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

```c#
public void ConfigureServices(IServiceCollection services)
{
    services.AddUmbraco(_env, _config)
        .AddBackOffice()
        .AddWebsite()
        .AddComposers()
        .AddMigrations()
        .Build();
}
```

_Note Umbraco recommends using [composers](https://our.umbraco.com/documentation/implementation/composing/) 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 ;-)

```sql
select * from adventuresUsers;

Id	Email	FirstName	LastName
```
The migration state is stored in the `umbracoKeyValue` table.

```sql
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
```


### 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.

```c#
[NullSetting]
public string? NickName { get; set; }
```
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.

```c#
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();
        }
    }
} 
```
Create a new migration...

```c#
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);
    }
}
```

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

```c#
From(string.Empty)
    .To<AddUsersTable>("AddUsersTable")
    .To<AddNickNameColumnToUsersTable>("AddUsersNickName");
```
Compile and run your SQL query again. It will still show an empty result... but you have a NickName column :-)

```sql
select * from adventuresUsers;

Id	Email	FirstName	LastName	NickName
```

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

```sql
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
```

---

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](https://our.umbraco.com/documentation/) (including the [API Docs](https://apidocs.umbraco.com/v10/csharp/api/Umbraco.Cms.Infrastructure.Migrations.html)) - which has only gotten better over the last few years.