Download the PHP package webrium/foxdb without Composer

FoxDB v4

FoxDB is the database layer of the Webrium framework, available as a standalone package. It gives you a fluent query builder for writing SQL without strings, an Eloquent-style ORM for working with your data as objects, a schema builder for managing your database structure in PHP, and a migration system for versioning those changes. All of this runs on top of PDO with no external dependencies beyond the driver itself.

What's new in v4

Version 4 is a complete rewrite that adds a full ORM layer and brings FoxDB from a query builder into a proper database toolkit.

• Eloquent ORM — define your tables as PHP classes. Get mass assignment protection, automatic timestamps, attribute casting, dirty tracking (only changed columns are saved), and full JSON serialization out of the box.
• Relations — hasOne, hasMany, belongsTo, belongsToMany, hasManyThrough. Both lazy and eager loading are supported.
• Soft Deletes — mark rows as deleted without actually removing them, and restore them later. The scope is applied automatically, including when the trait is inherited from a parent model.
• Schema Builder — create and modify tables using a fluent Blueprint API instead of writing DDL by hand.
• Migrations — version your schema changes with up() / down() methods and roll them back whenever you need.
• Collection — a rich wrapper around query results with map, filter, sort, chunk, paginate, and full JSON serialization. Calling toArray() on a collection of models correctly uses each model's own serialization, so hidden fields, casts, and loaded relations all behave as expected.
• Multi-driver — MySQL, PostgreSQL, and SQLite all work correctly, with per-driver SQL generation for things like identifier quoting, upserts, and UPDATE syntax.
• Query Log & Hooks — log every query that runs, measure execution time, detect slow queries, and attach beforeQuery / afterQuery callbacks.
• PHPUnit test suite — unit tests for SQL generation and model logic, plus integration tests that run against all three drivers in CI.

Requirements

• PHP 8.1 or higher
• PDO extension for your chosen database: pdo_mysql, pdo_pgsql, or pdo_sqlite

Installation

Table of Contents

• Connection Setup
• Query Builder
  • Fetching rows
  • WHERE conditions
  • JOIN
  • ORDER, GROUP BY, HAVING, LIMIT
  • Aggregates
  • INSERT
  • UPDATE
  • DELETE
  • Pagination
  • Raw SQL
  • Transactions
  • Debug helpers
• Eloquent ORM
  • Defining a Model
  • Mass assignment
  • CRUD operations
  • Dirty tracking
  • Casts
  • Soft Deletes
  • Relations
  • Eager Loading
  • Local Scopes
  • Serialization
• Collection
• Schema Builder
• Migrations
• Query Log
• Error Handling
• Running Tests

Connection Setup

Before you can do anything, tell FoxDB how to connect to your database. You do this once — typically in your application bootstrap file — by calling DB::addConnection() with a configuration array.

For SQLite, you only need the path to the database file:

Multiple connections

If your application talks to more than one database, register each connection under a name and switch between them as needed:

You can also assign a specific connection to a Model by setting $connection on the model class (see Defining a Model).

Query Builder

The query builder lets you construct and execute SQL queries using a fluent PHP API. Every query starts with DB::table('table_name'), which returns a Builder instance. You chain methods to build the query, and finish with a terminal method (get, first, insert, update, etc.) to execute it.

All user-supplied values are passed as PDO bindings — FoxDB never interpolates values directly into SQL, so you are protected from SQL injection without any extra sanitization on your part.

Fetching rows

The most common operation is fetching rows from a table:

When you need to process a very large number of rows without loading them all into memory at once, use chunk or each:

WHERE conditions

FoxDB supports every common SQL condition. The basic form is where(column, value) which assumes =, or where(column, operator, value) for other comparisons:

IN / NOT IN — check if a column value is in a list:

BETWEEN — check if a value falls in a range:

NULL checks:

Raw expressions — when you need SQL that the builder cannot produce:

Date helpers — compare against parts of a datetime column:

Column-to-column comparison:

Grouped conditions — wrap a group of conditions in parentheses by passing a closure:

Subquery conditions:

Shorthand methods — for common patterns, FoxDB provides shorter alternatives:

JOIN

ORDER, GROUP BY, HAVING, LIMIT

Aggregates

Aggregate methods execute the query immediately and return a single value:

INSERT

UPDATE

DELETE

Pagination

Pagination is a common need in any application that displays lists. FoxDB handles the total count, offset, and metadata for you:

The returned object has these properties:

|

|

The rows for this page |

Raw SQL

When you need to run SQL that the builder cannot express, you can execute raw statements directly:

Transactions

Transactions let you group multiple operations so that either all succeed or all are rolled back. The easiest way is to pass a closure to DB::transaction() — FoxDB will automatically commit if the closure returns normally, or roll back if it throws:

If you need more control, you can manage the transaction manually:

Debug helpers

When you need to inspect the SQL that FoxDB is generating, these methods let you do so without running the query (or while running it):

Eloquent ORM

The ORM layer lets you work with your database tables as PHP classes. Each table maps to a Model class, and each row in that table becomes an instance of that class. Instead of writing queries everywhere, you interact with your data through objects.

Defining a Model

Create a class that extends Foxdb\Eloquent\Model. At minimum you only need the class — FoxDB will infer the table name from it. Everything else is optional:

Mass assignment

Mass assignment means setting multiple attributes at once via create() or fill(). FoxDB protects you from accidentally setting columns you did not intend to expose — for example, an is_admin field that a user might inject through a form.

You control this with two properties:

• $fillable — an allowlist. Only these columns can be mass-assigned.
• $guarded — a blocklist. Everything except these columns can be mass-assigned. Set it to [] to allow all columns.

CRUD operations

Creating records:

Reading records:

Updating records:

Deleting records:

Reloading from the database:

Dirty tracking

FoxDB tracks which attributes have changed since the model was last loaded or saved. This is how it knows to only include changed columns in an UPDATE statement:

Casts

Casts tell FoxDB how to convert a raw database value (always a string or null from PDO) into a proper PHP type when you read an attribute. The cast is applied automatically — you never have to convert values manually.

|

|

DateTimeImmutable object |

Soft Deletes

Sometimes you want to "delete" a record without actually removing it from the database — so you can restore it later, or keep a history of what was deleted. FoxDB supports this via the HasSoftDeletes trait.

When you call delete() on a model with soft deletes, FoxDB sets a deleted_at timestamp instead of issuing DELETE. All subsequent queries automatically exclude soft-deleted rows, so they are invisible to the rest of your application unless you explicitly ask for them.

Your table needs a nullable deleted_at column (use $table->softDeletes() in your migration).

Soft deletes also work when HasSoftDeletes is applied on a parent model and inherited by a subclass:

Relations

Relations describe how your tables are connected. You define them as methods on your model that return a relation object. FoxDB then uses these to build the correct JOIN or subquery automatically.

HasMany — one user has many posts (posts.user_id points to users.id):

HasOne — one user has one profile:

BelongsTo — the post knows which user it belongs to (posts.user_id → users.id):

BelongsToMany — users can have many roles, roles can belong to many users, through a pivot table:

HasManyThrough — get all comments on a user's posts, without going through Post:

Lazy loading — relations are loaded the first time you access them and then cached on the instance:

Pivot methods for BelongsToMany:

BelongsTo helpers:

Eager Loading

The problem with lazy loading is that if you load 100 users and then access $user->posts for each one, you end up running 101 queries — one for the users and one per user for their posts. This is the N+1 problem.

Eager loading solves this by loading all the related data in one additional query:

You can eager-load multiple relations at once:

You can also constrain what gets loaded — for example, only published posts:

Eager-loaded relations are included in toArray() output automatically:

Local Scopes

Local scopes let you define reusable query conditions on your model. Define a method prefixed with scope and it becomes chainable as a static call without the prefix:

Serialization

When you want to convert a model — or a collection of models — to an array or JSON (for an API response, for example), use toArray() or toJson(). These methods respect your $hidden fields, apply all $casts, and include any loaded relations.

For a collection:

Important: Do not use (array) $model to convert a model to an array. PHP's object cast exposes internal protected properties with null-byte prefixed keys (\u0000*\u0000table, etc.), which will corrupt your JSON output. Always use ->toArray().

Collection

Collection wraps the array of rows returned by get() and most Model query methods. It implements ArrayAccess, Countable, IteratorAggregate, and JsonSerializable, so you can treat it like an array in most situations while also having a rich set of transformation methods. All transformation methods return a new Collection and leave the original unchanged.

Schema Builder

The Schema Builder lets you define your database structure in PHP rather than writing DDL statements by hand. It automatically generates the correct SQL for your database driver.

Creating a table

Modifying an existing table

Other schema operations

Migrations

Migrations are PHP classes that describe a change to your database schema. Each migration has an up() method that applies the change and a down() method that reverses it. This lets you version your schema alongside your code and roll back changes when needed.

Writing a migration

Running migrations

Query Log

The query log lets you see every SQL statement that FoxDB executes, along with its bindings and execution time. This is useful for debugging, performance profiling, and detecting N+1 issues.

You can also attach hooks that fire before or after every query — useful for logging to an external system, adding metrics, or profiling:

Error Handling

FoxDB throws typed exceptions so you can handle different failure scenarios specifically:

If you prefer not to use exceptions — for example, in a legacy codebase — set 'throw_exceptions' => false when registering the connection. Failed queries will return false instead of throwing.

Running Tests

FoxDB includes a PHPUnit test suite split into two groups:

• Unit tests require no database and test SQL generation and model logic.
• Integration tests run real queries against a database. You choose which driver to use via an environment variable.

CI runs all three drivers automatically on every pull request via GitHub Actions.

License

Apache-2.0 — see LICENSE.