BroadSQL BroadSQL Downloads
Documentation / Light scripting with JS

Light scripting with JS

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.

Running a script

JS RUN migrate_active_customers.js;
JS RUN migrate_active_customers.js PROD_ORDERS ARCHIVE_DB;
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 SCRIPT RUN/LIB RUN, 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 today's row count from the CUSTOMER table

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

Connect to a database as you normally would, then run it:

CONNECT MYDB;
JS RUN hello_db.js;
CUSTOMER table currently has 42 rows.

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 CUSTOMER 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.

-- migrate_active_customers.js
-- Copies every CLOSED order with a positive amount from PROD_ORDERS into ARCHIVE_DB.

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.");

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

Run it with JS RUN migrate_active_customers.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 migrate_active_customers.js PROD_ORDERS ARCHIVE_DB;

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

What it does, step by step:

  1. connect('PROD_ORDERS') and connect('ARCHIVE_DB') 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["amount"] to read a column off that row by name.
  3. The for loop walks every row from the source and, for each one whose amount is positive, builds and runs an INSERT against the target connection, ARCHIVE_DB, 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. migrated 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 exactly like SCRIPT LIST/SCRIPT FIND (see SQL Library & Scripts): the same -- @description/@instance/@environment/@tags/@alias/@status metadata header, the same instance/environment scoping (an entry tagged for a different instance 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: Migrate closed orders into the archive database
-- @instance: PROD_ORDERS
-- @environment: PROD
-- @tags: migration, archive, orders
-- @alias: migrate_orders
-- @status: stable

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

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 instance or environment prints a one-line warning but still runs it; the tag is informational, not a hard gate.

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.

Related pages