Deep Populate: Simplifying Multi-Database Data Access

Transform Complex (Database Joins | data populate) Into Simple API Requests

API Maker's deep populate feature revolutionizes how developers handle complex relational data by fetching nested information from multiple databases with a single API call. No more writing intricate JOIN queries or managing multiple database connections manually.

What Makes Deep Populate Different?

Traditional database queries require developers to write complex JOIN statements or make multiple API calls to gather related data. Deep Populate eliminates this complexity by automatically creating a network of relationships across multiple database systems, returning comprehensive nested data structures in one api request only.

Before Deep Populate:

-- Multiple queries needed
SELECT 
    p.id AS person_id,
    p.name AS person_name,
    s.name AS street_name,
    a.name AS area_name
FROM 
    person p
JOIN 
    streets s ON p.street_id = s.id
JOIN 
    area a ON s.area_id = a.id;

With Deep Populate:

• Just provide what we want to populate. System will automatically identify how to populate.
• For Schema defined API, deep populate API call URL is shown below.

/api/schema/user-path/instance/database/table?deep=[{s_key:"state_id"}]

• For schema API or generated API, we can override schema settings by providing "t_instance", "t_db", "t_col" in API's request body or query parameters.

/api/schema/user-path/instance/database/table?deep=[{s_key:"state_id",t_instance:"oracle_db",t_db:"inventory",t_col:"states",t_key:"id"}]
/api/gen/user-path/instance/database/table?deep=[{s_key:"state_id",t_instance:"oracle_db",t_db:"inventory",t_col:"states",t_key:"id"}]

Why Deep Populate Makes Cross-Database Data Retrieval Easier

Cross-Database Relationship Management

Where traditional ORMs struggle with multi-database or cross-database resource retrieval, API Maker can over come that limitation with the support of 8 database including both SQL and No-SQL databases including MongnoDB, PostgreSQL, MySQL many other which you can check here.

Automatic Performance Optimization

Built-in query optimization ensures that even complex multi-level relationships execute efficiently. The system intelligently batches queries, and implements caching strategies to maintain fast response times.

How Deep Populate Works | How nested join alternative works

The Relationship Chain Concept |

Database relations are defined at the time of schema creation, When the user targets a specific field via a request, API Maker travels through data by checking the database schema, retrieves the data and wraps the content into a single json response object. This allows API Maker to build relations between different database type instances without any limitation.

Let's say if the user does not want to define a schema and wants dynamic cross database data retrieval or wants to override the default relation, the s_key and t_key properties in the deep populate parameter object are used in this scenario. To learn more about this feature, click here.

Example Relationship Chain:

 Person → Street → City → Country

Each arrow represents a foreign key relationship that Deep Populate resolves automatically, even when the data is spread across different database types and their installations.

Sample Request Object With Deep Populated Data:

Deep Populate requests use a nested JSON structure that mirrors your data relationships:

{
    "find": {
        "person_name": "John"
    },
    "deep": [
        {
            "s_key": "street_id",
            "t_key": "id",
            "t_table": "streets",
            "deep": [
                {
                    "s_key": "city_id",
                    "t_key": "id",
                    "t_table": "cities",
                    "deep": [
                        {
                            "s_key": "country_id",
                            "t_key": "id",
                            "t_table": "countries"
                        }
                    ]
                }
            ]
        }
    ]
}

This structure tells API Maker exactly how to traverse your data relationships, regardless of which databases contain each piece of information.

Sample Response Object With Deep Populated Data:

{
    "person_name": "John",
    "street_id": {
        "id": 5,
        "name": "Main Street",
        "city_id": {
            "id": 101,
            "name": "New York",
            "country_id": {
                "id": 1,
                "name": "USA"
            }
        }
    }
}

Advanced Features for Enterprise Applications

Streaming for Large Datasets

When dealing with large result sets, The stream APIs can be used to optimize the api response content, The streams can help by providing the content of the request into chunks without loading whole data into memory which will prevent memory overload in larger data infrastructures.

Benefits Of Using Deep Populate With Streams:

• Memory efficient getting data of large result sets across database types
• Real-time & continuous data delivery for responsive user interfaces
• Pagination support for complex nested queries
• Scalable architecture that grows with your data volume
• Low memory servers can also handle huge volume of data.
  • Ex : 1GB RAM VPS can deliver 10GB size data & Millions of rows in single API call without giving memory spike

Automatic Caching In Deep Populate Feature

The in built caching system recognizes patterns in Deep Populate requests and automatically caches frequently accessed data combinations. This intelligent caching reduces database load and improves response times for common query patterns.

Caching Features:

• Automatic cache invalidation when source data changes with Redis.
• Caching can be enabled or disabled at table level, database level or instance level.
• Memory efficient cache storage by help of Redis.
• Cross database cache coherency for consistent results.

Schema-Based Relationship Definitions

Database relationships can be defined into api maker schemas which allow user to request the Data by providing the name of the field in the request which makes cross database data retrieval faster and easier. Visit this page for more detail.

• Just give field name as shown below. Relationship is already configured in schema. Find holds filter condition in the example.

{
  "find": { "person_name": "Kishan" },
  "deep": ["full_address_chain"]
}

Integration with Modern Development Workflows

RESTful API Compatibility

• All the APIs are REST APIs in API Maker but they also supports XML & YML responses.
• Deep Populate works seamlessly with standard REST API patterns, extending traditional GET requests with powerful relationship traversal capabilities. Your existing API clients require no modifications to benefit from Deep Populate functionality.

GraphQL Style Flexibility

• While maintaining REST simplicity, Deep Populate provides GraphQL like flexibility for specifying exactly which related data you need using select parameter.
• You can pass select in request body or request query parameters, and APIs will return only those columns or fields based on selected values.

Microservices Architecture Support

• In microservices environments, Deep Populate can aggregate data from multiple database installations and database types, providing a unified data access layer that simplifies client application development.
• API Maker retrieves data without zookeeper and kafa in between directly from the database, so API Maker is much faster compare to other micro service implementations.

Migration from Existing Systems

• while migrating from existing systems, you might have foreign key & primary key relations or they might not be defined.
• API Maker works in both cases for Deep populate feature.
• Auto generate schema feature can easily identify foreign key & primary key relations, and defines them in schema.
• To get multiple tables data, no need to write complex join operations.
• If you don't maintain foreign key & primary key relations, you can still get data by providing relations while doing API call as we saw earlier.
• So, API Maker's deep populate feature makes life lot easier while migrating from old systems to new systems. And Auto generated APIs saves lot of time in entire development process.

Multi Database Architecture Support

• Deep populate feature works seamlessly across API Maker's supported database ecosystem, database relations between SQL to SQL are possible as well as SQL to NoSQL
• So, we can get data from SQL databases to No-SQL databases in just one API call and vice versa.
• We can not write join operations across database types in SQL, API Maker solves this problem just out of the box.
• And we can also get data from SQL to SQL databases in just one API call and vice versa.
• Below database types are supported for deep populate feature to get data across database types
  • Relational Databases:
    • MySQL and MariaDB for traditional web applications
    • PostgreSQL for advanced data types and complex queries
    • Microsoft SQL Server for enterprise environments
    • Oracle Database for large-scale business applications
  • Document and Distributed Databases:
    • MongoDB for flexible document-based data models
    • TiDB for distributed SQL processing
    • Percona XtraDB for high-performance MySQL clustering

Deep Populate Of API Maker vs Supabase | Appwrite | Firebase

• When building modern applications, developers often choose between established backend platforms like Supabase, Firebase, Appwrite, or GraphQL implementations. Here's how Deep Populate compares across key features:

Supabase: PostgreSQL Excellence, Limited Scope

Supabase excels at PostgreSQL management but struggles with multi-database scenarios.

Supabase Limitations:

• Single PostgreSQL database per project
• Complex JOINs require manual SQL script writing
• No cross database relationship support
• Limited to PostgreSQL's data types and constraints

Firebase: Great for Simple Apps, Limited for Enterprise

• Firebase's Firestore is excellent for rapid prototyping but hits walls with complex data and vendor locking with Google

Firebase Limitations:

• No true relational queries (only subcollections)
• Expensive for read-heavy applications
• Limited to NoSQL document model
• Complex pricing structure for large datasets

Deep Populate Advantages:

• Query across PostgreSQL, MongoDB, MySQL etc database types simultaneously
• No SQL knowledge required for complex relationships
• Support for 8+ database types in one query

Why API Maker Doesn't Need GraphQL Support

Deep Populate: The GraphQL Alternative That Actually Works

• Many developers ask why API Maker doesn't offer GraphQL support. The answer is simple: Deep Populate provides all the benefits of GraphQL without any of the complexity, overhead, or limitations or without any coding at all.
• GraphQL was created to solve the problems of over-fetching data and making multiple API calls. API Maker's Deep Populate feature solves these same problems without any coding.

The GraphQL Problem Statement

GraphQL emerged to address several key issues with traditional REST APIs:

• Over-fetching: Getting more data than needed
• Under-fetching: Making multiple requests for related data
• API Versioning: Managing schema changes over time
• Frontend Flexibility: Letting clients specify exactly what data they need

API Maker's Deep Populate solves all these problems better than GraphQL and that is without doing any single line of code and works for across database types and across database installations.

Ready to transform your data access layer? Explore API Maker's Deep Populate documentation see how it can simplify your next project.

FAQ's

1. How many database levels can Deep Populate handle in a single request?

Deep Populate supports unlimited nesting levels. You can traverse relationships from Person → Street → Area → City → State → Country → Continent → Planet → Galaxy and beyond. Just remember API Maker will take when database will take time.

2. Can I use Deep Populate with real-time data or webhooks?

Absolutely. Deep Populate works with API Maker's real-time features. You can set up webhooks that trigger when any data in your relationship chain changes.

3. Does Deep Populate support filtering and sorting on nested data?

Yes. You can apply filters, sorting, and pagination at any level of your deep populate query.

4. How does Deep Populate handle performance with large datasets?

You can use stream APIs of API Maker for large data sets. API Maker's response will take time when when database will take time. Deep populate feature mostly works with primary keys so, index will be present on data. If index is not present make sure to create them and it will increase performance of data retrival.

5. Can Deep Populate work across different database types in one query?

Yes, absolutely. Deep Populate seamlessly connects data across all supported databases - MySQL, PostgreSQL, MongoDB, Oracle, SQL Server, MariaDB, TiDB, and Percona XtraDB. You can start with a MongoDB document and traverse to PostgreSQL tables without any additional configuration.

6. Can Deep Populate work with one database but across different installations?

Yes, absolutely. Deep populate feature can get data from mysql only but from different installations types without any coding at all. Same way it works with other database types.

Learn more about API Maker's low-code backend platform and other powerful features at apimaker.dev