APIs in BroadSQL: Configured Operations, SQL, and Automation

September 18, 2026

BroadSQL brings configured HTTP operations into the same workbench as SQL, local data, and exports. Its API architecture starts with reusable requests and points toward repeatable validation across systems.

An application investigation rarely stays inside one database. A support engineer may need to inspect an API response, check the records behind it, compare another environment, and preserve the findings. Each step is familiar. Reconstructing context between tools is the expensive part: selecting the right server, supplying credentials, repeating a request, and moving its results somewhere useful.

BroadSQL's API work addresses that boundary. It makes an external API a configured resource that users can inspect and invoke alongside a database Connection. The larger objective is to compose those operations with the SQL, scripting, local H2 storage, and export capabilities already central to BroadSQL.

BroadSQL 5.2.4 established the released API foundation. Current development, described here as of September 18, 2026, builds on it with URL-based invocation, better discovery, and more useful result handling. The longer-term design connects these capabilities to scripting and repeatable validation across systems.

An API client inside the SQL workbench

BroadSQL's Universal API Client makes outbound HTTP requests to external services. Its role is to bring those services into the SQL workbench: select a configured operation, supply its inputs, and work with the result. The API model described here is a client architecture.

The immediate value is reuse. Once an operation has a configured URL, method, environment, variables, headers, and authentication, invoking it should not require rebuilding the HTTP request. An engineer can concentrate on the resource and the result while BroadSQL supplies the stored request context.

This complements BroadSQL's database model without making HTTP look exactly like JDBC. A database Connection selects a database session. An API session selects an API definition and one of that API's Environments. These contexts coexist. Connecting to an API does not close the SQL connection, and disconnecting the API does not disconnect the database.

Database Groups organize database Connections, while API folders organize endpoint definitions. An API Environment supplies the API's server and variables independently of the active database Connection. That separation lets an investigation combine the contexts it needs, such as a service in a test environment and a local database holding reference data.

The architectural goal is a workbench in which configured operations and their results participate in the same investigation. HTTP keeps its request and response semantics, SQL keeps its relational model, and BroadSQL provides the commands that connect the two.

Consider a support procedure that begins with a customer identifier. The service response shows the application's view of that customer. A database query shows the records available to the investigator. A saved snapshot makes it possible to compare the two later, after the live system has changed. BroadSQL already has a role in each step; API support brings the service-side observation into the same working environment.

A configured operation carries more meaning than an isolated HTTP request. Its folder and name explain its purpose. Its Environment selects the deployment context. Its authentication and variables describe how to reach that context. A user can inspect and maintain this information once, then invoke the operation repeatedly with different inputs. The catalog becomes useful operational knowledge rather than a collection of copied command lines.

This also explains the division between configuration and execution. Configuration is where users establish shared request details. Execution is where they choose an operation and concrete values. Keeping those responsibilities separate reduces repetition while leaving the actual action visible. A short command can be concise because the surrounding context is explicit and inspectable.

What BroadSQL 5.2.4 established

By 5.2.4, API support was a complete usable workflow: import or configure an API, select an Environment, discover an endpoint, execute it, inspect the response, and export a suitable result. GET, HEAD, POST, PUT, PATCH, and DELETE were executable methods, covering both investigation and operations that change application state.

The catalog could be populated from a bundled Bruno OpenCollection YAML export. Import preserved the API's Environments, folders, endpoints, variables, headers, authentication, and request definitions. Re-import updated recognized objects and added new ones. Removing an item from the source collection did not automatically delete it from BroadSQL.

Alternatively, the Windows-only CONFIG API window allowed manual configuration while connected to $CDF, the Connections Definition File. Imported and manually created definitions entered the same catalog and used the same execution machinery. The window configured operations; it did not execute requests or display responses.

Environment selection gave a configured endpoint different base URLs and variable values without duplicating its definition. Authentication could be inherited from the API or a containing folder, or set directly on an endpoint. Optional local aliases provided names separate from both the display name and the import identity.

The following is released 5.2.4 syntax, assuming an imported or configured API called MYAPI, an Environment called Development, and an endpoint assigned the alias SEARCH. These names are examples of user configuration, not built-in services:

CONNECT API MYAPI:Development;
SHOW ENDPOINTS;
RUN SEARCH;
PULL API RESULT TO search_snapshot AS CSV;
DISCONNECT API;

In that release, RUN accepted an endpoint ID or alias in the active API context. The explicit form, EXECUTE API ENDPOINT MYAPI SEARCH Development;, supplied the same execution context without depending on an active API session. Current development changes the canonical RUN syntax; the example above is intentionally labeled for 5.2.4.

Configuration is an operation model

An endpoint definition contains the information needed to construct an operation, rather than merely bookmarking a URL. Request construction combines the method, URL template, parameters, headers, body, selected Environment, and effective authentication. Importing from Bruno and editing through CONFIG API are two ways to maintain that model.

Stored variable templates accept ${name} and {{name}}. Their scope proceeds from API variables through the selected Environment, then folders from root to leaf, then endpoint variables. More specific definitions override broader ones. This lets a shared definition acquire environment-specific values while still allowing an individual operation to specialize them.

Authentication follows a related but distinct rule. An endpoint's explicit authentication takes precedence over the nearest folder's authentication, then its ancestors, then the API default. Inherit continues the search. Explicit None stops it. That distinction prevents an intentionally unauthenticated endpoint from accidentally inheriting a configured authentication scheme.

Aliases address another kind of separation. A display name describes an operation to a person; an import identity recognizes it during synchronization; a BroadSQL alias is a local name chosen by the user. In 5.2.4 an alias was unique within its API and survived re-import of the recognized endpoint. It was not exported into Bruno YAML, so it was not portable through that file alone.

The practical benefit is that users can maintain the physical request and its local identity separately. A recognized endpoint can receive updated imported metadata while retaining its BroadSQL alias. The catalog becomes a maintained description of operations that people use, with a clear distinction between external definitions and local choices.

Execution and authentication

The released execution flow separates policy, request construction, authentication, transport, and display. Before sending the endpoint request, BroadSQL checks whether the method is supported and resolves the required configuration. The authentication layer then supplies the applicable credentials, and the HTTP transport sends the resulting request.

Both released invocation forms reach the same endpoint executor and apply the same authentication and method checks. API, endpoint, and Environment status are checked as part of execution. A saved session provides convenient context; the current definitions still determine whether an operation can run.

The catalog and execution layer have distinct responsibilities. Imported metadata can preserve operations that the runtime does not yet execute, including OPTIONS and structured form bodies. In 5.2.4, executable bodies include JSON, text, XML, and SPARQL text. Keeping the definition separate from execution support allows the catalog to retain useful information as the runtime develops.

A configured body uses the same variable substitution approach as the other request components. The user supplies suitable values and escaping for the external API's contract, and BroadSQL sends the resolved text. This keeps the relationship between the configured request and the transmitted request direct.

Running a configured POST, PUT, PATCH, or DELETE is an explicit instruction to send it. The selected Environment, credential, and endpoint define where that operation goes and under whose identity it runs. SQL and HTTP remain separate operations, with the database and service responsible for their respective transaction semantics.

OAuth2 demonstrates why authentication is a separate layer. Obtaining an access token requires its own HTTP exchange before the resource request. BroadSQL handles that exchange within the authentication runtime, then supplies the resulting token to the configured operation. Endpoint definitions can use this common mechanism without carrying provider-specific execution code.

BroadSQL 5.2.4 supports Basic authentication, Bearer tokens, API keys in a header or query parameter, and OAuth2 Client Credentials. Unsupported authentication is rejected rather than silently treated as anonymous access. The external API still decides what the supplied identity may read or change.

The OAuth2 authentication runtime includes an in-memory token cache tied to the effective authentication context, including the Environment and credential configuration. Expiry information governs reuse. This keeps token handling close to the credentials and environment that produced it, while the endpoint executor continues to work with an authenticated request.

API definitions share the encrypted CDF with database definitions. This gives the workbench a common place to maintain connection and API configuration, protected at rest by the CDF's master password. BroadSQL operates under the authority of the user who controls that file and its stored credentials; the external API enforces the permissions of the supplied identity.

Credential handling is built into the workflow. The displayed request URL redacts query values identified as authentication keys or secret endpoint parameters, and request construction does not echo the resolved body. Configuration tables mask secret values. Bruno export excludes recognized secret values by default, with explicit selection and confirmation when an export needs to include them.

These mechanisms keep credentials out of routine request preparation and display. Response data and exported results still belong to the user's information-handling workflow: the service determines their content, and the user chooses where to retain or share them.

Responses as data for SQL and export

BroadSQL distinguishes receiving an HTTP error from failing to obtain an HTTP response. A 401, 404, or 500 is an execution result with a status and possibly a body. A missing variable, unsupported operation, authentication setup failure, or network failure is a different class of problem. Treating them separately preserves information needed for diagnosis.

A 204 response is valid without a body. Nontextual responses are represented by metadata rather than dumped as arbitrary characters. JSON suitable for a table is tabularized in 5.2.4; other textual content remains inspectable. The released RAW option selects pretty-printed JSON or text instead of automatic table display. It is not a packet capture.

Tabularization is intentionally modest. Arrays of objects produce columns from their keys; missing values leave blank cells. A single object becomes one row. Arrays of primitive values use a VALUE column. Nested objects and arrays remain compact JSON inside cells rather than becoming additional relations.

The original response body remains available independently of its displayed form. A completed execution also records the last API result for subsequent export. A new valid execution attempt clears the previous API export snapshot before it can fail, preventing a failed request from leaving PULL to export an unrelated older response as though it were current.

A recorded HTTP response still needs interpretation. Successfully receiving and exporting an error body does not make the business operation successful. The user must inspect the status and the API's response semantics before treating the data as a valid outcome.

PULL API RESULT is the most concrete connection between API support and BroadSQL's established purpose. It takes the captured response rather than calling the endpoint again. That distinction matters for write operations, expensive requests, and services whose data changes between calls.

In 5.2.4 the result could be sent to H2, XLSX, ODS, CSV, TXT, JSON, Markdown, or HTML through the existing PULL exporters. A small intermediate table lets an API result enter the same export infrastructure as a SQL result. BroadSQL does not maintain an unrelated exporter for every API response format.

Materialization gives JSON a deliberately simple relational representation. Columns are text columns, nested values remain JSON text, and the response needs exportable columns to enter this workflow. Users can then apply the type conversions and comparisons appropriate to their data in SQL, rather than accepting an inferred schema that may misrepresent the service's values.

A local H2 snapshot can then participate in ordinary SQL analysis, including comparisons and joins with other available tables. The snapshot is local data, not a live SQL view over the remote API. The caller must handle types and choose when to capture another result.

This is already a useful integration model. An engineer can preserve what a service returned, compare it with database evidence, and export the findings using familiar commands. The architecture gains capability by reusing a data workflow rather than by turning the HTTP client into a separate product.

The ability to export the captured response changes what an API call produces for the investigator. It becomes a reusable observation: a particular response, obtained from a particular API and Environment at a particular time. Exporting that observation preserves the data already inspected. This gives a comparison a stable input even when the source service is changing.

For example, an engineer can pull a suitable response into H2, examine identifiers and statuses with SQL, and compare them with reference data available in the database workflow. Text columns make conversion explicit. A numeric comparison can use the appropriate SQL conversion; a nested JSON value can remain intact until the investigation needs to interpret it. The user decides which relationships matter.

Different destinations serve different purposes. H2 supports further local querying. A spreadsheet makes a result convenient to review with colleagues. CSV and text provide simple interchange, while JSON, Markdown, and HTML support other reporting needs. The architectural point is that API data reaches these destinations through an established BroadSQL capability. Learning API execution adds a new source to a workflow the user may already know.

Current development makes the request visible

The current development version replaces the canonical ID-or-alias RUN form with a relative URL and an optional method. GET is the default. The selected API and Environment come from CONNECT API; RUN no longer needs or accepts its own API and Environment clauses.

For a configured endpoint whose path is /api/customer/{id}, current development supports these documented forms:

CONNECT API MYAPI:Development;
RUN /api/customer/123;
VAR ID=123;
RUN /api/customer/:id;
RUN /api/customer/${ENV:CUSTOMER_ID} RAW;

The final line requires that operating-system environment variable to exist. These examples describe the development syntax, not 5.2.4 syntax, and the customer endpoint must be configured in MYAPI.

A typed relative URL is not permission to send an arbitrary request with the selected API's credentials. BroadSQL matches the method and path structure against active configured endpoints. No match is an error; multiple matches are an ambiguity to resolve, not an invitation to choose silently. Configuration still supplies authentication, headers, and the body.

The change makes the operation visible in command history and reusable text. A reader can see the method, resource path, and explicit arguments without first resolving a local numeric ID. Endpoint names, IDs, and aliases remain useful for discovery through SHOW ENDPOINT, SYNTAX, and completion, but a bare RUN SEARCH; is now rejected.

This is a compatibility change. The older explicit execution command remains in the implementation as a hidden compatibility path, while URL-based RUN is the documented direction. Existing automation should be reviewed rather than assumed to accept the new grammar unchanged.

Current development separates endpoint matching from parameter binding. It first identifies the configured operation from method and path structure, then resolves placeholders and validates values against available parameter metadata. A literal path value stays an explicit argument rather than becoming another stored setting.

For a usual :id placeholder, resolution starts with a matching session VAR, then the API/Environment variable scope, then the endpoint's persisted parameter value, then its default. A required unresolved value fails before endpoint execution. Declared integer, boolean, and enumeration constraints validate values where the corresponding metadata exists.

There are distinct variable mechanisms. ${ENV:CUSTOMER_ID} reads the operating-system environment at invocation time. ${name} and {{name}} in the typed URL resolve from API/Environment variables before endpoint matching. Stored endpoint templates retain their broader folder and endpoint scope. These mechanisms should not be described as one interchangeable namespace.

VAR ID=123; is temporary session state. VAR ID=123 PERSIST; instead writes the current API Environment's variable and clears a same-name session override. It requires an active API context. Persistence is an explicit configuration change, not an automatic consequence of using a value once.

Configured parameter values and defaults also help make repeated calls concise. An omitted query parameter can receive a value through the resolution chain. Optional parameters without a value are omitted; required parameters without a value produce an error. The command and the configuration together describe the complete request.

Discoverability, output, and network deployment

Current development uses endpoint metadata for syntax help and completion as well as execution. Users can discover an operation through its name, ID, or alias and expand it into URL syntax. A required parameter should be visible while preparing the command, not only after a rejected request.

JLine supplies interactive completion, while parsing and execution remain separate from terminal editing. Scripts and noninteractive input do not acquire a dependency on pressing TAB. Further completion refinements are still under development; they should not be read as functionality shipped in 5.2.4.

Response presentation is also changing. The current default is a complete vertical LIST view that preserves JSON structure. TABLE is an explicit choice for suitable shapes, and RAW retains its JSON/text role. These modes do not silently select a preferred subset of response fields. They change presentation rather than the underlying response.

Current clipboard integration recognizes the most recent successful SQL or API result. This extends the existing COPY RESULT workflow to API work without requiring an intermediate file. Users can inspect a service response, copy its tabular data into another tool, or preserve it through PULL, choosing the handoff that suits the task.

Proxy configuration makes the API client easier to use within an organization's network. Current development adds NONE, SYSTEM, and MANUAL modes: preserve the existing Java networking behavior, use platform-dependent system proxy discovery, or configure the API HTTP client explicitly. SYSTEM uses a JVM-wide setting, while MANUAL applies to the API client. The choice belongs to deployment configuration rather than individual endpoint definitions.

The current CONFIG API workflow also emphasizes deliberate configuration changes. A common Save action and unsaved-change handling distinguish editing from persistence when moving among an API, an Environment, and an endpoint or folder. This is consistent with the command-line distinction between temporary VAR values and explicit PERSIST, although the two interfaces edit different objects. Neither interface should make it unclear whether the next invocation uses a transient choice or saved configuration.

That clarity matters before adding more automation. A repeatable procedure needs a known operation, understandable input precedence, and inspectable output. Improving those fundamentals is architectural work even when the visible change is a better command, editor, or result display.

From requests to repeatable validation

The documented longer-term direction is to make configured API operations composable within BroadSQL scripts. A logical operation would have an explicit callable contract, bind inputs into its HTTP definition, and expose selected response values to subsequent steps. The aim is to reuse configured operations without making each script reconstruct HTTP details.

Callable contracts and response extraction are longer-term design goals. The design describes a future CALL-style mechanism whose grammar remains to be specified. The important idea is the contract: a script supplies named business inputs, configuration maps them into an HTTP request, and a selected result becomes available to the next step.

The next architectural layer described in those plans is a scenario combining API calls and database operations. A useful scenario might create a business object through a service, follow its identifier through another system, and compare the resulting records with an expected outcome. This is an intended use case, not a runnable example of existing scenario commands.

Such workflows need more than a sequence of successful HTTP responses. An accepted request may finish asynchronously. Validation needs bounded waits, explicit assertions, and a record of what happened. The design therefore includes polling, assertions, test cases, suites, and structured execution results as parts of the intended architecture, rather than as a promised release schedule.

H2 and SQL have a concrete role in that destination: retain execution evidence as data that can be queried, reconciled, compared across runs, and exported. Tracking business identifiers and end-to-end outcomes would answer a different question from measuring an individual HTTP response time: whether the intended process actually completed across systems.

There is an important distinction between automating requests and automating a useful procedure. Sending the same request repeatedly solves only the first problem. A procedure also needs to carry an identifier forward, select the right environment, decide what counts as success, and retain enough information to explain a failure. The vision places those concerns around the configured operation, so the request definition remains reusable across different procedures.

An order-validation scenario illustrates the intended composition. One operation could create an order, another could retrieve its processing state, and SQL could examine the corresponding records. A later step could compare the observed state with an expected result. The purpose of future response extraction would be to pass the order identifier between those steps. The purpose of assertions would be to make the expected outcome explicit. Each capability would have a clear place in the procedure.

Repeatability also depends on environment separation. The same logical procedure should be usable with different API environments and appropriate database Connections, while configuration supplies the deployment-specific details. This follows directly from the existing distinction between an endpoint definition and its Environment. Extending that distinction into scenarios is more coherent than maintaining separate copies of every request for every deployment.

The planned result model would make a completed scenario useful beyond its immediate console output. SQL could identify failed checks, compare observed values, or relate several calls through a business identifier. Export could package the findings for review. This is why local H2 storage appears throughout the architectural direction: it gives operational evidence a form that BroadSQL already knows how to query and present.

The intended result is an operational and integration-validation workbench built from BroadSQL's existing strengths. Configured APIs supply operations, scripts express procedures, SQL examines outcomes, and exports preserve evidence. BroadSQL 5.2.4 established the usable foundation; current development improves how people discover, invoke, and inspect those operations. The longer-term goal is to make that same model support repeatable procedures across databases and services.

For command details, see the Universal API Client guide. The Export guide describes result destinations, and the Security guide explains the CDF and file-protection model. The API guide evolves with development, so use the version distinctions in this article when comparing it with a 5.2.4 installation.