{"content":"# aux4/db-sqlite\n\nSQLite database tools for the aux4 CLI.\n\nThe aux4/db-sqlite package provides seamless integration with SQLite databases directly from your command line. You can execute SQL queries, perform batch inserts, stream results for large datasets, manage transactions, and handle errors gracefully. Ideal for quick prototypes, ETL pipelines, automation scripts, and interactive database tasks without writing custom scripts.\n\n## Installation\n\n``bash\naux4 aux4 pkger install aux4/db-sqlite\n`\n\n## Quick Start\n\nCreate a database, define a table, insert a record, and query data:\n\n`bash\n# Create a new database and users table\naux4 db sqlite execute \\\n --database my.db \\\n --query \"CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER, email TEXT)\"\n\n# Insert a user and return the inserted row as JSON\naux4 db sqlite execute \\\n --database my.db \\\n --query \"INSERT INTO users (name, age, email) VALUES ('Alice', 30, 'alice@example.com') returning *\"\n`\n\n## Usage\n\n### Main Commands\n\n- [aux4 db sqlite execute](./commands/db/sqlite/execute) - Execute SQL statements on a SQLite database and return all results as a JSON array.\n- [aux4 db sqlite stream](./commands/db/sqlite/stream) - Execute SQL statements and stream each row as a newline-delimited JSON object.\n- [aux4 db sqlite describe](./commands/db/sqlite/describe) - Describe the columns of a table as a canonical JSON array (see [Schema Introspection](#schema-introspection)).\n- [aux4 db sqlite list tables](./commands/db/sqlite/list/tables) - List the tables in the database.\n\n### Command Reference\n\n#### aux4 db sqlite execute\n\nRun one or more SQL statements on a SQLite database and collect all results in memory.\n\nUsage:\n`bash\naux4 db sqlite execute \\\n [--database <path>] \\\n [--query \"<SQL>\"] \\\n [--file <script.sql>] \\\n [--inputStream] \\\n [--tx] \\\n [--ignore]\n`\n\nOptions:\n\n- --database <path> Path to the SQLite file (default: :memory:)\n- --query \"<SQL>\" SQL statement to execute (positional if arg: true)\n- --file <sql_file.sql> Execute SQL from a file\n- --inputStream Read a JSON array from stdin as input parameters\n- --tx Wrap all operations in a single transaction\n- --ignore Ignore errors and continue processing, reporting failures\n\nExamples:\n\n`bash\n# Named-parameter insert\naux4 db sqlite execute \\\n --database my.db \\\n --query \"INSERT INTO users (name, age, email) VALUES (:name, :age, :email) returning *\" \\\n --name Bob --age 25 --email bob@example.com\n\n# Batch insert from JSON via stdin\necho '[{\"name\":\"Carol\",\"age\":22,\"email\":\"carol@example.com\"}]' | \\\n aux4 db sqlite execute --database my.db \\\n --query \"INSERT INTO users (name, age, email) VALUES (:name, :age, :email) returning *\" \\\n --inputStream\n\n# Transactional insert (rollback on error)\necho '[{\"name\":\"Tx1\",\"age\":40,\"email\":\"tx1@example.com\"},{\"name\":\"\"}]' | \\\n aux4 db sqlite execute --database my.db \\\n --query \"INSERT INTO users (name, age, email) VALUES (:name, :age, :email) returning *\" \\\n --inputStream --tx\n`\n\n#### aux4 db sqlite stream\n\nStream query results row-by-row for large datasets or piping into other commands.\n\nUsage:\n`bash\naux4 db sqlite stream \\\n [--database <path>] \\\n [--query \"<SQL>\"] \\\n [--file <script.sql>] \\\n [--inputStream] \\\n [--tx] \\\n [--ignore]\n`\n\nOptions are the same as execute, but results are emitted as newline-delimited JSON objects.\n\nExamples:\n\n`bash\n# Stream all users\naux4 db sqlite stream --database my.db --query \"SELECT * FROM users ORDER BY id\"\n\n# Stream with a filter parameter\naux4 db sqlite stream \\\n --database my.db \\\n --query \"SELECT name, email FROM users WHERE age >= :minAge ORDER BY name\" \\\n --minAge 30\n\n# ETL pipeline: stream and immediately insert into audit table\naux4 db sqlite stream --database my.db --query \"SELECT id, name FROM users\" | \\\n aux4 db sqlite stream \\\n --database my.db \\\n --query \"INSERT INTO user_audit (user_id, audit_name) VALUES (:id, :name) returning audit_id\" \\\n --inputStream\n`\n\n## Output Formats\n\n### Execute Command Output\n\nThe execute command returns results as JSON arrays:\n\n**Success:**\n`json\n[\n {\"id\": 1, \"name\": \"Alice\", \"age\": 30, \"email\": \"alice@example.com\"},\n {\"id\": 2, \"name\": \"Bob\", \"age\": 25, \"email\": \"bob@example.com\"}\n]\n`\n\n**Errors (to stderr):**\n`json\n[{\"item\": {\"name\": \"Bad Data\"}, \"query\": \"INSERT INTO users...\", \"error\": \"NOT NULL constraint failed: users.age\"}]\n`\n\n### Stream Command Output\n\nThe stream command returns newline-delimited JSON objects (NDJSON):\n\n`json\n{\"id\": 1, \"name\": \"Alice\", \"age\": 30, \"email\": \"alice@example.com\"}\n{\"id\": 2, \"name\": \"Bob\", \"age\": 25, \"email\": \"bob@example.com\"}\n`\n\n**Errors (to stderr):**\n`json\n{\"item\": {}, \"query\": \"SELECT invalid_column FROM users\", \"error\": \"no such column: invalid_column\"}\n`\n\n## Advanced Features\n\n### Batch Processing with inputStream\n\nProcess multiple records from JSON input:\n\n`bash\n# Create JSON file with batch data\ncat > users.json << EOF\n[\n {\"name\": \"User1\", \"age\": 25, \"email\": \"user1@example.com\"},\n {\"name\": \"User2\", \"age\": 30, \"email\": \"user2@example.com\"}\n]\nEOF\n\n# Execute batch insert\ncat users.json | aux4 db sqlite execute \\\n --database my.db \\\n --query \"INSERT INTO users (name, age, email) VALUES (:name, :age, :email) returning *\" \\\n --inputStream\n`\n\n### Parameter Override\n\nCLI parameters override JSON input parameters:\n\n`bash\n# Override email for all records in the batch\ncat users.json | aux4 db sqlite execute \\\n --database my.db \\\n --query \"INSERT INTO users (name, age, email) VALUES (:name, :age, :email) returning *\" \\\n --email \"override@example.com\" \\\n --inputStream\n`\n\n### Transaction Management\n\n**With transactions (--tx):**\n- All operations execute within a single transaction\n- On error, all changes are rolled back\n- Ensures data consistency for batch operations\n\n`bash\n# Transactional batch - all or nothing\ncat batch.json | aux4 db sqlite execute \\\n --database my.db \\\n --query \"INSERT INTO users (name, age, email) VALUES (:name, :age, :email) returning *\" \\\n --inputStream --tx\n`\n\n**Without transactions:**\n- Each operation commits individually\n- Successful operations persist even if later ones fail\n- Faster for large batches but less consistent\n\n### Error Handling\n\n**Default behavior (--ignore not set):**\n- Stop on first error\n- Exit with non-zero code\n- Error details sent to stderr\n\n**With --ignore flag:**\n- Continue processing remaining records\n- Output successful results to stdout\n- Send errors to stderr but exit with zero code\n\n`bash\n# Process all records, ignoring failures\ncat mixed_data.json | aux4 db sqlite execute \\\n --database my.db \\\n --query \"INSERT INTO users (name, age, email) VALUES (:name, :age, :email) returning *\" \\\n --inputStream --ignore\n`\n\n## Examples\n\n### Basic Query\n\n`bash\naux4 db sqlite execute --database my.db --query \"SELECT * FROM users\"\n`\n\n### Insert with Named Parameters\n\n`bash\naux4 db sqlite execute \\\n --database my.db \\\n --query \"INSERT INTO users (name, age, email) VALUES (:name, :age, :email) returning *\" \\\n --name \"Dave\" --age 45 --email dave@example.com\n`\n\n### Query with Parameters\n\n`bash\naux4 db sqlite execute \\\n --database my.db \\\n --query \"SELECT * FROM users WHERE age >= :minAge AND email LIKE :domain\" \\\n --minAge 25 --domain \"%@example.com\"\n`\n\n### Transaction Rollback Demonstration\n\n`bash\n# Good and bad records in a single batch; --tx rolls back all if any fail\necho '[{\"name\":\"Good\",\"age\":20,\"email\":\"good@example.com\"},{\"name\":\"Bad\"}]' | \\\n aux4 db sqlite execute --database my.db \\\n --query \"INSERT INTO users (name, age, email) VALUES (:name, :age, :email) returning *\" \\\n --inputStream --tx\n`\n\n### Stream Processing Pipeline\n\n`bash\n# Create audit table\naux4 db sqlite execute --database my.db \\\n --query \"CREATE TABLE user_audit (audit_id INTEGER PRIMARY KEY, user_id INTEGER, user_name TEXT, audit_timestamp TEXT DEFAULT CURRENT_TIMESTAMP)\"\n\n# Stream users and insert audit records\naux4 db sqlite stream --database my.db --query \"SELECT id, name FROM users WHERE age >= 25\" | \\\n aux4 db sqlite stream --database my.db \\\n --query \"INSERT INTO user_audit (user_id, user_name) VALUES (:id, :name) returning audit_id\" \\\n --inputStream\n`\n\n### Error Recovery with --ignore\n\n`bash\n# Process mixed data, continuing despite errors\ncat > mixed_data.json << EOF\n[\n {\"name\": \"Valid User\", \"age\": 30, \"email\": \"valid@example.com\"},\n {\"invalid_field\": \"bad data\"},\n {\"name\": \"Another Valid User\", \"age\": 25, \"email\": \"another@example.com\"}\n]\nEOF\n\ncat mixed_data.json | aux4 db sqlite execute \\\n --database my.db \\\n --query \"INSERT INTO users (name, age, email) VALUES (:name, :age, :email) returning *\" \\\n --inputStream --ignore\n`\n\n## Real-World Scenario\n\nAutomate an audit pipeline that copies user changes into an audit table:\n\n`bash\n# Ensure audit table exists\naux4 db sqlite execute --database my.db --query \"CREATE TABLE IF NOT EXISTS user_audit (audit_id INTEGER PRIMARY KEY, user_id INTEGER, audit_name TEXT, timestamp TEXT DEFAULT CURRENT_TIMESTAMP)\"\n\n# Stream users and insert audit entries\naux4 db sqlite stream --database my.db --query \"SELECT id, name FROM users WHERE age >= 18\" | \\\n aux4 db sqlite stream --database my.db --query \"INSERT INTO user_audit (user_id, audit_name) VALUES (:id, :name) returning audit_id\" --inputStream\n`\n\n## Schema Introspection\n\nThe introspection commands let you explore a database's structure — ideal for AI agents and scripts that need to discover tables and columns before querying. They reuse the same connection flag as execute: --database, the path to the SQLite file.\n\n**SQLite is a single-file engine.** There is no server, no separate schema, and no cross-database namespace — a database *is* a file. So --database (the file path) is the only scope, and there is deliberately **no --schema flag**, **no list databases**, and **no list schemas**. SQLite also has no column or table comments and no MySQL-style extra metadata, so describe never emits comment or extra keys.\n\n#### aux4 db sqlite describe\n\nReturn the columns of a table as a canonical JSON array, one object per column, in definition order.\n\nUsage:\n`bash\naux4 db sqlite describe \\\n [--database <path>] \\\n --table <table_name>\n`\n\nOptions:\n\n- --database <path> Path to the SQLite file (default: :memory:)\n- --table <table_name> Name of the table to describe (bound safely as a named parameter via pragma_table_info(:table))\n\nExample:\n\n`bash\naux4 db sqlite describe --database my.db --table product\n`\n\n`json\n[\n {\"name\":\"id\",\"type\":\"INTEGER\",\"nullable\":true,\"key\":\"PRI\"},\n {\"name\":\"name\",\"type\":\"TEXT\",\"nullable\":false},\n {\"name\":\"price\",\"type\":\"NUMERIC\",\"nullable\":true,\"default\":\"0\"},\n {\"name\":\"sku\",\"type\":\"TEXT\",\"nullable\":true}\n]\n`\n\nOnly keys that carry a value are returned — null and empty (\"\") fields are omitted, so a plain column is just {\"name\", \"type\", \"nullable\"}. nullable is always present as a real JSON boolean.\n\n**SQLite quirk:** a column declared INTEGER PRIMARY KEY is an alias for the internal rowid, and pragma_table_info reports its notnull flag as 0 — so describe faithfully reports it as nullable: true (as shown for id above) even though it can never actually hold NULL. A regular NOT NULL column reports nullable: false.\n\n#### aux4 db sqlite list tables\n\nList the tables in the database. Each row carries only the table name — SQLite has no meaningful namespace (database/schema) to qualify a table with, so name is the only key. Internal sqlite_ tables are excluded.\n\nUsage:\n`bash\naux4 db sqlite list tables \\\n [--database <path>]\n`\n\nExample:\n\n`bash\naux4 db sqlite list tables --database my.db\n`\n\n`json\n[\n {\"name\":\"product\"},\n {\"name\":\"tag\"}\n]\n`\n\n### Canonical Output Schema\n\nIntrospection output uses a **fixed, dialect-independent** set of keys so that tooling works identically across every aux4/db- adapter. A key is present only when it carries a value — null and empty (\"\") fields are omitted rather than emitted, keeping the output compact.\n\ndescribe — one object per column. When present, keys appear in this order:\n\n| Key | Type | Presence |\n|-----|------|----------|\n| name | string | always |\n| type | string | always |\n| nullable | boolean | always — true if the column accepts NULL, else false |\n| default | string | only when the column has a default |\n| key | string | only when set — PRI for a primary-key column |\n\nSQLite has no column comments and no extra metadata, so comment and extra (present on some other adapters such as MySQL) are **never** emitted here.\n\nlist tables — one object per table:\n\n| Key | Type | Presence |\n|-----|------|----------|\n| name | string | always |\n\nUnlike server-based engines, SQLite tables are not qualified by a database or schema` namespace, so no such key is emitted.\n\n## License\n\nThis package does not specify a license in its manifest. Please refer to the repository or the aux4 hub listing for licensing details.\n"}