Turn a GHOST.db posts in to mark down

generateMarkdown.js

// Import required modules
const sqlite3 = require('sqlite3').verbose(); // SQLite3 module for database operations
const fs = require('fs').promises; // File System module for file operations
const { promisify } = require('util'); // Util module to convert callback-based functions to promises

/**
 * Generates Markdown files from data in the SQLite database and sets their metadata.
 * The Markdown files are organized in folders based on the "posted" date from the database.
 */
(async () => {
  try {
    // Set the output folder path where the generated Markdown files will be stored
    const outputFolderPath = `./output`;

    // SQL query to retrieve data from the "posts" table, including formatted "posted" date
    const query = `
      SELECT title, slug, html, DATETIME(ROUND(p.created_at  / 1000), 'unixepoch') AS posted
      FROM posts p
    `;

    // Path to the SQLite database file
    const dbPath = './path/to/ghost.db'; // Update with the correct path to your ghost.db file

    // Open the SQLite database and execute the query to retrieve the data
    const db = new sqlite3.Database(dbPath, sqlite3.OPEN_READONLY);
    const rows = await promisify(db.all).call(db, query);

    // Loop through the retrieved data and generate Markdown files for each row
    for (const row of rows) {
      // Get the year from the "posted" date to organize files into folders
      const folderName = new Date(row.posted).getFullYear();

      // Create the file name for the Markdown file, replacing colons with hyphens in the title
      const fileName = `${row.slug}.md`;
      const titleWithHyphens = row.title.replace(/:/g, '-'); // Replace colons with hyphens

      // Format the date as "yyyy-mm-dd hh:mm" for the metadata
      const date = new Date(row.posted).toISOString().slice(0, 19).replace('T', ' ');

      // Generate the content of the Markdown file with metadata and HTML content
      const content = `---\ntitle: ${titleWithHyphens}\ndate: ${date}\n---\n\n${row.html}`;

      // Create the folder path and file path for the current Markdown file
      const folderPath = `${outputFolderPath}/${folderName}`;
      const filePath = `${folderPath}/${fileName}`;

      try {
        // Create the folder if it doesn't exist (recursive: true allows nested folders)
        await fs.mkdir(folderPath, { recursive: true });

        // Write the content to the Markdown file
        await fs.writeFile(filePath, content);
        console.log(`Created Markdown file: ${filePath}`);

        // Set the file's created date and last modified date to match the "posted" column's date
        const postedDate = new Date(row.posted);
        await fs.utimes(filePath, postedDate, postedDate);
      } catch (err) {
        // Handle any errors that occur during file creation or writing
        console.error(`Error creating Markdown file: ${err.message}`);
      }
    }

    // Close the SQLite database connection after processing all rows
    db.close();
    console.log('All Markdown files have been created.');
  } catch (err) {
    // Handle any other errors that occur during the script execution
    console.error(`Error: ${err.message}`);
  }
})();