Multi-Database Query Library

Common errors

• Error: Cannot find module 'mysql' (or 'pg', 'mssql', etc.)

  cause Although `database-query` lists all drivers as dependencies, sometimes a specific driver might fail to install or be missing from the environment.
  fix Manually try `npm install <missing-driver-package>` (e.g., `npm install mysql`) to ensure the required database driver is present in your `node_modules`.

• Error: connect ECONNREFUSED <host>:<port>

  cause The application could not establish a connection to the database server. This usually indicates an incorrect host, port, database server not running, or a firewall blocking the connection.
  fix Verify the `host` and `port` in your database configuration. Ensure the database server is running and accessible from the machine running your application. Check firewall rules on both client and server.

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

  cause The provided username or password in the database configuration is incorrect, or the user lacks necessary permissions from the specified host.
  fix Double-check your `user` and `password` credentials. Ensure the database user has permissions to connect from the `host` where your application is running, and to the `database` you specified.

Warnings

• gotcha The `oracledb` driver, included as a dependency, often requires native build tools (like `gcc` on Linux or Xcode command line tools on macOS) and the Oracle Instant Client libraries to be installed on the system where the package is used. This can lead to complex installation issues, particularly in containerized environments or on different operating systems.
  Fix: Refer to the official `oracledb` documentation for detailed installation prerequisites and troubleshooting. Ensure your build environment has the necessary tools and libraries configured before installing `database-query`.
• gotcha While `database-query` provides a unified API, the underlying connection configurations (e.g., specific options for connection pooling, SSL/TLS, timeouts) can still vary significantly between database types. Misconfigured options for a specific database can lead to connection failures or unexpected behavior.
  Fix: Always consult the official documentation for the specific database driver (e.g., `pg`, `mssql`, `mysql`) to understand database-specific configuration options. Test connections thoroughly with minimal configurations first.
• gotcha This library primarily focuses on direct query execution. While it supports parameterized queries (which protect against SQL injection), constructing queries via string concatenation without proper escaping or parameterization is a major security vulnerability.
  Fix: Always use parameterized queries (`db.query('SELECT * FROM users WHERE id = ?', [userId])`) to prevent SQL injection. Avoid directly embedding user-provided input into SQL strings.
• gotcha All supported database drivers (mysql, pg, mssql, oracledb, mongodb, clickhouse) are listed as direct dependencies in `package.json`. This means `npm install database-query` will attempt to install all of them, even if you only intend to use one. This can lead to increased install times, package bloat, and potential native module compilation failures for unused drivers (especially `oracledb`).
  Fix: If you encounter installation issues or wish to minimize dependencies, consider managing specific database drivers yourself and manually passing their connection details or client instances if the library's API allows, or investigate alternatives that allow explicit installation of only desired drivers. Otherwise, accept the bundled dependencies.

Install

• npm install database-query npm
• yarn add database-query yarn
• pnpm add database-query pnpm

Imports

• DataBase
  wrong:

  const DataBase = require('database-query');

  correct:

  import { DataBase } from 'database-query';

  The primary class for managing database connections and queries. ESM import is recommended in modern Node.js environments; CommonJS is also supported.
• IDataBaseConfig

  import { IDataBaseConfig } from 'database-query';

  TypeScript interface for configuring database connections, useful for type-safe configuration objects.
• DataBaseType

  import { DataBaseType } from 'database-query';

  TypeScript enum or type for specifying the database type (e.g., 'mysql', 'pgsql') in the configuration.

Quickstart

This quickstart demonstrates how to initialize `database-query` for a MySQL connection, execute a parameterized SELECT query, and perform an INSERT operation, including proper connection management with environment variables.

import { DataBase, DataBaseType } from 'database-query';

const runQuery = async () => {
  const config = {
    type: DataBaseType.MySql,
    host: process.env.DB_MYSQL_HOST ?? 'localhost',
    port: parseInt(process.env.DB_MYSQL_PORT ?? '3306', 10),
    user: process.env.DB_MYSQL_USER ?? 'root',
    password: process.env.DB_MYSQL_PASSWORD ?? 'password',
    database: process.env.DB_MYSQL_DATABASE ?? 'testdb',
    connectionLimit: 10 // Example for MySQL, other DBs might have different pool options
  };

  let db: DataBase | null = null;
  try {
    db = new DataBase(config);
    await db.connect();
    console.log('Successfully connected to MySQL.');

    const query = 'SELECT * FROM users WHERE age > ?';
    const params = [25];
    const results = await db.query(query, params);
    console.log('Query results:', results);

    // Example for an insert operation
    const insertQuery = 'INSERT INTO logs (message) VALUES (?)';
    const insertParams = ['User query executed successfully.'];
    const insertResult = await db.execute(insertQuery, insertParams);
    console.log('Insert result:', insertResult);

  } catch (error) {
    console.error('Database operation failed:', error);
  } finally {
    if (db) {
      await db.close();
      console.log('Database connection closed.');
    }
  }
};

runQuery();