Part 1

LiteCLI has an optional feature to use LLM powered SQL generation to get answers from your database.

The default LLM used by LiteCLI is OpenAI’s gpt-4o-mini. This can be changed to a different model including a local LLM running on Ollama.

Here are the steps to show how to switch your LLM model.

  1. Run \llm to enable the feature.

    sqlite> \llm
    

    This will offer to enable this feature by installing the necessary libraries. If you have already done this then it’ll print the “usage” documentation.

  2. Run \llm models to see the list of available models:

    sqlite> \llm models
    OpenAI Chat: gpt-4o (aliases: 4o)
    OpenAI Chat: gpt-4o-mini (aliases: 4o-mini)
    OpenAI Chat: gpt-4 (aliases: 4, gpt4)
    ....
    ....
    OpenAI Chat: o1
    OpenAI Chat: o1-2024-12-17
    OpenAI Chat: o1-preview
    OpenAI Chat: o1-mini
    Default: gpt-4o-mini
    
  3. The llm library has plugins that can enable access to more models. You can install additional plugins from right inside LiteCLI.

    sqlite> \llm install llm-gemini
    sqlite> \llm models
    OpenAI Chat: gpt-4o-mini (aliases: 4o-mini)
    OpenAI Chat: o1-mini
    ...
    ...
    GeminiPro: gemini-pro
    GeminiPro: gemini-1.5-pro-latest
    ...
    ...
    GeminiPro: gemini-2.0-flash-thinking-exp-01-21
    Default: gpt-4o-mini
    
  4. To use a local model first install ollama and launch it. This is a background process that serves local models that you can access with the data leaving your computer. Install a local model that you can run locally using ollama command line tool.

    Outside LiteCLI:

    $ ollama pull qwen2.5-coder
    

    Inside LiteCLI:

    sqlite> \llm install llm-ollama
    sqlite> \llm models
    OpenAI Chat: gpt-4o-mini (aliases: 4o-mini)
    OpenAI Chat: o1-mini
    ....
    Ollama: deepseek-r1:latest (aliases: deepseek-r1)
    Default: gpt-4o-mini
    
  5. Switch the default to your desired model:

    sqlite> \llm models default qwen2.5-coder
    
  6. Ask your questions and enjoy the benefits.

    sqlite> \llm "Customer with highest sales in the last month"
    sqlite> SELECT customer
            FROM sales
            WHERE datetime(timestamp / 1000000, 'unixepoch') >= datetime('now', '-1 month')
            ORDER BY amount DESC LIMIT 1;
    
  7. If you want to see the context in addition to the SQL query you can use \llm+ command.

     sqlite> \llm+ "Top 5 urls visited."
     To determine the "Top 5 URLs visited," the following tables are utilized:
    
     **`urls`**: This table contains the URL information along with the
       `visit_count`, which tracks how many times each URL has been visited.
       Using this table allows for an efficient retrieval of the most
       visited URLs without the need for complex aggregations.
    
     **SQL Query:**
    
     SELECT url, visit_count
     FROM urls
     ORDER BY visit_count DESC
     LIMIT 5;
    
     sqlite> SELECT url, visit_count
             FROM urls
             ORDER BY visit_count DESC
             LIMIT 5;
    

If you run into issues feel free to file a Github issue.