MySQL2 SQL File Importer

Common errors

• Error: Cannot find module 'mysql-import'

  cause This error occurs because the example in the `mysql2-import` README uses `require('mysql-import')` which refers to the original package, not `mysql2-import`.
  fix Change the import statement to `const Importer = require('mysql2-import');` or `import Importer from 'mysql2-import';`

• Error: ER_ACCESS_DENIED_ERROR: Access denied for user 'user'@'localhost' (using password: YES/NO)

  cause The provided username or password for the MySQL connection is incorrect, or the user lacks permissions from the specified host.
  fix Verify your MySQL `user`, `password`, and `host` credentials. Ensure the user has `GRANT` privileges for the target database and can connect from `localhost` or the specified `host`.

Warnings

• gotcha The README examples for `require` syntax incorrectly use `require('mysql-import')` instead of the package's actual name, `mysql2-import`. This will lead to a 'Cannot find module' error.
  Fix: Always use `require('mysql2-import')` or `import Importer from 'mysql2-import';`.
• breaking The `onProgress` and `onDumpCompleted` methods were introduced in version 5.0. Code relying on these callbacks will break if upgrading from a version older than 5.0.
  Fix: Review the API documentation for version 5.0+ and update any callback handling accordingly. For older versions, alternative progress tracking methods may be necessary or an upgrade to `mysql2-import@5.x`.
• gotcha Providing incorrect database connection parameters (host, user, password, database) is a very common source of errors. Ensure your MySQL server is running, the user has appropriate permissions, and network access is configured correctly.
  Fix: Double-check all connection parameters. Use environment variables for sensitive credentials. Verify database server accessibility and user privileges independently using a MySQL client.

Install

• npm install mysql2-import npm
• yarn add mysql2-import yarn
• pnpm add mysql2-import pnpm

Imports

• Importer
  wrong:

  const Importer = require('mysql-import');

  correct:

  import Importer from 'mysql2-import';

  The original README example incorrectly uses `mysql-import` instead of `mysql2-import`. The package also supports CommonJS `require` syntax.
• Importer
  wrong:

  import { Importer } from 'mysql2-import';

  correct:

  const Importer = require('mysql2-import');

  The default export for `Importer` is the class itself. Avoid named imports unless the package specifically exports named members (which it does not for the main class).
• Importer.prototype.import
  wrong:

  importer.import(['path/to/dump.sql']);

  correct:

  importer.import('path/to/dump.sql').then(() => { /* ... */ });

  The `import` method takes a variadic number of string arguments (file paths) or arrays of paths, not necessarily a single array as the primary form.

Quickstart

This quickstart demonstrates how to initialize the Importer, set up progress and completion callbacks, import a SQL file, and handle potential errors, including creating a dummy SQL file for a runnable example.

import Importer from 'mysql2-import';
import path from 'path';

// Ensure .env or similar is set up for production
const host = process.env.MYSQL_HOST ?? 'localhost';
const user = process.env.MYSQL_USER ?? 'root';
const password = process.env.MYSQL_PASSWORD ?? 'password';
const database = process.env.MYSQL_DATABASE ?? 'mydb';

const importer = new Importer({ host, user, password, database });

// Create a dummy SQL file for demonstration if it doesn't exist
import fs from 'fs';
const dummySqlFilePath = path.join(process.cwd(), 'dummy_data.sql');
if (!fs.existsSync(dummySqlFilePath)) {
  fs.writeFileSync(dummySqlFilePath, `
DROP TABLE IF EXISTS \`users\`;
CREATE TABLE \`users\` (
  \`id\` INT AUTO_INCREMENT PRIMARY KEY,
  \`name\` VARCHAR(255) NOT NULL
);
INSERT INTO \`users\` (name) VALUES ('Alice'), ('Bob');
`);
  console.log('Created dummy_data.sql for quickstart.');
}

importer.onProgress(progress => {
  const percent = Math.floor(progress.bytes_processed / progress.total_bytes * 10000) / 100;
  console.log(`File ${progress.file_no} of ${progress.total_files}: ${percent}% Completed (${progress.file_path})`);
});

importer.onDumpCompleted(data => {
  if (data.error) {
    console.error(`Error completing dump for ${data.file_path}:`, data.error);
  } else {
    console.log(`Dump completed for ${data.file_path}`);
  }
});

importer.import(dummySqlFilePath).then(() => {
  const files_imported = importer.getImported();
  console.log(`${files_imported.length} SQL file(s) imported successfully.`);
}).catch(err => {
  console.error('An error occurred during import:', err);
}).finally(() => {
  importer.disconnect();
  console.log('Importer disconnected.');
  // Clean up dummy file if it was created
  if (fs.existsSync(dummySqlFilePath)) {
    fs.unlinkSync(dummySqlFilePath);
    console.log('Removed dummy_data.sql.');
  }
});