Skip to main content

Custom SQL: From Authoring to Execution

On the platform, "Custom SQL" generally refers to statements stored server-side, with parameters, that can be invoked by applications or BFF services. The CLI helps you list, inspect, validate, save (create or update), and do a test run, as well as generate code as covered in Code Generation & Menu.

SQL resources on the platform are identified by a sqlCode, which looks like xxxxxxxx-xxxxxxxx (8 hex digits + - + 8 hex digits). If you don't remember the code, start with sql list.

List Your SQL Resources​

rabetbase sql list
rabetbase sql list --name report
rabetbase sql list --sqlcode xxxxxxxx-xxxxxxxx

When pagination is supported, you can use --page and --pagesize (see --help for defaults).

View a Single SQL Definition​

rabetbase sql detail --sqlcode xxxxxxxx-xxxxxxxx

This returns the full statement content, parameter definitions, and more -- handy for copying to a local file or comparing changes.

Prepare a .sql file locally and validate before saving:

rabetbase sql validate --file ./queries/report.sql

You can also pass the SQL directly as a string:

rabetbase sql validate --sql "SELECT * FROM t WHERE id = #{id}"

Validation checks the statement type, flags dangerous operations, extracts parameters like #{param}, and more. If you want cross-checking of table and column names against specific datasets, pass:

rabetbase sql validate --file ./queries/report.sql --schemas <datasetCode1>,<datasetCode2>

In CI pipelines, use --format json to get machine-readable results.

Save to the Platform (Create or Update)​

Create (requires at least a file path; add other fields per --help):

rabetbase sql save --file ./queries/report.sql --sqlname monthly-report

Update (must include an existing sqlCode):

rabetbase sql save --file ./queries/report.sql --sqlcode xxxxxxxx-xxxxxxxx

Saving runs the same validation as validate -- it cannot be skipped. If you only want to see validation results without persisting:

rabetbase sql save --file ./queries/report.sql --sqlcode xxxxxxxx-xxxxxxxx --dry-run

In non-interactive environments (CI), add --non-interactive; to skip confirmation prompts, use --yes or similar flags (see the command's --help).

Execute a Saved SQL Statement​

Use this for self-testing or ad-hoc data queries:

rabetbase sql exec --sqlcode xxxxxxxx-xxxxxxxx
rabetbase sql exec --sqlcode xxxxxxxx-xxxxxxxx --params '{"keyword":"test","page":1}'

--params accepts JSON; keys must match the #{...} placeholders in the SQL.

Summary​

  • Use list / detail to manage sqlCode references.
  • Adopt a validate then save workflow to catch issues before they reach the server.
  • Use exec for test-running parameterized queries.

For generating TypeScript call snippets, see Code Generation & Menu. To write server-side scripts with Git integration, see BFF Scripts.