TcKs.PgDbMigration

PgDbMigration

The opinionated .NET library for PostgreSql database migrations.

If you want easy-to-use db migration tool, you are on the right spot!

Build status

Installation from NuGet

dotnet add package TcKs.PgDbMigration

Usage & philosphy

The database migrations are batches of sql statements, which should be executed in correct order one by one.

In PgDbMigration is correct order ensured by MigrationOrderNumber. This order number has two parts:

• date-time part - the date (and time optionaly) when the migration was created
• number part - the order of migration in given date-time part

The three step migration can be written this way:

// Program.cs
return TcKs.PgDbMigration.Runners.ProgramRunner.Run();
// <<< This oneliner will runn all the migratoins.

/* >>> in other files >>> */

// the base class for all migration done in this day
public abstract record BaseMigration : Migration {
  protected BaseMigration(params int[] number)
    //         year, month, day
    : base(new(2023, 09,    26), number) { }
  }
}

// The first migration of the batch.
public sealed record M1 : BaseMigration {
  public M1() : base(1) {
    // The title is optional. But it is good practice to say,
    // what the purpose of migratoin is.
    Title = "Sample migration creating necessary extensions.";
  }

  // This method is doing the work.
  public override void UpgradeDatabase(NpgsqlTransaction transaction) {
    _ = transaction.ExecuteScalar(@"CREATE EXTENSION IF NOT EXISTS ""uuid-ossp"";");
    _ = transaction.ExecuteScalar(@"CREATE EXTENSION IF NOT EXISTS ""citext"";");
    _ = transaction.ExecuteScalar("CREATE SCHEMA IF NOT EXISTS admin;");
  }
}

// The second migration of the batch.
public sealed record M2 : BaseMigration {
  public M2() : base(2) {
    Title = "Sample migration creating admins.admin table.";
  }

  public override void UpgradeDatabase(NpgsqlTransaction transaction) {
    _ = transaction.ExecuteScalar(@"
        CREATE TABLE admin.admins (
          id uuid NOT NULL PRIMARY KEY DEFAULT uuid_generate_v4(),
          created_at timestamp with time zone NOT NULL DEFAULT now(),
          updated_at timestamp with time zone NOT NULL DEFAULT now(),
          disabled_at timestamp with time zone NULL,
          email citext NOT NULL UNIQUE,
          display_name text NOT NULL
        );
    ");
  }
}

// The third migration of the batch.
public sealed record M3 : BaseMigration {
  public M3() : base(3) {
    Title = "Sample insertion into admins.admin table.";
  }

  public override void UpgradeDatabase(NpgsqlTransaction transaction) {
    _ = transaction.ExecuteScalar(@"
        INSERT INTO admin.admins
            (email, display_name)
            VALUES
            ('[email protected]', 'Alice Awesome'),
            ('[email protected]', 'Bruno Best');
    ");
  }
}

Support

Feel free to open new issue if you have question or you found a bug.

Contributing

Contributors are vere welcome!

Open new merge-request and describe what purpose of the contribution is.

Especially contribution do documentation and examples are super welcome.

Authors and acknowledgment

• Jakub (TcKs) Müller - founder

License

This project is licencsed under MIT open source license.

Project status

This project is under development. The main branch contains tested and functional version of the code base.

I am very excited to extend and improve this library.