Runbooks

A runbook is a plain .sql file that you keep in Git next to your code: the queries to run when checkout is slow, a dashboard of the numbers that matter, the checks a release must pass. Comment lines say what each query is called, how to draw its result, and what it should return. ElyraSQL Client opens the file as a tab of named blocks. elyrasql-cli run runs the same file in CI.

Your team shares runbooks through Git, the way it shares code: reviewed in pull requests and versioned. No account and no service sit in between.

The file

-- @title: Checkout is slow
-- What to look at, top to bottom, when the checkout latency alert fires.

-- @name: Orders per minute
-- The last hour. A gap means checkout stopped completing.
-- @chart: line x=minute y=orders
-- @expect: rows > 0
SELECT DATE_FORMAT(created_at, '%H:%i') AS minute, COUNT(*) AS orders
FROM orders
WHERE created_at > NOW() - INTERVAL 1 HOUR
GROUP BY minute ORDER BY minute;

-- @name: Payments stuck in pending
-- @expect: empty
SELECT id, provider, created_at FROM payments
WHERE status = 'pending' AND created_at < NOW() - INTERVAL 10 MINUTE;

-- @name: Revenue today
-- @expect: value > 0
SELECT SUM(total) AS revenue FROM orders WHERE created_at >= CURDATE();

Each statement is a block. The -- comment lines directly above it, with no blank line in between, belong to it:

Other comment lines above a block are its note, shown with it. The first comment paragraph of the file, set apart by a blank line, is the runbook's introduction.

Name runbooks *.elyra.sql so the Runbooks dialog finds them. Any .sql file opens as one, so a folder of checks for elyrasql-cli check is a runbook too.

@expect

The operators are =, != (or <>), <, <=, > and >=. A number is compared as a number, and a value in single quotes as text. Write a quote inside one by doubling it: 'O''Brien'. Column names are matched in any case. Quote one with backquotes or double quotes when it holds spaces.

To decide rows > N, at most N + 1 rows are read, up to 100,000.

A directive that cannot be read is shown on its block, and the block does not run until it is fixed. In CI that is exit 2. A mistyped @expect therefore never passes without checking anything.

In the app

Tools → Runbooks… (or Runbooks… in the command palette, ⌘K) lists every *.elyra.sql in a folder and the folders below it. Hidden folders and node_modules, target, vendor, dist and build are skipped. Click one to open it, or type the path of any .sql file.

The runbook opens as a tab:

• Connection and database. The bar shows what the blocks run on: the connection on screen when you opened it, which you can change. That way the same runbook runs against staging and production.
• ▶ Run all runs every block in turn. ■ Stop stops after the block that is running. Each block also has its own ▶ Run.
• Results. Each block shows its rows as a table, or as its @chart. Each @expect is marked ✓ or ✗ with what was found ("1 row", "revenue is 0"), and the bar counts what passed and failed.
• Open in query tab copies the block into a query tab on the runbook's connection, where you can change it or run it fully.
• Reload. The file is the source: edit it in your editor. When it changes on disk, the tab says so, and ↻ Reload reads it again.

Saved queries as a runbook

The Runbooks dialog can write the queries saved for the database on screen to a new runbook in the folder, one block each, named. Checks get -- @expect: empty. Commit the file and your team has them too. An existing file is never overwritten.

Reads only

A runbook is text from a repository, so anyone who can commit to it wrote its SQL. Its blocks run under the same rules as a saved check: one read statement each, in a read-only transaction that is rolled back, under the connection's statement timeout (30 seconds if it has none). A block that writes (an UPDATE to fix the stuck payments, for instance) is not run. It is marked as such, and Open in query tab puts it in front of you to read and run yourself, with the usual production safeguards.

Runbooks are free in every edition. Running them from elyrasql-cli against a remote server needs Pro, as the command line does; see Command line & CI.