Levels of BroadSQL

Most people learn one command, get their answer and stop there. BroadSQL has more to offer for everything you do more than once. The levels below go in order: each adds one idea to what you already do, and each solves a different kind of repetition. Stop at the level your work needs.

Level 1: SQL at one prompt, on every database

What you gain: one console for every database you reach, with the same commands on each.

Type your SQL as usual: what BroadSQL does not recognize as its own command goes to the database unchanged.
Explore without leaving the prompt: SHOW TABLES, DESCR <table>, SHOW FK, FIND COLUMN.
Let the console help: Tab completes table and column names, Up and Ctrl+R recall earlier commands.

Next step: Getting started, then Exploring a database.

Level 2: the same query, everywhere, without retyping

What you gain: the check you just ran, run again in another Environment, and lists you did not have to type.

/ runs the last query again; / PROD runs it once on the production connection of the same Database Group.
ENV QA moves to the QA connection of the same application.
List sources put a list where it already is into a query: <@clipboard>, <@last:ID>, <@excel:...>.

Recipes: Check a figure in QA, then in production, Use a list of values from the clipboard.

Level 3: results out, data in, copies kept fresh

What you gain: results where people need them, and local copies you can work on.

DUMP writes a table, a query or what is on screen to Excel, ODS, CSV, JSON, Markdown, HTML or a local H2 database, with one syntax.
MODE MERGE KEY keeps a local copy of a table up to date; MODE APPEND adds rows to a worksheet.
LOAD checks a CSV file against a table, shows a preview, then inserts it in one transaction.
REPEAT watches a query at a fixed interval and can keep a timeline in a file.

Recipes: Build a weekly Excel report, Keep a local copy up to date, Load a CSV file.

Level 4: Scripts you run by name

What you gain: the routine you repeat, saved once, run the same way by anyone on the team.

A Script is a text file of SQL and BroadSQL commands in the Scripts Library, run with @name.
-- @param declares typed parameters, with defaults and lists; a wrong or missing value runs nothing.
ON ERROR STOP, ECHO and OUTPUT QUIET control how a run behaves; DUMP LIB exports its result only when it succeeds.
LIB LIST, LIB FIND and LIB LINT keep a growing library findable and consistent.

Recipes: Save a query and run it by name, Write a report Script that stops at the first failure. Start from the Scripts Gallery.

Level 5: logic and new commands

What you gain: loops, conditions and several connections at once, and commands of your own.

JS RUN runs a JavaScript file that can hold several connections open, loop and branch (experimental).
An extension JAR adds custom commands to BroadSQL, for a team that needs a permanent command of its own.

This level means writing a small program: it runs with the rights of BroadSQL itself and deserves the care of any code that touches a real database.

Pages: JavaScript scripting, Extending BroadSQL.

Safety at every level

On a connection of an Environment flagged Production, BroadSQL asks before any statement that can change data, whatever the level that runs it.
The activity log keeps what you ran and what it printed: Investigate a support ticket and keep the evidence.

Found something wrong or unclear on this page? Tell us, with the page address.