Cross Database Join & Find Feature

Why This Feature Matters

Most modern applications rely on multiple databases to store different types of information. While this setup is common, it comes with a major challenge.

Performing join operations and conditional filtering across different database types is not easy.

Traditional systems are not built to query across databases — especially if they use different engines or data formats.

This usually means you have to:

• Manually export and transform data
• Build and maintain complex ETL pipelines
• Write custom backend logic to handle joins across systems

All of which are time-consuming, error-prone, and difficult to scale.

Introducing: Find & Join Feature

API Maker’s Find & Join feature lets developers perform cross database joins operations and apply filtering conditions across multiple tables and databases — all within a single REST API request. It works with both auto-generated REST APIs and schema based REST APIs, giving you flexibility based on how your data is structured and how much control you want.

API Maker’s Find & Join feature works based on the relationships defined in your table schema. Whether the data is in MySQL, MongoDB, or any of the 8 supported databases, it can perform cross database joins and apply conditional filters across related fields — even if the tables belong to different database types. This lets you fetch deeply connected, filtered results in real-time, without writing any backend code.

This schema-driven query supports unlimited join depth (N‑level relationships) and rich filtering operators, enabling complex data fetching in one request.

Benefits of using Find & Join Feature

• Cross Database Joins: Query and join data from different databases or instances in one request. For example, you can join a MongoDB collection with a MySQL table in the same query.

• N‑Level Relationships: Fetch multi-level related records seamlessly. There is no hard limit on nesting; you can go till n number of deep relations (e.g. Customer → Order → Shipment).

• Schema Driven Queries: Define relationships once in your table schema, then use simple JSON queries. API Maker uses the schema’s structure to find the database, table/collection and the column for the join operations. This eliminates manual join logic.

• Advanced Filtering: Use MongoDB‑like operators ($eq, $gt, $in, $and, $or, etc.) on joined fields. You can filter nested data (e.g. owner_id.customer_id = 2 or combined conditions with $and/$or).

• Support for REST APIs: Find & Join works seamlessly with auto-generated REST APIs and also with schema based REST APIs, making it a perfect fit for flexible, real-time backends.

How It Works

API Maker uses each table’s schema to know its columns and relationships. When you send a JSON payload with a find object for your filter criteria.

API Maker reads this payload, validates it against the schema, and runs the corresponding queries on each database. It follows the keys that you defined in the schema and merges the results into one JSON response. Everything (filtering, joins, nesting) happens automatically at runtime, so you don’t write any backend code.

Using Find & Join Feature in Api Maker

Before using the find & join feature make sure that you have defined from where to perform the join operations in the table schema so it can take the values from schema and then perform join and any other given operations on them.

You can refer to this page for defining the column, table/collection, database and instance for performing join operations - Defining Table Schema

There are two ways in which you can use the find & join feature in api maker -

1.) In Get Apis

If you’re using auto-generated GET APIs (which API Maker creates for each collection or table), you can pass the find parameter directly in the URL. This lets you filter and join across same or multiple databases(as defined in your table schema) using a single query string.

It supports JSON5, so you don’t have to stress about strict syntax.

This query fetches customers where the linked product’s prize is 5000 — even if orders and products live in different databases.

For More Detailed Information about using find and join as query parameter you can refer to this example - Get All Api

2.) In Post Apis

In POST apis which are generated by api maker based on the table schema and which are used only for retrieving the data with the support of the body object, you can send your find object in the request body with many other options(like sort, limit and deep).

{
    "find": {
        "order_id.customer.name": "Alice"
    }
}

This will return all the records from the order table where the order_id is linked to a customer named Alice, even the data is present in the different databases.

For More Detailed Information about using find and join as a object in post apis you can refer to this example - Query For Get Data

Find & Join with Other Operations

Deep Populate is another powerful feature provided by API Maker that allows us to retrieve actual nested content.

We can use the Deep and Find & Join feature in a single operation. If you need help regarding this, you can visit this page - Using Find & Join with Deep

To learn more about the Deep Populate feature, you can visit this page - Deep Populate

For Using Find & Join feature with other conditional operations and also properties likes sort, limit and totalcount. Refer to this page - More Operations with Find & Join

How It Compares to Other Platforms (Supabase | Appwrite | Firebase | Strapi)

Unlike other platforms like Appwrite, Supabase, Strapi, or Firebase, API Maker supports real time databases joins operations across cross databases — even mixing SQL and NoSQL.

While others focus on either GraphQL or basic queries, API Maker gives you powerful nested filtering and deep joins — all through simple REST APIs. And though it doesn’t support GraphQL yet, it simplifies complex data fetching far beyond what most platforms offer.

FAQ's

1. How deep the find & join feature can go for join operations?

Api Maker can go n Number of depth for performing find and join operations.

2. What if I don’t define the relationships beforehand?

You must define relationships in the schema before using Find & Join — this is how api maker understands how and where to join.

3. Can I join tables or collections from different types of databases?

Yes. API Maker supports cross database joins — for example, joining MongoDB collections with MySQL or PostgreSQL tables with Oracle in the same query.

4. How many databases does the Find & Join feature support?

Api Maker currently supports 8 major databases, including both SQL and NoSQL types. You can explore detailed examples and integrations for each supported database on their dedicated pages.