BroadSQL Download
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 WORLD application, in QA" instead of remembering a list of unrelated connection names.

ConceptAnswersExample
ConnectionWhich physical database do I open?WORLD-QA, WORLD, WORKCOPY
Database GroupWhich application or system is it part of?WORLDAPP
EnvironmentWhich deployment stage is it?QA, PROD

The examples of this page use the WORLD sample and a practice set that does not exist by default: the Database Group WORLDAPP, with WORLD-IN and WORLD-QA, two copies of the sample, and WORLD itself playing the production database. Set up QA and production copies of WORLD creates it.

Database Groups, Environments and Connections A grid for the practice set built from the WORLD sample. The row is the Database Group WORLDAPP. Columns are Environments, IN, QA and PROD, PROD being flagged Production. Each cell is one Connection: WORLD-IN, WORLD-QA and WORLD. A standalone connection such as WORKCOPY belongs to no Database Group. CONNECT opens a cell by its ID. ENV PROD moves along the row of the current Database Group to its PROD connection. Slash PROD runs the last query once on that connection and comes back to QA. Database Group IN QA PROD (Production) WORLDAPP None WORLD-IN WORLD-QA WORLD WORKCOPY standalone, environment LOCAL: ENV and / PROD do not apply CONNECT WORLD-QA; opens one cell by its ID: the current connection. ENV PROD; moves along the row of the current Database Group, and stays there. / PROD runs the last query once on that cell and comes back: the prompt stays WORLD-QA.

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:

BroadSQL> CONNECT WORLD-QA;
  • The new Connection is opened first. If that 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.
  • A Connection whose URL opens BroadSQL's own configuration database (the CDF) is refused: see The configuration database (CDF).
  • 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, three names for one command) closes the current Connection. You then land at the BroadSQL> prompt, with no Connection, or, with the setting RememberLastConnection=TRUE, back on the Connection you were on before; BroadSQL never returns to the CDF by itself. ENV switches count as Connection changes for that history. See Starting, connecting and logging out. DISCONNECT API is a different command: it clears only the API context. 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: WORLDAPP in these examples, or the name of one of your applications. 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 WORLDAPP" a precise answer, and what ENV and / <environment> rely on.

BroadSQL session: after each prompt, what you type; the other lines are what BroadSQL prints
WORLD-QA> SHOW GROUP;
List of environments for Database Group 'WORLDAPP':
|------------|---------------|--------------------|---------------------------------------------|---------------|
|ENVIRONMENT |ID             |USER                |NAME                                         |TYPE           |
|------------|---------------|--------------------|---------------------------------------------|---------------|
|IN          |WORLD-IN       |                    |Sample World Database, integration           |H2             |
|PROD        |WORLD          |                    |Sample World Database                        |H2             |
|QA          |WORLD-QA       |                    |Sample World Database, QA                    |H2             |
|------------|---------------|--------------------|---------------------------------------------|---------------|

3 database connections found
Illustrative output: the values come from the WORLD sample database and the layout from this release; it was not captured from a BroadSQL run, and your timings will differ.

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.

BroadSQL session: after each prompt, what you type; the other lines are what BroadSQL prints
WORLD-QA> SHOW ALL ENVIRONMENTS;
|---------------|---------------------------------------------|----------|
|ID             |DESCRIPTION                                  |PRODUCTION|
|---------------|---------------------------------------------|----------|
|IN             |Integration                                  |false     |
|LOCAL          |Local                                        |false     |
|PROD           |Production                                   |true      |
|QA             |Quality assurance                            |false     |
|---------------|---------------------------------------------|----------|

4 environment(s) found
Illustrative output: the values come from the WORLD sample database and the layout from this release; it was not captured from a BroadSQL run, and your timings will differ.

Each Connection of a Database Group is assigned one Environment, so a Database Group and an Environment together designate exactly one Connection: WORLDAPP in QA is WORLD-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.
BroadSQL session: after each prompt, what you type; the other lines are what BroadSQL prints
WORLD-QA> ENV PROD;
Disconnected from 'Sample World Database, QA'

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

WORLD>
Illustrative output: the values come from the WORLD sample database and the layout from this release; it was not captured from a BroadSQL run, and your timings will differ.

ENV PROD worked because WORLD-QA is in the WORLDAPP 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:

BroadSQL session: after each prompt, what you type; the other lines are what BroadSQL prints
WORLD> ENV NOPE;
ERROR: Cannot switch to Environment 'NOPE': Environment 'NOPE' does not exist.
Illustrative output: the values come from the WORLD sample database and the layout from this release; it was not captured from a BroadSQL run, and your timings will differ.

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 WRLD_Q2_NEW and WORLD_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:

WORLD-QA>
WORLD-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:

BroadSQL session: after each prompt, what you type; the other lines are what BroadSQL prints
WORLD-QA> SHOW CONNECTION;
CONNECTION: WORLD-QA
NAME      : Sample World Database, QA
TYPE      : H2
URL       : jdbc:h2:./samples/WorldDB_QA
USER      : sa
DATABASE GROUP : WORLDAPP
ENVIRONMENT    : QA
Illustrative output: the values come from the WORLD sample database and the layout from this release; it was not captured from a BroadSQL run, and your timings will differ.

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. On such a Connection, BroadSQL asks for a confirmation before a statement that can modify the database: see Production confirmation. The flag plays no part in the Script tag warnings.

Production confirmation

When the open Connection belongs to an Environment flagged Production, BroadSQL asks before it runs a statement that can modify the database. Nothing is sent to the database before you answer.

When BroadSQL asks before a statement A decision flow. A statement that cannot change the database runs. A statement that can change it, on a connection whose Environment is not flagged Production, runs. On a Production connection, without a terminal to ask, it is refused. With a terminal, BroadSQL asks Execute yes or no: yes runs it; any other answer refuses it with SQLState BSQPR, and in a Script the whole run stops. A statement Can it change the database? no It runs yes Is the Environment flagged Production? no yes Is there a terminal to ask? no Refused no setting turns this off yes Execute? [yes/no] yes anything else Refused, SQLState BSQPR in a Script, the whole run stops
BroadSQL session: after each prompt, what you type; the other lines are what BroadSQL prints
WORLD> UPDATE COUNTRY SET POPULATION = 59225800 WHERE CODE = 'FRA';
WARNING: This operation can modify a Production database.
  Connection: WORLD (Environment PROD, Production)
  Statement:  UPDATE COUNTRY SET POPULATION = 59225800 WHERE CODE = 'FRA'
  Why:        it is an UPDATE
Execute? [yes/no]: yes
1 row(s) updated.
Illustrative output: the values come from the WORLD sample database and the layout from this release; it was not captured from a BroadSQL run, and your timings will differ.

On the practice set, answering yes changes WORLD; answer no to see the question without changing anything.

Only yes or y (any case) runs the statement. no, an empty answer, any other answer, end of input or CTRL+C refuse it:

BroadSQL session: after each prompt, what you type; the other lines are what BroadSQL prints
Execute? [yes/no]: no
Not executed.
SQL ERROR
------------------------------------------------------------
Connection : WORLD
SQLState   : BSQPR
Error code : 0

Production change refused on connection 'WORLD': the change was not confirmed. Nothing was executed.

Query:
1 | UPDATE COUNTRY SET POPULATION = 59225800 WHERE CODE = 'FRA'
------------------------------------------------------------
Illustrative output: the values come from the WORLD sample database and the layout from this release; it was not captured from a BroadSQL run, and your timings will differ.

Your answer is recorded in the activity log ("Production confirmation for WORLD: approved" or "refused"), not in the command history.

What asks and what does not

Never asksAlways asks
SELECT, WITH queries, SHOW, DESCRIBE, EXPLAIN, VALUESINSERT, UPDATE, DELETE, MERGE and every DDL (CREATE, ALTER, DROP, TRUNCATE...)
Session statements: ALTER SESSION, SET SCHEMA, SET search_path, SET ROLE, USEGRANT, REVOKE, other SET statements
COMMIT, ROLLBACK, SAVEPOINTCALL, EXEC, BEGIN and other blocks: BroadSQL cannot tell what they change
BroadSQL commands that only readSELECT ... INTO, SELECT ... FOR UPDATE, a sequence's next value, a WITH holding a data change, EXPLAIN ANALYZE

A statement BroadSQL cannot read (an unterminated quote, an unknown form) asks too. The rule applies wherever the statement comes from: typed, in a Script, in the Editor, in a login script, in a BroadSQL command that writes (LOAD, LINK TABLE), in JavaScript, or in a / <environment> rerun that reaches a Production Connection. A Connection of any other Environment never asks.

Every change, or once per session

The setting ProductionConfirmEveryChange chooses how often BroadSQL asks (see Settings):

  • TRUE (the default): before every statement that can modify the database. A Script of ten such statements asks ten times. A BroadSQL command is one statement: LOAD asks once for all its rows. In JavaScript, each execute or executeUpdate call asks.
  • FALSE: before the first such statement of each connected session. It asks again after any new connection: CONNECT, ENV, a switch to another Connection, DISCONNECT then CONNECT, an automatic reconnection after a lost connection, and LOGOUT. A / <environment> rerun and a JavaScript connect() open their own connection, so they ask each time.

A refusal never counts as an approval. A change of this setting in CONFIG (Application Settings tab) applies at once.

Scripts, typed lines and the Editor

  • A Script (@file, LIB RUN): a refusal stops the whole run, nested Scripts included, with "Run stopped: a Production change was refused at statement 3 of Script <name> (line 7)." Statements already executed stay executed.
  • A typed line with several statements: the refused statement and the statements after it are not run.
  • The BroadSQL Editor: the question is a dialog with Yes and No. No is the default button; Escape and closing the dialog also refuse. A refusal stops the run, as for a Script.

Without a terminal

When BroadSQL cannot ask (its input is redirected from a file, or no interactive terminal is attached), every statement that can modify a Production database is refused:

Production change refused on connection 'WORLD': confirmation is required and no interactive confirmation is
available. Nothing was executed.

No setting turns this off.

Transactions

The confirmation never commits or rolls back anything. With Autocommit=false, the changes you confirmed stay pending until COMMIT or ROLLBACK, as on any other Connection. On a database whose DDL statements commit the open transaction (Oracle, MySQL, H2 and others), the question of a DDL statement adds: "On this database, this statement also commits your pending changes."

SET PASSWORD on a Production Connection asks one question only, worded for Production: it is the confirmation.

Limits

  • BroadSQL recognizes statement forms, not what the database does: a function with side effects called from a SELECT, a trigger, a database link or the logic of a stored procedure cannot be seen.
  • The confirmation protects against mistakes. It is not access control: the rights of the database account remain the security boundary.

Everyday commands

You want toCommand
Open a ConnectionCONNECT <id> (aliases OPEN, CON, CONN)
Move to another Environment of the same applicationENV <environment>
Close the Connection (back to BroadSQL>, or to the previous Connection)DISCONNECT (aliases BYE, CLOSE)
Test a Connection without opening itPING <id> (alias CHECK)
See the open Connection's definition, Database Group and EnvironmentSHOW CONNECTION
See another Connection's definitionSHOW CONNECTION <id>
See the database product and driverSHOW DBINFOS
List every Connection, or those of one database typeSHOW ALL CONNECTIONS [type]
List the inactive ConnectionsSHOW INACTIVE CONNECTIONS
List the Connections of the current Database Group, by EnvironmentSHOW GROUP
List all Database Groups, or all EnvironmentsSHOW 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

A test city was added to the QA copy only (see Check a figure in QA, then in production), so the two runs differ:

BroadSQL session: after each prompt, what you type; the other lines are what BroadSQL prints
WORLD-QA> SELECT COUNT(*) AS CITIES FROM CITY WHERE COUNTRYCODE = 'FRA';
|--------------------|
|CITIES              |
|--------------------|
|41                  |
|--------------------|

1 rows fetched in 5 ms.

WORLD-QA> / PROD
Running last query on WORLD [PROD]...
|--------------------|
|CITIES              |
|--------------------|
|40                  |
|--------------------|

1 rows fetched in 0 ms.

WORLD-QA>
Illustrative output: the values come from the WORLD sample database and the layout from this release; it was not captured from a BroadSQL run, and your timings will differ.

The prompt still shows WORLD-QA: only that one run went to WORLD.

Work in production for a while, then come back

WORLD-QA> ENV PROD;
WORLD> SHOW CONNECTION;
WORLD> SELECT CODE, NAME, POPULATION FROM COUNTRY WHERE CONTINENT = 'Oceania' ORDER BY POPULATION DESC;
WORLD> DUMP / TO oceania_prod AS XLSX;
WORLD> ENV QA;
WORLD-QA>

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

Find the Connections of an application

WORLD-QA> SHOW GROUP;
WORLD-QA> SHOW GROUP WORLD-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.

Found something wrong or unclear on this page? , with the page address.