Documentation / Getting started

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, LANDSCAPE_ID, STATUS_ID, COMMENT)
VALUES ('MYDB', 'My first database', 'PostgreSQL', 'jdbc:postgresql://localhost:5432/mydb', 'app_user', 'app_password', 'DEV', '$CDF', 'ACTIVE', '');

Either way, a connection has these fields:

Field	Description
`ID`	Short, unique identifier: what you type after `CONNECT` (case-sensitive, so pick a consistent casing convention).
`NAME`	A free-text, human-friendly description.
`TYPE_ID`	Must match a row already in the CDF's `TYPE` table: see Technical requirements for what's pre-registered out of the box and how to add another type.
`URL`	The JDBC connection URL for this database.
`USER_NAME` / `USER_PASSWORD`	Credentials used to open the connection.
`INSTANCE_ID`	Which deployment platform this is: see "Instances and landscapes" below.
`LANDSCAPE_ID`	Which project this connection belongs to: see below. `$CDF` always exists as a default.
`STATUS_ID`	`ACTIVE` or `INACTIVE`. An inactive connection still exists but won't offer itself for use.
`COMMENT`	Free text, optional.

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

Four commands to know before anything else:

Command	What it does
`CONNECT <id>`	Opens a connection (see above).
`DISCONNECT`	Closes the current connection, without opening another.
`HELP`	Lists every available command; `HELP <command>` shows one command's arguments and examples.
`EXIT`	Quits BroadSQL. This is the only way to close it that's guaranteed to work cleanly on every terminal: see Command activity log for one CTRL+C caveat.

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.
@<file> runs every command in a text file, in order, as if you'd typed them one by one: see User guide for this and the other file macros (<@file>, @<file>).

See the command reference for every command BroadSQL has.

Instances and landscapes

Every connection has, in addition to the usual JDBC URL/driver/user/password, an instance and a landscape, two independent ways to group connections that solve different problems:

Instances answer "which deployment platform is this?": DEV, TI, QA, LIVE, and so on. BroadSQL ships with a default set you can adapt; add more with INSERT INTO INSTANCE ... against the CDF the same way you add a connection.
Landscapes answer "which project does this connection belong to?": useful once you're managing connections across more than one product or team. A single landscape, $CDF, exists by default; add more in the CDF's LANDSCAPE table.

Neither grouping changes how a connection behaves; they exist so SHOW ALL CONNECTIONS and similar commands 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.
PULL: copy a query's results into a local H2 database, a spreadsheet, or a flat file.
Export & Dump: the older, still fully supported way to write results to a file.
Extending BroadSQL: add your own commands.