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, create, pull, push, 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 it before pushing it to the platform:

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.

Create or Update SQL​

When creating SQL, the CLI first creates the platform resource, then scaffolds a managed local file in your project. In non-interactive environments, pass the name, database connection ID, and file mode explicitly:

rabetbase sql create --name monthly-report --db-id <dbId> --mode sql

After creation, edit the local file shown in the command output. Preview first, then push:

rabetbase sql push --sqlcode xxxxxxxx-xxxxxxxx --dry-run
rabetbase sql push --sqlcode xxxxxxxx-xxxxxxxx --yes

To update an existing SQL resource, pull it into the managed local directory, edit the file, then push:

rabetbase sql pull --sqlcode xxxxxxxx-xxxxxxxx
rabetbase sql push --sqlcode xxxxxxxx-xxxxxxxx --yes

sql push uses the same validation rules as validate; dangerous statements are blocked. In non-interactive environments (CI), add --non-interactive; to skip confirmation prompts, use --yes or similar flags (see the command's --help).

The old sql save flow is no longer the recommended path. Use sql create for new SQL, and sql pull / sql push for updates.

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 create / push 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.