SQLite Database Manager (javascript, written by Codex)

Codex wrote this javascript project from the task below. It installed and ran on a clean Ubuntu 22.04 machine as written.

Task given to the agent:

TASK: SQLite Database Manager

Write a program that provides a command-line interface for managing SQLite databases, supporting table creation, CRUD operations, schema inspection, data import/export, and query execution.

FUNCTIONAL REQUIREMENTS:
- Accept a database file path as a command-line argument (create it if it does not exist)
- Support creating tables with typed columns (TEXT, INTEGER, REAL, BLOB) and constraints (PRIMARY KEY, NOT NULL, UNIQUE, DEFAULT)
- Support INSERT, SELECT, UPDATE, and DELETE operations via command-line subcommands
- Support executing arbitrary SQL queries via a --query flag
- Implement schema inspection: list all tables, show column details for a given table, show row counts
- Import data from a CSV file into a specified table via --import flag, auto-detecting column types
- Export a table or query result to CSV via --export flag
- Support basic transactions: --begin, --commit, --rollback flags for grouping operations
- Print query results as formatted tables to console with column headers and aligned values
- If no database path is given, create a sample database with at least three related tables (e.g., users, orders, products), populate with sample data, and run demonstration queries showing joins, aggregations, and filtering
- Handle database errors gracefully with clear messages distinguishing connection, schema, constraint, and query errors

Create a complete JavaScript project for a clean Ubuntu 22.04 machine with only Node.js 20+ (LTS) installed. Include:
- Source code
- package.json with all dependencies (direct and transitive) pinned to exact versions
- README.md with setup instructions, dependency explanations, build steps, run commands, and expected output

[]

{
  "name": "sqlite-database-manager",
  "version": "1.0.0",
  "lockfileVersion": 3,
  "requires": true,
  "packages": {
    "": {
      "name": "sqlite-database-manager",
      "version": "1.0.0",
      "dependencies": {
        "sql.js": "1.13.0"
      },
      "engines": {
        "node": ">=20.0.0"
      }
    },
    "node_modules/sql.js": {
      "version": "1.13.0"
    }
  }
}

{
  "name": "sqlite-database-manager",
  "version": "1.0.0",
  "description": "CLI SQLite database manager with CRUD, schema inspection, import/export, and query execution.",
  "type": "module",
  "main": "src/index.js",
  "scripts": {
    "start": "node src/index.js"
  },
  "engines": {
    "node": ">=20.0.0"
  },
  "dependencies": {
    "sql.js": "1.13.0"
  }
}

# SQLite Database Manager (JavaScript)

Command-line SQLite manager implemented for Node.js 20+.

## Requirements

- Ubuntu 22.04
- Node.js 20+ (LTS)

## Dependencies

- Direct:
  - `sql.js@1.13.0`
- Transitive:
  - none

Dependencies are pinned exactly in `package.json` and `package-lock.json`.

## Setup

```bash
npm install
```

## Run

No DB path: creates a sample database and runs demo join/aggregation/filter queries.

```bash
node src/index.js
```

Use a specific database:

```bash
node src/index.js ./app.db --list-tables
```

## Commands

Create table:

```bash
node src/index.js ./app.db --create-table users --columns "id:INTEGER PRIMARY KEY,name:TEXT NOT NULL,email:TEXT UNIQUE"
```

Insert:

```bash
node src/index.js ./app.db --insert users --values "id=1,name=Alice,email=alice@example.com"
```

Select:

```bash
node src/index.js ./app.db --select users --columns "id,name,email" --where "id >= 1" --limit 10
```

Update:

```bash
node src/index.js ./app.db --update users --set "name=Alice Updated" --where "id = 1"
```

Delete:

```bash
node src/index.js ./app.db --delete users --where "id = 1"
```

Arbitrary query:

```bash
node src/index.js ./app.db --query "SELECT name FROM sqlite_master WHERE type='table'"
```

Schema inspection:

```bash
node src/index.js ./app.db --list-tables
node src/index.js ./app.db --describe users
node src/index.js ./app.db --row-count users
```

Import CSV:

```bash
node src/index.js ./app.db --import ./users.csv --table imported_users
```

Export to CSV:

```bash
node src/index.js ./app.db --export ./users_out.csv --table users
node src/index.js ./app.db --export ./query_out.csv --query "SELECT * FROM users WHERE id > 5"
```

Transactions:

```bash
node src/index.js ./app.db --begin --insert users --values "id=3,name=Carol,email=carol@example.com" --commit
node src/index.js ./app.db --begin --delete users --where "id=3" --rollback
```

## Expected Output

- Formatted query tables with aligned columns
- Clear error messages by category (`Connection Error`, `Schema Error`, `Constraint Error`, `Query Error`)
- Database file created automatically if missing

import fs from "fs";
import path from "path";
import { fileURLToPath } from "url";
import initSqlJs from "sql.js";

const LEVELS = {
  CONNECTION: "Connection Error",
  SCHEMA: "Schema Error",
  CONSTRAINT: "Constraint Error",
  QUERY: "Query Error",
};

function parseArgs(argv) {
  const options = {};
  const positional = [];
  for (let i = 0; i < argv.length; i += 1) {
    const token = argv[i];
    if (token.startsWith("--")) {
      const key = token.slice(2);
      const next = argv[i + 1];
      if (next && !next.startsWith("--")) {
        options[key] = next;
        i += 1;
      } else {
        options[key] = true;
      }
    } else {
      positional.push(token);
    }
  }
  return { options, positional };
}

function escapeIdent(name) {
  return `"${String(name).replace(/"/g, "\"\"")}"`;
}

function classifyError(message) {
  const lower = message.toLowerCase();
  if (
    lower.includes("no such table") ||
    lower.includes("no such column") ||
    lower.includes("already exists")
  ) {
    return LEVELS.SCHEMA;
  }
  if (
    lower.includes("constraint failed") ||
    lower.includes("not null") ||
    lower.includes("unique")
  ) {
    return LEVELS.CONSTRAINT;
  }
  if (lower.includes("syntax error") || lower.includes("near")) {
    return LEVELS.QUERY;
  }
  return LEVELS.CONNECTION;
}

function parseCsvLine(line) {
  const values = [];
  let current = "";
  let inQuotes = false;
  for (let i = 0; i < line.length; i += 1) {
    const ch = line[i];
    if (ch === '"') {
      if (inQuotes && line[i + 1] === '"') {
        current += '"';
        i += 1;
      } else {
        inQuotes = !inQuotes;
      }
    } else if (ch === "," && !inQuotes) {
      values.push(current);
      current = "";
    } else {
      current += ch;
    }
  }
  values.push(current);
  return values;
}

function parseCsv(content) {
  const lines = content.replace(/\r\n/g, "\n").replace(/\r/g, "\n").split("\n").filter((line) => line.length > 0);
  if (lines.length === 0) return [];
  return lines.map((line) => parseCsvLine(line));
}

function toCsv(rows) {
  return rows
    .map((row) =>
      row
        .map((value) => {
          const text = value == null ? "" : String(value);
          if (/[",\n]/.test(text)) return `"${text.replace(/"/g, "\"\"")}"`;
          return text;
        })
        .join(",")
    )
    .join("\n");
}

function parseValue(raw) {
  if (raw == null) return null;
  const trimmed = String(raw).trim();
  if (trimmed.length === 0) return "";
  if (/^null$/i.test(trimmed)) return null;
  if (/^(true|false)$/i.test(trimmed)) return /^true$/i.test(trimmed) ? 1 : 0;
  if (/^-?\d+$/.test(trimmed)) return Number.parseInt(trimmed, 10);
  if (/^-?\d+\.\d+$/.test(trimmed)) return Number.parseFloat(trimmed);
  if (
    (trimmed.startsWith('"') && trimmed.endsWith('"')) ||
    (trimmed.startsWith("'") && trimmed.endsWith("'"))
  ) {
    return trimmed.slice(1, -1);
  }
  return trimmed;
}

function parseKeyValueSpec(spec) {
  const entries = [];
  const parts = parseCsvLine(spec);
  for (const part of parts) {
    const idx = part.indexOf("=");
    if (idx <= 0) {
      throw new Error(`Invalid key=value pair: ${part}`);
    }
    const key = part.slice(0, idx).trim();
    const value = part.slice(idx + 1).trim();
    entries.push([key, parseValue(value)]);
  }
  return entries;
}

function parseColumnSpec(spec) {
  const defs = [];
  const parts = parseCsvLine(spec);
  for (const part of parts) {
    const trimmed = part.trim();
    if (!trimmed) continue;
    const colon = trimmed.indexOf(":");
    if (colon <= 0) {
      throw new Error(`Invalid column definition: ${trimmed}`);
    }
    const name = trimmed.slice(0, colon).trim();
    const rest = trimmed.slice(colon + 1).trim();
    if (!name || !rest) {
      throw new Error(`Invalid column definition: ${trimmed}`);
    }
    defs.push({ name, definition: rest });
  }
  return defs;
}

function printTable(columns, rows) {
  if (!columns || columns.length === 0) {
    console.log("(no columns)");
    return;
  }
  const widths = columns.map((col) => col.length);
  for (const row of rows) {
    for (let i = 0; i < columns.length; i += 1) {
      const text = row[i] == null ? "NULL" : String(row[i]);
      if (text.length > widths[i]) widths[i] = text.length;
    }
  }
  const pad = (text, width) => `${text}${" ".repeat(Math.max(0, width - text.length))}`;
  const sep = `+-${widths.map((w) => "-".repeat(w)).join("-+-")}-+`;
  console.log(sep);
  console.log(`| ${columns.map((c, i) => pad(c, widths[i])).join(" | ")} |`);
  console.log(sep);
  for (const row of rows) {
    const textRow = row.map((v) => (v == null ? "NULL" : String(v)));
    console.log(`| ${textRow.map((v, i) => pad(v, widths[i])).join(" | ")} |`);
  }
  console.log(sep);
  console.log(`${rows.length} row(s)`);
}

function resultSetFromExec(execResult) {
  if (!execResult || execResult.length === 0) return { columns: [], rows: [] };
  return { columns: execResult[0].columns, rows: execResult[0].values };
}

function detectType(values) {
  const nonEmpty = values.filter((v) => v != null && String(v).trim() !== "");
  if (nonEmpty.length === 0) return "TEXT";
  if (nonEmpty.every((v) => /^-?\d+$/.test(String(v).trim()))) return "INTEGER";
  if (nonEmpty.every((v) => /^-?\d+(?:\.\d+)?$/.test(String(v).trim()))) return "REAL";
  if (nonEmpty.every((v) => /^0x[0-9a-f]+$/i.test(String(v).trim()))) return "BLOB";
  return "TEXT";
}

function tableExists(db, tableName) {
  const sql = "SELECT name FROM sqlite_master WHERE type='table' AND name = ?;";
  const stmt = db.prepare(sql);
  stmt.bind([tableName]);
  const exists = stmt.step();
  stmt.free();
  return exists;
}

function saveDb(db, dbPath) {
  const exported = db.export();
  fs.writeFileSync(dbPath, Buffer.from(exported));
}

function runQueryAndPrint(db, sql) {
  const result = resultSetFromExec(db.exec(sql));
  printTable(result.columns, result.rows);
  return result;
}

function inspectSchema(db, options) {
  if (options["list-tables"]) {
    const tables = resultSetFromExec(
      db.exec("SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%' ORDER BY name;")
    );
    if (tables.rows.length === 0) {
      console.log("No user tables found.");
      return;
    }
    const rows = [];
    for (const row of tables.rows) {
      const table = row[0];
      const countRes = resultSetFromExec(db.exec(`SELECT COUNT(*) AS count FROM ${escapeIdent(table)};`));
      rows.push([table, countRes.rows[0][0]]);
    }
    printTable(["table", "row_count"], rows);
  }

  if (options.describe) {
    const table = options.describe;
    const details = resultSetFromExec(db.exec(`PRAGMA table_info(${escapeIdent(table)});`));
    printTable(details.columns, details.rows);
  }

  if (options["row-count"]) {
    const table = options["row-count"];
    const result = resultSetFromExec(db.exec(`SELECT COUNT(*) AS row_count FROM ${escapeIdent(table)};`));
    printTable(result.columns, result.rows);
  }
}

function doCreateTable(db, options) {
  if (!options["create-table"]) return false;
  const tableName = options["create-table"];
  const columnSpec = options.columns;
  if (!columnSpec) {
    throw new Error("--create-table requires --columns");
  }
  const defs = parseColumnSpec(columnSpec);
  const sql = `CREATE TABLE IF NOT EXISTS ${escapeIdent(tableName)} (${defs
    .map((def) => `${escapeIdent(def.name)} ${def.definition}`)
    .join(", ")});`;
  db.exec(sql);
  console.log(`Created/verified table: ${tableName}`);
  return true;
}

function doInsert(db, options) {
  if (!options.insert) return false;
  if (!options.values) throw new Error("--insert requires --values");
  const table = options.insert;
  const entries = parseKeyValueSpec(options.values);
  const columns = entries.map(([k]) => k);
  const values = entries.map(([, v]) => v);
  const placeholders = columns.map(() => "?").join(", ");
  const sql = `INSERT INTO ${escapeIdent(table)} (${columns.map(escapeIdent).join(", ")}) VALUES (${placeholders});`;
  const stmt = db.prepare(sql);
  stmt.run(values);
  stmt.free();
  console.log(`Inserted 1 row into ${table}`);
  return true;
}

function doSelect(db, options) {
  if (!options.select) return false;
  const table = options.select;
  const columns = options.columns ? options.columns.split(",").map((c) => c.trim()).filter(Boolean) : ["*"];
  const where = options.where ? ` WHERE ${options.where}` : "";
  const limit = options.limit ? ` LIMIT ${Number.parseInt(options.limit, 10) || 0}` : "";
  const sql = `SELECT ${columns.join(", ")} FROM ${escapeIdent(table)}${where}${limit};`;
  runQueryAndPrint(db, sql);
  return true;
}

function doUpdate(db, options) {
  if (!options.update) return false;
  if (!options.set) throw new Error("--update requires --set");
  const table = options.update;
  const entries = parseKeyValueSpec(options.set);
  const assignments = entries.map(([k]) => `${escapeIdent(k)} = ?`).join(", ");
  const values = entries.map(([, v]) => v);
  const where = options.where ? ` WHERE ${options.where}` : "";
  const sql = `UPDATE ${escapeIdent(table)} SET ${assignments}${where};`;
  const stmt = db.prepare(sql);
  stmt.run(values);
  stmt.free();
  const changed = resultSetFromExec(db.exec("SELECT changes() AS changed;")).rows[0][0];
  console.log(`Updated ${changed} row(s) in ${table}`);
  return true;
}

function doDelete(db, options) {
  if (!options.delete) return false;
  const table = options.delete;
  const where = options.where ? ` WHERE ${options.where}` : "";
  const sql = `DELETE FROM ${escapeIdent(table)}${where};`;
  db.exec(sql);
  const changed = resultSetFromExec(db.exec("SELECT changes() AS changed;")).rows[0][0];
  console.log(`Deleted ${changed} row(s) from ${table}`);
  return true;
}

function doImport(db, options) {
  if (!options.import) return false;
  if (!options.table) throw new Error("--import requires --table");
  const csvPath = path.resolve(options.import);
  const table = options.table;
  if (!fs.existsSync(csvPath)) throw new Error(`CSV file not found: ${csvPath}`);

  const csv = parseCsv(fs.readFileSync(csvPath, "utf8"));
  if (csv.length === 0) throw new Error("CSV is empty.");
  const headers = csv[0].map((h) => h.trim());
  const dataRows = csv.slice(1);

  if (!tableExists(db, table)) {
    const detected = headers.map((header, idx) => {
      const values = dataRows.map((row) => row[idx]);
      return { header, type: detectType(values) };
    });
    const createSql = `CREATE TABLE ${escapeIdent(table)} (${detected
      .map((c) => `${escapeIdent(c.header)} ${c.type}`)
      .join(", ")});`;
    db.exec(createSql);
  }

  if (dataRows.length > 0) {
    const placeholders = headers.map(() => "?").join(", ");
    const sql = `INSERT INTO ${escapeIdent(table)} (${headers.map(escapeIdent).join(", ")}) VALUES (${placeholders});`;
    const stmt = db.prepare(sql);
    for (const row of dataRows) {
      const values = headers.map((_, idx) => parseValue(row[idx] ?? ""));
      stmt.run(values);
    }
    stmt.free();
  }
  console.log(`Imported ${dataRows.length} row(s) into ${table}`);
  return true;
}

function doExport(db, options) {
  if (!options.export) return false;
  const outPath = path.resolve(options.export);
  let result;
  if (options.query) {
    result = resultSetFromExec(db.exec(options.query));
  } else if (options.table) {
    result = resultSetFromExec(db.exec(`SELECT * FROM ${escapeIdent(options.table)};`));
  } else {
    throw new Error("--export requires either --table or --query");
  }
  const rows = [result.columns, ...result.rows];
  fs.writeFileSync(outPath, toCsv(rows), "utf8");
  console.log(`Exported ${result.rows.length} row(s) to ${outPath}`);
  return true;
}

function buildSampleDb(db) {
  db.exec(`
    PRAGMA foreign_keys = ON;
    CREATE TABLE IF NOT EXISTS users (
      id INTEGER PRIMARY KEY,
      name TEXT NOT NULL,
      email TEXT UNIQUE NOT NULL
    );
    CREATE TABLE IF NOT EXISTS products (
      id INTEGER PRIMARY KEY,
      name TEXT NOT NULL,
      price REAL NOT NULL CHECK(price >= 0)
    );
    CREATE TABLE IF NOT EXISTS orders (
      id INTEGER PRIMARY KEY,
      user_id INTEGER NOT NULL,
      product_id INTEGER NOT NULL,
      quantity INTEGER NOT NULL DEFAULT 1,
      created_at TEXT NOT NULL,
      FOREIGN KEY(user_id) REFERENCES users(id),
      FOREIGN KEY(product_id) REFERENCES products(id)
    );
    DELETE FROM orders;
    DELETE FROM users;
    DELETE FROM products;
  `);

  const userStmt = db.prepare("INSERT INTO users (id, name, email) VALUES (?, ?, ?);");
  const users = [
    [1, "Alice", "alice@example.com"],
    [2, "Bob", "bob@example.com"],
    [3, "Carol", "carol@example.com"],
  ];
  for (const u of users) userStmt.run(u);
  userStmt.free();

  const productStmt = db.prepare("INSERT INTO products (id, name, price) VALUES (?, ?, ?);");
  const products = [
    [1, "Keyboard", 79.99],
    [2, "Monitor", 249.5],
    [3, "Mouse", 29.95],
  ];
  for (const p of products) productStmt.run(p);
  productStmt.free();

  const orderStmt = db.prepare(
    "INSERT INTO orders (id, user_id, product_id, quantity, created_at) VALUES (?, ?, ?, ?, ?);"
  );
  const orders = [
    [1, 1, 1, 2, "2026-02-01T10:00:00Z"],
    [2, 1, 2, 1, "2026-02-02T11:30:00Z"],
    [3, 2, 3, 3, "2026-02-03T15:45:00Z"],
    [4, 3, 2, 1, "2026-02-04T17:15:00Z"],
  ];
  for (const o of orders) orderStmt.run(o);
  orderStmt.free();
}

function runDemo(db) {
  console.log("No database path provided. Creating sample database and running demo queries.\n");
  buildSampleDb(db);

  console.log("Demo 1: Join users, orders, products");
  runQueryAndPrint(
    db,
    `SELECT o.id AS order_id, u.name AS user_name, p.name AS product_name, o.quantity, p.price,
            ROUND(o.quantity * p.price, 2) AS total
     FROM orders o
     JOIN users u ON u.id = o.user_id
     JOIN products p ON p.id = o.product_id
     ORDER BY o.id;`
  );

  console.log("\nDemo 2: Aggregation by user");
  runQueryAndPrint(
    db,
    `SELECT u.name, ROUND(SUM(o.quantity * p.price), 2) AS spent
     FROM users u
     JOIN orders o ON o.user_id = u.id
     JOIN products p ON p.id = o.product_id
     GROUP BY u.id, u.name
     ORDER BY spent DESC;`
  );

  console.log("\nDemo 3: Filtering products over 50");
  runQueryAndPrint(db, "SELECT id, name, price FROM products WHERE price > 50 ORDER BY price DESC;");
}

function usage() {
  console.log(`SQLite Database Manager

Usage:
  node src/index.js <db-path> [options]
  node src/index.js                      # generate sample DB and run demo

Core options:
  --create-table <table> --columns "id:INTEGER PRIMARY KEY,name:TEXT NOT NULL"
  --insert <table> --values "id=1,name=Alice,email=alice@example.com"
  --select <table> [--columns "id,name"] [--where "id > 1"] [--limit 20]
  --update <table> --set "name=Alice2" [--where "id=1"]
  --delete <table> [--where "id=1"]
  --query "SELECT * FROM users"

Schema inspection:
  --list-tables
  --describe <table>
  --row-count <table>

Import/export:
  --import <file.csv> --table <table>
  --export <file.csv> --table <table>
  --export <file.csv> --query "SELECT ..."

Transactions:
  --begin
  --commit
  --rollback
`);
}

async function main() {
  const { options, positional } = parseArgs(process.argv.slice(2));
  if (options.help) {
    usage();
    return;
  }

  const SQL = await initSqlJs({
    locateFile: (file) =>
      path.join(path.dirname(fileURLToPath(import.meta.url)), "..", "node_modules", "sql.js", "dist", file),
  });

  const dbPath = positional[0] ? path.resolve(positional[0]) : path.resolve("sample_demo.sqlite");
  const exists = fs.existsSync(dbPath) && fs.statSync(dbPath).size > 0;
  const data = exists ? fs.readFileSync(dbPath) : undefined;
  const db = data ? new SQL.Database(data) : new SQL.Database();

  let mutated = !exists;
  let operationsRun = 0;
  let inTransaction = false;

  try {
    if (!positional[0]) {
      runDemo(db);
      mutated = true;
      operationsRun += 1;
    } else {
      if (options.begin) {
        db.exec("BEGIN TRANSACTION;");
        inTransaction = true;
        console.log("Transaction started.");
      }

      if (doCreateTable(db, options)) {
        mutated = true;
        operationsRun += 1;
      }
      if (doInsert(db, options)) {
        mutated = true;
        operationsRun += 1;
      }
      if (doUpdate(db, options)) {
        mutated = true;
        operationsRun += 1;
      }
      if (doDelete(db, options)) {
        mutated = true;
        operationsRun += 1;
      }
      if (doImport(db, options)) {
        mutated = true;
        operationsRun += 1;
      }
      if (options.query) {
        runQueryAndPrint(db, options.query);
        operationsRun += 1;
      }
      if (doSelect(db, options)) {
        operationsRun += 1;
      }
      if (options["list-tables"] || options.describe || options["row-count"]) {
        inspectSchema(db, options);
        operationsRun += 1;
      }
      if (doExport(db, options)) {
        operationsRun += 1;
      }

      if (options.rollback && inTransaction) {
        db.exec("ROLLBACK;");
        console.log("Transaction rolled back.");
        mutated = false;
        inTransaction = false;
      } else if (options.commit && inTransaction) {
        db.exec("COMMIT;");
        console.log("Transaction committed.");
        mutated = true;
        inTransaction = false;
      } else if (inTransaction) {
        db.exec("COMMIT;");
        console.log("Transaction committed automatically.");
        mutated = true;
      }
    }

    if (operationsRun === 0) {
      usage();
    }

    if (mutated) {
      saveDb(db, dbPath);
    }
    console.log(`Database path: ${dbPath}`);
  } catch (error) {
    if (inTransaction) {
      try {
        db.exec("ROLLBACK;");
      } catch {
        // ignore rollback failures
      }
    }
    const message = error instanceof Error ? error.message : String(error);
    const category = classifyError(message);
    console.error(`[${category}] ${message}`);
    process.exitCode = 1;
  } finally {
    db.close();
  }
}

main();