mysql-all-in-one

Common errors

• Error: connect ECONNREFUSED 127.0.0.1:3306

  cause The MySQL database server is not running, is not accessible at the specified host/port, or firewall rules are blocking the connection.
  fix Ensure your MySQL server is running, verify the `host` and `port` in your `DataAccessObject` configuration, and check any local or network firewall settings that might be preventing the connection.

• TypeError: dao.select is not a function

  cause The `dao` object was not correctly instantiated as `DataAccessObject`, or `DataAccessObject` was not imported correctly, leading to `dao` being an undefined or incorrectly typed variable.
  fix Confirm that `const dao = new DataAccessObject(...)` is correctly written, and that `DataAccessObject` is imported from `mysql-all-in-one` using the correct ESM or CommonJS syntax.

• ER_BAD_FIELD_ERROR: Unknown column 'your_column_name' in 'where clause'

  cause A column name used in a query (e.g., within a `where` clause object of `select`, `update`, or `delete` methods) does not exist in the specified database table.
  fix Review your database schema and ensure all column names used in your `DataAccessObject` or `QueryBuilder` calls exactly match the actual column names in your target table. Pay attention to case sensitivity depending on your MySQL server configuration.

Warnings

• breaking This library acts as a wrapper around the `mysql2` package. While it abstracts many complexities, major version upgrades of the underlying `mysql2` dependency (which are not directly controlled by `mysql-all-in-one`'s versioning) could introduce subtle behavioral changes or require updates to connection options that might not be immediately reflected or fully abstracted. Always consult the `mysql2` changelog when troubleshooting unexpected behavior.
  Fix: Review the changelogs for `mysql2` after any major version update of that package. Check `mysql-all-in-one`'s release notes for any adaptations or new guidance related to `mysql2` changes.
• gotcha Hardcoding sensitive database credentials (host, user, password) directly in application code, especially for production environments, poses a significant security risk. This practice can lead to credential exposure if the code repository is compromised or accidentally exposed.
  Fix: Always use environment variables (e.g., `process.env.DB_HOST`), configuration management systems, or secure secret management services to inject connection details at runtime. The quickstart example demonstrates using `process.env` for this purpose.
• gotcha While `mysql-all-in-one` is designed to be SQL injection-proof by leveraging prepared statements for its `DataAccessObject` and structured `QueryBuilder` methods, custom SQL expressions created with `sqlExpression` still require careful usage. Although `sqlExpression` automatically escapes variables within its template literals, constructing arbitrary SQL strings without using this templating or manually escaping user-supplied data can inadvertently reintroduce vulnerabilities.
  Fix: Always pass dynamic values through `sqlExpression`'s template literal placeholders (e.g., `sqlExpression`WHERE column = ${userValue}`) rather than concatenating strings directly. For maximum safety, prefer the high-level `DataAccessObject` methods whenever possible.

Install

• npm install mysql-all-in-one npm
• yarn add mysql-all-in-one yarn
• pnpm add mysql-all-in-one pnpm

Imports

• DataAccessObject
  wrong:

  const DataAccessObject = require('mysql-all-in-one').DataAccessObject;

  correct:

  import { DataAccessObject } from 'mysql-all-in-one';

  The common CJS `require` pattern for named exports is `const { DataAccessObject } = require('mysql-all-in-one');`. The library ships with TypeScript types.
• QueryBuilder
  wrong:

  const QueryBuilder = require('mysql-all-in-one/QueryBuilder');

  correct:

  import { QueryBuilder } from 'mysql-all-in-one';

  QueryBuilder can be imported as a named export from the main package or directly from its module path. Both ESM and CJS forms are supported.
• sqlExpression
  wrong:

  import { sqlExpression } from 'mysql-all-in-one';

  correct:

  import { QueryBuilder } from 'mysql-all-in-one';
  const { sqlExpression } = QueryBuilder;

  The `sqlExpression` utility is typically destructured from the `QueryBuilder` object, rather than being a direct named export from the root package. It's used for safely injecting custom SQL expressions into queries.

Quickstart

This quickstart demonstrates how to establish a database connection using the DataAccessObject, create a table, insert data, and perform basic SELECT queries. It uses environment variables for secure credential handling.

import { DataAccessObject } from 'mysql-all-in-one';

const dao = new DataAccessObject({
  host: process.env.DB_HOST ?? 'localhost',
  user: process.env.DB_USER ?? 'root',
  password: process.env.DB_PASSWORD ?? '1234',
  port: Number(process.env.DB_PORT ?? 3306),
  database: process.env.DB_DATABASE ?? 'test_database'
});

const main = async () => {
  try {
    // Example: Create a table if it doesn't exist
    await dao.query('CREATE TABLE IF NOT EXISTS my_table (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), age INT)');
    console.log('Table my_table ensured.');

    // Example: Insert data
    await dao.insert({ into: 'my_table', values: { name: 'Alice', age: 30 } });
    await dao.insert({ into: 'my_table', values: { name: 'Bob', age: 24 } });
    console.log('Inserted sample data.');

    // Example: Select all rows
    const result = await dao.select({ from: 'my_table' });
    console.log('All rows:', result);

    // Example: Select with a WHERE clause
    const specificResult = await dao.select({ from: 'my_table', where: { name: 'Alice' } });
    console.log('Alice data:', specificResult);

  } catch (error) {
    console.error('Database operation failed:', error);
  } finally {
    // Disconnect if the DAO doesn't handle pooling internally (or if explicit close is needed)
    // The current DAO design maintains connection, explicit close might depend on `mysql2`'s pooling behavior.
    // For simple scripts, allowing process to exit is common.
  }
};

main();