Today I'll write about a tool I live coded on my twitch stream the first weeks of April As part of my F# journey
Please note that this tool does not require you to use .net at all it's a standalone tool that you can integrate to any workflow you may have but... if you are a .netcore user you can install this as a global tool as well
dotnet tool install --global Migrondi
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
…Migrondi is a simple SQL migrations tool with a very few commands
- init
- new
- up
- down
- list
To use it just download a binary from the releases page or... build the sources
after that just put it on your path and you can start working with it
For the rest of the post I will assume that the Migrondi binary is in the system's path and it's named "migrondi.exe" or "migrondi" in linux
Migrondi isn't tied to any project at all you can have your migrations entirely outside of your project or if you want to keep your SQL migrations as part of your git history you can do them on your repository as well.
Init
The init command is perhaps the first that you will run if you don't have anything in place yet, it will create a "migrations" directory and a "migrondi.json" wherever you had invoked the migrondi command
I created a sample directory under my user
PS C:\Users\scyth\sample> migrondi
Migrondi
Copyright (C) 2020 Angel D. Munoz
ERROR(S):
No verb selected.
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.
Running the init command creates a file and a directory
PS C:\Users\scyth\sample> migrondi init
Created C:\Users\scyth\sample\migrondi.json and C:\Users\scyth\sample\migrations\
PS C:\Users\scyth\sample>
the contents of the "migrondi.json" file are very simple
{
"connection": "Data Source=migrondi.db",
"migrationsDir": "C:\\Users\\scyth\\sample\\migrations\\",
"driver": "sqlite"
}
if you are just trying or curious you can leave the JSON file as is, it will work with SQLite database inside the same directory as the config file, if you want to try with other databases you can switch the connection and the driver, you can check here for more information about the config file
About the "migrationsDir" it can be a relative path as well, just remember where you are invoking migrondi from, in the README you will see it's listed as
"migrationsDir": "./migrations/"it's fine as well it just assumes that you are invoking migrondi above the migrations directory
New
Migrondi has just one objective and that is running migrations, I didn't want to build an abstraction over SQL because I don't think that's a simple approach at all and requires you to have .NET specific tooling which is not what I'm looking for.
If you choose MySQL as your SQL dialect, then your migrations have to be written MySQL. If you are using PostgreSQL the same thing applies.
the new command takes a parameter -n or --name which is the name of the migration that you will be adding to your database
PS C:\Users\scyth\sample> migrondi new -n InitialMigration
that will create a new SQL file with the name you provided and a Unix timestamp
the contents are up to you, either create a new table, insert values, alter columns, you name it.
In this example let's create a simple todo's table
-- ---------- MIGRONDI:UP:1586888312019 --------------
-- Write your Up migrations here
CREATE TABLE Todos(
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT
);
-- ---------- MIGRONDI:DOWN:1586888312019 --------------
-- Write how to revert the migration here
DROP TABLE Todos;
there are some important things here, please Do not remove the "MIGRONDI" annotations these are used to identify which part of the script run when the up/down command is invoked, you can delete the "-- Write your....." comments though
Up
The up command reads every file inside the migrations directory and runs it against the database, the history of these migrations is preserved in a table called "migration" and it's used to keep track of what to run when the up/down command is invoked
v0.6.0
We added a --dry-run true option and you can now check on your terminal what's going to be run against your database before doing it
After running the "up" command, our SQLite database is created. Let's check the contents
By the way, I'm using this VSCode plugin for the database access https://marketplace.visualstudio.com/items?itemName=mtxr.sqltools
The database was created and the migrations table was updated
Down
Let's say for some reason you need to revert those changes then you just need to run the down command
v0.6.0
We added a --dry-run true option and you can now check on your terminal what's going to be run against your database before doing it.
after running down I tried to execute the same select all query, but since the migration has been run down my down statement says DROP TABLE Todos; so it took down the table If I check my "migrations" table it shows empty meaning we went back to the beginning.
The Up and Down commands have the parameter "-t" "--total" which allows you to specify the number of migrations to go up or to go down if you don't specify a number it will run all migrations up and all migrations down
List
The list command gives you the migrations that are present in the database or that are pending. Let's say you had an error in your migrations and the up command worked with some migrations, you can easily detect which migration is in the database
that way you don't need to go down on all migrations and see if the next time it works or not, just fix the last one that failed and keep going.
Insights and closing thoughts
Migrondi was written in F# and the binary is a self-contained dotnet core app, you don't need to have anything installed besides that binary that's why it's a big binary.
Every migration is run inside a transaction so if anything inside the migration script fails the change is not committed to the database.
If you can give it a try and let me know if it's useful I'd be glad. If you like it or have any doubts ping me below in the comments or on Twitter :)
I hope you are having excellent week cheers :)
Top comments (0)