Getting started

This page walks through the first few minutes with BroadSQL: starting it for the first time, adding your first database connection, and running your first query. If you haven't installed BroadSQL yet, see Installation first.

What happens the first time you start BroadSQL

BroadSQL keeps every connection it knows about, including the one it uses for itself, in a single encrypted H2 database called the Connections Definition File, or CDF. The CDF has its own reserved connection ID, $CDF, and you can query and edit it exactly like any other database once you're connected to it.

The very first time you start BroadSQL (connect.bat on Windows, ./broadsql.sh on Linux), it opens the CDF automatically and asks for its password:

Enter master password:

Type the default password, clipper8AD, and press Enter. You'll land at the CDF's own prompt:

$CDF>

Change this password as soon as you can with SET MASTER PASSWORD;: it's the same password for every fresh install, so leaving it as-is means anyone with a copy of your ConnectionsDefinitionFile.cdf file (or broadsqlux.ini's equivalent) can open it.

Step 1: add your first connection

A connection is a row in the CDF's CONNECTIONS table: URL, USER_NAME, USER_PASSWORD, and a few more fields described below. You add one of two ways, depending on your platform.

Windows: type CONFIG; at the $CDF> prompt. This opens a graphical connections manager: fill in the fields and save. See CONFIG.

Linux: CONFIG isn't available: insert the row directly with SQL, since you're already connected to the CDF as a database:

INSERT INTO CONNECTIONS (ID, NAME, TYPE_ID, URL, USER_NAME, USER_PASSWORD, INSTANCE_ID, ENVIRONMENT_ID, STATUS_ID, COMMENT)
VALUES ('MYDB', 'My first database', 'PostgreSQL', 'jdbc:postgresql://localhost:5432/mydb', 'app_user', 'app_password', 'MYAPP', 'DEV', 'ACTIVE', '');

Either way, a connection has these fields:

Step 2: connect and run your first query

$CDF> CONNECT MYDB;
Connected to PostgreSQL connection 'MYDB'
MYDB> SELECT count(*) FROM some_table;

CONNECT tests the connection before switching to it, so a typo in the URL or a wrong password is reported immediately rather than leaving you stuck. On success, BroadSQL also runs SHOW DBINFO and SHOW AUTOCOMMIT automatically, so the first thing you see after connecting is a quick summary of what you just connected to.

From here, type any SQL your database understands, ending it with a semicolon: BroadSQL sends anything it doesn't recognize as one of its own commands straight to the database as-is.

The essentials

Every command, BroadSQL's own or plain SQL, must end with ;. A command can span several lines; it only runs once BroadSQL sees the closing ;. Several commands can also be typed on the same line, one after another, each ending with its own ;:

$CDF> SELECT 1; SELECT 2;

runs both, in order, exactly as if they had been typed on separate lines. If one of them fails, BroadSQL reports the error and stops there: any further command already typed on that same line is not run. A quoted value's own ; (SELECT 'a;b';) is never mistaken for a command separator.

Four commands to know before anything else:

When activatejline=ON (see activatejline), the Up/Down arrow keys and Ctrl-R recall commands from earlier in the session, and, by default, from earlier sessions too: history is saved per operating-system user and reloaded the next time BroadSQL starts (see jlinehistoryfile for where it is stored and how to change that). A password you typed is never recorded there, in this session or any previous one.

The same setting also turns on TAB completion for BroadSQL commands, SQL keywords and, once connected, database object names:

sel<TAB>                    -> SELECT
show end<TAB>                -> SHOW ENDPOINT (first of two matches, see below)
SELECT * FROM c<TAB>         -> completes directly if only one table/view starts with "c"

One matching candidate completes directly. Several matching candidates open a selection menu instead of BroadSQL picking one for you: TAB again moves to the next candidate, Shift-TAB moves back to the previous one, and typing more characters accepts whichever candidate is currently highlighted and keeps editing the line normally. Pressing Enter while the menu is showing accepts the highlighted candidate first (without running the line); press Enter again to actually run it. Table, view, schema and column completion needs an active database connection; BroadSQL command and SQL keyword completion work either way.

TAB also completes the names of things BroadSQL already knows, wherever a command expects one:

CONNECT W<TAB>              -> the connection IDs that start with W
CONNECT API C<TAB>          -> the API IDs that start with C
RUN get<TAB>                -> the endpoints of the active API whose name or alias starts with get
LIB EDIT QR<TAB>            -> the Scripts Library scripts that start with QR
@QR<TAB>                    -> the same scripts, for running
EDIT ENVIRONMENT P<TAB>     -> the Environments that start with P (EDIT GROUP does the same for Database Groups)

Matching is by prefix only and ignores case; the name inserted is always the one BroadSQL has stored, so CONNECT war<TAB> can become CONNECT WAREHOUSE_DEV. Nothing is guessed: if no name matches, the line is left as you typed it. The candidates are read from the same places the commands themselves use, so a connection, Environment, Database Group, API or Script you have just created or deleted is reflected immediately. A Script whose path contains a space is inserted in double quotes, the way BroadSQL expects it. Connections, Environments and Database Groups are the active ones; Scripts are listed as paths relative to the Scripts Library, without its archived entries.

Two shortcuts worth knowing early:

• / on a line by itself re-runs the last SELECT/INSERT/UPDATE/DELETE you ran: handy for re-checking a SELECT after an UPDATE, or re-running the same query against a different connection after CONNECT.
• @script runs a Script: a text file of SQL statements and BroadSQL commands, run in order as if you had typed them one by one. @maintenance/cleanup.bsql runs a Script from the Scripts Library (the scripts folder shipped with the install), @./helper.bsql a file relative to where you started BroadSQL, and @C:\temp\foo.bsql a file anywhere. See Scripts and the Scripts Library, and the User guide for the file macro (<@file>).

See the command reference for every command BroadSQL has.

Database Groups and environments

Every connection has, in addition to the usual JDBC URL/driver/user/password, an environment and, optionally, a Database Group: two independent ways to group connections that solve different problems. A connection with no Database Group is standalone.

• Database Groups answer "which product or system does this connection belong to?" (Wiki1, Wiki2, JIRA, MYSAP, and so on). Useful once you're managing connections across more than one product or team; manage them from the "Database Groups" tab of CONFIG (Windows), or by inserting directly into the CDF's INSTANCE table (kept under that name internally).
• Environments answer "which deployment platform is this?" (DEV, TI, QA, LIVE, and so on), with their own Production flag and comment. Manage them from the "Environments" tab of CONFIG, or in the CDF's ENVIRONMENT table.

A connection can't reuse the same (Database Group, Environment) pair as another connection already registered. Neither grouping changes how a connection behaves; they exist so SHOW ALL CONNECTIONS, SHOW GROUP, SHOW ALL GROUPS, and SHOW ALL ENVIRONMENTS can filter a long list down to something you can actually read once you have more than a handful of connections.

JDBC drivers

BroadSQL ships with drivers for H2, PostgreSQL, Apache Derby, HSQLDB, and SQLite: SHOW DRIVERS; lists every driver actually available (bundled or otherwise) in your installation, with versions. Any other JDBC-compatible database (Oracle, MySQL, SQL Server, MariaDB, and more) works too, once you add its driver JAR and register its type: see Technical requirements for the exact steps and which types already exist in a fresh CDF.

Where to go next

• User guide: the full guide, one page per topic.
• Command reference: every command, arguments, and examples.
• Export: copy a query's results into a local H2 database, a spreadsheet, or a flat file.
• Scripts and the Scripts Library: save and re-run Scripts by name.
• Import: preview and load CSV data into an existing table.
• Extending BroadSQL: add your own commands.