Azure / Azure/data-api-builder
[Enh]: support the `@db()` replacement function for metadata.
- Dominant language
- C#
- Stars
- 1.5k
- Forks
- 370
- Avg merge
- 3d 17h
- Merged PRs (30d)
- 8
Description
## What?
In the same way `@env()` and `@akv()` allow string replacements in the DAB configuration, introduce `@db()` which reads database metadata.
This allows DAB to dynamically pull descriptions or other extended properties directly from the connected database, ensuring configuration remains synchronized with database documentation.
## Behavior
* Resolves during configuration load after the database connection is established.
* Supported only when `data-source.type` is `mssql`.
* Applicable to any string property.
* Never writes back to the database.
* If the referenced extended property is missing, it resolves to an empty string (`''`).
* If the referenced database object, column, or parameter is missing, log a warning.
* If a non-MSSQL data source is in scope, log a warning and skip resolution.
## Syntax
```text
@db(':', '')
```
Where:
* `` identifies the database, schema, object, column, or parameter.
* `` identifies the extended property name.
* `` identifies the DAB-supported object type.
The second parameter is required for absolute object-level, column-level, and parameter-level lookups because SQL Server extended properties require the object type. DAB cannot safely infer whether an object is a table, view, or stored procedure from the object name.
The second parameter is optional only for database-scope and schema-scope lookups, or when DAB can infer the object and type from the current configuration context.
## Supported object types
```text
table
view
stored-procedure
```
When querying SQL Server, DAB maps `stored-procedure` to SQL Server's extended property level-1 type `procedure`.
## Path support
V1 supports simple multipart paths only.
Supported:
```text
dbo.Author
dbo.Author.Id
dbo.GetCustomer.@CustomerId
```
Not supported in V1:
```text
[dbo].[Author]
[dbo].[My.Table]
[My.Schema].[Author]
"dbo"."Author"
```
Bracketed or quoted identifiers are not supported in `@db()` paths in V1.
DAB must not infer default schema. If an object path is provided, the schema must be included.
## Path parsing
`:` separates the path from the extended property name.
`.` separates schema, object, and member names.
`@` at the beginning of the final path segment identifies the member as a parameter.
Examples:
```text
@db(':CompanyName')
@db('dbo:DisplayName')
@db('dbo.Author:MS_Description', 'table')
@db('dbo.Author.Id:MS_Description', 'table')
@db('dbo.GetCustomer.@CustomerId:MS_Description', 'stored-procedure')
```
For parameter lookups, the `@` prefix is DAB syntax only. DAB must remove the leading `@` before querying SQL Server.
Columns with names beginning with `@` are not supported by `@db()` path syntax in V1.
## Absolute examples
```text
@db(':CompanyName') // database-scope
@db('dbo:DisplayName') // schema-scope
@db('dbo.Author:MS_Description', 'table') // table-level
@db('dbo.Author.Id:MS_Description', 'table') // table column-level
@db('dbo.AuthorView:MS_Description', 'view') // view-level
@db('dbo.AuthorView.Id:MS_Description', 'view') // view column-level
@db('dbo.GetCustomer:MS_Description', 'stored-procedure') // stored procedure-level
@db('dbo.GetCustomer.@CustomerId:MS_Description', 'stored-procedure') // parameter-level
```
## Contextual resolution
When `@db()` is used inside an entity, field, or parameter configuration scope, DAB must infer the object and type when enough context is available.
Because `@db()` resolves after `@env()` and `@akv()`, `source.object` and `source.type` may use `@env()` or `@akv()`.
### Database-scope
Database-scope lookups require no source and no object type.
```text
@db(':CompanyName')
```
This is valid anywhere in configuration.
### Schema-scope
Schema-scope lookups require a schema name and no object type.
```text
@db('dbo:DisplayName')
```
This is valid anywhere in configuration.
### Entity-scope
Inside an entity with `source.object` and `source.type`, this:
```text
@db(':MS_Description')
```
resolves to the current entity source object.
Example:
```json
{
"Author": {
"description": "@db(':MS_Description')",
"source": {
"object": "dbo.Author",
"type": "table"
}
}
}
```
Resolves as:
```text
@db('dbo.Author:MS_Description', 'table')
```
### Field-scope
Inside a field, this:
```text
@db(':MS_Description')
```
resolves to the current field.
Example:
```json
{
"Author": {
"source": {
"object": "dbo.Author",
"type": "table"
},
"fields": {
"Id": {
"description": "@db(':MS_Description')"
}
}
}
}
```
Resolves as:
```text
@db('dbo.Author.Id:MS_Description', 'table')
```
Inside an entity, this:
```text
@db('.Id:MS_Description')
```
resolves to the named field on the current entity source object.
### Parameter-scope
Inside a parameter, this:
```text
@db(':MS_Description')
```
resolves to the current parameter.
Example:
```json
{
"GetCustomer": {
"source": {
"object": "dbo.GetCustomer",
"type": "stored-procedure"
},
"parameters": {
"CustomerId": {
"description": "@db(':MS_Description')"
}
}
}
}
```
Resolves as:
```text
@db('dbo.GetCustomer.@CustomerId:MS_Description', 'stored-procedure')
```
Inside a stored procedure entity, this:
```text
@db('.@CustomerId:MS_Description')
```
resolves to the named parameter on the current stored procedure source object.
## Resolution order
`@db()` is resolved after `@env()` and `@akv()`.
```mermaid
sequenceDiagram
actor Engine as Engine
participant ConfigInMem as ConfigInMem
participant Environment as Environment
participant AKV as AKV
participant DB as Database
participant Config as ConfigFile
Engine ->> Config: Load Config
Config -->> Engine: Config Data
Engine ->> ConfigInMem: Create In-Memory Config
Note over Engine: Perform Config Replacements
activate Engine
ConfigInMem -->> Engine: Parse @env Values
Engine ->> Environment: Get
Environment -->> Engine: Values
Engine ->> ConfigInMem: Replace @env Values
deactivate Engine
activate Engine
ConfigInMem -->> Engine: Parse @akv Values
Engine ->> AKV: Request
AKV -->> Engine: Secrets
Engine ->> ConfigInMem: Replace @akv Values
deactivate Engine
activate Engine
ConfigInMem -->> Engine: Parse @db Values
Engine ->> DB: Query Metadata
DB -->> Engine: Extended Property Values
Engine ->> ConfigInMem: Replace @db Values
deactivate Engine
Engine ->> Engine: Start
```
## SQL Server mapping
`@db()` maps to SQL Server's typed extended property hierarchy.
SQL Server requires:
```text
property,
level0type,
level0name,
level1type,
level1name,
level2type,
level2name
```
DAB should map the path and object type into that structure.
| Scope | Call |
| ---------------- | ----------------------------------------------------------------------- |
| Database | `@db(':CompanyName')` |
| Schema | `@db('dbo:DisplayName')` |
| Table | `@db('dbo.Author:MS_Description', 'table')` |
| Table column | `@db('dbo.Author.Id:MS_Description', 'table')` |
| View | `@db('dbo.AuthorView:MS_Description', 'view')` |
| View column | `@db('dbo.AuthorView.Id:MS_Description', 'view')` |
| Stored procedure | `@db('dbo.GetCustomer:MS_Description', 'stored-procedure')` |
| Parameter | `@db('dbo.GetCustomer.@CustomerId:MS_Description', 'stored-procedure')` |
## Normalization
DAB must normalize lookup requests before deduplication and resolution.
Normalization rules:
* Trim leading and trailing whitespace from path, property, object type, and each path segment.
* Preserve identifier casing.
* Do not lowercase paths.
* Do not infer default schema.
* Convert `stored-procedure` to `procedure` only for SQL Server lookup.
* Remove the leading `@` from parameter names before querying SQL Server.
* Preserve the original expression location for warning messages.
The deduplication key is:
```text
data-source + normalized-path + normalized-property + normalized-object-type
```
## Batch resolution
DAB should not query metadata one token at a time.
DAB must:
1. Parse all `@db()` expressions.
2. Resolve contextual expressions into absolute lookup requests.
3. Normalize lookup requests.
4. Deduplicate lookup requests.
5. Batch lookup requests by data source.
6. Resolve metadata using set-based queries where possible.
7. Cache results by deduplication key.
DAB should prefer set-based queries against `sys.extended_properties` and related catalog views instead of per-token calls to `fn_listextendedproperty`.
If batch metadata resolution fails, startup fails because configuration replacement could not complete.
## Example configuration
```json
{
"entities": {
"Author": {
"description": "@db(':MS_Description')",
"source": {
"object": "dbo.Author",
"type": "table"
},
"fields": {
"Id": {
"description": "@db(':MS_Description')"
}
}
},
"AuthorView": {
"description": "@db(':MS_Description')",
"source": {
"object": "dbo.AuthorView",
"type": "view"
},
"fields": {
"Id": {
"description": "@db(':MS_Description')"
}
}
},
"GetCustomer": {
"description": "@db(':MS_Description')",
"source": {
"object": "dbo.GetCustomer",
"type": "stored-procedure"
},
"parameters": {
"CustomerId": {
"description": "@db(':MS_Description')"
}
}
},
"Metadata": {
"description": "@db(':CompanyName')"
}
}
}
```
## Sample SQL
This sample shows the equivalent single-lookup shape. Implementation should prefer batched lookup when multiple `@db()` expressions are present.
```sql
DECLARE @property NVARCHAR(255) = N'MS_Description';
DECLARE @schema NVARCHAR(255) = N'dbo';
DECLARE @object NVARCHAR(255) = N'Author';
DECLARE @objectType NVARCHAR(255) = N'table';
DECLARE @member NVARCHAR(255) = N'Id';
DECLARE @memberType NVARCHAR(255) = N'column';
SELECT value
FROM fn_listextendedproperty (
@property,
N'schema',
NULLIF(@schema, N''),
CASE @objectType
WHEN N'stored-procedure' THEN N'procedure'
ELSE @objectType
END,
NULLIF(@object, N''),
NULLIF(@memberType, N''),
NULLIF(@member, N'')
);
```
## Error policy
Structural errors fail validation.
Runtime metadata misses warn or resolve empty.
Structural errors include:
* Invalid `@db()` syntax.
* Unsupported object type.
* Missing required object type for an absolute object, column, or parameter lookup.
* Contextual lookup where DAB cannot infer the current object or type.
* Bracketed or quoted identifiers in the path.
* Column lookup against an unsupported object type.
* Parameter lookup against an unsupported object type.
Runtime metadata misses include:
* Referenced database object does not exist.
* Referenced column does not exist.
* Referenced parameter does not exist.
* Referenced extended property does not exist.
DAB should warn when:
* The referenced database object does not exist.
* The referenced column or parameter does not exist.
* The data source is not MSSQL.
DAB should not warn when:
* The target exists, but the requested extended property does not exist.
Missing extended properties resolve to an empty string.
## Hot reload
On configuration reload, DAB must re-run `@db()` resolution.
The previous `@db()` resolution cache must be invalidated during reload.
Reload behavior should match a full configuration reload, not an incremental metadata refresh.
## Observability
DAB should create an OTEL activity around `@db()` metadata resolution.
Suggested activity name:
```text
DAB.Config.ResolveDatabaseMetadata
```
Suggested tags:
```text
db.system
data_source.name
lookup.count
lookup.batch_count
lookup.cache_hit_count
lookup.cache_miss_count
warning.count
```
Suggested metrics:
| Metric | Unit |
| ----------------------------------------- | ------------ |
| `dab.config.db_metadata.lookup.count` | count |
| `dab.config.db_metadata.batch.count` | count |
| `dab.config.db_metadata.cache.hit.count` | count |
| `dab.config.db_metadata.cache.miss.count` | count |
| `dab.config.db_metadata.warning.count` | count |
| `dab.config.db_metadata.duration` | milliseconds |
## Considerations
1. Keep lookups read-only.
2. Use the least database permissions required to read metadata.
3. Avoid object-name heuristics. Object type must be explicit or inherited from a known DAB source type.
4. Preserve the syntax in a way that does not prevent future provider support, but scope V1 behavior to MSSQL only.
5. V1 intentionally excludes bracketed and quoted paths. Future support should not be blocked by the parser design.
Contributor guide
Assessment
This issue has not been assessed yet.