data base queries in application is running on the local environment, but not on microsoft azure?

Question

i created a project which is using external mysql database , the application has node.js backend and react frontend i am using azure static web-app, and it is deployed on microsoft azure but according to my code it is giving query error

router.post("/adminlogin", (req, res) => {
    const sql = "SELECT * FROM admin WHERE email = ? AND password = ?";
    con.query(sql, [req.body.email, req.body.password], (err, result) => {
        if (err) {
            console.error("Query error:", err);
            return res.json({ loginStatus: false, Error: "Query error", Details: err.message });
        }
        if (result.length > 0) {
            const email = result[0].email;
            const token = jwt.sign(
                { role: "admin", email: email, id: result[0].id },
                "jwt_secret_key",
                { expiresIn: "1d" }
            );
            res.cookie('token', token);
            return res.json({ loginStatus: true });
        } else {
            return res.json({ loginStatus: false, Error: "Wrong email or password" });
        }
    });
});

i am using f1 shared free app services for my node js backend, why is it giving query error

the database queries in my node js backend should work on microsoft azure

Answer 1

Static Web app only support Azure Function as Integrated backend. For reference check this MS Document,

To use Express or any other backend code, its need to be deployed in Azure App Services OR Azure Container Apps and need to be linked to static web App. For Reference Check this MS Document. Link Azure App Service. Link Azure Container Apps. Link Azure API Management.

Below is my code to use Azure Function as backend. I am Using HTTP Trigger. Function code is inside Funcion folder. which is mention in API in yml file.

My Directory:

httpTrigger.js:

// const { app } = require('@azure/functions');
import { app } from "@azure/functions";
import mssql from "mssql";

app.http('data', {
    methods: ['GET', 'POST'],
    authLevel: 'anonymous',
    handler: async (request, context) => {

        context.log(`Http function processed request for url "${request.url}"`);
        try{
            const config = {  
                server: 'dbserver5spe.database.windows.net',  //update me
                authentication: {
                    type: 'default',
                    options: {
                        userName: 'vivek', //update me
                        password: 'xxxxxxxxxxxxxxx'  //update me
                    }
                },
                options: {
                    encrypt: true,
                    database: 'staticwebapp'  //update me
                }
            }; 
        
            await mssql.connect(config)
        
            const result = await mssql.query('SELECT SYSTEM_USER as username;');
            return {body: JSON.stringify(result.recordset)}
    }
    catch (error) {
        context.log.error('Database connection/query error:', error);
        return { body: 'Error connecting to the database', status: 500 };
    } finally {
        // Close the connection
        await mssql.close();
    }
}
})

App.jsx:

import { useEffect, useState } from 'react';
import './App.css';


function App() {
  const [data,setData] = useState([])
  useEffect(()=>{
  fetch('http://localhost:7071/api/data')
  .then(res => res.json())
  .then(data=>setData(data))
  },[])
  return (
    <div className="App">
      <h1>
        value: {data.map((item, index) => (
                  item.username
                ))}
      </h1>
    </div>
  );
}

export default App;

My Static web App github yml file

name: Azure Static Web Apps CI/CD

on:
  push:
    branches:
      - main
  pull_request:
    types: [opened, synchronize, reopened, closed]
    branches:
      - main

jobs:
  build_and_deploy_job:
    if: github.event_name == 'push' || (github.event_name == 'pull_request' && github.event.action != 'closed')
    runs-on: ubuntu-latest
    name: Build and Deploy Job
    steps:
      - uses: actions/checkout@v3
        with:
          submodules: true
          lfs: false
      - name: Build And Deploy
        id: builddeploy
        uses: Azure/static-web-apps-deploy@v1
        with:
          azure_static_web_apps_api_token: ${{ secrets.AZURE_STATIC_WEB_APPS_API_TOKEN_RED_PEBBLE_0C0BF510F }}
          repo_token: ${{ secrets.GITHUB_TOKEN }}
          action: "upload"
          app_location: "/" # App source code path
          api_location: "./Function" # Api source code path - optional
          output_location: "dist" # Built app content directory - optional
          
  close_pull_request_job:
    if: github.event_name == 'pull_request' && github.event.action == 'closed'
    runs-on: ubuntu-latest
    name: Close Pull Request Job
    steps:
      - name: Close Pull Request
        id: closepullrequest
        uses: Azure/static-web-apps-deploy@v1
        with:
          azure_static_web_apps_api_token: ${{ secrets.AZURE_STATIC_WEB_APPS_API_TOKEN_RED_PEBBLE_0C0BF510F }}
          action: "close"

OUTPUT:

React

Function backend