Add an extension point to allow appending a custom migration operation in every migration

[vigouredelaruse]

what: extension point is available to add code executed before Migration.Up(), the equivalent of adding 'default' statements to Up()?

if it's possible, here's what i've tried and why
i want to automate this migration operation (minimize requirements for remembering to add code to Up())

migrationBuilder.Sql(
    "SELECT create_hypertable( '\"Stocks\"', 'Time');\n" +
    "CREATE INDEX ix_symbol_time ON \"Stocks\" (\"Symbol\", \"Time\" DESC)"
);

i can see other people circling this

i read this

public class MyMigrationsModelDiffer : MigrationsModelDiffer {

  public MyMigrationsModelDiffer(IRelationalTypeMappingSource typeMappingSource,
    IMigrationsAnnotationProvider migrationsAnnotations,
    IChangeDetector changeDetector,
    IUpdateAdapterFactory updateAdapterFactory,
    CommandBatchPreparerDependencies commandBatchPreparerDependencies)
    : base(typeMappingSource, migrationsAnnotations, changeDetector, updateAdapterFactory, commandBatchPreparerDependencies) { }

the canonical documentation refers to a custom operation called like this

migrationBuilder.CreateUser("SQLUser1", "Password");

but i want to automate this step => Add the following lines to the end of the Up method in the new migration.

migrationBuilder.CreateHyperTableMigration("somecolumn","someTimestampColumn")

what part of the documentation haven't read?

or is this actually a feature request for an extension point to run migrationbuilder statements before and after up?
please advise

[AndriySvyryd]

We don't have support for this, and I don't think anyone else has requested something like it.

[AndriySvyryd]

Can you explain more how it would work if we added support for it? Would you always add the same line in each migration? Does it change depending on the other operations?

[vigouredelaruse]

thanks for entertaining it this far @AndriySvyryd
the workflow in this case is hypertable creation when the provider is used against timescale db.

here's a reasonable discussion of it, although please note that i haven't yet made sure that what's described here is completeable today

having said that

• timescale can be integrated with ef core using keyless entities [Keyless] public class Stock in a readonly capacity

the workflow for adding a timescale entity to the dbcontext seems to be as normal

public DbSet<Stock> Stocks { get; set; } = default!;
public DbSet<Company> Companies { get; set; } = default!;

after create table timescale workflow calls for converting the table to a hypertable.

users integrating with efcore therefore need to add the below to their migrations once, when they add the entity to the dbset as usual

// Convert Stocks Table to Hypertable
// language=sql
migrationBuilder.Sql(
    "SELECT create_hypertable( '\"Stocks\"', 'Time');\n" +
    "CREATE INDEX ix_symbol_time ON \"Stocks\" (\"Symbol\", \"Time\" DESC)"
);

i'm suggesting an enhancement that would reduce the cognitive load of such 'modify the Up()' method workflows by some mechanism that fits ef core idioms like savechanges interceptors for example

in the interceptors case these are registered in OnConfiguring

if the proposed enhancement was able to follow the same pattern, the hypertable creation workflow would be

• add [Keyless] model
• add DbSet to dbcontext
• add IMigrationUpPreTrigger implementation to OnConfiguring that renders the hypertable and index creation sql

note that hyperscale users may want to use dbfunctions, in which case both the Up() and Down() methods would need to be modified

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql("drop function get_weekly_results(timestamp with time zone);");
    }

let me know where any lucidity slippage is occurring
thanks

[AndriySvyryd]

I see. You should be able to accomplish that with the extension points that we already have. You can add a custom annotation to every entity type, something like:

modelBuilder.Entity<Stock>().HasAnnotation("HypertableColumn", "Time");

Then, create a class derived from NpgsqlAnnotationProvider and override For(ITable table, bool designTime) to forward this annotation to the Table. Replace IRelationalAnnotationProvider service with this.

Then, create a class derived from NpgsqlMigrationsSqlGenerator and override Generate for AlterTableOperation, CreateTableOperation and DropTableOperation to generate the additional statement. Replace IMigrationsCodeGenerator with this design-time service.

I explain more how this works in #34454 (comment)

To avoid having to put the annotation on each entity type manually you can implement a custom convention with the rules that make sense for your scenario.