Synchronous MySQL Client

Common errors

• Error: connect ECONNREFUSED

  cause The MySQL server is not running, is inaccessible from the specified host, or the port is incorrect.
  fix Ensure your MySQL server is running, check the `host` and `port` in your connection configuration, and verify no firewall is blocking the connection. For Docker, ensure the database container is healthy and ports are exposed correctly.

• TypeError: MySql is not a constructor

  cause Attempted to use `MySql` without correctly requiring it or using a named import when it's a default export.
  fix Use `const MySql = require('sync-mysql');` to correctly import the class. If using ESM, consider if `sync-mysql` is the right tool or if a CommonJS wrapper is needed.

• Error: Query arguments required (for parameterized query)

  cause A placeholder `?` was used in the SQL query string, but no array of values was provided as the second argument to `connection.query()`.
  fix Always provide an array of values as the second argument to `connection.query()` when using `?` placeholders. Example: `connection.query('SELECT * FROM users WHERE id = ?', [123])`.

• ReferenceError: assert is not defined

  cause The `assert` module used in quickstart examples is a built-in Node.js module but needs to be explicitly required.
  fix Add `const assert = require('assert');` at the top of your script to make the `assert` function available.

Warnings

• breaking The `sync-mysql` library performs synchronous I/O operations, which fundamentally blocks the Node.js event loop. This makes it unsuitable for server applications or any context requiring high concurrency, as it will halt all other processing until database operations complete. While not an API breaking change, adopting this library in an asynchronous Node.js application is a architectural anti-pattern and will lead to severe performance bottlenecks.
  Fix: For high-performance, asynchronous Node.js applications, use a standard promise-based or callback-based MySQL driver (e.g., `mysql2`, `node-mysql`). `sync-mysql` is best reserved for simple scripts, CLI tools, or tests where blocking is acceptable.
• gotcha Incorrectly concatenating strings into SQL queries can lead to severe SQL injection vulnerabilities. While `sync-mysql` provides parameterized queries via the second argument to `connection.query()`, developers might mistakenly build queries with string concatenation.
  Fix: Always use parameterized queries by passing an array of values as the second argument to `connection.query(sql, values)`. Example: `connection.query('SELECT * FROM users WHERE id = ?', [userId])`.
• gotcha The `sync-mysql` library does not natively support connection pooling or automatic reconnection strategies, common in modern database drivers. This can lead to issues with resource exhaustion or dropped connections over time in long-running processes.
  Fix: For applications requiring connection pooling or robust connection management, prefer asynchronous drivers that offer these features. If `sync-mysql` must be used, consider manual connection management and reconnection logic, though this often defeats the simplicity goal of a synchronous library.
• deprecated The library shows a very slow development cadence, with the last major release (v3.0.0) primarily bumping dependencies and fixing security vulnerabilities rather than adding significant new features or modernizing the API. It lacks features like async/await support, promise-based APIs, or robust TypeScript typings that are standard in modern Node.js ecosystems.
  Fix: Consider migrating to `mysql2` with its promise wrapper for a modern, actively maintained, and performant asynchronous MySQL driver that supports `async/await` and provides official TypeScript definitions.

Install

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

Imports

• MySql
  wrong:

  import MySql from 'sync-mysql';

  correct:

  const MySql = require('sync-mysql');

  The library primarily uses CommonJS `require()` syntax. It exports the `MySql` class as a default export, not named. ESM `import` is not officially supported or documented and may not work without transpilation.
• connection.query
  wrong:

  connection.query('SELECT ' + someVar)

  correct:

  connection.query('SELECT 1')

  Always pass dynamic values as the second argument to `query` to prevent SQL injection, rather than concatenating strings directly into the query string.
• connection.getRecord
  wrong:

  const record = connection.getRecord('users', { id: 1 });

  correct:

  const record = connection.getRecord('users', 1);

  The `getRecord` method expects the table name as the first argument and the ID as the second, assuming a column named 'id'.

Quickstart

This quickstart demonstrates establishing a synchronous MySQL connection, creating a table, inserting data, querying it, and performing a simple calculation, all in a blocking manner. It includes basic error handling and uses environment variables for sensitive credentials.

const MySql = require('sync-mysql');
const assert = require('assert');

const connection = new MySql({
  host: 'localhost',
  user: process.env.DB_USER ?? 'root',
  password: process.env.DB_PASSWORD ?? 'secret',
  database: process.env.DB_NAME ?? 'test_db'
});

try {
  const createTableResult = connection.query('CREATE TABLE IF NOT EXISTS users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255))');
  console.log('Table creation result:', createTableResult);

  const insertResult = connection.query("INSERT INTO users (name) VALUES (?) ", ['Alice']);
  console.log('Insert result:', insertResult);
  assert(insertResult.insertId > 0, 'User should be inserted');

  const selectResult = connection.query('SELECT * FROM users WHERE id = ?', [insertResult.insertId]);
  console.log('Select result:', selectResult);
  assert(selectResult.length === 1, 'Should find the inserted user');
  assert(selectResult[0].name === 'Alice', 'User name should be Alice');

  const solution = connection.query('SELECT 1 + 1 AS solution');
  assert(solution[0].solution === 2, 'Basic calculation failed');
  console.log('Synchronous queries executed successfully.');

  connection.end(); // It's good practice to end the connection when done.
} catch (error) {
  console.error('An error occurred:', error.message);
  // Ensure connection is ended even on error
  if (connection && typeof connection.end === 'function') {
    connection.end();
  }
  process.exit(1);
}