How to upload images via Gatsby Azure Static Web App into Azure Database

Question

I have the requirement to upload images via gatsby Azure static web app.

Currently I am using the following "tools":

1. Azure static web app
2. Azure SQL database

My high-level idea is:

1. Create input type="file" in Gatsby page/form
2. Call REST API to upload the image (and also the other fields of the form like text, numbers, ...) into database
3. Store image (and other fields) into Azure sql database column

Is it possible to implement it? If yes, how? Can I upload images with REST-API call via Gatsby form?

Question in general words: What is the best approach to upload files into database with Azure "tools" via Gatsby static web app form?

Please consider that I want to use only Azure "tools" which are free of charge.

Answer 1

The sample code below is for JSON files in Azure SQL, and it integrates file upload functionality into a web application using Node.js, REST API, and Gatsby.

• To store a file in Azure Blob Storage, you must first upload it. Afterward, you can use BULK INSERT or OPENROWSET to access the file.
• I followed "Work with JSON files with Azure SQL" by Davide Mauri and SO

Create a Database Scoped Credential:

CREATE DATABASE SCOPED CREDENTIAL AzureBlobCredential   
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'your_shared_access_signature';

Create an External Data Source:

CREATE EXTERNAL DATA SOURCE YourDataSource
WITH (
    TYPE = BLOB_STORAGE,
    LOCATION = 'https://yourblobstorage.blob.core.windows.net/container',
    CREDENTIAL = AzureBlobCredential
);

Create a Table for Storing JSON Data:

CREATE TABLE JSONDataTable (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    JsonData NVARCHAR(MAX)
);

Upload JSON Data into the Table:

INSERT INTO JSONDataTable (JsonData)
SELECT *
FROM OPENROWSET(
    BULK 'yourjson.json',
    DATA_SOURCE = 'YourDataSource',
    FORMAT = 'JSON'
) AS jsondata;

The code below sets up a Node.js Express server with a REST API to handle file uploads to Azure SQL.

const express = require('express');
const multer = require('multer');
const sql = require('mssql');
const cors = require('cors');

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

// Multer configuration for handling file uploads
const upload = multer({ dest: 'uploads/' });

// Azure SQL database 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
  }
};

// CORS middleware
app.use(cors());

// Endpoint for uploading JSON file and storing it in Azure SQL
app.post('/upload', upload.single('jsonFile'), async (req, res) => {
  try {
    // Connect to Azure SQL
    await sql.connect(config);

    // Read the uploaded JSON file
    const jsonData = require(req.file.path);

    // Insert JSON data into Azure SQL table
    const request = new sql.Request();
    await request.query(`INSERT INTO JSONDataTable (JsonData) VALUES ('${JSON.stringify(jsonData)}')`);

    // Respond with success message
    res.status(200).send('JSON data uploaded successfully!');
  } catch (error) {
    console.error('Error:', error.message);
    res.status(500).send('Internal Server Error');
  } finally {
    sql.close();
  }
});

// Endpoint for querying JSON data from Azure SQL
app.get('/data', async (req, res) => {
  try {
    // Connect to Azure SQL
    await sql.connect(config);

    // Query JSON data from Azure SQL
    const request = new sql.Request();
    const result = await request.query('SELECT * FROM JSONDataTable');

    // Respond with JSON data
    res.status(200).json(result.recordset);
  } catch (error) {
    console.error('Error:', error.message);
    res.status(500).send('Internal Server Error');
  } finally {
    sql.close();
  }
});

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

I followed this link for building a contact form.

Form in a Gatsby:

// components/JSONUploadForm.js
import React, { useState } from "react";

const JSONUploadForm = () => {
  const [file, setFile] = useState(null);

  const handleFileChange = (event) => {
    setFile(event.target.files[0]);
  };

  const handleSubmit = async (event) => {
    event.preventDefault();
    
    if (!file) {
      alert("Please select a file to upload");
      return;
    }

    const formData = new FormData();
    formData.append("jsonFile", file);

    try {
      const response = await fetch("http://localhost:3000/upload", {
        method: "POST",
        body: formData,
      });

      if (response.ok) {
        alert("JSON file uploaded successfully");
      } else {
        throw new Error("Failed to upload JSON file");
      }
    } catch (error) {
      console.error("Error uploading JSON file:", error);
      alert("An error occurred while uploading the JSON file");
    }
  };

  return (
    <form onSubmit={handleSubmit}>
      <div>
        <input type="file" accept=".json" onChange={handleFileChange} />
      </div>
      <div>
        <button type="submit">Upload JSON File</button>
      </div>
    </form>
  );
};

export default JSONUploadForm;

// pages/YourPage.js
import React from "react";
import JSONUploadForm from "../components/JSONUploadForm";

const YourPage = () => {
  return (
    <div>
      <h1>Upload JSON File</h1>
      <JSONUploadForm />
    </div>
  );
};

export default YourPage;