Commands for initializing and migrating the database

[sungo]

I'm looking at creating docker compose files to standing up conch and its dependencies. Typically, one uses the official postgres image and points the application to it. The implication here is that the official image doesn't have any of our sql files or scripts on it.

When standing up conch for the first time, the database needs to be initialized. Later, as the user upgrades the image, the database needs to be migrated. Since the API does not, for good reason, manage these tasks on its own, I need a way to perform these tasks out of band.

I'd like to have mojo commands, that use the regular conch/mojo config system, to initialize and migrate the database. Example syntax might be docker run --rm joyentbuildsops/conch-api bin/conch init-database and docker run --rm joyentbuildops/conch-api bin/conch migrate-database.

The conch docker image contains ubuntu's build-essential package so these commands could just be system commands but I leave that implementation detail up to the implementer.

[karenetheridge]

Is there anything the script should do for initialization, e.g. to confirm that docker is indeed running and it's pointing to the right image? Just running those two shell command with no checks seems a bit scary.

[sungo]

No. The code will not know that it is running inside docker. This is just like standing up and migrating databases in production and doesn't require any extra considerations.

[karenetheridge]

This should be really easy after the (planned and almost done, via validation refactoring) database renovations are in, as I've already done nearly the same thing in Test::Conch for initializing a fresh db for tests. The only caveat is that when getting $self->app in a command, the app is already initialized and we will pass through the "make sure the database has all its migrations up to date" check, which we'll need to bypass/defer with some sort of special hook, hopefully one not too hacky.

[karenetheridge]

How about these commands? bin/conch-db --initialize and bin/conch-db --migrate

[karenetheridge]

What about populating the db with basic data in order to run the application? Is that out of scope for this ticket? Normally we expect:

• a user with system_admin privileges that can create other users
• a workspace named 'GLOBAL'
• one or more validation_plan entries with validations
• probably more that I'm not thinking of presently

[sungo]

I don't know what basic data is required for conch to work initially. Accepting auth information for a first admin user makes sense. If GLOBAL is required for the API to operate out of the box, then yes create that too. If additional basics are required, then either the script needs to provide those or we need to examine why they're necessary for an empty instance.

The goal here is pretty simple. Given a postgres database and a conch docker image, I want to stand up a new instance of the conch API. When upgrading that instance later, I want to migrate the database so it keeps working.

The command syntax you suggested is fine.

[karenetheridge]

Given a postgres database

Can I presume to connect to postgres using the dsn/username/password in the config file (or command line overrides) and expect it to exist? Or do I need to do something to make that exist, first?

[karenetheridge]

We currently use hardcoded validation plans. I can just replicate those hardcoded plans for now and revisit this part again later when we make validations nicer.

[karenetheridge]

I don't know what basic data is required for conch to work initially

This depends on what customers we're considering. If we're only considering ourselves, where validation plans are locked in, then I've got everything I need.

[sungo]

Given that we have hardcoded values like the OTPs in the validation system, I think it's acceptable right now to focus on our use case.

[karenetheridge]

What I have so far:

conch-db [subcommand subcommand...] [-hnv] [long options...]

initialize:              initialize a new Conch database and its tables
create-validations:      create validation plans for the Conch application
create-global-workspace: create the GLOBAL workspace
create-admin-user:       create a user with admin privileges
migrate:                 run outstanding migrations on a Conch database (no effect with 'all')
all:                     alias for initialize create-valiations create-admin-user

The environment variables POSTGRES_DSN, POSTGRES_USER and POSTGRES_PASSWORD are used if set.
Otherwise, the config file will be used to find database credentials.

        -h --help       print usage message and exit
        -n --dry-run    use a test database instead of credentials you provide
        -v --verbose    print the queries that are executed

        --config STR    configuration file
                        (default value: conch.conf)
        --username STR  the new admin user's name
                        (default value: admin)
        --email STR     the new admin user's email address (required for
                        create-admin-user)
        --password STR  the new admin user's password (or one will be
                        randomly generated)

[karenetheridge]

I removed 'Mojo' from the title since implementing this as a mojo command is a royal pain. This is just a regular program that deals with the database.