DOCSEARCHbyAlgolia
Submit Docs

Data API builder (DAB)

repository·main·Indexed 23 days ago

https://github.com/azure/data-api-builder

An open-source, no-code engine that creates secure REST, GraphQL, and MCP endpoints directly from existing databases, including Azure SQL, SQL Server, PostgreSQL, MySQL, Azure SQL Data Warehouse, and Azure Cosmos DB (NoSQL). It provides automated CRUD operations, role-based access control, and configuration-driven entity management via a JSON file. Available as a CLI tool, a containerized runtime, or as the Microsoft.DataApiBuilder.Core NuGet package for embedding in .NET applications.

Tokens
23K
Snippets
26
Records
147
Agent score
80%

What's inside Data API builder

  1. Overview of Data API builder for Azure Databases

    main

    Data API builder (DAB) is an open-source tool that provides modern REST, GraphQL, and MCP (Model Context Protocol) endpoints for your Azure Databases. It allows you to expose database objects like collections, tables, views, and stored procedures as APIs, enabling access from any platform or language.

    Key capabilities include:

    • REST: CRUD operations (POST, GET, PUT, PATCH, DELETE) with built-in support for filtering, sorting, and pagination.
    • GraphQL: Queries and mutations with relationship navigation, filtering, sorting, and pagination.
    • MCP (Model Context Protocol): Exposes database objects as tools compatible with AI clients like GitHub Copilot or Azure AI Foundry. This includes DML tools for CRUD and custom tools via stored procedures.
    • Security: Supports OAuth2/JWT authentication, Azure EasyAuth, role-based authorization via claims, and item-level security using a policy engine.

  2. What is Data API builder (DAB)?

    main

    Data API builder (DAB) is an open-source, no-code CRUD data API engine. It creates secure, full-featured REST, GraphQL, and MCP endpoints for your database. It is designed to run in a container and is compatible with various cloud environments (Azure, AWS, GCP) and on-premises setups.

    Supported Databases:

    • Azure SQL
    • SQL Server
    • SQLDW
    • Cosmos DB
    • PostgreSQL
    • MySQL

    Supported Endpoints:

    • REST
    • GraphQL
    • MCP

  3. Key capabilities of the Data API builder Core engine

    main

    The Microsoft.DataApiBuilder.Core engine provides several automated features for exposing database data:

    • REST API engine: Automatically generates CRUD endpoints (POST, GET, PUT, PATCH, DELETE) including filtering, sorting, and pagination.
    • GraphQL engine: Generates queries and mutations with filtering, sorting, pagination, and relationship navigation.
    • MCP tool support: Exposes DML and custom MCP tools for building SQL MCP Servers.
    • Authentication: Supports OAuth2/JWT and EasyAuth (Azure App Service / Static Web Apps).
    • Authorization: Provides role-based access control with item-level security via policy expressions.
    • Configuration-driven: Entities, permissions, and relationships are defined in a JSON config file without requiring code changes.
    • Multi-database: Ability to connect to multiple database types from a single instance.
    • Caching: Built-in response caching using FusionCache.

  4. Enable Hot Reloading for Configuration

    main

    Data API builder (dab) supports hot reloading of the configuration file to allow runtime updates without restarting the service.

    Conditions for Hot Reloading:

    • The application must be running in a local development scenario (non-hosted).
    • The Host Mode must be set to development.

    When these conditions are met, changes made to the configuration file (or referenced configuration files) will trigger an update to the RuntimeConfig object model automatically. This feature currently focuses on properties associated with the Runtime section of the configuration.

  5. How Data API builder works

    main

    DAB acts as a translation layer between HTTP requests and your database. The lifecycle follows these steps:

    1. Initialization: DAB reads the dab-config.json and queries the database for metadata to warm up the engine.
    2. Request Handling: When a client sends an HTTP request, DAB performs authorization.
    3. Query Translation: The QueryBuilder translates the request into a SQL query.
    4. Execution: The SQL query is submitted to the database, and the resulting data is returned to the client as JSON.

  6. When to use Azure support tickets vs GitHub Issues

    main

    Determine where to seek help based on the suspected source of the problem:

    Use GitHub Issues for:

    • Bugs in the Data API builder engine.
    • Questions about how to use Data API builder.
    • Feature requests for the engine.

    Use Azure Support Tickets (via the Azure Portal) for:

    • Hosting Service Issues: If you are hosting Data API builder containers using services like Azure Static Web Apps, Azure Container Apps, Azure Container Instances, Azure Kubernetes Services, or Azure Web Apps for Containers.
    • Database Issues: If you suspect the issue lies with your underlying database, such as Azure SQL, Azure Cosmos DB, Azure Database for PostgreSQL, Azure Database for MySQL, or Azure SQL Data Warehouse.

  7. Understand Lazy Schema Initialization in DAB

    main

    Data API Builder supports a 'hosted' mode where configuration is supplied via a POST /configuration request after the host has started.

    Because Hot Chocolate v16 builds schemas eagerly by default, you must ensure options.LazyInitialization = true is set. This defers schema construction until the first GraphQL request is received, ensuring the schema is built using the metadata provided via the /configuration endpoint rather than an empty or invalid state at startup.

    Warning: Do not disable lazy initialization if you are using the hosted configuration pattern.

  8. How caching works in Data API builder

    main

    Data API builder (DAB) uses an in-memory caching layer to store responses from read operations (REST GET requests and GraphQL Queries).

    Key Concepts

    • Scope: Caching is applied to read operations only. Follow-up reads on mutations (REST/GraphQL) are not cached.
    • Storage: Cache data is stored in-memory and is not persisted to disk. It is lost if the DAB process shuts down.
    • Cache Key Schema: Keys are generated using the pattern dataSourceName:queryText:queryParameters. This ensures uniqueness per data source and prevents collisions between different queries.
    • Cache Value: The stored value is the database response serialized as a JSON string.
    • Eviction: Entries are evicted when they reach their configured ttl-seconds or when the cache approaches its memory limit.
    • Offline Behavior: If the backend database goes offline, DAB will continue to return unexpired cache entries until their ttl-seconds has passed.

    Security and Privacy

    • Authorization: Authorization checks occur before cache interaction. If a request is unauthorized, it is rejected before the cache is even consulted.
    • Data Residency: Because the cache is in-memory and not distributed, it does not transmit data further; it simply serves what was received over the original TLS connection from the database.

  9. Understand the Application Name Telemetry token format

    main

    The telemetry token is appended to the Application Name in the connection string. It is computed once per data source at configuration load and remains constant for the lifetime of that data source to avoid breaking connection pooling.

    Token Shape: dab_<environment>_<version>+<context>||<runtime>|<entity>+

    Example: dab_oss_2.0.0+XXSX||110000M1M000MMMMMWMM|100?111001110?+

    Components:

    • dab_oss_ or dab_hosted_: The deployment marker.
    • <version>: The product version (Major.Minor.Patch).
    • +<context>||<runtime>|<entity>+: The payload wrapped in plus signs.
    • context: Identifies the connection type (Protocol, Object, Source, Role).
    • runtime: A fingerprint of the global runtime configuration.
    • entity: A fingerprint of the merged entity set (e.g., if any entity is a table, view, etc.).
    dab_oss_2.0.0+XXSX||110000M1M000MMMMMWMM|100?111001110?+

  10. Understand the DAB Health Endpoints

    main

    Data API Builder (DAB) provides two levels of health monitoring via HTTP endpoints:

    1. Engine Health (Root /): Provides a high-level status of the DAB engine itself, including the version and app name. This is the default endpoint used for basic connectivity checks.

    2. Comprehensive Health Report (/health): Provides a detailed diagnostic report for all enabled data sources and entities (REST and GraphQL). This endpoint checks if individual entities are responding within user-defined performance thresholds.

    Health Status Definitions

    TermDescription
    Status.HealthyThe system or specific check is functioning correctly.
    Status.UnhealthyThe system has a critical failure, an error occurred, or a performance threshold was exceeded.

    Global Health Calculation Logic

    • Healthy: All individual checks pass.
    • Unhealthy: At least one check fails (e.g., a database query fails or an entity response time exceeds the configured threshold).
    {
    "status": "Healthy",
    "version": "Major.Minor.Patch",
    "appName": "dab_oss_Major.Minor.Patch"
}

  11. Understand Health Report access and roles

    main

    Access to the comprehensive health report is controlled by the runtime.health.roles array. DAB uses three types of roles to evaluate access:

    • anonymous: The superset of all roles. If included in runtime.health.roles, all users (authenticated and custom) can view the report.
    • authenticated: Includes all authenticated users and all custom roles. If included, custom roles are validated.
    • custom-role: Specific non-system roles assigned via an identity provider. These must match the incoming user's role exactly.

    Important Behavior by Environment:

    Role MembershipDevelopment ModeProduction Mode
    Not configuredRuns Checks403 Forbidden
    AllowedRuns ChecksRuns Checks
    Not Allowed403 Forbidden403 Forbidden

  12. Dynamic GraphQL Schema Updates via Hot Reload

    main

    The GraphQLSchemaManager class is responsible for handling dynamic updates to the GraphQL schema when the configuration changes.

    When a hot-reload event is triggered due to changes in the entities defined in the configuration, the GraphQLSchemaManager:

    1. Validates the updated schema.
    2. Applies the updated schema to the running instance.

    Note: In local development scenarios, inconsistent behavior with active requests may occur during the hot-reload process, as concurrency is not managed for these local updates.