From query to autopilot: a BroadSQL productivity roadmap

September 4, 2026

Most people learn one BroadSQL command, get their answer, and stop there. That's fine for a single question. It's a missed opportunity for everything you do more than once, and BroadSQL has four more levels built specifically for that.

You already know more than you think

If you've typed a SELECT at the BroadSQL prompt and read the result, you've used the tool correctly. That's not a beginner's use of BroadSQL, it's the foundation every other feature sits on top of. The question this article answers isn't "how do I learn BroadSQL," it's "what do I reach for once plain SQL starts to feel repetitive." There's a clear order to that, and skipping straight to the fancy end usually means more setup than the job deserves. Working through the levels in order also means each one only asks you to learn one new idea, building on habits you already have from the level before it.

Here's the roadmap in one sentence: start with SQL and push it as far as it goes, hand the output to a spreadsheet when a human needs to look at or chart it, save and automate what you find yourself repeating, and only reach for real code when the job needs branching logic or a permanent new command. Five stops, each one solving a different kind of repetition. Let's walk through them.

Level 1: start with SQL, and do as much as you can with it

This sounds obvious, and it is, which is exactly why it's worth saying first: the single biggest productivity mistake is reaching for automation before the plain query has been pushed as far as it goes. A well-written SELECT, with the right WHERE, GROUP BY, and JOIN, answers more questions than people give it credit for. Before you build a script or a spreadsheet pivot, ask whether the database can just do the work: an aggregate, a window function, a filtered join, often gets you straight to the number you need, with the database's own indexes and query optimizer doing the heavy lifting instead of you doing it by hand afterward in a spreadsheet.

BroadSQL's job at this level is to stay out of your way. Anything it doesn't recognize as one of its own commands is sent straight to the connected database, unmodified, so there's no special dialect to learn on top of the SQL you already know. And because the same shell connects to H2, PostgreSQL, Oracle, SQL Server, Derby, HSQLDB, SQLite, and anything else with a JDBC driver, "start with SQL" doesn't mean relearning a new client's quirks every time the database behind it changes. The muscle memory you build at this level, how to write a good filter, how to structure a join, carries over to every level above it: an export is only as good as the query behind it, a saved library entry is only as reusable as the query it wraps, and a script's whole job is usually to run one of these queries again, exactly the same way, whenever it's needed.

There's a real limit here, and it's worth naming honestly: SQL is not a charting tool, not a place to build a slide, and not the right place to eyeball a hundred rows looking for the one that's off. That's not a weakness of SQL, it's simply outside its job. That's what level 2 is for.

Level 2: let export do the visual and analytical work for you

Once a query answers the right question, the next question is usually "now what do I do with this." If the answer involves a chart, a pivot table, sharing the numbers with someone who doesn't use BroadSQL, or just wanting to scroll through two hundred rows comfortably, the honest answer is: get it out of the terminal and into the tool built for that job. Spreadsheets are genuinely excellent at charts, pivots, and quick visual scanning. BroadSQL doesn't try to compete with them; it tries to get your query's results into one, cleanly, as fast as possible.

BroadSQL gives you two ways to do that. EXPORT is the original mechanism: a toggle you switch on, after which every query you run writes its result to a file instead of the screen, until you switch it back off. DUMP is its one-shot cousin, exporting an entire table in a single command. Both write Excel (.xlsx), OpenDocument (.ods), CSV, or plain text, chosen by the file extension you give.

For a new script, PULL is the more explicit, more predictable choice, and the one worth learning first if you're starting fresh today. One unified syntax names the source, the destination, and the format all in the same command, and it adds formats EXPORT/DUMP don't have at all: JSON for feeding another tool, Markdown for pasting a table straight into a wiki page or a pull request, and HTML for pasting into an email. PULL can also write straight into a tab of a spreadsheet you're already building, adding to it without disturbing the tabs already there, so several runs on different days build up one running workbook instead of a folder full of one-off files.

-- Send today's regional totals straight into a spreadsheet tab, ready for a pivot chart
PULL (SELECT region, SUM(amount) AS total FROM sales WHERE sale_date = CURRENT_DATE GROUP BY region)
  TO monthly_report.today AS XLSX;

-- Or drop the same numbers into a Markdown table for a status update
PULL (SELECT region, SUM(amount) AS total FROM sales WHERE sale_date = CURRENT_DATE GROUP BY region)
  TO today_totals AS MD;

Notice what didn't change between those two commands: the query. That's the point of this level. You already know how to write the SELECT; export just decides where the answer lands. Once a chart or a pivot is genuinely what the moment calls for, the spreadsheet is the right tool, and BroadSQL's job stops the moment the file is written.

What export doesn't solve is repetition. If you find yourself running the same export every Monday morning, typing the same query from memory (probably slightly wrong, one week to the next), that's not an export problem anymore. That's level 3.

Level 3: stop retyping, save it, then automate the routine around it

Somewhere around the tenth time you've typed a query you know you've typed before, hunting through shell history or an old notes file for the exact filter you used last time, it stops being "just SQL" and starts being a chore. BroadSQL splits the fix into two separate tools, because they solve two genuinely different problems: the SQL library saves a single query so you never retype it, and scripts save a whole sequence of steps so you never re-run a routine by hand.

The SQL library: never retype a query again

The library (LIB *) is a personal catalog of saved, optionally parameterized queries, stored as plain text files on disk. Save the revenue-by-country query once, give it a short alias, and from then on you run it by name instead of by memory:

-- @description: Monthly revenue by country
-- @tags: finance, monthly, revenue
-- @alias: rev
select country, sum(amount) from revenue where year = %1 and month = %2 group by country;

LIB RUN rev CH 2026;

The %1, %2 placeholders are what make a library entry more than a saved text snippet: the same query becomes reusable across countries, months, or any other value you'd otherwise be editing by hand each time. A short header of @description, @tags, @alias, and optionally @instance/@environment makes every entry easy to find later with LIB LIST or LIB FIND, even months after you saved it and forgotten the exact name. Delete safely too: LIB DEL archives rather than destroys, so an entry you thought you no longer needed is always one LIB UNDO away from coming back.

The library's job is narrow on purpose: one query, parameterized, easy to find again. That's exactly right for the fifty diagnostic and reporting queries a team ends up accumulating, and exactly wrong for a job that involves more than one statement, or a decision along the way.

Scripts: automate the whole routine, not just one query

A script (SCRIPT *, backed by the same @<file> mechanism BroadSQL has always had) is a text file holding a sequence of commands and SQL statements, run in order, exactly as if you'd typed each one at the prompt yourself. Where the library saves one query, a script saves the whole routine around it: connect, run three statements, export the result, disconnect, all from one command.

SCRIPT RUN weekly_close.sql;

Scripts are cataloged, searched, and safely deleted the same way library entries are (the same @description/@tags header, the same archive-and-undo pattern for SCRIPT DEL), so once you've learned to work with the library, working with scripts costs you nothing extra to learn. Pair a script with a login script (commands that run automatically every time a given connection opens), and a routine that used to mean "remember to do this every Monday, and get every step right" becomes a single named command, run the same way every time, instead of reconstructed from memory.

What a plain script can't do is make a decision. It runs statement one, then statement two, then statement three, always in that order, with no way to say "but only if the row count was zero" or "repeat this once for every row in that other table." Those two limits, no branching and no looping, are exactly where the roadmap's next level begins.

Level 4: when a fixed sequence of steps isn't enough

Every so often, a routine genuinely needs a decision baked into it: check a condition and only act if it's true, loop over a set of rows and do something different for each one, or hold two databases open at the same time to compare or migrate data between them. A plain script can't express any of that, and reaching for a full compiled extension for what's really a five- or ten-line piece of logic is often more setup than the job deserves. That gap is exactly what the JS command family closes.

JS RUN <file> runs a plain JavaScript file; JS EVAL <code> runs a one-line snippet typed directly into the shell, no file needed. Inside either one, a small set of BroadSQL-provided functions gives the script real teeth: connect(name) opens one or more independent database connections at once (something the interactive shell itself can never do, since CONNECT always closes whatever was open before), .execute(sql) runs a query and hands back rows you can loop over in ordinary JavaScript, and println writes output back to the console. No compiling, no plugin to build, no restart required: save the file, run one command, see the result.

-- migrate_closed_orders.js: copy every CLOSED order with a positive amount into an archive database
var source = connect('PROD_ORDERS');
var target = connect('ARCHIVE_DB');

var rows = source.execute("SELECT id, customer, amount, status FROM orders WHERE status = 'CLOSED'");

var migrated = 0;
for (var i = 0; i < rows.size(); i++) {
    var row = rows.get(i);
    if (row["amount"] > 0) {
        target.executeUpdate(
            "INSERT INTO orders_archive (id, customer, amount, status) VALUES (" +
            row["id"] + ", '" + row["customer"] + "', " + row["amount"] + ", '" + row["status"] + "')"
        );
        migrated++;
    }
}
println("Migrated " + migrated + " of " + rows.size() + " closed orders.");

Two databases open at once, a loop, a condition: none of that is expressible with plain SQL, export, a library entry, or a script, and now it's ten lines. Like a saved query or a script, a .js file can carry its own @description/@tags header and show up in JS LIST/ JS FIND, so a script worth keeping doesn't just sit in a folder waiting to be forgotten about.

Be clear-eyed about what this level costs, because it's a real step up from everything below it: a JS script runs at the same trust level as a compiled extension, with no sandbox protecting you from a mistake, and it holds a whole result set in memory before handing it back, which is fine for ordinary sized results and the wrong tool for a query returning millions of rows. This is the point on the roadmap where "automating a routine" turns into "writing a small program," and it should be treated with the same care you'd give any other piece of code that touches a real database.

Level 5: when the job needs to become a permanent, shipped command

The top of the roadmap isn't about doing something more complicated than level 4, a JS script already handles genuinely complex logic. It's about who the automation is for, and how long it needs to live. A custom extension makes sense when the thing you're building stops being "my script for my job" and becomes "a command every member of the team should have," with its own keyword, its own entry in HELP, and behavior you want locked down rather than editable by whoever opens the file next.

An extension is a class written in Java, compiled into a plain JAR, and dropped into BroadSQL's extensions/ folder. BroadSQL scans it at startup (this means a restart, not a hot reload) and the command it defines becomes a first-class citizen of the shell: it appears in HELP like any built-in command, gets its own arguments and examples, and has the same access to the console, the current connection, and even other BroadSQL commands that a core command does.

public class CommandTableInfo extends Command {
    public CommandTableInfo() {
        super("TABLEINFO", "TI");
    }

    @Override
    public void execute(String query) throws BroadSQLException {
        String tableName = parseArgs(query)[0].trim();
        console.writeln("Summary for " + tableName + ":");
        int rowCount = sqlDatabase.getInt("SELECT COUNT(*) FROM " + tableName);
        console.writeln(rowCount + " row(s).");
        getConsoleCommandInterpreter().setQuery("DESCR " + tableName);
        getConsoleCommandInterpreter().executeCommand();
    }
    // getDescription(), getArguments(), getExamples() feed HELP and the docs
}

This is the right tool when the automation needs to be distributed, not just run: shipped inside a team's own JAR, reviewed like real code, versioned, and available to every user of that JAR without them needing to find, understand, or trust a loose script file first. It's also the more deliberate choice: a Java project, a build, a JDK, and (for BroadSQL itself being closed source) compiling against an installed copy rather than a public artifact. The BroadSQL Extension Kit exists specifically to shortcut that setup with five working example commands ready to copy from, rather than assembling the pieces from a blank file.

If you're weighing a JS script against a compiled extension for the same job, the honest rule of thumb is: reach for JS first. It costs nothing to try, nothing to distribute if it turns out to be a one-person, one-time job, and nothing to throw away if it doesn't pan out. Only promote it to a real extension once you know, from actually having used it, that it deserves to be a permanent command other people rely on.

The roadmap at a glance

One table, the whole ladder: what each level is for, what it costs to set up, and the signal that tells you it's time to move up to the next one.

Three recurring jobs, three different rungs

The levels above are easier to feel than to describe abstractly, so here are three ordinary jobs, each one landing naturally on a different rung, none of them hypothetical: they're the shape of the recurring work most teams already have somewhere.

A support engineer, paged at 2am, needs the same five queries every incident. Which orders failed in the last hour, what the current queue depth looks like, whether a specific customer's account is locked: the queries themselves rarely change, but under pressure, typing them from memory, correctly, at 2am, is exactly when mistakes happen. This is a textbook library job: five entries, each with a clear @description and an @alias short enough to type half asleep, found with LIB FIND the moment it's needed rather than reconstructed from a wiki page nobody kept current. No automation is even involved, just five saved queries that are always exactly right.

A finance team needs Monday morning's regional totals in a spreadsheet, ready for the weekly review. Nobody wants to sit at a keyboard at 7am running exports by hand, and the query itself doesn't change week to week, just the date filter. This is a script: connect, run the query, PULL the result straight into a tab of the same running workbook, disconnect: the whole routine collapses into one command instead of five minutes of manual clicking every Monday morning. The spreadsheet is where the real work, the pivot, the chart, the narrative for the meeting, gets built; BroadSQL's whole job is making sure the numbers are already sitting there, correct, every single Monday.

A data steward needs to archive every closed order older than a year into a separate database, but only the ones with a positive amount, and only if the archive doesn't already have them. That's two databases open at once, a condition on the amount, and logic to avoid re-archiving what's already there. No plain script can express that "only if" and "only the new ones," which is exactly the gap a JS RUN script closes, ten or fifteen lines, run manually the first time to check the numbers, then run the same way every time once it's trusted. If that same archiving logic later needs to run identically for every database administrator on a team of twenty, with no possibility of someone quietly editing the file, that's the point where it graduates into a real, compiled command instead.

Picking your level without overthinking it

In practice, most people never consciously "choose a level." They write the SQL, notice it needs a chart, export it. They notice they've typed the same filter three Mondays running, save it to the library. The roadmap above isn't a plan to follow top to bottom on day one, it's a map for recognizing the moment you're already at, so the next step is obvious instead of a research project. A few honest questions do most of the work:

• Do I just need to see or share this once? Stay on level 1 or 2, and don't build anything.
• Will I run this exact query again with different values? That's a library entry, not a script.
• Does the routine have more than one step, but always the same steps in the same order? That's a script.
• Does it need a decision, a loop, or more than one database open at once? That's a JS script.
• Does it need to belong to the team rather than to me, as a real, permanent command? That's an extension.

None of the levels above replace the one below it, they sit on top of it. A well-tuned query is still the foundation of a good export, a good library entry is still just a query, and a good JS script is usually built from statements you already tested by hand at level 1. The productivity gain isn't in skipping levels, it's in recognizing, honestly, which one the job you're doing right now actually needs.

Get help climbing the ladder

I help teams turn a folder of half-remembered queries into a proper library, a Monday-morning routine into a script that runs itself, and a JS prototype that's proven its worth into a real, shipped extension. Get in touch if you're not sure which rung of this ladder your own recurring job actually belongs on.