Colored Output from SQLite3

I recently had to check some data that had been reprocessed. By “check” I mean verify that the new totals match the old totals. The query itself was simple, but with lots of output (shown below) it was making my eyes go square:

Specifically I was looking for values that differed (indicated by the diff_*columns). Yes, I could have just added an extra condition to the query that would only show the difference but that’s much less fun.

Before we continue, this is the not the default way SQLite3 outputs data. You can get a much more readable output (similar to psql) by setting some options in the sqlitesession with dot commands:

.mode column
.headers on

Now that we have that out of the way, here is a much more pretty version:

If your next question is “Huh, I didn’t know SQLite3 supported colors?” then you’re right. It does not. However, bashdoes and by using ASCII escape codeswe can show values in color.

This is the starting point (prints Hello World!):

SELECT printf(“%c[3%dm%s%c[0m”, char(27), 2, ‘Hello World!’, char(27));

Technically this works, but it’s not very friendly to use. Also, you have to remember that 2 means green. So let’s take it one step further.

A color table allows us to reference a color by name (rather than its integer) and also provides us with a cleaner template for printf:

CREATE TABLE colors (color TEXT, n INT, fmt TEXT);
INSERT INTO colors (color, n) VALUES
  ('black', 0), ('red', 1), ('green', 2), ('yellow', 3),
  ('blue', 4), ('magenta', 5), ('cyan', 6), ('white', 7);
UPDATE colors SET fmt = printf("%c[3%dm%%s%c[0m", char(27), n, char(27));

Now we can do this:

SELECT printf(fmt, ‘Hello in Red!’) FROM colors WHERE color = ‘red’;

We have got rid of most of the nastiness. This is the simplest interface I could come up with.

How does it work in a real example, you say? Here you go:

CREATE TABLE accounts (
  name TEXT,
  total FLOAT
);
INSERT INTO accounts VALUES
  ("Bob", 150.3), ("Jane", -50), ("John", -210.1);

Format the output with negative numbers in red and positive numbers in green:

SELECT
  name,
  (SELECT printf(fmt, total)
   FROM colors
   WHERE color = CASE WHEN total < 0 THEN 'red' ELSE 'green' END) AS total
FROM accounts;

This time I am not using the column output. Explained below.

Caveats

When SQLite3 renders the output it uses the character length, but not the visible character length. This causes the headings to not match up (as seen in the original image). It seems that SQLite3 truncates these values unusually in some cases so that the color runs onto the next line, or even chops off prefix characters on the next line.

Anyway, have a play with it an tell me how you go. :)

Originally published at http://elliot.land on October 12, 2017.