Database Query Generator

Common errors

• ReferenceError: require is not defined

  cause Attempting to use `require()` in an ES Module (ESM) context (e.g., in a file with `"type": "module"` in package.json or a `.mjs` file).
  fix Change your file to use CommonJS (e.g., ensure it's a `.js` file without `"type": "module"`) or use dynamic import: `import('database-connectors').then(module => { /* use module.default */ })`.

• TypeError: connectionIdentifier.identify is not a function

  cause This usually happens if the package was imported incorrectly, or if the `database-connectors` package itself doesn't export an `identify` method directly (though the README suggests it does). Could also happen if `require()` fails silently or returns an empty object.
  fix Ensure you are using `const connectionIdentifier = require('database-connectors');` and not a named import. Verify the package is correctly installed.

• Error: Cannot find module 'node-database-connectors'

  cause The `require()` statement uses the module name `node-database-connectors` as shown in the README, but the actual npm package name is `database-connectors`.
  fix Update your `require()` statement to `const connectionIdentifier = require('database-connectors');`

Warnings

• breaking The package has not been updated since 2015 (v1.0.3), meaning it lacks support for modern JavaScript features (ESM) and may not be compatible with newer Node.js versions or database systems/standards. Security vulnerabilities are unlikely to be patched.
  Fix: Consider migrating to a actively maintained ORM or query builder library for modern Node.js environments and better security posture.
• gotcha The README examples use `require('node-database-connectors')`, but the actual npm package is `database-connectors`. Using the name `node-database-connectors` will result in a 'Cannot find module' error.
  Fix: Always use `require('database-connectors')` to import the package.
• deprecated This library is CommonJS-only (`require`). It does not support ES Modules (`import`), which is the standard module system for modern JavaScript development in Node.js and browsers.
  Fix: If integrating into an ESM project, you would need to use dynamic `import()` or configure your build system to handle CJS modules. However, given the package's age, migration is strongly advised.
• gotcha The generated SQL syntax might be outdated or not fully compatible with advanced features or specific dialects of modern database versions beyond basic MySQL. For example, `engine: 'MyISAM'` is an outdated MySQL storage engine, and the SQL generated may not optimize for newer features.
  Fix: Thoroughly test generated queries against your target database version. Manual inspection and potential adjustments of the JSON query structure might be necessary for complex or performance-critical queries.

Install

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

Imports

• default export (object with identify method)
  wrong:

  import connectionIdentifier from 'database-connectors';

  correct:

  const connectionIdentifier = require('database-connectors');

  This package is CommonJS only. The README incorrectly references `require('node-database-connectors')` while the npm package is `database-connectors`.
• identify
  wrong:

  const { identify } = require('database-connectors');

  correct:

  const connectionIdentifier = require('database-connectors');
  const objConnection = connectionIdentifier.identify(sampleConfig);

  The `identify` method is part of the default exported object, not a named export. It takes a configuration object to determine the database type.
• prepareQuery

  const connectionIdentifier = require('database-connectors');
  const objConnection = connectionIdentifier.identify(sampleConfig);
  const query = objConnection.prepareQuery(jsonQuery);

  The `prepareQuery` method is invoked on the object returned by `identify`, taking a JSON object representing the desired SQL query.

Quickstart

Demonstrates how to initialize a database connector configuration and generate a SQL SELECT query from a JSON object using the library's `identify` and `prepareQuery` methods.

const connectionIdentifier = require('database-connectors');

// Sample database configuration (MySQL assumed based on README)
const sampleConfig = {
  type: "database",
  engine: 'MyISAM',
  databaseType: 'mysql',
  database: 'test_db',
  host: "localhost",
  port: "3306",
  user: "root",
  password: process.env.DB_PASSWORD ?? '', // Use environment variable for password
  cacheResponse: false
};

// Sample JSON query for a SELECT statement
const jsonQuery = {
  table: "tbl_SampleMaster",
  alias: "SM",
  select: [
    { field: 'pk_tableID', alias: 'pk' },
    { field: 'refNumber' }
  ],
  sortby: [{ field: 'refNumber' }],
  filter: {
    AND: [{ field: 'pk_id', operator: 'EQ', value: '1' }]
  }
};

try {
  // Identify the connection type and get the connector object
  const objConnection = connectionIdentifier.identify(sampleConfig);
  
  // Prepare the SQL query from the JSON object
  const query = objConnection.prepareQuery(jsonQuery);

  console.log('Generated SQL Query:');
  console.log(query);
} catch (error) {
  console.error('Error generating query:', error.message);
}

// Example output for the above:
// SELECT ``.`pk_tableID` as `pk`,``.`refNumber`
// FROM `tbl_SampleMaster` as SM
// WHERE (``.`pk_id` = '1')
// ORDER BY `refNumber` ASC;