如何从数据库记录自动生成JavaScript站点地图列表？

Got it, let's break down how to automatically populate that urls array from your database records. I’ll use a Node.js example since your code snippet looks like it’s using the sitemap npm package, and I’ll cover common database setups so you can adapt it to your stack.

Core Idea #

The plan is straightforward:

1. Define fixed sitemap entries (like your homepage)
2. Fetch dynamic records from your database
3. Map those records into the format the sitemap library expects
4. Merge fixed and dynamic entries, then generate the final sitemap

Full Example (Using Mongoose for MongoDB) #

Let’s assume you have a photos collection storing your image records, with fields like _id (unique ID) and updatedAt (last modified timestamp).

const sm = require('sitemap');
const Photo = require('./models/Photo'); // Import your Mongoose model

async function buildSitemap() {
  const hostname = 'https://your-domain.com';

  // Step 1: Static entries (pages that don't change dynamically)
  const staticUrls = [
    {
      url: '/',
      changefreq: 'weekly',
      priority: 0.8,
      lastmodrealtime: true,
      lastmodfile: 'views/home.hbs'
    }
  ];

  try {
    // Step 2: Fetch only the fields we need from the database to keep queries efficient
    const photoRecords = await Photo.find().select('_id updatedAt');

    // Step 3: Map database records to sitemap-compatible URL objects
    const dynamicPhotoUrls = photoRecords.map(photo => ({
      url: `/photo/${photo._id}`, // Match your app's route structure
      changefreq: 'weekly',
      priority: 0.8,
      lastmodrealtime: true,
      lastmod: photo.updatedAt.toISOString() // Use the DB's modified timestamp for lastmod
    }));

    // Step 4: Combine static and dynamic entries
    const allUrls = [...staticUrls, ...dynamicPhotoUrls];

    // Step 5: Generate the sitemap
    const sitemap = sm.createSitemap({
      hostname: hostname,
      cacheTime: 600000, // 10-minute cache period
      urls: allUrls
    });

    // Optional: Save the sitemap to a file or serve it via HTTP
    // sitemap.toFile('./public/sitemap.xml', (err) => {
    //   if (err) console.error('Failed to write sitemap:', err);
    //   else console.log('Sitemap generated successfully!');
    // });

    return sitemap;
  } catch (error) {
    console.error('Error building sitemap:', error);
    throw error;
  }
}

// Run the function to generate your sitemap
buildSitemap();

Adapting to SQL (Using Sequelize) #

If you’re using a SQL database like PostgreSQL or MySQL with Sequelize, the only changes are in how you fetch the data:

const { Photo } = require('./models'); // Import your Sequelize model

// ... inside the buildSitemap function ...
const photoRecords = await Photo.findAll({
  attributes: ['id', 'updatedAt'] // Only fetch necessary fields
});

const dynamicPhotoUrls = photoRecords.map(photo => ({
  url: `/photo/${photo.id}`, // Use `id` instead of `_id` for SQL databases
  changefreq: 'weekly',
  priority: 0.8,
  lastmodrealtime: true,
  lastmod: photo.updatedAt.toISOString()
}));

Customization Tips #

• Per-record priorities/changefreq: If some pages need different values (e.g., featured photos have higher priority), add priority and changefreq columns to your database, then reference them in the map function: priority: photo.priority
• Template file paths: If you need lastmodfile for dynamic pages, store the template path in your database (e.g., a templatePath field) and include it in the mapped object
• Auto-refresh: Since you’ve set a cacheTime, consider running this function periodically (e.g., with a cron job) to refresh the sitemap as your database content updates

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