{"id":16334,"library":"database-js-xlsx","title":"Database-js XLSX Wrapper","description":"The `database-js-xlsx` package provides a robust `database-js` compatible interface for interacting with Microsoft Excel XLSX files, acting as a crucial bridge between SQL-like queries and spreadsheet data. It is built upon the `xlsx-populate` library for efficient in-memory spreadsheet manipulation and utilizes `node-sqlparser` to interpret SQL commands. Currently stable at version 1.0.6, its release cadence is tied to its underlying dependencies and the `database-js` ecosystem, rather than a fixed schedule. A key advantage of `database-js-xlsx` is its cross-platform compatibility, a significant improvement over Windows-specific drivers within the `database-js` family, such as `database-js-adodb`. Developers should note that the library works with an in-memory copy of the spreadsheet; all changes are buffered and written back to disk only when the connection is explicitly closed. This design means any external modifications to the file during an active connection will be overwritten. The SQL capabilities are limited, supporting SELECT, UPDATE, INSERT, and DELETE statements with functional WHERE clauses, but explicitly prohibiting JOINs and currently lacking support for GROUP BY. Furthermore, LIMIT and OFFSET operations are consolidated into a single `LIMIT [offset,]number` syntax, requiring developers to adapt their pagination strategies accordingly. This package is ideal for Node.js applications needing to perform basic CRUD operations on Excel data programmatically without complex setup or platform restrictions.","status":"maintenance","version":"1.0.6","language":"javascript","source_language":"en","source_url":"https://github.com/mlaanderson/database-js-xlsx","tags":["javascript","database-js","xlsx","excel"],"install":[{"cmd":"npm install database-js-xlsx","lang":"bash","label":"npm"},{"cmd":"yarn add database-js-xlsx","lang":"bash","label":"yarn"},{"cmd":"pnpm add database-js-xlsx","lang":"bash","label":"pnpm"}],"dependencies":[{"reason":"Core dependency providing the common database interface that this package extends.","package":"database-js","optional":false},{"reason":"Underlying library used for reading, writing, and manipulating XLSX files.","package":"xlsx-populate","optional":false},{"reason":"Used for parsing SQL queries into a structured format for execution against the spreadsheet.","package":"node-sqlparser","optional":false}],"imports":[{"note":"The `Database` class is provided by the `database-js` package. The README's example contains a likely typo using 'database-js2', which does not exist on npm.","wrong":"const Database = require('database-js2');","symbol":"Database","correct":"const Database = require('database-js');"},{"note":"Importing `database-js-xlsx` registers its driver with `database-js`, enabling connection strings like 'database-js-xlsx:///file.xlsx'. This import is typically done for its side effects.","symbol":"driver registration","correct":"require('database-js-xlsx');"}],"quickstart":{"code":"const Database = require('database-js');\nconst path = require('path');\n\n// This import is crucial for the 'database-js-xlsx' driver to be available\nrequire('database-js-xlsx');\n\n(async () => {\n    let connection, statement, rows;\n    const filePath = path.join(__dirname, 'test.xlsx');\n    // For a new file, 'test.xlsx' will be created on connection.close()\n    connection = new Database(`database-js-xlsx:///${filePath}`);\n\n    try {\n        console.log(\"Connected to XLSX file. Initializing with sample data if new.\");\n\n        // Example: Create/Ensure Sheet1 has columns if it's a new file\n        // Note: This operation may implicitly create Sheet1 and its headers if not present\n        try {\n            await connection.prepareStatement(\"CREATE TABLE IF NOT EXISTS Sheet1 (Name TEXT, City TEXT, State TEXT, Age INTEGER)\");\n        } catch (e) {\n            // CREATE TABLE is not directly supported, but the first INSERT will define schema\n            console.log(\"CREATE TABLE is not supported, proceeding with inserts to define schema.\");\n        }\n\n        // Example: Insert data (if no data exists for 'Alice')\n        console.log(\"\\nInserting new data into Sheet1 (if not exists)...\");\n        statement = await connection.prepareStatement(\"INSERT OR IGNORE INTO Sheet1 (Name, City, State, Age) VALUES (?, ?, ?, ?)\");\n        await statement.query('Alice Smith', 'Anytown', 'CA', 25);\n        await statement.query('Bob Johnson', 'Otherville', 'NY', 32);\n        console.log(\"Sample data ensured.\");\n\n        // Example: Select data\n        statement = await connection.prepareStatement(\"SELECT * FROM Sheet1 WHERE State = ?\");\n        rows = await statement.query('CA');\n        console.log(\"\\nQuery Results for 'CA':\");\n        console.log(rows);\n\n        // Example: Update data\n        console.log(\"\\nUpdating Alice Smith's age...\");\n        statement = await connection.prepareStatement(\"UPDATE Sheet1 SET Age = ? WHERE Name = ?\");\n        await statement.query(26, 'Alice Smith');\n        console.log(\"Alice Smith's age updated.\");\n\n        // Verify changes\n        statement = await connection.prepareStatement(\"SELECT * FROM Sheet1 WHERE Name = ?\");\n        rows = await statement.query('Alice Smith');\n        console.log(\"\\nVerifying updated data for Alice Smith:\");\n        console.log(rows);\n\n        // Example: Delete data\n        console.log(\"\\nDeleting Bob Johnson's data...\");\n        statement = await connection.prepareStatement(\"DELETE FROM Sheet1 WHERE Name = ?\");\n        await statement.query('Bob Johnson');\n        console.log(\"Bob Johnson's data deleted.\");\n\n    } catch (error) {\n        console.error(\"An error occurred:\", error);\n    } finally {\n        if (connection) {\n            console.log(`\\nClosing connection and saving changes to '${filePath}'...`);\n            await connection.close();\n            console.log(\"Connection closed and file saved.\");\n        }\n    }\n})();","lang":"javascript","description":"Demonstrates connecting to an XLSX file, performing CRUD (Create, Read, Update, Delete) operations, and properly closing the connection using `database-js-xlsx`. It also includes handling for initial file creation and path resolution."},"warnings":[{"fix":"Change `require('database-js2')` to `require('database-js')` in your code.","message":"The README example for importing the `Database` class uses `require('database-js2')`. This is a typo, as `database-js2` does not exist on npm. The correct package to import for the `Database` class is `database-js`.","severity":"gotcha","affected_versions":">=1.0.0"},{"fix":"Ensure exclusive access to the XLSX file while a connection is active, or implement a rigorous concurrency strategy if shared access is required (which is not directly supported by this library's design).","message":"This library operates on an in-memory copy of the XLSX file. Any changes made to the physical file on disk by external processes while a connection is open will be overwritten when `connection.close()` is called, as the in-memory state is written back to the original file path.","severity":"gotcha","affected_versions":">=1.0.0"},{"fix":"Refactor SQL queries to avoid `JOIN` and `GROUP BY`. Perform data aggregation or merging of results programmatically in JavaScript/TypeScript after fetching the base data.","message":"The SQL parser has significant limitations. Specifically, `JOIN` clauses are not supported, and `GROUP BY` is not yet implemented. Complex queries requiring these features must be broken down and processed in application logic.","severity":"gotcha","affected_versions":">=1.0.0"},{"fix":"Adjust pagination queries to use `LIMIT [offset,]number` where `offset` is optional. For example, `SELECT * FROM Sheet1 LIMIT 10` for the first 10 rows, or `SELECT * FROM Sheet1 LIMIT 10, 20` for 20 rows starting after the first 10.","message":"The `LIMIT` and `OFFSET` SQL clauses are combined into a single `LIMIT [offset,]number` syntax. This deviates from standard SQL and requires careful attention when implementing pagination.","severity":"gotcha","affected_versions":">=1.0.0"}],"env_vars":null,"last_verified":"2026-04-22T00:00:00.000Z","next_check":"2026-07-21T00:00:00.000Z","problems":[{"fix":"Update your `require` statement from `require('database-js2')` to `require('database-js')`.","cause":"The official README example for `database-js-xlsx` contains a typo, instructing users to `require('database-js2')` instead of the correct `database-js` package.","error":"Error: Cannot find module 'database-js2'"},{"fix":"Refactor your query to avoid `JOIN` operations. Instead, perform multiple `SELECT` statements and merge the results in your application logic.","cause":"The underlying SQL parser (`node-sqlparser`) used by `database-js-xlsx` explicitly does not support SQL JOIN clauses for combining data from multiple tables/sheets.","error":"Error: \"JOIN\" is not allowed"},{"fix":"Retrieve the necessary data without `GROUP BY` and then perform the aggregation (e.g., summing, counting) programmatically in your JavaScript/TypeScript code.","cause":"The `database-js-xlsx` library, via `node-sqlparser`, currently lacks support for the `GROUP BY` SQL clause, preventing server-side aggregation.","error":"Error: \"GROUP BY\" is not supported"},{"fix":"Ensure that `require('database-js-xlsx');` is executed early in your application's lifecycle, preferably before any `new Database(...)` calls, to register the XLSX driver.","cause":"The `database-js-xlsx` driver has not been correctly registered with the `database-js` core library, likely because `require('database-js-xlsx')` was omitted or occurred after the connection attempt.","error":"TypeError: connection.prepareStatement is not a function"}],"ecosystem":"npm"}