Azure / Azure/data-api-builder

[Enh]: Allow tables with unsupported data type columns.

Open
#3,181 0 comments 1 reaction 1 assignee Claimed by @RubenCerna2079 View on GitHub
2.2 mssql semantic usability
Dominant language
C#
Stars
1.5k
Forks
370
Avg merge
3d 17h
Merged PRs (30d)
8

Description

## What?

Support entities in MSSQL that contain columns with SQL data types Data API builder cannot represent.

- When enabled, DAB silently omits unsupported columns.

- This is an opt in feature and is disabled by default.

- This feature applies exclusively to MSSQL data sources.

- Support for other data sources can be implemented later.

## Why?

Data API builder does not support every SQL Server data type today and may never support all of them. In brownfield schemas, unsupported types can block adoption entirely because DAB fails at startup.

- DAB remains a contract engine. The API surface is authoritative, not the database schema. Omitted columns are treated as if they were never part of the exposed model.

## How?

```mermaid
sequenceDiagram
participant DAB
participant Config
participant DB as MSSQL

DAB->>Config: Load Configuration
Config-->>DAB: Configuration

DAB->>DB: Query metadata
DB-->>DAB: Metadata

rect rgba(255, 255, 0, 0.15)
Note right of DAB: This feature
DAB->>DAB: Omit columns
end

DAB->>DAB: Validate & start
```

## Configuration

The feature can be enabled at two levels. The entity level setting always wins. If the entity level setting is omitted or set to null, the global default applies.

### Global default

```jsonc
{
"data-source": {
"database-type": "mssql",
"connection-string": "...",
"options": {
"set-session-context": true,
"omit-unsupported-columns-default": false // new (default: false)
}
}
}
```

#### Command line

```
dab configure --data-source.options.omit-unsupported-columns-default true
dab configure --data-source.options.omit-unsupported-columns-default false
```

### Entity level override

An entity can override the global default in either direction using `omit-unsupported-columns`.

If the property is omitted or explicitly set to null, the entity inherits from the global default.

```jsonc
{
"entities": {
"Table1": {
"source": {
"object": "dbo.SpatialData",
"type": "table",
"omit-unsupported-columns": true // new (default: inherit)
}
},
"Table2": {
"source": {
"object": "dbo.SafeTable",
"type": "table",
"omit-unsupported-columns": false // new (default: inherit)
}
},
"Table3": {
"source": "dbo.InheritedTable"
}
}
}
```

#### Command line

```
dab add Table1 --data-source.options.omit-unsupported-columns true
dab update Table1 --data-source.options.omit-unsupported-columns true
```

### Rules

1. If you omit a column, you cannot reference it anywhere in configuration.
2. `omit-unsupported-columns-default` requires `data-source.database-type: mmsql`
3. `omit-unsupported-columns` requires `data-source.database-type: mmsql`

If an omitted field exists in any of these configurations, dab will not start. This will, in part, automatically validate because the field will not appear present in the metadata. Having said that, we need to think through how to ensure logging makes this clear to the developer.

* key-fields
* primary key inference
* fields array
* permission policies (any `@item.field` usage)
* permission include fields
* permission exclude fields

### REST and GraphQL schema

Omitted columns do not appear in the GraphQL schema. They are never visible to clients.

> [!WARNING]
> If DAB later adds support for a previously unsupported type, the column may appear in the schema in a future upgrade. Developers who depend on stable omission should version lock DAB.

### SELECT

Omitted columns are never projected and are never fetched from the database.

### INSERT and UPDATE

Omitted columns are never included in write operations.

> [!NOTE]
> The database default or NULL supplies the value. If there is no default, write operations will fail. If the database rejects the write due to these constraints, that error is returned at runtime.

## Stored procedures

This feature omits stored procedure result set columns just like table/view columns.

- If a stored procedure parameter type is unsupported, startup fails.

## Logging and diagnostics

All omissions are logged at Warning level with structured event data.

All startup failures due to references to omitted columns are Error level.

- Because developers can modify database schema after deployment, omissions and failures may occur on subsequent restarts if new unsupported columns are introduced.

## MCP implications

For SQL MCP Server, `describe_entities` reflects the post omission model.

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.