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

user:	qidentity
created:	November 20, 2024
karma:	1