Meg 2.3.0

Meg - Dotnet Migration Tool

Meg is a simple migration command line tool that is inspired by conventions of tools such as rake and ecto, and Saturn.Cli. Its the missing migration tool that I wish dotnet had.

Support for Postgresql, with limited / experimental support for MSSQL, MySql, and SQLite is provided.

• create - Creates initial database by name.
• drop - Drops a database by name.
• reset - Drops and creates a database by name.
• migrate - Runs migrations in the specified migration folder. Defaults to folder "Migrations". Migrations are just SQL scripts.
• gen - Generates a migration via meg DSL.
• env - Print the meg environment to see what your default database connection strings, migration folder, and db provider are.

Why not just use Saturn.Cli? I like Saturn.Cli, but it feels somewhat coupled to the Saturn framework, and I prefer to use other frameworks like Giraffe independently. I wanted a Framework agnostic tool.

Install

# Global
dotnet tool install --global meg

# Local
dotnet new tool-manifest
dotnet tool install meg

QuickStart

dotnet meg create -d my_new_db -c "Server=localhost;User Id=postgres;Password=postgres;Database=postgres;Port=5432;"

#> Executing db command: SELECT 1 FROM pg_database WHERE datname='my_new_db'
#> Creating DB my_new_db because it doesn't exist yet
#> Database 'my_new_db' created.

dotnet meg gen.migration AddUsersTable users Id:Guid Name:String Email:String SubscriptionId:Guid:References:Subscriptions:Id

#> Wrote migration Migrations/1716128697_AddUsersTable.SQL


dotnet meg migrate -d my_new_db -c "Server=localhost;User Id=postgres;Password=postgres;Database=postgres;Port=5432;"

# > Running Query from Script:
CREATE TABLE "users" (
	"Id" UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
	"Name" VARCHAR(255),
	"Email" VARCHAR(255),
	"SubscriptionId" UUID,
	FOREIGN KEY ("SubscriptionId") REFERENCES Subscriptions("Id")
);
# > Query Result: -1

Env Vars

You can supplement command line usage with some ENV vars in your project to make it even easier to use Meg. Having these vars set will allow them to be used as defaults.

DB_CONNECTION_STRING # The connection string used by 'create', 'drop' and 'migrate' commands. Default if --connection-string isn't set.
DB_PROVIDER # postgresql | mssql | mysql | sqlite. Default if --provider isn't set.
DB_MIGRATION_DIRECTORY # directory of your migrations. Default if --migration-directory isn't set.
DB_NAME # the name of your application database. Default if --database-name isn't set.

Example using direnv .envrc

export DB_CONNECTION_STRING="Host=localhost;Database=postgres;Username=postgres;Password=postgres;"
export DB_PROVIDER=postgresql # postgresql | mssql | mysql | sqlite
export DB_MIGRATION_DIRECTORY=Migrations # directory of your migrations, defaults to "./Migrations"
export DB_NAME=dev # directory of your migrations, defaults to "./Migrations"

Then you can just call create / migrate without having to specify your connection string.

dotnet meg create

#> Executing db command: SELECT 1 FROM pg_database WHERE datname='my_new_db'
#> Creating DB my_new_db because it doesn't exist yet
#> Database 'my_new_db' created.

dotnet meg migrate

# > Running Query from Script:
# > CREATE TABLE TODOS (
# >    Id SERIAL PRIMARY KEY,
# >    Name varchar(255),
# >    Text TEXT
# > )
# > Query Result: -1

Meg Env

Utility command to print your meg env. Meg defaults to a localhost postgres connection string, with PostgreSQL as the provider and Migrations as the migration directory.

dotnet meg env
# > DB_CONNECTION_STRING: Server=localhost;Port=54322;Database=postgres;User Id=postgres;Password=postgres;
# > DB_PROVIDER: PostgreSQL
# > DB_MIGRATION_DIRECTORY: Migrations
# > DB_NAME: dev

Usage

Create

$USAGE: meg create [--help] [--db-name <db name>] [--connection-string <connection string>]
                  [--provider <postgresql|mssql|mysql|sqlite>]

OPTIONS:

    --db-name, -d <db name>
                          Specify the name of the database to create.
    --connection-string, -c <connection string>
                          Specify the database connection string for the Admin database. Must be able to create DBs
                          with the permissions of the user.
    --provider, -p <postgresql|mssql|mysql|sqlite>
                          Specify the database provider.
    --help                display this list of options.

Drop

$USAGE: meg drop [--help] [--db-name <db name>] [--connection-string <connection string>]
                [--provider <postgresql|mssql|mysql|sqlite>]

OPTIONS:

    --db-name, -d <db name>
                          Specify the name of the database to create.
    --connection-string, -c <connection string>
                          Specify the database connection string for the Admin database. Must be able to create DBs
                          with the permissions of the user.
    --provider, -p <postgresql|mssql|mysql|sqlite>
                          Specify the database provider.
    --help                display this list of options.

Reset

Drop then create the specified DB

$USAGE: meg reset [--help] [--db-name <db name>] [--connection-string <connection string>]
[--provider <postgresql|mssql|mysql|sqlite>]

OPTIONS:

    --db-name, -d <db name>
                          Specify the name of the database to reset.
    --connection-string, -c <connection string>
                          Specify the database connection string for the Admin database. Must be able to create DBs
                          with the permissions of the user.
    --provider, -p <postgresql|mssql|mysql|sqlite>
                          Specify the database provider.
    --help                display this list of options.

Migrate

Migrations will only run if they have not already been run based on entries that exist in your schema_migrations table. Migrations are unique by filename, and are executed in alphabetical order ascending. Migrations are SQL scripts that are read from your configured migration directory, either set by the env var DB_MIGRATION_DIRECTORY or by cli option --migration-directory.

The schema_migrations table will be automatically created the first time you run meg migrate successfully.

$USAGE: meg migrate [--help] [--db-name <db name>] [--connection-string <connection string>] [--migration-directory <migration directory>]
                   [--provider <postgresql|mssql|mysql|sqlite>]

OPTIONS:
    --db-name, -d <db name>
                          Specify the name of the database to run migrations for.
    --connection-string, -c <connection string>
                          Specify the database connection string for the database. Must be able to create and update
                          tables  with the permissions of the user.
    --migration-directory, -i <migration directory>
                          Specify the directory that contains your order-named migration .SQL files.
    --provider, -p <postgresql|mssql|mysql|sqlite>
                          Specify the database provider.
    --help                display this list of options.

Gen Migrations

You can generate migrations using a convenient, limited DSL. The output is a SQL script which is written to your migrations directory, either set by the env var DB_MIGRATION_DIRECTORY or by cli option --migration-directory. You can modify the output SQL after its generated if desired. All SQL is generated assuming you are CREATING a new table.

Each DB provider has different field representations that result from the DSL input. The gen migration command will generate field mappings based on the DB_PROVIDER which you have set either as an env var or passed in as the --provider. To see the definition of all these mappings, set DB_PROVIDER and the meg gen migration --help command will then update with the corresponding resulting schema mappings.

Example command with break down: meg gen migration AddPostsTable posts Id:Guid Name:String UserId:Guid:References:Users:Id

meg gen migration - command base AddPostsTable - migration file name posts - table name Id:Guid Name:String UserId:Guid:References:Users:Id - schema definition

⚠️ WARNING: If you are using Guid field with PostgreSQL, make sure you've installed the required extension to support generating uuids using CREATE EXTENSION IF NOT EXISTS "uuid-ossp";.

$USAGE: meg gen migration [--help] [--provider <postgresql|mssql|mysql|sqlite>]
                         [--migration-directory <migration directory>]
                         [<string>...]

SCHEMA DEFINITION:

    <string>...           Specify the schema definition of the migration in
                          this format:
                          -----------
                          meg gen migration AddUsersTable users Id:Id
                          Name:String Description:Text
                          -----------
                          List of all schema types for PostgreSQL (see your
                          provider by setting DB_PROVIDER) :
                          Id              an integer autoincrementing primary
                          key. Maps to INTEGER.
                          Guid            Sane default (uuid PK) for dotnet
                          guids. Maps to UUID.
                          Integer         Maps to INTEGER.
                          Float           Maps to FLOAT.
                          Numeric         Maps to NUMERIC.
                          Boolean         Maps to BOOLEAN.
                          String          Maps to VARCHAR(255).
                          Text            Maps to TEXT.
                          Binary          Maps to BYTEA.
                          Array           Maps to JSONB.
                          Record          Maps to JSONB.
                          Date            Maps to DATE.
                          Time            Maps to TIME.
                          DateTime        Timestamp without TZ. Maps to
                          TIMESTAMP.
                          DateTimeTz      Timestamp with TZ. Maps to TIMESTAMP.
                          -- References --
                          You can include references using
                          [ColumnName]:[ColumnType]:References:[ReferenceTableNa
                          me]:[ReferenceColumnName]
                          For example
                          -----------
                          user_id:int:references:users:id
                          -----------
                          -- Additional Properties --
                          You can include additional properties to a non
                          reference field using a similar syntax
                          [ColumnName]:[ColumnType]:[ADDITIONAL PROPERTIES]
                          For example
                          -----------
                          "username:string:unique not null"
                          -----------


OPTIONS:

    --provider, -p <postgresql|mssql|mysql|sqlite>
                          Specify the database provider.
    --migration-directory, -o <migration directory>
                          The output directory to write migrations to. When not
                          specified, the resulting SQL is written to env var
                          DB_MIGRATION_DIRECTORY value, defaults to 'Migrations'
    --help                display this list of options.