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.

Try it now: the WORLD sample database

BroadSQL comes with a sample database, WORLD: countries, cities, languages, currencies and regions, in an H2 database included in the download. There is nothing to install or configure; from the $CDF> prompt:

$CDF> CONNECT WORLD;
WORLD> SHOW TABLES;
WORLD> DESCR COUNTRY;
WORLD> SELECT CODE, NAME, CONTINENT FROM COUNTRY WHERE CONTINENT = 'Europe';
WORLD> SHOW REFERENCES COUNTRY;

With the interactive console, DESCR COU followed by TAB offers COUNTRY and COUNTRYLANGUAGE. The Exploring a database and Export pages work well with it too, for example DUMP COUNTRY TO countries AS CSV;.

The database is the file samples/WorldDB.mv.db in the BroadSQL installation folder; the launchers (connect.bat, BroadSQL.bat, broadsql.sh) always start BroadSQL from that folder, so the relative URL finds it wherever you start them from. WORLD is ordinary sample data: change it as you like (insert, update, create your own tables). To get the original back, replace samples/WorldDB.mv.db with the one from the BroadSQL download while BroadSQL is not connected to 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 (or any platform, from the prompt): CONFIG isn't available on Linux: type ADD CONNECTION MYDB; and answer its questions (name, type, URL, user name, password, Database Group, Environment, comment), then y to save. The driver is taken from the type, and the new connection is tested right after it is saved. See ADD CONNECTION.

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:

Using the console

The console remembers what you type and helps you type it:

See Interactive console for every keyboard shortcut, what TAB completes where, command history, and the colors and table layout, which follow your terminal and the color, theme and displaymode settings.

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 Running Scripts, 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. Its Database Group and Environment must also be active: saving a connection that refers to an inactive one is refused. 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.

Switching Environment: ENV

Within a Database Group, you usually think in Environments rather than connection IDs. ENV <environment> switches to the connection of that Environment in the current connection's Database Group:

TATD3> ENV QA;
TATV3>

With TAT / QA mapped to connection TATV3, ENV QA opens TATV3 exactly as CONNECT TATV3 would (same checks, login script and prompt); the prompt shows the connection. ENVT and CONNECT ENVIRONMENT are aliases of ENV.

• CONNECT <name> always means a connection and ENV <name> always means an Environment, so a connection and an Environment with the same name never conflict.
• ENV never looks in other Database Groups: from a standalone connection (no Database Group), it fails.
• It also fails, leaving the current connection open, if the Environment does not exist, has no connection in the current Database Group, is mapped to an inactive connection, or if that connection cannot be opened.

To run the last query on another Environment without switching, use / <environment> (see Patterns). The whole model, with everyday workflows, is explained in Connections, Database Groups and Environments.

Editing the CDF directly

The CDF is an ordinary H2 database, so you can query it with CONNECT $CDF;. Reading is always safe. Prefer CONFIG and the BroadSQL commands for changes: they validate what direct SQL does not, and a mistake in the CDF can stop BroadSQL from loading your connections. Back up the file before editing it.

Do not create, drop, or alter tables or columns in the CDF. BroadSQL upgrades its schema itself when it starts, and it expects exactly the tables and columns above. Do not change the master password with SQL; use SET MASTER PASSWORD;. Changes made while BroadSQL is running are only picked up after a reload, so restart BroadSQL after editing.

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

• Application settings: the settings file, starting with the export and log folders.
• User guide: everyday work at the prompt, step by step.
• Interactive console: keyboard shortcuts, TAB completion, history, colors and table layout.
• Command reference: every command, arguments, and examples.
• Import / Export: copy a query's results into a local H2 database, a spreadsheet, or a flat file, and load CSV data into an existing table.
• Scripting overview: save and re-run Scripts by name, with variables and named arguments.
• Extending BroadSQL: add your own commands.