How can I automatically expose all the tables in my database?

[yorek]

If I have a database with a lot of tables, and I want to have them all published as API, what is the fastest way to put together the configuration file?

[yorek]

There is no automation for automatically taking all tables (or views or stored procedures) from a database and automatically at them to the DAB configuration file. If you're using Azure SQL or SQL Server, you can generate the entities section of the configuration file using the following sample T-SQL (works on SQL Server 2022 or Azure SQL only as it uses the new JSON_OBJECT and JSON_ARRAY functions)

/*
    Table to publish as API
*/
drop table if exists #t;
select         
    [name] as entity_name,
    [name] as table_name,
    object_id,
    schema_id
into
    #t
from 
    sys.tables where [name] not in ('AdventureWorksDWBuildVersion', 'DatabaseLog', 'NewFactCurrencyRate')
;
-- select * from #t;

/*
    Find many-to-one relationships between tables
*/
drop table if exists #r;
select      
    t.object_id,
    object_name(t.object_id) as entity,
    referenced_object_id,  
    object_name(referenced_object_id) as [target.entity],
    cast(iif(referenced_object_id is null, null, 'one') as varchar(10)) as cardinality,
    json_array((
                select string_agg([name], ',') 
                from sys.foreign_key_columns fkc 
                inner join sys.columns c on 
                    fkc.parent_object_id = c.object_id and fkc.parent_column_id = c.column_id 
                where 
                    fk.object_id = fkc.constraint_object_id
                )) as [source.fields],
    json_array((
                select string_agg([name], ',') 
                from sys.foreign_key_columns fkc 
                inner join sys.columns c on 
                    fkc.referenced_object_id = c.object_id and fkc.referenced_column_id = c.column_id 
                where 
                    fk.object_id = fkc.constraint_object_id
    )) as [target.fields]
into    
    #r
from
    #t t 
left join  
    sys.foreign_keys fk on fk.parent_object_id = t.object_id
;
--select * from #r;

/*
    Add inverse, one-to-many, relationships
*/
insert into 
    #r 
select 
    referenced_object_id as object_id,
    [target.entity] as entity,
    object_id as referenced_object_id, 
    entity as [target.entity],
    'many' as cardinality,
    [target.fields] as [source.fields],
    [source.fields] as [target.fields] 
from 
    #r
where   
    referenced_object_id is not null
;
--select * from #r;

/*
    Build relationship JSON config for each entity
*/
drop table if exists #jr;
select
    object_id,    
    entity,
    json_query(( '{' + (string_agg(
        '"' + [target.entity] + '": ' + 
            json_object(
                    'cardinality': cardinality,
                    'target.entity': [target.entity],
                    'source.fields': json_query([source.fields]),
                    'target.fields': json_query([target.fields])
            )
        ,
        ','
        )
    ) + '}')) as relationships
into
    #jr
from
    #r
group by
    object_id,
    entity
;
--select * from #jr;

/*
    Build complete JSON config for each entity
*/
drop table if exists #c;
select 
    [entity_name] as entity,
    json_object(
        'source': quotename(schema_name(schema_id)) + '.' + quotename(table_name),
        'permissions': json_array(
            json_object(
                'role': 'anonymous',
                'actions': json_array(json_object('action':'*'))
            )
        ),
        'relationships': json_query(isnull(r.relationships, '{}'))
    ) as config
into
    #c
from 
    #t t
left outer join
    #jr r on t.object_id = r.object_id
;
--select * from #c;

/*
    Create the final entities config section
*/
select 
    json_object('entities': json_query('{' + string_agg('"' + entity + '": ' + config, ',') + '}')) as entities_config
from
    #c

[yorek]

If anyone else has a SQL script that can work with the other supported databases, please add it here :)

[CalvinQuark]

For another take on this, here's my T-SQL implementation and the C# console app that invokes it to update the config files. At present, it only includes tables. Views and SPROCs have not been incorporated yet.

This T-SQL scalar-valued function returns a string containing the dab-config.json file's entities node as JSON. It positions system-versioned temporal tables in the History schema after the primary tables. And it excludes tables such as dbo.sysdiagrams.

/*
    SELECT Utility.f_DabAllEntitiesConfig(DEFAULT) AS [Azure Data API Builder All-Entities-Config];
*/
CREATE FUNCTION Utility.f_DabAllEntitiesConfig (
    @Role AS SYSNAME = N'admin'  -- N'anonymous'
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @Config AS NVARCHAR(MAX);
    DECLARE @History AS NVARCHAR(MAX);
    DECLARE @CrLf AS NCHAR(2) = Utility.f_CrLf();
    DECLARE @CrLfComma AS NCHAR(3) = N',' + @CrLf;

    WITH cte_a AS (
        SELECT s.name AS SchemaName, t.name AS TableName
        FROM sys.tables AS t
        JOIN sys.schemas AS s ON t.schema_id = s.schema_id
    )
    SELECT @Config = STRING_AGG(Utility.f_DabEntityConfig(a.SchemaName, a.TableName, @Role), @CrLfComma) WITHIN GROUP (ORDER BY a.SchemaName, a.TableName)
    FROM cte_a AS a
    WHERE CONCAT(a.SchemaName, N'.', a.TableName) NOT LIKE N'dbo.sys%'
      AND a.SchemaName <> N'History';
    
    WITH cte_a AS (
        SELECT s.name AS SchemaName, t.name AS TableName
        FROM sys.tables AS t
        JOIN sys.schemas AS s ON t.schema_id = s.schema_id
    )
    SELECT @History = STRING_AGG(Utility.f_DabEntityConfig(a.SchemaName, a.TableName, @Role), @CrLfComma) WITHIN GROUP (ORDER BY a.SchemaName, a.TableName)
    FROM cte_a AS a
    WHERE CONCAT(a.SchemaName, N'.', a.TableName) NOT LIKE N'dbo.sys%'
      AND a.SchemaName = N'History';

    SET @Config = IIF(@History <> N'', @Config + @CrLfComma + @History, @Config);

    SET @Config = CONCAT_WS(@CrLf, N'    "entities": {', @Config, N'    }');

    RETURN @Config;
END
GO
----------------------------------------------------------------------------------------------------
CREATE FUNCTION Utility.f_DabEntityConfig (
    @SchemaName AS SYSNAME,
    @TableName AS SYSNAME,
    @Role AS SYSNAME = N'admin'  -- N'anonymous'
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @Config AS NVARCHAR(MAX);

    IF @SchemaName = N'History'
        SET @Config = CONCAT(N'        "', @SchemaName, N'_', @TableName, N'": {
            "source": {
                "object": "', @SchemaName, N'.', @TableName, N'",
                "type": "table",
                "key-fields": ["Id", "ValidFrom", "ValidTo"]
            },
            "permissions": [
                {
                    "role": "', @Role, N'",
                    "actions": [ "read" ]
                }
            ]',
            N'
        }');
    ELSE
        BEGIN
            DECLARE @Relationships AS NVARCHAR(MAX) = Utility.f_DabRelationshipConfig(@SchemaName, @TableName);

            SET @Config = CONCAT(N'        "', @SchemaName, N'_', @TableName, N'": {
            "source": "', @SchemaName, N'.', @TableName, N'",
            "permissions": [
                {
                    "role": "', @Role, N'",
                    "actions": [
                        {
                            "action": "read",
                            "fields": {
                                "include": [
                                    "*"
                                ],
                                "exclude": [
                                    "ValidTo"
                                ]
                            }
                        },
                        "create",
                        "update",
                        "delete"
                    ]
                }
            ]', IIF(@Relationships = N'', N'', CONCAT(N',', Utility.f_CrLf(), @Relationships)),
            N'
        }'
            );
        END
  --END IF

    RETURN @Config
END
GO
----------------------------------------------------------------------------------------------------
CREATE FUNCTION Utility.f_DabRelationshipConfig (
    @SchemaName AS SYSNAME,
    @TableName AS SYSNAME
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @Relationships AS NVARCHAR(MAX);
    DECLARE @Many          AS NVARCHAR(MAX);
    DECLARE @History       AS NVARCHAR(MAX);
    DECLARE @One           AS NVARCHAR(MAX);
    DECLARE @CrLf          AS NCHAR(2) = Utility.f_CrLf();
    DECLARE @CommaCrLf     AS NCHAR(3) = CONCAT(N',', @CrLf);

    SELECT @Many = STRING_AGG(CONCAT(
    N'                "', v.FkTableSchema, N'_', v.FkTableName, N'": {', @CrLf,
    N'                    "cardinality": "many",', @CrLf,
    N'                    "target.entity": "', v.FkTableSchema, N'_', v.FkTableName, N'",', @CrLf,
    N'                    "source.fields": ["', v.PkColumnName, N'"],', @CrLf,
    N'                    "target.fields": ["', v.FkColumnName, N'"]', @CrLf,
    N'                }'), @CommaCrLf)
    FROM Utility.vForeignKeyConstraints AS v
    WHERE v.PkTableSchema = @SchemaName AND v.PkTableName = @TableName

    ;WITH cte_a AS (
        SELECT s.name AS FkTableSchema, t.name AS FkTableName, N'Id' AS PkColumnName, N'Id' AS FkColumnName
        FROM sys.tables AS t
        JOIN sys.schemas AS s ON t.schema_id = s.schema_id
        WHERE s.name = N'History'
          AND t.name = CONCAT(@SchemaName, @TableName)
          AND @SchemaName <> N'History'
    )
    SELECT @History = STRING_AGG(CONCAT(
    N'                "', a.FkTableSchema, N'_', a.FkTableName, N'": {', @CrLf,
    N'                    "cardinality": "many",', @CrLf,
    N'                    "target.entity": "', a.FkTableSchema, N'_', a.FkTableName, N'",', @CrLf,
    N'                    "source.fields": ["', a.PkColumnName, N'"],', @CrLf,
    N'                    "target.fields": ["', a.FkColumnName, N'"]', @CrLf,
    N'                }'), @CommaCrLf)
    FROM cte_a AS a

    SELECT @One = STRING_AGG(CONCAT(
    N'                "', v.PkTableSchema, N'_', 
    CASE 
        WHEN RIGHT(v.PkTableName, 1) = 's' THEN SUBSTRING(v.PkTableName, 1, LEN(v.PkTableName) - 1)
        ELSE v.PkTableName
    END
    , N'": {', @CrLf,
    N'                    "cardinality": "one",', @CrLf,
    N'                    "target.entity": "', v.PkTableSchema, N'_', v.PkTableName, N'",', @CrLf,
    N'                    "source.fields": ["', v.FkColumnName, N'"],', @CrLf,
    N'                    "target.fields": ["', v.PkColumnName, N'"]', @CrLf,
    N'                }'), @CommaCrLf)
    FROM Utility.vForeignKeyConstraints AS v
    WHERE v.FkTableSchema = @SchemaName AND v.FkTableName = @TableName

    IF @Many IS NULL AND @History IS NULL AND @One IS NULL
        SET @Relationships = N''
    ELSE
        SET @Relationships = CONCAT_WS(@CrLf, N'            "relationships": {', CONCAT_WS(@CommaCrLf, @Many, @One, @History), N'            }');
  --END IF

    RETURN @Relationships;
END
GO
----------------------------------------------------------------------------------------------------
/*
    SELECT v.PkTableSchema,
           v.PkTableName,
           v.PkColumnName,
           v.FkTableSchema,
           v.FkTableName,
           v.FkColumnName,
           v.ForeignKeyName,
           v.KeySeq
    FROM Utility.vForeignKeyConstraints AS v
    ORDER BY v.PkTableSchema, v.PkTableName, v.PkColumnName;
*/
CREATE VIEW Utility.vForeignKeyConstraints
AS
    WITH cte_a AS (
        SELECT FkObj.[name]    AS ForeignKeyName,
               PkSchema.[name] AS PkTableSchema,
               PkTable.[name]  AS PkTableName,
               PkColumn.[name] AS PkColumnName,
               FkSch.[name]    AS FkTableSchema,
               FkTbl.[name]    AS FkTableName,
               FkCol.[name]    AS FkColumnName,
               ROW_NUMBER() OVER (PARTITION BY FkObj.[name], FkSch.[name] ORDER BY FkColumns.constraint_column_id) AS KeySeq
        FROM sys.foreign_key_columns FkColumns
            JOIN sys.objects FkObj    ON FkObj.object_id = FkColumns.constraint_object_id
            JOIN sys.tables FkTbl     ON FkTbl.object_id = FkColumns.parent_object_id
            JOIN sys.schemas FkSch    ON FkTbl.schema_id = FkSch.schema_id
            JOIN sys.columns FkCol    ON FkCol.column_id = FkColumns.parent_column_id AND FkCol.object_id = FkTbl.object_id
            JOIN sys.tables PkTable   ON PkTable.object_id = FkColumns.referenced_object_id
            JOIN sys.schemas PkSchema ON PkTable.schema_id = PkSchema.schema_id
            JOIN sys.columns PkColumn ON PkColumn.column_id = FkColumns.referenced_column_id AND PkColumn.object_id = PkTable.object_id
    )
    SELECT a.PkTableSchema, a.PkTableName, a.PkColumnName, a.FkTableSchema, a.FkTableName, a.FkColumnName, a.ForeignKeyName, a.KeySeq
    FROM cte_a AS a;
GO
----------------------------------------------------------------------------------------------------
/*
    SELECT CONCAT(N'Line 1', Utility.f_CrLf(), N'Line 2') AS [CONCAT(N'Line 1', Utility.f_CrLf(), N'Line 2')];
*/
CREATE FUNCTION Utility.f_CrLf()
RETURNS NCHAR(2)
AS
BEGIN
    RETURN NCHAR(13) + NCHAR(10);
END
GO

And here's a C# console app implementation which updates the entities node in the dab-config.json and staticwebapp.database.config.json files.

File: Program.cs

using System.Data.SqlClient;
using System.Text.Json;
using Microsoft.Extensions.Configuration;
using DabConfigUpdater;

var builder = new ConfigurationBuilder().SetBasePath(Directory.GetCurrentDirectory()).AddJsonFile("AppSettings.json");
IConfiguration configuration = builder.Build();

string databaseName = configuration["DatabaseName"]!;
string connectionStringTemplate = configuration["DbConnectionStringTemplate"]!;
string connectionString = connectionStringTemplate.Replace("{DatabaseName}", databaseName);
string dabConfigPath = configuration["DabConfigPath"]!;
string dabConfigPathFileExt = Path.Combine(dabConfigPath, configuration["DabConfigFileName"]!);
string swaConfigPathFileExt = Path.Combine(dabConfigPath, configuration["SwaConfigPathFileExt"]!);

string FetchEntitiesNode(PermissionsRole role) {
    string query = $"SELECT Utility.f_DabAllEntitiesConfig({role.ToParamString()}) AS EntitiesNode;";
    using SqlConnection connection = new(connectionString);
    connection.Open();
    using SqlCommand command = new(query, connection);
    using SqlDataReader reader = command.ExecuteReader();
    if (reader.Read()) {
        return reader["EntitiesNode"].ToString()!;
    } else {
        throw new Exception($"No result found for the given query:\n\t`{query}`");
    }
}

void UpdateEntitiesNodeInFile(string filePath, PermissionsRole permissionsRole) {
    string newNodeValue = FetchEntitiesNode(permissionsRole);
    Console.WriteLine($"\n  Updating `{filePath}`\n with `{permissionsRole}` role permissions");
    string content = File.ReadAllText(filePath);
    using JsonDocument doc = JsonDocument.Parse(content);
    using MemoryStream memStream = new();
    var options = new JsonWriterOptions { Indented = true };
    using Utf8JsonWriter writer = new(memStream, options);
    writer.WriteStartObject();
    foreach (var element in doc.RootElement.EnumerateObject()) {
        if (element.Name.Equals("entities", StringComparison.OrdinalIgnoreCase)) {
            using JsonDocument newEntities = JsonDocument.Parse($"{{\n{newNodeValue}\n}}");
            writer.WritePropertyName("entities");
            newEntities.RootElement.GetProperty("entities").WriteTo(writer);
        } else {
            element.WriteTo(writer);
        }
    }
    writer.WriteEndObject();
    writer.Flush();
    File.WriteAllBytes(filePath, memStream.ToArray());
}

UpdateEntitiesNodeInFile(dabConfigPathFileExt, PermissionsRole.Anonymous);
UpdateEntitiesNodeInFile(swaConfigPathFileExt, PermissionsRole.Admin);

Console.WriteLine($"\n  The Azure Data API Builder configuration files’ `entities` nodes have been\n  updated with configuration for all tables in the `{databaseName}` database.\n\n\tPress any key to exit...");

Console.ReadKey();

File: PermissionsRole.cs

namespace DabConfigUpdater; 

public enum PermissionsRole {
    Anonymous,
    Admin
}

public static class PermissionRoleExtensions {
    public static string ToParamString(this PermissionsRole role) {
        return "N'" + role.ToString().ToLower() + "'";
    }
}

File: AppSettings.json

{
    "DatabaseName": "MyTestDb",
    "DbConnectionStringTemplate": "Data Source=localhost;Initial Catalog={DatabaseName};Integrated Security=True;TrustServerCertificate=True",
    "DabConfigPath": "C:\\Users\\...\\source\\repos\\MyProject...\\swa-db-connections\\",
    "DabConfigFileName": "dab-config.json",
    "SwaConfigPathFileExt": "staticwebapp.database.config.json"
}