Database Commands

This document covers the database utility commands (db:connect, db:query, and migrate) that provide direct access to the Maho database using native database clients and manage schema updates. These commands automatically use the database credentials configured in the application.

For general CLI architecture and command discovery, see CLI Architecture. For database abstraction and model-level database access, see Database Layer.

Purpose and Scope

The database commands provide administrators and developers with quick command-line access to the Maho database without manually entering connection credentials. These commands support all three database engines used by Maho: MySQL/MariaDB, PostgreSQL, and SQLite. Additionally, the migration command handles the execution of setup scripts to keep the database schema in sync with the code.

Available Commands

db:connect

Opens an interactive database client session using credentials from the application configuration lib/MahoCLI/Commands/DBConnect.php21-132

Command: ./maho db:connect

Behavior:

• Retrieves the database engine from the configured connection global/resources/default_setup/connection lib/MahoCLI/Commands/DBConnect.php34-36
• Depending on the engine, launches the appropriate native client:
  • MySQL: launches mysql with a temporary .my.cnf config file for secure authentication lib/MahoCLI/Commands/DBConnect.php46-62
  • PostgreSQL: launches psql with a temporary .pgpass file set via the PGPASSFILE environment variable lib/MahoCLI/Commands/DBConnect.php64-84
  • SQLite: launches sqlite3 pointing to var/db/{dbname} database file lib/MahoCLI/Commands/DBConnect.php86-96
• Uses proc_open to spawn the client subprocess maintaining an interactive shell session until exit lib/MahoCLI/Commands/DBConnect.php104-131

db:query

Executes a single SQL query and outputs the result using native database clients lib/MahoCLI/Commands/DBQuery.php22-152

Command: ./maho db:query "SELECT * FROM core_config_data LIMIT 5"

Behavior:

• Accepts a SQL query string as an argument lib/MahoCLI/Commands/DBQuery.php33-37
• Runs the query using native clients similar to db:connect but non-interactively.
• Handles MySQL by creating a temporary .my.cnf file and running mysql --defaults-extra-file=... -e "query" lib/MahoCLI/Commands/DBQuery.php58-75
• Handles PostgreSQL by creating a temporary .pgpass file, running psql with -c "query" lib/MahoCLI/Commands/DBQuery.php77-98
• Handles SQLite by running sqlite3 -header -column var/db/{dbname} "query" lib/MahoCLI/Commands/DBQuery.php100-111
• Streams output to stdout and errors to stderr lib/MahoCLI/Commands/DBQuery.php139-146

migrate

Runs the Maho database migration system (./maho migrate) to apply pending schema and data updates automatically.

Behavior:

• Initializes the Maho environment and loads modules to detect migration scripts.
• Executes schema changes and data patches in sql subdirectories of all modules.
• Enforces cross-database compatibility, such as converting MySQL TIMESTAMP columns to DATETIME for consistent handling across engines app/code/core/Mage/Core/sql/maho_setup/maho-26.5.0.php19-79
• Updates existing columns by removing MySQL-specific ON UPDATE CURRENT_TIMESTAMP, shifting management to PHP application logic app/code/core/Mage/Catalog/sql/maho_setup/maho-26.5.0.php17-25
• Helps maintain consistent behavior across MySQL, PostgreSQL, and SQLite by avoiding engine-specific quirks and improving portability.

Sources:
lib/MahoCLI/Commands/DBConnect.php21-132
lib/MahoCLI/Commands/DBQuery.php22-152
app/code/core/Mage/Core/sql/maho_setup/maho-26.5.0.php19-79
app/code/core/Mage/Catalog/sql/maho_setup/maho-26.5.0.php17-25

---

Command Architecture

Database CLI commands extend the BaseMahoCommand abstract base class, which initializes the Maho application environment before command execution. The commands use the DatabaseCliTrait to share helper methods for determining the database engine and creating temporary authentication files.

Class Structure

Title: Database Command Class Structure

This hierarchy reflects the Symfony Console integration and modular design. Both commands reuse the trait for common database client preparation logic.

Sources:
lib/MahoCLI/Commands/DBConnect.php21-132
lib/MahoCLI/Commands/DBQuery.php22-152
lib/MahoCLI/Commands/BaseMahoCommand.php13-26

---

Database Engine Support

Maho supports multiple database backends via adapters located at Maho\Db\Adapter\Pdo. The CLI commands adapt to the configured engine, supporting:

Engine	Config Identifier	CLI Client	Connection Details
MySQL	mysql	mysql	Uses mysql --defaults-extra-file={file} {dbname} for authentication via temporary file lib/MahoCLI/Commands/DBConnect.php55-59
PostgreSQL	pgsql	psql	Uses psql with PGPASSFILE environment variable for password lib/MahoCLI/Commands/DBConnect.php74-81
SQLite	sqlite	sqlite3	Uses SQLite DB file path under var/db/{dbname} lib/MahoCLI/Commands/DBConnect.php88-93

Execution Flow

Title: Database Command Execution Flow Sequence

This flow covers interactive connection and query execution, with temporary config file handling for credentials passed securely to native clients.

Sources:
lib/MahoCLI/Commands/DBConnect.php30-44
lib/MahoCLI/Commands/DBQuery.php41-56
lib/MahoCLI/Commands/BaseMahoCommand.php20-24

---

Database Maintenance Utilities

Adapter Implementations

Maho relies on specialized PDO adapter classes extending AbstractPdoAdapter that internally use Doctrine DBAL connections. Each adapter implements platform-specific details such as data type mappings, connection initialization, and features.

• MySQL Adapter:
  Maho\Db\Adapter\Pdo\Mysql

  • Maps standard DDL types to MySQL SQL types (e.g., TYPE_DATETIME → datetime)
  • On connection, sets SQL_MODE empty and enforces UTC timezone for timestamp consistency
  • Supports UNIX socket, IPv4, IPv6, and hostname parsing for connection lib/Maho/Db/Adapter/Pdo/Mysql.php20-155

• PostgreSQL Adapter:
  Maho\Db\Adapter\Pdo\Pgsql

  • Maps DDL types appropriately (TYPE_BLOB → bytea, TYPE_DATETIME→timestamp)
  • Sets UTF8 client encoding and standard conforming strings on connect
  • Implements a raw_query() method to retry on lost connections automatically
  • Disables GSS encryption mode by default for macOS stability lib/Maho/Db/Adapter/Pdo/Pgsql.php21-190

• SQLite Adapter:
  Maho\Db\Adapter\Pdo\Sqlite

  • Uses dynamic typing with type affinity; maps types accordingly (e.g., TYPE_BOOLEAN → INTEGER)
  • Enables foreign key constraints and Write-Ahead Logging (WAL) for performance
  • Registers user-defined SQL functions (REGEXP, GREATEST, LEAST) to emulate missing features for parity with MySQL/PostgreSQL
  • Uses PHP's preg_match to implement REGEXP in SQLite lib/Maho/Db/Adapter/Pdo/Sqlite.php19-190

Cross-Engine DDL Type Mapping

Maho defines common data types in Maho\Db\Ddl\Table and translates them into engine-specific SQL types via adapters.

Title: DDL Type Translation Mapping

This mapping ensures that migrations and schema definitions use a unified set of constants, improving portability.

Sources:
lib/Maho/Db/Adapter/Pdo/Mysql.php44-60
lib/Maho/Db/Adapter/Pdo/Pgsql.php45-71
lib/Maho/Db/Adapter/Pdo/Sqlite.php41-57
lib/Maho/Db/Adapter/AbstractPdoAdapter.php152-154

---

Summary

The database commands suite offers direct and native database interaction utilities as CLI commands, enhancing developer productivity when managing Maho stores. They abstract authentication details, adapt to the configured DB engine, and provide consistent cross-engine behavior through unified adapters and migration scripts.

The architecture tightly integrates with the Symfony Console framework, sharing traits for common functionality, and employs platform-specific PDO adapters with Doctrine DBAL to manage connection details and SQL dialect peculiarities. Migration commands evolve the database schema while maintaining compliance across supported engines.

Administrators and developers can use these commands to connect interactively or run SQL queries non-interactively, with consistent environment preparation and secure credential handling.

---

Sources:
lib/MahoCLI/Commands/DBConnect.php21-132
lib/MahoCLI/Commands/DBQuery.php22-152
app/code/core/Mage/Core/sql/maho_setup/maho-26.5.0.php19-79
app/code/core/Mage/Catalog/sql/maho_setup/maho-26.5.0.php17-25
lib/Maho/Db/Adapter/Pdo/Mysql.php20-155
lib/Maho/Db/Adapter/Pdo/Pgsql.php21-190
lib/Maho/Db/Adapter/Pdo/Sqlite.php19-190
lib/Maho/Db/Adapter/AbstractPdoAdapter.php24-154