Daterpillar

The Problem

Your .NET project depends on a SQL database to store it's data, therefore you need a way to keep your SQL tables in-sync with your entities/classes.

The Solution

Daterpillar is a netstandard library keep your classes and tables in-sync by generating sql-migration-scripts from your project's .dll file.

Usage

Lets say you have the following class in your project.

// Notice the `User` class is decorated with the `Table` and `Column` attributes.

[Table()]
public class User
{
    [Key(), Column(AutoIncrement = true)]
    public string Id { get; set; }

    [Column("full_name", scale: 32)]
    [StaticId("ec35bf5d-9a40-4e6f-8412-852b81807a09")]
    public string Name { get; set; }

    public DateTime DOB { get; set; }
}

The NUGET package ships with a MSBuild target called GenerateDaterpillarMigrationScript. It will run after the project has been built, and produce a .sql script that should update your database schema, but first you will need to configure it. Open your .*proj project-file and add the following elements inside a <PropertyGroup> element.

<!-- This turn on the feature -->
<ShouldGenerateDaterpillarMigrationScriptAfterBuild>true</ShouldGenerateDaterpillarMigrationScriptAfterBuild>

<!-- This is where the script will be saved. -->
<DaterpillarMigrationsDirectory>migrations</DaterpillarMigrationsDirectory>

<!-- The script's language. -->
<DaterpillarSqlLanguages>MySQL</DaterpillarSqlLanguages>

<!-- This file represents the current state/structure of your live database. (Optional defaulats to 'snapshot.schema.xml') -->
<DaterpillarSnapshotFilePath>snapshot.schema.xml</DaterpillarSnapshotFilePath>

Once configured, whenever the project is built, a .sql script will be created.

# EXAMPLE: `V1.0.0__create_schema.mysql.sql`
CREATE TABLE `user`(
    `Id` VARCHAR(64) NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `full_name` VARCHAR(32) NOT NULL,
    `DOB` DATETIME NOT NULL
);

It is also possible to generate a new script at runtime.

var migrator = new Migrator();
migrator.GenerateMigrationScript(Language.MySQL, typeof(User).Assembly, snapshotFilePath, migrationDirectory, fileName);

Now that you have a migration-script you will probably want to run it. You can run the script manually or use other tools like Evolve, FlywayDB or Powershell.

More about .schema.xml

To generated the migration-script mentioned earlier, the difference between snapshot.schema.xml and [your-assembly].schema.xml is used to generate the script. So where did this [your-assembly].schema.xml file came from? The MSBuild target ExportDaterpillarSchema, and it is executed after each successful build. You can extend a schema by merging multiple schemas together. Lets say you want to add seed-data to a table, first you will have to add a [assembly: Import("seed.schema.xml")] attribute to your project.

// example: `./Properties/AssemblyInfo.cs`
...
[assembly: Acklann.Daterpillar.Import("seed.schema.xml")]
// or
// [assembly: Acklann.Daterpillar.Import("*.schema.xml")]
...

Next add the seed.schema.xml to your project, and make sure it is being copied next to the .dll (Copy To Output Directory: Copy if newer).

<schema xmlns="https://github.com/raw/Ackara/Daterpillar/master/src/Daterpillar/daterpillar.xsd">
    <!-- This script will be appended -->
    <script name="seed" language="MySQL">
    INSERT INTO User (full_name, DOB) VALUES ('Petra Ral', '2000-11-15');
    </script>
</schema>

You can also override or add columns by redefining a table.

<schema xmlns="https://github.com/raw/Ackara/Daterpillar/master/src/Daterpillar/daterpillar.xsd">
    <table name="User">
        <!-- This will rename the existing 'full_name' column -->
        <column id="ec35bf5d-9a40-4e6f-8412-852b81807a09" name="first_name">
            <dataType scale="64">varchar</dataType>
        </column>

        <!-- This will add a new column -->
        <column name="last_name">
            <dataType scale="64">varchar</dataType>
        </column>
    </table>
</schema>