Example node.js scripts to modify a SQL dump file imported from a WordPress single site and multisite installation to only retain the first 10 records from the wp_posts and wp_users tables

bp_subset.js

// Export BuddyPress-related tables for the first 10 users of the main site (subdirectory 1)
const exportMainSiteBPCommand = `
  mysqldump -u ${dbConfig.user} -p${dbConfig.password} -h ${dbConfig.host} ${dbConfig.database} wp_1_bp_follow wp_1_bp_friends wp_1_bp_document wp_1_bp_document_folder wp_1_bp_document_folder_meta wp_1_bp_groups wp_1_bp_groups_groupmeta wp_1_bp_groups_membermeta wp_1_bp_groups_members wp_1_bp_invitations wp_1_bp_invitations_invitemeta wp_1_bp_messages_messages wp_1_bp_messages_meta wp_1_bp_messages_notices wp_1_bp_messages_recipients --where="user_id IN (SELECT ID FROM wp_1_users LIMIT 10)"
   > db-export-main-site-bp.sql
`;

// Export BuddyPress-related tables for the first 10 users of the second site (subdirectory 2)
const exportSecondSiteBPCommand = `
  mysqldump -u ${dbConfig.user} -p${dbConfig.password} -h ${dbConfig.host} ${dbConfig.database} wp_2_bp_follow wp_2_bp_friends wp_2_bp_document wp_2_bp_document_folder wp_2_bp_document_folder_meta wp_2_bp_groups wp_2_bp_groups_groupmeta wp_2_bp_groups_membermeta wp_2_bp_groups_members wp_2_bp_invitations wp_2_bp_invitations_invitemeta wp_2_bp_messages_messages wp_2_bp_messages_meta wp_2_bp_messages_notices wp_2_bp_messages_recipients --where="user_id IN (SELECT ID FROM wp_2_users LIMIT 10)"
   > db-export-second-site-bp.sql
`;

db_post_user_subset.js

const fs = require("fs");

const inputFilePath = "/path/to/full_database.sql";
const outputFilePath = "/path/to/modified_database.sql";

const inputStream = fs.createReadStream(inputFilePath, { encoding: "utf8" });
const outputStream = fs.createWriteStream(outputFilePath);

let postCounter = 0;
let userCounter = 0;
let write = false;

inputStream.on("data", (chunk) => {
  const lines = chunk.split("\n");

  lines.forEach((line) => {
    if (line.startsWith("INSERT INTO `wp_posts`")) {
      postCounter++;
      write = postCounter <= 10;
    } else if (line.startsWith("INSERT INTO `wp_users`")) {
      userCounter++;
      write = userCounter <= 10;
    }

    if (write) {
      outputStream.write(`${line}\n`);
    }
  });
});

inputStream.on("end", () => {
  outputStream.end();
});

outputStream.on("finish", () => {
  console.log("Modified SQL dump created successfully.");
});

outputStream.on("error", (err) => {
  console.error("Error writing to the output file:", err);
});

// Handle any errors while reading the input file
inputStream.on("error", (err) => {
  console.error("Error reading the input file:", err);
});

db_post_user_subset_ms.js

const fs = require("fs");
const { exec } = require("child_process");

// Your MySQL/MariaDB details
const dbConfig = {
  host: "localhost",
  user: "your_username",
  password: "your_password",
  database: "your_database",
};

// Export first 10 users and posts of the main site (subdirectory 1)
const exportMainSiteCommand = `
  mysqldump -u ${dbConfig.user} -p${dbConfig.password} -h ${dbConfig.host} ${dbConfig.database} wp_1_users wp_1_posts --where="1 LIMIT 10"
   > db-export-main-site.sql
`;

// Export first 10 users and posts of the second site (subdirectory 2)
const exportSecondSiteCommand = `
  mysqldump -u ${dbConfig.user} -p${dbConfig.password} -h ${dbConfig.host} ${dbConfig.database} wp_2_users wp_2_posts --where="1 LIMIT 10"
   > db-export-second-site.sql
`;

exec(exportMainSiteCommand, (error, stdout, stderr) => {
  if (error) {
    console.error(`Error exporting main site: ${error.message}`);
    return;
  }
  if (stderr) {
    console.error(`stderr exporting main site: ${stderr}`);
    return;
  }
  console.log(`Exported first 10 users and posts of the main site to db-export-main-site.sql`);
});

exec(exportSecondSiteCommand, (error, stdout, stderr) => {
  if (error) {
    console.error(`Error exporting second site: ${error.message}`);
    return;
  }
  if (stderr) {
    console.error(`stderr exporting second site: ${stderr}`);
    return;
  }
  console.log(`Exported first 10 users and posts of the second site to db-export-second-site.sql`);
});

These are example node.js scripts to modify a SQL dump file imported from a large site to only retain the first 10 records from the wp_posts and wp_users tables, as well as instructions for how to retain the first 10 records of all bp tables associated with those users. There is a script for a single site installation and a multisite network installation, but the bp tables code is included here separately as an example to modify the first two scripts because of the differences in how that would be implemented based on your database structure.

This is useful to manage large datasets while maintaining privacy, security, and reducing the complexity of development environments. It's especially useful when you only need a subset of the data for testing and development purposes. It is not run before or during importing a database from a live site, it is run after the sql dump file has been imported and then creates a modified sql file to use in order to complete the import process.

This approach allows you to handle the data after the initial import, giving you the flexibility to filter and modify the dataset as needed without altering the live site's database. It's a smart way to customize and streamline the development environment without affecting the original data.

Single Site Databases
This uses Node.js' fs module to read the input SQL dump file, process its contents line by line, identify the INSERT statements for the wp_posts and wp_users tables, and write only the first 10 records of each table to the output file. Ensure to replace /path/to/full_database.sql and /path/to/modified_database.sql with your actual file paths. Run this Node.js script using node db_post_user_subset.js, and it should create a modified SQL dump file with the desired number of records for wp_posts and wp_users.

Multisite Databases
For a multisite network, the user and post data are stored in tables associated with each site within the network. Use the db_post_user_subset_ms.js script to export only the first 10 users and posts for the main site and one subdirectory site of a multisite network. This script will create two separate SQL files, one for the main site (wp_1) and another for the second site (wp_2), each containing the first 10 users and posts from their respective sites in the multisite network. Adjust the table prefixes and site IDs (wp_1, wp_2) according to your database structure.

BuddyBoss/BuddyPress Tables
If you want to retain only the BuddyPress-related data associated with the first 10 users, you'd need to adjust the one of the first to scripts and add the code in bp_subset.js to include additional tables in the export query for the specific tables related to BuddyPress accordingly. Adjust the table names (prefixed with wp_1 and wp_2), the user IDs, and the number of users you want to include in the export based on your BuddyPress setup and database structure. This modified script will export the BuddyPress-related data associated with the first 10 users for each site in your multisite network into separate SQL files.

Run the Node.js script using the name of the script, like:
node db_post_user_subset.js

Please note, as always, be cautious while handling database files. Create backups before making any modifications to avoid accidental data loss. Adjust the script to match the structure of your SQL dump and handle edge cases specific to your database schema.