Database utility I want

dbutil.md

Let's imagine that you've got information for accessing several different databases you have to work with on a regular basis. Servers (e.g. mysql, pgsql, etc.), hostnames, ports, usernames, password files, database names, etc.

Let's also imagine that there are several programs you use on these databases regularly. If you use MySQL, this might include the mysql CLI client, the mysqldump utility, and liquibase for versioning.

Manually typing these commands together with this database information is tedious. Managing shorthand shell functions for the same purpose also seems suboptimal.

What I want is a wrapper utility of sorts to which I can specify the program I want to run and the name of (or potentially a shorthand alias for) the database I want to run it on, and have it pull all other information from a configuration file I create to derive and run the resulting CLI command.

Here's a hypothetical example of such a file:

{
  "databases": {
    "myalias": {
      "server": "mysql",
      "hostname": "db.myserver.com",
      "port": 3306,
      "username": "myusername",
      "defaultsFile": "/path/to/mysql-defaults-file",
      "database": "mydatabase",
      "liquibase": {
        "changeLogFile": "mychangelog.xml",
        "driver": "com.mysql.jdbc.Driver",
        "defaultsFile": "/path/to/liquibase-defaults-file"
      }
    }
  },
  "programs": {
    "liquibase": {
      "jar": "/path/to/liquibase.jar",
      "classpath": "/usr/share/java/mysql-connector-java.jar:/path/to/liquibase.jar:/path/to/changelogs",
      "logLevel": "info"
    }
  }
}

Note that I'm using default files for MySQL and Liquibase to protect my password.

Based on this configuration file, I could enter a command like this:

dbutil myalias liquibase update

And dbutil (patent pending - just kidding, this is a placeholder for a cooler name) would expand that to this and run it:

/usr/bin/java -jar /path/to/liquibase.jar \
  --driver=com.mysql.jdbc.Driver \
  --classpath="/usr/share/java/mysql-connector-java.jar:/path/to/liquibase.jar:/path/to/changelogs" \
  --changeLogFile=mychangelog.xml \
  --url="jdbc:mysql://db.myserver.com:3306/mydatabase" \
  --username="myusername" \
  --defaultsFile="/path/to/liquibase-defaults-file" \
  --logLevel=info \
  update

With the same configuration file, I could also issue this command:

dbutil myalias mysql

And dbutil would expand it to this and run it:

mysql \
  -hdb.myserver.com \
  -P3306 \
  -umyusername \
  --defaults-file=/path/to/mysql-defaults-file \
  mydatabase

Advantages to this:

• For programs that may not support default files as MySQL and Liquibase do, dbutil would provide that support
• dbutil would also provide a unified default file format, versus the INI format of MySQL or the properties format of Liquibase
• Rather than having to maintain a default file per connection, all information would be kept in one file, including cases where the program using the default file doesn't support some parameters (e.g. you can't specify a value for the mysql --database flag in more recent versions).

A more general purpose suggestion from @dcousineau, though I'm not sure how well-suited it is to this particular purpose since some programs within the use case described above are interactive (e.g. mysql):

http://www.fabfile.org/

Here's a Liquibase example.

MySQL Fabric exists, but appears to be geared toward full automation of server management versus just automating the process of accessing an individual server.

From @grahamc, this could work for invoking interactive processes from Python.