Node.js MySQL SQL File Importer

Common errors

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

  cause Incorrect MySQL database credentials (username or password) or insufficient permissions for the user.
  fix Double-check the `host`, `user`, and `password` parameters in the Importer constructor. Ensure the MySQL user has appropriate privileges to connect and write to the target database.

• ER_BAD_DB_ERROR: Unknown database 'mydb'

  cause The specified `database` in the Importer constructor or `importer.use()` method does not exist on the MySQL server.
  fix Create the database manually on your MySQL server before running the import, or ensure the SQL dump itself includes a `CREATE DATABASE` statement that executes successfully beforehand.

• Error: ENOENT: no such file or directory, open 'path/to/dump.sql'

  cause The path provided to `importer.import()` is incorrect, or the SQL file does not exist at the specified location.
  fix Verify the absolute or relative path to your SQL dump file. Ensure file permissions allow Node.js to read the file.

Warnings

• gotcha When dealing with very large SQL dump files, ensure that your MySQL server's `max_allowed_packet` setting is sufficiently large to accommodate the largest single SQL statement. Insufficient packet size can lead to import failures.
  Fix: Adjust `max_allowed_packet` in your `my.cnf` or `my.ini` file (e.g., `max_allowed_packet=256M`) and restart MySQL.
• gotcha Character encoding mismatches between your SQL dump file and the target MySQL database or connection can lead to 'Incorrect string value' errors or corrupted data. Always ensure consistent encoding, typically UTF-8 or utf8mb4.
  Fix: Use `importer.setEncoding('utf8mb4')` if your dump is UTF-8 based, and verify your MySQL database and table collations are also `utf8mb4_unicode_ci` or similar.
• gotcha If your SQL dump contains `CREATE DATABASE` or `USE database` statements, particularly when importing into an existing database or an environment with restricted permissions (e.g., shared hosting), these statements might cause 'Access denied' or 'Unknown database' errors.
  Fix: Pre-create the database if it doesn't exist, and consider stripping `CREATE DATABASE` and `USE` statements from your SQL dump if you intend to import into a specific pre-selected database.
• gotcha Providing dynamically generated or untrusted file paths to `importer.import()` without validation can pose a security risk, potentially allowing path traversal or the execution of arbitrary SQL files on your server.
  Fix: Always validate and sanitize file paths, ensuring they point only to expected and trusted SQL dump files.

Install

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

Imports

• Importer
  wrong:

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

  correct:

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

  For CommonJS, the library exports the Importer class directly as the module's default export.
• Importer
  wrong:

  import { Importer } from 'mysql-import';

  correct:

  import Importer from 'mysql-import';

  For ES Modules, the Importer class is the default export. Avoid named import syntax.

Quickstart

Demonstrates how to initialize the Importer, set up progress tracking, and import a single SQL file.

const host = 'localhost';
const user = 'root';
const password = 'password';
const database = 'mydb';

const Importer = require('mysql-import');
const importer = new Importer({host, user, password, database});

importer.onProgress(progress=>{
  var percent = Math.floor(progress.bytes_processed / progress.total_bytes * 10000) / 100;
  console.log(`${percent}% Completed`);
});

importer.import('path/to/dump.sql').then(()=>{
  var files_imported = importer.getImported();
  console.log(`${files_imported.length} SQL file(s) imported.`);
}).catch(err=>{
  console.error(err);
});