| name | sqlx-query |
| description | Write a compile-time-checked SQLx query for the vixen-rs Postgres database using query!/query_as! macros, keep the .sqlx/ offline cache in sync, and avoid N+1 patterns. Use when adding or modifying a DB query, struct, or anywhere `sqlx::query` appears. |
SQLx Query (Vixen server)
Prefer macros
sqlx::query! — untyped rows, ad-hoc.sqlx::query_as!(Type, "...") — returns Type directly, avoids manual row.get().sqlx::query_scalar! — single column, single value.
Compile-time checking needs either a live DB (DATABASE_URL) or the offline cache in server/.sqlx/.
Offline cache
CI runs with SQLX_OFFLINE=true. If .sqlx/ is stale, the build fails. After changing any query:
cd server && cargo sqlx prepare -- --all-targets
git add .sqlx
Or just run /db-migrate — it also refreshes .sqlx/ when migrations change.
Write queries as parameterized SQL
- Never
format!() user input into the SQL string. Use $1, $2, ... placeholders.
- Type annotations when the macro can't infer:
SELECT id AS "id!: Uuid" FROM ....
- For nullable columns:
SELECT col AS "col?" FROM ... yields Option<T>.
- For arrays of Telegram IDs:
$1::bigint[] and pass &Vec<i64>.
Vixen specifics
- Telegram IDs: bind as
i64, never narrow to i32.
- xxh3 spam hashes: bind as
i64 (Postgres has no unsigned 64-bit type — xxh3-64 fits in BIGINT because Postgres BIGINT is signed 64-bit; reinterpret bits via u64::cast_signed() / i64::cast_unsigned() consistently).
- Per-chat config: when reading JSONB settings, use
SELECT settings->>'clown_chance' AS "clown_chance: i16" with explicit type annotation.
Transactions
Use sqlx::Transaction for multi-statement writes:
let mut tx = pool.begin().await?;
sqlx::query!("DELETE FROM captcha_challenges WHERE id = $1", id).execute(&mut *tx).await?;
sqlx::query!("INSERT INTO verified_users (chat_id, user_id) VALUES ($1, $2)", chat, user).execute(&mut *tx).await?;
tx.commit().await?;
Passing &mut *tx to the executor slot is the idiomatic way — &mut tx won't compile.
Mandatory transactions in vixen:
- Captcha solve: delete challenge + insert verified_user must be atomic.
- Per-chat config update:
SELECT ... FOR UPDATE then UPDATE.
- Moderation action: insert action ledger row + side-effect (ban call etc.) — at minimum the ledger insert should be guarded by a uniqueness key so retries are safe.
Avoid N+1
- Fetch parents + children in one query with
JOIN + json_agg or two queries + in-memory merge.
- Do not loop with
for id in ids { query!("SELECT ... WHERE id = $1", id) }.
- Use
= ANY($1::bigint[]) for batched Telegram-ID lookups.
Typical pitfalls
- Integer overflow:
COUNT(*) returns i64; cast or store appropriately.
RETURNING on INSERT is cheaper than a follow-up SELECT.- Use
.fetch_optional() for "may or may not exist", then .ok_or(AppError::NotFound)?.
.fetch_all() + then .len() is wasteful — use query_scalar! with COUNT(*).- Don't put raw query strings in error messages (leaks schema).
Performance patterns
- Avoid
SELECT * in macros — name columns explicitly so the result struct matches and fetches stay lean.
- Batch reads via
WHERE id = ANY($1::bigint[]) to replace N+1 lookups (e.g., resolving multiple verified_users in one query).
- Keyset pagination for moderation-action lists:
WHERE (created_at, id) < ($1::timestamptz, $2::uuid) ORDER BY created_at DESC, id DESC LIMIT 50. Skip OFFSET — it gets slow on big tables.
- Idempotent inserts to the moderation ledger:
sqlx::query!(
"INSERT INTO moderation_actions (chat_id, target_user_id, action, message_id, ...) \
VALUES ($1, $2, $3, $4, ...) \
ON CONFLICT (chat_id, target_user_id, action, message_id) DO NOTHING",
chat_id, target_user_id, action, message_id, ...
).execute(&mut *tx).await?;
Related
- Error mapping: see
rust-error-handling skill.
- New tables/columns: see
add-migration skill.
- Schema reference: server/docs/database.md.
