Skip to content

Instantly share code, notes, and snippets.

@JerryNixon

JerryNixon/GenerateDabConfig.sql

Last active May 2, 2024 19:45
Show Gist options
  • Save JerryNixon/793d572852ae50c4f79ed98cfd94f0fd to your computer and use it in GitHub Desktop.
Save JerryNixon/793d572852ae50c4f79ed98cfd94f0fd to your computer and use it in GitHub Desktop.
Download ZIP
Raw
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;
@JerryNixon
Copy link
Author

JerryNixon commented May 2, 2024 •
edited
Loading

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"
                    ]
                }
            ]
        }
    }
}

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment