{"id":1802,"date":"2023-03-28T23:59:42","date_gmt":"2023-03-29T06:59:42","guid":{"rendered":"https:\/\/devblogs.microsoft.com\/azure-sql\/?p=1802"},"modified":"2023-03-28T13:52:38","modified_gmt":"2023-03-28T20:52:38","slug":"data-api-builder-quickstart-demo-from-vs-live-vegas","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/azure-sql\/data-api-builder-quickstart-demo-from-vs-live-vegas\/","title":{"rendered":"Data API Builder Quickstart Demo from VS Live Vegas"},"content":{"rendered":"<p dir=\"auto\">I was fortunate enough to be able to present Data API builder the week of March 20th at VS Live in Las Vegas and what an incredible group of attendees at that conference. During my session, I stepped through a live demo of Data API builder showing off REST and GraphQL endpoints on various database objects. This blog post contains the steps I performed in the demo so that you can perform them as well and create your first set of REST\/GraphQL endpoints on a SQL Database. <strong>This quickstart is using 100% local components which are 100% free to use.<\/strong><\/p>\n<p dir=\"auto\">Remember, while this quickstart uses SQL Server\/Azure SQL Database, Data API builder can also be used with Azure mySQL, Azure Cosmos DB, and Azure Postgres!<\/p>\n<h2 dir=\"auto\"><a id=\"user-content-prerequisites\" class=\"anchor\" href=\"https:\/\/github.com\/JetterMcTedder\/dabDemoJamStack\/blob\/main\/click-sheet.md#prerequisites\" aria-hidden=\"true\"><\/a>Prerequisites<\/h2>\n<p>Here is what you need to get started on this Data API builder quickstart demo. Ensure that they are all installed before starting.<\/p>\n<ul dir=\"auto\">\n<li><a href=\"https:\/\/code.visualstudio.com\/\">VS Code<\/a><\/li>\n<li><a href=\"https:\/\/learn.microsoft.com\/en-us\/sql\/azure-data-studio\/download-azure-data-studio\">Azure Data Studio<\/a><\/li>\n<li><a href=\"https:\/\/learn.microsoft.com\/en-us\/azure\/azure-functions\/functions-run-local?tabs=v4%2Cwindows%2Ccsharp%2Cportal%2Cbash#install-the-azure-functions-core-tools\">Azure Functions Core Tools<\/a><\/li>\n<li><a href=\"https:\/\/dotnet.microsoft.com\/en-us\/download\/dotnet\/7.0\">.NET SDK 6+<\/a><\/li>\n<li><a href=\"https:\/\/github.com\/microsoft\/go-sqlcmd\">Go-sqlcmd<\/a><\/li>\n<li><a href=\"https:\/\/learn.microsoft.com\/en-us\/sql\/tools\/sqlpackage\/sqlpackage-download\">SqlPackage<\/a><\/li>\n<li><a href=\"https:\/\/github.com\/Azure\/data-api-builder\">Data API Builder<\/a><\/li>\n<li><a href=\"https:\/\/www.docker.com\/products\/docker-desktop\/\">Docker desktop<\/a><\/li>\n<\/ul>\n<h2 dir=\"auto\"><a id=\"user-content-click-though-demo\" class=\"anchor\" href=\"https:\/\/github.com\/JetterMcTedder\/dabDemoJamStack\/blob\/main\/click-sheet.md#click-though-demo\" aria-hidden=\"true\"><\/a>Quickstart Demo<\/h2>\n<h3 dir=\"auto\"><a id=\"user-content-use-dabcli-to-create-config-file-and-add-objects\" class=\"anchor\" href=\"https:\/\/github.com\/JetterMcTedder\/dabDemoJamStack\/blob\/main\/click-sheet.md#use-dabcli-to-create-config-file-and-add-objects\" aria-hidden=\"true\"><\/a>Use dabcli to create the config file and add objects<\/h3>\n<ol dir=\"auto\">\n<li>\n<p dir=\"auto\">Start at a command prompt\/powershell and create a project directory<\/p>\n<div class=\"highlight highlight-source-shell notranslate position-relative overflow-auto\" dir=\"auto\">\n<pre>mkdir dabdemo<\/pre>\n<\/div>\n<\/li>\n<li>\n<p dir=\"auto\">Enter the directory<\/p>\n<div class=\"highlight highlight-source-shell notranslate position-relative overflow-auto\" dir=\"auto\">\n<pre><span class=\"pl-c1\">cd<\/span> dabdemo<\/pre>\n<\/div>\n<\/li>\n<li>\n<p dir=\"auto\">and create a Database Project using the new templates via .NET<\/p>\n<div class=\"highlight highlight-source-shell notranslate position-relative overflow-auto\" dir=\"auto\">\n<pre>dotnet new sqlproj -n <span class=\"pl-s\"><span class=\"pl-pds\">\"<\/span>dabDB<span class=\"pl-pds\">\"<\/span><\/span> -tp <span class=\"pl-s\"><span class=\"pl-pds\">\"<\/span>SqlAzureV12<span class=\"pl-pds\">\"<\/span><\/span><\/pre>\n<\/div>\n<\/li>\n<li>\n<p dir=\"auto\">Next, create a database locally with go-sqlcmd and DOcker Desktop. This command will grab the image from the repository and create a full SQL Server 2022 instance (Developer Editon) your local machine<\/p>\n<div class=\"highlight highlight-source-shell notranslate position-relative overflow-auto\" dir=\"auto\">\n<pre>sqlcmd create mssql -u dabDB --accept-eula<\/pre>\n<\/div>\n<\/li>\n<li>\n<p dir=\"auto\">Open Azure Data Studio from the current directory when the database is done creating. Issuing this command, you will automatically be connected to the database and an entry will be created in ADS as well<\/p>\n<div class=\"highlight highlight-source-shell notranslate position-relative overflow-auto\" dir=\"auto\">\n<pre>sqlcmd open ads<\/pre>\n<\/div>\n<\/li>\n<li>\n<p dir=\"auto\">Create 2 tables: a customer and address table. During the presentation, I used Table Designer but here is the resulting SQL code:<\/p>\n<div>\n<pre style=\"padding-left: 40px\">CREATE TABLE [dbo].[customer] (\r\n  \u00a0 [customer_id] \u00a0 \u00a0 \u00a0 \u00a0INT \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0IDENTITY (1, 1) NOT NULL PRIMARY KEY CLUSTERED ([customer_id] ASC),\r\n  \u00a0 [customer_name] \u00a0 \u00a0 \u00a0NVARCHAR (200) NOT NULL,\r\n  \u00a0 [customer_email] \u00a0 \u00a0 NVARCHAR (200) NOT NULL,\r\n  \u00a0 [customer_sales_rep] NVARCHAR (200) NOT NULL\r\n);<\/pre>\n<\/div>\n<pre style=\"padding-left: 40px\"><span style=\"font-size: 14.4px;text-align: var(--bs-body-text-align)\">CREATE TABLE [dbo].[address] (\r\n<\/span>  \u00a0 [address_id] \u00a0INT \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0IDENTITY (1, 1) NOT NULL PRIMARY KEY CLUSTERED ([address_id] ASC),\r\n  \u00a0 [customer_id] INT \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0NOT NULL,\r\n  \u00a0 [address] \u00a0 \u00a0 NVARCHAR (200) NOT NULL,\r\n  \u00a0 CONSTRAINT [FK_address_customer] FOREIGN KEY ([customer_id]) REFERENCES [dbo].[customer] ([customer_id])\r\n);<\/pre>\n<\/li>\n<li>\n<p dir=\"auto\">Publish the tables to the local docker database in Azure Data Studio\n<a href=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/03\/Screenshot-2023-03-27-094814.png\"><img decoding=\"async\" class=\"alignnone size-full wp-image-1813\" src=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/03\/Screenshot-2023-03-27-094814.png\" alt=\"Image Screenshot 2023 03 27 094814\" width=\"606\" height=\"346\" srcset=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/03\/Screenshot-2023-03-27-094814.png 606w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/03\/Screenshot-2023-03-27-094814-300x171.png 300w\" sizes=\"(max-width: 606px) 100vw, 606px\" \/><\/a><\/p>\n<\/li>\n<li>\n<p dir=\"auto\">Insert data into the tables<\/p>\n<div class=\"highlight highlight-source-sql notranslate position-relative overflow-auto\" dir=\"auto\">\n<pre><span class=\"pl-k\">insert into<\/span> <span class=\"pl-c1\">dbo<\/span>.<span class=\"pl-c1\">customer<\/span>(customer_name, customer_email, customer_sales_rep) <span class=\"pl-k\">values<\/span>(<span class=\"pl-s\"><span class=\"pl-pds\">'<\/span>Bill<span class=\"pl-pds\">'<\/span><\/span>,<span class=\"pl-s\"><span class=\"pl-pds\">'<\/span>bill@computer.com<span class=\"pl-pds\">'<\/span><\/span>,<span class=\"pl-s\"><span class=\"pl-pds\">'<\/span>Marlene<span class=\"pl-pds\">'<\/span><\/span>);\r\n<span class=\"pl-k\">insert into<\/span> <span class=\"pl-c1\">dbo<\/span>.<span class=\"pl-c1\">customer<\/span>(customer_name, customer_email, customer_sales_rep) <span class=\"pl-k\">values<\/span>(<span class=\"pl-s\"><span class=\"pl-pds\">'<\/span>Frank<span class=\"pl-pds\">'<\/span><\/span>, <span class=\"pl-s\"><span class=\"pl-pds\">'<\/span>frank@computer.com<span class=\"pl-pds\">'<\/span><\/span>,<span class=\"pl-s\"><span class=\"pl-pds\">'<\/span>Riley<span class=\"pl-pds\">'<\/span><\/span>);\r\n<span class=\"pl-k\">select<\/span> <span class=\"pl-k\">*<\/span> <span class=\"pl-k\">from<\/span> customer\r\n<span class=\"pl-k\">insert into<\/span> address (customer_id, address) <span class=\"pl-k\">values<\/span> (<span class=\"pl-c1\">1<\/span>, <span class=\"pl-s\"><span class=\"pl-pds\">'<\/span>Lincoln, MA<span class=\"pl-pds\">'<\/span><\/span>);\r\n<span class=\"pl-k\">insert into<\/span> address (customer_id, address) <span class=\"pl-k\">values<\/span> (<span class=\"pl-c1\">2<\/span>, <span class=\"pl-s\"><span class=\"pl-pds\">'<\/span>Baltimore, MD<span class=\"pl-pds\">'<\/span><\/span>);\r\n<span class=\"pl-k\">select<\/span> <span class=\"pl-c1\">c<\/span>.<span class=\"pl-c1\">customer_name<\/span>, <span class=\"pl-c1\">a<\/span>.<span class=\"pl-c1\">address<\/span>\r\n<span class=\"pl-k\">from<\/span> customer c, address a\r\n<span class=\"pl-k\">where<\/span> <span class=\"pl-c1\">c<\/span>.<span class=\"pl-c1\">customer_id<\/span> <span class=\"pl-k\">=<\/span> <span class=\"pl-c1\">a<\/span>.<span class=\"pl-c1\">customer_id<\/span>;\r\ngo<\/pre>\n<\/div>\n<\/li>\n<li>\n<p dir=\"auto\">Back at the command line, get the user\/password using sqlcmd<\/p>\n<div class=\"highlight highlight-source-sql notranslate position-relative overflow-auto\" dir=\"auto\">\n<pre>sqlcmd config connection-strings<\/pre>\n<\/div>\n<\/li>\n<li>\n<p dir=\"auto\">Open VS Code in this directory<\/p>\n<div class=\"highlight highlight-source-shell notranslate position-relative overflow-auto\" dir=\"auto\">\n<pre>code <span class=\"pl-c1\">.<\/span><\/pre>\n<\/div>\n<\/li>\n<li>\n<p dir=\"auto\">Open a new terminal in VS Code<\/p>\n<\/li>\n<\/ol>\n<p><a href=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/03\/Screenshot-2023-03-27-100549.png\"><img decoding=\"async\" class=\"alignnone size-full wp-image-1815\" src=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/03\/Screenshot-2023-03-27-100549.png\" alt=\"Image Screenshot 2023 03 27 100549\" width=\"733\" height=\"379\" srcset=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/03\/Screenshot-2023-03-27-100549.png 733w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/03\/Screenshot-2023-03-27-100549-300x155.png 300w\" sizes=\"(max-width: 733px) 100vw, 733px\" \/><\/a><\/p>\n<ol dir=\"auto\">\n<li>\n<p dir=\"auto\">Create the dab config file and DB connection (to local database). <strong>Remember to fill in the USER, PASSWORD and if you are using a port other than 1433.<\/strong><\/p>\n<div class=\"highlight highlight-source-shell notranslate position-relative overflow-auto\" dir=\"auto\">\n<pre>dab init --database-type <span class=\"pl-s\"><span class=\"pl-pds\">\"<\/span>mssql<span class=\"pl-pds\">\"<\/span><\/span> --connection-string <span class=\"pl-s\"><span class=\"pl-pds\">\"<\/span>Server=localhost;Database=dabDB;User ID=USER;Password=PASSWORD;TrustServerCertificate=true<span class=\"pl-pds\">\"<\/span><\/span> --host-mode <span class=\"pl-s\"><span class=\"pl-pds\">\"<\/span>Development<span class=\"pl-pds\">\"<\/span><\/span><\/pre>\n<\/div>\n<\/li>\n<li>\n<p dir=\"auto\">Add the customer and address tables to the dab config file by issuing these commands at the terminal as well.<\/p>\n<div class=\"highlight highlight-source-shell notranslate position-relative overflow-auto\" dir=\"auto\">\n<pre>dab add customer --source dbo.customer --permissions <span class=\"pl-s\"><span class=\"pl-pds\">\"<\/span>anonymous:*<span class=\"pl-pds\">\"<\/span><\/span>\r\ndab add address --source dbo.address --permissions <span class=\"pl-s\"><span class=\"pl-pds\">\"<\/span>anonymous:*<span class=\"pl-pds\">\"<\/span><\/span><\/pre>\n<\/div>\n<\/li>\n<\/ol>\n<h3 dir=\"auto\"><a id=\"user-content-start-dab-and-review-endpoints\" class=\"anchor\" href=\"https:\/\/github.com\/JetterMcTedder\/dabDemoJamStack\/blob\/main\/click-sheet.md#start-dab-and-review-endpoints\" aria-hidden=\"true\"><\/a>Start dab<\/h3>\n<ol dir=\"auto\">\n<li>Run DAB locally by running the following command in the terminal.<\/li>\n<\/ol>\n<pre dir=\"auto\" style=\"padding-left: 40px\">dab start<\/pre>\n<h4 dir=\"auto\"><a id=\"user-content-rest\" class=\"anchor\" href=\"https:\/\/github.com\/JetterMcTedder\/dabDemoJamStack\/blob\/main\/click-sheet.md#rest\" aria-hidden=\"true\"><\/a>REST Endpoints<\/h4>\n<ol dir=\"auto\">\n<li>\n<p dir=\"auto\">The REST examples can be done using a REST client extension in VS Code or via a REST client application such as <a href=\"https:\/\/www.postman.com\/downloads\/\">Postman<\/a> or <a href=\"https:\/\/insomnia.rest\/download\">Insomnia<\/a>. All the examples will use the following endpoints:<\/p>\n<div class=\"highlight highlight-source-shell notranslate position-relative overflow-auto\" dir=\"auto\">\n<pre>https:\/\/localhost:5001\/api\/customer\r\nhttps:\/\/localhost:5001\/api\/address<\/pre>\n<\/div>\n<\/li>\n<li>\n<p dir=\"auto\">Get by Primary Key<\/p>\n<div class=\"highlight highlight-source-shell notranslate position-relative overflow-auto\" dir=\"auto\">\n<pre>https:\/\/localhost:5001\/api\/customers\/customer_id\/1<\/pre>\n<\/div>\n<\/li>\n<li>\n<p dir=\"auto\">Filter columns using select<\/p>\n<div class=\"highlight highlight-source-shell notranslate position-relative overflow-auto\" dir=\"auto\">\n<pre>https:\/\/localhost:5001\/api\/customers<span class=\"pl-k\">?<\/span><span class=\"pl-smi\">$select<\/span>=customer_email<\/pre>\n<\/div>\n<\/li>\n<li>\n<p dir=\"auto\">Operations: equals and not equals<\/p>\n<div class=\"highlight highlight-source-shell notranslate position-relative overflow-auto\" dir=\"auto\">\n<pre>https:\/\/localhost:5001\/api\/customers<span class=\"pl-k\">?<\/span><span class=\"pl-smi\">$filter<\/span>=customer_name eq <span class=\"pl-s\"><span class=\"pl-pds\">'<\/span>bill<span class=\"pl-pds\">'<\/span><\/span>\r\nhttps:\/\/localhost:5001\/api\/customers<span class=\"pl-k\">?<\/span><span class=\"pl-smi\">$filter<\/span>=customer_name ne <span class=\"pl-s\"><span class=\"pl-pds\">'<\/span>bill<span class=\"pl-pds\">'<\/span><\/span>\r\nhttps:\/\/localhost:5001\/api\/customers<span class=\"pl-k\">?<\/span><span class=\"pl-smi\">$filter<\/span>=customer_sales_rep eq <span class=\"pl-s\"><span class=\"pl-pds\">'<\/span>Riley<span class=\"pl-pds\">'<\/span><\/span><\/pre>\n<\/div>\n<\/li>\n<li>\n<p dir=\"auto\">Sort results with orderby<\/p>\n<div class=\"highlight highlight-source-shell notranslate position-relative overflow-auto\" dir=\"auto\">\n<pre>https:\/\/localhost:5001\/api\/customers<span class=\"pl-k\">?<\/span><span class=\"pl-smi\">$orderby<\/span>=customer_id desc<\/pre>\n<\/div>\n<\/li>\n<li>\n<p dir=\"auto\">Using POST to create a record. <strong>Same endpoint but instead of using a GET method, this is a POST.<\/strong><\/p>\n<div class=\"highlight highlight-source-shell notranslate position-relative overflow-auto\" dir=\"auto\">\n<pre>POST https:\/\/localhost:5001\/api\/customers\r\n\r\n{\r\n    <span class=\"pl-s\"><span class=\"pl-pds\">\"<\/span>customer_name<span class=\"pl-pds\">\"<\/span><\/span>: <span class=\"pl-s\"><span class=\"pl-pds\">\"<\/span>Joel<span class=\"pl-pds\">\"<\/span><\/span>,\r\n    <span class=\"pl-s\"><span class=\"pl-pds\">\"<\/span>customer_email<span class=\"pl-pds\">\"<\/span><\/span>: <span class=\"pl-s\"><span class=\"pl-pds\">\"<\/span>joel@computer.com<span class=\"pl-pds\">\"<\/span><\/span>,\r\n    <span class=\"pl-s\"><span class=\"pl-pds\">\"<\/span>customer_sales_rep<span class=\"pl-pds\">\"<\/span><\/span>: <span class=\"pl-s\"><span class=\"pl-pds\">\"<\/span>Riley<span class=\"pl-pds\">\"<\/span><\/span>\r\n}<\/pre>\n<\/div>\n<\/li>\n<li>\n<p dir=\"auto\">Using PUT to create a record<b>\u00a0<\/b>when you are using a primary key with managed identity will result in an error. In this case, it is best to use a PATCH.<\/p>\n<div class=\"highlight highlight-source-shell notranslate position-relative overflow-auto\" dir=\"auto\">\n<pre><\/pre>\n<\/div>\n<\/li>\n<li>\n<p dir=\"auto\">Using a PATCH to create or update a record<\/p>\n<div class=\"highlight highlight-source-shell notranslate position-relative overflow-auto\" dir=\"auto\">\n<pre>PATCH https:\/\/localhost:5001\/api\/customers\/customer_id\/3\r\n\r\n{\r\n    <span class=\"pl-s\"><span class=\"pl-pds\">\"<\/span>customer_email<span class=\"pl-pds\">\"<\/span><\/span>: <span class=\"pl-s\"><span class=\"pl-pds\">\"<\/span>joel2@computer.com<span class=\"pl-pds\">\"<\/span><\/span>\r\n}<\/pre>\n<\/div>\n<\/li>\n<li>\n<p dir=\"auto\">Using a DELETE to delete a record<\/p>\n<div class=\"highlight highlight-source-shell notranslate position-relative overflow-auto\" dir=\"auto\">\n<pre>DELETE https:\/\/localhost:5001\/api\/Customers\/customer_id\/3<\/pre>\n<\/div>\n<\/li>\n<\/ol>\n<h3 dir=\"auto\"><a id=\"user-content-graphql\" class=\"anchor\" href=\"https:\/\/github.com\/JetterMcTedder\/dabDemoJamStack\/blob\/main\/click-sheet.md#graphql\" aria-hidden=\"true\"><\/a>GraphQL Endpoints<\/h3>\n<p dir=\"auto\"><strong>All examples use POST and the endpoint <a href=\"https:\/\/localhost:5001\/graphql\/\" rel=\"nofollow\">https:\/\/localhost:5001\/graphql\/<\/a><\/strong><\/p>\n<ol dir=\"auto\">\n<li>\n<p dir=\"auto\">A simple get using GraphQL<\/p>\n<div class=\"highlight highlight-source-shell notranslate position-relative overflow-auto\" dir=\"auto\">\n<pre>{\r\n  customers(first: 5) {\r\n    items {\r\n      customer_id\r\n      customer_name\r\n    }\r\n}}<\/pre>\n<\/div>\n<\/li>\n<li>\n<p dir=\"auto\">Using an orderBy to sort the results<\/p>\n<div class=\"highlight highlight-source-shell notranslate position-relative overflow-auto\" dir=\"auto\">\n<pre>{\r\n  customers(orderBy: {customer_id: DESC} )\r\n  {\r\n    items {\r\n      customer_id\r\n      customer_name\r\n      customer_email\r\n    }\r\n  }\r\n}<\/pre>\n<\/div>\n<\/li>\n<li>\n<p dir=\"auto\">Get a record by primary key. Data API builder will automatically create the <span style=\"font-family: 'courier new', courier, monospace\">customers_by_pk<\/span> function.<\/p>\n<div class=\"highlight highlight-source-shell notranslate position-relative overflow-auto\" dir=\"auto\">\n<pre>{\r\n  customers_by_pk(customer_id: 1) {\r\n      customer_id\r\n      customer_name\r\n      customer_email\r\n      customer_sales_rep\r\n    }\r\n}<\/pre>\n<\/div>\n<\/li>\n<li>\n<p dir=\"auto\">Create relationship one to many. To do this, stop DAB via the terminal in VS Code and run the following command. After running the command, restart DAB with <span style=\"font-family: 'courier new', courier, monospace\">dab start<\/span> and try the example.<\/p>\n<div class=\"highlight highlight-source-shell notranslate position-relative overflow-auto\" dir=\"auto\">\n<pre>dab update customer --relationship <span class=\"pl-s\"><span class=\"pl-pds\">\"<\/span>address<span class=\"pl-pds\">\"<\/span><\/span> --cardinality <span class=\"pl-s\"><span class=\"pl-pds\">\"<\/span>many<span class=\"pl-pds\">\"<\/span><\/span> --target.entity <span class=\"pl-s\"><span class=\"pl-pds\">\"<\/span>address<span class=\"pl-pds\">\"\r\n\r\n{ \r\n customers\r\n { \r\n   items { \r\n     customer_id \r\n     customer_name \r\n     customer_email\r\n     customer_sales_rep \r\n     address {\r\n       items { \r\n         address\r\n       } \r\n      }\r\n    } \r\n  } \r\n}\r\n<\/span><\/span><\/pre>\n<\/div>\n<\/li>\n<li>\n<p dir=\"auto\">Create relationship many to one. To do this, stop DAB via the terminal in VS Code and run the following command. After running the command, restart DAB with <span style=\"font-family: 'courier new', courier, monospace\">dab start<\/span> and try the example.<\/p>\n<div class=\"highlight highlight-source-shell notranslate position-relative overflow-auto\" dir=\"auto\">\n<pre>dab update address --relationship <span class=\"pl-s\"><span class=\"pl-pds\">\"<\/span>customer<span class=\"pl-pds\">\"<\/span><\/span> --cardinality <span class=\"pl-s\"><span class=\"pl-pds\">\"<\/span>one<span class=\"pl-pds\">\"<\/span><\/span> --target.entity <span class=\"pl-s\"><span class=\"pl-pds\">\"<\/span>customer<span class=\"pl-pds\">\"<\/span><\/span><\/pre>\n<\/div>\n<div class=\"highlight highlight-source-shell notranslate position-relative overflow-auto\" dir=\"auto\">\n<pre>{\r\n    addresses {\r\n\titems {\r\n\t    address\r\n\t    customer {\r\n\t\tcustomer_name\r\n\t\tcustomer_id\r\n\t\tcustomer_email\r\n\t    }\r\n\t}\r\n    }\r\n}<\/pre>\n<\/div>\n<\/li>\n<li>\n<p dir=\"auto\">Filter results using filter<\/p>\n<div class=\"highlight highlight-source-shell notranslate position-relative overflow-auto\" dir=\"auto\">\n<pre>{\r\n  customers(filter: { customer_sales_rep: { contains: <span class=\"pl-s\"><span class=\"pl-pds\">\"<\/span>Mar<span class=\"pl-pds\">\"<\/span><\/span> } })\r\n  {\r\n    items {\r\n      customer_id\r\n      customer_name\r\n      customer_sales_rep\r\n    }\r\n  }\r\n}<\/pre>\n<\/div>\n<\/li>\n<li>\n<p dir=\"auto\">Filter results with a relationship you created in the previous examples<\/p>\n<div class=\"highlight highlight-source-shell notranslate position-relative overflow-auto\" dir=\"auto\">\n<pre>{\r\n  customers(filter: { customer_name: { contains: <span class=\"pl-s\"><span class=\"pl-pds\">\"<\/span>ill<span class=\"pl-pds\">\"<\/span><\/span> } })\r\n  {\r\n    items {\r\n      customer_id\r\n      customer_name\r\n      customer_sales_rep\r\n      address {\r\n\titems {\r\n\t  address\r\n\t}\r\n      }\r\n    }\r\n  }\r\n}<\/pre>\n<\/div>\n<\/li>\n<li>\n<p dir=\"auto\">Create customer with mutation and GraphQL<\/p>\n<div class=\"highlight highlight-source-shell notranslate position-relative overflow-auto\" dir=\"auto\">\n<pre>mutation {\r\n  createcustomers(item: {\r\n    customer_name: <span class=\"pl-s\"><span class=\"pl-pds\">\"<\/span>Elle<span class=\"pl-pds\">\"<\/span><\/span>,\r\n    customer_email: <span class=\"pl-s\"><span class=\"pl-pds\">\"<\/span>elle@computer.com<span class=\"pl-pds\">\"<\/span><\/span>    \r\n    customer_sales_rep: <span class=\"pl-s\"><span class=\"pl-pds\">\"<\/span>Riley<span class=\"pl-pds\">\"<\/span><\/span>    \r\n  }) {\r\n    customer_name\r\n    customer_email\r\n    customer_sales_rep\r\n  }  \r\n}<\/pre>\n<\/div>\n<\/li>\n<li>\n<p dir=\"auto\">Update customers using mutation and GraphQL<\/p>\n<div class=\"highlight highlight-source-shell notranslate position-relative overflow-auto\" dir=\"auto\">\n<pre>mutation {\r\n  updatecustomers(customer_id: 1003, item: {\r\n    customer_email: <span class=\"pl-s\"><span class=\"pl-pds\">\"<\/span>ssss2@ssss.xxxx<span class=\"pl-pds\">\"<\/span><\/span>\r\n  }) {\r\n    customer_id\r\n    customer_email\r\n  }\r\n}<\/pre>\n<\/div>\n<\/li>\n<li>\n<p dir=\"auto\">Delete customers using mutation and GraphQL<\/p>\n<div class=\"highlight highlight-source-shell notranslate position-relative overflow-auto\" dir=\"auto\">\n<pre>mutation {\r\n  deletecustomers(customer_id: 1004)\r\n  {\r\n    customer_id\r\n  }  \r\n}<\/pre>\n<\/div>\n<\/li>\n<\/ol>\n<h3 dir=\"auto\"><a id=\"user-content-stored-procedures\" class=\"anchor\" href=\"https:\/\/github.com\/JetterMcTedder\/dabDemoJamStack\/blob\/main\/click-sheet.md#stored-procedures\" aria-hidden=\"true\"><\/a>Stored Procedures<\/h3>\n<ol dir=\"auto\">\n<li>\n<p dir=\"auto\">Open a new query sheet in Azure Data Studio connected to the database running in Docker and run the following code:<\/p>\n<div class=\"highlight highlight-source-sql notranslate position-relative overflow-auto\" dir=\"auto\">\n<pre>create <span class=\"pl-k\">or<\/span> alter procedure <span class=\"pl-c1\">dbo<\/span>.<span class=\"pl-c1\">get_customers_by_rep<\/span>\r\n@rep nvarchar(<span class=\"pl-c1\">200<\/span>)\r\n<span class=\"pl-k\">as<\/span>\r\n<span class=\"pl-k\">select<\/span> <span class=\"pl-k\">*<\/span> <span class=\"pl-k\">from<\/span> <span class=\"pl-c1\">dbo<\/span>.<span class=\"pl-c1\">customer<\/span> <span class=\"pl-k\">where<\/span> customer_sales_rep <span class=\"pl-k\">=<\/span> @rep;\r\ngo<\/pre>\n<\/div>\n<\/li>\n<li>\n<p dir=\"auto\">Run the following command to test the stored procedure in Azure Data Studio<\/p>\n<div class=\"highlight highlight-source-sql notranslate position-relative overflow-auto\" dir=\"auto\">\n<pre>exec <span class=\"pl-c1\">dbo<\/span>.<span class=\"pl-c1\">get_customers_by_rep<\/span> <span class=\"pl-s\"><span class=\"pl-pds\">'<\/span>Marlene<span class=\"pl-pds\">'<\/span><\/span>;<\/pre>\n<\/div>\n<\/li>\n<li>\n<p dir=\"auto\">Stop DAB and run the following to register the stored procedure<\/p>\n<div class=\"highlight highlight-source-shell notranslate position-relative overflow-auto\" dir=\"auto\">\n<pre>dab add getCustomersByRep --source dbo.get_customers_by_rep --source.type <span class=\"pl-s\"><span class=\"pl-pds\">\"<\/span>stored-procedure<span class=\"pl-pds\">\"<\/span><\/span> --source.params <span class=\"pl-s\"><span class=\"pl-pds\">\"<\/span>rep:<span class=\"pl-pds\">\"<\/span><\/span> --permissions <span class=\"pl-s\"><span class=\"pl-pds\">\"<\/span>anonymous:execute<span class=\"pl-pds\">\"<\/span><\/span> --rest.methods <span class=\"pl-s\"><span class=\"pl-pds\">\"<\/span>get<span class=\"pl-pds\">\"<\/span><\/span> --graphql.operation <span class=\"pl-s\"><span class=\"pl-pds\">\"<\/span>query<span class=\"pl-pds\">\"<\/span><\/span><\/pre>\n<\/div>\n<\/li>\n<li>\n<p dir=\"auto\">Start DAB<\/p>\n<pre dir=\"auto\">dab start<\/pre>\n<\/li>\n<li>\n<p dir=\"auto\">Use the procedure&#8217;s REST endpoint passing in Riley into the rep parameter.<\/p>\n<div class=\"highlight highlight-source-shell notranslate position-relative overflow-auto\" dir=\"auto\">\n<pre>https:\/\/localhost:5001\/api\/getCustomersByRep<span class=\"pl-k\">?<\/span>rep=Riley<\/pre>\n<\/div>\n<\/li>\n<li>\n<p dir=\"auto\">Use the procedure&#8217;s GraphQL endpoint passing in Riley into the rep parameter. DAB automatically creates the <span style=\"font-family: 'courier new', courier, monospace\">executegetCustomersByRep<\/span> function for you.<\/p>\n<div class=\"highlight highlight-source-shell notranslate position-relative overflow-auto\" dir=\"auto\">\n<pre>https:\/\/localhost:5001\/graphql\/\r\nquery {\r\n  executegetCustomersByRep(rep:<span class=\"pl-s\"><span class=\"pl-pds\">\"<\/span>Riley<span class=\"pl-pds\">\"<\/span><\/span>)\r\n   {\r\n    customer_id\r\n    customer_name\r\n    customer_email\r\n  }\r\n}<\/pre>\n<\/div>\n<\/li>\n<\/ol>\n<h3 dir=\"auto\"><a id=\"user-content-authentication-and-authorization\" class=\"anchor\" href=\"https:\/\/github.com\/JetterMcTedder\/dabDemoJamStack\/blob\/main\/click-sheet.md#authentication-and-authorization\" aria-hidden=\"true\"><\/a>Authentication and Authorization<\/h3>\n<div class=\"snippet-clipboard-content notranslate position-relative overflow-auto\">\n<p class=\"notranslate\">Start by changing the provider property in the dab-config.json filer to <strong>Simulator<\/strong>.<\/p>\n<\/div>\n<p><a href=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/03\/Screenshot-2023-03-27-104132.png\"><img decoding=\"async\" class=\"alignnone size-full wp-image-1819\" src=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/03\/Screenshot-2023-03-27-104132.png\" alt=\"Image Screenshot 2023 03 27 104132\" width=\"468\" height=\"224\" srcset=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/03\/Screenshot-2023-03-27-104132.png 468w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/03\/Screenshot-2023-03-27-104132-300x144.png 300w\" sizes=\"(max-width: 468px) 100vw, 468px\" \/><\/a><\/p>\n<p>Save the file.<\/p>\n<div class=\"snippet-clipboard-content notranslate position-relative overflow-auto\">\n<p class=\"notranslate\">We can now utilize the header parameter <strong>X-MS-API-ROLE<\/strong> in our REST\/GraphQL calls.<\/p>\n<\/div>\n<ol dir=\"auto\">\n<li>\n<p dir=\"auto\">Stop DAB and change the permissions section in the customer entity to:<\/p>\n<div class=\"highlight highlight-source-shell notranslate position-relative overflow-auto\" dir=\"auto\">\n<pre><span class=\"pl-s\"><span class=\"pl-pds\">\"<\/span>permissions<span class=\"pl-pds\">\"<\/span><\/span>: [\r\n\t{\r\n\t  <span class=\"pl-s\"><span class=\"pl-pds\">\"<\/span>role<span class=\"pl-pds\">\"<\/span><\/span>: <span class=\"pl-s\"><span class=\"pl-pds\">\"<\/span>anonymous<span class=\"pl-pds\">\"<\/span><\/span>,\r\n\t  <span class=\"pl-s\"><span class=\"pl-pds\">\"<\/span>actions<span class=\"pl-pds\">\"<\/span><\/span>: [ <span class=\"pl-s\"><span class=\"pl-pds\">\"<\/span>read<span class=\"pl-pds\">\"<\/span><\/span> ]\r\n      },\r\n      {\r\n\t  <span class=\"pl-s\"><span class=\"pl-pds\">\"<\/span>role<span class=\"pl-pds\">\"<\/span><\/span>: <span class=\"pl-s\"><span class=\"pl-pds\">\"<\/span>admin<span class=\"pl-pds\">\"<\/span><\/span>,\r\n\t  <span class=\"pl-s\"><span class=\"pl-pds\">\"<\/span>actions<span class=\"pl-pds\">\"<\/span><\/span>: [ <span class=\"pl-s\"><span class=\"pl-pds\">\"<\/span>*<span class=\"pl-pds\">\"<\/span><\/span> ]\r\n      }\r\n      ],<\/pre>\n<\/div>\n<p dir=\"auto\"><div class=\"alert alert-info\"><strong>SAVE THE FILE! (I have many times forgotten to do this and then wondered why this part didn&#8217;t work)<\/strong><\/div><\/p>\n<p>What this change is saying is that when I am calling this API and do not have the admin role, I can only issue GETs, otherwise I will get a 403 response. If I have the admin role, I can use all the methods (GET\/PUT\/PATCH\/POST\/DELETE).<\/li>\n<li>\n<p dir=\"auto\">Using the REST client, call the following GET<\/p>\n<pre dir=\"auto\">https:\/\/localhost:5001\/api\/customer<\/pre>\n<\/li>\n<li>\n<p dir=\"auto\">Using the REST client, call the following POST. You should see a response of 403, forbidden.<\/p>\n<div class=\"highlight highlight-source-shell notranslate position-relative overflow-auto\" dir=\"auto\">\n<pre>{\r\n    <span class=\"pl-s\"><span class=\"pl-pds\">\"<\/span>customer_name<span class=\"pl-pds\">\"<\/span><\/span>: <span class=\"pl-s\"><span class=\"pl-pds\">\"<\/span>Tommy<span class=\"pl-pds\">\"<\/span><\/span>,\r\n    <span class=\"pl-s\"><span class=\"pl-pds\">\"<\/span>customer_email<span class=\"pl-pds\">\"<\/span><\/span>: <span class=\"pl-s\"><span class=\"pl-pds\">\"<\/span>Tommy@computer.com<span class=\"pl-pds\">\"<\/span><\/span>,\r\n    <span class=\"pl-s\"><span class=\"pl-pds\">\"<\/span>customer_sales_rep<span class=\"pl-pds\">\"<\/span><\/span>: <span class=\"pl-s\"><span class=\"pl-pds\">\"<\/span>Marlene<span class=\"pl-pds\">\"<\/span><\/span>\r\n}<\/pre>\n<\/div>\n<\/li>\n<li>\n<p dir=\"auto\">Add the header info for admin and retry POST. You should see that the record was created<\/p>\n<div class=\"highlight highlight-source-shell notranslate position-relative overflow-auto\" dir=\"auto\">\n<pre>X-MS-API-ROLE: admin<\/pre>\n<pre>https:\/\/localhost:5001\/api\/customer\r\n{\r\n    <span class=\"pl-s\"><span class=\"pl-pds\">\"<\/span>customer_name<span class=\"pl-pds\">\"<\/span><\/span>: <span class=\"pl-s\"><span class=\"pl-pds\">\"<\/span>Tommy<span class=\"pl-pds\">\"<\/span><\/span>,\r\n    <span class=\"pl-s\"><span class=\"pl-pds\">\"<\/span>customer_email<span class=\"pl-pds\">\"<\/span><\/span>: <span class=\"pl-s\"><span class=\"pl-pds\">\"<\/span>Tommy@computer.com<span class=\"pl-pds\">\"<\/span><\/span>,\r\n    <span class=\"pl-s\"><span class=\"pl-pds\">\"<\/span>customer_sales_rep<span class=\"pl-pds\">\"<\/span><\/span>: <span class=\"pl-s\"><span class=\"pl-pds\">\"<\/span>Marlene<span class=\"pl-pds\">\"<\/span><\/span>\r\n}<\/pre>\n<\/div>\n<\/li>\n<li>\n<p dir=\"auto\">You can add Database Policies which will filter or restrict results using Static Web Apps CLI. You can see this code in action in the VS Live Keynote demo repository <a href=\"https:\/\/aka.ms\/VSLive23Demo\">here<\/a>.<\/p>\n<div class=\"highlight highlight-source-shell notranslate position-relative overflow-auto\" dir=\"auto\">\n<pre>    <span class=\"pl-s\"><span class=\"pl-pds\">\"<\/span>actions<span class=\"pl-pds\">\"<\/span><\/span>: [\r\n\t{\r\n\t    <span class=\"pl-s\"><span class=\"pl-pds\">\"<\/span>action<span class=\"pl-pds\">\"<\/span><\/span>: <span class=\"pl-s\"><span class=\"pl-pds\">\"<\/span>read<span class=\"pl-pds\">\"<\/span><\/span>,\r\n\t    <span class=\"pl-s\"><span class=\"pl-pds\">\"<\/span>policy<span class=\"pl-pds\">\"<\/span><\/span>: {\r\n\t\t<span class=\"pl-s\"><span class=\"pl-pds\">\"<\/span>database<span class=\"pl-pds\">\"<\/span><\/span>: <span class=\"pl-s\"><span class=\"pl-pds\">\"<\/span>@customers.customer_sales_rep eq 'Riley'<span class=\"pl-pds\">\"<\/span><\/span>\r\n\t    }\r\n\t}\r\n    ]\r\n\r\n\r\n<\/pre>\n<\/div>\n<p>Data API builder is an amazing tool and very simple to use. You can check out the documents <a href=\"https:\/\/github.com\/Azure\/data-api-builder\/tree\/main\/docs\">here<\/a> and see more samples <a href=\"https:\/\/github.com\/Azure\/data-api-builder\/tree\/main\/samples\">here<\/a>.<\/li>\n<\/ol>\n","protected":false},"excerpt":{"rendered":"<p>I was fortunate enough to be able to present Data API builder the week of March 20th at VS Live in Las Vegas and what an incredible group of attendees at that conference. During my session, I stepped through a live demo of Data API builder showing off REST and GraphQL endpoints on various database [&hellip;]<\/p>\n","protected":false},"author":95874,"featured_media":81,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[444,532,555,1,484,546,533],"tags":[244,534,510,541,560,531,559,558],"class_list":["post-1802","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-net","category-azure-data-studio","category-azure-functions","category-azure-sql","category-containers","category-github","category-visual-studio-code","tag-net","tag-azure-data-studio","tag-azure-sql-database","tag-azure-functions","tag-data-api-builder","tag-docker","tag-sqlcmd","tag-vs-code"],"acf":[],"blog_post_summary":"<p>I was fortunate enough to be able to present Data API builder the week of March 20th at VS Live in Las Vegas and what an incredible group of attendees at that conference. During my session, I stepped through a live demo of Data API builder showing off REST and GraphQL endpoints on various database [&hellip;]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts\/1802","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\/95874"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/comments?post=1802"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts\/1802\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/media\/81"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/media?parent=1802"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/categories?post=1802"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/tags?post=1802"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}