Whenever possible I like to have single commands in my bash history that perform one complete task. I rely heavily on fuzzy searching my history and if a given task is spread out over a few different commands, or if the task requires entering into some kind REPL, I’m much less likely to get value from my bash history.

Recently I spent some time figuring out how to perform a join query with sqlite when my tables are stored in separate db files without entering into the sqlite REPL. Here’s how to do it:

sqlite3 "" -cmd 'attach "system-a-hosts.db" as systema' -cmd 'attach "system-b-hosts.db" as systemb' "select * from systema.tablea as tablea left join systemb.tableb as tableb on tablea.name = tableb.host;"

2024-08 Update

I recently updated to sqlite3 version 3.26.0, and started seeing this error attempting the above command: “Error: database alerts is already in use”

Adjusting the command to the following seems to achieve the same result - note the use of the special in-memory only database file “:memory:”:

sqlite3 -cmd 'attach "system-a-hosts.db" as systema;' -cmd 'attach "system-b-hosts.db" as systemb;' "select * from systema.tablea as tablea left join systemb.tableb as tableb on tablea.name = tableb.host;" :memory: ".exit"