Chd.Library.Migrations 9.0.7

Migration library for .Net Core

Chd (cleverly handle difficulty) library helps you cleverly handle difficulty, writing code fastly and do your application stable.

📝 Table of Contents

• About
• Getting Started
• Injection PostgreSQL Migration Into The Project
• Injection Oracle Migration Into The Project
• Injection Mssql Migration Into The Project
• Injection SqlLite Migration Into The Project
• Apsettings Configurations
• Adding Schema Migration
• Adding Data Migration
• Usage
• Authors
• Acknowledgments

🧐 About

Migrations are a structured way to alter your database schema and are an alternative to creating lots of sql scripts that have to be run manually by every developer involved.

🏁 Getting Started

This migrations using a table called VersionInfo. Each record in this table contains a unique VersionId. Migration attribute have two parameters. First one is version number and second one is author name.

The library searches in the namespace of the class you enter when injected. It finding classes which inherited "Migration" class in this namespace. It runs the Up method of the classes which have a version number greater than the version number in the VersionInfo table. It runs the Down method of the classes which have a version number less than the version number in the VersionInfo table.

Prerequisites

You must use .net core 9.0 or higher

💉 Injection PostgreSQL Migration Into The Project

In your program.cs file, you should add the code below.

   var builder = WebApplication.CreateBuilder(args);
   ....
   .... 
   builder.Services.AddMigration<Program>(DatabaseType.PostgreSQL,"PostgreSQLTest");

💉 Injection Oracle Migration Into The Project

In your program.cs file, you should add the code below.

   var builder = WebApplication.CreateBuilder(args);
   ....
   .... 
   builder.Services.AddMigration<Program>(DatabaseType.Oracle,"OracleTest"); 

💉 Injection Mssql Migration Into The Project

In your program.cs file, you should add the code below.

   var builder = WebApplication.CreateBuilder(args);
   ....
   ....
   builder.Services.AddMigration<Program>(DatabaseType.MsSQL,"MsSQLTest"); 

💉 Injection SqlLite Migration Into The Project

In your program.cs file, you should add the code below.

   var builder = WebApplication.CreateBuilder(args);
   ....
   ....
   builder.Services.AddMigration<Program>(DatabaseType.SQLite,"SQLiteTest");

⚙️ Apsettings Configurations

You must add code below in appsettings.json

  "ConnectionStrings": {
    "SQLiteTest": "Data Source=mydb.db;",
    "OracleTest":"Data Source=MyOracleDB;User Id=myUsername;Password=myPassword;Integrated Security=no;",
    "MsSQLTest": "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;",
    "PostgreSQLTest": "IncludeErrorDetails = true;User ID=postgres;Password=postgres_admin_password;Server=localhost;Port=5432;Database=my_db;Integrated Security=true;Pooling=true;",
  }

🛠️ Adding Schema Migration

We make a sample schema migration for you. This migration creates a table called "student" and adds some columns to it. If you run the application, you can see tables whice you write in migration codes on the database.

We must give version of beginning 1 to 1000000 numbers to schema changes, because of having to separate schema changes and data changes. Then we give version id numbers after 1000000 to data changes.

We will write this migration for PostgreSQL word syntax. If you want to use another database, you can change the word syntax in the migration code.

    [Migration(1, "Mehmet Yoldaş")]
    public class ShemaChanges_V1 : Migration
    {
        public override void Down()
        {
        }
        public override void Up()
        {
            Create.Table("student").WithIdColumn().
            WithColumn("first_name").AsString().
            WithColumn("last_name").AsString().
            WithColumn("user_name").AsString().
            WithColumn("birth_date").AsDate();
        }
    }
    [Migration(1, "Mehmet Yoldaş")]
    public class ShemaChanges_V1 : Migration
    {
        public override void Down()
        {
        }
        public override void Up()
        {
            Create.Table("student").WithIdColumn().
            WithColumn("first_name").AsString().
            WithColumn("last_name").AsString().
            WithColumn("user_name").AsString().
            WithColumn("birth_date").AsDate();
        }
    }

    [Migration(2, "Mehmet Yoldaş")]
    public class ShemaChanges_V2 : Migration
    {
        public override void Down()
        {
        }
        public override void Up()
        {
            Create.Table("course").WithIdColumn().
            WithColumn("course_id").AsString().
            WithColumn("name").AsString();
        }
    }

    [Migration(3, "Mehmet Yoldaş")]
    public class ShemaChanges_V3 : Migration
    {
        public override void Down()
        {
        }
        public override void Up()
        {
            Create.Table("student_course").WithIdColumn().
            WithColumn("course_id").AsInt32().ForeignKey("course", "id").
            WithColumn("student_id").AsInt32().ForeignKey("student", "id");
        }
    }

    [Migration(6, "Mehmet Yoldaş")]
    public class ShemaChanges_V6 : Migration
    {
        public override void Down()
        {
        }
        public override void Up()
        {
            Create.Table("country").WithIdColumn().
            WithColumn("name").AsInt32();
            Create.Table("city").WithIdColumn().
            WithColumn("country_id").AsInt32().ForeignKey("country", "id").
            WithColumn("name").AsInt32();
            Create.Table("town").WithIdColumn().
            WithColumn("city_id").AsInt32().ForeignKey("city", "id").
            WithColumn("name").AsInt32();
            Create.Table("street").WithIdColumn().
            WithColumn("town_id").AsInt32().ForeignKey("town", "id").
            WithColumn("name").AsInt32();
        }
    }

    [Migration(7, "Mehmet Yoldaş")]
    public class ShemaChanges_V7 : Migration
    {
        public override void Down()
        {
        }
        public override void Up()
        {
            Create.Table("student_country").WithIdColumn().
            WithColumn("student_id").AsInt32().ForeignKey("student", "id").
            WithColumn("country_id").AsInt32().ForeignKey("country", "id");
        }
    }

    [Migration(4, "Mehmet Yoldaş")]
    public class ShemaChanges_V4 : Migration
    {
        public override void Down()
        {
        }
        public override void Up()
        {
            Create.Table("hold").WithIdColumn().
            WithColumn("hold_status").AsInt32().
            WithColumn("description").AsString();
        }
    }

    [Migration(5, "Mehmet Yoldaş")]
    public class ShemaChanges_V5 : Migration
    {
        public override void Down()
        {
        }
        public override void Up()
        {
            Create.Table("hold_v2_status_type").WithIdColumn().
            WithColumn("name").AsString().
            WithColumn("description").AsString();
            Create.Table("hold_v2").WithIdColumn().
            WithColumn("description").AsString();
            Create.Table("hold_v2_defination").WithIdColumn().
            WithColumn("hold_status_type_id").AsInt32().ForeignKey("hold_v2_status_type", "id").
            WithColumn("hold_id").AsInt32().ForeignKey("hold_v2", "id");
        }
    }
    [Migration(6, "Mehmet Yoldaş")]
    public class DataChanges_V6 : Migration
    {
        public override void Down()
        {
        }
        public override void Up()
        {
         Alter.Table("student").AddColumn("email").AsString();
        }
    }

📋 Adding Data Migration

We make a sample data migration for you. This migration adds some data to the table called "student". If you run the application, you can see data which you write in migration codes on the database.

We give version id numbers after 1000000 to data changes.

    [Migration(1000001, "Mehmet Yoldaş")]
    public class DataChanges_V1 : Migration
    {
        public override void Down()
        {
        }
        public override void Up()
        {
            Insert.Into("student").Row(new { first_name = "Mehmet", last_name = "Yoldaş", user_name = "mehmetyoldas", birth_date = DateTime.Now });
            Insert.Into("student").Row(new { first_name = "Ali", last_name = "Yılmaz", user_name = "aliyilmaz", birth_date = DateTime.Now });
            Insert.Into("student").Row(new { first_name = "Ayşe", last_name = "Kaya", user_name = "aysekaya", birth_date = DateTime.Now });
            Insert.Into("student").Row(new { first_name = "Fatma", last_name = "Kara", user_name = "fatmakara", birth_date = DateTime.Now });
        }
    }
    [Migration(1000002, "Mehmet Yoldaş")]
    public class DataChanges_V2 : Migration
    {
        public override void Down()
        {
        }
        public override void Up()
        {
            Insert.Into("course").Row(new { course_id = "1", name = "Math" });
            Insert.Into("course").Row(new { course_id = "2", name = "Science" });
            Insert.Into("course").Row(new { course_id = "3", name = "History" });
            Insert.Into("course").Row(new { course_id = "4", name = "Geography" });
        }
    }
    [Migration(1000003, "Mehmet Yoldaş")]
    public class DataChanges_V3 : Migration
    {
        public override void Down()
        {
        }
        public override void Up()
        {
           Insert.Into("student_course").Row(new { course_id = 1, student_id = 1 });
           Insert.Into("student_course").Row(new { course_id = 2, student_id = 1 });
           Insert.Into("student_course").Row(new { course_id = 3, student_id = 1 });
           Insert.Into("student_course").Row(new { course_id = 4, student_id = 1 });
           Insert.Into("student_course").Row(new { course_id = 1, student_id = 2 });
           Insert.Into("student_course").Row(new { course_id = 2, student_id = 2 });
           Insert.Into("student_course").Row(new { course_id = 3, student_id = 2 });
           Insert.Into("student_course").Row(new { course_id = 4, student_id = 2 });
        }
    }

After running application, you can see sample data in the database.

🎈 Usage

Just run the application to get the migrations work done. The library will automatically create the tables and columns you have written in the migration code. And it will add the data you have written in the data migration code to the tables.

✍️ Authors

• Mehmet Yoldaş - Linkedin

See also the list of contributors who participated in this project.

🎉 Acknowledgements

Thank you for using my library.