Effortlessly Join MySQL with Any Database Using API Maker’s Find & Join Feature

Modern applications increasingly use a polyglot database architecture, combining the structured reliability of MySQL with the flexibility of MongoDB, the analytical capabilities of PostgreSQL, or the enterprise features of Oracle and MSSQL. While this approach supports better scalability and data modeling, querying and joining related data across multiple databases remains one of the biggest challenges in multi-database environments.

API Maker’s Find & Join feature addresses this challenge by enabling real-time, cross-database joins and advanced filtering between MySQL and other databases—all through REST APIs, without writing custom backend code, SQL joins, or sync scripts.

Why Cross-Database Joins Matter in Modern Applications

Many organizations use MySQL alongside other specialized databases to meet diverse data needs:

• Optimized data modeling: MySQL is ideal for structured, transactional data, while MongoDB or PostgreSQL may handle flexible documents or analytical workloads.
• Scalable architecture: Different services or teams can adopt the database engine best suited to their specific use case.
• Legacy and modern systems: Traditional SQL databases often need to interoperate with newer NoSQL stores as part of digital transformation efforts.

However, joining data across these databases especially in real time has historically been difficult. Developers often face challenges such as:

• Writing and maintaining custom backend code to fetch and merge data from different systems
• Managing fragile ETL (Extract, Transform, Load) processes to keep data synchronized
• Handling data consistency, latency, and error-prone edge cases

These traditional methods are time-consuming, brittle, and difficult to scale. As data ecosystems grow more complex, the need for a simpler, more unified approach to cross-database joins becomes critical.

How API Maker’s Find & Join Feature Solves Cross-Database Joins

API Maker’s Find & Join feature is designed to simplify cross-database queries by enabling seamless, schema-based joins between MySQL and a wide range of other databases—including MongoDB, PostgreSQL, MariaDB, MSSQL, Oracle DB, TiDB, Percona XtraDB, and even multiple MySQL or MongoDB instances.

With Find & Join, you can:

• Define cross-database relationships visually in your schema—no backend code needed
• Query, filter, and sort across nested relationships in real time using standard REST APIs and JSON
• Retrieve deeply related data in a single API call, reducing complexity on both frontend and backend

Key Benefits

• No need to write SQL joins or backend logic
• Supports unlimited nested relationships (N-level joins)
• Works across SQL and NoSQL databases, in any direction (e.g., MySQL to MongoDB or vice versa)
• Real-time data access—no ETL pipelines or manual sync required

Setting Up Cross-Database Joins: MySQL to Any Other Database

To enable cross-database joins between MySQL and another supported engine, define the relationship directly in your schema using API Maker’s table schema configuration. This schema acts as the blueprint for resolving joins dynamically at runtime.

Step 1: Define the Relationship in Schema

Let’s say you have:

• An orders table in MySQL
• A customers collection in MongoDB

Each order record in MySQL includes a customer_id that references a customer in MongoDB.
In your API Maker schema for the MySQL orders table, define the relationship like this:

customer_id: {  
  __type: EType.string,   // Match the target field type  
  instance: "Mongo",      // Target database engine  
  database: "crm",        // Target database name  
  table: "customers",     // Target table or collection 
  column: "_id"           // Field in the target table or collection}  
}         

This tells API Maker how to fetch and link related records automatically during query execution.

When you call the orders API, the platform will automatically join and return the linked customer data from MongoDB—without requiring any custom backend code or manual joins. The same approach works across any supported combinations, including MySQL → PostgreSQL, MySQL → MSSQL, or even MySQL → MySQL (across instances).

Step 2: Querying Joined Data via REST API

Once your schema is configured, you can immediately perform cross-database joins using standard GET or POST API requests. API Maker automatically resolves relationships across databases—so you can filter, sort, and paginate using joined fields, with no backend logic required.

1. Filter MySQL Orders by Fields in MongoDB (GET Example)

Suppose you want to retrieve all orders from MySQL where the associated MongoDB customer has a "premium" tier:

GET /api/orders?find={"customer_id.tier":"premium"}  

Here, customer_id.tier uses dot notation to reference a field inside the related MongoDB document. API Maker will automatically join and merge the relevant customer data into the MySQL response.

2. Perform Advanced Filtering, Sorting, and Pagination (POST Example)

Want to fetch recent orders placed by active customers who signed up after January 1, 2024—and limit results to the top five?

POST /api/orders/query
Content-Type: application/json

{
  "find": {
    "customer_id.created_at": { "$gte": "2024-01-01" },
    "customer_id.status": "active"
  },
  "limit": 5,
  "sort": {
    "customer_id.created_at": -1
  }
}

This will return the five most recent MySQL orders placed by MongoDB customers who are active and joined after the specified date, sorted in descending order by creation time.

3. Query Nested and Multi-Level Joins

API Maker supports deep, N-level nested joins—across both SQL and NoSQL systems. For example, if:

• MySQL orders reference MongoDB customers
• MongoDB customers reference PostgreSQL distributors

You can query distributor fields (from PostgreSQL) through the customer (from MongoDB), while querying orders (from MySQL)—all in a single request. Just extend your schema and use dot notation like:

"find": {  
  "customer_id.distributor_id.region": "Europe"
}  

API Maker will automatically resolve all joins in real time, across databases.

Example Use Cases

• E-commerce platforms: Join orders stored in MySQL with customer profiles in MongoDB and return data in PostgreSQL—enabling unified queries for order history, customer segmentation, or support workflows.
• SaaS applications: Combine access logs from MySQL, customer accounts from MSSQL, and subscription plans from MongoDB—ideal for building real-time dashboards, billing summaries, or usage-based alerts.
• Legacy system modernization: Seamlessly integrate legacy MySQL databases with newer microservices using NoSQL (like MongoDB or Couchbase), enabling gradual migration without breaking existing workflows.

Supported Databases and Scalability

API Maker’s Find & Join feature works across all major SQL and NoSQL databases, making it ideal for modern, distributed data architectures.

Supported databases include:

• MySQL, MariaDB, PostgreSQL, MSSQL, Oracle DB
• MongoDB (including clustered setups), TiDB, Percona XtraDB

You can define unlimited cross-database relationships—even within a single table. For example:

• One field in a MySQL table can reference MongoDB
• Another field in the same table can link to PostgreSQL

Joins are fully flexible:

• From MySQL to other engines
• From other engines back to MySQL
• Multi-way joins between multiple databases in the same query

All joins are resolved dynamically at runtime, with no data replication or sync scripts required. Learn more in the Find & Join documentation.

Real-World Advantages Over Traditional Approaches

Building cross-database joins using traditional methods often requires:

• Custom backend services
• Manual data merging
• Fragile ETL pipelines
• High development and maintenance overhead

API Maker’s Find & Join feature eliminates these complexities by offering a declarative, no-code approach to joining data across MySQL and any supported database engine—instantly, and at runtime.

Comparison with Other Backend Platforms

API Maker stands apart as the only platform designed for true cross-database querying, delivering:

• Real-time access to federated data
• No-code configuration
• Compatibility with both SQL and NoSQL backends
• Unmatched scalability for modern, multi-database applications

Best Practices & Performance Tips

Maximize the efficiency and reliability of your cross-database joins by following these guidelines:

• Index your join fields: Ensure primary and foreign key fields (e.g., customer_id in MySQL or _id in MongoDB) are indexed to improve join performance and query speed.
• Define relationships explicitly: Clearly configure cross-database relationships in your schema. API Maker uses this metadata to optimize runtime joins and minimize query overhead.
• Limit payload size: Use precise field selection (select) and targeted filters (find) to reduce data transfer and speed up responses—especially when dealing with deeply nested or multi-way joins.
• Use deep populate efficiently: For multi-level or nested relationships, leverage API Maker’s deep populate capabilities to fetch related data in a single call without redundant queries.

These practices ensure your APIs remain fast, scalable, and maintainable—even as your schema and data complexity grow.

FAQ's

1. Can I join MySQL with other databases in both directions?

Yes. You can create joins from MySQL to any supported database (e.g., MongoDB, PostgreSQL, MSSQL) or vice versa. Just configure the relationships in your schema, API Maker will handle the rest at query time.

2. How do I apply filters on fields from joined databases?

Use dot notation based on your schema relationships.

For example: { "customer_id.status": "active" }

This will filter MySQL orders based on the status field in the related MongoDB customers collection.

3. Can I configure multiple relationships from the same MySQL table?

Absolutely. You can define multiple cross-database relationships for a single table (e.g., orders linked to MongoDB customers, PostgreSQL invoices, etc.). API Maker dynamically resolves all joins at runtime.

4. Do I need to write backend code to support joins?

No. API Maker’s Find & Join feature requires zero backend development, no Node.js, Java, Python, or custom SQL logic. Everything is configured declaratively through the visual schema editor.

5. What happens if I need to scale or migrate my architecture?

API Maker is designed for flexibility. If your architecture changes (e.g., migrating from PostgreSQL to MongoDB), simply update the schema configuration, no code changes required.

Learn More and Get Started

Explore how API Maker simplifies cross-database joins and accelerates your development workflow:

• 🔗 Overview of Find & Join:
  API Maker Find & Join
• 📘 Schema Configuration Guide:
  How to Define Cross-Database Relationships
• ⚙️ Try Auto-Generated REST APIs:
  Explore API Maker’s Auto-Generated API Reference