{"content":"# aux4/db-mysql\n\nMySQL database tools for the aux4 CLI.\n\nThe aux4/db-mysql package provides seamless integration with MySQL 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-mysql\n`\n\n## Quick Start\n\nConnect to a database, create a table, insert a record, and query data:\n\n`bash\n# Create a users table\naux4 db mysql execute \\\n --host localhost --port 3306 --database mydb --user root --password mypass \\\n --query \"CREATE TABLE IF NOT EXISTS users (id INT AUTO_INCREMENT PRIMARY KEY, name TEXT, age INTEGER, email TEXT)\"\n\n# Insert a user\naux4 db mysql execute \\\n --host localhost --port 3306 --database mydb --user root --password mypass \\\n --query \"INSERT INTO users (name, age, email) VALUES ('Alice', 30, 'alice@example.com')\"\n\n# Query all users\naux4 db mysql execute \\\n --host localhost --port 3306 --database mydb --user root --password mypass \\\n --query \"SELECT * FROM users\"\n`\n\n## Usage\n\n### Main Commands\n\n- [aux4 db mysql execute](./commands/db/mysql/execute) - Execute SQL statements on a MySQL database and return all results as a JSON array.\n- [aux4 db mysql stream](./commands/db/mysql/stream) - Execute SQL statements and stream each row as a newline-delimited JSON object.\n- [aux4 db mysql describe](./commands/db/mysql/describe) - Describe the columns of a table (types, keys, defaults, and comments) using a stable canonical schema.\n- [aux4 db mysql list tables](./commands/db/mysql/list/tables) - List the base tables in the current database with their comments.\n\n### Command Reference\n\n#### aux4 db mysql execute\n\nRun one or more SQL statements on a MySQL database and collect all results in memory.\n\nUsage:\n`bash\naux4 db mysql execute \\\n [--host <hostname>] \\\n [--port <port>] \\\n [--database <dbname>] \\\n [--user <username>] \\\n [--password <password>] \\\n [--query \"<SQL>\"] \\\n [--file <script.sql>] \\\n [--inputStream] \\\n [--tx] \\\n [--ignore]\n`\n\nOptions:\n\n- --host <hostname> Database host (default: localhost)\n- --port <port> Database port (default: 3306)\n- --database <dbname> Database name (default: mysql)\n- --user <username> Database user (default: root)\n- --password <password> Database password\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 mysql execute \\\n --host localhost --port 3306 --database mydb --user root --password mypass \\\n --query \"INSERT INTO users (name, age, email) VALUES (:name, :age, :email)\" \\\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 mysql execute \\\n --host localhost --port 3306 --database mydb --user root --password mypass \\\n --query \"INSERT INTO users (name, age, email) VALUES (:name, :age, :email)\" \\\n --inputStream\n\n# Transactional insert (rollback on error)\necho '[{\"name\":\"Tx1\",\"age\":40,\"email\":\"tx1@example.com\"},{\"name\":\"\"}]' | \\\n aux4 db mysql execute \\\n --host localhost --port 3306 --database mydb --user root --password mypass \\\n --query \"INSERT INTO users (name, age, email) VALUES (:name, :age, :email)\" \\\n --inputStream --tx\n`\n\n#### aux4 db mysql stream\n\nStream query results row-by-row for large datasets or piping into other commands.\n\nUsage:\n`bash\naux4 db mysql stream \\\n [--host <hostname>] \\\n [--port <port>] \\\n [--database <dbname>] \\\n [--user <username>] \\\n [--password <password>] \\\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 mysql stream \\\n --host localhost --port 3306 --database mydb --user root --password mypass \\\n --query \"SELECT * FROM users ORDER BY id\"\n\n# Stream with a filter parameter\naux4 db mysql stream \\\n --host localhost --port 3306 --database mydb --user root --password mypass \\\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 mysql stream \\\n --host localhost --port 3306 --database mydb --user root --password mypass \\\n --query \"SELECT id, name FROM users\" | \\\n aux4 db mysql stream \\\n --host localhost --port 3306 --database mydb --user root --password mypass \\\n --query \"INSERT INTO user_audit (user_id, audit_name) VALUES (:id, :name)\" \\\n --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 flags as execute (--host, --port, --database, --user, --password).\n\n**Scope is the connected database.** On MySQL the native scoping concept is the database, so --database is how you choose what describe and list tables see — connecting to database X makes both commands operate on X (internally via MySQL's DATABASE() function on the live connection). There is no separate --schema flag for MySQL.\n\n#### aux4 db mysql 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 mysql describe \\\n [--host <hostname>] \\\n [--port <port>] \\\n [--database <dbname>] \\\n [--user <username>] \\\n [--password <password>] \\\n --table <table_name>\n`\n\nOptions:\n\n- --host <hostname> Database host (default: localhost)\n- --port <port> Database port (default: 3306)\n- --database <dbname> Database name (default: mysql)\n- --user <username> Database user (default: root)\n- --password <password> Database password\n- --table <table_name> Name of the table to describe (bound safely as a named parameter)\n\nExample:\n\n`bash\naux4 db mysql describe \\\n --host localhost --port 3306 --database mydb --user root --password mypass \\\n --table product\n`\n\n`json\n[\n {\"name\":\"id\",\"type\":\"int\",\"nullable\":false,\"key\":\"PRI\",\"extra\":\"auto_increment\",\"comment\":\"Unique product identifier\"},\n {\"name\":\"name\",\"type\":\"varchar\",\"nullable\":false,\"comment\":\"Product display name\"},\n {\"name\":\"price\",\"type\":\"decimal\",\"nullable\":true,\"default\":\"0.00\",\"comment\":\"Unit price in USD\"}\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.\n\n#### aux4 db mysql list tables\n\nList the base tables in the current database. Each row carries the table name, the database it lives in (so an agent can fully qualify it), and the table comment when one is set.\n\nUsage:\n`bash\naux4 db mysql list tables \\\n [--host <hostname>] \\\n [--port <port>] \\\n [--database <dbname>] \\\n [--user <username>] \\\n [--password <password>]\n`\n\nExample:\n\n`bash\naux4 db mysql list tables \\\n --host localhost --port 3306 --database mydb --user root --password mypass\n`\n\n`json\n[\n {\"name\":\"product\",\"database\":\"mydb\",\"comment\":\"Catalog of products for sale\"}\n]\n`\n\nAs with describe, empty/null fields are omitted — a table with no comment is just {\"name\", \"database\"}.\n\n#### aux4 db mysql list databases\n\nList the databases (schemas) available on the server — the starting point for an agent that needs to discover a database before drilling into its tables. On MySQL a database *is* a schema, so there is no separate list schemas.\n\nUsage:\n`bash\naux4 db mysql list databases \\\n [--host <hostname>] \\\n [--port <port>] \\\n [--user <username>] \\\n [--password <password>]\n`\n\nExample:\n\n`bash\naux4 db mysql list databases \\\n --host localhost --port 3306 --user root --password mypass\n`\n\n`json\n[\n {\"name\":\"information_schema\"},\n {\"name\":\"mydb\"},\n {\"name\":\"mysql\"}\n]\n`\n\nThe server's system databases (information_schema, mysql, performance_schema, sys) are included; filter them client-side if you only want application schemas.\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 (primary key), UNI/MUL (indexed) |\n| extra | string | only when set — e.g. auto_increment |\n| comment | string | only when the column has a comment |\n\nlist tables — one object per table:\n\n| Key | Type | Presence |\n|-----|------|----------|\n| name | string | always |\n| database | string | always — the database (namespace) the table lives in |\n| comment | string | only when the table has a comment |\n\n**Notes:**\n- nullable is a real JSON boolean (true/false) — never the string \"YES\"/\"NO\" and never the number 1/0.\n- comment carries the semantic description of the column or table, which is especially useful for AI agents exploring an unfamiliar schema.\n- name, type, and nullable are guaranteed on every describe row; everything else is present only when it has a value. This same schema is shared verbatim by the other aux4/db- adapters (namespace naming follows each dialect: MySQL uses database; Postgres/MSSQL add schema; Oracle uses schema).\n\n## Output Formats\n\n### Execute Command Output\n\nThe execute command returns results as JSON arrays for SELECT queries:\n\n**SELECT 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**INSERT/UPDATE/DELETE Success:**\n`json\n[{\"fieldCount\":0,\"affectedRows\":1,\"insertId\":1,\"info\":\"\",\"serverStatus\":2,\"warningStatus\":0,\"changedRows\":0}]\n`\n\n**Errors (to stderr):**\n`json\n[{\"item\": {\"name\": \"Bad Data\"}, \"query\": \"INSERT INTO users...\", \"error\": \"Column 'age' cannot be null\"}]\n`\n\n### Stream Command Output\n\nThe stream command returns newline-delimited JSON objects (NDJSON) for SELECT queries:\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\": \"Unknown column 'invalid_column' in 'field list'\"}\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 mysql execute \\\n --host localhost --port 3306 --database mydb --user root --password mypass \\\n --query \"INSERT INTO users (name, age, email) VALUES (:name, :age, :email)\" \\\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 mysql execute \\\n --host localhost --port 3306 --database mydb --user root --password mypass \\\n --query \"INSERT INTO users (name, age, email) VALUES (:name, :age, :email)\" \\\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 mysql execute \\\n --host localhost --port 3306 --database mydb --user root --password mypass \\\n --query \"INSERT INTO users (name, age, email) VALUES (:name, :age, :email)\" \\\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 mysql execute \\\n --host localhost --port 3306 --database mydb --user root --password mypass \\\n --query \"INSERT INTO users (name, age, email) VALUES (:name, :age, :email)\" \\\n --inputStream --ignore\n`\n\n## Examples\n\n### Basic Query\n\n`bash\naux4 db mysql execute \\\n --host localhost --port 3306 --database mydb --user root --password mypass \\\n --query \"SELECT * FROM users\"\n`\n\n### Insert with Named Parameters\n\n`bash\naux4 db mysql execute \\\n --host localhost --port 3306 --database mydb --user root --password mypass \\\n --query \"INSERT INTO users (name, age, email) VALUES (:name, :age, :email)\" \\\n --name \"Dave\" --age 45 --email dave@example.com\n`\n\n### Query with Parameters\n\n`bash\naux4 db mysql execute \\\n --host localhost --port 3306 --database mydb --user root --password mypass \\\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 mysql execute \\\n --host localhost --port 3306 --database mydb --user root --password mypass \\\n --query \"INSERT INTO users (name, age, email) VALUES (:name, :age, :email)\" \\\n --inputStream --tx\n`\n\n### Stream Processing Pipeline\n\n`bash\n# Create audit table\naux4 db mysql execute \\\n --host localhost --port 3306 --database mydb --user root --password mypass \\\n --query \"CREATE TABLE user_audit (audit_id INT AUTO_INCREMENT PRIMARY KEY, user_id INTEGER, user_name TEXT, user_email TEXT, audit_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP)\"\n\n# Stream users and insert audit records\naux4 db mysql stream \\\n --host localhost --port 3306 --database mydb --user root --password mypass \\\n --query \"SELECT id, name, email FROM users WHERE age >= 25\" | \\\n aux4 db mysql stream \\\n --host localhost --port 3306 --database mydb --user root --password mypass \\\n --query \"INSERT INTO user_audit (user_id, user_name, user_email) VALUES (:id, :name, :email)\" \\\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 mysql execute \\\n --host localhost --port 3306 --database mydb --user root --password mypass \\\n --query \"INSERT INTO users (name, age, email) VALUES (:name, :age, :email)\" \\\n --inputStream --ignore\n``\n\n## License\n\nApache-2.0. See the LICENSE file for details.\n"}