Sourcing bash script variables from SQL

In this post, I will go through building a snippet for pulling variables into bash scripts dynamically using MariaDB.

Context

In a previous role of mine, changing even a single variable inside a bash script meant going through a full deployment process. Git branch, merge request, approvals, and then more approvals in the ticketing system. This made it difficult to quickly react to production needs in situations where process related delays often meant prolonged production incidents.

Changing a row in a database? It is easy to track and audit if needed, and also just as easy to rollback any change. If someone doesn’t want to rely on a database audit plugin, it’s always possible to create a change history table, and fill it with a database trigger. In addition to all that, updating or inserting rows in the database was covered by a change model, therefore also checking the figurative process checkbox.

The problem was that scripts had no unified and sane way of reading data from the database, and each script going its own way when you have hundreds of them is less than desirable.

The basic premise

-> It is assumed that this is part of a bigger script system, meaning each script has a name, a type (usually a good idea not to just dump every script in one big scripts folder), and the script is aware (has a variable set) of its own name and type.
-> The script sourcing this snippet should be able to immediately make use of the variables after sourcing this snippet, with no further adjustments
-> The script sourcing this snippet should only load variables that are relevant to it, not just blindly pull in an entire table

The table

Before diving into the snippet itself, let’s decide on a reasonable database table:

CREATE TABLE `script_variables` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `type` varchar(255) DEFAULT NULL,
  `script_name` varchar(255) DEFAULT NULL,
  `variable_name` varchar(255) DEFAULT NULL,
  `variable_value` varchar(255) DEFAULT NULL,
  `time_stamp` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UC_script_variable` (`script_name`,`variable_name`)
) ENGINE=InnoDB

The unique key ensures that we don’t try to accidentally insert multiple entries of the same variable for any script. Doing so would cause unpredictable behaviour in scripts.

Having a script type is optional, but it can be useful if one has a vast enough script library where such a classification makes sense.

A time_stamp is also not necessarily useful, but occasionally knowing when a variable was last updated can be useful for debugging.

The Snippet

#!/bin/bash

MARIADB="mysql -u root -A "

MARIADB_IFS=$(echo $MARIADB | sed 's/\s/\n/g')

OLDIFS=$IFS
IFS=$'\n'

VARS=(
variable_name
variable_value
)

#here we assume that the script sourcing this one is aware of a variable like this
SCRIPT_NAME_SANITIZED=$(echo ${SCRIPT_NAME} | sed 's/-//g')

IFS=$OLDIFS
#check if this is relevant now, skip if table has no entries for this script
VARCHECK=$(echo "USE test; SELECT EXISTS(SELECT 1/0 FROM script_variables WHERE script_name='$SCRIPT_NAME') AS varcheck" | $MARIADB_IFS)
IFS=$'\n'

if [[ $(echo $VARCHECK | awk '{print $2}') -eq 1 ]]; then
    while read -r "${VARS[@]}"; do
        for VARLINE in "${VARS[@]}"; do
            varname=$(printf '%s' ${!VARLINE} | awk '{print $1}')
            varvalue=$(printf '%s' ${!VARLINE} | awk '{print $2}')
            if [ ! -z "$varvalue" -a "$varvalue" != "variable_value" ]; then
                declare "s_${SCRIPT_NAME_SANITIZED}_${varname}"="$(echo $varvalue)"
            fi
        done
    done < <(
       echo "USE test; SELECT variable_name,variable_value FROM script_variables WHERE script_name='$SCRIPT_NAME'" | $MARIADB_IFS
    )
fi

IFS=$OLDIFS

A few minor points to note

-> MARIADB="mysql ... -A" in the case of a larger script system, this would likely live in a configuration file somewhere, and would be source-d, rather than hardcoding it like this.
-> VARS=( variable_name variable_value ) sets a list of expected database column headers from the query. This is not strictly necessary, but it acts as a simple check in case the database returns something malformed.
-> $SCRIPT_NAME_SANITIZED is important if script names contain dashes (-). Bash variables can not contain dashes, so these characters need to be eliminated. If your script names contain only [0-9a-zA-Z_]+ you can skip this.
-> SELECT EXISTS(...) AS varcheck this is a quick existence check, so that we can avoid unnecessary queries or empty loops
-> If the above check passes, the snippet queries for the variable_name and variable_value columns. For each result, it declares a new bash variable like:

s_scriptname_variablename1="value1"
s_scriptname_variablename2="value2"

These variables are immediately available for use in the rest of the script that sourced this snippet.

Tips for implementation

It’s also possible to loop through all the variables at once. This can be useful if it’s necessary to perform the same task on a bunch of different servers. For example, if we’re storing a server name in each variable for some hypothetical script, we can gather and loop over them like this:

for NAME in "${!s_script_name@}"; do
    SERVER_LIST+=(${!NAME})
done

SERVER_LEN=${#SERVER_LIST[*]}

for (( x=0; x<SERVER_LEN; x++ ));
    #do something with each server here...
done

This makes use of bash parameter expansion ${!prefix@} to grab all matching variable names. It’s a powerful trick when you don’t know the variable names ahead of time but want to process them generically. You can adjust this further, by having your variable names start with a specific prefix, eg. srv, ip, etc. Then you can adjust the parameter expansion from "${!s_script_name@}" to "${!s_script_name_srv@}" etc. allowing you to filter them more precisely:

for NAME in "${!s_myscript_srv@}"; do
    # Only loop over variables related to server names
done

for NAME in "${!s_myscript_ip@}"; do
    # Only loop over variables related to ip addresses
done

Conclusion

If you’re absolutely stuck with bash scripts, and can not move to a scripting language with proper built-in database handling, this can be a blueprint of an idea for managing variables/configurations in a slightly less chaotic way.

It can also double as a neat trick in situations where running through a deployment gauntlet becomes too tedious for the sake of changing a single variable.