README

A lightweight, secure ORM for PHP 8.0+ that makes database operations simple without the overhead of a full-featured framework.

Version 2.1.1 | Changelog | License: GPL-3.0

by Paige Julianne Sullivan paigejulianne.com | GitHub

Features

Minimal footprint: Single-file ORM (~1200 lines)
Multiple connections: Support for multiple named database connections
Secure by default: SQL injection protection via prepared statements and identifier validation
Zero dependencies: Only requires PHP 8.0+ and PDO
Auto-save: Changes are automatically persisted when objects go out of scope
Cross-database: Works with MySQL, PostgreSQL, SQLite, and other PDO-supported databases
Transaction support: Begin, commit, and rollback transactions with ease
Dirty checking: Track which fields have been modified

Installation

Via Composer (Recommended)

composer require paigejulianne/picoorm

Manual Installation

Download PicoORM.php and include it in your project:

require_once 'PicoORM.php';

Quick Start

1. Configure Your Database Connection

Create a .connections file in your project root:

[default]
DSN=mysql:host=localhost;dbname=myapp;charset=utf8mb4
USER=myuser
PASS=mypassword

Security Note: Add .connections to your .gitignore to prevent committing credentials!

2. Create a Model Class

Create a class that extends PicoORM, named after your database table:

use PaigeJulianne\PicoORM;

class Users extends PicoORM
{
    // That's it! PicoORM maps this class to the 'users' table
}

3. Start Using It

// Create a new user
$user = new Users();
$user->name = 'Alice';
$user->email = 'alice@example.com';
$user->save();

echo "Created user with ID: " . $user->getId();

// Load an existing user
$user = new Users(1);
echo $user->name; // Output: Alice

// Update a user
$user->name = 'Alice Smith';
$user->save();

// Delete a user
$user->delete();

Configuration

Option 1: Using a `.connections` File (Recommended)

The .connections file uses an INI-like format with sections for each database connection:

# Primary database
[default]
DSN=mysql:host=localhost;dbname=myapp;charset=utf8mb4
USER=app_user
PASS=secure_password

# Analytics database on a separate server
[analytics]
DSN=mysql:host=analytics.example.com;dbname=analytics
USER=analytics_readonly
PASS=analytics_password

# SQLite for local caching
[cache]
DSN=sqlite:/var/cache/myapp/cache.db
USER=
PASS=

PicoORM searches for the .connections file in these locations (in order):

The path specified via setConnectionsFile()
The parent directory of PicoORM.php
The current working directory

PDO Options

You can specify PDO options using the OPTIONS[constant] syntax:

[production]
DSN=mysql:host=db.example.com;dbname=prod
USER=prod_user
PASS=prod_password
OPTIONS[PDO::ATTR_PERSISTENT]=true
OPTIONS[PDO::ATTR_TIMEOUT]=10

Option 2: Programmatic Configuration

Add connections at runtime using addConnection():

use PaigeJulianne\PicoORM;

PicoORM::addConnection(
    'default',
    'mysql:host=localhost;dbname=myapp',
    'username',
    'password',
    [PDO::ATTR_PERSISTENT => true]
);

Option 3: Legacy Global Variables (Deprecated)

For backward compatibility, global variables are still supported:

global $PICOORM_DSN, $PICOORM_USER, $PICOORM_PASS, $PICOORM_OPTIONS;

$PICOORM_DSN = 'mysql:host=localhost;dbname=myapp';
$PICOORM_USER = 'username';
$PICOORM_PASS = 'password';
$PICOORM_OPTIONS = [PDO::ATTR_PERSISTENT => true];

Note: This method is deprecated. Please migrate to .connections file or addConnection().

Working with Models

Defining Models

Create a class extending PicoORM for each database table. The class name (lowercased) becomes the table name:

use PaigeJulianne\PicoORM;

// Maps to the 'products' table
class Products extends PicoORM {}

// Maps to the 'order_items' table
class OrderItems extends PicoORM {}

Custom Table Names

Override the table name using the TABLE_OVERRIDE constant:

class Product extends PicoORM
{
    const TABLE_OVERRIDE = 'shop_products';
}

Specifying a Connection

For models that use a non-default database connection:

class AnalyticsEvent extends PicoORM
{
    const CONNECTION = 'analytics';
}

Creating Records

// Method 1: Set properties individually
$product = new Products();
$product->name = 'Widget';
$product->price = 29.99;
$product->category_id = 5;
$product->save();

// Method 2: Set multiple properties at once
$product = new Products();
$product->setMulti([
    'name' => 'Gadget',
    'price' => 49.99,
    'category_id' => 3
]);
$product->save();

// Get the new record's ID
$newId = Products::getLastInsertId();
// or
$newId = $product->getId();

Loading Records

// Load by primary key (defaults to 'id' column)
$user = new Users(42);

// Load using a different column
$user = new Users('alice@example.com', 'email');

// Check if the record was found
if ($user->getId() === '-1') {
    echo "User not found";
}

Updating Records

$user = new Users(1);
$user->name = 'Updated Name';
$user->email = 'newemail@example.com';
$user->save();

// Or let auto-save handle it when the object is destroyed
function updateUser($id, $name) {
    $user = new Users($id);
    $user->name = $name;
    // save() is called automatically when $user goes out of scope
}

Deleting Records

$user = new Users(1);
$user->delete();

Checking If a Record Exists

// Check by ID
if (Users::exists(42)) {
    echo "User exists";
}

// Check by another column
if (Users::exists('alice@example.com', 'email')) {
    echo "Email is registered";
}

Checking Properties

$user = new Users(1);

if (isset($user->phone)) {
    echo "Phone: " . $user->phone;
} else {
    echo "No phone on file";
}

Refreshing Data

Reload the record from the database to get the latest values:

$user = new Users(1);
// ... some time passes, data may have changed ...
$user->refreshProperties();

Querying Multiple Records

Use getAllObjects() to retrieve multiple records with optional filtering:

// Get all users
$users = Users::getAllObjects();

// Get users with filters
$activeAdmins = Users::getAllObjects('id', [
    ['status', null, '=', 'active'],
    ['role', null, '=', 'admin']
], 'AND');

// Use OR logic
$featured = Products::getAllObjects('id', [
    ['is_featured', null, '=', 1],
    ['is_bestseller', null, '=', 1]
], 'OR');

// Force array return (even for single results)
$results = Users::getAllObjects('id', [], 'AND', true);

Filter Format

Each filter is an array: [column, null, operator, value]

Supported operators: =, !=, <>, <, >, <=, >=, LIKE, NOT LIKE, IN, NOT IN, IS, IS NOT

// Find users with Gmail addresses
$gmailUsers = Users::getAllObjects('id', [
    ['email', null, 'LIKE', '%@gmail.com']
]);

// Find products in a price range
$affordableProducts = Products::getAllObjects('id', [
    ['price', null, '>=', 10],
    ['price', null, '<=', 50]
], 'AND');

Pagination and Ordering

getAllObjects() supports pagination with limit, offset, orderBy, and orderDir parameters:

// Get first 10 users ordered by name
$users = Users::getAllObjects(
    idColumn: 'id',
    filters: [],
    filterGlue: 'AND',
    forceArray: true,
    limit: 10,
    offset: 0,
    orderBy: 'name',
    orderDir: 'ASC'
);

// Get page 2 (records 11-20)
$page2 = Users::getAllObjects('id', [], 'AND', true, 10, 10, 'created_at', 'DESC');

Finder Methods

Finding by Column

// Find all users with a specific role
$admins = Users::findBy('role', 'admin');

// Find with a different operator
$recentUsers = Users::findBy('created_at', '2024-01-01', 'id', '>=');

// Find a single record
$user = Users::findOneBy('email', 'alice@example.com');

if ($user === null) {
    echo "User not found";
}

First or Create (Upsert)

Find a record or create it if it doesn't exist:

// Find user by email, or create with additional attributes
$user = Users::firstOrCreate(
    ['email' => 'alice@example.com'],           // Search criteria
    ['name' => 'Alice', 'role' => 'user']       // Values for new record
);

// The returned user either existed or was just created
echo $user->name;

Update or Create

Find a record and update it, or create if it doesn't exist:

// Update existing user's login time, or create new user
$user = Users::updateOrCreate(
    ['email' => 'alice@example.com'],           // Search criteria
    ['last_login' => date('Y-m-d H:i:s')]       // Values to update/set
);

Counting Records

// Count all records
$totalUsers = Users::count();

// Count with filters
$activeAdmins = Users::count([
    ['role', null, '=', 'admin'],
    ['is_active', null, '=', 1]
], 'AND');

// Count with OR logic
$specialUsers = Users::count([
    ['role', null, '=', 'admin'],
    ['role', null, '=', 'moderator']
], 'OR');

Plucking Column Values

Get an array of values from a single column:

// Get all usernames
$usernames = Users::pluck('username');
// Returns: ['alice', 'bob', 'charlie']

// Get emails of active users
$emails = Users::pluck('email', [
    ['is_active', null, '=', 1]
]);

Data Export

Converting to Array

$user = new Users(1);

// Export all properties
$data = $user->toArray();
// Returns: ['id' => 1, 'name' => 'Alice', 'email' => 'alice@example.com', ...]

// Export specific columns only
$data = $user->toArray(['name', 'email']);
// Returns: ['name' => 'Alice', 'email' => 'alice@example.com']

Dirty Checking

Track which properties have been modified:

$user = new Users(1);

// Check if any changes have been made
$user->isClean();  // true
$user->isDirty();  // false

// Make a change
$user->name = 'New Name';

$user->isDirty();           // true
$user->isDirty('name');     // true
$user->isDirty('email');    // false

// Get all changed properties
$changes = $user->getDirty();
// Returns: ['name' => 'New Name']

// Get original value before changes
$original = $user->getOriginal('name');  // 'Alice'

// Get all original values
$allOriginal = $user->getOriginal();

Fresh Instance

Get a new instance with fresh data from the database (without modifying current instance):

$user = new Users(1);
$user->name = 'Modified';

// Get a fresh copy from database
$freshUser = $user->fresh();

echo $user->name;       // 'Modified' (still has local changes)
echo $freshUser->name;  // 'Alice' (fresh from database)

Atomic Operations

Increment and Decrement

Atomically update numeric columns:

$product = new Products(1);

// Increment view count
$product->increment('view_count');

// Increment by a specific amount
$product->increment('stock', 10);

// Decrement
$product->decrement('stock', 5);

// Works with floats too
$product->increment('price', 0.50);

Transactions

Manual Transaction Control

use PaigeJulianne\PicoORM;

try {
    PicoORM::beginTransaction();

    $user = new Users();
    $user->name = 'Alice';
    $user->save();

    $order = new Orders();
    $order->user_id = $user->getId();
    $order->total = 99.99;
    $order->save();

    PicoORM::commit();
} catch (\Exception $e) {
    PicoORM::rollback();
    throw $e;
}

Transaction Callback

A cleaner approach using a callback:

$result = PicoORM::transaction(function () {
    $user = new Users();
    $user->name = 'Alice';
    $user->save();

    $order = new Orders();
    $order->user_id = $user->getId();
    $order->total = 99.99;
    $order->save();

    return $order->getId();
});

echo "Created order: $result";

If any exception is thrown inside the callback, the transaction is automatically rolled back.

Transaction Status

// Check if currently in a transaction
if (PicoORM::inTransaction()) {
    // ...
}

// Transactions work with multiple connections
PicoORM::beginTransaction('analytics');
// ... operations on analytics connection ...
PicoORM::commit('analytics');

Type Validation

PicoORM automatically validates data types against your database schema, catching type mismatches before they reach the database.

How It Works

When you set a property or save a record, PicoORM:

Fetches the table schema (cached per request)
Validates the value type matches the column type
Throws a TypeError if validation fails

// Assuming 'age' is an INT column in the database
$user = new Users();
$user->name = 'Alice';           // OK - string to VARCHAR
$user->age = 25;                 // OK - int to INT
$user->age = 'twenty-five';      // TypeError: expected integer, got string

// String length validation
// Assuming 'username' is VARCHAR(50)
$user->username = str_repeat('a', 100);  // TypeError: exceeds max length of 50

Disabling Validation

Disable validation for a specific model by overriding the constant:

class LegacyData extends PicoORM
{
    const VALIDATE_TYPES = false;  // Disable type checking
}

Nullable Columns

Null values are only allowed for nullable columns:

// If 'email' is defined as NOT NULL
$user->email = null;  // TypeError: Column 'email' does not allow NULL values

// If 'phone' allows NULL
$user->phone = null;  // OK

Type Coercion Rules

PicoORM allows sensible type coercions:

Database Type	Accepts
INTEGER	`int`, `bool`, numeric strings (`"123"`)
FLOAT/DECIMAL	`float`, `int`, numeric strings
VARCHAR/TEXT	`string`, `int`, `float`, `bool` (auto-converted)
BOOLEAN	`bool`, `0`, `1`, `"0"`, `"1"`

Inspecting Schema

You can inspect the detected schema:

$schema = Users::getTableSchema();

print_r($schema);
// [
//     'id' => ['type' => 'int', 'php_type' => 'integer', 'nullable' => false, ...],
//     'name' => ['type' => 'varchar', 'php_type' => 'string', 'max_length' => 255, ...],
//     ...
// ]

Manual Validation

Validate a value without setting it:

$user = new Users(1);

// Returns true/false without throwing
$isValid = $user->validateColumnValue('age', 'invalid', throw: false);

// Or validate all pending changes
$user->validateAllChanges();

Clearing Schema Cache

If your schema changes during runtime:

// Clear all cached schemas
PicoORM::clearSchemaCache();

// Clear specific table
PicoORM::clearSchemaCache('users');

Custom Queries

For complex queries, use the low-level query methods. Use _DB_ as a placeholder for the table name:

// Fetch a single record
$result = Users::_fetch(
    'SELECT * FROM _DB_ WHERE email = ? AND status = ?',
    ['alice@example.com', 'active']
);

// Fetch multiple records
$results = Users::_fetchAll(
    'SELECT * FROM _DB_ WHERE created_at > ? ORDER BY name',
    ['2024-01-01']
);

// Execute a query (INSERT, UPDATE, DELETE)
Users::_doQuery(
    'UPDATE _DB_ SET last_login = NOW() WHERE id = ?',
    [42]
);

Multiple Database Connections

PicoORM supports connecting to multiple databases simultaneously.

Define Connections

In your .connections file:

[default]
DSN=mysql:host=localhost;dbname=main_app
USER=app_user
PASS=app_password

[analytics]
DSN=mysql:host=analytics-server;dbname=analytics
USER=analytics_user
PASS=analytics_password

[legacy]
DSN=mysql:host=old-server;dbname=legacy_data
USER=legacy_user
PASS=legacy_password

Use Connections in Models

// Uses 'default' connection
class Users extends PicoORM {}

// Uses 'analytics' connection
class PageView extends PicoORM
{
    const CONNECTION = 'analytics';
}

// Uses 'legacy' connection
class OldCustomer extends PicoORM
{
    const CONNECTION = 'legacy';
    const TABLE_OVERRIDE = 'tbl_customers';  // Legacy table name
}

Connection Management

// List all configured connections
$connections = PicoORM::getConnectionNames();
// Returns: ['default', 'analytics', 'legacy']

// Check if a connection exists
if (PicoORM::hasConnection('analytics')) {
    // Analytics connection is configured
}

// Get the connection used by an instance
$pageView = new PageView();
echo $pageView->getConnection(); // Output: analytics

Security

PicoORM includes several security measures:

SQL Injection Protection

Prepared Statements: All values are passed through PDO prepared statements
Identifier Validation: Column names and operators are validated against strict patterns
Operator Whitelist: Only approved SQL operators are allowed in filters

Valid Identifiers

Column and table names must:

Start with a letter (a-z, A-Z) or underscore (_)
Contain only letters, numbers, and underscores

// Valid
$user->first_name = 'Alice';     // OK
$user->address_line_1 = '123';   // OK

// Invalid - will throw InvalidArgumentException
$user->{'first-name'} = 'Alice'; // Error: hyphens not allowed

Credentials Protection

Store credentials in .connections file (not in code)
Add .connections to .gitignore
Use environment-specific connection files in production

Error Handling

PicoORM throws exceptions for error conditions:

use PaigeJulianne\PicoORM;

try {
    $user = new Users(1);
    $user->name = 'New Name';
    $user->save();
} catch (\PDOException $e) {
    // Database connection or query error
    error_log("Database error: " . $e->getMessage());
} catch (\InvalidArgumentException $e) {
    // Invalid column name, operator, or filter
    error_log("Invalid argument: " . $e->getMessage());
} catch (\RuntimeException $e) {
    // Connection not configured, statement preparation failed
    error_log("Runtime error: " . $e->getMessage());
}

Database Compatibility

PicoORM works with any PDO-supported database:

Database	Status	Notes
MySQL	Full	Primary development target
MariaDB	Full	Compatible with MySQL
PostgreSQL	Full	Tested and supported
SQLite	Full	Great for development/testing
SQL Server	Partial	May require adjustments

Note: Column names are escaped with backticks, which is MySQL/MariaDB syntax. For maximum compatibility, use simple alphanumeric column names.

API Reference

Static Methods

Method	Description
`setConnectionsFile($path)`	Set the path to the connections file
`loadConnections()`	Manually load connection configuration
`addConnection($name, $dsn, $user, $pass, $options)`	Add a connection programmatically
`getConnectionNames()`	Get list of configured connection names
`hasConnection($name)`	Check if a connection exists
`exists($id, $column)`	Check if a record exists
`getAllObjects($idColumn, $filters, $glue, $forceArray, $limit, $offset, $orderBy, $orderDir)`	Retrieve multiple records with pagination
`getLastInsertId()`	Get the last auto-increment ID
`count($filters, $filterGlue)`	Count records matching filters
`pluck($column, $filters, $filterGlue)`	Get array of values from a single column
`findBy($column, $value, $idColumn, $operator)`	Find all records matching a column value
`findOneBy($column, $value, $idColumn)`	Find a single record matching a column value
`firstOrCreate($attributes, $values, $idColumn)`	Find or create a record
`updateOrCreate($attributes, $values, $idColumn)`	Find and update, or create a record
`beginTransaction($connectionName)`	Begin a database transaction
`commit($connectionName)`	Commit the current transaction
`rollback($connectionName)`	Roll back the current transaction
`inTransaction($connectionName)`	Check if currently in a transaction
`transaction($callback, $connectionName)`	Execute callback within a transaction
`clearConnectionCache($connectionName)`	Clear cached PDO connections
`getTableSchema($connectionName)`	Get table column definitions
`clearSchemaCache($table)`	Clear cached schema information
`_fetch($sql, $values, $table)`	Fetch single record with custom SQL
`_fetchAll($sql, $values, $table)`	Fetch multiple records with custom SQL
`_doQuery($sql, $values, $table)`	Execute custom SQL

Instance Methods

Method	Description
`getId()`	Get the record's primary key value
`getConnection()`	Get the connection name used by this instance
`save()`	Persist changes to the database
`writeChanges()`	Alias for save()
`delete()`	Delete the record from the database
`refreshProperties()`	Reload data from the database
`setMulti($array)`	Set multiple properties at once
`toArray($columns)`	Export record as associative array
`isDirty($column)`	Check if model has unsaved changes
`isClean()`	Check if model has no unsaved changes
`getDirty()`	Get all changed properties and their values
`getOriginal($column)`	Get original value(s) before modifications
`fresh()`	Return a fresh instance from the database
`increment($column, $amount)`	Atomically increment a column value
`decrement($column, $amount)`	Atomically decrement a column value
`validateColumnValue($column, $value, $throw)`	Validate a value against column type
`validateAllChanges()`	Validate all pending changes

Class Constants

Constant	Description
`TABLE_OVERRIDE`	Override the default table name
`CONNECTION`	Specify which database connection to use
`VALIDATE_TYPES`	Enable/disable type validation (default: true)

Contributing

Contributions are welcome! Please:

Fork the repository
Create a feature branch
Submit a pull request

For bugs and feature requests, use the GitHub issue tracker.

License

PicoORM is released under the GPL-3.0-or-later license.

paigejulianne / picoorm

Maintainers

Details