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

> Easily add new SQLx database migrations and update the compile-time cache with the prepare_db workflow. Learn how to run `cargo sqlx migrate add` and `just prepare_db`.

- Repository: [Macro/macro](https://github.com/macro-inc/macro)
- Tags: how-to-guide
- Published: 2026-08-20

---

**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`](https://github.com/macro-inc/macro/blob/main/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:

```bash
cargo install sqlx-cli

```

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

```bash
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`](https://github.com/macro-inc/macro/blob/main/.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`](https://github.com/macro-inc/macro/blob/main/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:

```sql
-- 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`](https://github.com/macro-inc/macro/blob/main/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:

```bash
just setup_test_envs

```

This command, referenced in [`CONTRIBUTING.md`](https://github.com/macro-inc/macro/blob/main/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:

```bash
just prepare_db

```

This command is documented in [`docs/STYLE_GUIDE.md`](https://github.com/macro-inc/macro/blob/main/docs/STYLE_GUIDE.md) (section CS-09) and throughout [`CLAUDE.md`](https://github.com/macro-inc/macro/blob/main/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`](https://github.com/macro-inc/macro/blob/main/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:

```bash
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:

```bash
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`](https://github.com/macro-inc/macro/blob/main/docs/STYLE_GUIDE.md) (CS-09) mandates keeping this cache updated and committed.