Database Migrations

[techdragon]

TLDR Summary: Can we get first class support for code first Database Migrations... like Django, Laravel, Rails, etc

This feature has been requested before in past support/community channels so I'm re-requesting it here now that ServiceStack has migrated to using GitHub Discussions for community feedback/engagement.

Database Migrations are basically the one headline feature ServiceStack doesn't have a great answer for. It feels, at least to me, like OrmLite's create/drop/alter stable support isn't enough in this day and age. ServiceStack feels extremely modern and compelling, but the moment I have to deal with database schema changes it feels like I'm warping back in time an entire decade or two to when first class code first database change management support was considered an optional extra.

ASP.Net has them from EF/EF.Core, Rails has them, Laravel has them, Django has them, Phoenix has them, lower level database frameworks like Ecto and SqlAlchemy have them... The list goes on and on... to be honest outside of the .Net and Java worlds where there is lots of legacy "enterprise style" "database first" work and the Wordpress ecosystem where everyone is just doing it all manually with SQL even for brand new code... having support for managing the changes you make to your database as you develop your database models, is considered a normal and desirable feature outside of the "micro-framework" world where frameworks like Flask, et.al. make a deliberate design choice come with as few capabilities as possible. (and to be fair often the most popular library that work with a micro-framework will be a wrapper around an ORM library that typically includes migration support)

I've followed ServiceStack for years now and I'm just puzzled at the situation with database migrations. This is a big framework that does a lot of awesome stuff to help my development productivity. Yes its true that no migration framework is perfect for all potential database changes we could theoretically want to make, but neither is any particular ORM perfect at generating the best possible SQL queries, yet ServiceStack has OrmLite and it does an excellent job.

I'm not expecting EF Core style automagically inferred forward backward migrations... I'd be happy with something simpler like integrating FluentMigrator and having something in ServiceStack that automatically generate the migration file that FluentMigrations will use. This would be pretty similar to the workflow in Django which has distinct "make migrations" and "migrate" steps/commands, offers a lot of power and is quite flexible in that it also allows developers to mix their own manually written migrations in with the into the framework's in order to handle more complex things like otherwise unsupported operations and "data migrations" that don't change the table schema.

[mythz]

In this forum post I've discussed the db migrations we used at StackOverflow and what we're doing now, which is basically running OrmLite Modify Schema APIs in unit tests which uses ExecuteSql() when needing to execute raw custom SQL not available in OrmLite typed APIs.

Whilst other devs on that thread recommend using DbUp.

It's not something we've looked into since I don't see much friction with my preferred solution as-is, whilst everyone has their own solution they prefer & have experience using, resulting in there being not much demand for a OrmLite specific solution which wouldn't justify the time & effort in implementing & maintaining one.

[techdragon]

Its true that there are workarounds and its possible to implement a solution from the primitives OrmLite provides as you yourself said in that forum post, but you ( @mythz ) also said you wanted to implement this feature eventually:

Eventually I’d like to formalize a proposed simple solution like the above that makes use of a migrations state table so you could rerun a generic command to execute all migrations and it would only run any new migrations ... in alphabetical order.

Which is why I'm posting the request here now that this is the new place for the community to engage on things like feature requests. It was the 6th most requested feature on the old UserVoice so its clear that there is at least some demand. Its a request that users have had in the past, and that you have acknowledged that you would like to add eventually, so its worth making sure that the request remains visible, and users continue to have a place to add their support for. 😄

It's also worth mentioning that any users that truly "demand" this feature from a framework are likely simply choosing to use other frameworks because ServiceStack doesn't have any built in Database Migrations, they would then be unlikely to participate anywhere in the community (like the forums, or UserVoice or here) asking for it as a feature and this could be skewing the perspective of how desired the functionality is. As a paid product with a customer acquisition funnel there may be an element of Sampling Bias with respect to users that become engaged enough to ask for new features and the absence of the features they might ask for before they make the decision to engage with the product.

[mythz]

Yep raising it again here to give it more visibility is a good idea 💡

[pedoc]

Copy from ServiceStack/ServiceStack@0112c3c#commitcomment-81497037

Hi,glad to see the official start to provide built-in support for Migrate,but I have a suggestion

I think there are a lot of limitations and inconsistencies in building Migrate functionality in the form of Attributes

1. For example, the ToDelete field needs to be deleted in the code here, and deleting the database field generally means deleting the model field at the same time, and the current implementation must retain the model field.
2. Sometimes model adjustments need to follow a certain order, for example, you need to delete the foreign key table before deleting the foreign key
3. These model structure adjustments may often exist multiple times, and the current practice of using Attribute cannot preserve the history of these operations
4. Sometimes in addition to modifying the name, there is also a modification type, or even adding an index, etc.

In view of the above reasons, I personally think that Attribute's solution cannot solve these problems very well. If I want to implement the Migrate function, I am more inclined to learn the practice of EFCore, provide a set of infrastructure, and let users organize the code structure by themselves. For example, the possible sample code is as follows:

public abstract class MigrateBase
{
    protected readonly IDbConnection db;
    protected MigrateBase(IDbConnection db)
    {
        this.db=db;
    }

    public virtual int Order { get; } = 0;
    public abstract string Name { get; }
    public virtual string Description { get; }

    public virtual void Up() { }
    public virtual void Down() { }
}


public static class MigrateExtensions
{
    public static bool Migrate(this IDbConnection db,params Assembly[] migrationAssemblies){
        //1 Find all MigrateBase subclasses in a given assembly and sort by Order
        //2 Iterate over all Migrators and execute Migrator.Up And Migrator.Down,And record the execution history to a file or database
    }
}

public class Book {
    [AutoId]
    public int Id{get;set;}
    public string Field2{get;set;}
    public string Field3{get;set;}

    public string Name{get;set;}//new field
    //public double ToDelete{get;set;} //Field removed from model
}
public class BookMigrate:MigrateBase{
    public override string Name => "Book First Migrate";

    public override void Up()
    {
       db.AddColumnIfNotExist<Book>(t=>t.Name);
    }

    public override void Down()
    {
        if(db.ExistColumn<Book>("ToDelete")){
            db.RemoveCloumn("ToDelete");
        }
    }
}

Call Sample

var dbFactory=new();
var db=dbFactory.Open();
db.Migrate();

does this help? 😄

[pedoc]

I will attach a minimal implementation (this is based on my usage on a real project)

https://github.com/pedoc/OrmliteMigrationExample.git

[pedoc]

Disadvantages of the current implementation

1. The lack of an automatic generation mechanism like EFCore requires programmers to do it manually
2. The execution history is not currently recorded, but the change history can be reflected in the migration file itself
3. The execution results are not tracked, causing the migration to be executed every time the project is run

[nukedbit]

I also needed something that i could be run during deploy, independently, so i made a little framework for my own needs, it is a fresh tool so it is going to need more work, but for now is working for me.
It basically has basic migration and it store on the db on a specific table if the migration has already run, and it use a cli tool that can be run from CI.
It is similar to EF way but way more simpler :)

https://gitlab.com/nukedbit-open/nukedbit-ormlitemigrations

https://gitlab.com/nukedbit-open/nukedbit-ormlitemigrations/-/tree/main/src/MigrationTestLib

[mythz]

Cool, yeah we've also been using unit tests to apply schema changes up till now, hopefully we can create a nice solution that's appealing to others that don't have their own solution they're comfortable with.

[nukedbit]

I was just reading your answer below, i think the solution is great and could replace most of what i have done, they are very similar. Also i like the idea of attributes for those easy changes that don't need order of operation.
Going to check your impl very soon :)

[mythz]

Thanks for the feedback! I'll respond to your highlighted issues inline:

1. For example, the ToDelete field needs to be deleted in the code here, and deleting the database field generally means deleting the model field at the same time, and the current implementation must retain the model field.

There's no time dependent coupling since your App Data Models (which represent the current state of the DB) are not used in the migrations, each migration contains an inner class of the Data Model containing either the initial state (v1) or the deltas between each migration.

So your Book example would be written in 2 migrations as:

[Description("Initial Book Version")]
public class Migration1000 : MigrationBase
{
    public class Book 
    {
        [AutoIncrement]
        public int Id { get; set; }
        public string Field2 { get; set; }
        public string Field3 { get; set; }
        public double ToDelete { get; set; }
    }
    
    public override void Up()
    {
        Db.CreateTable<Booking>();
    }
}

[Description("Updated Book Table")]
public class Migration1001 : MigrationBase
{    
    public class Book 
    {
        //[AddColumn] (Optional  default)
        public string Name { get; set; } //new field
        [RemoveColumn]
        public double ToDelete { get; set; }
    }
    
    public override void Up()
    {
        Db.Migrate<Booking>();
    }
}

2. Sometimes model adjustments need to follow a certain order, for example, you need to delete the foreign key table before deleting the foreign key

3. These model structure adjustments may often exist multiple times, and the current practice of using Attribute cannot preserve the history of these operations

This example is just showing the declarative approach for typical schema changes for adding/removing or renaming properties/columns, you can use all of OrmLite (or alt ORMs like Dapper since Db is just an ADO.NET DB Connection). I expect this to be the more productive approach since you can copy new properties from your App's Data Model that you add/remove/rename instead of writing the procedural code to perform the same schema changes.

But instead of the declarative approach you can use OrmLite's existing Modify Schema commands to procedurally modify the databse.

4. Sometimes in addition to modifying the name, there is also a modification type, or even adding an index, etc.

You can add a column with db.AddColumn<T>(x => x.Column) which will create type and any indexes, we support renaming but don't have explicit APIs or change column type which can require bespoke code to handle converting re/populating data. You'll have access to execute Custom SQL so you'll be able to execute any customizations needed.

The lack of an automatic generation mechanism like EFCore requires programmers to do it manually

I don't like the magic of automated generation and prefer the explicitness of this approach where the deltas between migrations and what each migration is doing is clearly visible and tracked through source control, it also shouldn't require additional effort as devs would use Migrations to perform the actual schema changes, so it would contain the exact schema changes that are applied to the database and when they're checked-in other devs will be able to apply any new migrations locally so their DB would be in sync with the App's code which are both checked into source control.

In future versions we can look at generating the initial migration script to help bootstrap their App to start using this feature, we could generate deltas but if following the process of capturing all schema changes in migrations there would be no need for it.

The execution history is not currently recorded, but the change history can be reflected in the migration file itself

We record the state of migrations run, but would you also like to also capture the entire SQL executed? Yeah that's a good idea, will add it thx.

The execution results are not tracked, causing the migration to be executed every time the project is run

The Migration table does maintain which migrations are applied so only new migrations not applied to that DB are run.

Thanks for the interest & feedback!

[pedoc]

Happy to consider any concrete suggestions on removing boilerplate.

The main boilerplate code here comes from the inner class copying the entity class members.but must admit this is more type safety and declarative, and the implementation is refreshing to me.

My main question is that more Attribute are introduced, and Attribute cannot cover all scenarios and are easy to misuse (such as applying RenameAttribute to entity class members, but must be added to migration inner classes).

What interfaces and classes?

This is divided into 2 parts, for your implementation they are RemoveColumnAttribute RenameColumnAttribute AddColumnAttribute,Of course, these Attribute are required in the current implementation.

The other part is that @nukedbit's implementation introduces IMigrationBuilder IMigrationExecutor MigrationConnectionFactory, etc.

Currently, I think your implementation would be perfect if it could override migration business(provide order control ability)

[nukedbit]

@pedoc Yeah my implementations has a few more interfaces, but those are not meant to be used by the consumer, they are the infrastructure, that is needed currently for the global cli tool, so it can resolve them trough reflection. I also needed a factory so it could resolve connection string details, with a custom implementations, but has i said it is not meant to be used from others peoples has i was still testing and evolving this implementation.
At this point with the ServiceStack Implementation, most of my code can probably be removed/refactored.

[nukedbit]

@mythz The idea of inner types inside each migration looks very interesting to me, i need to try it.

Basically if understood correctly you would have the full Type implemented externally, and on each migration class you only add the properties that need to add or alter, on each migration over time.

I think is pretty clean and has the benefit to show what changed on that specific time without clutter, of adding manual calls to Db.Alter.. , but we can specialise with hand written code when needed.

One thing if not already I would like to see is that the inner class could be made internal or private, so it is not shown on auto import on tools like rider, this is of course a very minor issue just to avoid confusion.

This overall to me look as a good starting point without to much complexity, like for example EFCore where migrations are unreadable at first glance. This also seem easy to improve in the future if needed.

[mythz]

Basically if understood correctly you would have the full Type implemented externally, and on each migration class you only add the properties that need to add or alter, on each migration over time.

Yep exactly, the migrations capture all the states to go from the initial state to if defined correctly will end up with the App's current data model (which isn't used in any of the migrations).

One thing if not already I would like to see is that the inner class could be made internal or private, so it is not shown on auto import on tools like rider, this is of course a very minor issue just to avoid confusion.

That'll be up to you but IDE's generally doesn't show inner classes unless explicitly requested by static reference using the prefix of the outer class, you could leave it private and make it public when you need it, e.g. the MigrateBookings.cs tests uses it to refer to the old property name:

[Notes("Update Bookings Columns")]
public class Migration1001 : MigrationBase
{
    public class Booking
    {
        [RenameColumn(nameof(Migration1000.Booking.OldName))]
        public string? Name { get; set; }
    }

    public override void Up() => Db.Migrate<Booking>();
}

Which also makes logical sense in that the property exists in the previous migration that added it but not in this one which renames it. Of course you could use the "OldName" string literal to avoid the static inner class reference.

[nukedbit]

That'll be up to you but IDE's generally doesn't show inner classes unless explicitly requested by static reference using the prefix of the outer class, you could leave it private and make it public when you need it, e.g. the [MigrateBookings.cs]

Right, inner classes are excluded from completion, i got confused.

(https://github.com/ServiceStack/ServiceStack/blob/main/ServiceStack.OrmLite/tests/ServiceStack.OrmLite.Tests/Migrations/MigrateBookings.cs) tests uses it to refer to the old property name:

[Notes("Update Bookings Columns")]
public class Migration1001 : MigrationBase
{
    public class Booking
    {
        [RenameColumn(nameof(Migration1000.Booking.OldName))]
        public string? Name { get; set; }
    }

    public override void Up() => Db.Migrate<Booking>();
}

Which also makes logical sense in that the property exists in the previous migration that added it but not in this one which renames it. Of course you could use the "OldName" string literal to avoid the static inner class reference.
Good i didn't think about referencing the old property with nameof, i think is a nice touch that will make it easy to navigate the code.

[mythz]

This is divided into 2 parts, for your implementation they are RemoveColumnAttribute RenameColumnAttribute AddColumnAttribute,Of course, these Attribute are required in the current implementation.

They're only required when using the declarative approach used by Db.Migrate<T>(), you need some way to define what behavior you want to apply to each property and using attributes is idiomatic in C# for this, but you don't need to use this and can use whatever you want in your Up() impl, e.g. using the procedural Modify Schema APIs instead.

[pedoc]

Yes, I understand.

The current implementation doesn't seem to provide the dependency support discussed earlier (i.e. there needs to be a mechanism to ensure the execution order of the various Migrators), am I missing it? Or is there no plan for this support yet?

[mythz]

The current implementation doesn't seem to provide the dependency support discussed earlier (i.e. there needs to be a mechanism to ensure the execution order of the various Migrators), am I missing it? Or is there no plan for this support yet?

Yes it's order dependent by name where it executes migrations in alphabetic order and why we suggest a numerical naming strategy like:

public class Migration1001 : MigrationBase {}
public class Migration1002 : MigrationBase {}

Order is important as it's used to determine which migrations have run / need running.

You can use [Description] attribute (either ServiceStack's or .NET's Description attribute) to describe your migration which is logged in migration output and captured in the migration table we also added [Notes] alternative since NUnit has it's own [Description] attribute which causes an ambiguous reference in unit tests, so it's easier to just use the different [Notes] attribute in unit tests instead of using fully qualified attribute reference in every test class.

[mythz]

It's not an assumption, it's a rule for using this feature .The reason for using the name is that it's a symbol that follows a numerical naming convention that you wouldn't change since it's paramount that order is preserved. When you create a separate file per migration you can easily tell what the order of migrations are (since file names are automatically sorted), what the last migration was and if you copy & paste you can't have duplicate class names, none of this is possible using Attributes for ordering which can't be enforced, has poor discoverability and is prone to human errors.

We already maintain the date each migration is started & completed, which isn't a factor when determining order, only class names are.

[pedoc]

Ok, there is also a possible risk of using class names as the order, that is, name collisions when multiple people develop collaboratively

Consider the following scenario:

Current project tree:

ProjectA
    Migrations
        Migration1001.cs
        Migration1002.cs

Participants Developers A and B, at this time their source code is exactly the same,then

Developer A tries to add Migration1003.cs, then commit
Developer B tries to add Migration1003.cs, then commit

Will end up with a git merge conflict.

This problem is very likely to occur under the current rules, so,shouldn't we make the name a sorting factor?

[mythz]

This is another reason for using class names since if you have duplicate class names you want merge conflicts and build errors so they're identified immediately and can be resolved before any migrations are run.

[pedoc]

Thanks for the clarification, it sounds reasonable since even using a separate Order field to control the order there is a risk of conflict.

As of now, I have no other suggestions for current implementations, maybe just testing them in real projects.
Thanks again for the clarification. 😄

[mythz]

Right, the difference being you'll get silent conflicts hidden in file names maintained by other devs that wont be caught by git conflicts or broken builds.

[Siliconrob]

Please outline the Down part of db migrations as this is important when doing testing and validation.

[mythz]

Now that reverting support has been implemented I'll fill in how the rest of it fits together.

To demonstrate I'll use the Booking Migrations in OrmLite.Tests/Migrations used in my initial comment to show how revert works.

To revert the initial migration of creating the Booking table, you'd just drop the table:

[Notes("Create initial Database")]
public class Migration1000 : MigrationBase
{
    public class Booking
    {
        [AutoIncrement]
        public int Id { get; set; }
        public string? OldName { get; set; }
        public double ToDelete { get; set; }
    }

    public override void Up() => Db.CreateTable<Booking>();
    public override void Down() => Db.DropTable<Booking>();
}

To revert the declarative schema changes executed by Db.Migrate<Table>() you can use Db.Revert<Booking>():

[Notes("Update Bookings Columns")]
public class Migration1001 : MigrationBase
{
    public class Booking
    {
        [RenameColumn(nameof(Migration1000.Booking.OldName))]
        public string? Name { get; set; }
        
        public RoomType RoomType { get; set; }
        
        [RemoveColumn]
        public double ToDelete { get; set; }
    }
    public enum RoomType {} // Values not necessary (Enum's saved as string by default)

    public override void Up() => Db.Migrate<Booking>();

    public override void Down() => Db.Revert<Booking>();
}

Be mindful that whilst this will reverse the schema changes but some operations aren't completely reversible, e.g. if you remove a column then revert to add it again, the data in the column will be lost. Implementing Down() is optional but is required if you ever intend to revert migrations. Personally I've only ever adopted a "roll-forward" approach where any issues with a migration would be resolved in the next one, but having implemented it I can see that it's useful in iterating on a feature, e.g. instead of creating multiple migrations it can be useful to revert, refactor then re-run a migration locally before checking in the feature in source control.

For more complex migrations you'd need to reverse the operations executed in Up() as seen in Migration1002.cs:

[Notes("Add Player Feature")]
public class Migration1002 : MigrationBase
{
    //...
    public override void Down()
    {
        // Clear FK Data
        Db.DeleteAll<Level>();

        // DROP ForeignKey Tables in dependent order
        Db.DropTable<Level>();
        Db.DropTable<Player>();

        // DROP tables without Foreign Keys in any order
        Db.DropTable<Profile>();
        Db.DropTable<GameItem>();
    }
}

Running migrations from command-line

I believe the ef tool uses some magic to scan your App's code to extract out the connection strings and other db context artifacts, IMO that approach is too magical/fragile and a cleaner solution is to just run your App in "task mode" where it has access to your App's DB configuration that's used in executing the registered task, than immediately exits so migrations can be performed by CI in separate stages from your running App.

Here's how you'd register the "Migration Tasks" with your App via Modular Startup configuration:

public class ConfigureDbMigrations : IHostingStartup
{
    public void Configure(IWebHostBuilder builder) => builder
        .ConfigureAppHost(afterAppHostInit:appHost => {
            var migrator = new Migrator(appHost.Resolve<IDbConnectionFactory>(), typeof(Migration1000).Assembly);
            AppTasks.Register("migrate", _ => migrator.Run());
            AppTasks.Register("migrate.revert", args => migrator.Revert(args[0]));
            AppTasks.Run();
        });
}

Whilst non-ServiceStack ASP.NET Core Apps can instead run AppTasks before app.Run() in Program.cs:

var migrator = new Migrator(app.Services.Resolve<IDbConnectionFactory>(), typeof(MyApp.Migrations.Migration1000).Assembly);
AppTasks.Register("migrate", _ => migrator.Run());
AppTasks.Register("migrate.revert", args => migrator.Revert(args[0]));
AppTasks.Run();

app.Run();

This will allow you to run any pending migrations from the command-line with:

$ dotnet run --AppTasks=migrate

The format to revert a migration is:

$ dotnet run --AppTasks=migrate.revert:<name>

Where name can be the name of the Migration you want to revert to (Inclusive) or you can last to revert the last migration or all to revert all migrations.

To make this easier to use and remember we'll add them as npm scripts:

{
  "scripts": {
    "migrate": "dotnet run --AppTasks=migrate",
    "revert:last": "dotnet run --AppTasks=migrate.revert:last",
    "revert:all": "dotnet run --AppTasks=migrate.revert:all"
  }
}

Which you can run as:

$ npm run migrate
$ npm run revert:last
$ npm run revert:all

Rider provides a nice UX for running npm scripts which you can do directly from the IDE where it will print all executed SQL output in the console window:

Failed migration behavior

Executing migrations from the command-line would also be how they're run in CI after deploying your App, where if it succeeds the deployment will be successful and the new App will be run, but a failed migration will cause deployment to fail and should revert to using the old App.

Migrations are executed within a transaction that's rolled back if it fails, however you should be aware of the limitations of your RDBMS, e.g. newer MySQL versions has caveats on DDL Statements which can be rolled back, but otherwise it's fairly well supported on PostgreSQL, SQL Server, SQLite.

Running migrations from unit tests

The migration output should typically be enough to identify any issues, but you can debug in a unit test with:

var migrator = new Migrator(DbFactory, typeof(Migration1000).Assembly);
var result = migrator.Run();

result.Succeeded // true if successful
result.MigrationsRun // instances of MIgrationBase, can inspect any exception in `Error` for any Exceptions and `Log` for SQL
result.TypesCompleted // Types succesfully completed

You can revert using the Revert() method which accepts the same options as the command-line, e.g. Migration Type name, "all" or "last":

var result = migrator.Revert(Migrator.All);

Running Migrations individually

Should you ever need to, you can also run individual Migrations with the static Down() and Up() methods, e.g:

Migrator.Down(DbFactory, new[]{ typeof(Migration1001), typeof(Migration1000) });
Migrator.Up(DbFactory, new[]{ typeof(Migration1000), typeof(Migration1001) });

These lets you execute migration logic out-of-band where they have no impact on the migration table, so they could be useful in developing & iterating migration logic by removing/re-adding a feature without impacting any migration state.

and should you need to clear your migration table, e.g. at the start of tests you can run:

var db = DbFactory.Open();
Migrator.Clear(db);

Feedback Welcome

I believe this feature is functionally complete if you want to help test it it's now available from v6.2.1 that's now available on MyGet. Please also let me know if there's any other features you'd like to see in this first release.

[jonathan-rvezy]

There is an issue if a specific migration doesn't exist on the branch you're currently working on it will fail, and attempt to re-run all migrations.

If someone ran migration1010 on a branch ... and then switched to another branch and ran migration1009 ... it will fail.

The migrations runs this SQL command

SELECT TOP 1 "Id", "Name", "Description", "CreatedDate", "CompletedDate", "ConnectionString", "NamedConnection", "Log", "ErrorCode", "ErrorMessage", "ErrorStackTrace", "Meta" 
FROM "Migration"
ORDER BY "Name" DESC

Which causes it to only be aware of the last migration it ran, however since the last migration1010 doesn't exist on the current branch it seems to think that ALL the migrations need to be run, thus either messing up the database (depending on what the first migration was), or preventing it from running at all since it will fail.

I suggest this is changed to fetch all migrations that have been successfully run, and then only run the ones that haven't been run in order as it already does today.

[mythz]

That should already be resolved from the latest v6.10.1+ that's now available in our pre-release packages

[jonathan-rvezy]

Hi, we've updated to the most recent version which has this fix ... however it seems that it's still not working as expected. The migrations must -still- be in sequential order otherwise they don't run.

Consider this scenario...

You have 3 engineers working on the project, who have all written a migration.

Engineer A writes Migration0001
Engineer B writes Migration0002
Engineer C writes Migration0003

Engineer A merges, and it runs successfully.
Engineer C merges, and it runs successfully.
Engineer B merges, and while no errors occurred, it ignored the migration because a "later" migration has already been run.

The expected behaviour is that Engineer B's migration (Migration0002) still runs because it hasn't been run yet.

[mythz]

Migrations absolutely need to be run in sequential order.

[jonathan-rvezy]

Another issue that I've ran into, is that if you have a failed migration it will prevent migrations from running unless you go into the database and delete the failed migration row. Shouldn't it just mark it as failed, and attempt to run it again?

[jonathan-rvezy]

Ah ok. I tried the AlterColumn, but it's attempting to make it a PRIMARY KEY.

public class Migration1693943006 : MigrationBase
{
    public class PreviousTableA
    {
        public long A { get; set; }
    }
    public class TableA
    {
        [RenameColumn(nameof(PreviousTableA.A)), DecimalLength(17, 2)]
        public decimal AA { get; set; }
    }

    public override void Up()
    {
        Db.Migrate<TableA>();
        Db.AlterColumn<TableA>(x => x.AA);
    }

    public override void Down()
    {
        Db.Revert<TableA>();
        // haven't quite figured out how to revert the AlterColumn statement without writing custom SQL.
    }
}

Output

SQL: EXEC sp_rename 'TableA."A"', "AA", 'COLUMN';
SQL: ALTER TABLE "TableA" ALTER COLUMN "AA" DECIMAL(17,2) PRIMARY KEY;

[mythz]

It's because it follows OrmLite Data Model conventions for determining primary keys, i.e. defined either with the [PrimaryKey] or [AutoIncrement] attribute or has an Id column otherwise it assumes the first column in the data table is the PrimaryKey.

Instead of using Migrate you could add a PrimaryKey to the table then use the explicit Alter/Rename column APIs, e.g:

public class PreviousTableA
{
    public int Id { get; set; }
    public long A { get; set; }
}
public class TableA
{
    public int Id { get; set; }
    [RenameColumn(nameof(PreviousTableA.A)), DecimalLength(17, 2)]
    public decimal AA { get; set; }
}

Db.RenameColumn<TableA>(x => x.AA , nameof(PreviousTableA.A));
Db.AlterColumn<TableA>(x => x.AA);

[jonathan-rvezy]

Db.Migrate isn't the issue though, it's DB.AlterColumn that is causing the problem.

[jonathan-rvezy]

This worked.

public class Migration1693943006 : MigrationBase
{
    public class PreviousTableA
    {
        public long A { get; set; }
    }
    public class TableA
    {
        [Ignore]
        public Guid Id { get; set; }
        [RenameColumn(nameof(PreviousTableA.A)), DecimalLength(17, 2)]
        public decimal AA { get; set; }
    }

    public override void Up()
    {
        Db.Migrate<TableA>();
        Db.AlterColumn<TableA>(x => x.AA);
    }

    public override void Down()
    {
        Db.Revert<TableA>();
        // haven't quite figured out how to revert the AlterColumn statement without writing custom SQL.
    }
}

[mythz]

Cool, nice solution for being able to use [Ignore] to be able to use Migrate and AlterColumn on the same class.

FYI Db.AlterColumn was an issue because you're using a partial class without a PK, adding a PK to the table will allow you to use it (since it wont assume it's the PK), but then you wouldn't want to use Db.Migrate and would use the explicit RenameColumn/AlterColumn APIs instead.

[jonathan-rvezy]

They do, but they also don’t. When working in a multi-engineer environment, you will often create unrelated migrations where the order does not matter for a span of migrations. In our particular case we just create migrations based on timestamp, this ensures that the likelihood of name collisions is extremely low. We expect them to migrate in order, but we also expect that should a migration be missed, that it will be executed the next time around. The only reason a migration would be missed, is because it didn’t exist at the time. Having to rename migrations at release time is undesirable as it would require having to revert and reapply the migration in the various environments it was already run against. Additionally in some rare cases migrations are destructive in nature and therefore reverting them is very undesirable and usually requires restoring from a backup.

…

On Mon, Oct 23, 2023 at 10:04 PM Demis Bellot ***@***.***> wrote: Migrations absolutely need to be run in sequential order. — Reply to this email directly, view it on GitHub <#22 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AWTY3TH7MTDX3I5GJPSZNY3YA4OZZAVCNFSM5YRIN2O2U5DIOJSWCZC7NNSXTOKENFZWG5LTONUW63SDN5WW2ZLOOQ5TOMZWGQ3TMNQ> . You are receiving this because you commented.Message ID: ***@***.***>

[mythz]

We're only going to be running and reverting migrations in sequential order.

I don't see how it's possible migrations are missed if developers adding timestamps when they commit their completed migration. If for some reason it was missed you should be able to rename the missed migration to use a new timestamp.

Otherwise if needed you can also Run Migrations individually.

[jonathan-rvezy]

The timestamp is generated at the time of writing the feature. When they get released may not be in the order they were created. As stated in my previous reply, renaming a migration at the time of release isn’t desirable at all. Any other framework I’ve worked with doesn’t have a strict restriction of not running missed migrations. If you don’t want to default that on, at least add a flag or something. Running individual migrations creates an extra unnecessary and forgettable step in the deployment process.

…

On Mon, Oct 23, 2023 at 10:24 PM Demis Bellot ***@***.***> wrote: We're only going to be running and reverting migrations in sequential order. I don't see how it's possible migrations are missed if developers adding timestamps when they commit their completed migration. If for some reason it was missed you should be able to rename the missed migration to use a new timestamp. Otherwise if needed you can also Run Migrations individually <https://docs.servicestack.net/ormlite/db-migrations#running-migrations-individually> . — Reply to this email directly, view it on GitHub <#22 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AWTY3TH2ZO4RSOYNBNNVNWLYA4RFNAVCNFSM5YRIN2O2U5DIOJSWCZC7NNSXTOKENFZWG5LTONUW63SDN5WW2ZLOOQ5TOMZWGQ4DOMA> . You are receiving this because you commented.Message ID: ***@***.***>

[mythz]

Then change it so it's renamed with an affixed timestamp when the completed migration is committed.
You'll need to come up with some solution as we're not going to run migrations out of order.