update-sqlx

name	update-sqlx
description	How to safely update SQLx offline query cache. MUST use when SQL queries change.

SQLx Offline Query Cache

Windmill uses SQLX_OFFLINE=true in CI, which requires all sqlx::query! / sqlx::query_as! macros to have matching cached query data in backend/.sqlx/.

When to Run

Run after adding or editing a SQL query in Rust source. Without it, CI fails with:

error: `SQLX_OFFLINE=true` but there is no cached data for this query

Do NOT run it when a change only removes queries. The cache is already complete for CI; all that is left are orphaned entries, which are cosmetic and never break a build. Running prepare to tidy them risks destroying the cache for no gain. Delete them offline instead: for each .sqlx/query-*.json, normalize its query field (strip \ line-continuations, collapse whitespace) and check whether it still appears in any .rs file. That detector reports ~48 false positives in a CE checkout — EE queries live in *_ee.rs symlinks it cannot read — so filter to the tables your change touched and delete only those.

Before You Run Anything

1. Back the cache up. prepare deletes .sqlx/ before regenerating, so any compile failure leaves it gutted (observed: 2350 → 142 entries).

   bash .agents/skills/update-sqlx/sqlx-cache.sh backup
   

   Its state is per-worktree, so a sibling worktree running prepare at the same time cannot overwrite your backup.
2. Point DATABASE_URL at THIS worktree's database. prepare compiles every sqlx::query! against the live database. Another worktree's DB lacks your migrations, so every new-table query fails and takes the cache down with it. The symptom is relation "<your_new_table>" does not exist — that is a wrong DATABASE_URL, not a broken query. See AGENTS.md → "Per-worktree ports and database".

Queries Inside Tests Need --all-targets, Which Fails In A CE Checkout

prepare only caches queries in code it compiles, and --workspace alone does not compile test targets. A sqlx::query! inside tests/*.rs therefore gets no entry, and CI fails on the test target with the usual "no cached data" error even though the lib built clean. SQLX_OFFLINE=true cargo check --workspace --all-targets is what reproduces it.

Adding --all-targets caches them — and, in a CE checkout, aborts partway through: backend/tests/otel.rs imports windmill_common::otel_ee, which exists only behind the private feature, so the compile dies after prepare has already emptied .sqlx/. Observed: 2435 → 4 entries, error: cargo check failed with status: exit status: 101.

Do not fight it — the abort is a pre-existing EE gap, not something your change caused. Take the entries you need and put the backup back:

bash .agents/skills/update-sqlx/sqlx-cache.sh backup

cd backend
DATABASE_URL=<this worktree's db> \
  cargo sqlx prepare --workspace -- --workspace --features all_sqlx_features --all-targets
# expected to fail; it still wrote the entries it got to before dying
cd ..

bash .agents/skills/update-sqlx/sqlx-cache.sh newq      # prints each added query
bash .agents/skills/update-sqlx/sqlx-cache.sh restore   # backup back, added entries grafted on

Read what newq prints before running restore — it shows each added entry's query field, and every one should be yours. The set is small (one per new test query); anything else in there means the run got further than you think.

Then verify both targets, since the lib passing says nothing about the tests:

SQLX_OFFLINE=true cargo check --workspace --features all_sqlx_features   # lib
SQLX_OFFLINE=true cargo check -p <your-crate> --all-targets              # tests

The Problem

cargo sqlx prepare --workspace deletes all existing cache files and regenerates only the ones found in the current compilation. If you don't compile with every feature flag (especially private for EE files), you will silently delete EE query caches, breaking CI for enterprise tests.

The standard ./update_sqlx.sh script tries to compile with all features, but it often fails locally because the EE symlinks can be out of sync with main.

Safe Procedure

Always preserve the existing EE caches from origin/main. Use this workflow:

cd backend

# 1. Restore the full cache from main (includes EE caches)
git checkout origin/main -- .sqlx/

# 2. Run prepare with OSS features (what compiles locally)
#    This regenerates OSS caches to match your code changes.
cargo sqlx prepare --workspace -- --workspace --features all_sqlx_features

# 3. Restore any EE caches that were deleted in step 2.
#    These are files present in origin/main but missing after prepare.
git ls-tree origin/main backend/.sqlx/ \
  | awk '{print $4}' | sed 's|backend/\.sqlx/||' | sort > /tmp/main_files.txt

find backend/.sqlx -name "*.json" -printf '%P\n' | sort > /tmp/current_files.txt

comm -23 /tmp/main_files.txt /tmp/current_files.txt > /tmp/missing_files.txt

while read f; do
  git show "origin/main:backend/.sqlx/$f" > "backend/.sqlx/$f"
done < /tmp/missing_files.txt

# 4. Verify nothing was lost from main
find backend/.sqlx -name "*.json" -printf '%P\n' | sort > /tmp/current_files.txt
comm -23 /tmp/main_files.txt /tmp/current_files.txt | wc -l
# Should output: 0

If EE Compiles Locally

If your EE repo happens to be in sync, you can use the full script (faster):

cd backend
./update_sqlx.sh

But if it fails with EE compilation errors, use the safe procedure above.

What NOT to Do

• Never run cargo sqlx prepare --workspace with only OSS features and commit the result — it will delete EE caches.
• Never set SQLX_OFFLINE=true for local cargo sqlx prepare — use a live database per CLAUDE.md. (CI runs with SQLX_OFFLINE=true, which is why the cache must be complete.)
• Never run prepare without a .sqlx backup, or against a DATABASE_URL you have not confirmed belongs to this worktree.
• Never run prepare at all for a removal-only change.
• Never skip the verification step (step 4 above).
• Never leave a --all-targets run's output in place after it aborts — it is a near-empty cache. Restore the backup and graft on only the entries you verified.

Step 4 compares against origin/main because step 1 restored from it, so the two agree. If you did not run step 1 — auditing a branch's cache on its own, say — compare against git merge-base HEAD origin/main instead: origin/main advances, so its newer entries would read as losses on your branch.

Verification

After committing, the diff against origin/main should show:

• A few new cache files (for your changed queries)
• A few deleted cache files (for old queries that no longer exist)
• Zero net deletions from the EE cache set

git diff origin/main --stat backend/.sqlx/