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; }
}
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();
}
}
}
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>();
}
}
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");
}
}
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);
}
}
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();
}
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;
}
}
... and use that ... :-)
public void ConfigureServices(IServiceCollection services)
{
services.AddUmbraco(_env, _config)
.AddBackOffice()
.AddWebsite()
.AddComposers()
.AddMigrations()
.Build();
}
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
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
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; }
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();
}
}
}
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);
}
}
... and increment the migration state in your MigrationPlan.
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 :-)
select * from adventuresUsers;
Id Email FirstName LastName NickName
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
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)