Azure MySQL Integration with Azure Functions

Introduction

This page demonstrates how Azure Functions can seamlessly integrate with Azure Database for MySQL, enabling serverless data operations. This approach is ideal for event-driven scenarios, API backends, and background tasks that require interaction with your MySQL database.

Leveraging Azure Functions with a managed MySQL service offers:

Scalability: Functions scale automatically based on demand.
Cost-Effectiveness: Pay only for the compute time consumed.
Simplified Management: Focus on code, not infrastructure.
Rich Ecosystem: Connect to other Azure services easily.

Core Concepts

The integration typically involves:

Azure Functions: Your serverless compute, written in languages like Node.js, C#, Python, or Java.
Azure Database for MySQL: A fully managed relational database service.
Connection String: Securely configured in Function App settings to allow your functions to connect to the database.
Database Drivers/SDKs: Libraries within your function code to interact with MySQL (e.g., mysql2 for Node.js, PyMySQL for Python).

Functions can perform operations such as reading data, writing new records, updating existing ones, and executing stored procedures.

Example: Reading Data

This example shows a Node.js Azure Function that retrieves records from a 'products' table.

                    const mysql = require('mysql2/promise');

module.exports = async function (context, req) {
    context.log('JavaScript HTTP trigger function processed a request.');

    const connection = await mysql.createConnection({
        host: process.env.MYSQL_HOST,
        user: process.env.MYSQL_USER,
        password: process.env.MYSQL_PASSWORD,
        database: process.env.MYSQL_DATABASE
    });

    try {
        const [rows] = await connection.execute('SELECT * FROM products');
        context.res = {
            status: 200,
            headers: {
                'Content-Type': 'application/json'
            },
            body: rows
        };
    } catch (error) {
        context.log.error('Database query error:', error);
        context.res = {
            status: 500,
            body: "Error retrieving data from database."
        };
    } finally {
        await connection.end();
    }
};
                

Environment Variables: Ensure these are set in your Function App's Application Settings:

                    MYSQL_HOST=your_mysql_server_name.mysql.database.azure.com
MYSQL_USER=your_db_user
MYSQL_PASSWORD=your_db_password
MYSQL_DATABASE=your_database_name
                

Example: Writing Data

This example demonstrates adding a new product to the 'products' table.

                    const mysql = require('mysql2/promise');

module.exports = async function (context, req) {
    context.log('Add product HTTP trigger function processed a request.');

    const newProduct = req.body;

    if (!newProduct || !newProduct.name || !newProduct.price) {
        context.res = {
            status: 400,
            body: "Please provide product name and price in the request body."
        };
        return;
    }

    const connection = await mysql.createConnection({
        host: process.env.MYSQL_HOST,
        user: process.env.MYSQL_USER,
        password: process.env.MYSQL_PASSWORD,
        database: process.env.MYSQL_DATABASE
    });

    try {
        const [result] = await connection.execute(
            'INSERT INTO products (name, price, description) VALUES (?, ?, ?)',
            [newProduct.name, newProduct.price, newProduct.description || null]
        );
        context.res = {
            status: 201,
            headers: {
                'Content-Type': 'application/json'
            },
            body: { id: result.insertId, ...newProduct }
        };
    } catch (error) {
        context.log.error('Database insert error:', error);
        context.res = {
            status: 500,
            body: "Error adding product to database."
        };
    } finally {
        await connection.end();
    }
};
                

Security Best Practices

Securing your database connection is paramount:

Use Application Settings: Store connection strings and credentials in Function App's Application Settings, not directly in code.
Managed Identities: For enhanced security, consider using Azure Function Managed Identities to authenticate with Azure Database for MySQL if supported by your configuration or intermediate services.
Firewall Rules: Configure Azure Database for MySQL firewall rules to only allow connections from your Function App's outbound IP addresses or VNet integration.
SSL/TLS: Enforce SSL/TLS connections to encrypt data in transit.
Least Privilege: Grant database users only the necessary permissions.

API Endpoints (Illustrative)

Common API patterns for interacting with MySQL data via Functions:

GET /api/products

Retrieves a list of all products.

GET /api/products/{id}

Retrieves a specific product by its ID.

POST /api/products

Creates a new product. Requires product details in the request body.

PUT /api/products/{id}

Updates an existing product. Requires updated details in the request body.

DELETE /api/products/{id}

Deletes a product by its ID.