How to Build a Unified API Layer Across MySQL, Postgres & MongoDB with DreamFactory

This guide shows how to create a single API layer that joins data across MySQL, Postgres, and MongoDB using a federated query engine with an API gateway pattern. You will implement a hands-on build, see code samples, and review performance, security, and governance steps. DreamFactory is a secure, self-hosted enterprise data access platform that provides governed API access to any data source, connecting enterprise applications and on-prem LLMs with role-based access and identity passthrough.

What is a Unified API Layer for Heterogeneous Databases?

A unified API layer exposes consistent endpoints over disparate data sources so teams can query, join, and govern data through one contract. In this model, a federation engine performs cross-store joins while DreamFactory enforces access, caching, and auditing. The result is a stable interface for web, mobile, and on-prem LLM workloads. DreamFactory auto-generates REST endpoints for SQL objects and views, adds role-based controls, and centralizes observability without requiring application teams to learn each backend.

Why a Unified Layer Matters in 2026

Modern stacks mix relational and document stores, which complicates joins, governance, and identity mapping. A unified layer reduces custom glue code and shortens delivery time for internal tools and customer experiences. DreamFactory lets organizations standardize authentication, rate limits, and data masking in one place while a federation tier handles cross-database joins. This approach supports zero trust principles, simplifies LLM retrieval flows, and helps teams evolve backends without breaking client contracts.

Common Challenges and How Platforms Solve Them

Heterogeneous joins, inconsistent schemas, and varying query semantics often degrade performance and security. DreamFactory addresses governance with role-based access, field-level controls, and identity passthrough, while a federation tier performs pushdown and consolidation. Together, they reduce N+1 request patterns, centralize observability, and avoid brittle point integrations. The pattern separates concerns so DreamFactory focuses on secure delivery and the federation engine focuses on cross-source query planning and optimization.

Key Problems Encountered

• Cross-store joins: MySQL, Postgres, and MongoDB differ in types and operators.
• Identity and tenancy: Enforcing row filters across sources is difficult.
• Performance: Joins over networks can be slow and unstable.
• Governance: Inconsistent access, logging, and masking create risk.
• Change management: Backend changes break clients without an abstraction.

DreamFactory solves governance, identity, and API lifecycle, while the federation tier solves query planning and data movement. This division lets teams implement least privilege, pushdown filtering, and caching without locking into a single database product.

What to Look For in a Platform for This Use Case

Teams need a platform that unifies authentication, authorization, audit, and schema exposure while remaining neutral to backend choices. DreamFactory provides auto-generated APIs for SQL schemas and views, OpenAPI documentation, role-based access, request policies, and response transforms. A complementary federation engine should support pushdown, cost-based planning, and stable connectors to each source. The combined stack reduces delivery risk, improves performance, and supports clear ownership boundaries for data and platform teams.

Must-Have Features

• Secure, self-hosted control plane with RBAC and SSO
• Schema introspection with API generation for views and tables
• Identity passthrough and tenant-aware filtering
• Request transforms, caching, and rate limits
• Auditing, metrics, and error budgets for APIs

DreamFactory meets these needs by generating governed endpoints from your federation schema, enforcing role policies, and providing consistent documentation. The platform adds API keys, OAuth, and per-role controls, while exposing only approved views. This keeps client contracts stable and ensures operational visibility.

Architecture Overview

DreamFactory acts as the secure API layer. A Postgres-based federation schema uses foreign data wrappers to map MySQL and MongoDB. Views or materialized views express cross-store joins. DreamFactory exposes those views as REST endpoints with role-based controls and identity passthrough.

Clients / Tools / LLMs         |         v   DreamFactory API Layer  <— RBAC, identity passthrough, caching, auditing         |         v  Postgres Federation Schema  <— views, materialized views, RLS    |                 \    v                  v MySQL CRM        MongoDB Events 

DreamFactory may also expose direct endpoints to source systems for operational tasks, while the unified endpoints are backed by the federation schema. This gives teams flexibility with minimal duplication.

Hands-On Build: Federated Query Engine + DreamFactory

This section shows a practical, end-to-end build using Postgres as the federation engine. DreamFactory will connect to the Postgres federation schema and publish governed endpoints for internal and external consumers.

Step 1: Prepare the Federation Database

Use a managed or self-hosted Postgres instance with extensions for foreign data wrappers. DreamFactory will connect to this database as a read or read-write service, depending on your policy. Ensure network routes from Postgres to MySQL and MongoDB are secured with TLS, and that credentials are scoped with least privilege.

-- In Postgres, enable required FDWs CREATE EXTENSION IF NOT EXISTS mysql_fdw; CREATE EXTENSION IF NOT EXISTS mongo_fdw; 

Step 2: Register MySQL as a Foreign Server

Model the source schema you plan to expose. Start with customer data commonly needed by internal tools and external apps. DreamFactory will later expose only approved views, not raw tables, which simplifies governance and change control.

CREATE SERVER mysql_crm   FOREIGN DATA WRAPPER mysql_fdw   OPTIONS (host 'mysql.host', port '3306');  CREATE USER MAPPING FOR postgres   SERVER mysql_crm   OPTIONS (username 'crm_reader', password '********');  CREATE SCHEMA IF NOT EXISTS mysql_map; IMPORT FOREIGN SCHEMA crm   LIMIT TO (customers)   FROM SERVER mysql_crm INTO mysql_map; 

Step 3: Register MongoDB as a Foreign Server

Flatten the fields you join on and the attributes clients query most. You can retain a jsonb column for less-structured attributes. DreamFactory will expose the final views that contain these columns with consistent naming.

CREATE SERVER mongo_analytics   FOREIGN DATA WRAPPER mongo_fdw   OPTIONS (address 'mongo.host', port '27017');  CREATE USER MAPPING FOR postgres   SERVER mongo_analytics   OPTIONS (username 'events_reader', password '********');  CREATE SCHEMA IF NOT EXISTS mongo_map; CREATE FOREIGN TABLE mongo_map.events (   _id text,   user_id text,   event_type text,   ts timestamp,   payload jsonb ) SERVER mongo_analytics   OPTIONS (db 'analytics', collection 'events'); 

Step 4: Use Native Postgres Tables as Needed

If orders already live in Postgres, reference them directly. If not, register another FDW. DreamFactory will not need to know where each table lives because it will connect to the federation schema only.

CREATE SCHEMA IF NOT EXISTS local_app; -- Example native table CREATE TABLE IF NOT EXISTS local_app.orders (   order_id bigint primary key,   customer_id bigint,   total_amount numeric,   created_at timestamp ); 

Step 5: Build a Unified View for Cross-Store Joins

Define a stable contract by joining customer, order, and event data. Keep field names consumer friendly. DreamFactory will expose this as a read-only endpoint to external clients and a broader endpoint to internal tools.

CREATE SCHEMA IF NOT EXISTS federation;  CREATE OR REPLACE VIEW federation.unified_customer_activity AS SELECT   c.customer_id,   c.email,   o.order_id,   o.total_amount,   e.event_type,   e.ts AS event_ts FROM mysql_map.customers c LEFT JOIN local_app.orders o   ON o.customer_id = c.customer_id LEFT JOIN mongo_map.events e   ON e.user_id = c.customer_id::text; 

Step 6: Optional Materialized View for Performance

Networked joins can be expensive. Materialized views reduce latency for read-heavy scenarios. DreamFactory can expose the materialized view to customers while internal tools may query the live view for freshness.

CREATE MATERIALIZED VIEW federation.unified_customer_activity_mv AS SELECT * FROM federation.unified_customer_activity;  CREATE INDEX ON federation.unified_customer_activity_mv (customer_id, event_ts); -- Refresh window scheduled by cron or a scheduler -- REFRESH MATERIALIZED VIEW CONCURRENTLY federation.unified_customer_activity_mv; 

Step 7: Enforce Row-Level Security and Tenancy

Apply row filters to protect tenants and align with identity passthrough. DreamFactory forwards identity context, which you map to Postgres session variables or lookup keys. This ensures consistent authorization across all queries.

ALTER TABLE local_app.orders ENABLE ROW LEVEL SECURITY;  CREATE POLICY tenant_isolation ON local_app.orders USING (current_setting('request.jwt.tenant_id', true)::text = tenant_id::text);  -- Set the variable at connection time from DreamFactory claims mapping -- SELECT set_config('request.jwt.tenant_id', :tenant_id, false); 

Step 8: Connect DreamFactory to the Federation Schema

Create a PostgreSQL service in DreamFactory that points to the federation database. Restrict exposure to the federation schema and selected views. DreamFactory will generate REST endpoints, OpenAPI documentation, and apply role-based access for each route.

Example service name: federation. Example base path: /api/v2/federation. Use system environment variables for credentials and enforce TLS to the database.

Step 9: Define Roles, Identity Passthrough, and Masking

Create separate roles for internal tools and customer-facing apps. In DreamFactory, restrict methods, set field-level filters, and use lookup keys to inject tenant or user identifiers into queries. Mask emails or other PII for external roles. This ensures least privilege while keeping one endpoint surface.

Step 10: Query the Unified API from Clients

Use fields, filter, limit, and order parameters for efficient access. DreamFactory will translate the request to SQL on the federation view and return JSON. Add caching and ETags to reduce latency. The same endpoint can back analytics dashboards, web clients, and on-prem LLM retrieval.

curl -s \   -H "X-DreamFactory-API-Key: <api_key>" \   -H "Authorization: Bearer <jwt>" \   "https://api.yourdomain.com/api/v2/federation/_table/unified_customer_activity_mv\   ?fields=customer_id,email,total_amount,event_type,event_ts\   &filter=customer_id=123\   &limit=50&order=event_ts%20DESC" 

Advanced Patterns and Industry Examples

Enterprises often separate internal and external contracts while sharing the same federation core. DreamFactory exposes internal endpoints with broader fields and methods, and public endpoints with masked PII and tighter limits. Retail teams join CRM in MySQL, order data in Postgres, and clickstream in MongoDB for lifetime value. Financial services teams apply strict RLS with identity passthrough. Healthcare teams use materialized views for predictable latency and audited access.

• Internal tools: Expanded fields and write operations
• External apps: Masked PII and read-only access
• LLM retrieval: Read-only, least privilege, narrow fields
• Data products: Stable, versioned views for partner APIs

DreamFactory provides the governance layer that keeps these patterns consistent.

Best Practices and Expert Tips

• Model joins in views rather than in client code to stabilize contracts with DreamFactory.
• Start with read-only materialized views for customer traffic and review refresh schedules.
• Use narrow fields and pagination parameters to reduce payload sizes and improve latency.
• Apply row-level policies and claim mapping early to avoid authorization drift.
• Add request caching and rate limits in DreamFactory for burst protection.
• Monitor query plans in Postgres and enable pushdown where supported by FDWs.

Performance Considerations

DreamFactory sits on the hot path, so enforce partial response patterns with fields, limit, and order. Use materialized views for cross-store joins where pushdown is limited. Consider PgBouncer for connection pooling. Keep indexes aligned to join keys and common predicates. Use prepared statements and bind parameters where appropriate. Cache common responses and set conservative limits for public roles. Measure with realistic payloads and record P95 and P99 latencies before exposing endpoints broadly.

Security and Governance Checklist

DreamFactory centralizes authentication, authorization, and audit across endpoints. Enforce TLS everywhere and rotate credentials through environment variables or vault integrations. Use per-role policies, IP allow lists, and secrets scoping. Map identity claims to tenancy filters in Postgres. Turn on request logging and retain audit trails for compliance. For external apps, disable write methods, mask PII, and set strict rate limits. Review OpenAPI docs and approve schema changes through change control.

How DreamFactory Simplifies the Process

DreamFactory generates governed REST APIs over your federation schema in minutes, not weeks of custom code. The platform centralizes authentication, role-based access, caching, transforms, and auditing so teams can publish stable endpoints without writing controllers. Identity passthrough allows consistent tenancy enforcement at the database layer. OpenAPI docs help clients integrate quickly. DreamFactory’s neutral approach lets you evolve underlying stores or federation mechanics while keeping the same API contract.

The Future and Next Steps

Unified data access is moving toward policy-driven APIs that separate data contracts from storage choices. With DreamFactory at the edge and a federation tier behind it, teams can iterate on storage, indexing, and refresh strategies without impacting clients. Start by modeling one high-value view, apply RLS, and publish a read-only endpoint. Add caching and rate limits, then expand coverage. Contact our team to review your schema design, security posture, and rollout plan.

Key Takeaways and How to Get Started

• Use Postgres as a federation hub for MySQL and MongoDB
• Express cross-store joins as views and materialized views
• Connect DreamFactory to the federation schema only
• Enforce RBAC, masking, and identity passthrough in DreamFactory
• Optimize with pushdown, indexing, caching, and pooling

Create a Postgres federation, connect DreamFactory, publish views, and validate with realistic load. When ready, version your endpoints and expand with confidence.

FAQs 

What is a federated query engine and API gateway pattern?

A federated query engine unifies data across stores by mapping remote tables and pushing filters to each source. An API gateway publishes stable endpoints and applies governance. DreamFactory provides the API gateway layer, auto-generates endpoints over views, and enforces role-based access and identity passthrough. This pattern separates data access from storage so teams deliver faster, with less custom code and clearer security controls across internal tools and customer applications.

Why do teams need DreamFactory for a unified API layer?

Teams need consistent authentication, authorization, and auditing across heterogeneous data. DreamFactory centralizes these controls, generates OpenAPI-documented endpoints, and applies role-based filters. The platform reduces integration time and helps enforce least privilege. With identity passthrough, tenancy rules apply at the database layer. This improves delivery speed for dashboards, customer portals, and LLM retrieval, while keeping a single surface for monitoring and incident response.

What are the best tools for joining MySQL, Postgres, and MongoDB?

For cross-store joins, Postgres with FDWs is a practical choice, while DreamFactory exposes the resulting views as governed APIs. Other engines may fit certain needs, but the Postgres plus DreamFactory pattern balances operability and performance. It provides SQL familiarity, predictable indexing, and stable contracts for clients. DreamFactory adds RBAC, caching, and documentation, which removes substantial glue code and reduces operational risk for internal and external consumers.

How do I handle performance for high-traffic external APIs?

Start with materialized views for heavy endpoints and schedule refreshes aligned to freshness needs. Use fields and limit for partial responses. Add indexes to join keys and common filters. Pool connections to Postgres and ensure FDW pushdown is enabled. Cache responses in DreamFactory for hot queries and set role-based rate limits. Measure P95 and P99 latencies under realistic load before launch, then iterate on indexing and refresh intervals.

Can I enforce tenant isolation with identity passthrough?

Yes. DreamFactory maps identity claims from OAuth or SSO into request context and lookup keys. Postgres row-level security can read those values as session settings to filter rows per tenant or user. Define policies in the federation schema and restrict DreamFactory roles to the approved views. This approach keeps enforcement close to the data while providing a consistent API for client teams without duplicating authorization logic in applications.