How to handle REST API calls in Azure Static web apps

Question

I want to create a Gatsby Azure static web app.

What is the best practice for implementing CRUD operations via REST API connection to an Azure SQL database in different Azure Static web app forms?

Please consider the two different types of tables:

1. 1:1/1:N relationship tables -> for example: Books-Table
2. N:M relationship tables -> for example: Book-Category-Table

Here are my ideas:

1:1/1:N relationship-table

C(reate) book form: 
   POST (for inserting new book)
R(ead) book(s) form:
   GET (for selecting one or all books)
U(pdate) book form:
   PUT (for updating the selected book)
D(elete) book form:
   DELETE (for deleting the selected book)

N:M relationship table

C(reate) book-category form: 
   POST (for inserting new book-category combinations)
R(ead) book-category form:
   GET (for selecting one or all book-category combinations)    
U(pdate) book-category form:
   DELETE (for deleting all book-category combinations)
   POST (for inserting the updated/new book-category combinations)
D(elete) book-category form:
   DELETE (for deleting the relevant book-category combination)

Is there any other function to handle especially updates of N:M relationship table?

Can I send any request which checks which book-category combinations already exists and handles them properly?

I am totally open for different ideas or approaches.

Addition:

My main question is: How can I implement the update of a N:M relationsship. I have a table book (with book_id as primary key) and a table book-category (with surrogate key "book AND category")

For example the book-category-table looks like that: Book 1 - Category 10 Book 1 - Category 11

Now I want to update the relevant categories via form and REST API call. Book 1 - Category 10 Book 1 - Category 20

How can I do that? Do I have to delete all entries for "book = 1" and then insert the new values? I can not update the book, I have to update the book-category table.

Answer 1

The below sample code is to handle REST API calls in Azure Static web apps

The gatsby-source-rest-api plugin enables you to fetch data from various REST APIs and use it within your Gatsby application.

The below sample is to fetching data from REST APIs and displaying it on your Gatsby site with data sourced from REST APIs using the gatsby-source-rest-api plugin.

• Install the gatsby-source-rest-api package npm install --save gatsby-source-rest-api

gatsby-config.js:

module.exports = {
  siteMetadata: {
    title: "My Gatsby Site",
  },
  plugins: [
    {
      resolve: 'gatsby-source-rest-api',
      options: {
        endpoints: [
          // Specify the endpoints from which you want to fetch data
          'https://jsonplaceholder.typicode.com/posts',
          'https://jsonplaceholder.typicode.com/users',
          // Add more endpoints if needed
        ],
      },
    },
  ],
}

posts.js:

// src/pages/posts.js

import React from "react"
import { graphql } from "gatsby"

const PostsPage = ({ data }) => {
  const posts = data.allRestApiPosts.edges

  return (
    <div>
      <h1>Posts</h1>
      <ul>
        {posts.map(({ node }) => (
          <li key={node.id}>
            <h2>{node.title}</h2>
            <p>{node.body}</p>
          </li>
        ))}
      </ul>
    </div>
  )
}

export const query = graphql`
  query {
    allRestApiPosts {
      edges {
        node {
          id
          userId
          title
          body
        }
      }
    }
  }
`

export default PostsPage

seo.js:

//src\components\seo.js
import * as React from "react"
import { useStaticQuery, graphql } from "gatsby"

function Seo({ description, title, children }) {
  const { site } = useStaticQuery(
    graphql`
      query {
        site {
          siteMetadata {
            title
            description
          }
        }
      }
    `
  )

  const metaDescription = description || site.siteMetadata.description
  const defaultTitle = site.siteMetadata?.title

  return (
    <>
      <title>{defaultTitle ? `${title} | ${defaultTitle}` : title}</title>
      <meta name="description" content={metaDescription} />
      <meta property="og:title" content={title} />
      <meta property="og:description" content={metaDescription} />
      <meta property="og:type" content="website" />
      <meta name="twitter:card" content="summary" />
      <meta name="twitter:title" content={title} />
      <meta name="twitter:description" content={metaDescription} />
      {children}
    </>
  )
}

export default Seo

Azure Static Web App Output:

• Call all your CRUD operations URL via a REST API connection to an Azure SQL database in gatsby-config.js and change the logic according to the requirements in Gatsby and deploy to the Azure static web app. Not sure about the functionality to handle updates of N:M relationship table.

• Yes, the functionality of checking book-category combinations is possible.

Implementing an N:M relationship:

-- Books table
CREATE TABLE Books (
    BookID INT PRIMARY KEY,
    Title VARCHAR(100),
    Author VARCHAR(100),
    PublishedYear INT
);

-- Categories table
CREATE TABLE Categories (
    CategoryID INT PRIMARY KEY,
    Name VARCHAR(100)
);

-- BookCategory table
CREATE TABLE BookCategory (
    BookID INT,
    CategoryID INT,
    PRIMARY KEY (BookID, CategoryID),
    FOREIGN KEY (BookID) REFERENCES Books(BookID),
    FOREIGN KEY (CategoryID) REFERENCES Categories(CategoryID)
);

SELECT * FROM BookCategory WHERE BookID = 1;\

DELETE FROM BookCategory WHERE BookID = 1 AND CategoryID = 10;

INSERT INTO BookCategory (BookID, CategoryID) VALUES (1, 30);

Updated:

Creating a REST API with Node.js and Express.

• The code below is an Express.js server that interacts with a SQL Server database. It provides endpoints to retrieve, add, and delete book categories.

GET Endpoint:

• /api/bookcategories: This endpoint retrieves book categories based on the provided bookid query parameter.

POST Endpoint:

• /api/bookcategories: This endpoint adds a new book category. It expects the BookID and CategoryID in the request body.

DELETE Endpoint:

• /api/bookcategories: This endpoint deletes a book category based on the provided bookid and categoryid query parameters.

• Use this documentation to create a Node.js web app on Azure App Service.

const express = require('express');
const bodyParser = require('body-parser');
const sql = require('mssql');
const cors = require('cors');

const app = express();
const port = process.env.PORT || 3000;

// SQL Server configuration
const config = {
  server: 'your_server.database.windows.net',
  database: 'your_database',
  user: 'your_username',
  password: 'your_password',
  options: {
    encrypt: true,
    trustServerCertificate: false,
    connectionTimeout: 30000,
    enableArithAbort: true
  }
};

// Middleware
app.use(cors());
app.use(bodyParser.json());

// GET endpoint to retrieve book categories by BookID
app.get('/api/bookcategories', async (req, res) => {
  try {
    const pool = await sql.connect(config);
    const result = await pool.request()
      .input('BookID', sql.Int, req.query.bookid)
      .query('SELECT * FROM BookCategory WHERE BookID = @BookID');
    
    res.json(result.recordset);
  } catch (err) {
    console.error('Error executing SQL query:', err);
    res.status(500).send('Internal Server Error');
  }
});

// POST endpoint to add a new book category
app.post('/api/bookcategories', async (req, res) => {
  try {
    const pool = await sql.connect(config);
    const result = await pool.request()
      .input('BookID', sql.Int, req.body.BookID)
      .input('CategoryID', sql.Int, req.body.CategoryID)
      .query('INSERT INTO BookCategory (BookID, CategoryID) VALUES (@BookID, @CategoryID)');
    
    res.status(201).send('Book category added successfully');
  } catch (err) {
    console.error('Error executing SQL query:', err);
    res.status(500).send('Internal Server Error');
  }
});

// DELETE endpoint to delete a book category by BookID and CategoryID
app.delete('/api/bookcategories', async (req, res) => {
  try {
    const pool = await sql.connect(config);
    const result = await pool.request()
      .input('BookID', sql.Int, req.query.bookid)
      .input('CategoryID', sql.Int, req.query.categoryid)
      .query('DELETE FROM BookCategory WHERE BookID = @BookID AND CategoryID = @CategoryID');
    
    res.status(200).send('Book category deleted successfully');
  } catch (err) {
    console.error('Error executing SQL query:', err);
    res.status(500).send('Internal Server Error');
  }
});

// Start server
app.listen(port, () => {
  console.log(`Server listening on port ${port}`);
});

I followed this doc to deploy the sample code as an app on Azure.