I Built a SQLite Editor in 180 Lines, Then Rebuilt It in 730 for the Browser

#devtools #python #productivity

Background

I work with a lot of SQLite databases — patent data, court rulings, government records, scraped datasets. I wanted a quick way to open a .db file, pick columns, filter, and explore. Nothing fancy.

I built it first with Streamlit. 180 lines, done in an hour. Used it daily. Then I wanted to share it and hit a wall.

Streamlit: 180 Lines, Done

Streamlit + pandas + sqlite3. st.data_editor handles virtual scrolling, sorting, inline editing, wide table support — all for free.

Key features:

Sidebar table list with row counts
Column selection (multiselect) + pin columns (sticky left)
Raw WHERE clause input as filter
Sort, LIMIT, CSV export
Cell editing with PK-based UPDATE generation
Query execution timing
Tables without primary keys are read-only

Core query builder:

[bash]
display = sel_cols or col_names
q = f'SELECT {", ".join(f\'"{c}"\' for c in display)} FROM "{S.tbl}"'
if where.strip():
q += f" WHERE {where.strip()}"
if order:
q += f' ORDER BY "{order}" {"DESC" if desc else "ASC"}'
q += f" LIMIT {limit}"
[/bash]

Display with st.data_editor, detect edits, issue UPDATE statements keyed on PK columns. 179 lines total.

The Catch

To publish a Streamlit app, users upload their .db files to your server. Fine for personal use, but for a public tool, you're receiving other people's databases. Not ideal.

So I rebuilt it with sql.js — SQLite compiled to WebAssembly. Everything runs in the browser. The file never leaves your machine.

WASM Version: Single HTML File, 730 Lines

The trade-off: I lost st.data_editor and had to build the UI myself. ~520 lines of vanilla JS, 124 lines of CSS, ~90 lines of HTML structure. 731 lines total in a single HTML file. Zero frameworks. The only external dependency is sql.js.

What it does:

Drag and drop .db files
Column picker + pin columns for wide tables
Inline cell editing with download of modified DB
Raw WHERE filter with debounced auto-execution
Sort, pagination (OFFSET/LIMIT), CSV export
Dark/light mode (persisted to localStorage)
PWA support (Service Worker + manifest.json) for full offline use
Mobile responsive with hamburger menu
Keyboard shortcuts (Ctrl+S save, Ctrl+E CSV, Ctrl+Enter re-run)

Column Pinning Implementation

The key feature for wide tables: pin ID or key columns to the left edge so they stay visible while scrolling horizontally.

[bash]
function applyPinning() {
if (!S.pinnedCols.length) return;
const ths = gridHead.querySelectorAll('th');
const displayCols = S.result.columns;
const pinIndices = S.pinnedCols.map(c => displayCols.indexOf(c)).filter(i => i >= 0);

let cumLeft = 0;
pinIndices.forEach((colIdx, i) => {
const th = ths[colIdx];
const w = th.offsetWidth;
th.style.position = 'sticky';
th.style.left = cumLeft + 'px';
th.style.zIndex = 3;

gridBody.querySelectorAll('tr').forEach(tr => {
  const td = tr.children[colIdx];
  td.style.position = 'sticky';
  td.style.left = cumLeft + 'px';
  td.style.zIndex = 1;
});
cumLeft += w;

});
}
[/bash]

Accumulate offsetWidth for each pinned column to calculate dynamic left positions. Headers get z-index: 3, body cells z-index: 1 to layer correctly. The last pinned column gets a box-shadow to mark the boundary.

Inline Editing and Save

Cells become editable via contentEditable. Changes are detected on blur. Tables without a primary key get no contentEditable — they're read-only by design.

[bash]
const editable = hasPk && !S.pkColumns.includes(columns[ci]);
if (editable) {
td.contentEditable = true;
td.dataset.ri = ri;
td.dataset.col = columns[ci];
td.dataset.orig = val === null ? '\x00NULL\x00' : String(val);
td.dataset.pk = JSON.stringify(pkIndices.map(i => row[i]));
}
[/bash]

On save, iterate pendingEdits and issue PK-based UPDATE statements. A coerce function applies type enforcement for numeric columns — prevents strings from being inserted into INTEGER columns.

Keyboard Shortcuts

Ctrl+S: Save edits
Ctrl+E: CSV export
Ctrl+Enter: Re-run query
Enter: Move to cell below
Tab/Shift+Tab: Move to adjacent cell
Escape: Cancel edit

Cell navigation with Tab/Enter/Escape gives a spreadsheet-like editing experience. Paste is restricted to plain text only to prevent HTML tag contamination.

Surprisingly, This Didn't Exist

I looked around and couldn't find a simple, single-file SQLite editor that does column pinning and inline editing in the browser. Most tools are either full IDEs (DB Browser for SQLite) or educational SQL playgrounds. Nothing in between for people who just want to quickly explore a database.