BroadSQL Download
On this page
Documentation / JavaScript scripting

JavaScript scripting

JavaScript scripting is experimental: read What to know before relying on this before you depend on it.

This page is for developers. JS runs a plain JavaScript file (or a one-line snippet typed directly on the command line) with a small set of BroadSQL-provided functions for opening connections and running queries: a much lower-friction alternative to writing and compiling a full custom command JAR for something that is really a short automation.

It fills a specific gap. The interactive shell holds exactly one live connection at a time (CONNECT always closes whatever was open before), so anything that needs two databases open at once (comparing, migrating, reconciling) or a loop with conditional logic driving several statements is not possible today without leaving BroadSQL. A JS script can do both, in a plain text file you can edit, version control, and hand to a colleague, without ever opening a Java project or running a build.

SQL Scripts or JavaScript?

Most automation does not need JavaScript. SQL Scripts are the normal way to automate work in BroadSQL: a text file of SQL and BroadSQL commands with named arguments, variables, scalar query assignment (LET x = SELECT ...), messages (ECHO), output control (OUTPUT) and an error policy (ON ERROR). Start there.

JavaScript scripting is the advanced capability for what SQL Scripts intentionally do not do: loops, conditions, general-purpose logic, and several database connections open at the same time.

NeedUse
Run a sequence of SQL statements and BroadSQL commandsSQL Script
Pass named values to it, keep a value read from the databaseSQL Script: arguments and variables
Export the result of a prepared querySQL Script with DUMP LIB
Repeat something for each row, or branch on a valueJavaScript
Read from one database and write to another in one runJavaScript

The two are separate: a .js file is not a Script, is not run by @ or LIB RUN, does not appear in the Scripts Library, and does not share SQL scripting variables.

Running a script

JS RUN copy_big_cities.js;
JS RUN copy_big_cities.js WORLD WORKCOPY;
JS EVAL println(2 + 2);

JS RUN <file> [args...] resolves <file> against the JsScripts catalog (see Application settings): a relative path, a declared @alias, or an unambiguous bare file name, then runs it. Unlike @ and LIB RUN, which run Scripts and never search, the extension is not optional: pass the file name with its .js extension (or a declared @alias, which matches regardless of extension). Any extra tokens after <file> are passed into the script as args (args[0], args[1], ...).

JS EVAL <code> runs a short snippet typed directly on the command line, the quick, no-file equivalent of JS RUN.

A first script

The smallest useful script is a handful of lines. Save this as hello_db.js:

-- @description: Prints the row count of the COUNTRY table

if (db === null) {
    println("No active connection. Connect first, then run this script.");
} else {
    var rows = db.execute("SELECT COUNT(*) AS CNT FROM COUNTRY");
    println("COUNTRY table currently has " + rows.get(0)["CNT"] + " rows.");
}

Connect to the WORLD sample, then run it:

BroadSQL session: after each prompt, what you type; the other lines are what BroadSQL prints
BroadSQL> CONNECT WORLD;
WORLD> JS RUN hello_db.js;
COUNTRY table currently has 254 rows.
Illustrative output: the values come from the WORLD sample database and the layout from this release; it was not captured from a BroadSQL run, and your timings will differ.

There is no compile step, no plugin folder to drop a JAR into, and no restart. Save the text file, type one command, see the result. On its own, a script this small doesn't buy you much over typing SELECT COUNT(*) FROM COUNTRY directly. The point of this example is the mechanics: the file, the JS RUN command, the db binding, println. Once those are familiar, the step from here to a script that opens two connections, loops over rows, and branches on a condition (something the interactive prompt cannot express at all) is small. That's what the example under "Two connections at once" below shows.

Try a one-off snippet with no file at all:

JS EVAL println(2 + 2);
4

The scripting API

Before running your code, BroadSQL binds the following into a fresh JavaScript context. Nothing is ever reused or shared across runs: every execution of JS RUN or JS EVAL gets a clean slate.

NameWhat it does
connect(name)Opens a new, independent connection to name (a connection already registered and visible to SHOW ALL CONNECTIONS). Does not touch, close, or get closed by whatever is connected interactively, so a script can hold several connections open at once. The script is responsible for calling .close() on it.
dbThe connection already active in the interactive session, if any (null if nothing is connected, so check before using it). Calling .close() on it does nothing: the script did not open it, so it cannot close it out from under the session.
<connection>.execute(sql)Runs a SELECT and returns every row read eagerly into memory: .size(), .get(i) (a row, plain key/value access by column name, e.g. row['AMOUNT']). No cursor, so not suitable for a huge result set.
<connection>.executeUpdate(sql)Runs an INSERT/UPDATE/DELETE/DDL statement, returns the affected row count.
<connection>.close()Closes a connection opened with connect(...). A script that forgets to call it leaves the connection open until BroadSQL exits.
print(...) / println(...)Writes to the console, exactly like a command's own output (never raw JavaScript output, so it respects logging and redirection).
argsThe trailing tokens from JS RUN <file> arg1 arg2, as args[0], args[1], ... (empty for JS EVAL).

A syntax error, a thrown value, or a SQL error from .execute/.executeUpdate never crashes the shell: BroadSQL catches it, reports it with a line number where one is available, and the console is ready for your next command right after, the same failure contract every other command already has.

Two connections at once

This example demonstrates the actual gap described at the top of this page: two live database connections open at the same time, something the interactive shell cannot do.

-- copy_big_cities.js
-- Copies the WORLD cities of more than eight million inhabitants into the table BIG_CITY of WORKCOPY.

var source = connect('WORLD');
var target = connect('WORKCOPY');

var rows = source.execute(
    "SELECT ID, NAME, POPULATION FROM CITY WHERE POPULATION > 5000000"
);

var copied = 0;
for (var i = 0; i < rows.size(); i++) {
    var row = rows.get(i);
    if (row["POPULATION"] > 8000000) {
        target.executeUpdate(
            "INSERT INTO BIG_CITY (ID, NAME, POPULATION) VALUES (" +
            row["ID"] + ", '" + String(row["NAME"]).trim().replace(/'/g, "''") + "', " + row["POPULATION"] + ")"
        );
        copied++;
    }
}

println("Copied " + copied + " of " + rows.size() + " cities above five million inhabitants.");

source.close();
target.close();

The target table must exist first: DUMP (SELECT ID, TRIM(NAME) AS NAME, POPULATION FROM CITY WHERE 1 = 0) TO WORKCOPY.BIG_CITY AS H2; creates WORKCOPY and an empty BIG_CITY. On WORLD, the script reads 24 cities and copies the 10 above eight million. Run it with JS RUN copy_big_cities.js;, or pass the two connection names as arguments instead of hard-coding them, so the same file works against any pair of environments:

JS RUN copy_big_cities.js WORLD WORKCOPY;

(reading them inside the script as args[0]/args[1] in place of the literal names).

What it does, step by step:

  1. connect('WORLD') and connect('WORKCOPY') each open a separate, independent connection, looked up from the same connection registry CONNECT/SHOW ALL CONNECTIONS already use. Neither touches the other, and neither touches whatever is connected interactively, if anything.
  2. source.execute(...) runs the SELECT and reads every matching row into memory up front. rows is then an ordinary collection: rows.size() for the count, rows.get(i) for a row, and row["POPULATION"] to read a column off that row by name.
  3. The for loop walks every row from the source and, for each one whose POPULATION is above eight million, builds and runs an INSERT against the target connection, WORKCOPY, a different database from where the row came from. This is the part no single SQL statement, and no sequence of statements typed one at a time at an interactive prompt, could do across two databases at once.
  4. copied counts how many rows actually passed the condition and were copied, separately from the total row count read from the source.
  5. println reports a one-line summary through the normal BroadSQL console, and both connections are closed explicitly, releasing them instead of leaving them open until the process exits.

If either connection fails to open, or the SELECT/INSERT hits a SQL error, the script stops with an error message and BroadSQL's prompt is ready for the next command right after, exactly as if any other command had failed.

Cataloging scripts

JS LIST [<searchTerm>|ALL] and JS FIND <term> work like LIB LIST/LIB FIND (see Scripts Library, which also spells out exactly what @instance/ @environment do and don't enforce): the same -- @description/@instance/@environment/@tags/ @alias/@status metadata header, the same Database Group/environment scoping (an entry tagged for a different Database Group or environment is hidden from the default view; JS LIST ALL shows everything), and the same grid output. The metadata header is skipped automatically before a script is handed to the JavaScript engine, so it never interferes with execution, even though -- is not JavaScript comment syntax.

-- @description: Copy the largest WORLD cities into the local copy
-- @instance: WORLDAPP
-- @environment: PROD
-- @tags: copy, cities, world
-- @alias: copy_cities
-- @status: stable

var source = connect('WORLD');
...

Once cataloged, JS LIST and JS FIND surface the script by its description, tags, or alias, exactly as they would a SQL library entry. Running a script whose tags don't match your current connection's Database Group or environment prints a one-line warning but still runs it; the tag is informational, not a hard gate, and this is never affected by the connection's environment being flagged Production.

JS does not currently have SHOW, EDIT, or DEL/archive-and-undo commands, only RUN, LIST, and FIND. Edit a .js file with any text editor, or BroadSQL's own EDIT <fileName>.

What to know before relying on this

  • No sandbox. A script runs at the same trust level as an extension JAR or a .bat file you already run: it can read/write files, start processes, or do anything else the JVM can do, through JavaScript's Java interop (Java.type(...)). This is not a safe way to run a script you did not write yourself.
  • CTRL+C cannot interrupt a script stuck in a pure JavaScript loop (e.g. while (true) {}) that never calls into a database. CTRL+C still works to cancel a script blocked on a slow query, the same as any other command.
  • No result streaming. execute(sql) reads the whole result set into memory before returning it, so avoid it for a query that returns a very large number of rows.
  • Production confirmation. On a connection of an Environment flagged Production, each execute or executeUpdate call that can modify the database asks for a confirmation on the console; a refusal raises an error in the script. See Production confirmation.

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