README

A minimal, dependency - free ActiveRecord implementation for PHP using PDO. Designed for rapid development, security, and maximum portability - just drop in a single file and start working with your database safely and efficiently.

Why MicroActiveRecord?

Zero dependencies: No frameworks or external libraries required. Just one file is enough for full functionality.
Security by design: All queries use prepared statements and strict type conversion, protecting your application from SQL injection and data corruption.
Autonomous and flexible: Models auto-detect table names, fields, and types. You can extend the base class or use it directly for any table.
Portable: Works with MySQL, MariaDB, SQLite, PostgreSQL, MSSQL (sqlsrv/dblib), and Oracle (oci) out of the box.
Developer-friendly: Fluent, chainable query builder with expressive syntax. Easy to read, easy to maintain.
Extensible: Add your own models, customize logging, or override behaviors as needed.

Supported Databases and DSN Examples

MySQL: mysql:host=localhost;dbname=testdb;charset=utf8mb4
MariaDB: mysql:host=localhost;dbname=testdb;charset=utf8mb4 (MariaDB is fully supported via the MySQL driver)
SQLite: sqlite:/path/to/database.db
PostgreSQL: pgsql:host=localhost;port=5432;dbname=testdb;user=...;password=...
MSSQL: sqlsrv:Server=localhost;Database=testdb (Windows) or dblib:host=localhost;dbname=testdb (Linux)
Oracle: oci:dbname=//localhost:1521/XE;charset=UTF8

Note: For MSSQL and Oracle, the appropriate PDO driver must be installed and enabled in your PHP environment.

Getting Started

Copy the ActiveRecord.php and DbConn.php files (and optionally Model.php and ModelCollection.php) into your project.
Create and inject your PDO connection:

use Wnikk\MicroActiveRecord\DbConn;
use Wnikk\MicroActiveRecord\ActiveRecord;

// Example for MySQL/MariaDB
DbConn::setConfig([
    'dsn' => 'mysql:host=localhost;dbname=testdb;charset=utf8mb4',
    'user' => 'dbuser',
    'password' => 'dbpass',
    'options' => [PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC]
]);

// Or second example for MySQL/MariaDB
// $pdo = new PDO('mysql:host=localhost;dbname=testdb;charset=utf8mb4', 'user', 'pass');
// DbConn::setPdo($pdo);

// Example for MSSQL
//$pdo = new PDO('sqlsrv:Server=localhost;Database=testdb', 'user', 'pass');
//DbConn::setPdo($pdo);

// Example for Oracle
//$pdo = new PDO('oci:dbname=//localhost:1521/XE;charset=UTF8', 'user', 'pass');
//DbConn::setPdo($pdo);

// Now you can use ActiveRecord directly:
$users = ActiveRecord::table('users')
    ->where('status', 'active')
    ->get();

Installation with Composer (optional)

You do not need Composer for minimal usage - just copy the files as described above. However, if you prefer to use Composer for dependency management and autoloading, you can install MicroActiveRecord as a package:

composer require wnikk/micro-active-record

Composer will handle autoloading for you, so you can use the classes directly in your project.

Usage Examples

Optional: Set PDO connection multi connections (using DbConn class)

$pdo = new PDO('sqlite::memory:');
DbConn::setPdo($pdo, 'second_connection');

$users = ActiveRecord::table('users')
    ->setConnection('second_connection')
    ->where('status', 'active')
    ->get();

Basic Select (without DbConn only ActiveRecord+PDO)

$pdo = new PDO('sqlite::memory:');
$users = ActiveRecord::setConnection($pdo)
    ->table('users')
    ->where('status', 'active')
    ->get();

Select with class for table

class User extends ActiveRecord {}
$users = User::where('status', 'active')
    ->get();

Insert

$id = ActiveRecord::table('users')->insert([
    'login' => 'foo',
    'status' => 'active',
    'created_at' => time()
]);

Update

ActiveRecord::table('users')
    ->where('status', 'waiting')
    ->where('profile', 'LIKE', '%confirm%')
    ->update(['status' => 'active']);

Update with class for table

class User extends ActiveRecord {}
// Auto detect table name "users"
User::where('id', 5)->update(['status' => 'inactive']);

Delete

ActiveRecord::table('users')
    ->where('id', 5)
    ->delete();

Complex Query

$recentAdmins = ActiveRecord::table('users')
    ->where('created_at', '>=', time()-3600)
    ->whereIn('status', ['active', 'hidden'])
    ->where(function($q) {
        $q->where('type', 'root')->orWhere('type', 'admin');
    })
    ->orderBy('id', 'DESC')
    ->get();

Call Raw Query

ActiveRecord::query('CREATE INDEX idx_pname ON Users (LastName, FirstName);')->execute();

Get Raw SQL

# For get SQL query and bindings add toSql() before any execution method
[$sql, $bindings] = ActiveRecord::table('users')
    ->where('status', 'active')
    ->toSql()
    ->get();

Logging Queries

$log = []; 

ActiveRecord::table('users')
    ->setLogger(function($sql, $bindings, $result) use (&$log) {
        $log[] = ['sql' => $sql, 'bindings' => $bindings, 'result' => $result];
    })
    ->where('status', 'active')
    ->get();

print_r($log);

Add join with alias and select specific fields

$users = ActiveRecord::table('users', 'u')
    ->join('profiles p', 'p.user_id = u.id', 'LEFT')
    ->where('u.status', 'active')
    ->selectRaw('u.*, p.bio')
    ->get();

Security and Data Safety

All input is type-checked and converted according to the table schema. Invalid data (e.g., malformed dates, wrong enum values) throws an exception.
Prepared statements are used everywhere, so user data is always safe from SQL injection.
Schema auto-detection ensures that only valid fields are used in queries.

Error Handling and Diagnostics

ActiveRecord provides advanced error handling and diagnostics for robust application development:

showException (public property, default: true):
- If true (default), all errors and validation issues throw exceptions as usual.
- If false, no exceptions are thrown during query execution. Instead, the method returns null and error details are stored in the lastErrors property.
lastErrors (public property):
- An array containing details of the last error(s) that occurred during query execution or validation. Each error includes the type, message, code, stack trace, SQL, and bindings.
lastQuery (public property):
- An array with the last executed SQL query and its bindings, regardless of success or failure.

Example:

$ar = new ActiveRecord('users');
$ar->returnException = false;
$result = $ar->insert(['login' => null]); // login is NOT NULL
if ($result === null) {
    print_r($ar->lastErrors); // See error details
    print_r($ar->lastQuery);  // See the last attempted SQL
}

This makes it easy to build user-friendly APIs or admin panels where you want to handle errors gracefully without interrupting the application flow.

API Reference

Query Builder Methods

All methods are chainable and can be called statically (e.g., ActiveRecord::table(...)->where(...)) or on an instance.

table(string $table, ?string $alias = null) - Set the table name (with optional alias).
query(?string $sql = null, ?array $params = null) - Start a new query builder or use a raw SQL query.
toSql() - Return the SQL and bindings instead of executing.
join($table, string $on, string $type = 'INNER', ?string $alias = null) - Add a JOIN clause.
whereRaw(string $expression, array $bindings = [], string $boolean = 'AND') - Add a raw SQL WHERE condition.
where(string|array|ArrayObject|callable $field, string|null $op = null, mixed|null $value = null) - Add a WHERE condition.
orWhere(string|callable $field, string|null $op = null, mixed|null $value = null) - Add an OR WHERE condition.
whereIn(string $field, array $values) - Add a WHERE IN condition.
orWhereIn(string $field, array $values) - Add an OR WHERE IN condition.
whereNull(string $field) - Add a WHERE ... IS NULL condition.
whereNotNull(string $field) - Add a WHERE ... IS NOT NULL condition.
select(string $fields) - Set SELECT fields (comma-separated or '*').
selectRaw(string $expression) - Set a raw SELECT expression.
orderBy(string $field, string $direction = 'ASC') - Set ORDER BY clause.
orderByRaw(string $expression) - Set a raw ORDER BY clause.
groupBy(string|array $fields) - Set GROUP BY clause.
groupByRaw(string $expression) - Set a raw GROUP BY clause.
limit(int $limit, int $offsetOrPage = 0, bool $pageMode = false) - Set LIMIT and OFFSET (or page mode).
having($field, $op = null, $value = null) - Add a HAVING condition.
orHaving($field, $op = null, $value = null) - Add an OR HAVING condition.
havingRaw(string $expression, array $bindings = [], string $boolean = 'AND') - Add a raw HAVING condition.

Execution Methods

get() - Get all results as an ArrayObject (or custom collection class).
getOne() - Get a single value (first row, first column).
getLine() - Get the first row as ArrayObject (or null).
find($id) - Find a record by primary key value.
insert(array $data) - Insert a row and return the last insert id.
update(array|ArrayObject $data) - Update rows matching the current query.
delete() - Delete rows matching the current query.
execute() - Execute a raw SQL query with optional bindings.
createTable(bool $ifNotExists = false) - Create a table in the database based on the current schema.

Utility Methods

setConnection(PDO $conn) - Set a PDO connection directly.
setLogger(callable $logger) - Set a logger callback for queries and results.
setRowClass(string $class) - Set the class for one row result.
setCollectionClass(string $class) - Set the class for a collection of rows.

Advanced Usage

Custom Models

You can extend ActiveRecord or Model to add business logic, validation, or computed properties:

use Wnikk\MicroActiveRecord\ActiveRecord;
class User extends ActiveRecord {
    // Add custom methods or override behaviors
}

Using with Different Databases

Just change the DSN in your config or inject a different PDO. All features work with MySQL, MariaDB, SQLite, PostgreSQL, MSSQL (sqlsrv/dblib), and Oracle (oci).

Schema Definition

You can define columns and types in your model for stricter validation:

class User extends ActiveRecord {
    protected string $table = 'global_users';
    protected array $columns = [
        'id' => 'int primary key auto_increment',
        'login' => 'varchar(255) not null',
        'status' => "enum('active','inactive') not null default 'active'",
        'created_at' => 'datetime not null',
    ];
}

FAQ

Q: Can I use this in production?
A: Yes! It is designed for safety and performance. All queries are parameterized and types are checked.

Q: What if I need more features?
A: You can extend the base class or add your own methods. The code is simple and well-documented.

Q: Is it compatible with frameworks?
A: Yes, but it is designed to be framework-agnostic. You can use it anywhere PHP and PDO are available.

wnikk / micro-active-record

Maintainers

Details