Download the PHP package or81/eloquent without Composer

PHP Query Builder

A lightweight, dependency-free SQL query builder for PHP with a Laravel-style fluent API. Supports MySQL and SQLite, and queries by Jalali (Shamsi) date out of the box.

Every value goes through a bound parameter, so the builder is safe against SQL injection by construction. Only identifiers are interpolated, and those are quoted by the grammar.

Requirements

• PHP >= 8.0
• ext-pdo (pdo_mysql and/or pdo_sqlite)

Installation

Or clone the repository and register the Or81\Eloquent\ namespace against src/ in your own autoloader.

Configuration

From a .env file, with no setup

Put the credentials in a .env file next to your project and start querying. Nothing else is needed:

The file is found by looking in the working directory first, then walking up from the package, which reaches the project root when installed under vendor/. If vlucas/phpdotenv is installed it is used to load the file, exactly as an application would:

Without that package a small built-in reader parses the same file, so nothing has to be installed. Real environment variables ($_ENV, $_SERVER, getenv) always win over the file, which is what you want on a server where the credentials are injected rather than committed.

Recognised keys:

SQLite needs only two:

Point at a .env somewhere else — a directory or the file itself — before the first query:

To see what the environment actually produced:

Overriding it by hand

NDB::configure() sits on top of whatever .env gave you, so you can change one key and leave the rest alone:

Or ignore the environment entirely and state everything:

The connection is opened lazily, on the first query.

Multiple connections

A second connection is configured by name. In .env, prefix its keys with that name:

Only the default connection falls back to the unprefixed DB_* keys, so a typo in a connection name fails loudly instead of quietly pointing at the main database.

Long-running scripts

MySQL closes a connection that has been idle longer than wait_timeout (eight hours by default, but often minutes on shared hosting). The client only finds out on the next statement, which fails with SQLSTATE[HY000]: General error: 2006 MySQL server has gone away. An importer that spends a while on an API call, a file, or a sleep between rounds runs into this.

Nothing has to be done about it: when a statement dies with the connection, it is run once more on a fresh one.

The retry is deliberately narrow, because repeating a statement is only safe when it never reached the server:

• Only a dropped connection is retried - 2006, 2013, a killed or shut-down connection, a broken pipe. A duplicate key or a bad column is thrown at once.
• Only once. A second drop in a row is thrown, rather than looping against a server that is down.
• Never inside a transaction. Everything the transaction had done is already gone, so replaying one statement would write it on top of nothing. The QueryException is thrown instead, with the transaction level reset so the rollBack() in your catch block does not fail on top of it.
• Never on a PDO you handed over with setPdo().

To turn it off and see every drop yourself:

Either way, isLostConnection() tells a dropped connection from a bad query:

Before a batch that follows a long quiet stretch you can check the connection first, so the work does not start on a dead one:

ping() returns false when the server cannot be reached at all, and when a transaction is open - reconnecting there would throw the transaction away silently.

A "gone away" that arrives immediately, on a connection that was busy the whole time, is usually a different problem: a single statement larger than the server's max_allowed_packet. Insert in smaller chunks, or raise the setting.

An existing PDO instance

Managing connections

Getting a builder

There are two ways in, and they return the same query builder. Pick whichever suits the job.

A table, directly. Rows come back as Row objects, so columns are read as properties.

A model. Everything about the table is declared on a class, and rows come back as objects with casts applied. This is the Model half of the package.

Both accept exactly the same clauses, so nothing in the rest of this file changes between them.

Models

A Model describes one table: its name, its key, which columns may be filled, how each column is typed, and how its dates are stored. Extend it and you get the query builder, attribute casting, timestamps, soft deletes and scopes.

What you declare

Nothing is mass assignable until you list it in $fillable. If every attribute of a create() call is rejected the model says so rather than quietly writing a blank row:

Casts

A cast runs both ways. array is encoded to JSON when written and decoded when read; jalali accepts a string, an array, a Jalali or a DateTimeInterface and writes whatever $dateStorage says the column holds. An unknown cast name throws rather than being ignored.

Accessors and mutators

An accessor for a real column receives the cast value and may reshape it. One with no column behind it is computed on the fly; list it in $appends to have it show up in toArray().

Scopes

A scopeSomething() method on the model becomes Something() on the query:

Calling a method that is neither a builder method nor a scope names the scope you would have to write.

Reading and writing

save() writes only the changed columns, and does nothing at all when nothing changed. $timestamps keeps created_at and updated_at current, and respects $dateStorage — a project that stores Jalali text gets Jalali timestamps.

Soft deletes

Relations

Each relation returns a query, so finish it yourself. Eager loading is not included: this is a query builder with a model layer on top, not a full ORM.

Keys are guessed from the class names and can be given explicitly: hasMany(Order::class, 'buyer_id', 'id').

Output

Jalali and DateTimeInterface values become strings on the way out, so the result is always JSON-safe.

Or skip the model

If you want the table-name inference without any of the above, extend NDB instead. You get a plain query builder returning Row objects:

Selecting

Results are objects

get() returns a list of Row objects and first(), find(), findOrFail() and sole() return one, so columns are read as properties:

Array access still works, so nothing written against the older array results has to change:

A Row also has:

A column that is not in the result reads as null; use has() to tell an absent column from one that is really null.

When you want plain arrays after all — for array_column, a CSV writer, or an assertion — ask for them:

pluck(), value() and the aggregates were never rows, and are unchanged.

Single results

find(), findOrFail(), create() and delete($id) look rows up by id. Point them at a different key once:

Where clauses

Every one of these has an orWhere* counterpart.

An operator the builder does not recognise throws rather than compiling into broken SQL, so where('age', '=>', 5) is caught at the call site. Reach for whereRaw() when you need something exotic.

One condition, several columns

The search-box case. Each of these is compiled as a single group, so it cannot leak into the clauses around it:

orWhereAny() and orWhereAll() join the group on with or.

Grouped conditions

Sub-query conditions

Jalali (Shamsi) dates

Query with Persian dates against columns that hold whatever your schema already holds. Nothing needs to change in the database, and no Jalali support is needed from MySQL or SQLite.

Every clause compiles to a half-open range over the plain column, so an index on it is still used:

The column is never wrapped in a function, which is what makes this different from whereDate() / whereYear().

Telling the builder how a column is stored

Two things vary independently: the calendar (Gregorian or Jalali) and the column type (date, datetime, varchar, timestamp). What the builder actually needs to know is which value to compare against, so there are three storage modes:

Jalali::GREGORIAN is the default. Declare anything else:

Per model, so you only say it once:

Per connection, when a whole legacy database stores Jalali text:

Or let the builder work it out by reading one stored value. A Jalali year lands in 1000–1699 and a Gregorian one does not, which is what separates the two. The result is cached for the rest of the process, so the probe runs once per column:

Range comparison relies on the stored text sorting chronologically, so a varchar must hold a zero-padded year-month-day value. A format like Y/n/j (1403/5/6) or d/m/Y is refused with an explanation rather than silently returning wrong rows.

The clauses

whereJalali, whereJalaliDate, whereJalaliBetween, whereJalaliYear and whereJalaliMonth each have an orWhereJalali* counterpart. Values may be a string ('1403/05/26', '1403-5-26', Persian digits), an array ([1403, 5, 26]), a Jalali, or a DateTimeInterface.

Ordering needs nothing special: all three storage modes already sort chronologically, so orderBy('created_at') is correct as it stands.

Jalali output

The Jalali class

A self-contained immutable date, modelled on verta's API but with no dependency on it. Every mutator returns a new instance.

format() accepts Y y m n d j H G i s F M l D N w t L z a A U, and a backslash escapes the next character. z is the 1-based day of the year.

A verta instance can be handed to any of these directly — Jalali::parse($verta) picks up its datetime().

Jalali::now(), today() and the UNIX storage mode all read PHP's default timezone, so set it once at bootstrap:

Joins

Pass a closure for compound conditions. The closure receives a JoinClause, which is a full builder plus on() / orOn():

Sub-query joins:

Grouping, ordering and limits

Aggregates

Pagination and chunking

Reading a table too big for memory

get() builds the whole result set before returning it: a row array and a Row object for every record. On a few hundred thousand rows that is enough to exhaust a default 128 MB limit.

pluck() streams and keeps only the values, so use it whenever you want one column. cursor() streams whole rows, holding one at a time:

PDO buffers by default on MySQL, which would pull the entire result set into memory at execute() and make fetching one row at a time pointless. cursor() turns buffering off for its own duration and puts it back afterwards, including when you break or an exception is thrown. While an unbuffered cursor is open MySQL will not run another query on that connection — finish or leave the loop before querying again, or use a second connection.

Narrowing the query is better still. Loading only the keys you are about to look at costs a fraction of loading them all:

chunk() pages with limit/offset, so rows the callback deletes or reorders shift the pages still to come. When the callback writes to the same table, page by key instead:

chunkById walks in key order and remembers where it got to, so nothing is skipped or seen twice. Pass a column name as the third argument when the key is not id.

Conditional clauses

Inserting

Getting the row back

create() inserts one row and reads it back, so the generated key and any column defaults the database filled in are there straight away:

Plain inserts

Every row of a multi-row insert() must carry the same columns. One that does not is reported by position, rather than becoming a VALUES list the driver rejects with a vaguer message.

Updating and deleting

Both return the number of affected rows.

orderBy() / limit() on an update or delete are MySQL-only. On SQLite the builder throws rather than silently dropping them.

Transactions

Nested transaction() calls use savepoints, so an inner rollback does not discard the outer work.

Raw statements

Use NDB::raw() for a fragment that must not be quoted. Never build one from user input:

Values still belong in bindings. Put a ? where each one goes and pass them in the same order, and the fragment stays safe wherever it is used — in a select, a where, a having, an insert or an update:

This is the fill-in-the-blanks update: write the incoming value only where the column is currently empty, and leave it alone otherwise. See the worked example.

Debugging

Error handling

A failed statement throws Or81\Eloquent\QueryException, carrying the SQL and bindings that caused it:

QueryException is not a PDOException. It extends RuntimeException and keeps the driver's exception as its previous. catch (PDOException $e) will not fire, and $e->errorInfo is not on it — reach for the helpers below instead.

Telling one failure from another

isUniqueViolation() covers MySQL's 1062 and SQLite's 19 / 1555 / 2067, so nothing has to test for a driver code by hand. The raw values are there when you want them:

isLostConnection() is the third of them: it says the server was gone rather than the statement wrong. See long-running scripts for what the connection does about that on its own.

Better still, do not collide

Catching a duplicate to then update is a race: another process can insert the same row between your insert and your update. upsert() settles it in one atomic statement, and the worked example below shows the fill-in-the-blanks version.

Worked examples

Every snippet below is executed by tests/examples_test.php, so they are all known to run.

A filtered, paginated listing

The classic search page: several optional filters, a joined table, a Jalali date range, and a page of results. when() keeps the optional filters from turning into a pile of if statements.

The q filter is wrapped in its own closure on purpose: without it the orWhereLike would escape the group and pull in rows the other filters meant to exclude.

Dashboard counters

A sales report grouped into Jalali months

Neither MySQL nor SQLite knows what a Jalali month is, so group by day in SQL — both have a date() function — and fold the days into months in PHP. One query, correct boundaries.

Top customers, by a correlated sub-select

Only customers who ordered this Jalali year

whereExists keeps this to one query, with no join and no duplicate rows to clean up.

A write batch inside a transaction

insertGetId gives you the new id for the child rows, and the whole thing rolls back together if anything throws.

Throwing anywhere inside the closure rolls the whole batch back:

A legacy table that stores Jalali text

issue_date is a varchar holding 1403/05/26. Let the builder work that out and then query it like any other date column.

Or state it, and skip the probe:

Streaming an export

cursor() fetches one row at a time instead of loading the table into memory, and castJalali converts the dates on the way past.

Bulk updates

Filling in only the blank columns

A partial record arrives and should top up whatever is still missing, without overwriting anything already there. One statement, one round trip, every value bound:

coalesce walks left to right: the stored value wins if it is not blank, the incoming value fills the gap, and the trailing column name keeps the row unchanged when neither has anything. The column names are literals you wrote, so nothing here comes from user input.

If you would rather not write SQL, the same thing as three guarded updates:

Three round trips instead of one, but each condition is spelled out and the return value tells you which columns were actually filled.

Insert, or fill in only the blanks

A student record arrives repeatedly, sometimes more complete than last time. Insert it if it is new; if it is already there, top up only the columns still empty and leave everything else alone.

The obvious shape is insert inside a try, catching the duplicate and updating in the catch. Two things go wrong with that. QueryException is not a PDOException, so catch (PDOException $e) never fires. And even correctly caught, there is a window between the failed insert and the update in which another process can write the same row.

upsert() does the whole thing in one statement:

The third argument lists the only columns an existing row may have written to, so name and student_code are used on insert and ignored on conflict. Each coalesce keeps the stored value when it is not blank, falls back to the incoming one, and leaves the row unchanged when neither has anything.

MySQL needs a unique index on national_code; SQLite needs the same, and uses the column list to find it.

When a write genuinely cannot be saved — a missing NOT NULL, a bad foreign key — that is still an exception, and worth keeping:

The same thing for thousands of rows

The single-row version binds the incoming value with ?. A batch cannot: one placeholder would be one value for every row in the statement. The update clause has to name the incoming value instead, which is what incoming() gives you — values(col) on MySQL, excluded.col on SQLite, evaluated per row.

Every row is judged on its own values inside one statement: a row whose stored city is blank takes the incoming one, a row that already has a city keeps it, and a blank incoming value cannot wipe anything because it is put through the same nullif chain.

Each chunk is one round trip. 500 rows of six columns is 3000 placeholders, comfortably inside MySQL's limit; lower it if your rows are wider. A chunk is all-or-nothing, so a single bad row rejects its whole batch — wrap the call in a try/catch and retry that chunk row by row if you need to isolate the offender.

Walking a large table

Testing

The package ships its own suite, with no PHPUnit or other dependency to install:

Every public method of every class is exercised.

Run one group, or show every assertion:

A single suite is a plain script, so it can be run on its own:

Every suite uses an in-memory SQLite database and runs in its own process, so nothing has to be set up and nothing leaks between them. What each one covers:

Classes

Upgrading from the old DB class

src/DB.php has been removed and replaced by NDB. The differences that matter:

• Values are bound, not interpolated. The old where() built ... '$value' directly into the SQL.
• Configuration is external. Credentials are passed to NDB::configure() instead of being hardcoded as private properties.
• update() works. The old one mixed ? placeholders with named bindings and never executed.
• insertMultiple() is gone; insert() takes either one row or a list of rows, and writes all of them. The old version bound every row but executed once, so only the last survived.
• Failures throw QueryException instead of calling die().
• insert() returns bool; use insertGetId() when you need the new id.
• update() and delete() return the affected row count instead of void.
• get() returns [] rather than false when nothing matches; first() returns null.
• The global dd() function is gone. Use $query->dump() / $query->dd().

Automatic table naming is still there, and now handles BlogPost -> blog_posts, Person -> people and Category -> categories.

Rows became objects

get(), first(), find() and cursor() used to hand back associative arrays and now hand back Row objects. Row implements ArrayAccess, IteratorAggregate, Countable and JsonSerializable, so the things usually done with those arrays keep working unchanged:

Two things do change:

• A strict comparison against an array ($rows === [['id' => 1]]) no longer holds. Call ->toArray(), or ask the query for toBase().
• Functions that require a real array — array_column($rows, 'name'), array_map over the columns of one row — need toBase() or toArray() first.

pluck(), value(), the aggregates, and Connection::select() were never rows and are untouched.

License

MIT. See LICENSE.

Contact

[email protected]