Show HN: SQLTab – Chat with your SQLite database

Show HN: SQLTab – Chat with your SQLite database(sqltab.com)

1 points by attilakun 1 year ago | 2 comments

Demo: http://www.sqltab.com/static/average_earning.gif

Try it here: https://www.sqltab.com/demo

Main features:

- AI-powered SQL assistant. Allows (one-click) execution and undo/redo of SQL generated by the AI.

- GUI to rename, remove, and reorder columns in SQLite tables.

- Undo/redo support using SQLite's savepoints [1].

- JSON data expansion: https://www.sqltab.com/static/json_expand.gif

- The pivot table extension [2] is loaded and available for use. The AI is also aware of it.

Privacy & Data:

- Databases are stored locally in the browser using OPFS.

- Database schema is sent to OpenAI as part of the chat session's context window. If the chat session contains results of data querying, then that will also be sent to OpenAI with the next message.

Tech stack:

- WASM build of SQLite (using a custom fork of the official repo [3]).

- React + Mantine for the UI.

- esbuild to build the frontend (invoked from Go). So much faster than the alternatives.

- Hosted on Cloudflare Pages, deployed via wrangler CLI.

- Single API endpoint just to proxy OpenAI calls in order to hide my API key. Hosted on Cloudflare Pages Functions (very practical but only for simple things).

Technical notes:

- The entire application is a static website except for the OpenAI proxy. Planning to support user-supplied API keys to (optionally) get rid of this.

- OPFS storage comes with some limitations (e.g., no incognito mode on mobile, same DB can't be opened in multiple tabs).

- gpt-4o is used for the LLM. Might switch to gpt-4o-mini to save costs.

Why not DuckDB?

- I got some out-of-memory errors when expanding JSON data. SQLite worked fine on the same data.

- No OPFS support yet. PRs have been pending for a year now.

If you want to reset the demo, just delete the IMDB.sqlite database (disk icon in the top-left corner -> "Delete DB") and load the demo URL again in the browser.

Please let me know if you have ideas about how to make this more useful.

[1] https://www.sqlite.org/lang_savepoint.html

[2] https://github.com/jakethaw/pivot_vtab

[3] https://sqlite.org/wasm/doc/trunk/about.md

qidentity 1 year ago |

This is pretty interesting!

I tried to ask: "Recommend me great sci-fi movies from 2010 to 2024". It generated a SQL script for me to execute which unfortunately doesn't work.

attilakun 1 year ago | |

It's probably because it's trying to guess what the filter condition should be and uses the wrong casing for the genre (should be "Sci-Fi", not "sci-fi"). At least that's what happened when I tried your prompt a couple of times.

Sometimes it gets the casing correct by accident straight away, other times it struggles. When it does struggle, I just keep typing "fix" in the chat, and eventually it figures it out. Sometimes it asks me to run a query to determine the distinct set of genres, sometimes it just decides to lowercase the genre column in the filter condition.