How to Add a SQLx Database Migration and Update the `.sqlx` Cache Using the `prepare_db` Workflow

Use cargo sqlx migrate add from the relevant database crate, edit the generated migration file, then run just prepare_db from the repository root to regenerate the compile-time query cache.

Adding a new migration to one of the SQLx-backed crates in the Macro monorepo follows a standardized workflow defined in the repository's documentation. This process ensures migrations are created with correct timestamps, schema changes are properly versioned, and the .sqlx query cache stays synchronized for compile-time query verification.

Identify the Target Database Crate

Each database-backed crate in the macro-inc/macro repository maintains its own migrations/ directory. Common examples include:

• crates/macro_db_client
• crates/comms_db_client
• crates/email_db_client

As documented in CLAUDE.md, you must "run sqlx migrate add from the relevant database crate." Running the command from the wrong directory places the migration in the incorrect location, breaking the workspace's migration management.

Install sqlx-cli and Create the Migration

First, ensure the SQLx CLI tool is available:

cargo install sqlx-cli

Navigate to the root of your target crate and create the migration:

cd crates/macro_db_client
cargo sqlx migrate add <snake_case_name>

Replace <snake_case_name> with a descriptive name such as add_user_preferences or create_user_sessions_table.

The .pi/skills/sqlx-migration/SKILL.md file explicitly warns: "Do not manually add timestamps." The cargo sqlx migrate add command automatically generates a timestamped filename (e.g., 20240325143515_add_user_preferences.sql), guaranteeing proper ordering and consistent naming conventions.

Add Your Schema Changes

Edit the generated migration file in the crate's migrations/ directory. The file structure requires:

-- Up migration
CREATE TABLE user_preferences (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id),
    theme VARCHAR(50) DEFAULT 'light',
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Down migration (optional but recommended)
DROP TABLE IF EXISTS user_preferences;

The UP statements apply the schema change; the DOWN statements enable rollbacks. Migration examples throughout the repository—such as services/document_text_extractor/migrations/20240325143515_basic_schema_for_testing.sql—demonstrate this pattern.

Set Up the Test Environment

Before running migrations or regenerating the query cache, prepare your local environment:

just setup_test_envs

This command, referenced in CONTRIBUTING.md and multiple service READMEs, creates the necessary .env files and makes a local Postgres instance available for testing.

Regenerate the SQLx Query Cache with prepare_db

After creating or modifying any migration, you must update the compile-time query cache. From the repository root:

just prepare_db

This command is documented in docs/STYLE_GUIDE.md (section CS-09) and throughout CLAUDE.md. Under the hood, just prepare_db runs cargo sqlx prepare for every SQLx-backed crate in the workspace, rebuilding the .sqlx/ directories that contain query metadata.

Critical: The CLAUDE.md file stresses that you should never edit .sqlx files manually. Always use just prepare_db to regenerate them atomically after schema changes.

Verify and Commit Your Changes

Run the test suite to confirm everything works:

cargo test

# or

just test

This verifies that:

• The new migration applies correctly against a live test database
• All sqlx::query! and query_as! macros compile with the updated schema

Before committing, run quality checks:

just clippy
cargo fmt

Commit both the new migration file and any regenerated .sqlx/ query files together.

Summary

• Locate the correct crate — each database client crate has its own migrations/ directory
• Use cargo sqlx migrate add — never manually timestamp migration files
• Write both UP and DOWN statements — enable proper rollbacks
• Run just setup_test_envs — once per session to prepare local Postgres
• Execute just prepare_db — mandatory after any schema-changing migration
• Test and commit — include .sqlx/ cache updates with your migration

Frequently Asked Questions

What happens if I forget to run just prepare_db after adding a migration?

Your Rust code using sqlx::query! or query_as! will fail to compile with "no cached data for query" errors. The .sqlx cache must stay synchronized with the actual database schema for SQLx's compile-time verification to work.

Can I create multiple migrations before running just prepare_db?

Yes, though it's safer to run just prepare_db after each migration to catch issues early. The command processes all pending migrations and updates query caches for the entire workspace.

Where does just prepare_db look for database crates?

The justfile at the repository root defines the prepare_db recipe, which iterates through known SQLx-backed crates and executes cargo sqlx prepare in each. You don't need to specify individual crates.

Why does the macro-inc/macro repository commit .sqlx/ files to version control?

The .sqlx/ directory contains JSON metadata for compile-time query verification. CI builds and fresh clones require these files to compile without a live database connection. docs/STYLE_GUIDE.md (CS-09) mandates keeping this cache updated and committed.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}