AI Image Generator with Azure OpenAI and SQL Server

Problem

Modern e-commerce and catalog applications demand high-quality product images. But creating and managing these images at scale is challenging, especially if you already have a large product catalog that needs to be illustrated. Is it possible to avoid manual creation by using AI image generator tools to generate high-quality images for records stored on a SQL Server table, based on name and description?

Solution

With new Generative AI capabilities, we can now automate image creation and integrate it into a cloud-native workflow.

This demonstration will use Azure Open AI Dall-E-3 to test a general prompt that will then be used via a Python script to process a list of products without images. We will use a simple Azure Static web app to present the image generation results. To have a modern, secure stack, we will implement the database calls via an API using Data Api Builder.

Our Architecture

• Azure SQL Database with a products table (or whatever is needed to illustrate)
• Data API Builder (DAB) – To expose SQL data as REST API. The Api will be created using Azure Static Web Apps capabilities.
• Azure OpenAI (DALL·E 3) – To generate product images.
• Azure Blob Storage – To persist generated images.
• Python Script – To orchestrate everything in a loop over the table.
• A sample React page in Azure Static Web Apps to present the results. (out of scope for this tip)

In the diagram above, we expose the data to the application and the image generation script via the API. This increases security as the Azure SQL database is never directly exposed. We store the images in a blob storage, and using the API, update the records to save the URL of the generated image. Then, the frontend calls the API and images from blob storage to display the images.

Setting Up the Example

First, we need to get our product list. For this example, I am using an Azure SQL database table called Products:

CREATE TABLE Products (
    Id INT IDENTITY(1,1) PRIMARY KEY,
 
    ProductName NVARCHAR(255) NOT NULL,
    Description NVARCHAR(1000) NOT NULL,
    CategoryID INT NOT NULL,  -- Foreign key to Categories table
 
    Tags NVARCHAR(MAX) NULL,
    Price DECIMAL(10, 2) NOT NULL,
    ImageUrl NVARCHAR(1000) NOT NULL,
 
    CreatedAt DATETIME2 DEFAULT SYSDATETIME(),
 
    CONSTRAINT FK_Products_Categories FOREIGN KEY (CategoryID)
        REFERENCES Categories(CategoryID)
);

For sample products, this example will use products with existing images and others without an image:

I have created stored procedures to get the product list and to update the URL that we will use later:

CREATE OR ALTER PROCEDURE GetProducts
    @ProductID INT = -1
AS
BEGIN
    SET NOCOUNT ON;
    IF @ProductID = -1
    BEGIN
        SELECT p.Id AS ProductID, ProductName, [Description], c.CategoryID, Tags, Price, ImageUrl, CreatedAt,
               c.CategoryName
        FROM dbo.Products p
        INNER JOIN dbo.Categories c ON p.CategoryID = c.CategoryID;
    END
    ELSE
    BEGIN
        SELECT p.Id AS ProductID, ProductName, [Description], c.CategoryID, Tags, Price, ImageUrl, CreatedAt,
               c.CategoryName
        FROM dbo.Products p
        INNER JOIN dbo.Categories c ON p.CategoryID = c.CategoryID
        WHERE p.Id = @ProductID;
    END
END;

The code below will update the product with the new URL:

CREATE OR ALTER PROCEDURE UpdateProductImageUrl
    @ProductID INT,
    @ImageUrl NVARCHAR(2000)
AS
BEGIN
    SET NOCOUNT ON;
 
    IF NOT EXISTS (SELECT 1 FROM dbo.Products WHERE Id = @ProductID)
    BEGIN
        RAISERROR('Product not found.', 16, 1);
        RETURN;
    END
 
    UPDATE dbo.Products
    SET ImageUrl = @ImageUrl
    WHERE Id = @ProductID;
 
    SELECT 'success' AS Status, @ProductID AS ProductID, @ImageUrl AS ImageUrl;
END;

Using Data API Builder, with the help of a static web app, I have created the API using this JSON file:

{
  "$schema": "https://github.com/Azure/data-api-builder/releases/download/v1.1.7/dab.draft.schema.json",
  "data-source": {
    "database-type": "mssql",
    "connection-string": "@env('DATABASE_CONNECTION_STRING')",
    "options": {
      "set-session-context": false
    }
  },
  "runtime": {
    "rest": {
      "enabled": true,
      "path": "/rest",
      "request-body-strict": true
    },
    "graphql": {
      "enabled": true,
      "path": "/graphql",
      "allow-introspection": true
    },
    "host": {
      "cors": {
        "origins": ["*"],
        "allow-credentials": false
      },
      "authentication": {
        "provider": "StaticWebApps"
      },
      "mode": "production"
    }
  },
  "entities": {
    "GetProducts": {
      "source": {
        "object": "[dbo].[GetProducts]",
        "type": "stored-procedure",
        "parameters": {
          "ProductID": { "type": "int", "nullable": false, "required": false }
        }
      },
      "graphql": {
        "enabled": true,
        "operation": "mutation",
        "type": {
          "singular": "GetProduct",
          "plural": "GetProducts"
        }
      },
      "rest": {
        "enabled": true,
        "methods": ["get"]
      },
      "permissions": [
        {
          "role": "anonymous",
          "actions": [ { "action": "execute" } ]
        },
        {
          "role": "authenticated",
          "actions": [ { "action": "execute" } ]
        }
      ]
    },
    "InsertProduct": {
      "source": {
        "object": "[dbo].[InsertProduct]",
        "type": "stored-procedure",
        "parameters": {
          "ProductName": "s",
          "Description": "s",
          "CategoryID": "i",
          "Tags": "s",
          "Price": "n",
          "ImageUrl": "s"
        }
      },
      "graphql": {
        "enabled": true,
        "operation": "mutation",
        "type": {
          "singular": "InsertProduct",
          "plural": "InsertProducts"
        }
      },
      "rest": {
        "enabled": true,
        "methods": ["post"]
      },
      "permissions": [
        {
          "role": "anonymous",
          "actions": [ { "action": "execute" } ]
        }
      ]
    },
    "UpdateProductImageUrl": {
      "source": {
        "object": "[dbo].[UpdateProductImageUrl]",
        "type": "stored-procedure",
        "parameters": {
          "ProductID": "i",
          "ImageUrl": "s"
        }
      },
      "graphql": {
        "enabled": true,
        "operation": "mutation",
        "type": {
          "singular": "UpdateProductImageUrl",
          "plural": "UpdateProductImageUrls"
        }
      },
      "rest": {
        "enabled": true,
        "methods": ["post"]
      },
      "permissions": [
        {
          "role": "anonymous",
          "actions": [ { "action": "execute" } ]
        }
      ]
    },
    "GetCategories": {
      "source": {
        "object": "[dbo].[GetCategories]",
        "type": "stored-procedure"
      },
      "graphql": {
        "enabled": true,
        "operation": "query",
        "type": {
          "singular": "GetCategory",
          "plural": "GetCategories"
        }
      },
      "rest": {
        "enabled": true,
        "methods": ["get"]
      },
      "permissions": [
        {
          "role": "anonymous",
          "actions": [ { "action": "execute" } ]
        },
        {
          "role": "authenticated",
          "actions": [ { "action": "execute" } ]
        }
      ]
    }
  }
}

We will use postman to review the results from GET with this parameter: data-api/rest/GetProducts?ProductID=-1

Then you will need the following:

1. An Azure Blob Storage Account to Store the Images

For this example, I have enabled public read access as I am only storing the images there:

For write access, I have enabled a SAS token. If you plan to use the script on a schedule or automated process, consider using managed identities instead:

2. Use Azure Gen AI Capabilities to Generate Images

You need an Azure Ai foundry service that can be accessed via https://ai.azure.com. We will learn how to deploy and use a model later in this tip.

3. (Optional) Use the Same Static Web App Where the Data API is Deployed

You can have a simple page to display the products and their images. At this point, some of the products do not have images; we will generate them later.

Since frontend development is out of scope for this tip, we will not explain how to do it, but you can ask Copilot to create a simple page in React.

Step 1: Using Azure AI Foundry Portal to Generate an Image

First, we need to generate a proper prompt to generate an image. So, this step involves a lot of trial-and-error. To do it more easily, you can use AI Foundry portal to play with the prompts.

Let’s deploy our image generation model. Go to Azure AI Foundry portal at https://ai.azure.com. Then, go to deployments on the left side:

If you do not have a Dall-E-3 model already, click Deploy Model and select Deploy Base Model:

Search for Dall-E-3 and click Confirm

You can use the default options or click Customize to select the closest region to your other resources.

Once the resource is deployed, we can play with it on the playground. On the left side under Playgrounds, select Images, then select the deployment just created:

We are ready to experiment with a proper prompt we want to apply to all the images. For example, we can take 1 or 2 product info: ‘Kids Dinosaur Pajamas’, ‘Set of kids pajamas with colorful dinosaur prints, neatly folded.’

You must experiment to meet your needs. However, for this example, I use something like this:

Create a clean product photo for an online store.
 
Product Name: {product_name}
Description: {description}
Category: {category_name}
Tags: {tags}
 
Show the product fully visible, perfectly centered in the frame, with no cropping. Use a straight-on perspective, on a plain white background.
Do not include any text, labels, props, packaging, or background scene.
 
Ensure the product appears balanced, isolated, and positioned centrally as if professionally shot for a catalog.
Image must be centered

Then, we replace it with our test product and click Generate. For style, I use General:

You can adjust as needed, but for this prompt, we obtain something like this:

Step 2: Generate the Images Programmatically for All Products

Once you are satisfied with the results, we need to apply the prompt to all products via code, but do not worry, Azure Foundry has you covered.

You will obtain a code snippet to use as a base by clicking View code:

You can get the code to generate this same image. We will execute the loop on all images in our Data API products table, and use Copilot to fill the missing parts.

For this example, since we are running it one time, use the Key authentication option:

The API Key and Endpoint are available at the bottom of this page.

This part can be completed manually, or ask ChatGPT or Copilot to complete the code. In my case, I used Copilot in VSCode. I pasted the previous code in a new Python file and asked Copilot to include the changes for the loop:

• Loop through our products table and generate an image for missing URL products.
• Save the generated image on blob storage.
• Update each product with the generated URL.

Below is the prompt I used in Copilot to update the Foundry code snippet:

hi, can you please update this code to loop through all products on this API https://<URL>/data-api/rest/GetProducts?ProductID=-1?
 
sample call:
 
{"value":[{"ProductID":4,"ProductName":"Chocolate Bar","Description":"Rich milk chocolate bar, perfect for snacking.","CategoryID":4,"Tags":"snack,sweet","Price":2.49,"ImageUrl":"https://<URL>.blob.core.windows.net/products/chocolate_bar.jpg","CreatedAt":"2025-07-25T18:23:08.6059582","CategoryName":"Candy"}]}
 
if the product has image URL, skip it.
 
If no, and the image is generated successfully, store the image on this BLOB storage account, sample code, SAS token will be provided on a variable at the start of the script
 
safe_name = product_name.lower().replace(' ', '_')
filename = f"{safe_name}.jpg"
blob_url = f"https://<URL>.blob.core.windows.net/products/{filename}"
try:
# Use SAS token from environment variable
sas_token = os.getenv("BLOB_SAS_TOKEN")
if not sas_token:
return func.HttpResponse(
json.dumps({"error": "BLOB_SAS_TOKEN environment variable is not set."}),
mimetype="application/json",
status_code=500
)
# Append SAS token to blob URL
blob_url_with_sas = f"{blob_url}?{sas_token.lstrip('?')}"
blob_client = BlobClient.from_blob_url(blob_url_with_sas)
blob_client.upload_blob(image_bytes, overwrite=True)
except Exception as blob_err:
return func.HttpResponse(
json.dumps({"error": "Failed to upload image to blob storage", "details": str(blob_err)}),
mimetype="application/json",
status_code=500
 
finally, update the product URL using this api: https://<URL>/data-api/rest/UpdateProductImageUrl
 
sample body: {
"ProductID": 9,
"ImageUrl": "https://<URL>.blob.core.windows.net/products/wireless_gamer_mouse.jpg"
}

Below is the code generated by Copilot:

# Batch image generation for products missing images
import os
import json
import requests
from openai import AzureOpenAI
from azure.storage.blob import BlobClient
 
# Environment variables (redacted)
endpoint = os.getenv("AZURE_OPENAI_ENDPOINT", "<your-openai-endpoint>")
api_version = os.getenv("OPENAI_API_VERSION", "2024-04-01-preview")
deployment = os.getenv("DEPLOYMENT_NAME", "<your-deployment>")
api_key = os.getenv("AZURE_OPENAI_API_KEY")  
sas_token = os.getenv("BLOB_SAS_TOKEN")
 
# Ensure OpenAI SDK finds the API key
os.environ["AZURE_OPENAI_API_KEY"] = api_key
 
client = AzureOpenAI(
    api_version=api_version,
    azure_endpoint=endpoint,
    api_key=api_key,
)
 
products_api = "<your-api-base>/data-api/rest/GetProducts?ProductID=-1"
update_api = "<your-api-base>/data-api/rest/UpdateProductImageUrl"
 
response = requests.get(products_api)
if response.status_code != 200:
    print(f"Failed to fetch products: {response.text}")
    exit(1)
 
products = response.json().get("value", [])
results = []
 
for product in products:
    product_id = product.get("ProductID")
    product_name = product.get("ProductName", "")
    description = product.get("Description", "")
    category_name = product.get("CategoryName", "")
    tags = product.get("Tags", "")
    image_url = product.get("ImageUrl", "")
 
    print(f"Processing ProductID {product_id}: {product_name}")
 
    if image_url:
        print(f"  Skipped: Image already exists.")
        results.append({"ProductID": product_id, "status": "skipped", "reason": "Image already exists."})
        continue
 
    prompt = f"""Create a clean product photo for an online store.\n\nProduct Name: {product_name}\nDescription: {description}\nCategory: {category_name}\nTags: {tags}\n\nShow the product fully visible, perfectly centered in the frame, with no cropping. Use a straight-on perspective, on a plain white background.\nDo not include any text, labels, props, packaging, or background scene.\n\nEnsure the product appears balanced, isolated, and positioned centrally as if professionally shot for a catalog."""
 
    # Generate image
    try:
        print("  Generating image with OpenAI...")
        result = client.images.generate(
            model=deployment,
            prompt=prompt,
            n=1,
            style="natural",
            quality="standard",
        )
        gen_image_url = json.loads(result.model_dump_json())["data"][0]["url"]
        print("  Image generated.")
    except Exception as openai_err:
        print(f"  Error: Failed to generate image with OpenAI: {openai_err}")
        results.append({"ProductID": product_id, "status": "error", "reason": "OpenAI error", "details": str(openai_err)})
        continue
 
    # Download image
    try:
        print("  Downloading image...")
        image_response = requests.get(gen_image_url)
        if image_response.status_code != 200:
            print(f"  Error: Failed to download image: {image_response.text}")
            results.append({"ProductID": product_id, "status": "error", "reason": "Image download failed", "details": image_response.text})
            continue
        image_bytes = image_response.content
        print("  Image downloaded.")
    except Exception as download_err:
        print(f"  Error: Exception while downloading image: {download_err}")
        results.append({"ProductID": product_id, "status": "error", "reason": "Image download exception", "details": str(download_err)})
        continue
 
    # Upload to blob
    safe_name = product_name.lower().replace(' ', '_')
    filename = f"{safe_name}.jpg"
    blob_path = f"/products/{filename}"
    blob_url = f"<your-blob-base>{blob_path}"
    try:
        print("  Uploading image to blob storage...")
        if not sas_token:
            print("  Error: Missing SAS token.")
            results.append({"ProductID": product_id, "status": "error", "reason": "Missing SAS token"})
            continue
        blob_url_with_sas = f"{blob_url}?{sas_token.lstrip('?')}"
        blob_client = BlobClient.from_blob_url(blob_url_with_sas)
        blob_client.upload_blob(image_bytes, overwrite=True)
        print("  Image uploaded to blob storage.")
    except Exception as blob_err:
        print(f"  Error: Failed to upload image to blob storage: {blob_err}")
        results.append({"ProductID": product_id, "status": "error", "reason": "Blob upload failed", "details": str(blob_err)})
        continue
 
    # Update product image URL
    update_body = {
        "ProductID": product_id,
        "ImageUrl": blob_path
    }
    try:
        print("  Updating product image URL via API...")
        update_response = requests.post(update_api, json=update_body)
        if not str(update_response.status_code).startswith("2"):
            print(f"  Error: Failed to update product image URL: {update_response.text}")
            results.append({"ProductID": product_id, "status": "error", "reason": "Update API failed", "details": update_response.text})
            continue
        print("  Product image URL updated.")
    except Exception as update_err:
        print(f"  Error: Exception while updating product image URL: {update_err}")
        results.append({"ProductID": product_id, "status": "error", "reason": "Update API exception", "details": str(update_err)})
        continue
 
    print(f"  Success: ProductID {product_id} processed.")
    results.append({"ProductID": product_id, "status": "success", "ImageUrl": blob_url})
 
print(json.dumps({"results": results}, indent=2))

You will notice that some products may have a content policy violation. You can update the prompt for these cases, or just skip them, for example, for my case, this one:

“ProductID”:28,”ProductName”:”Blue Denim Jeans”,”Description”:”Pair of blue denim jeans, straight fit, laid flat with visible stitching.”

Checking the Azure blob storage, you can see that all other product images were generated successfully.

And if we check the SQL Server table:

Final Results

We can use our sample app to display all the generated images:

And some more:

And some more. You can see the script failed on only one image (the one we described earlier):

If we zoom in on any of the product images, we can see the quality is acceptable for our example:

Final Notes

• You may need to dynamically tune prompts or retry with modified ones in case of generation failure or flagged content. Batch generating images multiple times can be costly, so be cautious about this.
• Remember that Azure OpenAI usage is not free. Determine if this is the best cost-effective solution for you.

Azure Open AI Dall-E-3 costs (at the writing of this article):

For example, just one batch run for 500 products with standard resolution will cost around $20.

• For production usage, consider integrating structured logging and retries for image generation, blob upload, and product update steps. Be especially careful if your product images must contain logos or text, since AI does not generate text very accurately.
• Remember to validate first that you have not duplicated, and/or have vague or empty descriptions, as the results can be undesirable. Probably some special characters can break the prompt structure, so always do a cleanup first before generating them.

Next Steps

• The key point here is the prompt used to generate images; you can achieve different results with different prompts, or you probably want a more modern or informal style. You can achieve it.
• The quality of the product images generated also depends on the quality of the descriptions; vague descriptions will not generate great results.
• Check out this tip on Data API Builder.
• Check out this thread for prompt tips to get better results.
• Make sure to try Copilot in the new SSMS 21.
• How to use Copilot in VSCode.