node-database-connectors: JSON to SQL Query Builder

Common errors

• TypeError: connectionIdentifier.identify is not a function

  cause Attempting to call `identify` on an incorrectly imported or undefined `connectionIdentifier` module, often due to incorrect CommonJS `require` or ESM `import` syntax.
  fix Ensure `node-database-connectors` is correctly installed and imported as a default export using `const connectionIdentifier = require('node-database-connectors');` for CommonJS or `import connectionIdentifier from 'node-database-connectors';` for ESM.

• Error: Invalid JSON query structure: 'table' is a required field.

  cause The `jsonQuery` object provided to `prepareQuery` is missing a mandatory top-level key like `table`, which is essential for almost all SQL operations.
  fix Review your `jsonQuery` object and ensure all necessary fields, such as `table`, `select`, `insert`, `update`, or `filter`, are present and correctly structured according to the library's documentation for the intended query type.

• Error: Unsupported database type: 'sqlserver'

  cause The `databaseType` specified in your `sampleConfig` is not recognized or supported by the `node-database-connectors` library for generating SQL queries.
  fix Check the `databaseType` field in your configuration object. Use one of the officially supported types (e.g., 'mysql', 'postgres') as documented by the library.

Warnings

• gotcha The library generates SQL strings from JSON input. While it provides abstraction, proper input validation and sanitization are crucial. Directly inserting unvalidated user-supplied values into the `value` fields of `jsonQuery` could expose your application to SQL injection vulnerabilities, as the library's internal escaping mechanisms are not explicitly documented. Always sanitize or validate external input thoroughly.
  Fix: Ensure all user-supplied input used within `jsonQuery` (especially in `value` fields) is either strictly validated, type-checked, or explicitly escaped before being passed to the library. Consider using a dedicated input validation library or parameterized queries at the database driver level.
• gotcha The library's support for specific SQL features, functions, and syntax may vary across different `databaseType` engines (e.g., 'mysql', 'postgres'). Complex or highly database-specific SQL constructs might not be fully supported, or they might generate unexpected or inefficient SQL. Always test generated queries thoroughly for your target database environment.
  Fix: Consult the library's documentation or source code for a definitive list of supported `databaseType` values and their respective query generation capabilities. Verify the output of `prepareQuery` in your specific database environment to ensure correctness and performance.
• gotcha The library strictly expects `jsonQuery` objects to adhere to a predefined structure for each SQL operation type (SELECT, INSERT, UPDATE, DELETE). Incorrectly formatted JSON objects, missing required fields, or misspellings will lead to runtime errors during query generation.
  Fix: Refer to the comprehensive examples in the package's README for the correct JSON structure for each query type. Implement schema validation for `jsonQuery` objects if they are dynamically constructed from external sources to catch errors early.

Install

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

Imports

• connectionIdentifier
  wrong:

  import { connectionIdentifier } from 'node-database-connectors';

  correct:

  import connectionIdentifier from 'node-database-connectors';

  The library primarily uses a default export for its main interface, which exposes the `identify` method. Attempting to destructure it as a named import will fail.
• connectionIdentifier

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

  This is the standard CommonJS `require` pattern as demonstrated in the package's README and is suitable for Node.js environments.
• objConnection.prepareQuery

  const objConnection = connectionIdentifier.identify(sampleConfig);
  const query = objConnection.prepareQuery(jsonQuery);

  The `identify` method on the default export returns a connection object, which then provides the `prepareQuery` method to convert JSON into SQL.

Quickstart

Demonstrates how to configure a database connection, define a JSON-based SELECT query, and generate the corresponding SQL string using `node-database-connectors`.

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

// Sample database configuration
const sampleConfig = {
  type: "database",
  engine: 'MyISAM',
  databaseType: 'mysql',
  database: 'test_db',
  host: "localhost",
  port: "3306",
  user: "root",
  password: process.env.DB_PASSWORD ?? '', // Use environment variables for sensitive data
  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 {
  const objConnection = connectionIdentifier.identify(sampleConfig);
  const generatedQuery = objConnection.prepareQuery(jsonQuery);
  console.log('Generated SQL Query:\n', generatedQuery);
} catch (error) {
  console.error('Error generating query:', error.message);
}

// Expected Output (similar to README example):
// SELECT ``.`pk_tableID` as `pk`,``.`refNumber`
// FROM `tbl_SampleMaster` as SM
// WHERE (``.`pk_id` = '1')
// ORDER BY `refNumber` ASC;