Databases
The SQL editor is for the admin consoles, reporting screens and internal tools where people
write SQL against your application's database. It reads PostgreSQL, SQL Server, MySQL and MariaDB, SQLite,
Oracle and standard SQL the way each of those databases reads it. Give it your schema - tables and columns, or
simply the rows of a query on information_schema.columns - and it completes table and column
names, explains a name when you rest on it, and checks every statement as it is typed: a column that is not
there (with the nearest one that is), UPDATE or DELETE without WHERE, = NULL, columns neither
grouped nor aggregated, syntax from another database. Format lays a script out again and changes nothing it
means.
The editor never connects to a database itself. Your page gives it a run function that hands the
SQL to your own service, and the editor shows what comes back: a tab for each result set, the database's
messages, an error marked at its place in the text, and a history of every run, with your snippets beside it.
Ask for a plan and it reads PostgreSQL EXPLAIN, SQL Server showplan XML, MySQL EXPLAIN and SQLite EXPLAIN
QUERY PLAN alike, points to the hot spots and compares the plan with the one before.
Try it with your own SQL scripts and query plans in confidence: they are opened by your browser, on your device. Nothing is uploaded, no copy reaches us or anyone else, and nothing is kept once you close this tab.
A reporting console, checked against its schema
Copperbeck Cycle Hire, a made-up bike hire company with six stations around the Lakes, keeps its rides in
PostgreSQL. This is its Monday report as typed in a hurry, against an invented schema of four tables: a
misspelt column, a comparison with NULL that never matches, a SELECT * and an UPDATE that would
retire every bike. Find the mistakes, put them right, lay the script out and run it. There is no database on
this page: its run function answers from a small set of invented rides kept in the page.
// Node 18 or later, from the Pro or Gold package: check the reports' SQL in CI, and fail when a query plan gets dearer.
import { readdirSync, readFileSync } from 'node:fs';
import { checkSql, sqlSchemaFromColumns, readSqlPlan, compareSqlPlans } from './dist/node/bmx-sql-editor.mjs';
// The rows of SELECT table_name, column_name, data_type FROM information_schema.columns, saved as JSON.
const schema = sqlSchemaFromColumns(JSON.parse(readFileSync('schema/columns.json', 'utf8')));
let failed = 0;
for (const name of readdirSync('reports').filter(n => n.endsWith('.sql'))) {
const { ok, problems } = checkSql(readFileSync(`reports/${name}`, 'utf8'), { dialect: 'postgres', schema });
for (const p of problems) console.log(`reports/${name}:${p.line}:${p.column} ${p.severity}: ${p.message}`);
if (!ok) failed++;
}
// EXPLAIN (FORMAT JSON) of the late-returns query, from the last release and from this build.
const before = readSqlPlan(readFileSync('plans/late-returns.release.json', 'utf8')).plan;
const after = readSqlPlan(readFileSync('plans/late-returns.build.json', 'utf8')).plan;
const { cost } = compareSqlPlans(before, after);
if (cost[1] > cost[0] * 1.5) {
console.log(`late-returns: estimated cost ${cost[0]} became ${cost[1]}`, after.hotSpots.map(h => h.message));
failed++;
}
process.exit(failed ? 1 : 0);
A slow query, before and after an index
Copperbeck's late-returns screen takes more than a second at the end of the month. Its plans here are
invented PostgreSQL EXPLAIN (ANALYZE, FORMAT JSON) output, for an imagined table of 2.4 million
rides. Find where the time goes, add the index, and explain the query again to compare the two plans. Then
see an invented SQL Server showplan of another report, with the index SQL Server itself suggests.