Data API Builder with SQL Server

Data API Builder (DAB) is an open-source tool from Microsoft that automatically generates secure REST and GraphQL endpoints directly from your database tables — no application code required. In this article you will see how to spin up a SQL Server instance with Docker, seed a Todos table, configure DAB, and call the resulting API from a browser in under ten minutes.

What is Data API Builder?

DAB sits between your database and your frontend (or any HTTP client). You point it at a connection string, tell it which tables or views to expose, declare who may access them, and DAB handles every CRUD operation automatically. It supports SQL Server, PostgreSQL, MySQL, Azure SQL, and Azure Cosmos DB.

Key capabilities:

1. Auto-generated REST endpoints (/api/{Entity}) for GET, POST, PUT, PATCH, DELETE
2. Auto-generated GraphQL endpoint (/graphql) with full query and mutation support
3. Built-in Swagger / OpenAPI documentation at /swagger
4. Role-based permissions — anonymous, authenticated, or custom roles
5. Zero application code — configuration is a single JSON file

Prerequisites

1. Docker Desktop (used to run SQL Server locally)
2. .NET 8 SDK or newer
3. Data API Builder CLI — installed in the next step

Step 1 — Install the DAB CLI

Install DAB as a global .NET tool:

If you already have it installed, update instead:

Verify the installation:

Step 2 — Start SQL Server with Docker Compose

Rather than pulling the image manually, a docker-compose.yaml file keeps the configuration repeatable. Create the file shown below in your project folder, then start the container with one command.

Wait a few seconds for the engine to become ready, then verify it is running:

Step 3 — Create the Database and Table

Create the TodosDb database and the dbo.Todos table, then insert sample rows:

If you prefer a SQL client (SSMS, Azure Data Studio, or DataGrip), open a connection to localhost,1433 with user sa and run the script directly.

Step 4 — Initialize the DAB Configuration

Navigate to your project folder and run dab init. The command creates a dab-config.json file that DAB reads at startup.

--host-mode Development enables the Swagger UI and relaxed error messages. Use Production when deploying to a real environment.

Step 5 — Add the Todo Entity

Register the dbo.Todos table as a DAB entity named Todo and allow all operations without authentication (suitable for a local demo):

The anonymous:* permission grants create, read, update, and delete to any caller. For a production setup, replace anonymous with a named role and restrict the allowed actions accordingly.

Step 6 — Start DAB

Expected output:

DAB is now running and your API is live.

Step 7 — Test the API

REST — List All Todos

Open your browser or use curl:

Response:

REST — Create a New Todo

REST — Update a Todo

REST — Delete a Todo

GraphQL — Query

Swagger UI

Browse the interactive documentation at http://localhost:5000/swagger/index.html to explore and test every endpoint without writing any client code.

How the Routing Works

DAB follows a predictable URL pattern for REST:

Full Example — dab-config.Development.json

This is the complete DAB configuration file generated for the TodosDb project. It includes the data source connection string, REST/GraphQL/MCP runtime settings, CORS, host mode, and the Todo entity definition.

Key Concepts Summary

1. dab init — creates dab-config.json with the database type, connection string, and host mode.
2. dab add <EntityName> — registers a table or view as an API entity. The entity name becomes the URL segment (/api/Todo).
3. permissions — controls who can call which operations. Roles map to Azure Static Web Apps or Entra ID identities in production; anonymous skips authentication entirely.
4. host-mode Development — exposes Swagger UI, verbose error messages, and allows unauthenticated introspection. Switch to Production before deploying.
5. MCP endpoint (/mcp) — Model Context Protocol support, allowing AI agents and tools to query the API schema and data automatically.

Running DAB and SQL Server Together with Docker Compose

Running DAB locally with the CLI is fine during development, but for a reproducible, shareable setup — or for a staging environment — Docker Compose is the right tool. It lets you declare both SQL Server and DAB as services in a single YAML file, wire them together in an isolated network, and start the whole stack with one command.

Why localhost Breaks Inside Docker

When DAB runs as a Docker container, localhost inside that container refers to the container itself — not to your host machine or to the SQL Server container. This is the most common reason DAB fails to connect when moved to Docker: a config that works perfectly on the host breaks the moment it is containerised.

Docker Compose automatically creates a shared network and assigns each service a DNS name equal to the service name defined in docker-compose.yaml. If the SQL Server service is called sqlserver, then the correct hostname for the connection string is sqlserver.

Using @env() to Inject the Connection String

Hardcoding a different connection string for Docker means the config breaks locally. DAB solves this cleanly with the @env('VARIABLE_NAME') syntax: anywhere a string value appears in dab-config.json, you can replace it with an env-var reference and DAB will substitute the actual value at startup.

Change the connection-string field to:

Now the same config file works in every environment. You supply a different value for SQL_CONNECTION_STRING depending on where you run it:

1. Local CLI: set the variable in your shell before calling dab start
2. Docker Compose: set it under the environment: key of the DAB service
3. Azure Container Apps / App Service: set it as an application setting

PowerShell (local):

Bash / Linux (local):

The Complete docker-compose.yaml

The file below declares two services. SQL Server 2025 starts first; DAB waits until the database port is accepting connections before it starts, then receives its connection string through an environment variable that points to the SQL Server container by service name.

Notable points in this file:

1. healthcheck with TCP probe — instead of calling sqlcmd (whose path differs between SQL Server versions), a lightweight Bash TCP redirect (</dev/tcp/localhost/1433) checks whether the port is open. This works regardless of the SQL Server version or tool installation.
2. depends_on: condition: service_healthy — DAB will not start until the SQL Server healthcheck passes. Without this, DAB often crashes on startup because it tries to connect before SQL Server is ready.
3. Server=sqlserver,1433 — the hostname is sqlserver, the Docker Compose service name. Using localhost here would fail.
4. restart: on-failure — if DAB does start before SQL Server despite the healthcheck (e.g. on a slow machine), it will automatically retry.
5. volume mount — ./dab-config.Development.json:/App/dab-config.json maps the local config file into the container at the path DAB expects.

The Complete dab-config.Development.json

This is the final configuration file with the @env() substitution applied. The connection string is no longer hardcoded; it is resolved from the environment at runtime.

Starting the Stack

Docker pulls the images (first run only), creates the shared network, starts SQL Server, waits for the TCP healthcheck to pass, then starts DAB. After about 30–40 seconds on a typical machine, DAB is ready at http://localhost:5000.

Check both containers are running:

Follow DAB logs in real time:

Look for Successfully completed runtime initialization and Now listening on: http://[::]:5000 to confirm a successful startup.

Tear the stack down (data is preserved in the named volume):

Tear down and delete the SQL Server data volume too:

Using a .env File to Avoid Secrets in YAML

Putting a password directly in docker-compose.yaml is convenient for demos but should be avoided in shared or version-controlled projects. Docker Compose reads a .env file in the same directory automatically.

Create .env:

Update docker-compose.yaml to reference the variable:

Add .env to .gitignore so the password is never committed to source control.

How All the Pieces Fit Together

Reference

Microsoft Learn — Quickstart: Use Data API builder with SQL

Microsoft Learn — DAB Configuration File Reference

Microsoft Learn — Run Data API builder in a Docker container