SQLite Database Manager (javascript, written by Claude Code)

Claude Code 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

[]

#!/usr/bin/env node
/**
 * SQLite Database Manager CLI - Trial 1 (better-sqlite3 + commander + cli-table3).
 */

const { Command } = require('commander');
const Database = require('better-sqlite3');
const Table = require('cli-table3');
const fs = require('fs');
const path = require('path');

const program = new Command();

let dbPath = 'sample.db';

function getDb() {
    return new Database(dbPath);
}

function ensureDemoDb() {
    if (fs.existsSync(dbPath)) return;
    const db = new Database(dbPath);
    db.exec(`
        CREATE TABLE IF NOT EXISTS employees (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            department TEXT NOT NULL,
            salary REAL NOT NULL,
            hire_date TEXT NOT NULL
        )
    `);
    const insert = db.prepare(
        'INSERT INTO employees (name, department, salary, hire_date) VALUES (?, ?, ?, ?)'
    );
    const demoData = [
        ['Alice Johnson', 'Engineering', 95000.0, '2021-03-15'],
        ['Bob Smith', 'Marketing', 72000.0, '2020-07-01'],
        ['Carol White', 'Engineering', 102000.0, '2019-11-20'],
        ['David Brown', 'Sales', 68000.0, '2022-01-10'],
        ['Eve Davis', 'Marketing', 75000.0, '2021-09-05'],
    ];
    const insertMany = db.transaction((rows) => {
        for (const row of rows) insert.run(...row);
    });
    insertMany(demoData);
    db.close();
    console.log(`Created demo database at ${dbPath}`);
}

function printRows(rows) {
    if (!rows || rows.length === 0) {
        console.log('No rows returned.');
        return;
    }
    const headers = Object.keys(rows[0]);
    const table = new Table({ head: headers, style: { head: ['cyan'] } });
    for (const row of rows) {
        table.push(headers.map((h) => (row[h] === null ? 'NULL' : String(row[h]))));
    }
    console.log(table.toString());
    console.log(`(${rows.length} row(s))`);
}

program
    .name('db-manager')
    .description('SQLite Database Manager - manage tables, data, and queries.')
    .version('1.0.0')
    .option('--db <path>', 'Path to SQLite database file.', 'sample.db')
    .hook('preAction', (thisCommand) => {
        dbPath = thisCommand.opts().db;
        ensureDemoDb();
    });

program
    .command('create-table <tableName> [columns...]')
    .description('Create a new table. Columns: name:TYPE ...')
    .action((tableName, columns) => {
        const db = getDb();
        try {
            const colDefs = columns.map((c) => {
                const parts = c.split(':');
                if (parts.length !== 2) {
                    console.error(`Error: Invalid column definition '${c}'. Use name:TYPE format.`);
                    process.exit(1);
                }
                return `${parts[0]} ${parts[1]}`;
            });
            db.exec(`CREATE TABLE IF NOT EXISTS ${tableName} (${colDefs.join(', ')})`);
            console.log(`Table '${tableName}' created successfully.`);
        } catch (e) {
            console.error(`SQL Error: ${e.message}`);
            process.exit(1);
        } finally {
            db.close();
        }
    });

program
    .command('insert <tableName>')
    .description('Insert a row into a table.')
    .requiredOption('-v, --values <values>', 'Comma-separated values.')
    .option('-c, --columns <columns>', 'Comma-separated column names.')
    .action((tableName, opts) => {
        const db = getDb();
        try {
            const vals = opts.values.split(',').map((v) => v.trim());
            const placeholders = vals.map(() => '?').join(', ');
            let sql;
            if (opts.columns) {
                sql = `INSERT INTO ${tableName} (${opts.columns}) VALUES (${placeholders})`;
            } else {
                sql = `INSERT INTO ${tableName} VALUES (${placeholders})`;
            }
            db.prepare(sql).run(...vals);
            console.log(`Row inserted into '${tableName}'.`);
        } catch (e) {
            console.error(`SQL Error: ${e.message}`);
            process.exit(1);
        } finally {
            db.close();
        }
    });

program
    .command('select <tableName>')
    .description('Select rows from a table.')
    .option('-c, --columns <columns>', 'Columns to select.', '*')
    .option('-w, --where <where>', 'WHERE clause.')
    .option('-o, --order-by <orderBy>', 'ORDER BY clause.')
    .option('-l, --limit <limit>', 'Row limit.', parseInt)
    .action((tableName, opts) => {
        const db = getDb();
        try {
            let sql = `SELECT ${opts.columns} FROM ${tableName}`;
            if (opts.where) sql += ` WHERE ${opts.where}`;
            if (opts.orderBy) sql += ` ORDER BY ${opts.orderBy}`;
            if (opts.limit) sql += ` LIMIT ${opts.limit}`;
            const rows = db.prepare(sql).all();
            printRows(rows);
        } catch (e) {
            console.error(`SQL Error: ${e.message}`);
            process.exit(1);
        } finally {
            db.close();
        }
    });

program
    .command('update <tableName>')
    .description('Update rows in a table.')
    .requiredOption('--set <setClause>', 'SET clause.')
    .requiredOption('-w, --where <where>', 'WHERE clause.')
    .action((tableName, opts) => {
        const db = getDb();
        try {
            const sql = `UPDATE ${tableName} SET ${opts.set} WHERE ${opts.where}`;
            const result = db.prepare(sql).run();
            console.log(`${result.changes} row(s) updated in '${tableName}'.`);
        } catch (e) {
            console.error(`SQL Error: ${e.message}`);
            process.exit(1);
        } finally {
            db.close();
        }
    });

program
    .command('delete <tableName>')
    .description('Delete rows from a table.')
    .requiredOption('-w, --where <where>', 'WHERE clause.')
    .action((tableName, opts) => {
        const db = getDb();
        try {
            const sql = `DELETE FROM ${tableName} WHERE ${opts.where}`;
            const result = db.prepare(sql).run();
            console.log(`${result.changes} row(s) deleted from '${tableName}'.`);
        } catch (e) {
            console.error(`SQL Error: ${e.message}`);
            process.exit(1);
        } finally {
            db.close();
        }
    });

program
    .command('schema [tableName]')
    .description('Inspect database schema.')
    .action((tableName) => {
        const db = getDb();
        try {
            if (tableName) {
                const rows = db.prepare(`PRAGMA table_info(${tableName})`).all();
                if (rows.length === 0) {
                    console.log(`Table '${tableName}' not found.`);
                    return;
                }
                console.log(`\nSchema for table '${tableName}':`);
                printRows(rows);
            } else {
                const tables = db
                    .prepare("SELECT name FROM sqlite_master WHERE type='table' ORDER BY name")
                    .all();
                if (tables.length === 0) {
                    console.log('No tables found in the database.');
                    return;
                }
                console.log('\nTables in database:');
                for (const t of tables) {
                    console.log(`  - ${t.name}`);
                    const cols = db.prepare(`PRAGMA table_info(${t.name})`).all();
                    for (const col of cols) {
                        const nullable = col.notnull ? 'NOT NULL' : 'NULLABLE';
                        const pk = col.pk ? ' PRIMARY KEY' : '';
                        console.log(`      ${col.name} ${col.type} ${nullable}${pk}`);
                    }
                }
            }
        } catch (e) {
            console.error(`SQL Error: ${e.message}`);
            process.exit(1);
        } finally {
            db.close();
        }
    });

program
    .command('import-csv <tableName> <csvFile>')
    .description('Import data from a CSV file.')
    .option('--no-create', "Don't auto-create table.")
    .action((tableName, csvFile, opts) => {
        const db = getDb();
        try {
            const content = fs.readFileSync(csvFile, 'utf-8');
            const lines = content.trim().split('\n');
            if (lines.length === 0) {
                console.error('CSV file is empty.');
                process.exit(1);
            }
            const headers = lines[0].split(',').map((h) => h.trim());

            if (opts.create !== false) {
                const colDefs = headers.map((h) => `${h} TEXT`).join(', ');
                db.exec(`CREATE TABLE IF NOT EXISTS ${tableName} (${colDefs})`);
            }

            const placeholders = headers.map(() => '?').join(', ');
            const colNames = headers.join(', ');
            const stmt = db.prepare(
                `INSERT INTO ${tableName} (${colNames}) VALUES (${placeholders})`
            );

            const insertMany = db.transaction((dataLines) => {
                for (const line of dataLines) {
                    const vals = line.split(',').map((v) => v.trim());
                    stmt.run(...vals);
                }
            });

            const dataLines = lines.slice(1);
            insertMany(dataLines);
            console.log(
                `Imported ${dataLines.length} row(s) into '${tableName}' from '${csvFile}'.`
            );
        } catch (e) {
            console.error(`Error: ${e.message}`);
            process.exit(1);
        } finally {
            db.close();
        }
    });

program
    .command('export-csv <tableName> <csvFile>')
    .description('Export a table to a CSV file.')
    .action((tableName, csvFile) => {
        const db = getDb();
        try {
            const rows = db.prepare(`SELECT * FROM ${tableName}`).all();
            if (rows.length === 0) {
                console.log(`No data found in table '${tableName}'.`);
                return;
            }
            const headers = Object.keys(rows[0]);
            const lines = [headers.join(',')];
            for (const row of rows) {
                lines.push(headers.map((h) => (row[h] === null ? '' : String(row[h]))).join(','));
            }
            fs.writeFileSync(csvFile, lines.join('\n') + '\n', 'utf-8');
            console.log(`Exported ${rows.length} row(s) from '${tableName}' to '${csvFile}'.`);
        } catch (e) {
            console.error(`Error: ${e.message}`);
            process.exit(1);
        } finally {
            db.close();
        }
    });

program
    .command('query <sql>')
    .description('Execute a raw SQL query.')
    .option('-p, --params <params>', 'Comma-separated parameters.')
    .action((sql, opts) => {
        const db = getDb();
        try {
            const params = opts.params ? opts.params.split(',').map((p) => p.trim()) : [];
            const trimmedSql = sql.trim().toUpperCase();
            if (trimmedSql.startsWith('SELECT') || trimmedSql.startsWith('PRAGMA')) {
                const rows = db.prepare(sql).all(...params);
                printRows(rows);
            } else {
                const result = db.prepare(sql).run(...params);
                console.log(`Query executed successfully. ${result.changes} row(s) affected.`);
            }
        } catch (e) {
            console.error(`SQL Error: ${e.message}`);
            process.exit(1);
        } finally {
            db.close();
        }
    });

program.parse(process.argv);

{
  "name": "sqlite-db-manager-t1",
  "version": "1.0.0",
  "description": "SQLite Database Manager CLI using better-sqlite3, commander, and cli-table3",
  "main": "db_manager.js",
  "bin": {
    "db-manager": "./db_manager.js"
  },
  "scripts": {
    "start": "node db_manager.js"
  },
  "dependencies": {
    "better-sqlite3": "9.4.3",
    "commander": "11.1.0",
    "cli-table3": "0.6.3"
  }
}

# SQLite Database Manager - JavaScript Trial 1

CLI for managing SQLite databases using better-sqlite3, commander, and cli-table3.

## Dependencies

- Node.js 16+
- better-sqlite3 9.4.3
- commander 11.1.0
- cli-table3 0.6.3

## Installation

```bash
npm install
```

## Usage

```bash
# Show help
node db_manager.js --help

# Create a table
node db_manager.js create-table users id:INTEGER name:TEXT email:TEXT

# Insert a row
node db_manager.js insert users -c "name,email" -v "Alice,alice@example.com"

# Select rows
node db_manager.js select users
node db_manager.js select users -w "name='Alice'" -o "name ASC" -l 10

# Update rows
node db_manager.js update users --set "email='new@example.com'" -w "name='Alice'"

# Delete rows
node db_manager.js delete users -w "name='Alice'"

# Inspect schema
node db_manager.js schema
node db_manager.js schema users

# Import/Export CSV
node db_manager.js import-csv users data.csv
node db_manager.js export-csv users output.csv

# Execute raw SQL
node db_manager.js query "SELECT * FROM employees WHERE salary > ?" -p "80000"

# Use a specific database
node db_manager.js --db mydata.db select employees
```