Database Module Guide

The database layer centralizes PDO connections through DatabaseHandler and exposes query helpers through SQLManager. A handler is created lazily on the first SQL call; no service-container registration is needed in 0.4.1.

For tests or a custom connection lifecycle, inject a handler with SQLManager::setDatabaseHandler($handler) before querying. Keep the connection configured in app-owned environment/config files, not in view templates.

Creating Configuration via CLI

Generate a database configuration interactively:

php coriander make:database

The command prompts for MySQL or SQLite and writes the appropriate settings to config/database.php.

Configuration

Database settings can come from .env or config/database.php.

For local projects, .env is usually enough:

DB_TYPE=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_CHARSET=utf8mb4
DB_NAME=app
DB_USER=user
DB_PASSWORD=secret

For projects that need PHP-level configuration, create config/database.php with the required constants:

<?php
// config/database.php

define('DB_TYPE', 'mysql');          // or 'sqlite'
define('DB_HOST', 'localhost');
define('DB_PORT', 3306);
define('DB_CHARSET', 'utf8mb4');
define('DB_NAME', 'app');
define('DB_USER', 'user');
define('DB_PASSWORD', 'secret');

The file is automatically loaded from config/config.php when present. Constants already defined in config/database.php take precedence over .env values. Avoid committing credentials to version control.

Migrations

CorianderPHP supports timestamped migration files with batch tracking.

Create migration files

php coriander make:migration CreateUsersTable

This creates a file in database/migrations named like:

20260305123000_create_users_table.php

Each migration file returns an object with up(PDO $pdo) and optional down(PDO $pdo) methods.

Apply migrations

php coriander migrate

Check migration status

php coriander migrate:status

Rollback migrations

php coriander migrate:rollback
php coriander migrate:rollback --step=2

Migration safety notes

• Executed migration checksums are tracked in the migrations table.
• If an executed migration file changes, commands fail by default.
• Use --allow-changed only in local development when intentionally editing history.
• Keep migrations immutable in shared/staging/production environments.

Each migration's changes and history record share a transaction where the database supports it. With SQLite, a failure rolls back both. MySQL DDL statements can commit implicitly, so a schema migration is not guaranteed to roll back atomically; keep backups and test deployment migrations first.

Migration commands acquire a database-scoped lock to prevent concurrent runners from applying the same work. File-backed SQLite uses a persistent lock file next to the database, for example database/app.sqlite.coriander-migrations.lock. The directory must be writable by the CLI user. Do not delete the lock file while a migration process may be running; the file's presence alone does not mean the lock is held. MySQL uses a named database lock.

Error Handling

• DatabaseHandler logs warnings if required constants are missing, unsupported drivers are used, or the connection cannot be established.
• Wrap query calls in try/catch blocks and log exceptions to avoid exposing details:

use CorianderCore\Core\Database\SQLManager;
use CorianderCore\Core\Database\DatabaseException;

try {
    $users = SQLManager::findAll('users');
} catch (DatabaseException $e) {
    // handle or log error
}

Best Practices

• Use prepared statements and parameter binding to prevent SQL injection.
• Prefer findWhere, updateWhere, and deleteWhere when your conditions are simple equality checks.
• Use sqlScript() for joins, ordering, grouping, ranges, raw SQL expressions, or write statements that do not fit the simple helpers.
• Keep long-lived connections to a minimum; enable DatabaseHandler::setAutoCloseConnection(false) only when necessary.
• Centralize complex queries in repository classes to maintain SOLID principles.
• Close connections explicitly in long-running scripts.

Usage Examples

Use findAll(['col1', 'col2'], $table) when you want an explicit column list. findAll($table) is the concise all-columns signature. findAll(['*'], $table) remains available for compatibility but is not recommended.

Selecting Records

use CorianderCore\Core\Database\SQLManager;

$activeUsers = SQLManager::findWhere(
    ['id', 'email'],
    'users',
    ['status' => 'active']
);

Fetches the ID and email for every user marked as active.

Nullable Conditions

Condition maps in findWhere(), updateWhere(), and deleteWhere() combine with AND. A PHP null value generates IS NULL; other values use bound equality, including zero and empty strings.

$users = SQLManager::findWhere(['id', 'email'], 'users', [
    'tenant_id' => 10,
    'deleted_at' => null,
]);

This selects users in tenant 10 whose deleted_at is SQL NULL. It assumes your app has those columns; they are not framework-created fields. SQL column = NULL does not match these rows, so use IS NULL explicitly when writing custom sqlScript() queries.

In 0.4.1, nullable updates and deletes now affect matching rows. Review existing calls before deployment, keep identifying conditions such as tenant/id where needed, and remember that an empty condition map is rejected. For example:

SQLManager::updateWhere('users', ['status' => 'disabled'], [
    'id' => 5,
    'deleted_at' => null,
]);

This updates user 5 only if its deleted_at is SQL NULL. null is not interchangeable with '', 0, or the string 'NULL'.

Inserting Records

use CorianderCore\Core\Database\SQLManager;

SQLManager::insertInto('users', [
    'email' => 'john@example.com',
    'status' => 'active',
]);

Creates a user record with the provided email and marks it as active.

Updating Records

use CorianderCore\Core\Database\SQLManager;

SQLManager::updateWhere('users', ['status' => 'disabled'], ['id' => 5]);

Disables the user whose ID equals 5.

Deleting Records

use CorianderCore\Core\Database\SQLManager;

SQLManager::deleteWhere('users', ['status' => 'inactive']);

Removes all users currently flagged as inactive.

Custom SQL

Use sqlScript() when a repository needs joins, grouping, ordering, ranges, or other SQL that does not fit the simple helpers.

$rows = SQLManager::sqlScript(
    'SELECT users.id, users.email, COUNT(topics.id) AS topic_count
     FROM users
     LEFT JOIN topics ON topics.user_id = users.id
     WHERE users.created_at >= :from
     GROUP BY users.id
     ORDER BY topic_count DESC',
    ['from' => '2026-01-01']
);

sqlScript() uses prepared statements and bound parameters. Its return shape depends on the statement result:

[] // SELECT returned no rows

['id' => 1, 'email' => 'admin@example.com'] // SELECT returned one row

[
    ['id' => 1, 'email' => 'admin@example.com'],
    ['id' => 2, 'email' => 'user@example.com'],
] // SELECT returned multiple rows

Write statements return true when execution succeeds:

SQLManager::sqlScript(
    'UPDATE users SET status = :status WHERE id = :id',
    ['status' => 'disabled', 'id' => 5]
);

When a query may return one or many rows, normalize the result inside the repository:

private function rows(array|bool $result): array
{
    if ($result === true || $result === []) {
        return [];
    }

    return array_is_list($result) ? $result : [$result];
}

For one-row lookups, add LIMIT 1 and return null for the empty array:

$row = SQLManager::sqlScript(
    'SELECT id, email FROM users WHERE id = :id LIMIT 1',
    ['id' => $id]
);

return $row === [] ? null : $row;

Practical Workflows

Follow Custom Modules for a complete schema, repository, and runnable read endpoint. Continue with Pagination for bounded query parameters, record counts, and empty pages, or Transactions for a service that commits two writes together and rolls both back on failure.