database-js-sqlparser: SQL Parser for database-js Drivers

Common errors

• TypeError: this.load is not a function

  cause An extending driver class failed to implement one of the required abstract methods (e.g., `load`, `store`, `remove`, `create`, `drop`, `ready`, `close`).
  fix Implement all seven abstract methods (`ready`, `close`, `load`, `store`, `remove`, `create`, `drop`) in your custom driver class that extends `SQLParser`.

• Error: SQLParseError: Syntax error near 'UNSUPPORTED_KEYWORD'

  cause The SQL query contains syntax not supported by `database-js-sqlparser` (e.g., `FULL JOIN`, unsupported functions, or dialect-specific clauses).
  fix Review the supported SQL syntax in the `database-js-sqlparser` documentation and rewrite the query to use only supported features. Debug by simplifying the query to isolate the unsupported part.

• Promise { <pending> }

  cause Operations on the `SQLParser` or its extending driver methods are asynchronous, but the calling code did not `await` their results or handle the returned Promises correctly.
  fix Always use `await` when calling asynchronous methods (like `ready()`, `load()`, `store()`, etc.) on your `SQLParser`-based driver, or chain `.then()` and `.catch()` to handle the Promise resolution.

Warnings

• gotcha This package is an abstract SQL parser and *does not provide any data storage or database connectivity itself*. It must be extended by a custom driver that implements the underlying storage mechanism.
  Fix: Always extend the `SQLParser` class and implement all seven required asynchronous methods (`ready`, `close`, `load`, `store`, `remove`, `create`, `drop`) to provide concrete storage logic.
• gotcha The SQL parser has limitations: it does not support `FULL JOIN` or `OUTER JOIN` clauses.
  Fix: Ensure that SQL queries submitted to drivers built on `database-js-sqlparser` only use `INNER JOIN`, `LEFT JOIN`, or `RIGHT JOIN`.
• gotcha When using aggregate functions like `SUM`, providing non-numeric columns may not throw an error but will result in an `undefined` return value for that aggregate.
  Fix: Always ensure that columns passed to aggregate functions like `SUM` contain valid numeric data types to avoid unexpected results.
• gotcha Column types `CHARACTER(n)` and `VARCHAR(n)` handle length constraints differently. `CHARACTER(n)` will pad or truncate to `n` length, while `VARCHAR(n)` will only truncate to `n` length without padding.
  Fix: Be mindful of these differences when defining table schemas and inserting string data to avoid unexpected padding or data loss.

Install

• npm install database-js-sqlparser npm
• yarn add database-js-sqlparser yarn
• pnpm add database-js-sqlparser pnpm

Imports

• SQLParser
  wrong:

  import { SQLParser } from 'database-js-sqlparser';

  correct:

  import SQLParser from 'database-js-sqlparser';

  The library primarily uses a CommonJS `module.exports = SQLParser;` pattern. For ESM, it is typically consumed as a default import for compatibility.
• SQLParser (CommonJS)

  const SQLParser = require('database-js-sqlparser');

  This is the native CommonJS export pattern for the `SQLParser` class.
• extending SQLParser

  class MyDriver extends SQLParser { /* ... */ }

  The primary usage pattern is to extend the `SQLParser` class to implement custom storage logic.

Quickstart

This quickstart demonstrates how to extend the `SQLParser` class to create a custom in-memory `database-js` driver. It implements the required `ready`, `close`, `load`, `store`, `remove`, `create`, and `drop` methods, simulating basic table and row management in RAM. It shows how the abstract methods translate SQL operations to direct storage interactions.

import SQLParser from 'database-js-sqlparser';

class InMemoryDriver extends SQLParser {
    constructor() {
        super();
        this.tables = {}; // Simple in-memory storage
        this.lastId = 0;
    }

    ready() {
        console.log('InMemoryDriver: Ready.');
        return Promise.resolve(true);
    }

    close() {
        console.log('InMemoryDriver: Closing...');
        this.tables = {};
        this.lastId = 0;
        return Promise.resolve(true);
    }

    async load(tableName) {
        console.log(`InMemoryDriver: Loading from table '${tableName}'`);
        return Promise.resolve(Object.values(this.tables[tableName] || {}));
    }

    async store(tableName, index, row) {
        console.log(`InMemoryDriver: Storing in table '${tableName}', index: ${index}`);
        if (!this.tables[tableName]) {
            throw new Error(`Table '${tableName}' does not exist.`);
        }

        let id = index;
        if (id === null || id === undefined) {
            this.lastId++;
            id = this.lastId;
            row.id = id; // Assuming 'id' is a common primary key column
        }

        this.tables[tableName][id] = { ...row, id };
        return Promise.resolve(id);
    }

    async remove(tableName, index) {
        console.log(`InMemoryDriver: Removing from table '${tableName}', index: ${index}`);
        if (!this.tables[tableName] || !this.tables[tableName][index]) {
            throw new Error(`Row with index '${index}' not found in table '${tableName}'.`);
        }
        delete this.tables[tableName][index];
        return Promise.resolve(index);
    }

    async create(tableName, definition) {
        console.log(`InMemoryDriver: Creating table '${tableName}' with definition:`, definition);
        if (this.tables[tableName]) {
            throw new Error(`Table '${tableName}' already exists.`);
        }
        this.tables[tableName] = {}; // Initialize with an empty object for rows
        // In a real scenario, 'definition' would be used to validate column types.
        return Promise.resolve(true);
    }

    async drop(tableName) {
        console.log(`InMemoryDriver: Dropping table '${tableName}'`);
        if (!this.tables[tableName]) {
            throw new Error(`Table '${tableName}' does not exist.`);
        }
        delete this.tables[tableName];
        return Promise.resolve(true);
    }
}

async function runExample() {
    const driver = new InMemoryDriver();
    await driver.ready();

    // Simulate SQL operations by calling internal methods (in a real scenario, database-js would do this)
    // This is for demonstration of implementing the driver methods
    await driver.create('users', [
        { name: 'id', type: 'INTEGER' },
        { name: 'name', type: 'VARCHAR(255)' },
        { name: 'age', type: 'INTEGER' }
    ]);

    let userId1 = await driver.store('users', null, { name: 'Alice', age: 30 });
    let userId2 = await driver.store('users', null, { name: 'Bob', age: 25 });

    console.log('All users after inserts:', await driver.load('users'));

    await driver.store('users', userId1, { name: 'Alicia', age: 31, id: userId1 }); // Update
    console.log('Users after update:', await driver.load('users'));

    await driver.remove('users', userId2);
    console.log('Users after delete:', await driver.load('users'));

    await driver.drop('users');
    console.log('Driver closing...');
    await driver.close();
}

runExample().catch(console.error);