SQLitePilot — VS Code Extension

A full SQLite/.db editor for VS Code: browse, edit, and query databases without leaving the editor.

Features

• Multi-database management — connect to and switch between any number of .sqlite/.db files, all listed in a dedicated sidebar (activity bar icon).
• Database explorer — expand a database to see its tables, row counts, and each table's columns with type/PK/NOT NULL info.
• Full CRUD
  • View table data (double-click a table in the sidebar).
  • Edit a cell in place (double-click a cell in table-browse mode — requires a primary key).
  • Delete a row (✕ button per row).
  • Insert a row (+ Add Row button, prompts for each column).
  • Create a table (right-click a database → "Create Table...", pre-fills a CREATE TABLE template you edit and run).
  • Drop a table (right-click a table → "Drop Table").
• Raw SQL editor — a resizable panel to write and run any SQL. Run executes the whole editor; Run Selected executes only the highlighted text; Ctrl/Cmd+Enter also runs.
• Pagination on everything — both table browsing and arbitrary SELECT queries are paginated (adjustable page size: 50/100/500/1000) via LIMIT/OFFSET, so multi-million-row tables stay responsive. Only the current page is ever loaded into the webview.
• Query metadata — execution time and row/changed-row counts shown after every run.
• Export — export the full result set of the current query/table to JSON, CSV, XLSX, HTML, or SQL (SQL export includes INSERT statements, optionally with the original CREATE TABLE statement for table exports).
• Query history — every query is logged (with timestamp, duration, and result) per database; browse the History tab and click any entry to re-run it.

How it works

Uses sql.js (SQLite compiled to WebAssembly) rather than a native Node addon, so there's nothing to compile — it installs and runs identically on Windows/macOS/Linux. Each open database is loaded into memory and changes are auto-saved back to the .db/.sqlite file on disk shortly after each write.

Project layout

Tree Provider Overview

The treeProvider.ts file is responsible for managing the hierarchical representation of databases, tables, and columns within the SQLitePilot extension. It leverages Visual Studio Code's Tree Data Provider API to create an interactive sidebar that allows users to navigate through their databases seamlessly.

Key Classes

• DbTreeItem: This class represents a single item in the tree structure. It can represent a database, table, or column, and it includes properties such as:

  • kind: Indicates whether the item is a database, table, or column.
  • label: The display name of the item.
  • filePath: The path to the database file.
  • tableName: The name of the table (if applicable).
  • description: Additional information, such as row counts for tables.

• SqliteTreeProvider: This class implements the TreeDataProvider interface, managing the tree's data. Key functionalities include:

  • refresh(): Updates the tree view when changes occur in the database.
  • getTreeItem(): Returns the tree item representation for a given element.
  • getChildren(): Retrieves child items (tables or columns) based on the selected database or table.

How the Tree Structure Works

The tree structure is built dynamically based on the user's database connections. When a database is selected, its tables are displayed, and selecting a table reveals its columns. The tree updates automatically when changes are made to the database, ensuring that users always see the latest information.

This structured approach allows users to easily browse and manage their SQLite databases directly within VS Code.

sqlite-editor/
├── package.json          # extension manifest (commands, views, menus)
├── tsconfig.json
├── src/
│   ├── extension.ts       # activation, command registration
│   ├── databaseManager.ts # sql.js connections, CRUD, pagination
│   ├── treeProvider.ts     # sidebar tree (databases → tables → columns)
│   ├── queryPanel.ts       # webview: SQL editor, results grid, history
│   ├── exportUtils.ts      # JSON/CSV/XLSX/HTML/SQL exporters
│   └── historyManager.ts   # persisted per-database query history
└── media/db-icon.svg

Running it (development)

1. Open this folder in VS Code.
2. npm install
3. npm run compile (or npm run watch while developing)
4. Press F5 — this launches an "Extension Development Host" window with the extension loaded.
5. In that window, click the database icon in the activity bar → Open Database File... (or Create New Database...) to get started.

Packaging a .vsix you can install anywhere

npm install -g @vscode/vsce   # if you don't already have it
npm install
npm run compile
vsce package

This produces sqlite-pilot-0.1.0.vsix. Install it in any VS Code (or VS Code–compatible IDE, e.g. Cursor, VSCodium) via:

• Extensions panel → ... menu → Install from VSIX..., or
• code --install-extension sqlite-pilot-0.1.0.vsix

Notes / current limitations

• Cell editing, row delete, and "Add Row" require the table to have a primary key column — for tables without one, use the SQL editor directly (UPDATE/DELETE/INSERT always work there regardless).
• CREATE TABLE is a guided template rather than a full visual column builder — it opens the query editor with a starter CREATE TABLE statement you edit and run, which keeps the UI simple while still supporting arbitrary column definitions, constraints, and foreign keys.
• Multi-statement scripts in the SQL editor run via Run/Run Selected; results shown reflect the final statement.