db

This is a database scaffold for creating custom database command-line interfaces (CLIs).

Databases

• PostgreSQL
• MSSQL
• Oracle
• MySQL
• SQLite/Turso
• Other databases

DB Driver Implementation Requirements

When implementing a database driver for the aux4 db system, your driver must handle two primary operations: execute and stream. Both operations receive standardized input and must produce specific output formats.

Input Format

The db driver receives input via stdin as JSON with the following structure:

{
  "action": "execute|stream",
  "query": "SQL query string",
  "file": "path/to/sql/file (optional)",
  "inputStream": "true|false",
  "tx": "true|false", 
  "ignore": "true|false",
  "params": {
    "param1": "value1",
    "param2": "value2"
  }
}

When inputStream is true, the driver should also read JSON objects from stdin for batch processing:

{"param1": "value1", "param2": "value2"}
{"param1": "value3", "param2": "value4"}

Execute Operation

The execute operation should return complete result sets as JSON arrays.

Success Output:

[
  {"column1": "value1", "column2": "value2"},
  {"column1": "value3", "column2": "value4"}
]

Error Output (to stderr):

[{"item": {...}, "query": "SQL query", "error": "error message"}]

With ignore flag: Continue processing remaining items, output successful results to stdout, errors to stderr.

With tx flag: Execute all operations within a single transaction. On error, rollback all changes.

Stream Operation

The stream operation should return results as newline-delimited JSON objects (NDJSON).

Success Output:

{"column1": "value1", "column2": "value2"}
{"column1": "value3", "column2": "value4"}

Error Output (to stderr):

{"item": {...}, "query": "SQL query", "error": "error message"}

With ignore flag: Continue streaming remaining results, output errors to stderr.

With tx flag: Execute within transaction, rollback on any error.

Parameter Handling

• Parameters in queries use named placeholders (e.g., :paramName, $paramName, or ? depending on database)
• CLI parameters override JSON input parameters
• Missing parameters should be treated as null
• Support both single-record and batch operations via inputStream

Transaction Behavior

When tx is true:

• Begin transaction before first operation
• Commit on successful completion of all operations
• Rollback on any error
• For streaming: buffer results until transaction completes

Error Handling

When ignore is false (default):

• Stop processing on first error
• Return error details to stderr
• Exit with non-zero code

When ignore is true:

• Continue processing remaining items
• Output successful results normally
• Send errors to stderr but continue
• Exit with zero code if any operations succeeded

SQL Parameter Binding

The db package provides a SQL parameter binding tool that safely substitutes parameters into SQL queries with built-in SQL injection protection.

Usage

aux4 db driver sql-bind --query "SELECT * FROM users WHERE name = :name AND age = :age" --params '{"name": "john", "age": 25}'

Output:

SELECT * FROM users WHERE name = 'john' AND age = 25

Features

• SQL Injection Protection: Automatically escapes single quotes by doubling them (' → '')
• Type Handling: Properly formats different data types:
• Strings: 'quoted'
• Numbers: 25, 99.99
• Booleans: TRUE, FALSE
• Null values: NULL
• Array Support: Converts arrays to IN clause format: [1,2,3] → (1, 2, 3)
• Empty Arrays: Safe handling: [] → (NULL)

Examples

String with quotes (SQL injection protection):

aux4 db driver sql-bind --query "SELECT * FROM users WHERE name = :name" --params '{"name": "O'\''Reilly"}'
# Output: SELECT * FROM users WHERE name = 'O''Reilly'

IN clause with arrays:

aux4 db driver sql-bind --query "SELECT * FROM users WHERE id IN :ids" --params '{"ids": [1, 2, 3]}'
# Output: SELECT * FROM users WHERE id IN (1, 2, 3)

Mixed types:

aux4 db driver sql-bind --query "SELECT * FROM orders WHERE user_id = :user_id AND amount > :min_amount AND active = :active" --params '{"user_id": 123, "min_amount": 50.75, "active": true}'
# Output: SELECT * FROM orders WHERE user_id = 123 AND amount > 50.75 AND active = TRUE

How to Create a Custom Database CLI

{
  "scope": "<scope>",
  "name": "db-<database name>",
  "version": "<version>",
  "description": "<database name> database tools",
  "tags": ["db", "database", "<database name>"],
  "dependencies": ["aux4/db"],
  "profiles": [
    {
      "name": "db",
      "commands": [
        {
          "name": "<database name>",
          "execute": ["profile:db:<database name>"],
          "help": {
            "text": "<database name> database tools"
          }
        }
      ]
    },
    {
      "name": "db:<database name>",
      "commands": [
        {
          "name": "execute",
          "execute": [
            "stdin:aux4 db driver prepare execute params(query, file, inputStream, tx, ignore) --params value(*) | <command to execute a query> values(<db params>)"
          ],
          "help": {
            "text": "Execute a query on the <database name> database",
            "variables": [
              {
                <db variables>
              },
              {
                "name": "query",
                "text": "SQL query to execute",
                "default": ""
              },
              {
                "name": "file",
                "text": "SQL file to execute",
                "default": ""
              },
              {
                "name": "inputStream",
                "text": "Read JSON input from stdin",
                "default": "false"
              },
              {
                "name": "tx",
                "text": "Execute the query in a transaction",
                "default": "false"
              },
              {
                "name": "ignore",
                "text": "Ignore errors and continue processing",
                "default": "false"
              }
            ]
          }
        },
        {
          "name": "stream",
          "execute": [
            "stdin:aux4 db driver prepare stream params(query, file, inputStream, tx, ignore) --params value(*) | <command to stream a query> values(<db params>)"
          ],
          "help": {
            "text": "Execute a query on the <database name> database and stream results",
            "variables": [
              {
                <db variables>
              },
              {
                "name": "query",
                "text": "SQL query to execute",
                "default": ""
              },
              {
                "name": "file",
                "text": "SQL file to execute",
                "default": ""
              },
              {
                "name": "inputStream",
                "text": "Read JSON input from stdin",
                "default": "false"
              },
              {
                "name": "tx",
                "text": "Execute the query in a transaction",
                "default": "false"
              },
              {
                "name": "ignore",
                "text": "Ignore errors and continue processing",
                "default": "false"
              }
            ]
          }
        }
      ]
    }
  ]
}

You can also include the variables required to connect to the database in the execute and stream commands, for example:

...
      {
        "name": "host",
        "text": "Database host",
        "default": "localhost"
      },
      {
        "name": "port",
        "text": "Database port",
        "default": "<default port>"
      },
      {
        "name": "user",
        "text": "Database user",
        "default": "<default user>"
      },
      {
        "name": "password",
        "text": "Database password"
      },
      {
        "name": "database",
        "text": "Database name",
        "default": "<default database>"
      }
...