[Bug]: CLI create API incorrectly requires field value that has been excluded in the config

[CalvinQuark]

What happened?

The DAB CLI hosted API for creating a new record incorrectly requires field value that has been specifically excluded in the config.

Version

0.8.52

What database are you using?

Azure SQL

What hosting model are you using?

Local (including CLI), Static Web Apps (SWA)

Which API approach are you accessing DAB through?

GraphQL

Relevant log output

No response

Code of Conduct

• I agree to follow this project's Code of Conduct

[CalvinQuark]

DAB CLI shouldn't required specifically excluded field to create a new record

The DAB CLI hosted API for creating a new record incorrectly requires a field value that has been specifically excluded in the config.

To demonstrate the issue, create a table containing an autogenerated column.

CREATE SCHEMA Dev;
GO

CREATE TABLE Dev.Issues (
    Id int NOT NULL IDENTITY (1, 1),
    Title nvarchar(500) NOT NULL
)

ALTER TABLE Dev.Issues ADD CONSTRAINT PK_Issues_Id PRIMARY KEY CLUSTERED (Id);

ALTER TABLE Dev.Issues
    ADD ValidFrom DATETIME2(2) GENERATED ALWAYS AS ROW START NOT NULL DEFAULT GETUTCDATE(),
        ValidTo DATETIME2(2) GENERATED ALWAYS AS ROW END NOT NULL DEFAULT CAST('9999-12-31 23:59:59.9999999' AS DATETIME2(2)),
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);

Configure the entitiy in dab-config.json:

{
    "$schema": "https://github.com/Azure/data-api-builder/releases/download/v0.8.52/dab.draft.schema.json",
    "data-source": {
        "database-type": "mssql",
        "connection-string": "Data Source=localhost;Initial Catalog={{DATABASE_NAME}};Integrated Security=True;TrustServerCertificate=True",
        "options": {
            "set-session-context": false
        }
    },
    "runtime": {
        "rest": {
            "enabled": false
        },
        "graphql": {
            "enabled": true,
            "path": "/graphql"
        },
        "host": {
            "cors": {
                "origins": [ ],
                "allow-credentials": false
            },
            "authentication": {
                "provider": "StaticWebApps"
            },
            "mode": "development"
        }
    },
    "entities": {
        "Dev_Issues": {
            "source": "Dev.Issues",
            "permissions": [
                {
                    "role": "anonymous",
                    "actions": [
                        {
                            "action": "create",
                            "fields": {
                                "include": [
                                    "*"
                                ],
                                "exclude": [
                                    "ValidFrom",
                                    "ValidTo"
                                ]
                            }
                        },
                        {
                            "action": "read",
                            "fields": {
                                "include": [
                                    "*"
                                ],
                                "exclude": [
                                    "ValidTo"
                                ]
                            }
                        },
                        "update",
                        "delete"
                    ]
                }
            ]
        }
    }
}

Launch the DAB CLI: dab start --no-https-redirect --config .\swa-db-connections\dab-config.json

Navigate to the http://localhost:5000/graphql/.

Initial read works.

query DevIssues {
  dev_Issues {
    items {
      Id
      Title
    }
  }
}

And returns an emtpty array:

{
  "data": {
    "dev_Issues": {
      "items": []
    }
  }
}

The record creation operation fails:

mutation CreateDevIssue($issue:CreateDev_IssuesInput!) {
  createDev_Issues(item:$issue) {
    Id
    Title
  }
}

Payload:

{
  "issue": {
    "Title": "DAB-CLI Generated/hosted APIs shouldn't require the explicitly excluded `ValidFrom` field to create new [Dev].[Issues] record"
  }
}

Even though the ValidFrom column has been explicitly excluded in the create action's configuration. Here's the response:

{
  "errors": [
    {
      "message": "DateTime cannot parse the given literal of type `StringValueNode`.",
      "path": [
        "issue",
        "ValidFrom"
      ],
      "extensions": {
        "field": "CreateDev_IssuesInput.ValidFrom",
        "fieldType": "DateTime"
      }
    }
  ]
}

[seantleonard]

Thank you for reporting.

Looks like when DAB resolves a table column's metadata, the metadata dictionary that is provided from SQL looks like:
Metadata Key Name:
COLUMN_DEFAULT
Metadata Value:
(getutcdate())

data-api-builder/src/Core/Services/MetadataProviders/SqlMetadataProvider.cs

Line 1213 in c699250

columnDefinition.DefaultValue = columnInfo["COLUMN_DEFAULT"];

DAB's metadata provider does not account for the fact that a default value can be a system function per documentation:

constant_expression
Is a literal value, a NULL, or a system function used as the default column value. If used in conjunction with a column defined to be of a .NET Framework user-defined type, the implementation of the type must support an implicit conversion from the constant_expression to the user-defined type.
https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-table-column-definition-transact-sql?view=sql-server-ver16#:~:text=constant_expression%0AIs%20a,user%2Ddefined%20type.

The workaround at the moment is to either:

• not set a system function as a default value and allow the client to provide. (probably not what you want)
• format your graphql mutation like the following to get the appropriate error to show:

mutation myMutation {
  createDev_Issues(item: {Title:"me", ValidFrom:"2022-08-09"}) {
    Id,
    Title
  }
}

More background:
Because the value resolved for the column default is (getutcdate()), that value is set as the default in the graphql input type for field ValidFrom which is of type DateTime:

"""
Input type for creating Dev_Issues
"""
input CreateDev_IssuesInput {
  """
  Input for field Title on type CreateDev_IssuesInput
  """
  Title: String!

  """
  Input for field ValidFrom on type CreateDev_IssuesInput
  """
  ValidFrom: DateTime = "(getutcdate())"
}

When your mutation is initially processed by the Hot Chocolate GraphQL engine inside DAB, that default value is automatically resolved from the schema and a cast is attempted before any of DAB's GraphQL resolvers execute. Because that literal value does not convert to a datetime type, you see the error you reported.

If you update you payload to the following with the ValidFrom field defined with a valid value for the data type:

{
  "issue": {
    "ValidFrom": "2022-08-09",
    "Title": "DAB-CLI Generated/hosted APIs shouldn't require the explicitly excluded `ValidFrom` field to create new [Dev].[Issues] record"
  }
}

you will see the expected permission error.

I'll need a little more time to identify the best way to improve this experience. Because the generated GraphQL schema defines a single input object type for the respective entity, the schema will ensure that the default value is included with your request, even if you didn't specify it. Unfortunately, that breaks compatibility with roles which exclude columns with default values. DAB does not allow you to use that column once it is passed to DAB's resolvers, as enforced by your permissions in DAB's runtime config.

[CalvinQuark]

Thanks for investigating. The DEFAULT specification for the ValidFrom (and ValidTo) column definitions seems to be superfluous as the docs suggest. For inserts or updates, the current UTC date/time value is persisted regardless of whether or not the SYSTEM_TIME column's default value has been explicitly specified. Hence, redefining the ValidFrom column without explicitly specifying its default constraint is an effective workaround for this issue.

[CalvinQuark]

For anyone encountering this issue, here's a script to drop all of the problematic constraints:

DECLARE @DropConstraintSql NVARCHAR(MAX);

SET @DropConstraintSql = (
    SELECT STRING_AGG(CONCAT(N'ALTER TABLE ', QUOTENAME(OBJECT_SCHEMA_NAME(c.parent_object_id)), N'.', QUOTENAME(OBJECT_NAME(c.parent_object_id)), 
                              N' DROP CONSTRAINT ', QUOTENAME(c.name)), N';' + CHAR(13))
    FROM sys.default_constraints AS c
    JOIN sys.tables AS t ON c.parent_object_id = t.object_id
    JOIN sys.columns AS col ON t.object_id = col.object_id AND c.parent_column_id = col.column_id
    WHERE c.type_desc = N'DEFAULT_CONSTRAINT'
      AND col.name = N'ValidFrom'
      AND c.definition LIKE N'(%getutcdate%)'
);

IF @DropConstraintSql IS NOT NULL
    EXEC sp_executesql @DropConstraintSql;

I didn't delete the default constraints for the ValidTo columns since they always have the same value.

[abhishekkumams]

@CalvinQuark , can you try it out with 0.9.7 and confirm if you still see the above issue.