database-js CSV Driver

Common errors

• Error: Driver not found for connection string: csv:///test.csv?headers=true&overwriteOnExecute=true

  cause The `database-js-csv` driver was not loaded or registered with the core `database-js` library.
  fix Add `require('database-js-csv');` to your application's entry point to ensure the driver is initialized, typically after requiring `database-js` itself.

• TypeError: Cannot read properties of undefined (reading 'Connection')

  cause You are trying to destructure `Connection` from a module that doesn't export it, or the `database-js` package was not installed or imported correctly.
  fix Ensure `npm install database-js` is run and that you are importing `Connection` from `database-js` using `const { Connection } = require('database-js');` or `const Connection = require('database-js').Connection;`.

• Error: CSV parsing error: Expected a header row but 'headers=true' was specified and the file is empty or malformed.

  cause The CSV file specified by the connection string either does not exist, is empty, or its format does not match the `headers=true` expectation.
  fix Verify the CSV file exists at the specified path, contains data, and has a header row if `headers=true` is used in the connection string. Check for correct delimiters or line endings.

Warnings

• gotcha The example in the package's README uses `require('database-js2')`. This appears to be a typo or an outdated reference; the correct dependency for the core API is `database-js`. Always use `require('database-js')`.
  Fix: Change `require('database-js2')` to `require('database-js')`.
• gotcha The `database-js-csv` package must be explicitly `require()`d to register its driver with the `database-js` core, even if you don't assign its exports to a variable. Without this, `database-js` will not recognize the `csv:///` connection string.
  Fix: Include `require('database-js-csv');` at the beginning of your script, alongside `require('database-js');`.
• gotcha The underlying `jl-sql-api` package, which provides the SQL parsing, has not been actively maintained for several years (last update ~2 years ago). This may limit support for advanced SQL features or lead to unpatched bugs in the SQL parsing logic.
  Fix: Review `jl-sql-api`'s documentation and open issues for known limitations. Consider alternative CSV query libraries if complex SQL features are required.
• gotcha When specifying file paths in the connection string (e.g., `csv:///path/to/file.csv`), ensure they are correctly escaped and formatted for a URL. Relative paths might behave differently depending on the execution context and operating system.
  Fix: Use absolute paths or carefully construct relative paths. Ensure special characters in paths are properly URL-encoded if not handled automatically by `database-js`.
• gotcha All data read from CSV files is initially treated as strings. While `jl-sql-api` has some type detection capabilities (e.g., `detectNumbers`, `detectDates`), explicit type casting within SQL queries or post-processing in JavaScript might be necessary for accurate data handling.
  Fix: Leverage connection string options like `?detectNumbers=true` or perform JavaScript type conversions after querying. Use `CAST()` functions within your SQL if supported by `jl-sql-api`.

Install

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

Imports

• Connection
  wrong:

  import { Connection } from 'database-js'

  correct:

  const Connection = require('database-js').Connection;

  The core database-js library primarily uses CommonJS `require` syntax. For CSV access, ensure `database-js-csv` is also required for its side effects (driver registration).
• Database
  wrong:

  import { Database } from 'database-js'

  correct:

  const { Database } = require('database-js');

  The `Database` constructor is the primary entry point for creating connections, invoked after `database-js-csv` has registered its driver.
• Side-effect import for driver registration
  wrong:

  import 'database-js-csv';

  correct:

  require('database-js-csv');

  The `database-js-csv` package must be explicitly `require()`d (even without assigning to a variable) to register itself as a driver with `database-js`.

Quickstart

Demonstrates connecting to a CSV file, performing a SELECT query with a prepared statement, inserting data, and then querying all data, including necessary setup and cleanup.

const fs = require('fs');
const { Database } = require('database-js');

// IMPORTANT: Require the driver to register it with database-js
require('database-js-csv');

const csvContent = `user_name,email,role\nsecret_user,secret@example.com,admin\nnot_so_secret_user,notsosecret@example.com,user\n`;
const csvFilePath = './test.csv';

// Create a dummy CSV file for the example
fs.writeFileSync(csvFilePath, csvContent);

(async () => {
    let connection;
    try {
        // The 'csv:///' prefix activates the database-js-csv driver
        connection = new Database(`csv://${csvFilePath}?headers=true&overwriteOnExecute=true`);
        
        let statement = await connection.prepareStatement("SELECT user_name, email FROM CSV WHERE user_name = ?");
        let rows = await statement.query('not_so_secret_user');
        console.log('Query result for not_so_secret_user:', rows);

        statement = await connection.prepareStatement("INSERT INTO CSV (user_name, email, role) VALUES (?, ?, ?)");
        await statement.execute('new_user', 'new@example.com', 'guest');
        console.log('Inserted new user.');

        // Query all data to see the inserted user
        statement = await connection.prepareStatement("SELECT * FROM CSV");
        rows = await statement.query();
        console.log('All data after insert:', rows);

    } catch (error) {
        console.error('Error:', error);
    } finally {
        if (connection) {
            await connection.close();
        }
        // Clean up the dummy CSV file
        fs.unlinkSync(csvFilePath);
        console.log('Cleaned up test.csv');
    }
})();