Skip to content

Database ORM

Jump to bottom
Precious Lyson edited this page Nov 13, 2026 · 3 revisions

The Mlangeni Machinjiri Database System is a standalone PHP database toolkit designed to give you a fluent, expressive API for working with relational databases (MySQL, PostgreSQL, SQLite) and document databases (MongoDB) — without forcing you to adopt a full framework.

What it solves

  • Consistent API across multiple database drivers
  • Connection pooling to reuse expensive database connections
  • Intelligent query caching with tag-based invalidation
  • Schema-as-code via Blueprint and ColumnBuilder
  • Migration, Seeder, and Factory tooling for managing schema and test data
  • MongoDB support through a parallel query builder

Core Components

Component Purpose
DatabaseConnection Singleton connection manager with pooling and transaction support
QueryBuilder Fluent SQL builder for SELECT/INSERT/UPDATE/DELETE and DDL
MongoQueryBuilder Fluent MongoDB query builder
CachedQueryBuilder Decorator for caching query results
Blueprint Schema builder for CREATE/ALTER/DROP tables
ColumnBuilder Chainable column modifiers (nullable, unique, etc.)
MigrationHandler / MigrationCreator Migration file creation and execution
SeederManager / Seeder Seeder file generation and execution
FactoryManager / Factory / ModelFactory Test data factories using Faker
Grammar / MySqlGrammar / PostgresGrammar / SQLiteGrammar SQL dialect compilation
PrefetchManager Cache warming based on logs or query lists

Installation

Requirements

  • PDO extension with the appropriate driver (pdo_mysql, pdo_pgsql, pdo_sqlite)
  • For MongoDB: mongodb/mongodb via Composer
  • For factories: fakerphp/faker via Composer

Composer

composer require mlangeni/machinjiri

If you plan to use factories:

composer require fakerphp/faker

If you plan to use MongoDB:

composer require mongodb/mongodb

Quick Start

<?php
use Mlangeni\Machinjiri\Core\Database\DatabaseConnection;
use Mlangeni\Machinjiri\Core\Database\Builders\QueryBuilder;

// 1. Configure the connection
DatabaseConnection::setConfig([
    'driver' => 'mysql',
    'host' => '127.0.0.1',
    'port' => 3306,
    'database' => 'my_app',
    'username' => 'root',
    'password' => 'secret',
    'charset' => 'utf8mb4',
]);

// 2. Query data
$users = (new QueryBuilder('users'))
    ->select(['id', 'name', 'email'])
    ->where('active', '=', 1)
    ->orderBy('name', 'ASC')
    ->limit(10)
    ->get();

// 3. Insert data
(new QueryBuilder('users'))->insert([
    'name' => 'Jane Doe',
    'email' => 'jane@example.com',
    'created_at' => date('Y-m-d H:i:s'),
])->execute();

Configuration

Configuration is done once per request via DatabaseConnection::setConfig().

Basic Relational Configuration

DatabaseConnection::setConfig([
    'driver'   => 'mysql',      // mysql | pgsql | sqlite | mongodb | custom
    'host'     => '127.0.0.1',
    'port'     => 3306,
    'database' => 'my_app',
    'username' => 'root',
    'password' => 'secret',
    'charset'  => 'utf8mb4',
    'options'  => [],            // optional PDO driver options
]);

SQLite

DatabaseConnection::setPath(__DIR__ . '/database/database.sqlite');
DatabaseConnection::setConfig(['driver' => 'sqlite']);

You may also pass a directory; the system will create database.sqlite inside it if needed.

PostgreSQL

DatabaseConnection::setConfig([
    'driver'   => 'pgsql',
    'host'     => '127.0.0.1',
    'port'     => 5432,
    'database' => 'my_app',
    'username' => 'postgres',
    'password' => 'secret',
]);

MongoDB

DatabaseConnection::setConfig([
    'driver'   => 'mongodb',
    'host'     => 'localhost',
    'port'     => 27017,
    'username' => null,
    'password' => null,
    'database' => 'my_app',
    'uri'      => 'mongodb://localhost:27017', // optional
    'options'  => [],                          // optional
]);

Custom PDO DSN

DatabaseConnection::setConfig([
    'driver'   => 'custom',
    'dsn'      => 'odbc:Driver={SQL Server};Server=localhost;Database=my_app',
    'username' => 'sa',
    'password' => 'secret',
]);

Pooling Configuration

Pooling is disabled by default. Enable it under the pool key:

DatabaseConnection::setConfig([
    'driver' => 'mysql',
    // ... connection details ...
    'pool' => [
        'enabled'         => true,
        'max_connections' => 20,
        'min_connections' => 2,
        'idle_timeout'    => 60,     // seconds
        'wait_timeout'    => 5,      // seconds to wait for a free connection
        'persistent'      => false,  // use PDO::ATTR_PERSISTENT
    ],
]);

Note: Pooling is process-local in PHP-FPM. Since PHP tears down state at the end of each request, the pool only lives for the duration of a single request unless you use persistent PDO connections in a long-running worker (Swoole, RoadRunner, FrankenPHP, etc.). Pooling is most beneficial in long-running environments.

Basic Usage

Query Builder

The QueryBuilder class is the primary interface for relational queries.

SELECT

use Mlangeni\Machinjiri\Core\Database\Builders\QueryBuilder;

// All columns
$rows = (new QueryBuilder('users'))->get();

// Specific columns
$rows = (new QueryBuilder('users'))
    ->select(['id', 'name'])
    ->get();

// First row only
$user = (new QueryBuilder('users'))
    ->where('id', '=', 1)
    ->first(); // returns array or null

WHERE clauses

// Simple equality
(new QueryBuilder('users'))->where('email', '=', 'a@b.com')->get();

// Comparison operators: =, !=, >, >=, <, <=, like, in, is null
(new QueryBuilder('products'))->where('price', '>', 100)->get();

// AND / OR
(new QueryBuilder('users'))
    ->where('active', '=', 1)
    ->orWhere('role', '=', 'admin')
    ->get();

// WHERE IN
(new QueryBuilder('users'))->whereIn('id', [1, 2, 3])->get();

// WHERE NULL
(new QueryBuilder('users'))->whereNull('deleted_at')->get();

INSERT

(new QueryBuilder('users'))->insert([
    'name'  => 'Jane',
    'email' => 'jane@example.com',
])->execute();
// Returns ['rowCount' => 1, 'lastInsertId' => 42]

UPDATE

(new QueryBuilder('users'))
    ->where('id', '=', 1)
    ->update(['name' => 'Janet'])
    ->execute();

DELETE

(new QueryBuilder('users'))
    ->where('id', '=', 1)
    ->delete()
    ->execute();

ORDER / LIMIT / OFFSET

(new QueryBuilder('users'))
    ->orderBy('created_at', 'DESC')
    ->limit(20)
    ->offset(40)
    ->get();

Raw Queries

For complex SQL, use DatabaseConnection::executeQuery():

use Mlangeni\Machinjiri\Core\Database\DatabaseConnection;

$stmt = DatabaseConnection::executeQuery(
    'SELECT * FROM users WHERE created_at > ? AND role = ?',
    ['2024-01-01', 'admin']
);

$rows = $stmt->fetchAll();

Or, to borrow a connection and run arbitrary logic:

DatabaseConnection::withConnection(function (PDO $pdo) {
    $stmt = $pdo->query('SELECT 1');
    return $stmt->fetchColumn();
});

Transactions

use Mlangeni\Machinjiri\Core\Database\DatabaseConnection;

try {
    DatabaseConnection::beginTransaction();

    (new QueryBuilder('accounts'))->where('id', '=', 1)->update(['balance' => 900])->execute();
    (new QueryBuilder('accounts'))->where('id', '=', 2)->update(['balance' => 1100])->execute();

    DatabaseConnection::commit();
} catch (\Throwable $e) {
    DatabaseConnection::rollback();
    throw $e;
}

Important: Transactions hold a borrowed connection until commit/rollback. Do not call commit() or rollback() without an active transaction — the system will throw a DatabaseException.

Intermediate Usage

Joins

(new QueryBuilder('users'))
    ->select(['users.id', 'users.name', 'orders.total'])
    ->join('orders', 'orders.user_id', '=', 'users.id')
    ->where('orders.total', '>', 100)
    ->get();

// LEFT JOIN shortcut
(new QueryBuilder('users'))
    ->select(['users.id', 'orders.id AS order_id'])
    ->leftJoin('orders', 'orders.user_id', '=', 'users.id')
    ->get();

Aggregates

$count = (new QueryBuilder('users'))->where('active', '=', 1)->count();
$sum   = (new QueryBuilder('orders'))->sum('total');
$avg   = (new QueryBuilder('orders'))->avg('total');
$min   = (new QueryBuilder('orders'))->min('total');
$max   = (new QueryBuilder('orders'))->max('total');
$distinct = (new QueryBuilder('users'))->count('email', true);

Date Range Helpers

(new QueryBuilder('orders'))->whereCurrentWeek('created_at')->get();
(new QueryBuilder('orders'))->whereCurrentMonth('created_at')->get();
(new QueryBuilder('orders'))->whereCurrentYear('created_at')->get();
(new QueryBuilder('orders'))->whereDateRange('created_at', '2024-01-01', '2024-12-31')->get();

Subqueries & Nested Where Clauses

(new QueryBuilder('users'))
    ->where('active', '=', 1)
    ->where(function (QueryBuilder $q) {
        $q->where('role', '=', 'admin')
          ->orWhere('role', '=', 'manager');
    })
    ->get();
// Produces: WHERE active = ? AND (role = ? OR role = ?)

MongoDB Support

The MongoQueryBuilder mirrors the relational API, but works against MongoDB collections.

use Mlangeni\Machinjiri\Core\Database\Builders\MongoQueryBuilder;

$config = [
    'uri'      => 'mongodb://localhost:27017',
    'database' => 'my_app',
];

$users = (new MongoQueryBuilder('users', $config))
    ->where('age', '>', 18)
    ->orderBy('name', 'ASC')
    ->limit(10)
    ->get();

// Insert
(new MongoQueryBuilder('users', $config))->insert([
    'name' => 'Jane',
    'email' => 'jane@example.com',
])->execute();

// Update
(new MongoQueryBuilder('users', $config))
    ->where('_id', '=', 'abc123')
    ->update(['name' => 'Janet'])
    ->execute();

// Delete
(new MongoQueryBuilder('users', $config))
    ->where('active', '=', false)
    ->delete()
    ->execute();

// Count
$count = (new MongoQueryBuilder('users', $config))->count();

Nested MongoDB Conditions

(new MongoQueryBuilder('users', $config))
    ->where('active', '=', true)
    ->where(function ($q) {
        $q->where('role', '=', 'admin')
          ->orWhere('role', '=', 'manager');
    })
    ->get();

Date range helpers work too

(new MongoQueryBuilder('orders', $config))->whereCurrentMonth('created_at')->get();

Advanced Usage

Connection Pooling

DatabaseConnection::setConfig([
    'driver' => 'pgsql',
    // ...
    'pool' => [
        'enabled'         => true,
        'max_connections' => 20,
        'min_connections' => 2,
        'idle_timeout'    => 300,
        'wait_timeout'    => 5,
        'persistent'      => true,
    ],
]);

Under the hood:

  • borrowConnection() returns an idle connection from the pool or creates one if under max_connections.
  • releaseConnection() verifies the connection is still alive (via SELECT 1) before returning it to the pool.
  • Connections that fail the health check are discarded.
  • A register_shutdown_function releases any borrowed connections at end-of-request.
  • The static $pool array holds idle connections keyed by spl_object_hash($pdo).

If pooling is disabled, a single simple connection is reused for the lifetime of the request (similar to old behavior).

Query Caching

CachedQueryBuilder decorates QueryBuilder and caches terminal results.

use Mlangeni\Machinjiri\Core\Artisans\Caching\CacheManager;
use Mlangeni\Machinjiri\Core\Database\Caching\CachedQueryBuilder;

$cache = new CacheManager(/* backend config */);

$users = (new CachedQueryBuilder(new QueryBuilder('users'), $cache))
    ->where('active', '=', 1)
    ->withCache(600, ['users'])   // TTL 600s, tag "users"
    ->get();

Cache Control API

Method Description
withCache(?int $ttl = null, array $tags = []) Force caching with optional TTL/tags
withoutCache() Bypass cache for this query
withStrategy(string $strategy) Use a named TTL strategy from CacheManager

Cache Invalidation

Write operations (insert, update, delete, replace, truncate) automatically:

  1. Extract affected table names from the SQL.
  2. Clear cache tags for those tables.
  3. Increment the cache invalidation metric.

Cache Key Generation

Keys are deterministic:

dbq:<md5(sql)>:<md5(normalized_bindings)>

Array bindings are sorted before hashing so IN (1,2) and IN (2,1) map to the same key.

Stampede Protection

When a miss occurs, CachedQueryBuilder uses CacheManager::remember() — if supported by your backend, this prevents multiple concurrent clients from regenerating the same expensive query simultaneously.

Prefetching / Cache Warming

PrefetchManager warms the cache before traffic hits it.

use Mlangeni\Machinjiri\Core\Database\Caching\PrefetchManager;

$prefetch = new PrefetchManager($cache);

// 1. Register named warmers
$prefetch->registerWarmer('dashboard', function ($cache) {
    $cache->set('dbq:dashboard', [
        (new QueryBuilder('stats'))->count(),
    ], 600);
});
$prefetch->warm('dashboard');
$prefetch->warmAll();

// 2. Warm from a log file
$prefetch->warmFromLog('/var/log/queries.log', 1000);

// 3. Warm from an array of SQL statements
$prefetch->warmFromArray([
    'SELECT * FROM users WHERE active = 1',
    'SELECT COUNT(*) FROM orders',
]);

The warmFromLog method:

  1. Reads the file (up to $limit lines).
  2. Extracts SQL from each line (handles prefixed log lines like [2024-01-01 12:00:00] SELECT ...).
  3. Normalizes literals to ? placeholders so the cache key matches a prepared statement.
  4. Executes each unique normalized query and stores the result.

Custom Grammars

To add dialect-specific behavior, extend Grammar:

use Mlangeni\Machinjiri\Core\Database\Grammars\Grammar;

class OracleGrammar extends Grammar
{
    public function compileAutoIncrement(): string { return ''; }
    public function compileColumnType(string $type, array $parameters = []): string
    {
        return match (strtoupper($type)) {
            'INTEGER' => 'NUMBER(10)',
            'STRING'  => 'VARCHAR2(' . ($parameters[0] ?? 255) . ')',
            default   => $type,
        };
    }
}

To make the QueryBuilder use a custom grammar, you'll need to override DatabaseConnection::getGrammar() or subclass QueryBuilder and set $this->grammar manually in your constructor.

Built-in Grammars

Grammar Used for
MySqlGrammar MySQL / MariaDB
PostgresGrammar PostgreSQL (quoted identifiers, SMALLINT for TINYINT, REAL for FLOAT, etc.)
SQLiteGrammar SQLite (largely type-affinity based)

Schema Management

Blueprint API

Blueprint is the primary schema-definition API. It wraps QueryBuilder and produces DDL.

use Mlangeni\Machinjiri\Core\Database\Schema\Blueprint;

$blueprint = new Blueprint('users', new QueryBuilder('users'));

// Auto-incrementing primary key
$blueprint->id();

// Columns
$blueprint->string('name', 100)->notNull();
$blueprint->string('email', 255)->unique();
$blueprint->text('bio')->nullable();
$blueprint->integer('age');
$blueprint->boolean('active')->default(true);
$blueprint->decimal('balance', 10, 2)->default(0);
$blueprint->timestamp('created_at')->default('CURRENT_TIMESTAMP');
$blueprint->timestamp('updated_at')->nullable();

// Execute
$blueprint->build();

Common Column Types

Method Description
id() Auto-incrementing primary key
uuid() UUID primary key
string($name, $length = 255) VARCHAR
text($name) TEXT
integer($name, $length = 11) INTEGER
tinyInteger($name) TINYINT
bigInteger($name) BIGINT (currently mapped to INT)
unsignedInteger($name) UNSIGNED INT
unsignedBigInteger($name) UNSIGNED BIGINT
boolean($name) TINYINT(1) / SMALLINT
float($name, $precision, $scale) FLOAT
decimal($name, $precision, $scale) DECIMAL
date($name) DATE
dateTime($name) DATETIME
timestamp($name) TIMESTAMP
timestamps() Shortcut for created_at + updated_at
softDeletes() Adds nullable deleted_at
json($name) Mapped to TEXT (no JSON-specific grammar yet)
jsonb($name) Mapped to TEXT
enum($name, $allowed) MySQL ENUM
set($name, $allowed) MySQL SET
binary($name, $length) VARBINARY
point($name) POINT
foreignId($name) UNSIGNED integer for FKs
foreignIdFor($name, $relatedTable) FK column with constraint
slug($name = 'slug') String + auto-index
rememberToken() Nullable VARCHAR(100)
ipAddress($name = 'ip_address') VARCHAR(45)
morphs($name, $nullable) *_type + *_id polymorphic columns

ColumnBuilder Modifiers

Every type method returns a ColumnBuilder with a chainable API:

$blueprint->string('email', 255)
    ->unique()
    ->notNull()
    ->comment('Primary contact email');
Modifier Effect
nullable() Adds NULL
notNull() Adds NOT NULL
default($value) DEFAULT clause (special-cases CURRENT_TIMESTAMP)
unique() Adds UNIQUE
unsigned() Adds UNSIGNED (MySQL)
primaryKey() Adds PRIMARY KEY
comment($text) MySQL COMMENT '...'
autoIncrement() Uses grammar-specific auto-increment token
reference($table, $column) REFERENCES table(column)
foreignKey($refTable, $on = 'id', $onDelete = 'RESTRICT', $onUpdate = 'RESTRICT') Full FK clause

Indexes and Constraints

$blueprint->unique(['email']);
$blueprint->index(['last_name', 'first_name']);
$blueprint->fullText(['bio']);
$blueprint->spatialIndex(['location']);

$blueprint->foreign('user_id', 'fk_orders_user', 'id', 'users', 'CASCADE', 'CASCADE');

ALTER TABLE with Blueprint

$blueprint = new Blueprint('users', new QueryBuilder('users'));
$blueprint->setAction('alter');

$blueprint->string('phone', 20)->nullable();
$blueprint->dropColumn('legacy_field');
$blueprint->build();

DROP TABLE

$blueprint = new Blueprint('old_table', new QueryBuilder('old_table'));
$blueprint->setAction('drop');
$blueprint->build();

Using the QueryBuilder DDL Helpers

QueryBuilder also exposes direct DDL shortcuts:

$query = new QueryBuilder('');
$query->createTable('logs', [
    'id'   => 'INTEGER PRIMARY KEY AUTOINCREMENT',
    'msg'  => 'TEXT NOT NULL',
])->execute();

Or via Blueprint helper:

$query->createTableWithBlueprint('posts', function (Blueprint $b) {
    $b->id();
    $b->string('title');
    $b->text('body');
    $b->timestamps();
});

Migrations

Creating a Migration

use Mlangeni\Machinjiri\Core\Database\Migrations\MigrationCreator;

$creator = new MigrationCreator($container);
$path = $creator->create('create_posts_table');
// → database/migrations/2024_01_15_120000_create_posts_table.php

Generated file:

<?php
use Mlangeni\Machinjiri\Core\Database\Schema\Blueprint;

class CreatePostsTable
{
    public function up(Blueprint $blueprint): void
    {
        $blueprint->id();
        $blueprint->string('title');
        $blueprint->build();
    }

    public function down(Blueprint $blueprint): void
    {
        $blueprint->setAction('drop')->build();
    }
}

Running Migrations

use Mlangeni\Machinjiri\Core\Database\Migrations\MigrationHandler;

$handler = new MigrationHandler($container);
$handler->migrate();     // runs all pending migrations
$handler->rollback();    // rolls back the most recent batch

MigrationHandler tracks executed migrations in a migrations table automatically.

Migration File Naming

Files are timestamped: YYYY_MM_DD_HHMMSS_migration_name.php. The class name is derived by dropping the first 4 underscore-delimited parts (the timestamp) and camel-casing the rest.

Custom Migrations (without Blueprint)

You can write raw SQL in a migration:

public function up(): void
{
    DatabaseConnection::executeQuery('CREATE INDEX idx_users_email ON users(email)');
}

If your migration class does not accept a Blueprint in up()/down(), you'll need to adjust your MigrationHandler usage or ensure your migration accepts the argument.

Seeders

Creating a Seeder

use Mlangeni\Machinjiri\Core\Database\Seeder\SeederManager;

$manager = new SeederManager($container);
$manager->make('UsersSeeder');
// → database/seeders/2024_01_15_120000_users_seeder.php

Generated file:

<?php
namespace Mlangeni\Machinjiri\Database\Seeders;

use Mlangeni\Machinjiri\Core\Database\Seeder\Seeder;

class UsersSeeder extends Seeder
{
    public function run(): void
    {
        // Insert seed data here
    }
}

Writing a Seeder

namespace Mlangeni\Machinjiri\Database\Seeders;

use Mlangeni\Machinjiri\Core\Database\Seeder\Seeder;

class UsersSeeder extends Seeder
{
    public function run(): void
    {
        $this->table('users')->insert([
            'name' => 'Admin',
            'email' => 'admin@example.com',
        ])->execute();

        // Call another seeder
        $this->call(PostsSeeder::class);

        // Run within a transaction
        $this->seedWithTransaction(function () {
            $this->table('roles')->insert(['name' => 'admin'])->execute();
        });

        // Count records
        $count = $this->count('users');
    }
}

Seeder provides:

  • table($name) — returns a QueryBuilder for that table
  • query($sql) — run raw SQL
  • call($seeders) — run child seeders
  • seedWithTransaction($callback) — wrap in BEGIN/COMMIT/ROLLBACK
  • count($table) — count rows
  • disableForeignKeyConstraints() / enableForeignKeyConstraints()
  • Mlangeni\Machinjiri\Database\Seeders\... as the default namespace with a fallback to Database\Seeders\...

Running Seeders

$manager->run('users_seeder.php');          // single seeder
$manager->runAll();                         // all seeders
$manager->runAllInTransaction();            // all seeders in a transaction
$manager->refresh();                        // truncate + re-seed everything
$manager->list();                           // list available seeders

Factories

Setting up a Factory

Factories work on top of the standalone Factory class. In addition, FactoryManager gives you file-generation + running from the filesystem.

use Mlangeni\Machinjiri\Core\Database\Factory\Factory;

Factory::define('User', function ($faker) {
    return [
        'name'     => $faker->name,
        'email'    => $faker->unique()->safeEmail,
        'password' => password_hash('password', PASSWORD_DEFAULT),
    ];
});

Creating Records

use Mlangeni\Machinjiri\Core\Database\Factory\Factory;

// Persist one record
$user = Factory::make('User', [], true);

// Persist many
$users = Factory::create('User', 10);

// With attribute overrides
$admins = Factory::create('User', 3, ['role' => 'admin']);

// Without persisting
$blueprints = Factory::make('User', [], false);

Using ModelFactory 

$users = Factory::factoryForModel('User')
    ->count(5)
    ->state('admin')
    ->attributes(['active' => 1])
    ->create();

Using FactoryManager (File-Based)

use Mlangeni\Machinjiri\Core\Database\Factory\FactoryManager;

$manager = new FactoryManager($container);

// Generate a factory file at database/factories/users_factory.php
$manager->make('User');

// Run the factory N times, inserting records
$result = $manager->run('User', 10);

// Generate data without persisting
$fakes = $manager->fake('User', 10);

// Run many factories at once
$manager->runMultiple([
    'User'  => ['count' => 10],
    'Post'  => ['count' => 50, 'attributes' => ['published' => true]],
]);

// List available factory files
$manager->list();

Raw Seeding

Factory::raw('users', ['name' => 'X', 'email' => 'x@example.com']);
Factory::rawMany('users', [
    ['name' => 'A', 'email' => 'a@example.com'],
    ['name' => 'B', 'email' => 'b@example.com'],
]);

Pre-Built Common Factories

FactoryBuilder::defineCommonFactories() provides default definitions for User, Post, and Category. FactoryBuilder::seedCommonData() populates a starter dataset:

use Mlangeni\Machinjiri\Core\Database\Factory\FactoryBuilder;

FactoryBuilder::defineCommonFactories();
FactoryBuilder::defineCommonStates();
$seeded = FactoryBuilder::seedCommonData([
    'users' => 20,
    'posts' => 100,
    'categories' => 10,
]);

Driver-Specific Notes

MySQL

  • Grammar: MySqlGrammar
  • AUTO_INCREMENT is used for auto-increment columns.
  • TIMESTAMP and DATETIME both compile as their own types.
  • TINYINT(1) for booleans.
  • UNSIGNED is supported via ColumnBuilder::unsigned().

PostgreSQL

  • Grammar: PostgresGrammar
  • Identifiers are wrapped in double quotes.
  • TINYINT is compiled to SMALLINT.
  • FLOAT is compiled to REAL.
  • DATETIME is compiled to TIMESTAMP.
  • AUTO_INCREMENT is not emitted; use SERIAL / BIGSERIAL via a custom type if needed.
  • TRUNCATE ... RESTART IDENTITY CASCADE is used by SeederManager::truncateTable.

SQLite

  • Grammar: SQLiteGrammar
  • Type affinity is used: INTEGER for INT/TINYINT/BOOLEAN, TEXT for DATE/DATETIME/TIMESTAMP, REAL for FLOAT/DECIMAL.
  • AUTOINCREMENT (used with INTEGER PRIMARY KEY).
  • The grammar ignores table options.

MongoDB

  • Uses a separate MongoQueryBuilder and MongoAdapter.
  • Connection is managed as a singleton by DatabaseConnection::getInstance() when driver is mongodb.
  • Filter/sort/projection options are compiled using MongoDB's native operators ($eq, $ne, $gt, $gte, $lt, $lte, $regex, $in, $and, $or, $exists).
  • _id is automatically stringified on read.
  • like is translated to $regex.

Best Practices

  1. Configure once per process. Call setConfig() once, ideally in a bootstrap file.
  2. Use withConnection() for borrowed work. For long-running processes, use withConnection() so your connection is always released back to the pool, even on exceptions.
  3. Use transactions for multi-step writes. Call beginTransaction()/commit()/rollback() explicitly.
  4. Always bind values. Never interpolate user input into SQL; where(..., '=', $value) and executeQuery($sql, $params) bind automatically.
  5. Reuse builder instances or create fresh ones. Builder instances reset their state after execute()/get(), so they're safe to chain.
  6. Keep cache TTLs aligned to write frequency. Use withCache(60, ['table']) for hot data, longer for reference tables.
  7. Tag your caches. The automatic table-name extraction only catches tables referenced in the SQL. Add custom tags for joins from related models or computed aggregates.
  8. Health-check pooled connections. The pool does this automatically via SELECT 1, but make sure your idle_timeout is shorter than your database server's wait_timeout.
  9. Use Blueprint over string DDL. It's safer across dialects and lets you switch drivers without rewriting schema code.
  10. Register migrations/seeders/factories in source control. They're the schema/data contract for your application.

Troubleshooting

Database configuration not set. Call setConfig() first.

Call DatabaseConnection::setConfig([...]) before any query. This is typically a bootstrap issue.

Missing required configuration: host

For MySQL/Postgres, the driver requires host, database, username. Provide all three (plus password if your user has one).

No free connection available after X seconds

Your pool is exhausted. Options:

  • Increase max_connections.
  • Reduce query duration.
  • Ensure every borrowConnection() has a matching releaseConnection() (use withConnection()).
  • Check for long-running transactions holding connections.

No active transaction to commit/rollback.

You called commit() or rollback() without a matching beginTransaction(). Wrap in try/finally.

Cannot unserialize a singleton.

DatabaseConnection blocks unserialize() by design. Don't serialize connection objects.

MongoDB: A MongoAdapter instance or config array is required.

Pass either an existing MongoAdapter instance or a config array to MongoQueryBuilder. Alternatively, configure the driver as mongodb and call DatabaseConnection::getInstance() to get the singleton MongoDB\Client.

Migrations directory does not exist

Create database/migrations/ (or configure Container::getRootPath() correctly). The MigrationCreator expects the directory to exist.

Seeder class '...' not found in file.

Verify:

  • The generated file lives in database/seeders/.
  • The class name in the file matches the filename convention: 2024_..._users_seeder.php → UsersSeeder.
  • If you use the fallback Database\Seeders\... namespace, ensure PSR-4 autoloading is configured for it.

Query results look "wrong" for IN order

The cache key sorts IN arrays before hashing. So IN (1,2,3) and IN (3,2,1) share a cache entry. Result order is determined by the database, not the builder. Use orderBy() if order matters.

Factories throw Faker library is required

Install fakerphp/faker:

composer require fakerphp/faker --dev

API Reference

DatabaseConnection

Method Description
setConfig(array $config) Configure the driver, credentials, and pool
setPath(string $path) Set SQLite path
getInstance() Get a PDO or MongoDB\Client
withConnection(callable $cb) Borrow a connection, run callback, release it
executeQuery(string $sql, array $params) Prepare/execute/bind
beginTransaction() / commit() / rollback() Transaction lifecycle
getDriver() Current driver string
getGrammar() Current Grammar instance
releaseConnection(PDO $conn) Return a borrowed connection to the pool
shutdown() Release all borrowed connections (auto-registered)

QueryBuilder

Method Returns
select(array $columns) QueryBuilder
distinct() QueryBuilder
join/leftJoin(...) QueryBuilder
where/orWhere($col, $op, $val) QueryBuilder
whereIn/whereNull(...) QueryBuilder
orderBy($col, $dir) QueryBuilder
limit($n) / offset($n) QueryBuilder
groupBy($col) QueryBuilder
insert/update/delete() QueryBuilder
get() array
first() ?array
count/sum/avg/min/max(...) scalar
whereCurrentWeek/Month/Year(...) QueryBuilder
whereDateRange($col, $start, $end) QueryBuilder
compileSelect() SQL string
getBindings() array
getGrammar() Grammar
Column helpers: id/string/integer/float/decimal/date/dateTime/timestamp/foreignId/boolean/text ColumnBuilder

MongoQueryBuilder

Mirrors QueryBuilder but for MongoDB. get()/first() return arrays; insert/update/delete return operation summaries (insertedId, matched, modified, deleted).

Blueprint

Category Methods
Table options setAction, ifNotExists, temporary, comment, engine, charset, collation, autoIncrement
Columns id, uuid, string, text, integer, tinyInteger, bigInteger, unsignedInteger, unsignedBigInteger, boolean, float, decimal, date, dateTime, timestamp, json, jsonb, enum, set, binary, point
Relationships foreignId, foreignIdFor, foreign
Constraints primary, unique, index, fullText, spatialIndex
Timestamps timestamps, softDeletes, dropTimestamps, dropSoftDeletes
Alterations dropColumn, renameColumn, dropPrimary, dropUnique, dropIndex, dropForeign
Helpers slug, morphs, rememberToken, ipAddress, currencyAmount, percentage, auditColumns
Execute build()

ColumnBuilder

Method Effect
nullable() NULL
notNull() NOT NULL
default($value) DEFAULT ...
unique() UNIQUE
unsigned() UNSIGNED
primaryKey() PRIMARY KEY
comment($text) COMMENT '...'
autoIncrement() Grammar-specific auto-increment
reference($table, $column) REFERENCES ...
foreignKey(...) FK with ON DELETE/ON UPDATE
__toString() Serialized SQL fragment

MigrationHandler

Method Description
migrate() Run pending migrations
rollback() Rollback latest batch
migrateFiles(array $files) Run specific migrations
getRanMigrations(bool $all = false) Query migration record
getMigrationFiles() List migration files
getMigrationClassName($file) Derive PHP class from filename

SeederManager

Method Description
make($name, $overwrite = false) Generate a seeder file
makeMultiple(array $names, $overwrite = false) Generate many
run($name) Run one seeder
runAll() Run all seeders
runAllInTransaction() Run all seeders in a transaction
refresh(array $tables = [], array $seeders = []) Truncate + reseed
list() List available seeders
getStatistics() Internal run statistics
registerAutoload() Register a PSR-4-style autoloader

Factory / ModelFactory / FactoryManager

Method Description
Factory::define($model, callable) Register a model definition
Factory::state($model, $name, callable) Register a state
Factory::make($model, $attrs, $persist) Build one (optionally persist)
Factory::create($model, $count, $attrs) Build many (persist)
Factory::raw($table, $data) Insert raw attributes
Factory::factoryForModel($model) Fluent ModelFactory
ModelFactory::count($n) Set count
ModelFactory::state($name) Apply a state
ModelFactory::attributes($attrs) Override attributes
ModelFactory::create() / make() Build (and persist)
FactoryManager::make($model) Generate a factory file
FactoryManager::run($model, $count, $attrs) Persist fake data
FactoryManager::fake($model, $count) Generate without persisting

Grammar Interface

Method Description
compileAutoIncrement() Auto-increment token
compileColumnType($type, $params) Column type compilation
wrapTable($table) Identifier quoting for tables
wrapColumn($column) Identifier quoting for columns
compileCreateTable($table, $columns, $options) CREATE TABLE SQL
compileAlterTable(...) ALTER TABLE SQL
compileDropTable($table) DROP TABLE SQL
compileTruncateTable($table) TRUNCATE TABLE SQL

Appendix: End-to-End Example

<?php
require __DIR__ . '/vendor/autoload.php';

use Mlangeni\Machinjiri\Core\Database\DatabaseConnection;
use Mlangeni\Machinjiri\Core\Database\Builders\QueryBuilder;
use Mlangeni\Machinjiri\Core\Database\Schema\Blueprint;
use Mlangeni\Machinjiri\Core\Database\Factory\Factory;

// --- 1. Connect ---
DatabaseConnection::setConfig([
    'driver'   => 'sqlite',
    'pool'     => ['enabled' => false],
]);
DatabaseConnection::setPath(__DIR__ . '/app.sqlite');

// --- 2. Schema ---
$blueprint = new Blueprint('users', new QueryBuilder('users'));
$blueprint->id();
$blueprint->string('name')->notNull();
$blueprint->string('email')->unique()->notNull();
$blueprint->boolean('active')->default(true);
$blueprint->timestamps();
$blueprint->build();

// --- 3. Seed a bit of data ---
Factory::define('User', function ($faker) {
    return [
        'name'  => $faker->name,
        'email' => $faker->unique()->safeEmail,
        'active' => true,
    ];
});
Factory::create('User', 5);

// --- 4. Query ---
$active = (new QueryBuilder('users'))
    ->select(['id', 'name', 'email'])
    ->where('active', '=', 1)
    ->orderBy('name')
    ->get();

foreach ($active as $user) {
    echo $user['id'] . ' — ' . $user['name'] . PHP_EOL;
}

// --- 5. Transaction ---
try {
    DatabaseConnection::beginTransaction();
    (new QueryBuilder('users'))->where('id', '=', 1)->update(['name' => 'Updated'])->execute();
    DatabaseConnection::commit();
} catch (\Throwable $e) {
    DatabaseConnection::rollback();
    throw $e;
}

Machinjiri Docs

Clone this wiki locally