loading...
Cover image for Migrondi, Simple SQL migrations tool

Migrondi, Simple SQL migrations tool

tunaxor profile image Angel D. Munoz Updated on ・5 min read

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 --version 0.4.0

GitHub logo AngelMunoz / Migrondi

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

.NET Core

Migrondi

This is a pretty simple SQL Migrations tool that I've been live coding for the last week (Apr 6 - Apr 10) Migrondi provides a way to execute SQL migrations against a database

You don't need a dotnet project/toolchain etc to use this, if you are using node/java/python/ruby that's completely fine this project works directly with your database and you shouldn't even need to have .net installed at all. If you want a brief tutorial/explanation you can check this post in dev.to

Usage

Grab the binary from the releases page or build from source

.net users

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

dotnet tool install --global Migrondi --version 0.4.0

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

PS C:\Users\x\Migrondi> ./Migrondi.exe init
Created

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 0.4.0
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>

after init

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 very useful at all if you choose MySQL as your SQL dialect, then you migrations have to be written thinking in 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

Initial Migration

the content's as you can see are up to you
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 "migrations" and it's used to keep track what to run when the up/down command it's invoked

migrondi up
As you can see, after running the "up" command, our SQLite database is created let's check the contents

Check database exists

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
migrondi down
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
migrondi list
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.

So yeah! this was a cool program to write I have released it as pre-release since I haven't had a chance to write tests I have been testing manually. 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 on the comments or on twitter :)

I hope you are having excellent week cheers :)

Posted on by:

tunaxor profile

Angel D. Munoz

@tunaxor

I'm a Mexican dude, tacos and stuff. In a crush with F# |> ❤

Discussion

markdown guide