README

A MySQL abstraction layer for the ModEl Framework, providing elegant database operations with built-in caching, transactions, migrations, and event dispatching.

Features

Simple CRUD Operations: Intuitive methods for insert, update, delete, and select operations (uses model/query-builder under the hood)
Smart Caching: Automatic caching for frequently accessed data with intelligent invalidation
Transaction Management: Nested transaction support with automatic commit/rollback
Database Migrations: Integrated Phinx-based migration system
Event System: Comprehensive event dispatching for all database operations
Provider System: Extensible provider architecture for customizing database behavior
Query Limits: Built-in protection against runaway queries
Type Safety: Automatic type casting for database values

Installation

composer require model/db

Configuration

The package uses the model/config system. Configuration is stored under the db key:

[
    'databases' => [
        'primary' => [
            'host' => 'localhost',
            'port' => 3306,
            'username' => 'root',
            'password' => '',
            'name' => 'database',
            'charset' => 'utf8',
            'migrations' => ['migrations/'], // Migration paths
            'cache_tables' => [], // Tables to always cache
            'limits' => [
                'query' => 100,  // Max same query executions
                'table' => 10000, // Max queries per table
                'total' => null,  // Max total queries
            ],
        ],
    ],
]

Basic Usage

Getting a Connection

use Model\Db\Db;

// Get the default connection (first defined or 'primary')
$db = Db::getConnection();

// Get a specific connection
$db = Db::getConnection('alternative_db');

Query builder integration

This package leans on the model/query-builder package for the query building. Here follows a quick overview, but refer to said package for more details on "where" clauses building and options.

Insert Operations

// Insert a single row
$id = $db->insert('users', [
    'name' => 'John Doe',
    'email' => 'john@example.com',
    'age' => 30
]);

Select Operations

// Select by ID
$user = $db->select('users', 1);

// Select with conditions
$user = $db->select('users', ['email' => 'john@example.com']);

// Select all rows
$users = $db->selectAll('users');

// Streaming results (it returns a generator and not an array)
$users = $db->selectAll('users', [], ['stream' => true]);
foreach ($users as $user) {
    // Process each user
}

Streaming is the default behavior, but you can disable it by setting the stream option to false.

Update Operations

// Update by ID
$db->update('users', 1, ['name' => 'Jane Doe']);

// Update entire table (requires confirmation)
$db->update('users', [], ['active' => true], ['confirm' => true]);

// Update or insert
$id = $db->updateOrInsert('users', ['email' => 'john@example.com'], [
    'name' => 'John Updated',
    'age' => 31
]);

Delete Operations

// Delete by ID
$db->delete('users', 1);

// Delete with conditions
$db->delete('users', ['status' => 'inactive']);

// Delete entire table (requires confirmation)
$db->delete('users', [], ['confirm' => true]);

Count Operations

// Count all rows
$total = $db->count('users');

// Count with conditions
$active = $db->count('users', ['status' => 'active']);

// Count with grouping
$count = $db->count('users', [], ['group_by' => 'status']);

Advanced Features

Transactions

$db->beginTransaction();

try {
    $id = $db->insert('users', ['name' => 'John']);
    $db->insert('profiles', ['user_id' => $id]);
    
    $db->commit();
} catch (\Exception $e) {
    $db->rollBack();
    throw $e;
}

Transactions support nesting - only the outermost transaction actually commits to the database.

Bulk/Deferred Inserts

For high-performance bulk operations, use deferred inserts:

// Defer inserts and flush every 100 rows
for ($i = 0; $i < 1000; $i++) {
    $db->insert('users', [
        'name' => 'User ' . $i,
        'email' => 'user' . $i . '@example.com',
    ], ['defer' => 100]);
}

// Manually flush remaining inserts
$db->bulkInsert('users');

Caching

The package provides intelligent caching:

Simple queries on small tables (< 200 rows) are automatically table-cached (meaning that the whole table is cached in memory)
Tables listed in cache_tables config are always cached
Cache is automatically invalidated on INSERT, UPDATE, or DELETE
In-memory cache for repeated queries in the same request (with the same parameters)

// Disable cache for a query
$users = $db->selectAll('users', [], ['cache' => false]);

// Manually invalidate cache for a table
$db->changedTable('users');

Union Queries

$results = $db->unionSelect([
    [
        'table' => 'active_users',
        'where' => ['status' => 'active'],
    ],
    [
        'table' => 'pending_users',
        'where' => ['status' => 'pending'],
    ],
]);

Raw Queries

$statement = $db->query('SELECT * FROM users WHERE age > 18');

foreach ($statement as $row) {
    // Process row
}

Database Introspection

// Get table structure
$table = $db->getTable('users');
echo $table->name;
print_r($table->columns);
print_r($table->primary);
print_r($table->indexes);

// Get the parser
$parser = $db->getParser();

// Get the query builder
$builder = $db->getBuilder();

// Get the PDO instance
$pdo = $db->getDb();

Events

The package dispatches events for all database operations:

Model\Db\Events\Query - Any query execution
Model\Db\Events\InsertQuery - Before insert
Model\Db\Events\InsertedQuery - After insert
Model\Db\Events\UpdateQuery - Before update
Model\Db\Events\DeleteQuery - Before delete
Model\Db\Events\SelectQuery - Before select
Model\Db\Events\ChangedTable - Table data changed

See model/events package for more details.

Migrations

The package uses Phinx for database migrations.

Running Migrations

Migrations are automatically run when refreshing the ModEl Framework cache. To manually run migrations:

use Model\Db\Db;

// Run all migrations for all databases
Db::migrate();

// Run migrations for a specific database
Db::migrate('primary');

Creating Migrations

Create migration files in the configured migrations folder:

<?php

use Phinx\Migration\AbstractMigration;

class CreateUsersTable extends AbstractMigration
{
    public function change()
    {
        $table = $this->table('users');
        $table->addColumn('name', 'string', ['limit' => 100])
              ->addColumn('email', 'string', ['limit' => 100])
              ->addColumn('age', 'integer')
              ->addColumn('created_at', 'datetime')
              ->addIndex(['email'], ['unique' => true])
              ->create();
    }
}

See robmorgan/phinx package docs for more details.

model / db

Maintainers

Details