{"id":2751,"date":"2023-12-18T18:52:46","date_gmt":"2023-12-19T02:52:46","guid":{"rendered":"https:\/\/devblogs.microsoft.com\/azure-sql\/?p=2751"},"modified":"2024-03-11T08:26:29","modified_gmt":"2024-03-11T15:26:29","slug":"data-api-builder-relationships","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/azure-sql\/data-api-builder-relationships\/","title":{"rendered":"Relationship Advice from Data API builder"},"content":{"rendered":"

Data API builder<\/a> exposes REST endpoints for MySQL, PostgreSQL, Cosmos DB, SQL Server and Azure SQL. REST (Representational State Transfer) endpoints allow developers to easily query a single table, view or stored procedure. However, Data API builder also exposes GraphQL endpoints. Like REST, GraphQL returns data, but unlike REST, GraphQL can return data from multiple related tables in nested results<\/strong>. This includes one-to-many, many-to-many, and many-to-one relationships.<\/p>\n

\"Image<\/a><\/p>\n

This diagram illustrates a simple database that uses all three relationships. Data API builder supports each<\/a>, but to do so it is required that the configuration file describe bot the table and the relationship. Developers can edit the JSON fire directly or use the CLI to update the JSON programmatically. Let’s focus on the CLI approach as there’s no question it is the easiest approach. Interacting JSON is simple but very prone to error. With the CLI, you don’t need to know the JSON schema. And with the CLI you don’t need to remember every command and closing brace.\u00a0<\/span><\/p>\n

Adding tables<\/h3>\n

The CLI (dab) has an “add” command we use to add tables, views, or stored procedures to the configuration file. With it, we can configure the entity name, schema, and authentication. As of this article the supported flags and arguments are as follows:<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
-s, –source<\/strong><\/span><\/td>\nRequired<\/strong>. Name of the source database object.<\/td>\n<\/tr>\n
–permissions<\/strong><\/span><\/td>\nRequired<\/strong>. Permissions required to access the source table or container.<\/td>\n<\/tr>\n
–source.type<\/strong><\/span><\/td>\nType of the database object. Must be one of: [table, view, stored-procedure]<\/td>\n<\/tr>\n
–source.params<\/strong><\/span><\/td>\nDictionary of parameters and their values for Source object. “param1:val1,param2:value2,..”<\/td>\n<\/tr>\n
–source.key-fields<\/strong><\/span><\/td>\nThe field(s) to be used as primary keys.<\/td>\n<\/tr>\n
–rest<\/strong><\/span><\/td>\nRoute for rest API.<\/td>\n<\/tr>\n
–rest.methods<\/strong><\/span><\/td>\nHTTP actions to be supported for stored procedure. Specify the actions as a comma separated list. Valid HTTP actions are: [GET, POST, PUT, PATCH, DELETE]<\/td>\n<\/tr>\n
–graphql<\/strong><\/span><\/td>\nType of GraphQL.<\/td>\n<\/tr>\n
–graphql.operation<\/strong><\/span><\/td>\nGraphQL operation to be supported for stored procedure. Valid operations are: [Query, Mutation]<\/td>\n<\/tr>\n
–fields.include<\/strong><\/span><\/td>\nFields that are allowed access to permission.<\/td>\n<\/tr>\n
–fields.exclude<\/strong><\/span><\/td>\nFields that are excluded from the action lists.<\/td>\n<\/tr>\n
–policy-request<\/strong><\/span><\/td>\nSpecify the rule to be checked before sending any request to the database.<\/td>\n<\/tr>\n
–policy-database<\/strong><\/span><\/td>\nSpecify an OData style filter rule that will be injected in the query sent to the database.<\/td>\n<\/tr>\n
\u00a0 -c, –config<\/strong><\/span><\/td>\nPath to config file. Defaults to ‘dab-config.json’ unless ‘dab-config.<DAB_ENVIRONMENT>.json’ exists, where DAB_ENVIRONMENT is an environment variable.<\/td>\n<\/tr>\n
–help<\/strong><\/span><\/td>\nDisplay this help screen.<\/td>\n<\/tr>\n
–version<\/strong><\/span><\/td>\nDisplay version information.<\/td>\n<\/tr>\n
\u00a0 Entity (pos. 0)<\/strong><\/span><\/td>\nName of the entity.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Minimal syntax<\/h3>\n

It doesn’t take much to add a table to the Data API builder configuration file. The basics are its name, table schema including keys, and permissions. Here’s how we add the authors<\/strong> table.<\/p>\n

dab add \"authors\" --source \"[dbo].[authors]\" --source.type \"table\" --source.key-fields \"id\" --permissions \"anonymous:*\"<\/code><\/pre>\n
The resulting configuration looks like this:<\/p>\n
  \"entities\": {\r\n    \"authors\": {\r\n      \"source\": {\r\n        \"object\": \"[dbo].[authors]\",\r\n        \"type\": \"table\",\r\n        \"key-fields\": [ \"id\" ]\r\n      },\r\n      \"graphql\": {\r\n        \"enabled\": true,\r\n        \"type\": {\r\n          \"singular\": \"authors\",\r\n          \"plural\": \"authors\"\r\n        }\r\n      },\r\n      \"rest\": { \"enabled\": true },\r\n      \"permissions\": [\r\n        {\r\n          \"role\": \"anonymous\",\r\n          \"actions\": [ { \"action\": \"*\" } ]\r\n        }\r\n      ]\r\n    }\r\n  }<\/code><\/pre>\n
That’s a lot for a little. And more could be added; but for now, let’s make this simple. It’s worth pointing out that regardless of the backend database, the Data API builder configuration syntax is identical – you don’t need to do anything special to account for your backend data source. So far we’ve added the authors<\/strong> table, now let’s add series<\/strong>, books<\/strong>, and the cross-reference table: books_authors<\/strong>.<\/p>\n
dab add \"authors\" --source \"[dbo].[authors]\" --source.type \"table\" --source.key-fields \"id\" --permissions \"anonymous:*\"\r\n\r\ndab add \"series\" --source \"[dbo].[series]\" --source.type \"table\" --source.key-fields \"id\" --permissions \"anonymous:*\"\r\n\r\ndab add \"books\" --source \"[dbo].[books]\" --source.type \"table\" --source.key-fields \"id\" --permissions \"anonymous:*\"\r\n\r\ndab add \"books_authors\" --source \"[dbo].[books_authors]\" --source.type \"table\" --source.key-fields \"book_id,author_id\" --permissions \"anonymous:*\" --graphql false<\/code><\/pre>\n
In the sample above, each table uses the same CLI syntax to get into the configuration file. Run this twice and you will get an error that the entities are already defined. If you want to modify an existing entity, use update<\/code> instead of\u00a0add<\/code>.<\/p>\n
Hiding tables<\/h3>\n
However, if you look closely the last line is different. There is one extra flag --graphql false<\/code> added to the books_authors<\/strong> table. Why? Setting\u00a0--graphql<\/code>to false<\/strong> tells the Data API builder engine to hide the table from the graph schema. Why? The graph schema tells the consumer what entities it can query – including intellisense in debugging tools – and there is no practical reason to encourage consumers to directly query a cross-reference table. To be clear, this doesn’t mean we can’t use hidden entities in relationship definitions. What it means is that our graph schema is nice and clean.<\/p>\n
A note about relationships in Data API builder<\/strong><\/p>\n
A part of you may wonder, why do I need to define my relationships when they are already defined in my database? That’s a fair question. First, let me remind you that a RDMS supports relationships but does not require them. That is to say, relationships in a database like SQL Server help ensure data integrity, but do not actually come into play when you perform JOIN statements in your query. The practical reason for relationship definitions in Data API builder is so you can customize them, naming them how you want them to appear in your queries and limit relationships you don’t want to present.<\/p>\n
One-to-many & many-to-one relationships<\/h3>\n
There is one series with many books in it. For example, Isaac Asimov’s Foundation series has some seven books from Foundation<\/em> all thew way to Forward the Foundation<\/em>. This, and its corollary many-to-one are the simplest relationships, showing up in almost every RDBM schema.<\/p>\n
\"Image<\/a><\/p>\n
The image above highlights the series to book relationship. This is a one-to-many relationship when we consider series-to-books<\/strong>. It is a many-to-one relationship when we consider books-to-series<\/strong>. Let’s see what is required for us to add this to the Data API builder configuration file using the CLI.\u00a0<\/span><\/p>\n
dab update \"series\" --relationship \"books\" --target.entity \"series\" --cardinality many --relationship.fields \"id:series_id\"<\/code><\/pre>\n
dab update \"books\" --relationship \"series\" --target.entity \"series\" --cardinality one --relationship.fields \"series_id:id\"<\/code><\/pre>\n
The first defines series-to-books<\/strong> and the second defines books-to-series<\/strong>. In both cases we identify the primary key and the foreign keys relevant to the relationship. When the result of the relationship (with the target table) is zero or more, we set the cardinality to many. When the result of the relationship (with the target table) is zero or one, we set the cardinality to one. Relationships, don’t forget, are exposed through Data API builder GraphQL endpoints. Relationship definitions do not impact REST endpoints.<\/p>\n
\"relationships\": {\r\n  \"books\": {\r\n    \"cardinality\": \"many\",\r\n    \"target.entity\": \"series\",\r\n    \"source.fields\": [ \"id\" ],\r\n    \"target.fields\": [ \"series_id\" ]\r\n  }\r\n}<\/code><\/pre>\n
The relationship section of the entity is added automatically, perfectly formatted and ready for the Data API builder engine to start. If you look at it, it’s pretty simple. That said, notice how compound keys are supported by arrays, this and other features make Data API builder perfect for even the most complicated data schema.<\/p>\n
Many-to-many<\/h3>\n
What makes many-to-many relationships tricky is that it requires a third table, usually referred to as a cross-reference or linking table. They typically have the key(s) for each of the two outside tables, and sometimes columns for data to describe relationship-specific data.<\/p>\n
\"Image<\/a><\/p>\n
In the case of a relational database, the resulting SQL required to query this relationship would have a minimum of two JOIN statements to connect the tables. Data API builder does this for you. All you need to do is to ensure the linking table is already referenced in the configuration file. However, as we noted above, the linking table entities never need to be exposed directly through the Grap schema.<\/p>\n
For our database a book<\/strong> can be written by many authors<\/strong>. At the same time, an author<\/strong> can write several books<\/strong>. That’s the classic scenario<\/a> for a many-to-many<\/strong> relationship. Here books_authors<\/strong> is the cross-reference table holding the primary key of the author<\/strong> and the primary key of the book<\/strong>. This is our linking table and what we include in our CLI command to build out the configuration.<\/p>\n
dab update \"books\" --relationship \"authors\" --target.entity \"authors\" --cardinality many --linking.object \"dbo.books_authors\" --linking.source.fields \"book_id\" --linking.target.fields \"author_id\" \r\n\r\ndab update \"authors\" --relationship \"books\" --target.entity \"books\" --cardinality many --linking.object \"dbo.books_authors\" --linking.source.fields \"author_id\" --linking.target.fields \"book_id\"<\/code><\/pre>\n
A few things are removed, and a few things are added. Look at the syntax closely and the --relationship:fields<\/code> we saw in our previous example has been removed. That’s because the Data API builder engine can infer those with other data you have already provided. Also notice that the linking table is listed specifically. This entity must already exist in the configuration file, but, as we mentioned above, it does not have to be exposed explicitly through the GraphQL endpoint.<\/p>\n
Intuitive & readable<\/h3>\n
Without a doubt, this short syntax is simpler than the SQL required to execute it. But let’s take a minute to step through a few important parts. Note the name of the first relationship is “authors<\/em>” and the second is “books<\/em>“. These appear in the graph query, so that books { authors }<\/strong> and authors { books }<\/strong> keeps your query both intuitive and readable<\/a>.<\/p>\n
\"authors\": {\r\n  \"cardinality\": \"many\",\r\n  \"target.entity\": \"authors\",\r\n  \"linking.object\": \"dbo.books_authors\",\r\n  \"linking.source.fields\": [ \"book_id\" ],\r\n  \"linking.target.fields\": [ \"author_id\" ]\r\n}<\/code><\/pre>\n
The configuration for many-to-many<\/strong> relationships is simple enough, the CLI is your best bet to get it right the first time. Can you add it manually into the JSON? Yes. Can you edit the JSON after running the CLI? Yes. The CLI is a helper, not a constraint. You do you.<\/p>\n
GraphQL queries<\/h3>\n
query {\r\n    series {\r\n        items {\r\n            name\r\n            books {\r\n                items {\r\n                    title\r\n                }\r\n            }\r\n        }\r\n    }\r\n    books {\r\n        items {\r\n            title\r\n            series {\r\n                name\r\n            }\r\n        }\r\n    }\r\n}<\/code><\/pre>\n
This query exercises both the series one-to-many<\/strong> relationship to books and the books many-to-one<\/strong> relationship to series. Look and how clean the Data API builder’s endpoint implementations are and how simple it can be for developers to access complex data. Now, let’s query the many-to-many<\/strong> relationship between authors<\/strong> and books<\/strong>.<\/p>\n
query {\r\n    series {\r\n        items {\r\n            name\r\n            books {\r\n                items {\r\n                    title\r\n                    pages\r\n                    authors {\r\n                        items {\r\n                            first_name\r\n                            last_name\r\n                        }\r\n                    }\r\n                }\r\n            }\r\n        }\r\n    }\r\n}<\/code><\/pre>\n
Wrapping up<\/h3>\n
Using the Data Api builder CLI to constructor a configuration file that describes your tables, views, and stored procedures it all you need to get REST and GraphQL endpoints running against your MySQL, Cosmos DB, Progres and Azure SQL databases. But with a little more, you can quite easily define relationships between entities and make your GraphQL queries rich and capable.<\/p>\n","protected":false},"excerpt":{"rendered":"
Data API builder exposes REST endpoints for MySQL, PostgreSQL, Cosmos DB, SQL Server and Azure SQL. REST (Representational State Transfer) endpoints allow developers to easily query a single table, view or stored procedure. However, Data API builder also exposes GraphQL endpoints. Like REST, GraphQL returns data, but unlike REST, GraphQL can return data from multiple […]<\/p>\n","protected":false},"author":96788,"featured_media":2563,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[],"class_list":["post-2751","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-azure-sql"],"acf":[],"blog_post_summary":"
Data API builder exposes REST endpoints for MySQL, PostgreSQL, Cosmos DB, SQL Server and Azure SQL. REST (Representational State Transfer) endpoints allow developers to easily query a single table, view or stored procedure. However, Data API builder also exposes GraphQL endpoints. Like REST, GraphQL returns data, but unlike REST, GraphQL can return data from multiple […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts\/2751","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/users\/96788"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/comments?post=2751"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts\/2751\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/media\/2563"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/media?parent=2751"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/categories?post=2751"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/tags?post=2751"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}