angelmunoz/migrondi

A Super simple SQL Migrations Tool for SQLite, PostgreSQL, MySQL and SQL Server

Currently Working on the next version to also add better support for the VSCode Extension!

Migrondi

Migrondi is a SQL Migrations tool designed to be simple and execute simple migrations. Write SQL and execute SQL against your database.

No need to install it, use it from VSCode! https://github.com/AngelMunoz/migrondi-vscode

Migrondi Runs on Linux-x64, Linux-arm64, Windows-x64, and MacOS-x64 (intel based)

Install

For Non .NET users

Grab the binary from the releases page or build from source and put it on your PATH, that way the command is available globally e.g.

# you can put this at the end of your ~/.bashrc
# $HOME/Apps/migrondi is a directory where you have downloaded your "Migrondi" binary
export MIGRONDI_HOME="$HOME/Apps/migrondi"
export PATH="$PATH:$MIGRONDI_HOME"

For .NET users

you can now install this as a global/local tool as well

dotnet tool install --global Migrondi

Usage

Init

If you are starting from scratch you can run the init command to create the migrondi files and directories needed for the rest of the commands to work properly

~/Migrondi $ ./Migrondi init
Created /home/x/Migrondi/migrondi.json and /home/x/Migrondi/migrations
~/Migrondi $

PS C:\Users\x\Migrondi> ./Migrondi.exe init
Created C:\Users\x\Migrondi\migrondi.json and C:\Users\x\Migrondi\migrations\
PS C:\Users\x\Migrondi>

Then you can adapt the configuration as needed

Config File

to use this tool you need to supply a JSON configuration file (the name must be migrondi.json)

{
  "connection": "Data Source=Migrondi.db",
  "migrationsDir": "./migrations/",
  "driver": "sqlite"
}

• connection

  This is a IDbConnection compatible connection string (you can find examples in the follwing links)

  • SqlServer
  • SQLite
  • MySQL
  • PostgreSQL

• migrationsDir

  this is an absolute or relative path to where the migrations will be stored Note: please include the trailing slash to prevent writing on the directory above of the one you pointed to. (if you use the init command, this is created for you)

• driver

  any of the following "mssql" "sqlite" "mysql" "postgres"

Migrondi 0.5.0
Copyright (C) 2020 Angel D. Munoz

  init       Creates basic files and directories to start using migrondi.

  new        Creates a new Migration file.

  up         Runs the migrations against the database.

  down       Rolls back migrations from the database.

  list       List the amount of migrations in the database.

  help       Display more information on a specific command.

  version    Display version information.

New

To create a new migration file run Migrondi.exe new -n CreateTodosTable where CreateTodosTable is the name of your migration, you can replace that name with your migration name it will create a new file with a name like this: SampleMigration_1586550686936.sql with the following contents

-- ---------- MIGRONDI:UP:1586550686936 --------------
-- Write your Up migrations here

-- ---------- MIGRONDI:DOWN:1586550686936 --------------
-- Write how to revert the migration here

Please do not remove the MIGRONDI:UP:TIMESTAMP and MIGRONDI:DOWN:TIMESTAMP comments these are used to differentiate what to run when you run the up or down commands.

Up

To run your migrations against your database use the "up" command Migrondi.exe up you can use -t <number> to specify how many migrations you want to run

Down

To rollback your migrations from your database use the "down" command Migrondi.exe down you can use -t <number> to specify how many migrations you want to roll back

List

If you want to list migrations you can use the command "list" Migrondi.exe list with the following flag combinations

• Migrondi.exe list --last true
• Migrondi.exe list --all true --missing true
• Migrondi.exe list --all true --missing false

these will give you these outputs

• Last migration in the database
• All migrations that are missing
• All migrations present in the database

Build

Use the build.fsx (dotnet fsi build.fsx) script or clone and run

dotnet publish -c Release -r <RID> --self-contained true -p:PublishSingleFile=true -o dist

replace RID and the angle brackets with any of the following

• Windows RIDs
• Linux RIDs
• MacOS RIDs

this should give you a binary file in the dist directory, after that put it wherever you want and add it to your path and you can start using it

migrondi's Issues

Wrong directory when using Migrondi for the first time in OSX.

{
  "connection": "Data Source=migrondi.db",
  "migrationsDir": "/",
  "driver": "sqlite"
}

Migrondi set the directory to root. Think it should rather be './'?

Running the migrondi command immediately kills the migrondi process

Describe the bug

Trying to invoke the migrondi command immediately invokes a pid killed error.

To Reproduce

Steps to reproduce the behavior:

app git:(main) ✗ dotnet tool install --global Migrondi
You can invoke the tool using the following command: migrondi
Tool 'migrondi' (version '0.7.0') was successfully installed.
➜  app git:(main) ✗ migrondi
[1]    10871 killed     migrondi

Expected behavior
A clear and concise description of what you expected to happen.

Screenshots

Desktop (please complete the following information):

• OS: macOS Big Sur

Add Colored Output

We can replace all of the println with AnsiConsole.MarkupLine from https://github.com/spectresystems/spectre.console
as well as all of the eprintln with it as well

Also as a bonus, we can add a Progress to the up and down commands if we want to go fancy with it

Dry Run

Implement a --dry-run option for up and down commands

Address code inconsistenciescistencies

Right now even after a big re-architecture we still have some places were we have stuff is miss-placed like get-separator and even checking if the driver is correct or not we'll need to revisit these places to ensure everything is going well

Migrondi: command not found

Hi,
I installed Migrondi using the command dotnet tool install --global Migrondi on Windows and Ubuntu 18.04. However, on Ubuntu it show the message: command not found. If I try to install again I receive the message: Tool 'migrondi' is already installed.. What could be?

dotnet SDKs:

• 3.1.407
• 5.0.201

Failing to apply migrations doesn't fail a build

Hello! 👋🏻

First, I'd like to thank you for this amazing project! We're a small team writing fsharp apps and we're using migrondi to manage db migrations with postgres and it's been extremely helpful!

We just found something and I thought I'd share it with you. Here's what's happening:

We have migrondi run as part of our release pipeline. So every time we release, new migrations are applied automatically. This makes life a breeze.
But I had a typo on my latest migration script and, even though I could see the error in the logs, my release didn't fail. We build on azure devops.

What I'm thinking is that migrondi is probably not returning an exit code other than 0 when failing to apply migrations.
Ideally, it'd be best if a failure to apply migrations failed my release pipeline so we could fail fast.

Does this make sense to you? I'd be happy to send a PR with an attempt at implementing this.

Let me know if you need any more information on this.

Thank you!

Migration File Status

Add a new command

• status --filename string

it should report a simple boolean or a simple string that says if it is in the database or is pending

MariaDB is not working

it might be a bad assumption of mine, but I thought mariadb should be working as well as mysql, mysql doesn't provide an aarm64 image in docker, so I can't really test that right now but we can ensure at least mariadb works propperly I guess it's a similar situation as mysql

OS: Ubuntu Server aarm64

Distributor ID: Ubuntu
Description:    Ubuntu 21.04
Release:        21.04
Codename:       hirsute

Migrondi version: Migrondi 0.6.0

Connection String Secret

How should I manage a connection string with a secret?

Docker guidance

This is probably out of scope, but do you have any guidance for running Mirondi automatically when starting a docker container?

I'd like to have a more unified way of initializing the SqlHydra databases with data. Not only for initializing the adventureworks db, but some providers need custom tables added for provider specific features. For example, postgres needs to have a schema added to test enum support. It would be cool to have a standard scripts folder that would just work for each provider.

New options to support migrondi-vscode

There are a couple of new flags I'd like to introduce to Migrondi in lieu of migrondi-vscode

• no color

  I think this one has to be complemented with a more streamlined output not just showing success, error kind of panels but it should also help in cases of CI/CD where colors are simply not important

• json output

  This is a necessary approach that will allow external tools to consume the contents of the stdout/stderr

having these tools can help the extension to provide a better input to show information in a more ergonomic format.

Validate new migration names

Hello! It's me again! 😁

I just noticed something I think we can improve, and I wanted to discuss with you:

When creating a new migration with migrondi new -n MyMigration, migrondi will create the migration file with any name you set. For example, migrondi new -n My_Migration is created properly, but migrondi up will fail because My_Migration is not a valid name.

I see we already have the validation functions, so I was thinking it'd be a nice to have if we could validate on new as well.

What do you think?

Roadmap

Where do you desire this project to go?

How can I help?

I'm not much of a .NET developer, but I can assist any other way that's needed.
Just let me know.

Running the migrondi up command second time output error message and nothing else happens

Describe the bug

Trying to run migrondi up or migrondi down after the first migrondi up gives always an error message "42P07: relation "migration" already exists" as an output and nothing else happens.

To Reproduce

Steps to reproduce the behavior:

1. Run command migrondi up
2. Run command migrondi up or migrondi down

Expected behavior

Both commands (migrondi up and migrondi down) should work without any errors related to 'migrations' table existence still after the first run.

Screenshots

Desktop (please complete the following information):

• OS: Windows, Linux (Manjaro)
• Database: PostgreSQL
• Version: 0.7.0

Additional context

I assume that problem exists inside of the Queries.createMigrationsTable function which is used as a implementation for Queries.ensureMigrationsTable functionality. There seems to be handling for situations when table already exists for SQLite databases, but how about other database types?

[CI]: Run Integration Tests in GH Actions

We might be able to do some basic integration tests, or rather just run stuff and expect it to work (no idea how to perform an actual integration test for this)
Given:
https://stackoverflow.com/a/57549440/12970202

it seems we can use a docker container in GH Actions so we might be able to setup a particular database to run some basic migrations against the current database

Experiment: Allow F# Scripts

Given this snippet

#r "nuget: FSharp.Compiler.Service"

open System
open System.IO
open FSharp.Compiler.Interactive.Shell

let defConfig = FsiEvaluationSession.GetDefaultConfiguration()


let argv = [|"--noninteractive";"--nologo";"--gui"|]

let eval = 
    new StringReader(
        """println "Hello World!"
        """)


let session = FsiEvaluationSession.Create(defConfig, argv, eval, Console.Out, Console.Error, true)

let scriptcontent = 
    """
    #r "nuget: Mondocks"
    open Mondocks.Queries

    let Up = 
        let seedDatabase = 
            insert "users" { documents [{| name = "Peter"; age = 10 |}] }
        [ seedDatabase
          seedDatabase
          seedDatabase
          seedDatabase
          seedDatabase
        ]
    let Down = 
        let deleteUsersCmd = 
            delete "users" { deletes [box {| q = {||} |}] }
        [ deleteUsersCmd
          deleteUsersCmd
          deleteUsersCmd
          deleteUsersCmd
        ]
    [Up;Down]
    """

let interaction = session.EvalInteractionNonThrowing scriptcontent
match interaction with 
| Choice1Of2 value, error ->
    match value with 
    | Some value ->
        match value.ReflectionValue with 
        | :? list<list<string>> as migrations ->
            for migration in migrations.Head do
                printfn "Executing migrations up :%s" migration

            for migration in migrations.Tail.Head do
                printfn "Executing migrations down :%s" migration
        | _ -> printfn $"Unsuported type %A{value.ReflectionType}"
    | None -> 
        eprintfn "%A" error
| Choice2Of2 ex, error ->
   eprintfn "%A" error
   eprintfn "%O" ex

that produces this output

❯ dotnet fsi dotnetfsi.fsx

> [Loading C:\Users\daniel\AppData\Local\Temp\nuget\3380--1b128567-d627-4977-a014-ad4518de13d6\Project.fsproj.fsx]
namespace FSI_0001.Project

Binding session to 'C:\Users\daniel\.nuget\packages\mondocks\0.3.15\lib\net5.0\Mondocks.dll'...
Binding session to 'C:\Users\daniel\.nuget\packages\fsharp.systemtextjson\0.15.14\lib\netcoreapp3.0\FSharp.SystemTextJson.dll'...
Binding session to 'C:\Users\daniel\.nuget\packages\mongodb.bson\2.11.4\lib\netstandard2.0\MongoDB.Bson.dll'...
val Up : string list =
  ["{"insert":"users","documents":[{"age":{"$numberInt":"10"},"na"+[14 chars];
   "{"insert":"users","documents":[{"age":{"$numberInt":"10"},"na"+[14 chars];
   "{"insert":"users","documents":[{"age":{"$numberInt":"10"},"na"+[14 chars];
   "{"insert":"users","documents":[{"age":{"$numberInt":"10"},"na"+[14 chars];
   "{"insert":"users","documents":[{"age":{"$numberInt":"10"},"na"+[14 chars]]
val Down : string list =
  ["{"delete":"users","deletes":[{"q":{}}]}";
   "{"delete":"users","deletes":[{"q":{}}]}";
   "{"delete":"users","deletes":[{"q":{}}]}";
   "{"delete":"users","deletes":[{"q":{}}]}"]
val it : string list list =
  [["{"insert":"users","documents":[{"age":{"$numberInt":"10"},"na"+[14 chars];
    "{"insert":"users","documents":[{"age":{"$numberInt":"10"},"na"+[14 chars];
    "{"insert":"users","documents":[{"age":{"$numberInt":"10"},"na"+[14 chars];
    "{"insert":"users","documents":[{"age":{"$numberInt":"10"},"na"+[14 chars];
    "{"insert":"users","documents":[{"age":{"$numberInt":"10"},"na"+[14 chars]];
   ["{"delete":"users","deletes":[{"q":{}}]}";
    "{"delete":"users","deletes":[{"q":{}}]}";
    "{"delete":"users","deletes":[{"q":{}}]}";
    "{"delete":"users","deletes":[{"q":{}}]}"]]

Executing migrations up :{"insert":"users","documents":[{"age":{"$numberInt":"10"},"name":"Peter"}]}
Executing migrations up :{"insert":"users","documents":[{"age":{"$numberInt":"10"},"name":"Peter"}]}
Executing migrations up :{"insert":"users","documents":[{"age":{"$numberInt":"10"},"name":"Peter"}]}
Executing migrations up :{"insert":"users","documents":[{"age":{"$numberInt":"10"},"name":"Peter"}]}
Executing migrations up :{"insert":"users","documents":[{"age":{"$numberInt":"10"},"name":"Peter"}]}
Executing migrations down :{"delete":"users","deletes":[{"q":{}}]}
Executing migrations down :{"delete":"users","deletes":[{"q":{}}]}
Executing migrations down :{"delete":"users","deletes":[{"q":{}}]}
Executing migrations down :{"delete":"users","deletes":[{"q":{}}]}

I'm thinking about exploring the possibility to allow the users to bring their own specialized tools to generate SQL and then add a new type of migration called fsx or fscripted (not that important right now) that would emit files like these

(*Add your dependencies here e.g.
  #r "nuget: SqlBuildeLibrary3000"
  open  SqlBuildeLibrary3000 
  ... and so on*)

/// <summary>
///    SQL queries that will be run when upgrading the database (up command)
///   NOTE: This must be a list of strings
/// </summary>
let Up : string list = 
    [(* put your SQL here*)]

/// <summary>
///    SQL queries that will be run when downgrading the database (down command)
///   NOTE: This must be a list of strings
/// </summary>
let Down : string list = 
    [(* put your SQL here*)]

/// Return a List of  "List of strings"
/// the first list is the Up command
/// the second is the Down command
[Up; Down]

then we could just execute the up/down the corresponding list, the benefit here would be that you can use the full power of F# to generate SQL queries that will be executed in the same workflow we currently have

@VitorRigoni does this sound like something of value?

Accepted file pattern for the commands new and up differs

Hi Angel,

I tried your tool today and it worked fine.
I found just one little issue.

The tool accept this for the new command:

migrondi.exe new -n setup-model.sql

but the resulting file is invalid due to the hyphen.

setup-model.sql_1626383847780.sql

BR Roman

Adjust options for the List command

Right now for the list command we have a strict set of options you can use to know what migrations are in the database but these are mutually exclusive

• --all bool provides a simplistic way to show either all of the migrations or just the last applied/missing migration
• --missing bool is also a simplistic way to decide between showing the migrations existing in the database or the migrations that are pending to apply

these two options serve for basic needs, but it would be nice to have an option that gives us both applied and missing, combined with a json output from #20 we could allow pretty good reporting. what I have in mind is a simple behavior change between the flags

I propose the following

• --amount int defaults to -1 pulls everything
• --all bool defaults to false ignores missing flag when true
• --missing bool defaults to true

dotnet tool?

Have you considered publishing this as a global or local tool? That might make it a bit more accessible to people.

Expose Migrondi as a library

Is your feature request related to a problem? Please describe.
Right now if you want to use Migrondi, you need to use the binary distribution to execute migrations ideally if you are a .NET user, you should be able to use Migrondi as a library

Describe the solution you'd like
Expose Migrondi core parts as a library with handy types (Func/Action for C#) (F# lambdas for F# users)

Could not migrate stored procedure for Microsoft SQL Server

Hi there,

I am trying to migrate stored procedure for SQL Server with Migrondi.
But I received following error when run 'migrondi up'

Unexpected Exception 'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.

Here is the doc from MSDN (https://docs.microsoft.com/en-us/sql/t-sql/statements/create-procedure-transact-sql?view=sql-server-ver15)

One work around is to wrap whole procedure inside Execute like this

EXEC ('create procedure dbo.test
  as
  begin
    select * from some_table
  end')

Thank you very much,
Teerawat

Which migrations will up and down commands run?

I use Flyway right now. Whether part of Flyway or just the way we use it, each migration file starts with a number and only the highest number will be run. This is especially annoying in a large team scenario. If we both grab the same number the first to submit will win!

In migrondi, which migration file(s) will the up and down commands run? Any that haven’t been run previously? Where is that info stored? The newest one based on its file name? Does the caller specify?