{"id":16653,"library":"mysql-all-in-one","title":"mysql-all-in-one","description":"mysql-all-in-one is a comprehensive wrapper library for interacting with MySQL databases in Node.js, built on top of the robust `mysql2` package. Currently at version 4.10.4, it provides a high-level Data Access Object (DAO) for executing common database operations like SELECT, INSERT, UPDATE, DELETE, and UPSERT. A core feature is its inherent prevention of SQL injection vulnerabilities through the consistent use of prepared statements. The package also includes a standalone Query Builder for constructing raw SQL queries programmatically, offering advanced capabilities like complex WHERE clause nesting and custom SQL expressions. Key functionalities extend to creating database dumps and simplifying multi-database interactions. While the README doesn't specify a precise release cadence, its active development and recent version history suggest ongoing maintenance. Its primary differentiator lies in offering a simplified, secure, and opinionated interface over `mysql2`, ideal for applications requiring robust database interactions without direct exposure to raw SQL string manipulation.","status":"active","version":"4.10.4","language":"javascript","source_language":"en","source_url":"https://github.com/Gabriel-Eduardo-Cunha/mysql-all-in-one","tags":["javascript","mysql","safe","sqlinjection","query","querybuilder","dataaccessobject","DAO","connection","typescript"],"install":[{"cmd":"npm install mysql-all-in-one","lang":"bash","label":"npm"},{"cmd":"yarn add mysql-all-in-one","lang":"bash","label":"yarn"},{"cmd":"pnpm add mysql-all-in-one","lang":"bash","label":"pnpm"}],"dependencies":[{"reason":"This package is a wrapper around `mysql2` and uses it as its underlying database driver.","package":"mysql2","optional":false}],"imports":[{"note":"The common CJS `require` pattern for named exports is `const { DataAccessObject } = require('mysql-all-in-one');`. The library ships with TypeScript types.","wrong":"const DataAccessObject = require('mysql-all-in-one').DataAccessObject;","symbol":"DataAccessObject","correct":"import { DataAccessObject } from 'mysql-all-in-one';"},{"note":"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.","wrong":"const QueryBuilder = require('mysql-all-in-one/QueryBuilder');","symbol":"QueryBuilder","correct":"import { QueryBuilder } from 'mysql-all-in-one';"},{"note":"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.","wrong":"import { sqlExpression } from 'mysql-all-in-one';","symbol":"sqlExpression","correct":"import { QueryBuilder } from 'mysql-all-in-one';\nconst { sqlExpression } = QueryBuilder;"}],"quickstart":{"code":"import { DataAccessObject } from 'mysql-all-in-one';\n\nconst dao = new DataAccessObject({\n  host: process.env.DB_HOST ?? 'localhost',\n  user: process.env.DB_USER ?? 'root',\n  password: process.env.DB_PASSWORD ?? '1234',\n  port: Number(process.env.DB_PORT ?? 3306),\n  database: process.env.DB_DATABASE ?? 'test_database'\n});\n\nconst main = async () => {\n  try {\n    // Example: Create a table if it doesn't exist\n    await dao.query('CREATE TABLE IF NOT EXISTS my_table (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), age INT)');\n    console.log('Table my_table ensured.');\n\n    // Example: Insert data\n    await dao.insert({ into: 'my_table', values: { name: 'Alice', age: 30 } });\n    await dao.insert({ into: 'my_table', values: { name: 'Bob', age: 24 } });\n    console.log('Inserted sample data.');\n\n    // Example: Select all rows\n    const result = await dao.select({ from: 'my_table' });\n    console.log('All rows:', result);\n\n    // Example: Select with a WHERE clause\n    const specificResult = await dao.select({ from: 'my_table', where: { name: 'Alice' } });\n    console.log('Alice data:', specificResult);\n\n  } catch (error) {\n    console.error('Database operation failed:', error);\n  } finally {\n    // Disconnect if the DAO doesn't handle pooling internally (or if explicit close is needed)\n    // The current DAO design maintains connection, explicit close might depend on `mysql2`'s pooling behavior.\n    // For simple scripts, allowing process to exit is common.\n  }\n};\n\nmain();","lang":"typescript","description":"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."},"warnings":[{"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.","message":"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.","severity":"breaking","affected_versions":">=1.0"},{"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.","message":"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.","severity":"gotcha","affected_versions":">=1.0"},{"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.","message":"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.","severity":"gotcha","affected_versions":">=1.0"}],"env_vars":null,"last_verified":"2026-04-22T00:00:00.000Z","next_check":"2026-07-21T00:00:00.000Z","problems":[{"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.","cause":"The MySQL database server is not running, is not accessible at the specified host/port, or firewall rules are blocking the connection.","error":"Error: connect ECONNREFUSED 127.0.0.1:3306"},{"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.","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.","error":"TypeError: dao.select is not a function"},{"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.","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.","error":"ER_BAD_FIELD_ERROR: Unknown column 'your_column_name' in 'where clause'"}],"ecosystem":"npm"}