Published on

Dynamic QuestDB-Shell Autocompletion

Authors
  • avatar
    Name
    Teddy Xinyuan Chen
    Twitter

In my last post I mentioned how I used carefully selected rlwrap flags to provide a user-friendly shell for QuestDB with history and autocompletions, in this post I'll share exactly how I achieved that.

Demo (1 min)

I use tab to trigger completion auggestions of table names and SQL keywords.

Half way through the demo, I accidentally made a query that returned 10 million rows (the equities_1d table), and I had to Control-Z to put the process in the background (since it wasn't responding to Control-C) and killed it with kill %1 😊.

https://www.youtube.com/watch?v=Z_36FI_DDDQ

Screenshots

2nd screenshot shows autocompletion of both table names and SQL keywords. The chhist_* tables stores my chrome history dump for further analysis 🙃.

Full screen: https://gg.teddysc.me/?g=e5bb610d28e63f5f81ec2c5f70dbda3b&c=2

rlwrap Options

  • -w so that it wait .2s before reprompts, so that the prompt doesn't mix with output of my queries.
  • -f, --file file: Split file into words and add them to the completion word list. This option can be given more than once, and adds to the default completion list in $RLWRAP_HOME or /usr/share/rlwrap/completions.
    • tables is a valid QuestDB query, and is equivalent to SELECT * FROM tables and returns all of my tables in the DB. Without extra output format controlling options, it returns JSON, and jq parses the output to get the table names.
    • questdb-keywords.txt is generated with the TypeScript script at the bottom of this page.
  • -pGreen to get the nostalgic green prompt :)
  • questdb-cli exec --psql --query sends user input to QuestDB's /exec endpoint, --psql formats output to a psql-style ASCII table from raw JSON.

questdb-shell

#!/usr/bin/env bash

# Check if questdb-cli is available
if ! command -v questdb-cli &>/dev/null; then
    echo "Error: questdb-cli is not installed or not in PATH"
    echo "Please install questdb-cli first"
    exit 1
fi

QUESTDB_TABLE_NAME_LIST_FILE=$(mktemp) # Create a temporary file and store its path in a variable
# get list of table names for completion
questdb-cli exec -q 'tables' | jq '.dataset[][1]' -r >"$QUESTDB_TABLE_NAME_LIST_FILE" # Redirect the output of your command to the tempfile

# Use patient mode (-w -40) to prevent the prompt from potentially
# flashing or appearing before the previous command's output is fully settled.
# It tells rlwrap to wait 40ms after the last output before displaying
# the "cooked" prompt, holding it back entirely until then.

rlwrap \
    --case-insensitive \
    -f ~/your-questdb-cli-dir/questdb-keywords.txt \
    -f "${QUESTDB_TABLE_NAME_LIST_FILE}" \
    -H ~/your-questdb-cli-dir/rlwrap.history \
    -r \
    -R \
    -S 'questdb > ' \
    -pGreen \
    -w -200 \
    bash -c 'while read line; do questdb-cli exec --psql --query "${line}"; done' # -l ~/your-questdb-cli-dir/rlwrap.log \

Generating keyword list

// this generates questdb-keywords.txt
import { dataTypes, functions, keywords, constants } from "@questdb/sql-grammar";

// Function to safely print items from an array
const printItems = (items: unknown, categoryName: string): string[] => {
  if (Array.isArray(items) && items.every(item => typeof item === 'string')) {
    // console.log(`\n--- ${categoryName} ---`); // Optional: Add category headers
    for (const item of items) {
      console.log(item);
    }
    return items as string[]; // Type assertion is safe here due to the check
  } else {
    console.warn(`Warning: Could not load or validate ${categoryName} from @questdb/sql-grammar`);
    return [];
  }
};

Source Code