On this page

Documentation / Connections, Database Groups and Environments

Connections, Database Groups and Environments

BroadSQL organizes the databases you work with around three ideas. A Connection is one physical database you can open. A Database Group gathers the Connections of one application or system. An Environment says which deployment stage a Connection belongs to: DEV, QA, PROD and so on. Together they let you think "the sales database, in QA" instead of remembering a list of unrelated connection names.

Concept	Answers	Example
Connection	Which physical database do I open?	`SALES_QA`, `SALES_PROD`, `WORKCOPY`
Database Group	Which application or system is it part of?	`SALES`
Environment	Which deployment stage is it?	`QA`, `PROD`

Connection

A Connection is one database endpoint and the way to reach it: a JDBC URL, a database type (which gives the JDBC driver), a user name and a password, plus a short ID that you type to open it. Connections are stored, with their credentials encrypted, in the Connections Definition File (CDF). You create them with CONFIG on Windows or ADD CONNECTION on any platform (see Getting started).

CONNECT <id> opens a Connection:

$CDF> CONNECT SALES_QA;

The Connection is tested first. If the test fails, the error is reported and the current Connection stays open, so a typo never leaves you without a session.
On success, the previous Connection is closed: BroadSQL has one open database Connection at a time.
The prompt changes to the Connection ID, the Connection's login script runs, and a short summary of the database (driver, product and version, autocommit) is printed.

DISCONNECT (or BYE, CLOSE) closes the current Connection and returns to the CDF itself, $CDF: BroadSQL is never left without a connection. PING <id> (or CHECK <id>) tests a Connection without opening it.

Database Group

A Database Group is the application or system a Connection belongs to: SALES, CRM, MYSAP. One application usually has several physical databases, one per deployment stage, and the Database Group is what ties them together. A Connection without a Database Group is standalone; the local H2 databases created by DUMP ... AS H2 are standalone, for example.

A Database Group differs from a Connection in that you never open it: it is a label shared by several Connections. Its value is in the rule it carries: **a Database Group holds at most one Connection per Environment**. That rule is what makes "the QA database of SALES" a precise answer, and what ENV and / <environment> rely on.

SALES_QA> SHOW GROUP;
List of environments for Database Group 'SALES':
|------------|---------------|--------------------|---------------------------------------------|---------------|
|ENVIRONMENT |ID             |USER                |NAME                                         |TYPE           |
|------------|---------------|--------------------|---------------------------------------------|---------------|
|PROD        |SALES_PROD     |                    |Sales database, production                   |H2             |
|QA          |SALES_QA       |                    |Sales database, QA                           |H2             |
|------------|---------------|--------------------|---------------------------------------------|---------------|

2 database connections found

Database Groups are managed in the "Database Groups" tab of CONFIG, or with ADD GROUP, EDIT GROUP and DEL GROUP. SHOW ALL GROUPS lists them all.

Environment

An Environment is a deployment stage: DEV, INT, QA, PROD, or whatever names your organization uses. Environments form one list shared by every Database Group, each with a description, a comment and a Production flag. LOCAL is built in.

SALES_QA> SHOW ALL ENVIRONMENTS;
|---------------|---------------------------------------------|----------|
|ID             |DESCRIPTION                                  |PRODUCTION|
|---------------|---------------------------------------------|----------|
|LOCAL          |Local                                        |false     |
|PROD           |Production                                   |true      |
|QA             |Quality assurance                            |false     |
|---------------|---------------------------------------------|----------|

3 environment(s) found

Each Connection of a Database Group is assigned one Environment, so a Database Group and an Environment together designate exactly one Connection: SALES in QA is SALES_QA. Environments are managed in the "Environments" tab of CONFIG, or with ADD ENVIRONMENT, EDIT ENVIRONMENT and DEL ENVIRONMENT.

`CONNECT` versus `ENV`

The two commands both open a Connection, but you name a different thing:

CONNECT <id> opens a physical Connection by its ID. It works for any Connection, in any Database Group or none.
ENV <environment> switches to the Connection of that Environment in the Database Group of the current Connection. It opens it exactly as CONNECT would: same test, same login script, same prompt.

SALES_QA> ENV PROD;
Disconnected from 'Sales database, QA'

Connected to 'SALES_PROD' using JDBC driver 'H2 JDBC Driver, 2.3.232 (2024-08-11)'
Database: H2 version 2.3.232 (2024-08-11)

SALES_PROD>

ENV PROD worked because SALES_QA is in the SALES Database Group, which has a Connection for PROD. The name after CONNECT is always a Connection and the name after ENV is always an Environment, so a Connection and an Environment with the same name never conflict. ENVT and CONNECT ENVIRONMENT are aliases of ENV.

ENV never searches other Database Groups. It fails, and leaves the current Connection open, when the current Connection is standalone, when the Environment does not exist, when the Database Group has no Connection for it, when that Connection is inactive, or when it cannot be opened:

SALES_PROD> ENV NOPE;
ERROR: Cannot switch to Environment 'NOPE': Environment 'NOPE' does not exist.

Why the model exists

The same application in several stages. Most databases you manage exist in DEV, QA and PROD. With a Database Group you move between them by stage (ENV QA, ENV PROD) instead of remembering SLS_Q2_NEW and SALES_PRD_01.
Comparing stages. A query you just ran in QA can be run once against PROD with / PROD, without leaving QA; COMPARE TABLE STRUCTURE compares a table with the same table in another Connection.
Knowing where you are. SHOW CONNECTION states the Database Group and Environment of the open Connection, and a Production Environment changes the color of the prompt.
Context for Scripts. A Script can be tagged with the Database Groups and Environments it is meant for (-- @instance:, -- @environment:); BroadSQL warns when it runs elsewhere, and LIB LIST shows the Scripts that match the current Connection (see Scripts Library).

Knowing where you are

The prompt shows the ID of the open Connection, followed by the API session when there is one:

SALES_QA>
SALES_QA [API CRM:TEST]>

The Database Group and the Environment are not part of the prompt. SHOW CONNECTION shows them, with the rest of the Connection's definition:

SALES_QA> SHOW CONNECTION;
CONNECTION: SALES_QA
NAME      : Sales database, QA
TYPE      : H2
URL       : jdbc:h2:tcp://dbhost/sales_qa
USER      : sales_reader
DATABASE GROUP : SALES
ENVIRONMENT    : QA

SHOW CONNECTION <id> shows another Connection without opening it, and SHOW DBINFOS shows the driver and database version of the open one.

Production Environments

When colors are on (the color and theme settings, see Interactive console), the prompt of a Connection whose Environment is flagged Production is shown in a distinct style, so you notice before you type. With colors off, the prompt looks the same in every Environment. The flag does not block, confirm or restrict any command, and it plays no part in the Script tag warnings.

Everyday commands

You want to	Command
Open a Connection	`CONNECT <id>` (aliases `OPEN`, `CON`, `CONN`)
Move to another Environment of the same application	`ENV <environment>`
Close the Connection and return to the CDF	`DISCONNECT` (aliases `BYE`, `CLOSE`)
Test a Connection without opening it	`PING <id>` (alias `CHECK`)
See the open Connection's definition, Database Group and Environment	`SHOW CONNECTION`
See another Connection's definition	`SHOW CONNECTION <id>`
See the database product and driver	`SHOW DBINFOS`
List every Connection, or those of one database type	`SHOW ALL CONNECTIONS [type]`
List the inactive Connections	`SHOW INACTIVE CONNECTIONS`
List the Connections of the current Database Group, by Environment	`SHOW GROUP`
List all Database Groups, or all Environments	`SHOW ALL GROUPS`, `SHOW ALL ENVIRONMENTS`
Run the last query again, here	`/`
Run the last query once on another Environment, staying here	`/ <environment>`
Show the last query without running it	`//` or `SHOW QUERY`

/ and / <environment> are typed on a line by themselves. / <environment> resolves the Environment in the current Database Group, as ENV does, but only for that one run: the open Connection does not change.

Examples

Check a figure in QA, then in production

SALES_QA> SELECT COUNT(*) AS FAILED FROM ORDERS WHERE STATUS = 'FAILED';
|--------------------|
|FAILED              |
|--------------------|
|2                   |
|--------------------|

1 rows fetched in 5 ms.

SALES_QA> / PROD
Running last query on SALES_PROD [PROD]...
|--------------------|
|FAILED              |
|--------------------|
|4                   |
|--------------------|

1 rows fetched in 0 ms.

SALES_QA>

The prompt still shows SALES_QA: only that one run went to SALES_PROD.

Work in production for a while, then come back

SALES_QA> ENV PROD;
SALES_PROD> SHOW CONNECTION;
SALES_PROD> SELECT * FROM ORDERS WHERE STATUS = 'FAILED';
SALES_PROD> DUMP / TO failed_prod AS XLSX;
SALES_PROD> ENV QA;
SALES_QA>

DUMP / exports the result you just looked at (see Exporting data).

Find the Connections of an application

SALES_QA> SHOW GROUP;
SALES_QA> SHOW GROUP SALES_QA PROD;

The second form keeps only the PROD row. SHOW ALL CONNECTIONS H2; lists every H2 Connection, whatever its Database Group.

What stays the same when you switch

Script variables belong to the BroadSQL session: they survive CONNECT, ENV and DISCONNECT.
An API session opened with CONNECT API is independent of the database Connection: switching one never closes the other (see Running API requests).
Pending changes are not carried over: opening another Connection closes the previous one, which rolls back its uncommitted work when autocommit is off.

Getting started: creating Connections, Database Groups and Environments, and editing the CDF.
Exploring a database: what to do once you are connected.
Interactive console: the prompt, colors, / and other shortcuts.
Application settings: color, theme and the other settings.
Command reference: CONNECT, ENV, DISCONNECT, PING, SHOW CONNECTION, SHOW GROUP, SHOW ALL CONNECTIONS, SHOW ALL GROUPS, SHOW ALL ENVIRONMENTS, SHOW QUERY.