Skip to content

Instantly share code, notes, and snippets.

Created July 21, 2023 11:35
Show Gist options
  • Save andyj/c097014d8784a27903604a4bd2f33166 to your computer and use it in GitHub Desktop.
Save andyj/c097014d8784a27903604a4bd2f33166 to your computer and use it in GitHub Desktop.
Turn a GHOST.db posts in to mark down
// 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
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}`);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment