LiteCLI is a command-line client for SQLite databases that has auto-completion and syntax highlighting. I’ve added the ability to use an LLM to create a SQL query.
Disclaimer: This is an EXPERIMENTAL feature. Not yet released.
Ask a question about your database in English and get a SQL query back to find out the answer.
This is the SQLite db used by Chrome.
~/D/c/s/a/s/llm-litecli (litecli) ↪ litecli ~/History LiteCli: 1.13.3.dev5+g6485fa6.d20241216 (SQLite: 3.44.2) GitHub: https://github.com/dbcli/litecli /Users/amjith/History> \llm Top 10 most visited websites. [F3] Multiline: OFF Right-arrow to complete suggestion
Exploring my Chrome history to find interesting things about my browsing habits.
Adjust the SQL before executing it. Sometimes the SQL returned by the LLM is close but not quite right. No problem we can edit the query with all the litecli features before executing it.
download_date BETWEEN date('now', '-7 days') AND date('now') ORDER BY received_bytes DESC LIMIT 5; ``` ### Explanation of the Query: 1. **Selection**: The query selects `id`, `received_bytes`, and a calculated `download_date` (converted from `start_time`). 2. **Datetime Conversion**: The conversion uses SQLite's `datetime` function. The `start_time` is divided by `1000000` because it is in microseconds, and `11644473600` is subtracted to convert from Windows FILETIME to UNIX epoch. 3. **Filter**: The `WHERE` clause filters downloads to include only those from the last week, using the current date for reference. 4. **Ordering**: The query orders the results by `received_bytes` in descending order to get the largest files. 5. **Limiting the Results**: Finally, it limits the output to just the top 5 downloads by size. /Users/amjith/History> SELECT id, received_bytes, datetime(start_time / 1000000 - 11644473600, 'unixepoch', 'localtime') AS download_date FROM downloads WHERE download_date BETWEEN date('now', '-7 days') AND date('now') ORDER BY received_bytes DESC LIMIT 5; [F3] Multiline: OFF Right-arrow to complete suggestion
Edit the query before execution.
The explanation for the SQL query is provided to help understand the reasoning behind it. The explanation is useful for the LLMs themselves to produce higher quality answers.
### Explanation: - **`SELECT u.url, SUM(v.visit_duration) AS total_duration`**: We select the URL and calculate the total time spent on each site by summing up `visit_duration` for all visits to that URL. - **`FROM visits v`**: We start from the `visits` table to access visit records, which contain the individual `visit_duration`. - **`JOIN urls u ON v.url = u.id`**: This join allows us to match visit records with their corresponding URLs, giving us easy access to the site URLs. - **`GROUP BY u.url`**: This groups the results by each unique URL so that the total time spent on each site can be calculated. - **`ORDER BY total_duration DESC`**: This orders the results by the total time spent in descending order, ensuring that the site with the most time spent appears first. - **`LIMIT 1`**: This restricts the output to a single result, showing only the site where the most time was spent./Users/amjith/History> SELECT u.url, SUM(v.visit_duration) AS total_duration FROM visits v JOIN urls u ON v.url = u.id GROUP BY u.url ORDER BY total_duration DESC LIMIT 1; [F3] Multiline: OFF Right-arrow to complete suggestion
Implementation Details
This feature uses the llm python library to interface with an LLM.
The final prompt sent to the LLM includes the schema for the database and an example row from each table in the database. The prompt is designed to craft a SQL query to answer your question.
The demo above uses GPT-4o model. But the model can be swapped out for a local LLM running in Ollama.
I learned a few things from this experiment.
- Chrome History stores the timestamps as microseconds since 1601-01-01.
- Both GPT-4o and Claude 3.5 Haiku recognized the Chrome history DB and crafted my SQL queries to adjust for this special start timestamp.
- Local LLMs (especially qwen2.5-coder) are good enough and fast enough to produce high quality results.
Inspiration
This feature is heavily inspired by Simon’s sqlite-utils-ask plugin. Thanks to Simon for creating the llm library that made it trivial to add this feature.
This feature currently lives in a git branch: https://github.com/dbcli/litecli/tree/ai