Are colours in the terminal simply unnecessary bling? No. They can be useful as a mental cue to remind ourselves of the environment we're working in - for example, green: development, yellow: staging, red: production.

And for those of us who spend long hours working in the terminal, perhaps colours might even make the experience a little bit more pleasant, who knows?

Below I will look at colouring options for the MariaDB command-line client - mariadb.

The prompt

Command-line option, options file or environment variable?

There are a couple of ways to configure the mariadb prompt. First of all, we can use the --prompt= command-line option which supports a number of codes for customising the prompt. (See the MariaDB documention for details.) This option can also be set in an option file, e.g. in your ~/.my.cnf file - just place a prompt= ... line inside the [mariadb-client] option group. (But sadly, option files can't be used to add colours to the prompt.)

Secondly, and similar to the Unix PS1 prompt statement environment variable, there is also a MYSQL_PS1 environment variable for the mariadb and mysql command-line clients. It supports exactly the same codes as does the command-line option.

An example

While the MariaDB documention says nothing about colours for the prompt, we can in fact use the same colour codes as for the Unix PS1 prompt, although we do have to add some trickery. Example:

export MYSQL_PS1=$'\001\033[1;31m\002PROD \\u@\\h \001\033[1;34m\002\\d >\001\033[00m\002 '

Note: Beware of MariaDB option files (system-wide or otherwise) containing prompt options as this will override your MYSQL_PS1 variable!

Example explained

From left to right:

$: The dollar sign character in front of the string tells the shell (bash in my case) to use ANSI-C quoting: Backslash-escaped characters are replaced as specified by the ANSI C standard. Without the leading $ we would have to double-backslash escape these characters.

\001 and \002: Octal values which are decoded to the corresponding 8-bit characters (Ctrl-A and Ctrl-B). They are needed because the mariadb client is using the EditLine wrapper (previously GNU readline). This wrapper expects these codes to embrace "non-printable characters", i.e. characters that don't take up any space on the screen, such as colours. This is the trickery I mentioned before, and is not needed for the Unix PS1 prompt.

\033[: Octal value + "[", this indicates to the mariadb client the start of control characters.

1: The colour-mode where 1 is for lighter than normal. Other options: 2 for darker than normal and 0 to reset/normal.

31m: Red foreground colour. The m terminates the list. Other options: 30: black. 32: green. 33: yellow. 34: blue. 35: purple. 36: cyan. 37: white. The same colours for background are available in the range 40-47. You can also set foreground and background colour at the same time: 41;37m

\\u, \\h and \\d: These codes tells the mariadb client to print the current user, host and database, respectively. They are double escaped to prevent the shell from expanding them before they're sent to mariadb.

Other shells

I've tested the above export command successfully with bash, zsh, ksh and the FreeBSD sh shell. In tcsh you can make it work with something like:

setenv MYSQL_PS1 \
`printf "\001\033[1;31m\002PROD \\u@\\h \001\033[1;34m\002\\d >\001\033[00m\002 "`

You can also do something similar in pwsh (PowerShell):

$env:MYSQL_PS1 =
$(printf "\001\033[1;31m\002PROD \\u@\\h \001\033[1;34m\002\\d >\001\033[00m\002 ")

... but that will probably only work in Unix-like OSes with a printf command. (That said, MYSQL_PS1 with ANSI colour control codes might only work in Unix-like OSes anyway.)

Alternative formats

All octal values can be substituted with corresponding hex or Unicode values, if your shell supports that:

Host-dependent colour

How can we automatically get a particular colour for each individual database host? We don't want to resort to doing anything manually, such as invoking a special script or option for each host, as that would be hard to remember and would interfere with our workflow.

Below is some example bash code for your ~/.bash_profile file(s). It assumes you log in through SSH on each host, and that you only ever log in through SSH on each host and run the mariadb client from there. (E.g. because you've disallowed remote connections to MariaDB.)

function mysql_ps1 {
  BLUE=$'\001\033[01;34m\002'
  RED=$'\001\033[01;31m\002'
  AMBER=$'\001\033[01;33m\002'
  GREEN=$'\001\033[01;32m\002'
  RESET=$'\001\033[00m\002'

  if [ ${1} = "dev" ]; then
     export MYSQL_PS1="${GREEN}dev ${BLUE}[\\d] >${RESET} "
  elif [ ${1} = "stage" ]; then
     export MYSQL_PS1="${AMBER}stage ${BLUE}[\\d] >${RESET} "
  elif [ ${1} = "prod" ]; then
     export MYSQL_PS1="${RED}prod ${BLUE}[\\d] >${RESET} "
  fi
}

if [ $(hostname -s) = "dev-hostname" ]; then
   mysql_ps1 dev
elif [ $(hostname -s) = "stage-hostname" ]; then
   mysql_ps1 stage
elif [ $(hostname -s) = "prod-hostname" ]; then
   mysql_ps1 prod
fi

The pager - colour your results

The pager is a program which takes the output from another program and displays it one page at a time. It knows how much text fits in a page because it reads the terminal's horizontal and vertical number of characters (rows and columns).

However, a pager clearly can do more than that - such as adding colours to the output.

The mariadb client can be configured to use a pager. By default it doesn't, but it can be set interactively using the pager command.

Command-line option, options file or environment variable?

While the pager can be set in an environment variable (PAGER), this doesn't actually enable it. (Run the interactive command pager to enable it.) That's not a great option for someone who wants to avoid manual work.

There is also a command-line option --pager=, but we don't want to specify that every time we start the client. (Although an alias might do the trick.)

The best option is therefore to use the option file. Place a line pager = ... in the [mariadb-client] option group in your ~/.my.cnf file.

Pager options

You can write your own pager, but below is an example in perl borrowed from this insightfult StackOverflow anwer. Place that in a file pcc and configure the mariadb client to use if as per the previous section.

#!/usr/bin/perl -n
print "\033[1m\033[35m$1\033[36m$2\033[32m$3\033[33m$4\033[m" while /([|+-]+)|([0-9]+)|([a-zA-Z_]+)|([^\w])/g;

The characters '|', '+', '-' are coloured purple (35), numbers are coloured cyan (36), letters are coloured green (32), while any other characters are coloured yellow (33).

Another option is to install the Generic Coloriser package: dnf install grc on Fedora or apt-get install grc on Ubuntu, pkg install grc on FreeBSD etc. Then use e.g. pager = grcat /usr/(local/)share/grc/conf.sql in ~/.my.cnf as described above. grcat is a little more advanced than our Perl one-liner above and is therefore at least in principle able to do a better job.

A third option is mypager which is a MySQL and PostgreSQL pager for Unix platforms written in Perl. Just git clone the repository and run make install. This installs the mypager script in your ~/bin/ directory, so you can then use pager = ~/bin/mypager in your ~/.my.cnf file. man mypager gives more information.

What about syntax highlighting?

Unfortunately, there doesn't appear to be a way within the mariadb command-line client to colourise the SQL you write. (Please do let me know if you know about solutions for this!) However, there are other command-line clients, such as mycli, which is a MySQL, MariaDB and Percona MySQL CLI with auto-completion and syntax highlighting written in Python.

See also

• ANSI-C quoting
• MariaDB documentation: --prompt option