# MacroDB Database Architecture and Migration Management in the Macro Platform

> Discover MacroDB's PostgreSQL database architecture and how SQLx and Just automate migrations for the Macro Platform. Learn efficient database management.

- Repository: [Macro/macro](https://github.com/macro-inc/macro)
- Tags: architecture
- Published: 2026-08-14

---

**MacroDB is a PostgreSQL relational database accessed through the dedicated `macro_db_client` crate, with migrations managed via SQLx's integrated migration system and automated through Just recipes.**

The Macro platform stores all persistent data—users, projects, documents, chats, email threads, and permissions—in **MacroDB**. This article examines the database architecture and migration workflow as implemented in the [macro-inc/macro](https://github.com/macro-inc/macro) repository, based on source code analysis of the `macro_db_client` crate and related infrastructure.

## MacroDB Core Architecture

### PostgreSQL with SQLx Compile-Time Validation

MacroDB runs on **PostgreSQL**, deployed locally via Docker Compose for development and on managed RDS clusters in production. The database is never accessed directly by services; instead, all interaction flows through the **`macro_db_client`** crate located at `crates/macro_db_client/`.

The crate provides a **typed, compile-time-checked API** using **SQLx** with offline mode enabled (`SQLX_OFFLINE=true`). This guarantees that every query in the codebase matches the actual schema without requiring a live database during builds.

Key architectural components in [`crates/macro_db_client/src/lib.rs`](https://github.com/macro-inc/macro/blob/main/crates/macro_db_client/src/lib.rs):

- **`DbPool`** — A `sqlx::Pool<Postgres>` exported for service use, built once at startup with `DbPool::new().await`
- **Query macros** — `query!`, `query_as!`, and related macros provide schema-level safety
- **Environment integration** — Credentials are loaded exclusively through `macro_env_var` macros, never via `std::env::var`

### Connection Management Pattern

Services obtain the pool through Axum's `State` system:

```rust
// Pool creation at service startup
let db = DbPool::new().await?;

// Pool passed to handlers via Axum State
let app = Router::new()
    .route("/users", get(get_user_handler))
    .with_state(db);

```

This centralizes connection pooling and enables graceful shutdown handling.

### SQLx Offline Cache

The repository maintains a `.sqlx/` directory under `crates/macro_db_client/.sqlx/` containing cached query metadata. This allows the project to compile in CI environments without database connectivity. After any schema change, developers regenerate the cache using `just prepare_db`.

## Migration Management Workflow

MacroDB migrations follow SQLx's standard conventions, fully integrated into the Macro development workflow through Just recipes.

### Creating and Applying Migrations

Migrations are timestamped SQL files stored in `crates/macro_db_client/migrations/`. Each file contains paired **up** and **down** statements:

```bash

# Create a new migration

cd crates/macro_db_client
sqlx migrate add add_user_created_at

# Result: crates/macro_db_client/migrations/YYYYMMDDHHMMSS_add_user_created_at.sql

```

The standard development workflow:

1. **Write migration** — Edit the generated file with `CREATE TABLE`/`DROP TABLE` or analogous statements
2. **Test locally** — `just test` spins up PostgreSQL and applies migrations via `just migrate_db`
3. **Update offline cache** — `just prepare_db` regenerates `.sqlx` metadata for all crates
4. **Deploy** — CI runs `just migrate_db` against production RDS before service startup

### Example Migration File

From [`crates/macro_db_client/migrations/20260728143113_add_user_created_at.sql`](https://github.com/macro-inc/macro/blob/main/crates/macro_db_client/migrations/20260728143113_add_user_created_at.sql):

```sql
-- Up
ALTER TABLE "User" ADD COLUMN "created_at" TIMESTAMP WITH TIME ZONE DEFAULT now();

-- Down
ALTER TABLE "User" DROP COLUMN "created_at";

```

This pattern appears throughout the migration directory, with files like [`20260702014623_create_favorites_table.sql`](https://github.com/macro-inc/macro/blob/main/20260702014623_create_favorites_table.sql) introducing the `favorites` table and [`20260715154212_add_document_live_id_owner_index.sql`](https://github.com/macro-inc/macro/blob/main/20260715154212_add_document_live_id_owner_index.sql) adding performance indexes.

## Using the Database Client

Services interact with MacroDB through typed functions exported by `macro_db_client`. Here's a complete example:

```rust
use macro_db_client::{DbPool, get_user_by_email};

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    // Build the connection pool (environment variables via macro_env_var)
    let db = DbPool::new().await?;

    // Fetch a user by email
    let user = get_user_by_email(&db, "alice@example.com").await?;
    println!("Found user: {} (id={})", user.name, user.id);

    Ok(())
}

```

The query implementation resides in [`crates/macro_db_client/src/user/get/get_user_by_email.rs`](https://github.com/macro-inc/macro/blob/main/crates/macro_db_client/src/user/get/get_user_by_email.rs), with similar patterns for all CRUD operations across the schema.

## Key Files and Commands Reference

| Path | Purpose |
|------|---------|
| [`crates/macro_db_client/src/lib.rs`](https://github.com/macro-inc/macro/blob/main/crates/macro_db_client/src/lib.rs) | `DbPool` definition and connection setup |
| `crates/macro_db_client/migrations/` | All timestamped migration SQL files |
| `crates/macro_db_client/.sqlx/` | Offline query cache for compile-time validation |
| `justfile` (workspace root) | `migrate_db` and `prepare_db` recipes |
| [`docs/STYLE_GUIDE.md`](https://github.com/macro-inc/macro/blob/main/docs/STYLE_GUIDE.md) | DB credential loading requirements |

**Essential commands:**

```bash
sqlx migrate add <name>     # Create new migration

just migrate_db             # Apply pending migrations

just prepare_db             # Regenerate SQLx offline cache

just test                   # Full test run with ephemeral database

```

## Summary

- **MacroDB** is PostgreSQL, accessed exclusively through the `macro_db_client` crate using SQLx with compile-time query validation
- **Offline mode** (`SQLX_OFFLINE=true`) enables builds without live database connectivity via cached metadata in `.sqlx/`
- **Migrations** are timestamped SQL files in `crates/macro_db_client/migrations/`, managed through standard SQLx commands wrapped by Just recipes
- **Environment security** mandates `macro_env_var` macros for all credential access, keeping secrets out of source code
- **Deployment safety** requires `just migrate_db` to run against production RDS before service binaries start

## Frequently Asked Questions

### How does MacroDB ensure queries match the schema at compile time?

SQLx's `query!` and `query_as!` macros connect to the database during development (or use cached metadata from `.sqlx/` in CI) to verify that each query's columns, types, and tables exist. If a query references a non-existent column or mismatched type, compilation fails. The `just prepare_db` command updates this cache after migrations.

### What happens if a migration fails in production?

The `sqlx migrate run` command used in `just migrate_db` applies migrations transactionally. If any migration fails, the transaction rolls back and the deployment halts before services start. This prevents partial schema changes. CI pipelines should test migrations against staging environments that mirror production.

### Can I build the Macro project without PostgreSQL installed?

Yes. With `SQLX_OFFLINE=true` (set by default in the repository), the compiler uses the checked-in `.sqlx/` cache instead of connecting to a database. You only need PostgreSQL when: (1) writing new queries, (2) adding migrations, or (3) running `just prepare_db` to update the cache.

### Why doesn't `macro_db_client` use an ORM like Diesel or SeaORM?

The Macro team chose **SQLx** for its zero-cost compile-time checking without the abstraction overhead of traditional ORMs. The `query_as!` macro maps rows to Rust structs with full type safety, while raw SQL provides complete control over query plans and PostgreSQL-specific features. This matches the project's preference for explicit, inspectable database access.