Converting Data-Api-Builder Config to Static Web App Config

2023, May 11

If you are working with the Data API Builder in isolation, but then you decide to combine and publish the Db configuration and the client side (JavaScript files) together on a Static Web App, how do you do it?

The DAB Config file

Let's consider the SQL database script for this exercise.

I have shrunk it for this example though:

drop table if exists dbo.books;
drop sequence if exists dbo.globalId;
go

create sequence dbo.globalId
as int
start with 1000000
increment by 1
go

create table dbo.books
(
    id int not null primary key default (next value for dbo.globalId),
    title nvarchar(1000) not null,
    [year] int null,
    [pages] int null
)
go

insert into dbo.books values
    (1000, 'Prelude to Foundation', 1988, 403),
    (1001, 'Forward the Foundation', 1993, 417),
    (1002, 'Foundation', 1951, 255),
    (1003, 'Foundation and Empire', 1952, 247),
    (1004, 'Second Foundation', 1953, 210),
    (1005, 'Foundation''s Edge', 1982, 367),
    (1006, 'Foundation and Earth', 1986, 356),
    (1007, 'Nemesis', 1989, 386),
    (1008, 'Starship Troopers', null, null),
    (1009, 'Stranger in a Strange Land', null, null),
    (1010, 'Nightfall', null, null),
    (1011, 'Nightwings', null, null),
    (1012, 'Across a Billion Years', null, null),
    (1013, 'Hyperion', 1989, 482),
    (1014, 'The Fall of Hyperion', 1990, 517),
    (1015, 'Endymion', 1996, 441),
    (1016, 'The Rise of Endymion', 1997, 579),
    (1017, 'Practical Azure SQL Database for Modern Developers', 2020, 326),
    (1018, 'SQL Server 2019 Revealed: Including Big Data Clusters and Machine Learning', 2019, 444),
    (1019, 'Azure SQL Revealed: A Guide to the Cloud for SQL Server Professionals', 2020, 528),
    (1020, 'SQL Server 2022 Revealed: A Hybrid Data Platform Powered by Security, Performance, and Availability', 2022, 506)
go

Assuming you have already DAB installed, run the following commands on PowerShell:

$env:DATABASE_CONNECTION_STRING='Server=<sqlserver.database.windows.net>;Database=<mydatabase>;User ID=<user>;Password=<password>;TrustServerCertificate=true'

dab init --database-type "mssql" --connection-string "@env('DATABASE_CONNECTION_STRING')" --host-mode "Development"

dab add Book --source dbo.books --permissions "anonymous:*"

The file dab-config.json will be generated, looking like this:

{
  "$schema": "https://github.com/Azure/data-api-builder/releases/download/v0.6.14/dab.draft.schema.json",
  "data-source": {
    "database-type": "mssql",
    "options": {
      "set-session-context": false
    },
    "connection-string": "@env('DATABASE_CONNECTION_STRING')"
  },
  "runtime": {
    "rest": {
      "enabled": true,
      "path": "/api"
    },
    "graphql": {
      "allow-introspection": true,
      "enabled": true,
      "path": "/graphql"
    },
    "host": {
      "mode": "development",
      "cors": {
        "origins": [],
        "allow-credentials": false
      },
      "authentication": {
        "provider": "StaticWebApps"
      }
    }
  },
  "entities": {
    "Book": {
      "source": "dbo.books",
      "permissions": [
        {
          "role": "anonymous",
          "actions": [
            "*"
          ]
        }
      ]
    }
  }
}

Html and Javascript

If you are running the HTML page on a web server and the API via DAB separately, you will end up with something like this:

File: index.html

<!doctype html>

<html lang="en">

<head>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">

  <title>Fullstack Jamstack Test</title>
  <meta name="description" content="A simple HTML5 Jamstack/Fullstack test">
  <meta name="author" content="Davide">

  <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.2.2/dist/css/bootstrap.min.css" rel="stylesheet" integrity="..." crossorigin="anonymous">
</head>

<body>
  <div class="container">   
    <h1>Books list:</h1>    
        <div id="#books">
            <ul class="list-group">                
            </ul>
        </div>
    </div>    
  <script src="https://code.jquery.com/jquery-3.6.4.min.js" crossorigin="anonymous"></script>
    <script>
        async function loadBooks() {
            var url = "https://localhost:5001/api/Book?$first=5"
            var response = await fetch(url) 
            var payload = await response.json();   
            return payload.value;
        }

        $(function() {
            loadBooks().then(result => {                 
                var ul = $("ul");
                result.forEach(book => {
                    var li = $(`<li class='list-group-item'>${book.title}</li>`);
                    ul.append(li);
                });
            } );
        });
        
  </script>
</body>

</html>

NOTE: If the html page is running on port 8000, the CORS origins on dab-config.json need updating to allow for queries to happen: "origins": ["http://localhost:8000"]. This page sample was originally created by Davide, however as I have tweaked it, it is available as dab-simple-before on PlayGoKids repo.

The SWA Database Config file

The SWA Database configuration file is also a DAB configuration file! Let's have a look, on the same directory run the command:

swa db init --database-type mssql --connection-string "@env('DATABASE_CONNECTION_STRING')"

This will initialize a new SWA config on .\swa-db-connections\staticwebapp.database.config.json. Looking like this:

{
  "$schema": "https://github.com/Azure/data-api-builder/releases/download/v0.6.14/dab.draft.schema.json",
  "data-source": {
    "database-type": "mssql",
    "options": {
      "set-session-context": false
    },
    "connection-string": "@env('DATABASE_CONNECTION_STRING')"
  },
  "runtime": {
    "rest": {
      "enabled": true,
      "path": "/rest"
    },
    "graphql": {
      "allow-introspection": true,
      "enabled": true,
      "path": "/graphql"
    },
    "host": {
      "mode": "production",
      "cors": {
        "origins": [],
        "allow-credentials": false
      },
      "authentication": {
        "provider": "StaticWebApps"
      }
    }
  },
  "entities": {}
}

It has the same structure as a dab configuration file because it is a dab configuration file! With a minor change:

API path

From (when compared to dab-config.json):

"rest": {
  "enabled": true,
  "path": "/api"
},

To:

"rest": {
  "enabled": true,
  "path": "/rest"
},

NOTE: Ignore the Host mode, as this can be either set to development or production.

Make sure to copy the entities from dab-config.json, and then things should work:

{
  "$schema": "https://github.com/Azure/data-api-builder/releases/download/v0.6.14/dab.draft.schema.json",
  "data-source": {
    "database-type": "mssql",
    "options": {
      "set-session-context": false
    },
    "connection-string": "@env('DATABASE_CONNECTION_STRING')"
  },
  "runtime": {
    "rest": {
      "enabled": true,
      "path": "/rest"
    },
    "graphql": {
      "allow-introspection": true,
      "enabled": true,
      "path": "/graphql"
    },
    "host": {
      "mode": "production",
      "cors": {
        "origins": [],
        "allow-credentials": false
      },
      "authentication": {
        "provider": "StaticWebApps"
      }
    }
  },
  "entities": {
    "Book": {
      "source": "dbo.books",
      "permissions": [
        {
          "role": "anonymous",
          "actions": [
            "*"
          ]
        }
      ]
    }
  }
}

Html and Javascript

Here we can run the Html page and DB configuration together. Before it can happen we need to update the /rest endpoint on Html.

File: index.html

<!doctype html>

<html lang="en">

<head>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">

  <title>Fullstack Jamstack Test</title>
  <meta name="description" content="A simple HTML5 Jamstack/Fullstack test">
  <meta name="author" content="Davide">

  <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.2.2/dist/css/bootstrap.min.css" rel="stylesheet" integrity="..." crossorigin="anonymous">
</head>

<body>
  <div class="container">   
    <h1>Books list:</h1>    
        <div id="#books">
            <ul class="list-group">                
            </ul>
        </div>
    </div>    
  <script src="https://code.jquery.com/jquery-3.6.4.min.js" crossorigin="anonymous"></script>
    <script>
        async function loadBooks() {
            var url = "/data-api/rest/Book?$first=5"
            var response = await fetch(url) 
            var payload = await response.json();   
            return payload.value;
        }

        $(function() {
            loadBooks().then(result => {                 
                var ul = $("ul");
                result.forEach(book => {
                    var li = $(`<li class='list-group-item'>${book.title}</li>`);
                    ul.append(li);
                });
            } );
        });
        
  </script>
</body>

</html>

NOTE: In this case, the URL mapping was updated. This page sample was originally created by Davide. This is available as dab-simple-after on PlayGoKids repo.

Then we can run the Html page and DB configuration in a single command via SWA CLI:

swa start --data-api-location .\swa-db-connections\

Both the server and client sides will be running simultaneously in different ports: https://localhost:5001 and http://localhost:4280.

If you were helped, please star this repo, or...Buy Me A Coffee
# Azure# Beginner