Query-Time Media Resolution in SurrealDB: Pushing Transforms into DEFINE FUNCTION

Every API endpoint that returns an entity with media fields has the same problem. You fetch the entity, collect all the media asset IDs, batch-fetch the media records, resolve storage keys to CDN URLs, and stitch the resolved URLs back into the response. Two queries, a pile of post-processing code, and a pattern that gets copy-pasted across every handler.

I eliminated all of it by pushing media resolution into SurrealDB itself using DEFINE FUNCTION.

The N+1 Post-Processing Tax

In my system, agents have four media fields: avatar, listing_media, wizard_intro_media, and credits_icon. Each field stores a record link to a media_asset record.

The old handler pattern looked like this:

let agent = state.dal.agent.get_by_id(&id).await?;
let media_ids = collect_agent_media_ids(std::slice::from_ref(&agent));
let resolved = resolve_media_refs(&state.dal, &base_url, &media_ids).await;
let response = AgentResponse::from_agent(agent, &resolved);

Two database round-trips per request. One for the entity, one for the media assets. For listing endpoints returning N agents, the second query fetched up to 4 * N media assets. The resolve_media_refs function deduplicated IDs before querying, but it was still an extra round-trip with 200+ lines of orchestration code duplicated across seven handler files.

DEFINE FUNCTION: Resolution at Query Time

SurrealDB lets you define reusable functions in the database. I wrote two: one for single media fields, one for localized media (per-locale variants).

DEFINE FUNCTION OVERWRITE fn::resolve_media($ref: any, $base: string) {
    IF $ref == NONE OR $ref == NULL THEN
        RETURN NONE;
    END;
    LET $asset = <record> $ref;
    RETURN {
        id: <string> $asset.id,
        url: string::concat($base, '/', $asset.storage_key),
        media_type: $asset.media_type,
        mime_type: $asset.mime_type,
        width: $asset.width,
        height: $asset.height,
        file_size: $asset.file_size,
        alt_text: $asset.alt_text,
        thumbnail_url: IF $asset.thumbnail_storage_key != NONE
            THEN string::concat($base, '/', $asset.thumbnail_storage_key) END
    };
};

The key is <record> $ref. This follows the record link, dereferencing the media_asset row inline. SurrealDB resolves the join internally.

string::concat($base, '/', $asset.storage_key) builds the CDN URL right in the query. The thumbnail conditionals prevent broken https://cdn.example.com/null URLs for assets without thumbnails.

For localized media (objects with per-locale entries), SurrealDB lacks FOR..IN over object keys in functions. The workaround uses object::entries to convert to key-value pairs, array::map to transform each entry, and object::from_entries to rebuild:

DEFINE FUNCTION OVERWRITE fn::resolve_localized_media($lm: any, $base: string) {
    IF $lm == NONE OR $lm == NULL OR $lm.locales == NONE THEN
        RETURN NONE;
    END;
    LET $entries = object::entries($lm.locales);
    LET $resolved = array::map($entries, |$e| {
        RETURN [$e[0], {
            media: fn::resolve_media($e[1].media_asset_id, $base),
            status: $e[1].status,
            updated_at: $e[1].updated_at
        }];
    });
    RETURN { locales: object::from_entries($resolved) };
};

Composable SQL Projections in Rust

The Rust DAL uses helper functions to build the SQL projection fragments:

pub fn resolve_media_projection(field: &str) -> String {
    format!("fn::resolve_media({field}, $base) AS {field}")
}

pub fn agent_media_projections() -> String {
    [
        resolve_media_projection("avatar"),
        resolve_media_projection("listing_media"),
        resolve_media_projection("wizard_intro_media"),
        resolve_media_projection("credits_icon"),
    ]
    .join(", ")
}

This produces:

LET $base = $base_url;
SELECT *,
  fn::resolve_media(avatar, $base) AS avatar,
  fn::resolve_media(listing_media, $base) AS listing_media,
  fn::resolve_media(wizard_intro_media, $base) AS wizard_intro_media,
  fn::resolve_media(credits_icon, $base) AS credits_icon
FROM agent WHERE workspace_id = $workspace_id

One query. Zero post-processing. SurrealDB follows the record links, builds the full media objects with CDN URLs, and returns the complete AgentResponse shape directly.

The Handler After

Every handler that returns resolved agents now looks like this:

let response = state.dal.agent.get_by_id_resolved(&id).await?;
Ok(Json(ApiResponse::new(response)))

One line. The _resolved suffix methods (get_by_id_resolved, list_with_filters_resolved, list_by_workspace_resolved) use the projection helpers. The old methods stay for the write path where you don't need resolved media.

Handling the Dual Shape

The DbAgent model uses Option<serde_json::Value> for media fields because the same struct handles both shapes:

pub struct DbAgent {
    pub avatar: Option<serde_json::Value>,
    // ...
}

Two From implementations extract different shapes. The write path uses extract_media_id (pulls the string ID). The read path uses extract_resolved_media (deserializes the full object):

fn extract_resolved_media(value: serde_json::Value) -> Option<ResolvedMediaRef> {
    match &value {
        serde_json::Value::Object(map) if map.contains_key("url") => {
            serde_json::from_value(value).ok()
        }
        _ => None,
    }
}

If the value has a url key, it's resolved. If it's a string or record ID struct, it's raw. Clean separation, no runtime flags.

What This Pattern Unlocks

Once your API layer returns resolved responses directly from the database, caching becomes trivial. Redis stores the AgentResponse with resolved media. On cache hit, the handler returns it with zero database queries and zero resolution.

Before this change, caching was awkward because you had to cache the intermediate state and still resolve media on every hit. I wrote about user-configurable cache TTL separately — it builds on top of this.

Metric	Before	After
DB round-trips per request	2	1
Lines of post-processing code	200+ across 7 files	0
Cache-friendly	No (intermediate state)	Yes (final shape)

Not everything can use this approach. Freeform JSON blobs where users embed media_asset:xxx at arbitrary depths have no schema to project against. A JSON-walking resolver still exists for those — but it went from eleven call sites to three.

The pattern is not SurrealDB-specific in concept. PostgreSQL has CREATE FUNCTION. MongoDB has $lookup with $addFields. But SurrealDB's record links and <record> casting make the dereference natural — you're not writing a JOIN, you're following a pointer.

---

Your application layer should not be a data transformation pipeline. Push the transforms into the database, and let your handlers do what they should have been doing all along: nothing.