Database Migrations with Umbraco 9 .Net Core - Part 1

How to create a custom table on Umbraco 9

The first thing we need to do is create the Custom Migration Plan.

using Umbraco.Cms.Infrastructure.Migrations;

namespace UmbracoProject.Migrations
{

     public class CustomMigrationPlan : MigrationPlan
     {

     public CustomMigrationPlan() : base("Umbracart.Migrations.CustomMigrationPlan")
     {
          DefinePlan();
     }

     protected void DefinePlan()
     {
     }
}

Then we need to create the DTO model.

using NPoco;
using Umbraco.Cms.Infrastructure.Persistence.DatabaseAnnotations;

namespace UmbracoProject.Migrations.Dtos.Store
{
     [TableName(TableName)]
     [PrimaryKey("id", AutoIncrement = true)]
     [ExplicitColumns]
     public class StoreDto
     {
          public const string TableName = "umbracart_Store";

          [Column("id")]
          [PrimaryKeyColumn]
          public int Id { get; set; }

          [Column("name")]
          public string Name { get; set; }
     }
}

Then implement the logic for creating the table

using Umbraco.Cms.Infrastructure.Migrations;
using Umbraco.Extensions;
using UmbracoProject.Migrations.Dtos.Store;

namespace UmbracoProject.Migrations.Tables.Store
{
     public class CreateStoreTable : MigrationBase
     {

          public CreateStoreTable(IMigrationContext context) : base(context)
          {
          }

          protected override void Migrate()
          {
               var tables = SqlSyntax.GetTablesInSchema(Context.Database);
               if (tables.InvariantContains(StoreDto.TableName)) return;

               Create.Table<StoreDto>().Do();
          }
     }
}

Then add the "CreateStoreTable" step in the "CustomMigrationPlan"

using Umbraco.Cms.Infrastructure.Migrations;
using UmbracoProject.Migrations.Tables.Store;

namespace UmbracoProject.Migrations
{
     public class CustomMigrationPlan : MigrationPlan
     {

          public CustomMigrationPlan() : base("Umbracart.Migrations.CustomMigrationPlan")
          {
               DefinePlan();
          }

          protected void DefinePlan()
          {
               From(string.Empty).
                    To<CreateStoreTable>("{F3EEC4E8-F5E7-49AE-AB7E-9F8E6713BAA3}");
          }
     }
}

As you can see from the code above. We have added the CreateStoreTable step and there's a random GUID identifier. This is the target state value that Umbraco uses to check which step you are on. You can see it on the umbracoKeyValue table. Reference: https://bit.ly/3vTcUTe

Then we need to create the component then add it to the composition pool so every time the site boots up it will be triggered.

using Microsoft.Extensions.Logging;
using Umbraco.Cms.Core.Composing;
using Umbraco.Cms.Core.Scoping;
using Umbraco.Cms.Core.Services;
using Umbraco.Cms.Infrastructure.Migrations;
using Umbraco.Cms.Infrastructure.Migrations.Upgrade;
using UmbracoProject.Migrations;

namespace Website.Core.Components
{

     public class CustomMigrationComponent : IComponent
     {
          private readonly IMigrationPlanExecutor migrationPlanExecutor;
          private readonly IScopeProvider scopeProvider;
          private readonly IKeyValueService keyValueService;

          public CustomMigrationComponent(
               IMigrationPlanExecutor migrationPlanExecutor,
               IScopeProvider scopeProvider,
               IKeyValueService keyValueService)
          {
               this.migrationPlanExecutor = migrationPlanExecutor;
               this.scopeProvider = scopeProvider;
               this.keyValueService = keyValueService;
          }

          public void Initialize()
          {
               var upgrader = new Upgrader(new CustomMigrationPlan());

               upgrader.Execute(migrationPlanExecutor, scopeProvider, keyValueService);
          }

          public void Terminate()
          {
          }
     }
}

Adding the CustomMigrationComponent to the composition pool

builder.Components().Append<CustomMigrationComponent>();

You can read more about compositions here:

How to compose a custom service in Umbraco 9

Different Ways to Inject Events, Helpers and Services into Umbraco 9

Conclusion

There's nothing much changed from Umbraco V8 they have only added ILoggerFactory as a dependency when using IComponent and I'll be writing Part 2 for this topic where we will tackle extending an existing table and changing its data type. You may also want to check the available methods of MigrationBase class to have some ideas for Altering, Deleting, etc.

About the author

Vlael Samuel Layug

PHP Developer
.NET Developer
Umbraco Contributor

Hello. I'm a web developer who specializes in Backend for about 4 years+ and working towards being a Full Stack Developer and Umbraco MVP in the future.

In my free time, I play around with the Umbraco CMS codebase a lot and picking up for grabs in the issue tracker. For me, it's like hitting two birds in one stone. I learn a lot every day and from every pull request that is being merged, it's like my way of giving back to the community and saying thanks to the developers who are maintaining Umbraco.

My vision & fundamentals

I want to make this website that can help fellow Umbracians learn a lot and get jobs.
Also, I want to mention that I've been reading the Umbrajobs blogs before being an author and it helped me a lot! I want to say thanks to them and #H5YR

Follow me on social media

If you're an Umbraco professional, partner, employer, recruiter or a passionate Umbraco community builder then I'm more than happy to hear from you. Follow me on my social media channels and please reach out if you have any needs, want help or consultation with anything Umbraco related or just want a general chat.