Databases

A relational data source lets Bitween read rows into exchanges and run SQL for deliveries. Four engines are supported.

Each enabled node runs one adapter process per database, holding a connection pool. Every subscription bound to that database shares the process and its pool. Database sources are placed on every enabled node by default, so pool sizes apply per node.

For engine-by-engine advice on writing statements, see the per-engine guide.

How it fits together#

Data source            the connection and its pool
 ├── Statements        the named SQL the connection may run
 └── Subscriptions bound to the data source
      ├── as a receiver   poll a statement and turn rows into exchanges
      └── as a handler    run a statement with the exchange as parameters

• SQL lives in statements, never in subscription properties. A subscription names the statement it runs. Partner values are therefore never templated into SQL, and people can write queries without the right to change credentials.
• Every value reaches the database as a bound parameter.

Connection settings#

Every engine#

AllowAdHocSql is an ordinary setting, so anyone with data-sources.edit can turn it on.

PostgreSQL#

Placeholders are written @name.

MySQL#

Placeholders are written @name. MySQL has no RETURNING, so execute returns only a row count.

SQL Server#

Placeholders are written @name. Schema and SnapshotIsolation each add a round trip per statement.

Oracle#

Placeholders are written :name.

Statements#

A statement belongs to one data source.

Statements are managed on the data source's page and need the data-source-statements permissions. A statement cannot be deleted or renamed while a subscription uses it, and expanding a statement lists those subscriptions.

Checked when saved#

When a statement is saved, Bitween asks the running adapter whether the database accepts it.

• A bare procedure name passes without checking that the procedure exists.
• An error that looks like the wrong placeholder style comes with a hint.
• The check is skipped, and the save reports not checked, when the adapter is not running on the node serving the request.
• Editing a statement re-checks it only when the SQL changed.

Oracle executes DDL while parsing. Saving or testing a statement such as DROP TABLE against an Oracle source runs it. Connect Oracle sources with a login that has no DDL rights, and grant statement permissions accordingly.

Saving any statement restarts the database's adapter on the next reconcile, within 30 seconds. A new statement can be used once that has happened.

Receiving rows#

Use the database adapter as a scheduled job's Source, and bind the job to the data source.

The key and cursor columns belong to the polled statement. The subscription page can edit them, which saves the statement straight away.

On the first run the cursor is 0, or 1900-01-01 for timestamps.

select id, order_no, customer, updated_at
from orders
where id > @cursor
order by id

Each run does the following.

1. Runs the statement with the cursor, taking at most the batch size of rows.
2. Turns each row into one exchange. The input is a JSON object keyed by column names as the database returns them, so Oracle names are upper case. The file is named {key}.json.
3. After each exchange is stored, runs the mark-processed statement with key and every column of the row as parameters, then saves the cursor at that row's value.

• Order the statement by the cursor column. The cursor moves to each accepted row's value in turn, so unordered rows can be skipped or read again.
• The cursor is kept per subscription, so several scheduled jobs can poll the same database independently.
• If a row fails before its exchange is stored, the run fails and the row is read again next time.
• If a row's exchange is stored but marking it or saving the cursor fails, the next poll creates the exchange again. Database receiving has no deduplication.

Writing with a handler#

Use the database adapter as a subscription's Delivery, and bind it to the data source.

With a statement set, the exchange content must be a JSON object. Each property becomes the parameter of the same name, with or without the engine's prefix. Nested objects and arrays are sent as JSON text, and null as a database null.

{ "order_no": "SO-1001", "customer": "Acme", "total": 125.5 }

insert into orders (order_no, customer, total)
values (@order_no, @customer, @total)
returning id

Without a statement, the content must be a request that names one, or carries raw SQL when AllowAdHocSql is on.

{ "name": "insert-order", "parameters": { "order_no": "SO-1001" }, "timeoutSeconds": 10 }

The response file is JSON with rows, affectedRows, output, columns and elapsedMs, plus cursorId and hasMore when rows were left over.

• Each statement commits on its own.
• A database error fails the exchange, and retry policies see it as an Error. A database result is never a bad response.
• Rows beyond MaxRows cannot be fetched by a delivery, and the cursor left open holds a pooled connection until it times out. Keep delivery queries under the limit.

Testing and browsing#

• Test connection checks the connection, authentication, a simple query and the login's privileges, then checks every active statement and reports each result.
• Stats shows the running adapter's counters.
• The schema browser lists tables, views, functions, procedures and sequences, with approximate row counts. Use in a statement fills in the statement form with a starting query in the engine's style. Names that are not lower case are wrapped in double quotes, which MySQL rejects unless ANSI_QUOTES is on.

These all ask the adapter on the node serving the request. See Data sources.

API#

Inspect accepts { "command": "Discover", "arguments": { ... } } with objectType, schema, nameLike and take (1 to 1000, default 200), or { "command": "Describe" } for the engine's capabilities, placeholder prefix and the login's privileges.

Limits#

• The database adapters do not declare the mapper role, so they are not offered as mappers.
• Bitween does not check that a subscription's data source matches its adapter. A mismatch fails when an exchange runs.
• A subscription that uses a database adapter without a bound data source returns its own connection settings, including the password, unmasked.
• Anyone with data-sources.view can read the database catalogue and the login's privileges.
• Data source settings, including passwords, are stored unencrypted.