基于Node.js的Azure Function生成CSV并邮件发送方案咨询

Hey there! Glad you’ve already nailed the MySQL data fetching part—let’s walk through the remaining steps to generate a CSV and send it as an email attachment in your Azure Function. I’ll break this down into two clear parts, with code examples you can adapt directly.

Part 1: Generate CSV from MySQL Data #

First, we’ll convert the JSON data you get from MySQL into a CSV format. For this, I recommend using the csv-writer npm package—it’s lightweight and perfect for serverless environments like Azure Functions.

Step 1: Install Dependencies #

Add the package to your project by running this in your Function directory:

npm install csv-writer

Step 2: Convert Data to CSV (In-Memory) #

Instead of writing to a local file (which isn’t reliable in serverless), we’ll generate the CSV directly in memory as a string. Here’s how to integrate this with your existing MySQL fetch code:

const { createObjectCsvStringifier } = require('csv-writer');

// Assume this is your existing MySQL data fetch function
async function fetchDataFromMySQL() {
  // Your existing code to pull data (returns an array of objects)
  return [
    { id: 1, name: "John Doe", email: "john@example.com" },
    { id: 2, name: "Jane Smith", email: "jane@example.com" }
  ];
}

async function generateCsv(data) {
  // Define CSV columns (match the keys in your MySQL data)
  const csvStringifier = createObjectCsvStringifier({
    header: [
      { id: 'id', title: 'ID' },
      { id: 'name', title: 'NAME' },
      { id: 'email', title: 'EMAIL' }
    ]
  });

  // Combine header and rows into a single CSV string
  const header = csvStringifier.getHeaderString();
  const rows = csvStringifier.stringifyRecords(data);
  return header + rows;
}

Part 2: Send CSV as Email Attachment #

For sending emails, nodemailer is the go-to package for Node.js. If you’re using Azure, SendGrid (a Microsoft service) is also a great option for better deliverability—both work seamlessly. Let’s cover both:

Option A: Using Nodemailer with SMTP #

Step 1: Install Nodemailer

npm install nodemailer

Step 2: Configure Email Client & Send with Attachment

Store your SMTP credentials (username, password, server) in Azure Function’s Application Settings (never hardcode them!) so you can access them via process.env.

const nodemailer = require('nodemailer');

async function sendEmailWithCsvAttachment(csvContent) {
  // Create SMTP transporter (use your email provider's details)
  const transporter = nodemailer.createTransport({
    host: process.env.SMTP_HOST, // e.g., "smtp.office365.com" for Outlook
    port: process.env.SMTP_PORT || 587,
    secure: false, // true for 465, false for other ports
    auth: {
      user: process.env.SMTP_USER, // Your sender email
      pass: process.env.SMTP_PASS  // Your email password/app password
    }
  });

  // Email options with CSV attachment
  const mailOptions = {
    from: 'your-sender-email@example.com',
    to: 'recipient@example.com', // The specified user(s)
    subject: 'MySQL Data Export',
    text: 'Attached is the latest data export from your MySQL database.',
    attachments: [
      {
        filename: 'mysql-data.csv',
        content: csvContent,
        contentType: 'text/csv'
      }
    ]
  };

  // Send the email
  try {
    await transporter.sendMail(mailOptions);
    console.log('Email sent successfully');
  } catch (error) {
    console.error('Error sending email:', error);
    throw error;
  }
}

Option B: Using Azure SendGrid (Recommended for Azure Environments) #

SendGrid integrates seamlessly with Azure and offers better deliverability. Here’s a quick example:

Step 1: Install SendGrid Package

npm install @sendgrid/mail

Step 2: Configure & Send Email

Get your SendGrid API key from the Azure Portal or SendGrid dashboard, then add it to Application Settings as SENDGRID_API_KEY.

const sgMail = require('@sendgrid/mail');
sgMail.setApiKey(process.env.SENDGRID_API_KEY);

async function sendEmailWithCsvAttachment(csvContent) {
  const msg = {
    to: 'recipient@example.com',
    from: 'your-sender-email@example.com',
    subject: 'MySQL Data Export',
    text: 'Attached is the latest data export from your MySQL database.',
    attachments: [
      {
        filename: 'mysql-data.csv',
        content: Buffer.from(csvContent).toString('base64'),
        type: 'text/csv',
        disposition: 'attachment'
      }
    ]
  };

  try {
    await sgMail.send(msg);
    console.log('Email sent via SendGrid successfully');
  } catch (error) {
    console.error('SendGrid error:', error);
    throw error;
  }
}

Combine Everything in Your Azure Function #

Now, tie all parts together in your Function trigger (e.g., HTTP trigger or Timer trigger):

module.exports = async function (context, req) {
  try {
    // 1. Fetch data from MySQL
    const data = await fetchDataFromMySQL();
    
    // 2. Generate CSV
    const csvContent = await generateCsv(data);
    
    // 3. Send email with attachment
    await sendEmailWithCsvAttachment(csvContent);

    context.res = {
      status: 200,
      body: 'CSV generated and email sent successfully'
    };
  } catch (error) {
    context.res = {
      status: 500,
      body: `Error: ${error.message}`
    };
    context.log.error('Function failed:', error);
  }
};

Key Notes for Azure Function Deployment #

• Application Settings: Store all sensitive data (DB credentials, SMTP/SendGrid keys) in Azure Function’s Application Settings to keep them secure.
• Network Access: Ensure your Azure Function can reach your MySQL server (if it’s in a VNET, configure VNET integration) and the email server/SendGrid API.
• Large Data Handling: If you’re dealing with huge datasets, use streaming CSV libraries like fast-csv to avoid memory issues instead of generating the entire CSV in memory.
• Error Handling: Add proper try/catch blocks to handle failures at each step (data fetch, CSV generation, email send) and log errors for debugging.

内容的提问来源于stack exchange，提问作者Sanjay Kumawat