GenerateDabConfig.sql

DECLARE @JsonOutput NVARCHAR(MAX);

WITH TablesToExclude AS
(
	SELECT fk.parent_object_id AS object_id
	FROM sys.foreign_keys AS fk
	GROUP BY fk.parent_object_id
	HAVING COUNT(fk.object_id) = 2 

	UNION

	SELECT object_id
	FROM sys.tables
	WHERE name = 'sysdiagrams'
	  OR is_edge = 1
	  OR is_node = 1
	  OR is_external = 1
	  OR is_filetable = 1
	  OR is_ms_shipped = 1
)
, EntityTables AS (
	SELECT
        t.name AS TableName,
        s.name AS SchemaName,
        CONCAT('[', s.name, '].[', t.name, ']') AS ObjectName,
        STRING_AGG(CONCAT('"', c.name, '": "', c.name, '"'), ', ') WITHIN GROUP (ORDER BY c.column_id) AS ColumnMappings,
        STRING_AGG('"' + kc.name + '"', ', ') WITHIN GROUP (ORDER BY c.column_id) AS KeyFields
	FROM sys.tables AS t
	JOIN sys.schemas AS s ON t.schema_id = s.schema_id
	JOIN sys.columns AS c ON t.object_id = c.object_id
	LEFT JOIN sys.index_columns AS ic ON t.object_id = ic.object_id AND c.column_id = ic.column_id
	LEFT JOIN sys.indexes AS i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
	LEFT JOIN sys.key_constraints AS k ON i.object_id = k.parent_object_id AND i.index_id = k.unique_index_id
	LEFT JOIN sys.columns AS kc ON ic.object_id = kc.object_id AND ic.column_id = kc.column_id AND i.is_primary_key = 1
	WHERE t.object_id NOT IN (SELECT object_id FROM TablesToExclude)
	GROUP BY t.object_id, t.name, s.name
)
, EntityJson (JsonString) AS 
(
	SELECT '{' + STRING_AGG(
        '"' + TableName + '": {' +
        '"source": { ' + 
        	'"object": "' + ObjectName + '", ' + 
        	'"type": "table", ' + 
        	'"key-fields": [' + KeyFields + '] ' + 
        '}, ' +
        '"mappings": {' + ColumnMappings + '}, ' +
        '"cache": {"enabled": false, "ttl-seconds": 5},'+
        '"graphql": { ' + 
        	'"enabled": true, ' + 
        	'"type": { ' + 
                '"singular": "' + TableName + '", ' + 
                '"plural": "' + TableName + '" ' + 
        	'} ' + 
        '},'+
        '"rest": {"enabled": true, "path": "/' + TableName + '"},'+
        '"permissions": [{"role": "anonymous", "actions": ["create", "read", "update", "delete"]}]}'
        , ', ') WITHIN GROUP (ORDER BY TableName) + '}'
	FROM EntityTables
)
SELECT @JsonOutput = (
  SELECT
    'https://github.com/Azure/data-api-builder/releases/download/v0.11.132/dab.draft.schema.json' AS '$schema',

    JSON_QUERY((
      SELECT
        'mssql' AS 'database-type',
        '@env(''my-connection-string'')' AS 'connection-string'
      FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    )) AS 'data-source',

    JSON_QUERY('[]') AS 'data-source-files',

    JSON_QUERY((

      SELECT
        JSON_QUERY((
          SELECT
            'development' AS 'mode',
            JSON_QUERY((
              SELECT
                'StaticWebApps' AS 'provider'
              FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
            )) AS 'authentication'
          FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
        )) AS 'host',

        JSON_QUERY((
          SELECT
            CAST(1 AS BIT) AS 'enabled',
            '/api' AS 'path',
            CAST(0 AS BIT) AS 'request-body-strict'
          FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
        )) AS 'rest',

        JSON_QUERY((
          SELECT
            CAST(1 AS BIT) AS 'enabled',
            '/graphql' AS 'path',
            CAST(1 AS BIT) AS 'allow-introspection'
          FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
        )) AS 'graphql',

        JSON_QUERY((
          SELECT
            CAST(0 AS BIT) AS 'enabled',
            5 AS 'ttl-seconds'
          FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
        )) AS 'cache',

        JSON_QUERY((
          SELECT
            JSON_QUERY((
              SELECT
                CAST(0 AS BIT) AS 'enabled',
                '' AS 'connection-string'
              FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
            )) AS 'application-insights'
          FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
        )) AS 'telemetry'

      FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    )) AS 'runtime',

    JSON_QUERY((SELECT JsonString FROM EntityJson)) AS 'entities'
  FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
);

SET @JsonOutput = REPLACE(@JsonOutput, '\/', '/');
SELECT @JsonOutput AS GeneratedJson;

Output

{
    "$schema": "https://github.com/Azure/data-api-builder/releases/download/v0.11.132/dab.draft.schema.json",
    "data-source": {
        "database-type": "mssql",
        "connection-string": "@env('my-connection-string')"
    },
    "data-source-files": [],
    "runtime": {
        "host": {
            "mode": "development",
            "authentication": {
                "provider": "StaticWebApps"
            }
        },
        "rest": {
            "enabled": true,
            "path": "/api",
            "request-body-strict": false
        },
        "graphql": {
            "enabled": true,
            "path": "/graphql",
            "allow-introspection": true
        },
        "cache": {
            "enabled": false,
            "ttl-seconds": 5
        },
        "telemetry": {
            "application-insights": {
                "enabled": false,
                "connection-string": ""
            }
        }
    },
    "entities": {
        "authors": {
            "source": {
                "object": "[dbo].[authors]",
                "type": "table",
                "key-fields": [
                    "id"
                ]
            },
            "mappings": {
                "id": "id",
                "first_name": "first_name",
                "middle_name": "middle_name",
                "last_name": "last_name"
            },
            "cache": {
                "enabled": false,
                "ttl-seconds": 5
            },
            "graphql": {
                "enabled": true,
                "type": {
                    "singular": "authors",
                    "plural": "authors"
                }
            },
            "rest": {
                "enabled": true,
                "path": "/authors"
            },
            "permissions": [
                {
                    "role": "anonymous",
                    "actions": [
                        "create",
                        "read",
                        "update",
                        "delete"
                    ]
                }
            ]
        },
        "books": {
            "source": {
                "object": "[dbo].[books]",
                "type": "table",
                "key-fields": [
                    "id"
                ]
            },
            "mappings": {
                "id": "id",
                "title": "title",
                "year": "year",
                "pages": "pages",
                "series_id": "series_id"
            },
            "cache": {
                "enabled": false,
                "ttl-seconds": 5
            },
            "graphql": {
                "enabled": true,
                "type": {
                    "singular": "books",
                    "plural": "books"
                }
            },
            "rest": {
                "enabled": true,
                "path": "/books"
            },
            "permissions": [
                {
                    "role": "anonymous",
                    "actions": [
                        "create",
                        "read",
                        "update",
                        "delete"
                    ]
                }
            ]
        },
        "reviews": {
            "source": {
                "object": "[dbo].[reviews]",
                "type": "table",
                "key-fields": [
                    "id"
                ]
            },
            "mappings": {
                "id": "id",
                "book_id": "book_id",
                "date": "date",
                "author": "author",
                "title": "title",
                "review": "review"
            },
            "cache": {
                "enabled": false,
                "ttl-seconds": 5
            },
            "graphql": {
                "enabled": true,
                "type": {
                    "singular": "reviews",
                    "plural": "reviews"
                }
            },
            "rest": {
                "enabled": true,
                "path": "/reviews"
            },
            "permissions": [
                {
                    "role": "anonymous",
                    "actions": [
                        "create",
                        "read",
                        "update",
                        "delete"
                    ]
                }
            ]
        },
        "series": {
            "source": {
                "object": "[dbo].[series]",
                "type": "table",
                "key-fields": [
                    "id"
                ]
            },
            "mappings": {
                "id": "id",
                "name": "name"
            },
            "cache": {
                "enabled": false,
                "ttl-seconds": 5
            },
            "graphql": {
                "enabled": true,
                "type": {
                    "singular": "series",
                    "plural": "series"
                }
            },
            "rest": {
                "enabled": true,
                "path": "/series"
            },
            "permissions": [
                {
                    "role": "anonymous",
                    "actions": [
                        "create",
                        "read",
                        "update",
                        "delete"
                    ]
                }
            ]
        }
    }
}