SQL API Explorer for VS Code

A Visual Studio Code extension for exploring PostgreSQL databases via a custom SQL API. Ideal for database interaction in environments where direct database port access is restricted but an HTTP interface is available.

---

TUI version

Features

• Connection Management: Add, edit, and remove host configurations via a dedicated UI.
• Authentication Support: Built-in support for Basic Auth, Bearer Token, and Custom Headers.
• Database Browsing: Intuitive tree view for Schemas, Tables, and Columns.
• Table Metadata: View Column details, Indexes, Keys, and auto-generated DDL (CREATE TABLE, ALTER TABLE).
• Data Explorer (Webview):
• Pagination (Next/Previous pages)
• Filtering (WHERE clause input)
• Sorting (Clickable column headers)
• Row limits adjustment
• Detailed row view with expandable rows
• SQL Mode: Execute arbitrary SQL queries in a dedicated Webview editor.
• Export: Interactive HTML table export with built-in Rose Pine Moon and Rose Pine Dawn themes.
• Native Integration: Respects your editor's font family and font size settings for a seamless look.

Backend Requirements

To use this extension, your backend must implement the following API endpoints:

Endpoints

• POST /api/v1/dev/query-sql: For SELECT queries.
• POST /api/v1/dev/execute-sql: For INSERT, UPDATE, DELETE, and other DDL/DML queries.

Request Format

JSON

{
  "sql": "SELECT * FROM users"
}

Success Response Format

JSON

{
  "rows": 10,
  "data": [
    {
      "id": 1,
      "username": "admin",
      "email": "admin@example.com"
    },
    ...
  ]
}

Note: The explorer attempts to preserve column order based on the keys of the first object in the JSON data array. For consistent results, ensure the backend returns JSON keys in the desired display order.

Error Response Format

JSON

{
  "message": "Short error description",
  "error": "Detailed database error message"
}

Installation

1. Open VS Code.
2. Go to the Extensions view (Ctrl+Shift+X).
3. Search for SQL API Explorer (or install from .vsix).
4. Click Install.

Usage

Managing Hosts

All database activities are centered in the API DB activity bar item (Database icon):

• Add Host: Click the + button in the view title to open the configuration form.
• Edit/Remove: Use the pencil or trash icon next to a host name in the tree.

Exploring Data

1. Expand a host and schema in the tree view.
2. Click the table icon (Show Data) next to a table name to open the data explorer.
3. Click the info icon (Show Table Info) to view structure, indexes, and DDL.

Running Custom SQL

• Click the terminal icon (Run SQL Query) next to a host name.
• Enter your SQL and click Execute SQL.
• Results can be exported to HTML with a theme selection (Rose Pine Moon/Dawn).

Data Explorer Controls

• Click Column Header: Sort by that column (ASC/DESC).
• Click ⚉ in Row: Expand/collapse detailed record view.
• Pagination: Use "Next" and "Previous" buttons at the bottom to navigate records.