Migrations and CreateTableIfNotExists

#1

I’m confused about the reasoning for the DB Migrations.
I understand how they work, you create a MigrationXXXX class, in which you add the changes only, and the “AppTask” will add/remove/rename columns.

But, previously we have always used CreateTableIfNotExists functions, and that works great when putting the code on a new server.

However, I’m thinking that this won’t work anymore. As the “main class”, which represents a table, is always the most updated one. If I were to put my code on a new server, run CreateTable, that would create the most up-to-date definition of the table. But the migrations are of course based on some historical version of the table, and the changes thereafter.

So in practice, this means:

  1. I’ll have to backtrack each migration and try to reconstruct the class/table before the 1st migration.
  2. Then that “original table class” will be put in the very first migration, using CreatTable in there.
  3. I’ll have to maintain two “copies” of my table-classes from now on. One in the migrations, and one up-to-date one, that I’ll use everywhere in my code.

Is this how it’s supposed to work?

#2

Right, CreateTableINotExists works great for the first release, what DB migrations solves is how to handle schema changes after your App has already been deployed, previously you would’ve had needed to handle this yourself out-of-band, now all schema changes can be added in a new Migration which will only run that migration in deployed Apps that have run the previous migrations, whilst any App’s or new developers that haven’t run any migrations will run all of them to get to the latest state - represented by your App Data Models.

How would you handle schema changes to existing tables? You don’t need to use DB Migrations, you can continue using whatever strategy you were doing before.

Not clear why you’d do this, if you want to migrate to DB Migrations then you should just put all your App’s data models in the first migration. If you want your existing App’s to avoid running the migration you can just do a simple check that if the table exists to avoid running the first migration:

public override void Up()
{
    if (Db.TableExists<Table>()) return;
    //... create all App's tables
}

Once this migration is run this check can be removed as it wont run the first migration again which is maintained in the Migration table.

You wouldn’t need to “maintain” the table in Migration since that should never be touched again.

The future workflow would look like any new Tables are also copied in the next migration you’re working on, likewise any new columns you add or need to remove would also be copied into your next “working migration” where you’d then run Migrate locally to apply these schema changes to your local database.

I’ll often run Revert and Rerun Last Migration locally during development of a new feature that requires schema changes to revert and reapply new schema changes so that I constantly update and combine all schema changes into a single migration before checking the completed feature in.

#3

Thanks for clarifying. I guess there is no right answer, but it’s very useful to know a bit of the reasoning behind how it’s meant to be used. The “revert & rerun” seems like a good trick.

In my situation I have a mix of CreateTableIfNotExists and migrations, so that’ll need to be fixed: Any table that is involved in migrations, need to be first created by migrations, and use the pure migrations workflow. And then I’ll only maintain the newest version, and add migrations every time it changes (not maintain them after that).