
Show HN: Mgrt – Simple SQL Migrations - bkq
https://github.com/andrewpillar/mgrt
======
stephenr
Having recently written a shell-based migration tool due to my frustration at
trying to support the Python one we were using previously, this is an
interesting topic to me.

As with the python tool we had been using, this doesn't appear to support SSL
connections for mysql/postgresql?

I'm also dubious about the "format" used - the python tool we had been using,
and the new shell tool I wrote to replace it, both just read regular SQL files
in a series of directories (one called `up.sql` and one called `down.sql`).
This meant that migrating from one tool to another was _ridiculously_ easy,
and if trying to debug, an entire SQL file can just be read into `mysql`/etc.
Having the files separated (and without any special syntax required) also
makes creating them much simpler - anything that can write out SQL can do it -
even e.g. schema-change tools that record changes between two known states.

Anyway, good luck with it. Ultimately these tools usually come about to solve
some problem or scratch some itch, so I hope your itch is scratched!

~~~
bkq
>I'm also dubious about the "format" used - the python tool we had been using,
and the new shell tool I wrote to replace it, both just read regular SQL files
in a series of directories (one called `up.sql` and one called `down.sql`).

This is something I considered, whereby the revisions themselves would be
stored in `up`, and `down` directories beneath the revisions directory itself.
It would make things even more transparent I suppose when it comes to
integrating with other SQL tooling, instead of having to rely on some ad-hoc
DSL. However, I chose to use the special format because for me, I found it
simpler to work with, each revision is its own SQL file, and for the size of
the revisions I was writing this suits me fine.

>As with the python tool we had been using, this doesn't appear to support SSL
connections for mysql/postgresql?

Yes, unfortunately. I will need to look into testing this, as it was something
I overlooked. For my use case specifically I was simply using it against local
databases, and yes that does make for a weak excuse as to why SSL is not
supported.

>Anyway, good luck with it. Ultimately these tools usually come about to solve
some problem or scratch some itch, so I hope your itch is scratched!

Essentially, I wanted something simple that read in plain SQL and kept a log
of what had been performed. Though with what you said about using separate
`up`/`down` files for the respective directions would make things far simpler
than they currently are.

For this to work the revisions would need to be changed from standalone SQL
files, to actual directories themselves. Not that big of an issue, but perhaps
something I will look into. Ideally I want this tool to play nice with other
tools in regards to the data it produces.

~~~
stephenr
> I was simply using it against local databases, and yes that does make for a
> weak excuse as to why SSL is not supported.

It's one of the reasons I chose to just write my tool in shell and call
`mysql` directly, (ab)using the 'sectioned' nature of mysql .cnf files for
config, so that any possible mysql option can be set for the connection,
without needing to handle it in-app.

> For this to work the revisions would need to be changed from standalone SQL
> files, to actual directories themselves. Not that big of an issue, but
> perhaps something I will look into. Ideally I want this tool to play nice
> with other tools in regards to the data it produces.

That's essentially how mine, and the python one before it worked: a root
'migrations' directory somewhere, with named subdirectories representing each
migration, and then `up.sql` and `down.sql` in each one (my tool also supports
`pre` and `post` SQLs at either the migration level, or the 'global' level,
but that's a pretty niche option I'd suggest.

For reference my tool is called `mallard`:
[https://bitbucket.org/koalephant/mallard](https://bitbucket.org/koalephant/mallard)
and the Python tool that inspired it is called `dbschema`:
[https://github.com/gabfl/dbschema](https://github.com/gabfl/dbschema)

