Configuration reference

{
  "$schema": "https://github.com/Azure/data-api-builder/releases/download/v2.1.5/dab.draft.schema.json",
  "data-source": {
    "database-type": "mssql",
    // Never a literal: @env() reads an environment variable, @akv() a Key Vault secret
    "connection-string": "@env('SQL_CONNECTION_STRING')"
  },
  "runtime": {
    "rest":    { "enabled": true, "path": "/api" },
    "graphql": { "enabled": true, "path": "/graphql", "allow-introspection": false },
    "mcp": {
      "enabled": true,
      "path": "/mcp",
      "description": "Product catalog for the Contoso storefront",   // sent to MCP clients as instructions
      "dml-tools": {
        "describe-entities": true,
        "read-records": true,
        "create-record": false,
        "update-record": false,
        "delete-record": false,                                     // a common production choice
        "execute-entity": true,
        "aggregate-records": { "enabled": true, "query-timeout": 30 }
      }
    },
    "host": {
      "mode": "production",                                         // development enables Simulator and richer errors
      "cors": { "origins": ["https://shop.contoso.com"], "allow-credentials": false },
      "authentication": {
        "provider": "EntraId",                                      // formerly AzureAd; default is Unauthenticated
        "jwt": {
          "audience": "api://contoso-catalog",
          "issuer": "https://login.microsoftonline.com/<tenant-id>/v2.0"
        }
      }
    },
    "cache":      { "enabled": true, "ttl-seconds": 30 },
    "pagination": { "default-page-size": 100, "max-page-size": 1000 },
    "telemetry":  { "application-insights": { "enabled": true, "connection-string": "@env('APPLICATIONINSIGHTS_CONNECTION_STRING')" } }
  },
  "entities": {
    "Product": {
      "source": { "object": "dbo.Product", "type": "table", "object-description": "Products for sale" },
      "fields": [
        { "name": "ProductID", "primary-key": true },
        { "name": "ListPrice", "alias": "price", "description": "Price in EUR" }   // DAB 2.x: replaces "mappings"
      ],
      "rest":    { "enabled": true, "path": "/products" },
      "graphql": { "enabled": true, "type": { "singular": "Product", "plural": "Products" } },
      "cache":   { "enabled": true, "ttl-seconds": 60, "level": "L1" },
      "permissions": [
        { "role": "anonymous",     "actions": [ { "action": "read", "fields": { "exclude": ["Cost", "Embedding"] } } ] },
        { "role": "catalogEditor", "actions": [ "create", "read", "update" ] }
      ],
      "relationships": {
        "category": {
          "cardinality": "one",
          "target.entity": "Category",
          "source.fields": ["CategoryID"],
          "target.fields": ["CategoryID"]
        }
      }
    },
    "Category": {
      "source": { "object": "dbo.Category", "type": "table" },
      "permissions": [ { "role": "anonymous", "actions": [ "read" ] } ],
      "relationships": {
        "products": { "cardinality": "many", "target.entity": "Product" }   // inferred from the foreign key
      }
    },
    "MyOrders": {
      "source": { "object": "dbo.vw_OrderSummary", "type": "view" },
      "fields": [ { "name": "OrderNo", "primary-key": true } ],            // views need a key (was "key-fields")
      "permissions": [
        {
          "role": "authenticated",
          "actions": [ { "action": "read",
                         "policy": { "database": "@item.CustomerOid eq @claims.oid" } } ]   // per-user rows
        }
      ]
    },
    "PlaceOrder": {
      "source": {
        "object": "dbo.usp_PlaceOrder",
        "type": "stored-procedure",
        "parameters": [ { "name": "CustomerID", "required": true, "description": "Buyer" } ]   // DAB 2.x array format
      },
      "rest":    { "methods": ["post"] },
      "graphql": { "operation": "mutation" },
      "mcp":     { "custom-tool": true },                                   // exposed as a named MCP tool
      "permissions": [ { "role": "authenticated", "actions": [ "execute" ] } ]
    }
  }
}

# Create the file (the connection string stays in an environment variable)
dab init --database-type mssql --connection-string "@env('SQL_CONNECTION_STRING')" --host-mode development

# Add entities: a table, a view with a key, a stored procedure
dab add Product   --source dbo.Product --permissions "anonymous:read"
dab add MyOrders  --source dbo.vw_OrderSummary --source.type view \
                  --fields.name OrderNo --fields.primary-key true --permissions "authenticated:read"
dab add PlaceOrder --source dbo.usp_PlaceOrder --source.type stored-procedure \
                  --permissions "authenticated:execute" --rest.methods "post" --graphql.operation "mutation"

# Evolve them
dab update Product  --relationship category --target.entity Category --cardinality one
dab update Product  --cache.enabled true --cache.ttl-seconds 60
dab configure --runtime.host.authentication.provider EntraId
dab configure --runtime.mcp.enabled true

# Check and run
dab validate                      # schema, permissions and database connectivity
dab start                         # REST /api, GraphQL /graphql, MCP /mcp on http://localhost:5000
dab start --mcp-stdio role:anonymous --config ./dab-config.json   # MCP over stdio for a local client

<Project DefaultTargets="Build">
  <Sdk Name="Microsoft.Build.Sql" Version="2.3.0" />

  <PropertyGroup>
    <Name>Contoso.Database</Name>
    <!-- Target platform: Sql170 = SQL Server 2025, SqlAzureV12 = Azure SQL Database,
         SqlDbFabric = SQL database in Fabric (preview) -->
    <DSP>Microsoft.Data.Tools.Schema.Sql.SqlAzureV12DatabaseSchemaProvider</DSP>
    <ModelCollation>1033, CI</ModelCollation>
    <RunSqlCodeAnalysis>True</RunSqlCodeAnalysis>
    <TreatTSqlWarningsAsErrors>False</TreatTSqlWarningsAsErrors>
  </PropertyGroup>

  <!-- Every .sql file under the folder is part of the model by default (globbing).
       Scripts that are not schema objects are taken out of the build and wired as deployment scripts. -->
  <ItemGroup>
    <Build Remove="Scripts\**\*.sql" />
    <PreDeploy  Include="Scripts\PreDeployment.sql" />
    <PostDeploy Include="Scripts\PostDeployment.sql" />
  </ItemGroup>

  <!-- Per-environment values, referenced in scripts as $(Environment) -->
  <ItemGroup>
    <SqlCmdVariable Include="Environment">
      <DefaultValue>dev</DefaultValue>
    </SqlCmdVariable>
  </ItemGroup>

  <!-- Database references as NuGet packages, for example the master database -->
  <ItemGroup>
    <PackageReference Include="Microsoft.SqlServer.Dacpacs.Azure.Master" Version="170.0.4" />
  </ItemGroup>
</Project>

<?xml version="1.0" encoding="utf-8"?>
<!-- publish/Production.publish.xml — deployment options, never credentials -->
<Project ToolsVersion="Current" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
  <PropertyGroup>
    <TargetDatabaseName>ContosoDb</TargetDatabaseName>
    <BlockOnPossibleDataLoss>True</BlockOnPossibleDataLoss>
    <DropObjectsNotInSource>False</DropObjectsNotInSource>
    <BlockWhenDriftDetected>True</BlockWhenDriftDetected>
    <RegisterDataTierApplication>True</RegisterDataTierApplication>
    <IncludeTransactionalScripts>True</IncludeTransactionalScripts>
  </PropertyGroup>
  <ItemGroup>
    <SqlCmdVariable Include="Environment">
      <Value>prod</Value>
    </SqlCmdVariable>
  </ItemGroup>
</Project>

name: Database CI/CD

on:
  pull_request:
    branches: [main]
    paths: ['database/**']
  push:
    branches: [main]
    paths: ['database/**']
  workflow_dispatch:

permissions:
  contents: read

jobs:
  build-and-test:
    runs-on: ubuntu-latest
    services:
      sql:                                            # throwaway SQL Server for integration tests
        image: mcr.microsoft.com/mssql/server:2025-latest
        env:
          ACCEPT_EULA: 'Y'
          MSSQL_SA_PASSWORD: ${{ secrets.CI_SA_PASSWORD }}
        ports: ['1433:1433']
    steps:
      - uses: actions/checkout@v7
      - uses: actions/setup-dotnet@v6
        with:
          dotnet-version: '10.0.x'
      - name: Build the model (validates every reference)
        run: dotnet build database/Contoso.Database.sqlproj -c Release
      - name: Deploy to the CI container and run tests
        run: |
          dotnet tool install -g microsoft.sqlpackage
          sqlpackage /Action:Publish \
            /SourceFile:database/bin/Release/Contoso.Database.dacpac \
            /TargetConnectionString:"Server=localhost;Database=CiDb;User ID=sa;Password=${{ secrets.CI_SA_PASSWORD }};TrustServerCertificate=True"
          # run unit and integration tests here (for example tSQLt via sqlcmd)
      - uses: actions/upload-artifact@v7
        with:
          name: dacpac
          path: database/bin/Release/Contoso.Database.dacpac

  deploy-production:
    if: github.ref == 'refs/heads/main' && github.event_name != 'pull_request'
    needs: build-and-test
    runs-on: ubuntu-latest
    environment: production                          # required reviewers, branch rule, env secrets
    permissions:
      contents: read
      id-token: write                                # OIDC token for azure/login: no client secret
    steps:
      - uses: actions/checkout@v7
      - uses: actions/download-artifact@v8
        with:
          name: dacpac
      - uses: azure/login@v3
        with:
          client-id: ${{ vars.AZURE_CLIENT_ID }}
          tenant-id: ${{ vars.AZURE_TENANT_ID }}
          subscription-id: ${{ vars.AZURE_SUBSCRIPTION_ID }}
      - name: Review the plan
        uses: azure/sql-action@v2
        with:
          connection-string: ${{ secrets.PROD_SQL_CONNECTION_STRING }}   # Authentication=Active Directory Default
          path: ./Contoso.Database.dacpac
          action: DeployReport
      - name: Publish
        uses: azure/sql-action@v2
        with:
          connection-string: ${{ secrets.PROD_SQL_CONNECTION_STRING }}
          path: ./Contoso.Database.dacpac
          action: Publish
          arguments: '/p:BlockOnPossibleDataLoss=true'

# .github/CODEOWNERS — the last matching pattern wins
*                      @contoso/app-team
/database/             @contoso/dba-team
/database/Security/    @contoso/dba-team @contoso/security
/.github/workflows/    @contoso/platform-team

# Contoso database — instructions for GitHub Copilot

## Platform
- Azure SQL Database, compatibility level 170. Do not use features that need PREVIEW_FEATURES.

## T-SQL conventions
- Schema-qualify every object (dbo.Customer). List columns explicitly; never SELECT *.
- New procedures: CREATE OR ALTER, SET NOCOUNT ON, SET XACT_ABORT ON, TRY...CATCH with THROW.
- Store semi-structured data in json columns; add a JSON index for queried paths.
- Embeddings: vector(1536) from the external model Embed3Small; search with VECTOR_SEARCH
  and SELECT TOP (N) WITH APPROXIMATE, never the deprecated TOP_N parameter.

## Security
- Parameterize all input; dynamic SQL only through sp_executesql with parameters.
- Never generate GRANT ... TO public, logins with passwords, credentials or connection strings.

## Project
- Schema lives in database/ as an SDK-style SQL project; one object per file under Schema/ObjectType/.
- Reference data goes in Scripts/PostDeployment.sql as an idempotent MERGE.

---
applyTo: "database/**/*.sql"
excludeAgent: "code-review"
---
# Rules for database object scripts
- File name equals the object name: database/dbo/Tables/Customer.sql.
- Tables need a named primary key constraint (PK_<Table>) and named defaults (DF_<Table>_<Column>).
- Foreign key columns get a nonclustered index.

---
description: Generate a stored procedure that follows the team template
agent: agent
tools: ['mssql/*']
---
Create a stored procedure named ${input:procName:usp_VerbNoun} that ${input:purpose}.

Follow .github/copilot-instructions.md. Include:
- SET NOCOUNT ON and SET XACT_ABORT ON
- an explicit transaction for any data modification, inside TRY...CATCH with THROW
- a header comment describing parameters and result sets

Write it to database/dbo/StoredProcedures/${input:procName}.sql.

{
  "inputs": [
    { "id": "catalog_token", "type": "promptString", "description": "Token for the catalog API", "password": true }
  ],
  "servers": {
    // SQL MCP Server started by VS Code as a child process (local development)
    "contoso-sql-local": {
      "type": "stdio",
      "command": "dab",
      "args": ["start", "--mcp-stdio", "role:anonymous", "--config", "${workspaceFolder}/api/dab-config.json", "--LogLevel", "error"]
    },
    // SQL MCP Server already running in Azure Container Apps (HTTP transport)
    "contoso-sql-remote": {
      "type": "http",
      "url": "https://contoso-catalog.azurecontainerapps.io/mcp",
      "headers": { "Authorization": "Bearer ${input:catalog_token}" }   // prompted, never stored
    },
    // A Fabric data agent published as an MCP server (lakehouse or warehouse data)
    "fabric-sales-agent": {
      "type": "http",
      "url": "<MCP server URL copied from the data agent's Settings → Model Context Protocol tab>"
    }
  }
}