data-api-builder
data-api-builder copied to clipboard
Azure
🥕[Bug]: REST works, GraphQL does not
Query
query a {
characters {
items {
Name
}
}
}
Error
info: Microsoft.AspNetCore.Routing.EndpointMiddleware[1]
Executed endpoint 'Hot Chocolate GraphQL Pipeline'
fail: Microsoft.AspNetCore.Server.Kestrel[13]
Connection id "0HN9S4N57VFC1", Request id "0HN9S4N57VFC1:00000003": An unhandled exception was thrown by the application.
Azure.DataApiBuilder.Service.Exceptions.DataApiBuilderException: No relationship exists between Character and Actor
at Azure.DataApiBuilder.Service.GraphQLBuilder.Sql.SchemaConverter.FindNullabilityOfRelationship(String entityName, DatabaseObject databaseObject, String targetEntityName) in /_/src/Service.GraphQLBuilder/Sql/SchemaConverter.cs:line 470
at Azure.DataApiBuilder.Service.GraphQLBuilder.Sql.SchemaConverter.GenerateFieldForRelationship(String entityName, DatabaseObject databaseObject, RuntimeEntities entities, String relationshipName, EntityRelationship relationship) in /_/src/Service.GraphQLBuilder/Sql/SchemaConverter.cs:line 276
at Azure.DataApiBuilder.Service.GraphQLBuilder.Sql.SchemaConverter.CreateObjectTypeDefinitionForTableOrView(String entityName, DatabaseObject databaseObject, Entity configEntity, RuntimeEntities entities, IEnumerable`1 rolesAllowedForEntity, IDictionary`2 rolesAllowedForFields) in /_/src/Service.GraphQLBuilder/Sql/SchemaConverter.cs:line 195
at Azure.DataApiBuilder.Service.GraphQLBuilder.Sql.SchemaConverter.GenerateObjectTypeDefinitionForDatabaseObject(String entityName, DatabaseObject databaseObject, Entity configEntity, RuntimeEntities entities, IEnumerable`1 rolesAllowedForEntity, IDictionary`2 rolesAllowedForFields) in /_/src/Service.GraphQLBuilder/Sql/SchemaConverter.cs:line 56
at Azure.DataApiBuilder.Core.Services.GraphQLSchemaCreator.GenerateSqlGraphQLObjects(RuntimeEntities entities, Dictionary`2 inputObjects) in /_/src/Core/Services/GraphQLSchemaCreator.cs:line 214
at Azure.DataApiBuilder.Core.Services.GraphQLSchemaCreator.GenerateGraphQLObjects() in /_/src/Core/Services/GraphQLSchemaCreator.cs:line 609
at Azure.DataApiBuilder.Core.Services.GraphQLSchemaCreator.InitializeSchemaAndResolvers(ISchemaBuilder schemaBuilder) in /_/src/Core/Services/GraphQLSchemaCreator.cs:line 155
at Azure.DataApiBuilder.Service.Startup.<>c.<AddGraphQLService>b__14_0(IServiceProvider serviceProvider, ISchemaBuilder schemaBuilder) in /_/src/Service/Startup.cs:line 237
at HotChocolate.Execution.RequestExecutorResolver.CreateSchemaAsync(NameString schemaName, RequestExecutorSetup options, RequestExecutorOptions executorOptions, IServiceProvider serviceProvider, TypeModuleChangeMonitor typeModuleChangeMonitor, CancellationToken cancellationToken)
at HotChocolate.Execution.RequestExecutorResolver.CreateSchemaServicesAsync(NameString schemaN
Error message
No relationship exists between Character and Actor
There is a relationship between Character and Actor. This error is wrong.
Observation
There are TWO relationships. If I remove EITHER it starts working.
Configuration
{
"$schema": "https://github.com/Azure/data-api-builder/releases/download/v1.3.19/dab.draft.schema.json",
"data-source": {
"database-type": "mssql",
"connection-string": "{conn-string}",
"options": {
"set-session-context": false
}
},
"runtime": {
"host": {
"cors": {
"origins": [],
"allow-credentials": false
},
"authentication": {
"provider": "StaticWebApps"
},
"mode": "development"
}
},
"entities": {
"Character": {
"source": {
"object": "dbo.Character",
"type": "table"
},
"permissions": [
{
"role": "anonymous",
"actions": [
{
"action": "*"
}
]
}
],
"relationships": {
"Actor": {
"cardinality": "many",
"target.entity": "Actor",
"source.fields": [
"ActorId"
],
"target.fields": [
"Id"
],
"linking.source.fields": [],
"linking.target.fields": []
}
}
},
"Character_Species": {
"source": {
"object": "dbo.Character_Species",
"type": "table"
},
"permissions": [
{
"role": "anonymous",
"actions": [
{
"action": "*"
}
]
}
],
"relationships": {
"Character": {
"cardinality": "many",
"target.entity": "Character",
"source.fields": [
"CharacterId"
],
"target.fields": [
"Id"
],
"linking.source.fields": [],
"linking.target.fields": []
}
}
},
"Actor": {
"source": {
"object": "dbo.Actor",
"type": "table"
},
"permissions": [
{
"role": "anonymous",
"actions": [
{
"action": "*"
}
]
}
]
}
}
}
Database schema
SET NOCOUNT ON
use [trek];
-- Create Login
IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = 'DabLogin')
CREATE LOGIN [DabLogin] WITH PASSWORD = 'P@ssw0rd!';
ALTER SERVER ROLE sysadmin ADD MEMBER [DabLogin];
-- Create User
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = 'DabUser')
CREATE USER [DabUser] FOR LOGIN [DabLogin];
EXEC sp_addrolemember 'db_owner', 'DabUser';
-- Drop tables in reverse order of creation due to foreign key dependencies
DROP TABLE IF EXISTS Character_Species;
DROP TABLE IF EXISTS Series_Character;
DROP TABLE IF EXISTS Character;
DROP TABLE IF EXISTS Species;
DROP TABLE IF EXISTS Actor;
DROP TABLE IF EXISTS Series;
-- create tables
CREATE TABLE Series (
Id INT PRIMARY KEY,
Name NVARCHAR(255) NOT NULL
);
CREATE TABLE Actor (
Id INT PRIMARY KEY,
Name NVARCHAR(255) NOT NULL,
[BirthYear] INT NOT NULL
);
CREATE TABLE Species (
Id INT PRIMARY KEY,
Name NVARCHAR(255) NOT NULL
);
CREATE TABLE Character (
Id INT PRIMARY KEY,
Name NVARCHAR(255) NOT NULL,
ActorId INT NOT NULL,
Stardate DECIMAL(10, 2),
FOREIGN KEY (ActorId) REFERENCES Actor(Id)
);
CREATE TABLE Series_Character (
SeriesId INT,
CharacterId INT,
Role VARCHAR(500),
FOREIGN KEY (SeriesId) REFERENCES Series(Id),
FOREIGN KEY (CharacterId) REFERENCES Character(Id),
PRIMARY KEY (SeriesId, CharacterId)
);
CREATE TABLE Character_Species (
CharacterId INT,
SpeciesId INT,
FOREIGN KEY (CharacterId) REFERENCES Character(Id),
FOREIGN KEY (SpeciesId) REFERENCES Species(Id),
PRIMARY KEY (CharacterId, SpeciesId)
);
I'm not sure that the configuration is correct in your example?
I tried the below which may be more in line with your expected behavior? Note that the example is not complete for the entire database, but should be enough to prove a point. The point being that it is important to specify the cardinality.
For Series_Character, you would need to use a pair of relationships as described here https://learn.microsoft.com/en-us/azure/data-api-builder/relationships#using-a-pair-of-one-to-manymany-to-one-relationships or otherwise you will not be able to get the "role" attribute on the relationship.
First, populate with dummy data.
-- Insert dummy data into Series table
INSERT INTO Series (Id, Name)
VALUES
(1, 'Star Trek: The Original Series'),
(2, 'Star Trek: The Next Generation'),
(3, 'Star Trek: Deep Space Nine');
-- Insert dummy data into Actor table
INSERT INTO Actor (Id, Name, BirthYear)
VALUES
(1, 'William Shatner', 1931),
(2, 'Leonard Nimoy', 1931),
(3, 'Patrick Stewart', 1940),
(4, 'Avery Brooks', 1948);
-- Insert dummy data into Species table
INSERT INTO Species (Id, Name)
VALUES
(1, 'Human'),
(2, 'Vulcan'),
(3, 'Klingon');
-- Insert dummy data into Character table
INSERT INTO Character (Id, Name, ActorId, Stardate)
VALUES
(1, 'James T. Kirk', 1, 1312.4),
(2, 'Spock', 2, 2254.0),
(3, 'Jean-Luc Picard', 3, 41153.7),
(4, 'Benjamin Sisko', 4, 46379.1);
-- Insert dummy data into Series_Character table
INSERT INTO Series_Character (SeriesId, CharacterId, Role)
VALUES
(1, 1, 'Captain'),
(1, 2, 'First Officer'),
(2, 3, 'Captain'),
(3, 4, 'Commander');
-- Insert dummy data into Character_Species table
INSERT INTO Character_Species (CharacterId, SpeciesId)
VALUES
(1, 1), -- James T. Kirk is Human
(2, 2), -- Spock is Vulcan
(3, 1), -- Jean-Luc Picard is Human
(4, 1); -- Benjamin Sisko is Human
Then adjust the configuration a bit so that we have
- One actor can play many characters
- One character can only be played by one actor (which is a bit broken since for instance Darth Vader is played by several)
- Many to many relationship between Species and Characters
{
"$schema": "https://github.com/Azure/data-api-builder/releases/download/v1.3.19/dab.draft.schema.json",
"data-source": {
"database-type": "mssql",
"connection-string": "{conn-string}",
"options": {
"set-session-context": false
}
},
"runtime": {
"host": {
"cors": {
"origins": [],
"allow-credentials": false
},
"authentication": {
"provider": "StaticWebApps"
},
"mode": "development"
}
},
"entities": {
"Character": {
"source": {
"object": "dbo.Character",
"type": "table"
},
"permissions": [
{
"role": "anonymous",
"actions": [
{
"action": "*"
}
]
}
],
"relationships": {
"Actor": {
"cardinality": "one",
"target.entity": "Actor",
"source.fields": [
"ActorId"
],
"target.fields": [
"Id"
],
"linking.source.fields": [],
"linking.target.fields": []
},
"Species": {
"cardinality": "many",
"target.entity": "Species",
"source.fields": [
"Id"
],
"target.fields": [
"Id"
],
"linking.object": "dbo.Character_Species",
"linking.source.fields": ["CharacterId"],
"linking.target.fields": ["SpeciesId"]
}
}
},
"Actor": {
"source": {
"object": "dbo.Actor",
"type": "table"
},
"permissions": [
{
"role": "anonymous",
"actions": [
{
"action": "*"
}
]
}
],
"relationships": {
"Character": {
"cardinality": "many",
"target.entity": "Character",
"source.fields": [
"Id"
],
"target.fields": [
"ActorId"
],
"linking.source.fields": [],
"linking.target.fields": []
}
}
},
"Species": {
"source": {
"object": "dbo.Species",
"type": "table"
},
"permissions": [
{
"role": "anonymous",
"actions": [
{
"action": "*"
}
]
}
],
"relationships": {
"Actor": {
"cardinality": "many",
"target.entity": "Character",
"source.fields": [
"Id"
],
"target.fields": [
"Id"
],
"linking.object": "dbo.Character_Species",
"linking.source.fields": ["SpeciesId"],
"linking.target.fields": ["CharacterId"]
}
}
}
}
}
And then we can do a query as such
query {
actors {
items {
Id
Name
Character {
items {
Id
Name
Species {
items {
Id
Name
}
}
}
}
}
}
}
With this result
{
"data": {
"actors": {
"items": [
{
"Id": 1,
"Name": "William Shatner",
"Character": {
"items": [
{
"Id": 1,
"Name": "James T. Kirk",
"Species": {
"items": [
{
"Id": 1,
"Name": "Human"
}
]
}
}
]
}
},
{
"Id": 2,
"Name": "Leonard Nimoy",
"Character": {
"items": [
{
"Id": 2,
"Name": "Spock",
"Species": {
"items": [
{
"Id": 2,
"Name": "Vulcan"
}
]
}
}
]
}
},
{
"Id": 3,
"Name": "Patrick Stewart",
"Character": {
"items": [
{
"Id": 3,
"Name": "Jean-Luc Picard",
"Species": {
"items": [
{
"Id": 1,
"Name": "Human"
}
]
}
}
]
}
},
{
"Id": 4,
"Name": "Avery Brooks",
"Character": {
"items": [
{
"Id": 4,
"Name": "Benjamin Sisko",
"Species": {
"items": [
{
"Id": 1,
"Name": "Human"
}
]
}
}
]
}
}
]
}
}
}
@JerryNixon , It seems there might be an issue with the configuration, as it doesn't fully capture the correct entity relationships.
Scenario_1
going through the config i feel you mean to say that 1 character can have multiple actors in that case the database and config should look like below:
database: (It correctly shows that 1 Character can have multiple actors)
CREATE TABLE Character (
Id INT PRIMARY KEY,
Name NVARCHAR(255) NOT NULL,
Stardate DECIMAL(10, 2)
);
CREATE TABLE Actor (
Id INT PRIMARY KEY,
Name NVARCHAR(255) NOT NULL,
[BirthYear] INT NOT NULL,
CharacterId INT NOT NULL,
FOREIGN KEY (CharacterId) REFERENCES Character(Id)
);
and the confid should be updated to:
"entities": {
"Character": {
"source": {
"object": "dbo.Character",
"type": "table"
},
"permissions": [
{
"role": "anonymous",
"actions": [
{
"action": "*"
}
]
}
],
"relationships": {
"Actor": {
"cardinality": "many",
"target.entity": "Actor",
"source.fields": [
"Id"
],
"target.fields": [
"CharacterId"
],
"linking.source.fields": [],
"linking.target.fields": []
}
}
},
"Character_Species": {
"source": {
"object": "dbo.Character_Species",
"type": "table"
},
"permissions": [
{
"role": "anonymous",
"actions": [
{
"action": "*"
}
]
}
],
"relationships": {
"Character": {
"cardinality": "many",
"target.entity": "Character",
"source.fields": [
"CharacterId"
],
"target.fields": [
"Id"
],
"linking.source.fields": [],
"linking.target.fields": []
}
}
},
"Actor": {
"source": {
"object": "dbo.Actor",
"type": "table"
},
"permissions": [
{
"role": "anonymous",
"actions": [
{
"action": "*"
}
]
}
]
}
}
With the above changes i am getting 200.
Scenario_2
1 Character can have 1 Actor In this case just using cardinality as "one". would make your current setup work.
"entities": {
"Character": {
"source": {
"object": "dbo.Character",
"type": "table"
},
"permissions": [
{
"role": "anonymous",
"actions": [
{
"action": "*"
}
]
}
],
"relationships": {
"Actor": {
"cardinality": "one",
"target.entity": "Actor",
"source.fields": [
"ActorId"
],
"target.fields": [
"Id"
],
"linking.source.fields": [],
"linking.target.fields": []
}
}
}
}
