Cross Database Joins and Conditional Filtering with SQL Server in API Maker

Find & Join Across Databases with SQL Server as the Source

Microsoft SQL Server is a go-to choice for enterprise-grade workloads, known for its robust tooling, strong consistency, and tight integration with the Microsoft ecosystem. But most modern stacks aren’t built around a single database. You might use MongoDB for unstructured data or PostgreSQL and Oracle for analytics or legacy systems, all alongside SQL Server.

The challenge? Joining data across these systems usually means building custom APIs, setting up ETL pipelines, or writing backend glue code. It's complex, brittle, and not built for change.

That’s where API Maker’s Find & Join feature comes in. It lets you use SQL Server as your starting point and query across any supported database, relational or NoSQL, using a unified, schema-driven API. You can define cross-database relationships and then fetch, join, and filter data from multiple sources in real time using a simple JSON query.

This guide walks you through using SQL Server as the source for cross-database joins and conditional filters. For a full overview of how the Find & Join Feature works, visit the Find & Join Feature page.

Why Use SQL Server as Your Primary Source in API Maker?

If your main data, like customers, orders, or transactions, lives in Microsoft SQL Server, API Maker lets you use it as your starting point to query across other databases in real time. With Find & Join, you can not only merge data from multiple sources, but also filter your results using fields from joined records, no SQL, no ETL, no data duplication.

Just define the schema relationships once, and you're good to go.

What You Can Do with Joins and Filters

• Join SQL Server orders with MongoDB products and filter orders where the product category is "electronics"
• Link SQL Server users to PostgreSQL profiles and return users where profile.age > 30
• Combine SQL Server transactions with MySQL payments and fetch only the failed ones
• Join tickets in SQL Server with agents from another SQL Server instance to get tickets assigned to inactive agents

These real-time, cross-database queries are powered by API Maker’s Find & Join Feature, which takes care of all the joins and filters automatically through REST APIs.

Connecting SQL Server to API Maker

Before you can use SQL Server as a source for cross-database queries, you’ll need to set it in API Maker through the built-in Secret Manager. This ensures your connection credentials remain secure while making the instance fully available for schema setup and querying.

🔗 For detailed setup instructions, refer to the SQL Server Connection Guide. and for setting up the connection in the secret manager, refer to the Secret Manager Page.

Required Configuration

When adding SQL Server, here are the essential parameters you'll need to pass as a connection string:

• Type: SQLServer
• Connection String Example:

	Server=sqlserver.local;Database=orders_db;User Id=admin;Password=secret;

Once your SQL Server instance is connected, it becomes available in the schema editor. You can then begin defining relationships that allow real-time joins between SQL Server tables and external databases like PostgreSQL, MongoDB, or Oracle—powered by the Find & Join feature.

How Cross-Database Joins Work

API Maker enables real-time cross-database joins by letting you define relationships directly in your SQL Server schema. These relationships are declared using the ISchemaProperty format, allowing SQL Server tables to connect seamlessly with external systems like Oracle, MongoDB, MySQL, and more.

The process is fully declarative—no need to write custom SQL or manage external pipelines. Once defined, API Maker understands how a field in one table (such as customer_ref) relates to a document or row in another database.

Example: Linking SQL Server payments to Oracle invoices

customer_ref: <ISchemaProperty> {
  __type: EType.string,
  instance: "Oracle",
  database: "finance_db",
  table: "invoices",
  column: "invoice_id"
}

What’s happening here:

• You have a payments table in SQL Server.
• Each record contains a customer_ref, which refers to the primary key invoice_id in Oracle’s invoices table.
• API Maker resolves this reference dynamically at query time—no ETL jobs or replication required.

This makes it possible to:

• Perform joins across databases in a single REST API request
• Apply filters based on fields from remote data (e.g. filter payments by invoices.status)
• Access and filter related data using dot notation (customer_ref__ref.total_due)

For more on defining relationships, check out the API Maker Schema Docs.

Query Example: Cross-Database Join with Deep Populate

In a hospital system, suppose you store patient appointments in a SQL Server database, while doctor policies (such as specialization and max_consultations) are managed in an Oracle system.

With API Maker, you can:

• Filter appointments based on remote Oracle fields
• Enrich the response with full joined doctor policy data
• Do all of this in a single API call, without SQL or ETL jobs

Example Schema Reference:

doctor_id: <ISchemaProperty> {
  __type: EType.string,
  instance: "Oracle",
  database: "hospital_admin",
  table: "doctor_policies",
  column: "id"
}

This tells API Maker:
SQL Server's appointments.doctor_id maps to Oracle's doctor_policies.doctor_id.

Cross-Database Join and Filter with Deep Populate Request

POST /api/appointments
Content-Type: application/json

{
  "find": {
    "doctor_id.max_consultations": { "$lt": 10 }
  },
  "deep": [
    {
      "s_key": "doctor_id"
    }
  ],
  "limit": 5,
  "sort": { "scheduled_at": -1 }
}

What This Query Does

• Finds appointments in SQL Server
• Filters only those where the joined Oracle record (doctor_policies.max_consultations) is less than 10
• Returns each result along with its nested Oracle data using the deep property.

Sample Response:

{
  "success": true,
  "statusCode": 200,
  "data": [
    {
      "appointment_id": 10452,
      "patient_id": "PAT-2024",
      "scheduled_at": "2025-08-01T10:30:00Z",
      "reason": "Follow-up",
      "doctor_id": {
        "id": "DR-219",
        "specialization": "Cardiology",
        "max_consultations": 8
      }
    }
  ]
}

Key Advantages

• Join, Filter and Populate across databases in one request
• Dot notation (doctor_id.max_consultations) enables remote filtering
• deep lets you include full remote records inline—no need for follow-up lookups
• Eliminates the N+1 problem with optimized internal batching
• Works across SQL Server, Oracle, PostgreSQL, MongoDB, and more

For more details, check out the Deep Populate overview and Deep Populate Guide.

Best Practices for Cross-Database Joins with SQL Server

To ensure efficient and reliable cross-database joins when SQL Server is your source, follow these key recommendations:

• Index join and lookup fields (e.g., doctor_id, policy_code) in your SQL Server tables to speed up join resolution, especially when working with high-volume records.
• Ensure data type consistency across all linked databases—IDs, dates, and numeric fields should use compatible formats to avoid type conflicts and unexpected filter results.
• Apply limit, skip, and find to your queries to reduce the size of intermediate join results. This helps API Maker process and return data faster with less memory overhead.
• Be mindful of date and number formats—SQL Server may use different time zones or precision levels than Oracle, MongoDB, or others. Always validate how comparisons behave at runtime.
• Verify your schema configuration early by using sample queries with joins. Incorrect __type, instance, or column mappings can silently cause incomplete or failed responses.

By following these practices, you ensure that cross-database joins stay fast, accurate, and production-ready even as your data sources evolve.

FAQ's

1. Can I filter SQL Server data using fields from joined Oracle tables?

Yes. You can apply filters using fields from the joined Oracle dataset.
For example, use customer.due_date or customer.status in your find object to filter SQL Server results based on Oracle data.

2. Do I need to write SQL JOIN statements manually?

No. API Maker handles that automatically.
Once you've defined your cross-database relationships in the schema, API Maker resolves the joins during query execution, so you only need declarative JSON-based queries.

3. Can I perform reverse joins from Oracle, MongoDB, or other systems back to SQL Server?

Yes. Reverse joins work if the reference is defined in the external system’s schema pointing back to a SQL Server field.

4. Is deep population supported for cross-database joins?

Yes. Use the deep parameter in your request to populate nested joined data (e.g., policy) inline within the response.

5. How many databases can I join in a single query?

You can join across multiple systems (e.g., SQL Server → Oracle → MongoDB, or SQL Server → PostgreSQL → MySQL) in one query, provided the schema relationships are properly defined.

6. Which databases are supported by API Maker’s Find & Join feature?

API Maker supports cross-database operations with the following engines: MongoDB, MySQL, PostgreSQL, Oracle, SQL Server, MariaDB, TiDB, and Percona XtraDB.