ORM Studio

SQL Console

Run read-only SQL against SQLite, PostgreSQL or MySQL from Axil Studio, with highlighting, a schema tree, history, saved snippets and CSV/JSON export.

5 min readUpdated DocumentationEdit this page
On this page9

The SQL console (#/sql, shortcut g q) runs read-only SQL against the database Studio is connected to and shows the result in a sortable grid. A Read-only notice and the driver badge (sqlite, postgres or mysql) sit in the editor toolbar.

Editor

  • Syntax highlighting for keywords, function calls, strings, numbers, operators and comments, with line numbers.
  • Tab inserts two spaces.
  • Your draft is saved in the browser as you type and restored next time. The first time, the editor starts with SELECT * FROM <first table> LIMIT 50;.
  • Save stores the query as a snippet; Clear empties the editor.

Running queries

HowRuns
Run buttonThe selected text, or the whole editor when nothing is selected
Ctrl Enter / Cmd Enter in the editorSame as Run
Query in SQL on a table in the Database browserSELECT * FROM <table> LIMIT 100;, immediately
A query in Recent queries on the OverviewThat query, immediately
Opening #/sql?runThe current editor text

Selecting one statement and pressing Ctrl Enter is the easiest way to keep several queries in the editor and run them one at a time.

Results

The result header shows the row count, the run time and, when reported, the number of affected rows. With rows present you also get:

  • CSV and JSON buttons, downloading query-result.csv / query-result.json.
  • A copy button that copies all rows as JSON.

The grid uses the same cell rendering as the Database browser (NULL pills, booleans, JSON, dates). Click a column header to sort the result in the browser (ascending, descending, off). Click a row to open it in the details drawer, or double-click a cell to copy it.

If the query fails, the panel shows Query failed with the database error. If Studio refused it, it shows Query rejected and a reminder of which statements are allowed.

Side panel

The left panel has three tabs; the one you used last is remembered.

Schema

Every table with its row count. Expand a table to see its columns, marked PK / FK, with their types.

  • The insert button next to a table inserts its name at the cursor.
  • Clicking a column inserts its name at the cursor.

Names that aren't plain identifiers are inserted double-quoted.

History

The last 50 queries you ran, newest first (running the same SQL again moves it to the top). Each entry shows whether it succeeded, the row count and duration, and how long ago it ran. Buttons load it into the editor or run it again; Clear empties the history.

Saved

Save current query (or Save in the editor toolbar) asks for a name, defaulting to the query's first line, and stores the snippet in the browser (up to 100). Each snippet can be loaded, run or deleted.

Note

Drafts, history and snippets live in this browser's localStorage. They are not written to your project and are not shared between browsers.

Read-only rules

Queries are sent to POST /api/studio/query. The server enforces the rules, not the browser:

  1. Statement type: after trimming, the SQL must start with SELECT, WITH, SHOW, DESCRIBE, EXPLAIN or PRAGMA (any case). Anything else returns Only SELECT queries are allowed in Studio.
  2. No file writes: SQL containing OUTFILE or DUMPFILE anywhere (MySQL SELECT ... INTO OUTFILE / DUMPFILE, even split by comments) is rejected with INTO OUTFILE / DUMPFILE is not allowed in Studio.
  3. Read-only execution per database:
DatabaseHow the query runs
SQLiteOn a connection opened with readonly: true
PostgreSQLInside BEGIN TRANSACTION READ ONLY, using the extended protocol so only a single statement runs, then ROLLBACK
MySQLInside START TRANSACTION READ ONLY, then ROLLBACK

On PostgreSQL this step also stops what the prefix check can't, such as a data-modifying CTE (WITH x AS (DELETE ...) SELECT ...) or a stacked SELECT 1; DROP TABLE users.

Warning

These guarantees apply to the connection Studio opens itself (the axil run setup). If you attach your own driver with studio.setDriver(), Studio uses that driver's queryReadOnly method when it has one and falls back to its plain query method otherwise, so only the statement-type and OUTFILE checks apply. See Configuration.

Example queries

SQL
SQL
1-- Orders per status
2SELECT status, COUNT(*) AS orders, SUM(total) AS revenue
3FROM orders
4GROUP BY status
5ORDER BY orders DESC;
SQL
SQL
1-- Join with a CTE
2WITH recent AS (
3  SELECT * FROM orders ORDER BY created_at DESC LIMIT 20
4)
5SELECT r.id, u.email, r.total
6FROM recent r
7JOIN users u ON u.id = r.user_id;
SQL
SQL
-- SQLite: inspect a table
PRAGMA table_info(users);

Was this page helpful?

Help improve the documentation

AxilJS is open source and documentation improvements are welcome.

AxilJS DocumentationMIT License · Built by SyntaxilitY