Deep Populate with MySQL in API Maker

What is Deep-populate with MySQL

API Maker's Deep Populate for MySQL enables complex, nested, and cross-database queries with a single API call. It eliminates the need for manual SQL joins or multiple backend integrations, making relational data retrieval effortless.

This guide covers everything you need to implement MySQL Deep Populate in modern, multi-database environments.

Why Use Deep Populate with MySQL?

MySQL's structured, transactional strengths combined with API Maker's Deep Populate create powerful APIs that access nested data seamlessly across multiple database systems.

Key Benefits

• Zero SQL Writing: Fetch deeply related data without complex JOINs
• Cross-Database Support: Query MySQL alongside MongoDB, PostgreSQL, Oracle, and more
• Auto-Generated APIs: ~30 APIs generated automatically for each table with ~700+ requests/second performance on 1 CPU core
• Schema-Based Security: Built-in field-level access control
• Unlimited Nesting: Support for deeply nested relationship levels
• REST-Native: Compatible with standard REST API conventions

Core Deep Populate Syntax

Deep populate uses s_key (source key) and t_key (target key) to define relationships:

{
  "s_key": "foreign_key_field",
  "t_instance": "database_type",
  "t_db": "database_name", 
  "t_col": "target_table",
  "t_key": "primary_key_field",
  "select": "field1,field2,field3",
  "isMultiple": false
}

Single-Level Deep Populate

Example: Products with Categories

Request:

GET /api/schema/mysql/shop/products?find={"product_id":10}&deep=[{
  "s_key": "category_id",
  "t_instance": "mysql",
  "t_db": "shop",
  "t_col": "categories",
  "t_key": "id",
  "select": "name,description"
}]

Response:

{
  "product_id": 10,
  "name": "Gaming Laptop",
  "category_id": {
    "id": 3,
    "name": "Electronics",
    "description": "Electronic gadgets and devices"
  }
}

Multi-Level Deep Populate

Example: Orders → Line Items → Products → Suppliers

Request:

GET /api/schema/mysql/sales/orders?find={"order_id":1001}&deep=[{
  "s_key": "id",
  "t_col": "order_items",
  "t_key": "order_id",
  "isMultiple": true,
  "deep": [{
    "s_key": "product_id",
    "t_col": "products", 
    "t_key": "id",
    "select": "name,price",
    "deep": [{
      "s_key": "supplier_id",
      "t_col": "suppliers",
      "t_key": "id",
      "select": "company_name,contact_email"
    }]
  }]
}]

Response:

{
  "order_id": 1001,
  "id": 1001,
  "order_items": [
    {
      "product_id": {
        "id": 501,
        "name": "USB Keyboard",
        "price": 29.99,
        "supplier_id": {
          "id": 9,
          "company_name": "TechGear Inc.",
          "contact_email": "support@techgear.com"
        }
      }
    },
    {
      "product_id": {
        "id": 502,
        "name": "Gaming Mouse",
        "price": 59.99,
        "supplier_id": {
          "id": 11,
          "company_name": "MouseWorks",
          "contact_email": "sales@mouseworks.com"
        }
      }
    }
  ]
}

Cross-Database Deep Populate

Example: MySQL Orders + MongoDB Customer Profiles

Request:

GET /api/schema/mysql/sales/orders?find={"order_id":101}&deep=[{
  "s_key": "customer_id",
  "t_instance": "mongodb",
  "t_db": "crm",
  "t_col": "customers",
  "t_key": "customer_id",
  "select": "name,email,preferences"
}]

Response:

{
  "order_id": 101,
  "customer_id": {
    "_id": "55",
    "name": "Charlie Davis",
    "email": "charlie@example.com",
    "preferences": {"newsletter": true, "sms": false}
  }
}

Advanced Query Features

Conditional Filtering

{
  "s_key": "customer_id",
  "t_col": "payments",
  "t_key": "customer_id",
  "find": {"status": "completed", "amount": {"$gte": 100}},
  "isMultiple": true
}

Sorting and Pagination

{
  "s_key": "category_id",
  "t_col": "products",
  "t_key": "category_id", 
  "sort": {"created_at": -1},
  "limit": 10,
  "skip": 0
}

Supported Database Combinations with MySQL

API Maker supports MySQL as the primary database with seamless integration to multiple database types:

MySQL + MongoDB

Best For: E-commerce, Content Management, Product Catalogs

• MySQL: Structured order data, customer accounts, financial transactions
• MongoDB: Product catalogs, user preferences, content metadata, reviews
• Benefits:
  • ACID compliance for payments with flexible product attributes
  • Fast transactional processing with rich content storage
  • Structured pricing with unstructured product descriptions

Example Use Case: E-commerce platform where MySQL handles orders/payments while MongoDB stores product variants, images, and user-generated content.

MySQL + PostgreSQL

Best For: Analytics, Reporting, Enterprise Applications

• MySQL: Core application data, user management, real-time operations
• PostgreSQL: Data warehousing, complex analytics, reporting, audit logs
• Benefits:
  • MySQL's speed for OLTP with PostgreSQL's analytical capabilities
  • JSON support in both databases for flexible data modeling
  • Advanced indexing and full-text search in PostgreSQL

Example Use Case: SaaS application with MySQL for user data and PostgreSQL for advanced analytics and reporting.

MySQL + Oracle

Best For: Enterprise Integration, Legacy System Modernization

• MySQL: Modern application layer, API data, user interfaces
• Oracle: Enterprise resource planning, financial systems, compliance data
• Benefits:
  • Cost-effective modern development with enterprise-grade legacy integration
  • MySQL's simplicity with Oracle's enterprise features
  • Gradual migration path from Oracle to modern stack

Example Use Case: Enterprise modernization where new applications use MySQL while maintaining Oracle for critical financial systems.

With API Maker, you can implement the multi-Database architecture in your projects and take advantage of all the supported databases.

All Supported Databases with mysql in API Maker

• MySQL
• PostgreSQL
• Oracle
• SQL Server
• MariaDB
• TiDB
• Percona XtraDB

Cross-Database Query Benefits

1. Data Locality: Keep data in the most appropriate database type
2. Performance Optimization: Use each database's strengths
3. Cost Efficiency: Avoid expensive database licensing for simple operations
4. Scalability: Scale different data types independently
5. Technology Flexibility: Adopt new technologies without complete rewrites
6. Risk Mitigation: Distribute data across multiple systems for redundancy

Performance Best Practices

• Index Strategy: Always index s_key and t_key fields
• Field Selection: Use select to limit response payload size
• Depth Limits: Keep nesting under 5 levels for optimal performance
• Schema APIs: Use /api/schema/ endpoints instead of /api/gen/ for better performance
• Batch Queries: Group related requests to reduce API overhead

API Methods with Deep Populate

GET

Retrieve nested data with filtering and sorting

GET /api/schema/mysql/db/table?deep=[...]

POST

Create records with nested relationships in a single transaction

POST /api/schema/mysql/sales/orders
Content-Type: application/json

{
  "customer_id": 123,
  "order_date": "2024-08-05",
  "order_items": [
    {
      "product_id": 456,
      "quantity": 2,
      "price": 99.99
    }
  ]
}

PUT

Update parent records and automatically sync related data across databases

PUT /api/schema/mysql/sales/orders/1001
Content-Type: application/json

{
  "status": "shipped",
  "tracking_number": "TRK123456",
  "deep_update": [
    {
      "s_key": "customer_id",
      "t_instance": "mongodb",
      "t_db": "crm",
      "t_col": "customers",
      "update_data": {
        "last_order_date": "2024-08-05",
        "order_count": {"$inc": 1}
      }
    }
  ]
}

DELETE

Remove records with intelligent cascading across multiple databases

DELETE /api/schema/mysql/sales/orders/1001?cascade_deep=[
  {
    "s_key": "id",
    "t_col": "order_items",
    "t_key": "order_id",
    "action": "delete"
  },
  {
    "s_key": "customer_id",
    "t_instance": "mongodb",
    "t_db": "analytics",
    "t_col": "customer_stats",
    "action": "update",
    "update_data": {
      "total_orders": {"$inc": -1}
    }
  }
]

Common Use Cases

E-commerce Platform

• Orders: MySQL (ACID compliance for transactions)
• Product Catalog: MongoDB (flexible product attributes)
• User Reviews: PostgreSQL (full-text search capabilities)

Enterprise CRM

• Customer Records: MySQL (structured contact information)
• Interaction Logs: MongoDB (flexible event data)
• Analytics: PostgreSQL (complex reporting queries)

Financial Services

• Account Data: MySQL (regulatory compliance)
• Transaction Logs: PostgreSQL (audit requirements)
• Risk Profiles: MongoDB (machine learning data)

Error Handling

Common Issues

• Missing Foreign Keys: Returns null for unmatched relationships
• Type Mismatches: Automatic type conversion where possible
• Connection Timeouts: Configurable timeout settings per database

Debugging Tips

• Enable API logging to trace query execution
• Use response metadata for relationship validation
• Test queries in development environment first

Security Features

• Role-Based Access Control (RBAC): Role-based control for each database and tables
• Schema Validation: Automatic input sanitization
• Audit Logging: Track all data access and modifications

Comparison with Traditional Approaches

Quick Start Checklist

1. Install API Maker: Minimum 1GB RAM, 1 CPU core, 25-30GB storage on Ubuntu 22.04 LTS
2. Connect Databases: Configure MySQL and additional database credentials
3. Define Schemas: Map table relationships using foreign keys
4. Test Queries: Start with single-level deep populate
5. Scale Gradually: Add multi-level and cross-database queries as needed

FAQs

Q: How deep can I nest relationships?

A: Technically unlimited, but recommended to stay under 5 levels for optimal performance.

Q: What if a foreign key doesn't match?

A: The nested field returns null, and you can check response metadata for details.

Q: Can I use this without writing backend code?

A: Yes, API Maker requires zero backend coding—everything is configured via JSON schemas.

Q: Does this work with existing databases?

A: Yes, API Maker can connect to existing MySQL databases without modifications.

Q: How does performance compare to native SQL?

A: API Maker delivers ~700+ requests/second on a single CPU core, with automatic caching and connection pooling.

Next Steps

Ready to implement MySQL Deep Populate? Start with the official API Maker documentation or explore the installation guide to set up your environment.

For technical support, visit the API Maker GitHub repository or contact their team directly.