@speles7172/sql-console

A React SQL console — query editor, results grid, schema browser, sorting, pagination and CSV/JSON export — that you point at your own API.

npm install @speles7172/sql-console

What it looks like

┌ SQL console ─────────────────────────────────────────────────────────────┐
│                                                                          │
│ ┌ Saved ───────┐  ┌ Customers ─┐┌ Revenue ×┐┌ + ┐                        │
│ │ CUSTOMERS    │  ├────────────┴┴──────────┴┴───┴───────────────────────┐ │
│ │  Paying cus… │  │ Query ─── Ctrl+Enter  [Main ▾]  Save        [ Run ] │ │
│ │  Recent sig… │  ├─────────────────────────────────────────────────────┤ │
│ │ REVENUE      │  │ SELECT id, email, created_at                        │ │
│ │  Refunded o… │  │ FROM users                                          │ │
│ ├ Tables ──────┤  │ WHERE active = true                                 │ │
│ │ ▾ users      │  └─────────────────────────────────────────────────────┘ │
│ │     id  int  │                                                          │
│ │     email    │  ┌ Results (1,284 rows)     Export CSV   Export JSON ──┐ │
│ │ ▸ orders     │  │ id ▲    │ email              │ created_at           │ │
│ │ ▸ invoices   │  ├─────────┼────────────────────┼──────────────────────┤ │
│ │              │  │ 1       │ [email protected]    │ 2026-01-04           │ │
│ │              │  │ 2       │ [email protected]  │ 2026-01-05           │ │
│ │              │  │ 3       │ NULL               │ 2026-01-06           │ │
│ │              │  ├─────────┴────────────────────┴──────────────────────┤ │
│ └──────────────┘  │ ◀ Prev   Page 1 of 13  [100 / page ▾]      Next ▶   │ │
│                   └─────────────────────────────────────────────────────┘ │
└──────────────────────────────────────────────────────────────────────────┘

Tabs — each holds its own statement, results and page, and they come back after a reload. Saved queries — one click opens and runs one, in a new tab. One-click run — same for a table in the sidebar.

Click a column header to sort — it rewrites the ORDER BY in your SQL and re-runs, so you are sorting the whole result set rather than the page you can see. Click a table in the sidebar to drop a SELECT into the editor.

Open the live demo → The real component against an in-memory dataset — sort, page, browse the schema, trigger an error. Build it yourself from demo/.

Quickstart

The console never talks to a database directly — a browser cannot open a socket to PostgreSQL. You give it a transport: one function that sends the query to your API.

import { SqlConsole } from '@speles7172/sql-console';
import '@speles7172/sql-console/styles.css';

const transport = {
  async execute({ sql, page, pageSize }) {
    const response = await fetch('/api/sql', {
      method: 'POST',
      headers: { 'Content-Type': 'application/json' },
      body: JSON.stringify({ sql, page, pageSize }),
    });
    if (!response.ok) throw new Error(await response.text());
    return response.json();
  },
};

export function AdminSqlPage() {
  return <SqlConsole transport={transport} />;
}

That is the whole integration. Everything below is optional.

The other half: your endpoint

Back the endpoint with @speles7172/sql-client and the shapes line up with no translation — the console consumes exactly what the client returns.

import express from 'express';
import { createSqlClient, registerDatabase } from '@speles7172/sql-client';

registerDatabase({ name: 'main', engine: 'postgres', version: '17', secretArn: '…' });
const db = createSqlClient();

app.post('/api/sql', requireAdmin, async (req, res) => {
  try {
    // explore(), not query(): a human typed this, so it runs inside a
    // rolled-back READ ONLY transaction unless a write is both requested
    // by the console and permitted here.
    const result = await db.explore('main', req.body.sql, {
      page: req.body.page,
      pageSize: req.body.pageSize,
      writeMode: req.body.writeMode === true && callerMayWrite(req),
    });
    res.json(result);
  } catch (error) {
    res.status(400).send(error.message);
  }
});

requireAdmin is doing real work there. This component runs in a browser, so anyone who can reach your endpoint can run any statement your database user can. Authorisation belongs on the endpoint — the console cannot enforce it, and hiding the page does not.

Adding the optional features

Each one appears only when the transport can serve it, so a minimal transport gets a minimal console.

A database picker

const transport = {
  execute,
  async listDatabases() {
    return [
      { name: 'main', label: 'Production', engine: 'postgres' },
      { name: 'lake', label: 'Analytics',  engine: 'athena'   },
    ];
  },
};

The picker appears once there is more than one. The selected name is sent as database with every query, and the engine tells the console how to quote identifiers when sorting — backticks for Cypher, double quotes for SQL.

The schema sidebar

async listTables(database) {
  return [{ name: 'users', columns: [{ name: 'id', type: 'integer' }] }];
}

Server-side, that is one query:

SELECT table_name, column_name, data_type
FROM information_schema.columns
WHERE table_schema = 'public'
ORDER BY table_name, ordinal_position

Write mode

Off entirely unless you opt in, so a console is read-only by default:

<SqlConsole transport={transport} allowWriteMode />

That adds a Write mode switch. With it on, Run stops for an explicit confirmation showing the exact statement, and only then sends writeMode: true to your endpoint. Paging does not re-confirm — that is a read of a result that already exists.

The console enforces nothing. It runs in a browser, and anyone can call your endpoint directly with writeMode: true. Treat the flag as a request and decide on the server:

const result = await db.explore('main', req.body.sql, {
  // The console asked; the server decides.
  writeMode: req.body.writeMode === true && callerMayWrite(req),
  maxRows: 1000,
});

sql-client's explore() runs anything without writeMode inside a rolled-back READ ONLY transaction, so an un-permitted write fails at the engine rather than relying on the UI having asked nicely.

CSV import

Implement importRows and an Import CSV… button appears above the editor:

async importRows(request) {
  const response = await fetch('/api/sql/import', {
    method: 'POST',
    headers: { 'Content-Type': 'application/json' },
    body: JSON.stringify(request),
  });
  if (!response.ok) throw new Error(await response.text());
  return response.json();
}

Server-side that is one call — sql-client compiles the mappings into a parameterized statement per row:

app.post('/api/sql/import', requireAdmin, async (req, res) => {
  res.json(await db.importRows('main', req.body));
});

The wizard walks file → table → mapping → options → result. Each target column takes a CSV column, a SQL transform, or both: {value} is that column's cell and {Header} any other, so {First} || ' ' || {Last} maps two source columns into one. A guided if/else builder writes the CASE for anyone who would rather not.

It reuses the schema the sidebar already loaded, so listTables is what feeds the table picker.

To let mappings be reused across files, pass a store — same shape as saved queries:

import { localStorageMappingStore } from '@speles7172/sql-console';

<SqlConsole transport={transport} importMappings={localStorageMappingStore()} />

A loaded mapping is reconciled against the current file before it is applied: a column that no longer exists is dropped, and a CSV column or {Header} this file lacks is cleared for you to re-pick, rather than failing on every row.

Saved queries

Pass a store and the library appears in the sidebar, grouped by category, with a Save button in the editor:

import { SqlConsole, localStorageQueryStore } from '@speles7172/sql-console';

<SqlConsole transport={transport} savedQueries={localStorageQueryStore()} />

localStorageQueryStore keeps them in the one browser, which is the honest default. For a library the whole team shares, implement the same three methods against your API — each may be sync or async:

const teamLibrary: SavedQueryStore = {
  list:   () => api('/api/saved-queries'),
  save:   (input) => api('/api/saved-queries', { method: 'POST', body: JSON.stringify(input) }),
  remove: (id) => api(`/api/saved-queries/${id}`, { method: 'DELETE' }),
};

Clicking one opens it in a new tab and runs it, so a saved query never discards what you were already looking at.

Tabs, and what persists

Tabs are on by default and remembered in localStorage, so half-written statements survive closing the browser. Results are deliberately not restored — rows fetched before a reload, presented as current, are worse than an empty grid.

<SqlConsole transport={transport} sessionStore={null} />        {/* memory only */}
<SqlConsole transport={transport} sessionStore={myStore} />     {/* your own */}

Exporting everything, not just the page

Without this, Export writes the rows currently on screen. With it, the console asks your API for the whole result set:

async exportAll({ sql, database }) {
  const response = await fetch('/api/sql/export', { /* … */ });
  return response.json(); // Record<string, unknown>[]
}

Styling

The components render plain semantic HTML with sqlc- class names and no styling dependency. styles.css is optional; skip it and write your own.

Re-theme without touching a rule by overriding the custom properties. The theme rules below add no specificity of their own, so your .sqlc override wins as long as your stylesheet loads after this one:

.sqlc {
  --sqlc-accent: #7c3aed;
  --sqlc-radius: 4px;
  --sqlc-mono: "JetBrains Mono", monospace;
}

Light and dark

The console is light by default. It is embedded in your page, so your page decides how it looks — not the reader's operating system.

<SqlConsole transport={transport} theme="dark" />  {/* always dark */}
<SqlConsole transport={transport} theme="auto" />  {/* prefers-color-scheme */}

Reach for auto only if the surrounding app follows the OS setting too; otherwise you get a dark console sitting in a light page.

The default stylesheet also stops the grid from scrolling the page sideways and honours prefers-reduced-motion.

Building your own console

useSqlConsole is the whole behaviour with no markup — state, pagination arithmetic, the ORDER BY rewriting, export. Use it when your design system needs different markup:

import { useSqlConsole } from '@speles7172/sql-console';

function MyConsole({ transport }) {
  const sql = useSqlConsole({ transport, pageSize: 50 });

  return (
    <MyLayout>
      <MyEditor value={sql.sql} onChange={sql.setSql} onRun={() => sql.execute()} />
      {sql.error && <MyAlert>{sql.error}</MyAlert>}
      {sql.selectResult && <MyGrid rows={sql.selectResult.rows} onSort={sql.sortByColumn} />}
    </MyLayout>
  );
}

SqlEditor, SchemaSidebar and ResultsPanel are exported individually too, so you can keep two of the three and replace the other.

Keyboard

| Key | Does | |---|---| | Ctrl+Enter / Cmd+Enter | Run the query | | Double-click a tab | Rename it | | Enter / Escape in the save form | Save / cancel | | Tab | Move between the sidebar, editor and controls |

Column headers and table names are real buttons, so the whole console is reachable without a mouse, and the sorted column reports aria-sort to a screen reader.

Behaviour worth knowing

Sorting rewrites your SQL. Clicking a header replaces the top-level ORDER BY and re-runs from page 1. Sorting the page in the browser would sort one page and quietly lie about the order of everything else.

An ORDER BY inside a subquery or CTE is left alone — it means something different there, and rewriting it would change what the query returns rather than the order it returns it in. LIMIT and OFFSET stay at the end.

NULL is rendered distinctly from the string "NULL" and from an empty cell. In a query console those are three different things.

CSV exports defuse spreadsheet formulas. Excel, Sheets and LibreOffice execute any cell beginning =, +, - or @, so a row containing =HYPERLINK(…) runs when someone opens the file. Those values are prefixed with an apostrophe, which displays the original text and evaluates nothing. Numbers are exempt — -5 is not a formula, and prefixing every negative would corrupt any numeric export. Column names are neutralised too, since a crafted alias is the same vector.

A slow query cannot overwrite a fast one — in its own tab or any other. Results are matched to the run that asked for them, so a slow query finishing in a background tab never lands in the one you are looking at.

Paging re-runs the statement that produced the result, not whatever is in the editor now. Edit the SQL after running, click Next, and you still page the query you actually ran — the alternative silently pages a different query while the grid still claims to show the first.

Errors are shown, not swallowed. The message and any detail the database gave are displayed as an alert, and the previous grid is cleared rather than left looking current.

API

| | | |---|---| | <SqlConsole transport … /> | the whole console | | <SqlEditor state /> · <SchemaSidebar state /> · <ResultsPanel state /> | individual panels | | useSqlConsole({ transport, initialSql, initialDatabase, pageSize }) | headless behaviour | | applyOrderBy(sql, column, direction, engine) | the sort rewriter, standalone | | toCsv(rows, columns) · toJson(rows) | exporters, RFC 4180 quoting |

SqlConsoleTransport

| Method | Required | Gives you | |---|---|---| | execute({ database, sql, page, pageSize }) | yes | running queries | | listDatabases() | no | the database picker | | listTables(database) | no | the schema sidebar | | exportAll({ database, sql }) | no | export beyond the visible page |