Add features for more of a "batteries included" experience

[djrobstep]

I'm opinionated about the fact that migra should be a pure diff tool, and shouldn't impose a particular workflow on database operations.

Nevertheless, there are common pieces of code that could allow people to get set up and productive with migra without having to roll their own workflows from scratch.

This seems to be the main barrier to actually using migra for workflows in production.

These features should probably be contained in a separate module migra.util, migra.helpers, migra.tasks, something like that.

Ideally we'd gather some feedback about how to use migra from existing users. If you're a user, feel free to weigh in right here with what supporting code would be useful.

[saifulmuhajir]

Haven't used this yet, but looks promising for managing our environments.

What I don't see is the ability to select specific tables. Sometimes we only want to migrate several tables instead of the whole database. Maybe for application readiness or some other things. Thank you.

[djrobstep]

@saifulmuhajir you can already restrict output to a particular schema already with the --schema flag, but we could add an additional flag that would work similarly but allow you to specify one or more table/object names instead of a schema name.

[afonso360]

It would be nice to have a proxy feature for non public facing databases. My current issue is specifically with ssh. But other proxies would be nice.

[StephenCarboni]

I'm interested in using an sql script as a source. Sometimes you just want to compare the DDL in a script to a database or even another script.

Could this not be done behind the scenes as simply creating a temporary database to execute the script and then using that as the source?

[gwn]

Hello, new Migra user here. I became familiar with this whole "state driven database" concept with Migra, and liked it very much.

These days I'm having test runs with it in my new projects to see how far it can take me.

Here is my workflow:

• Require developers to setup a helper database along with the main database when installing the app for the first time
• After a schema change; invoke a shell command to pass the new schema to the helper database, create the diff, save it to a "migration.sql" file in the working directory, and empty the helper database after that.
• Optionally edit the migration script.
• Apply the migration script to the main database via a shell command.

For me these are the things that have to be automated. Other migration tools offer simple commands such as foo migrate up and foo migrate down; so the experience needs to be comparable in order for Migra to be able to compete with them.

Below is a small shell script I wrote to implement the mentioned automations:

#!/bin/sh

# Small migration helper script built on top of Migra

mgrscript="migration.sql"

main() {
    if test -z "$PGHOST" -o \
        -z "$PGUSER" -o \
        -z "$PGDATABASE" -o \
        -z "$PGDATABASE_HELPER"
    then
        echo '$PGHOST, $PGUSER, $PGDATABASE, $PGDATABASE_HELPER must be defined'
        exit 3
    fi

    cmd="$1"

    case "$cmd" in
        patch) patch ;;
        diff) diff ;;
        *) usage ;;
    esac
}

usage() {
    echo "Usage:"
    echo "  $0 diff <path/to/schema.sql  # Diff current db schema with given schema"
    echo "  $0 patch <path/to/migration.sql  # Apply given migration to current db"
}

patch() {
    psql "postgres://$PGUSER:$PGPASSWORD@$PGHOST:$PGPORT/$PGDATABASE" -1 \
    || exit 4
}

diff_cleanup() {
    echo -e "\nCleaning up:\n"

    echo "drop schema public cascade; create schema public" \
    | PGDATABASE="$PGDATABASE_HELPER" "$0" patch
}

diff() {
    trap diff_cleanup 1 2 3 6 9

    echo -e "Setting up the helper database with the new schema:\n"

    cat | PGDATABASE="$PGDATABASE_HELPER" "$0" patch

    echo "-- Please review and feel free to modify this auto-generated migration file." > "$mgrscript"
    echo -e "-- Don't worry, it won't be applied until you explicitly call the patch command.\n" >> "$mgrscript"

    migra --unsafe \
        "postgres://$PGUSER:$PGPASSWORD@$PGHOST:$PGPORT/$PGDATABASE" \
        "postgres://$PGUSER:$PGPASSWORD@$PGHOST:$PGPORT/$PGDATABASE_HELPER" \
    >> "./$mgrscript"

    test $? -eq 2 && diff_cleanup || exit 5

    echo -e "\nSuccessfully generated migration script: $mgrscript"
    echo "Please review & optionally modify it"
    echo "Apply it when you are ready using the patch command"

    test -n "$EDITOR" && "$EDITOR" "$mgrscript" </dev/tty
    # "</dev/tty" fixes vim's "input not from terminal" error
}

main "$@"

Hope the feedback helps. Thank you for Migra.