Enrich API responses at the gateway layer using Postgres, MongoDB, and Redis workflow nodes

Enlarge

Backend services rarely return everything a consumer needs in one call. A payments API returns a transaction and its amount. The consumer also needs the account holder's tier, credit limit, and feature flags — all sitting in a Postgres database that the payments service deliberately does not own. A product catalog API returns SKU metadata. The consumer also needs real-time inventory from MongoDB. These are not design failures. They are deliberate boundaries. The problem is what happens next: teams build adapter services, BFF layers, or patch the backend to fetch the extra data and bolt it onto the response. Every approach adds a new thing to deploy, monitor, and maintain.

API gateway response enrichment solves this at the infrastructure layer. The Zerq workflow builder includes five native database nodes — postgres_node, mongodb_node, sqlserver_node, oracle_node, and redis_node — that run inline in the request path. The gateway fetches the backend response, queries the database, merges the result, and returns the enriched payload. No adapter service. No Lambda function. No Lua plugin.

Why the common workarounds fail

Teams reach for an adapter service first. It is familiar: a small Go or Node service that calls the backend, queries the database, and returns the merged response. The problem is operational. Every adapter service needs a Docker image, a deployment manifest, health checks, alerts, secrets for the database credential, and someone responsible for keeping it current. A modest platform can accumulate a dozen of these before anyone acknowledges the pattern.

Lambda or serverless functions are the cloud-native version of the same problem. The enrichment logic lives in a function that the gateway invokes synchronously. You get cold start latency on top of the query latency, IAM configuration for every database the function needs to reach, and a separate deployment pipeline. AWS API Gateway supports Lambda integration but has no native concept of a database query in the request path.

Kong's plugin model handles this with Lua plugins. Writing a Lua plugin that opens a Postgres connection, executes a parameterized query, and merges the result into the response body is possible but not simple. The plugin needs connection pooling logic, error handling that does not crash the gateway worker, and separate deployment as a custom plugin. Kong Enterprise's pre-built plugins do not include generic database enrichment.

MuleSoft can query databases inside a flow using the Database Connector, but the Mule runtime is heavy and Anypoint Platform runs in MuleSoft's hosted control plane by default. Running it on-premises requires a separate licensed component. The operational footprint is substantial for what amounts to a SELECT query.

The Zerq workflow builder treats database queries as first-class workflow nodes. The credential lives in the gateway's encrypted credential store. The query runs in the request path. The output is available to downstream nodes via the $json expression context. No separate deployment, no plugin authoring, no Lambda.

How database enrichment works in Zerq workflows

Every proxy endpoint in Zerq can have a workflow attached to it. The workflow is a directed graph of nodes connected by edges. The entry node is always http_trigger. The exit node for API traffic is response_node. Between them, you chain any combination of the available node types.

The expression system is what makes database nodes practical. Every node outputs its result into the $json context keyed by the node's ID. A downstream node can reference any upstream output using $json['node_id'].field. This means the database query can use data from the incoming request — a path parameter, a field from the backend response, a value from a request header — as a query parameter. The result flows forward to the next node exactly the same way.

Database credentials are stored separately in the Zerq credential store. The node references the credential by ID; the connection string or URI never appears in the workflow definition. Credentials support environment-variable backing so the value is never stored in plain text in the database.

Step-by-step: enrich a payments API response with customer tier from Postgres

This walkthrough adds a Postgres lookup to a payments proxy endpoint. The payments backend returns a transaction object with a customer_id field. The workflow queries a customer_profiles table to add the customer's tier and credit_limit to the response.

Step 1: Open the workflow builder

1. In the management console, go to Collections and open the collection that contains your payments proxy.
2. Click into the proxy endpoint (for example, GET /transactions/{transactionId}).
3. Click Edit Workflow. The canvas opens with a default http_trigger → response_node graph.

Step 2: Add a proxy node

1. Click Add node in the toolbar and place a proxy_node on the canvas between the trigger and the response node.
2. Connect: http_trigger output → proxy_node input, proxy_node success output → (leave disconnected for now).
3. In the proxy_node configuration panel, set the path to pass through the original request:
   • Path: {{ $json['http_trigger'].request.path }}
   • Method: {{ $json['http_trigger'].request.method }}
4. Leave headers empty to inherit the incoming headers.

The proxy_node calls the assigned collection backend and makes the response available at $json['proxy_node'].response.body.

Step 3: Add a Postgres credential

Before adding the postgres_node, create the credential it will use:

1. Go to Credentials → New Credential.
2. Select type postgres.
3. Set postgresql_connection_string to your database URL. To avoid storing the connection string in the gateway database, set the field using an environment variable reference (for example {{ env.PG_CUSTOMERS_URI }}).
4. Save the credential and note its name — you will select it in the next step.

Step 4: Add a postgres node

1. Click Add node and place a postgres_node after the proxy node.
2. Connect: proxy_node success → postgres_node input.
3. Configure the node:

{
  "id": "get_customer",
  "type": "postgres_node",
  "config": {
    "credentials_id": "pg-customers",
    "operation": "query",
    "database": "platform",
    "timeout_ms": 3000
  },
  "inputs": {
    "query": "SELECT tier, credit_limit, feature_flags FROM customer_profiles WHERE customer_id = $1",
    "params": ["{{ $json['proxy_node'].response.body.customer_id }}"]
  }
}

The params array maps positionally to $1, $2, and so on in the SQL statement. Using parameterized queries prevents injection — never build SQL with string concatenation in the expression.

A successful query produces $json['get_customer'].result.rows as an array. Each element is an object with the column names as keys.

Step 5: Add a set node to merge the payloads

1. Click Add node and place a set_node after the postgres node.
2. Connect: get_customer success → set_node input.
3. Configure the assignments to merge the transaction response with the customer data:

{
  "id": "build_response",
  "type": "set_node",
  "config": {
    "assignments": {
      "transaction": "{{ $json['proxy_node'].response.body }}",
      "customer": "{{ $json['get_customer'].result.rows[0] }}"
    }
  }
}

The set_node output is available at $json['build_response'].result, with transaction and customer as top-level keys.

Step 6: Return the enriched response

1. Connect: build_response output → response_node input.
2. Configure the response_node:

{
  "id": "response_node",
  "type": "response_node",
  "config": {
    "status": 200,
    "headers": { "Content-Type": "application/json" },
    "body": "{{ JSON.stringify($json['build_response'].result) }}"
  }
}

Step 7: Handle the error branch

The postgres_node emits an error output when the query fails. Connect this to a separate response_node with a 503 status and an error body. Never leave the error branch disconnected — a disconnected error branch causes the workflow to stall.

{
  "id": "db_error_response",
  "type": "response_node",
  "config": {
    "status": 503,
    "headers": { "Content-Type": "application/json" },
    "body": "{\"error\": \"upstream_data_unavailable\"}"
  }
}

Step 8: Test, save, and enable

1. Click Execute Workflow in the toolbar. Set a sample transactionId in the test panel.
2. Each node shows its output inline after execution. Confirm get_customer returns the expected rows and build_response produces the merged object.
3. Click Save to persist the workflow definition.
4. Click Enable Workflow to activate it for live traffic. The proxy routes through the workflow engine; disable returns to simple pass-through.

Adding a cache layer with Redis

For high-traffic endpoints, running a Postgres query on every request adds latency and load. A redis_node before the database node short-circuits the query for repeated lookups.

Insert a redis_node between the proxy node and the postgres node:

{
  "id": "cache_check",
  "type": "redis_node",
  "config": {
    "credentials_id": "redis-cache",
    "operation": "get",
    "timeout_ms": 500
  },
  "inputs": {
    "key": "customer:{{ $json['proxy_node'].response.body.customer_id }}"
  }
}

Connect the cache_check success branch to a condition_node that checks whether the cached value exists:

{
  "id": "cache_hit",
  "type": "condition_node",
  "config": {
    "condition": "$json['cache_check'] !== null && $json['cache_check'] !== undefined"
  }
}

• True branch: skip the postgres query, use $json['cache_check'] directly in the set_node.
• False branch: proceed to postgres_node, then add a redis_node with operation: set to populate the cache with a TTL:

{
  "id": "cache_write",
  "type": "redis_node",
  "config": {
    "credentials_id": "redis-cache",
    "operation": "set",
    "timeout_ms": 500
  },
  "inputs": {
    "key": "customer:{{ $json['proxy_node'].response.body.customer_id }}",
    "value": "{{ JSON.stringify($json['get_customer'].result.rows[0]) }}",
    "ttl_seconds": 300
  }
}

The result is a cache-first enrichment pattern entirely within the workflow, with no application code written.

MongoDB enrichment for document-oriented data

The pattern for MongoDB is the same structure, with a mongodb_node in place of the postgres_node:

{
  "id": "get_inventory",
  "type": "mongodb_node",
  "config": {
    "credentials_id": "mongo-catalog",
    "operation": "findOne",
    "collection": "inventory",
    "database": "catalog"
  },
  "inputs": {
    "filter": { "sku": "{{ $json['proxy_node'].response.body.sku }}" }
  }
}

The findOne result is available at $json['get_inventory'].document. For multi-document results, use find and access $json['get_inventory'].documents as an array.

What this looks like in practice

A fintech platform running open banking APIs has a transactions endpoint that returns raw transaction data from a payments processor. Product managers want to include the account holder's product tier and consent status in every response so downstream apps can render personalised views without an extra call.

Before this change, a small Node.js enrichment service sat between the gateway and the client. It called the payments processor, then queried an RDS Postgres instance, merged the responses, and returned the result. The service had its own CI pipeline, its own Docker image, its own alerting rules, and its own on-call runbook.

After configuring the workflow in Zerq, the enrichment service was decommissioned. The postgres_node uses the same pg-customers credential already stored in the gateway for other workflows. The workflow graph is visible and editable by any platform engineer with modifier access. Every execution is logged in the audit trail — timestamp, actor, request ID, outcome — giving the compliance team full traceability for every enriched response without instrumenting the removed service.

The Redis cache layer reduced average response latency on the transactions endpoint from 120ms to 38ms for repeat customers, with no changes to the payments backend and no new infrastructure beyond the Redis instance that was already provisioned for rate limit state.

What you get from the workflow approach

Enrichment logic that lives in the gateway workflow is visible, versioned, and testable without deploying anything. The credential is encrypted and audited. The error branches force you to handle the failure case explicitly — if Postgres is unavailable, the gateway returns a defined error shape rather than a 500 from an unhandled exception in a sidecar service. And because the workflow is part of the same platform as rate limiting, authentication, and access control, the enrichment logic inherits all the same security and observability controls without additional configuration.

Zerq is an enterprise API gateway built for regulated industries — one platform for API management, AI agent access, compliance audit, and developer portal, running entirely in your own infrastructure. See how it works or request a demo to walk through your specific requirements.