API structure loaded from DB → change APIs without code changes

Enterprises often need fast, low-risk ways to expose, adapt and version APIs. A Metadata-Driven API Engine lets you define routes, request/response shapes, validation rules, transformations, and security policies in a database (or a central configuration store). The engine reads that metadata at runtime (or on deploy) and exposes fully working HTTP endpoints — no code changes required for many common API changes.

This article is a production-ready, beginner-to-expert guide for senior developers. It explains architecture, data models, runtime design, security, validation, versioning, performance, testing, and operational concerns. Example code is in .NET (ASP.NET Core), but the concepts are applicable to other platforms.

Table of contents

  1. Problem statement and goals

  2. High-level benefits and tradeoffs

  3. Architecture overview

  4. Workflow diagram (how metadata → API)

  5. Flowchart (runtime request processing)

  6. Metadata model (tables / JSON schema)

  7. Core engine components (router, controller factory, binder, validator, transformer, policy engine)

  8. Implementation details — ASP.NET Core patterns and snippets

  9. Validation, schema evolution and compatibility

  10. Security (auth, authorization, input sanitation, rate limits)

  11. Performance, caching and scaling strategies

  12. Observability, testing and CI/CD for metadata changes

  13. Governance, audit, and approvals

  14. Real-world use cases and examples

  15. Limitations and when not to use this approach

  16. Roadmap: extending the engine (UI, DSL, GraphQL adapter)

  17. Conclusion and next steps

1. Problem statement and goals

Typical problem:

Goal for a metadata-driven API engine:

2. High-level benefits and tradeoffs

Benefits

Tradeoffs / Risks

3. Architecture overview

Main components

Components communicate securely; metadata is cached and refreshed to avoid DB roundtrips on every request.

4. Workflow diagram (metadata → API)

Code
  +-----------------+      +-----------------+      +-----------------+
  | Admin UI / CI   | ---> | Metadata Store  | ---> | API Engine      |
  | (create/update) |      | (endpoints,     |      | (loads metadata |
  +-----------------+      | models, policies)|     |  and exposes)   |
                                 |                     |
                                 v                     v
                       +------------------+     +-------------------+
                       | Approval Workflow|     | Backend Connectors|
                       +------------------+     +-------------------+

5. Flowchart: runtime request processing

Code
Incoming HTTP Request
       |
       v
Match route to metadata (fast lookup)
       |
       v
Check API version & status (active? deprecated?)
       |
       v
Authorize (authN/authZ) using metadata policy
       |
       v
Bind and parse inputs (JSON/Query/Form) → apply type coercion
       |
       v
Validate against schema (required, types, regex)
       |
       v
If validation fail → return 4xx with structured error
       |
       v
Transform input (mapping, enrichment) if metadata specifies
       |
       v
Invoke backend connector(s) (DB, HTTP, function)
       |
       v
Transform backend response to API response schema
       |
       v
Apply response filters (masking, pagination, projection)
       |
       v
Apply caching / rate limits / metrics
       |
       v
Return response & write audit log

6. Metadata model (tables / JSON schema)

A compact normalized relational model (can be adapted to document DB). Keep metadata small and versioned.

Tables (conceptual)

Metadata example (JSON stored in ApiEndpoint.jsonDefinition)

Code
{
  "id": "customers.search.v1",
  "path": "/v1/customers",
  "method": "GET",
  "status": "active",
  "requestModel": "CustomerSearchRequest",
  "responseModel": "CustomerListResponse",
  "backend": {
    "type": "sql",
    "connectionId": "orders-db",
    "queryTemplate": "SELECT id, name, email FROM customers WHERE name ILIKE @name LIMIT @limit OFFSET @offset"
  },
  "mappings": [
    { "from": "query.name", "to": "@name", "transform": "trim|sqlLikePattern" },
    { "from": "query.limit", "to": "@limit", "default": 25 }
  ],
  "policies": {
    "auth": { "required": true, "roles": ["CustomerViewer"] },
    "rateLimit": { "perMinute": 120 }
  }
}

Store ApiModel definitions with JSON Schema v2020-12 so you can validate inputs and generate docs automatically.

7. Core engine components

Design the engine as modular middleware and runtime factories:

  1. Metadata Loader & Cache

    • Loads metadata at startup and refreshes via polling or push (webhooks). Keep a fast in-memory index keyed by (path, method, version).

    • Use an immutable versioned snapshot to avoid partial updates.

  2. Router / Endpoint Factory

    • Dynamically register routes in the web host based on metadata snapshot. (In ASP.NET Core you can register a catch-all route and dispatch inside a middleware using metadata lookup.)

  3. Request Binder

    • Bind incoming values (route, query, headers, body, cookies) into a canonical context object.

    • Coerce types and apply simple transform functions.

  4. Schema Validator

    • Use JSON Schema validators (NJsonSchema, JsonSchema.Net) to validate request/response shapes. Return structured errors that map to clients.

  5. Transform Engine

    • Allow lightweight transformations specified in metadata. Avoid raw JS execution unless you run in a sandbox (e.g., Jailed V8, Wasm). Prefer a safe expression language (JMESPath, CEL, JsonLogic) or a sandboxed script engine with strict whitelisting.

  6. Backend Connectors

    • Implement adapter pattern for SQL, NoSQL, HTTP, gRPC, function invocation, or message queue. Use parameterized queries and connection pooling. Secrets and connection strings must be stored encrypted and injected via Key Vault.

  7. Response Mapper & Filter

    • Map backend results to response model, apply projection, masking, pagination. Support fields= style projections if enabled.

  8. Policy Engine

    • Enforce authZ (roles/scopes), rate limits, quotas, CORS, and audit rules. Integrate with existing identity provider (OIDC, JWT) and RBAC.

  9. Audit & Metrics

    • Structured logging for request metadata (apiId, version, userId, latency, outcome). Generate trace IDs and integrate with distributed tracing (OpenTelemetry).

  10. Admin & Approval Workflow

8. Implementation details — ASP.NET Core patterns and snippets

Two approaches:

I recommend Approach B for runtime agility.

8.1 Dispatcher middleware (simplified)

Code
public class MetadataDispatcherMiddleware
{
    private readonly RequestDelegate _next;
    private readonly IMetadataService _metadata;
    private readonly IEngine _engine;

    public MetadataDispatcherMiddleware(RequestDelegate next, IMetadataService metadata, IEngine engine)
    {
        _next = next;
        _metadata = metadata;
        _engine = engine;
    }

    public async Task Invoke(HttpContext context)
    {
        var path = context.Request.Path.Value;
        var method = context.Request.Method;
        var ep = _metadata.Lookup(path, method, context.Request.Headers["Accept-Version"]);
        if (ep == null)
        {
            await _next(context);
            return;
        }

        try
        {
            var result = await _engine.HandleRequestAsync(context, ep);
            context.Response.StatusCode = result.StatusCode;
            context.Response.ContentType = "application/json";
            await context.Response.WriteAsync(JsonSerializer.Serialize(result.Payload));
        }
        catch (ApiException ex)
        {
            context.Response.StatusCode = ex.Status;
            await context.Response.WriteAsync(JsonSerializer.Serialize(new { error = ex.Message }));
        }
    }
}

Register at startup early, after authentication middleware.

8.2 Engine.HandleRequestAsync (high level)

8.3 Safe transformations

Prefer expression languages rather than arbitrary JS. Example: use JsonLogic or CEL for transforms and conditions.

Example transform metadata:

Code
"transform": {
  "stage": "post",
  "scriptType": "cel",
  "script": "response.items.map(i, { id: i.id, fullName: concat(i.firstName, ' ', i.lastName) })"
}

Evaluate in a sandboxed CEL engine with controlled imports.

8.4 Backend SQL example with parameter binding

Use parameterized SQL templates. Do not construct raw SQL via string concatenation.

Code
SELECT id, name, email FROM customers
WHERE (@name IS NULL OR name ILIKE @name)
ORDER BY id
LIMIT @limit OFFSET @offset

Template engine populates @name with sanitized parameter. For complex queries prefer stored procedures or prepared statements.

9. Validation, schema evolution and compatibility

10. Security (auth, authorization, input sanitation, rate limits)

Security must be first-class:

11. Performance, caching and scaling strategies

12. Observability, testing and CI/CD for metadata changes

13. Governance, audit, and approvals

14. Real-world use cases and examples

Example: expose GET /v1/active-users mapping to an internal analytics DB query defined in metadata — change the SQL or add filters without code release.

15. Limitations and when not to use this approach

Do not use metadata engine for:

Use engine for:

16. Roadmap: extending the engine

Ideas to extend:

17. Conclusion and next steps

A Metadata-Driven API Engine lets teams move fast while maintaining governance and observability. The core ideas are: