Umbraco Custome table Updating with new Columns

Questions
1

Hi,

I have been asked to add some custom tables to one of our clients site, I see the document at Creating a Custom Database Table | CMS 16.latest | Umbraco Documentation .

But my question is, if We add the table and then the customer says I want to add Extra data to the table say for instance we have a table that has

ID: Int
Add1: String
Add2: String

Then they say want to add add3 and add4 how would I run a second or 3rd migration on a table.

I can’t find any info how to add a 2nd or 3rd Migration.

Thanks

2

Hi,

The example doesn’t really talk about it much, but you can have multiple steps in your migration plan, to create/amend and generally update things.

The idea would be :

  1. update the model
  2. create a new migration
  3. add that to the migration plan.

so you might have something like this:

[TableName(“tableName”)]
[PrimaryKey(“id”)]
[ExplicitColumns]
internal class MyTable
{
    [Column("id")]
    [PrimaryKeyColumn]
    public int Id { get; set; }

    [Column("Add1")]
    public string Add1 { get; set; }
    
    [Column("Add2")]
    public string Add1 { get; set; }

    // new column added to the model. 
    [Column("Add3")]
    [NullSetting(NullSetting = NullSettings.Null)]
    public string Add3 { get; set; }
}


internal class AddColumnToTableMigration : AsyncMigrationBase
{
    public AddColumnToTableMigration(IMigrationContext context) : base(context)
    {
    }

    protected override Task MigrateAsync()
    {
        if (TableExists("tableName"))
        {
            if (!ColumnExists("tableName", "Add3"))
            {
                Create.Column("Add3")
                    .OnTable("tableName")
                    .AsString()
                    .Nullable()
                    .Do();
            }
        }

        return Task.CompletedTask;
    }
}

and then you add that to the migration plan.

migrationPlan.From(string.Empty)
   .To<AddCommentsTable>("blogcomments-db")
   .To<AddColumnToTableMigration>("add_column")

the when the site is first installed, the migrations run, the first one would probably create the table and the second one will check if the table has the Colum and add it if it’s missing.

The NullSetting, just lets the value be null, and given that you will be adding this to an exsiting table, and rows, you will want this, so all the existing values are still valid

Personally, i think the examples mix it all up a bit (mainly to keep it all in the same code files i think).

  • i preferer to separate out the migration plan to it’s own class, so you can see / alter it without looking at the code that actually starts / manages the migration process.
  • For neatness i also have folders per migration so i can see what i am doing for each one.
  • Some constants are better for tableNames etc, or you just get a typo and get lost in a migration (not that i have every done that! oh no. :person_shrugging: )

There are some more examples of migrations here :

(edited - example migration was missing a .Do())

3

Thank you for pointing me in this direction