Custom SQL local development guide

This guide is for everyday developers. The goal is to bring the platform's custom SQL into local project maintenance — create, pull, edit, validate, push, execute, and delete locally — so you avoid the "changed on the platform, unknown locally" drift.

When to use this:

• You write complex queries, cross-table statistics, or reporting SQL.
• You want to pull existing platform SQL into local long-term maintenance.
• You want SQL files to go through code review, version control, or AI Agent workflows.
• You want local validation and change previews before pushing to the platform.

When it's not a fit:

• Simple data CRUD — prefer the SDK's filter/getOne/create/update/delete.
• Simple grouped summaries — consider the SDK aggregate first.
• Cross-system calls, transaction orchestration, or complex business logic — prefer a BFF.

---

1. Prerequisites

Confirm the project already has a .rabetbase.json and the default app is correct:

rabetbase app list --format json

If a project has multiple apps configured, switch to the one you want to work with:

rabetbase app use <appName>

You can also skip switching the default app and just add this to individual commands:

--app <appName>
# 或
--appcode <appCode>

Confirm the CLI's SQL commands:

rabetbase sql --help

The recommended main flow is currently:

list/detail -> create 或 pull -> 编辑本地文件 -> validate -> status -> push -> exec -> delete

---

2. Local directory conventions

The CLI maintains SQL files under the project root at:

.rabetbase/sql/<appCode>/<dbName|db-<id>>/<sqlCode>_<sqlName>.sql|xml

For example:

.rabetbase/sql/app-xxxxxxxx/order_db/2305f915-dd48cd4c_getOrderList.sql
.rabetbase/sql/app-xxxxxxxx/db-10001/2305f915-dd48cd4c_getOrderMapper.xml

It also maintains a lock file:

.rabetbase/sql.lock.json

The lock file records:

• the remote SQL's remoteId
• sqlCode
• the current file path
• the SQL name
• the bound database
• the local hash
• the remote version number

As a developer you normally only edit the .sql/.xml files — never edit sql.lock.json by hand.

---

3. View existing SQL on the platform

List SQL first:

rabetbase sql list --format json

Search by name:

rabetbase sql list --name 用户 --format json

View details:

rabetbase sql detail --sqlcode <sqlCode> --format json

If you need the full raw object:

rabetbase sql detail --sqlcode <sqlCode> --verbose --format json

Tip: when you don't know the sqlCode, run list --name first; once you have the sqlCode, run detail.

---

4. Create a new SQL

To create a new SQL:

rabetbase sql create --name <sqlName> --db-id <dbId> --mode sql --yes --format json

MyBatis XML mode:

rabetbase sql create --name <sqlName> --db-id <dbId> --mode mybatisXml --yes --format json

Preview before running it for real:

rabetbase sql create --name <sqlName> --db-id <dbId> --mode sql --dry-run --format json

After a successful create, the CLI will:

• Create the SQL on the platform.
• Receive the server-generated sqlCode.
• Generate a local file under .rabetbase/sql/<appCode>/<dbName|db-<id>>/.
• Write an entry into .rabetbase/sql.lock.json.

The generated local file carries an @lovrabet header comment, for example:

-- @lovrabet.sqlCode: 2305f915-dd48cd4c
-- @lovrabet.sqlName: getUserList
-- @lovrabet.dbId: 10001
-- @lovrabet.dbName: order_db
-- @lovrabet.mode: sql
-- @lovrabet.syncedAt: 2026-04-20T10:00:00.000Z

SELECT 1 AS id;

These header comments are for local identification and troubleshooting. sql push strips them automatically on upload — they never get written back into the platform SQL body.

---

5. Pull existing SQL to local

If the SQL already exists on the platform, pull it locally before editing:

rabetbase sql pull --sqlcode <sqlCode> --format json

You can also pull in bulk by name:

rabetbase sql pull --name 用户 --format json

Preview which files will be written:

rabetbase sql pull --sqlcode <sqlCode> --dry-run --format json

If the local file differs from the remote, the CLI won't overwrite it by default and returns something like:

local differs from remote

Once you've confirmed you want the remote to overwrite local, run:

rabetbase sql pull --sqlcode <sqlCode> --force --format json

Caution: --force overwrites local unpushed changes. Before using it, check:

rabetbase sql status --format json

---

6. Edit SQL files

Edit the generated or pulled .sql/.xml files directly.

Plain SQL example:

SELECT
  id,
  username,
  phone
FROM app_user
WHERE status = #{status}
ORDER BY create_time DESC;

MyBatis XML example:

<select id="query" resultType="map">
  SELECT
    id,
    username,
    phone
  FROM app_user
  WHERE 1 = 1
  <if test="status != null">
    AND status = #{status}
  </if>
  ORDER BY create_time DESC
</select>

Use #{paramName} consistently for parameters. When you execute later, the keys in --params must match the parameter names in the SQL.

---

7. Validate before pushing

Validate a local file:

rabetbase sql validate --file ./.rabetbase/sql/<appCode>/<dbName>/<sqlCode>_<sqlName>.sql --format json

Or validate a piece of SQL directly:

rabetbase sql validate --sql "SELECT * FROM app_user WHERE id = #{id}" --format json

Validation checks:

• The SQL type, such as SELECT/INSERT/UPDATE/DELETE/DDL
• Whether it contains dangerous statements
• Referenced table names
• #{param} / ${param} parameters

To cross-check table/column names against datasets:

rabetbase sql validate --file <filePath> --schemas <datasetCode1>,<datasetCode2> --format json

Tip: after writing SQL, run validate first, then status, then push.

---

8. Check local status

Check the status of SQL files under the current app:

rabetbase sql status --format json

Output categories:

Category	Meaning	Typical action
added	Exists locally but not in the lock	Decide whether to establish sync via sql create or sql pull
modified	Local file changed vs. the lock	Run validate first, then push --dry-run
missing	In the lock but the local file is gone	Re-run sql pull --sqlcode <code>
unchanged	Local matches the lock	Usually nothing to do
remoteOnly	On the remote only, not local/lock	Checked when --remote is added; pull as needed

Check SQL that exists only on the remote:

rabetbase sql status --remote --format json

---

9. Push changes to the platform

Preview first:

rabetbase sql push --sqlcode <sqlCode> --dry-run --format json

Then push for real once confirmed:

rabetbase sql push --sqlcode <sqlCode> --yes --format json

Without --sqlcode, all SQL under the current app's local sync directory gets scanned:

rabetbase sql push --dry-run --format json

If the local hash matches the lock but you still want to force a push:

rabetbase sql push --sqlcode <sqlCode> --force --yes --format json

After a successful push, the CLI will:

• Upload the SQL/XML body to the platform.
• Strip the local @lovrabet header comments automatically.
• Refresh the hash, path, version number, and other fields in sql.lock.json.

---

10. Execute and verify

Run without parameters:

rabetbase sql exec --sqlcode <sqlCode> --format json

Run with parameters:

rabetbase sql exec --sqlcode <sqlCode> --params '{"status":1}' --format json

The response usually contains:

{
  "rows": [],
  "rowCount": 0,
  "elapsed": 123
}

Tip: run exec at least once after every push to confirm the SQL parameters, fields, and response structure match what callers expect.

---

11. Delete SQL

Preview first:

rabetbase sql delete --sqlcode <sqlCode> --dry-run --format json

Then delete once confirmed:

rabetbase sql delete --sqlcode <sqlCode> --yes --format json

After a successful delete:

• The platform SQL is deleted.
• The local SQL file moves to .rabetbase/sql-trash/.
• The matching entry in sql.lock.json is removed.

After deleting, confirm with:

rabetbase sql list --name <sqlName> --format json
rabetbase sql status --format json

---

12. sql save is deprecated

You may still see this in older workflows:

rabetbase sql save --file ...

Don't use it as the create or update entry anymore. It has been replaced by:

# 新建
rabetbase sql create --name <sqlName> --db-id <dbId> --mode sql|mybatisXml --yes

# 修改
rabetbase sql push --sqlcode <sqlCode> --yes

If you run sql save, the CLI only returns a migration hint — nothing is actually saved.

---

13. Recommended development SOP

Create new SQL:

rabetbase sql create --name getUserList --db-id 10001 --mode sql --dry-run --format json
rabetbase sql create --name getUserList --db-id 10001 --mode sql --yes --format json
rabetbase sql validate --file ./.rabetbase/sql/<appCode>/<dbName>/<sqlCode>_getUserList.sql --format json
rabetbase sql status --format json
rabetbase sql push --sqlcode <sqlCode> --dry-run --format json
rabetbase sql push --sqlcode <sqlCode> --yes --format json
rabetbase sql exec --sqlcode <sqlCode> --params '{"status":1}' --format json

Modify existing SQL:

rabetbase sql list --name getUserList --format json
rabetbase sql pull --sqlcode <sqlCode> --format json
rabetbase sql validate --file ./.rabetbase/sql/<appCode>/<dbName>/<sqlCode>_getUserList.sql --format json
rabetbase sql status --format json
rabetbase sql push --sqlcode <sqlCode> --dry-run --format json
rabetbase sql push --sqlcode <sqlCode> --yes --format json
rabetbase sql exec --sqlcode <sqlCode> --params '{"status":1}' --format json

Delete SQL:

rabetbase sql delete --sqlcode <sqlCode> --dry-run --format json
rabetbase sql delete --sqlcode <sqlCode> --yes --format json

---

14. FAQ

14.1 How to find the db-id

If you know which database to use but not its ID, check the platform's database configuration, or have your team record commonly used dbId values in the project docs.

If you're just maintaining existing SQL, prefer sql pull — the CLI generates the directory from the dbId in the remote SQL.

14.2 local differs from remote

This means the local file differs from the remote content. First decide which direction you want:

• To keep local changes: run sql push --sqlcode <sqlCode> --dry-run, then add --yes once confirmed.
• To discard local changes: run sql pull --sqlcode <sqlCode> --force.

14.3 missing remote version

This means the lock has no remote version number. Run this first:

rabetbase sql pull --sqlcode <sqlCode> --force --format json

Then edit and push again.

14.4 What happens if I rename the local file

The <sqlName> in the filename is treated as the current SQL name. Renaming the file alone is enough for sql status to report it as modified, and running sql push updates the remote SQL name accordingly.

14.5 What happens if I move the file to another database directory

If you move the file from one database directory to another, sql push re-resolves the target dbId from the directory name and syncs the remote binding. Keep the CLI-generated directory names — don't rename them to something the CLI can't recognize.

14.6 When to use --force

Use it only when you explicitly want to overwrite local files or force a re-push:

• sql pull --force: overwrite local with the remote.
• sql push --force --yes: force a push even when the local hash looks unchanged.

---

15. Summary

For day-to-day work, developers only need to remember:

新建用 create，修改用本地文件 + push，拉远端用 pull，提交前先 validate 和 status，sql save 不再使用。

Local custom SQL tutorial