(image created by Adminer)
NotORM is a PHP library for simple working with data in the database. The most interesting feature is very easy work with table relationships. The overall performance is also very important, and NotORM can actually run faster than a native driver. NotORM for databases is like SimpleXML for XML documents.
composer require vrana/notorm or Download
Latest version v2.0.0 from 2026-10-19, upgrade from NotORM 1
NotORM provides a very intuitive API. The examples show the API on a simple database that is included in the download (tests/software.sql).
require "vendor/autoload.php"; // composer require vrana/notorm
$pdo = new PDO("mysql:dbname=software");
$db = new NotORM\NotORM($pdo);
NotORM uses the PDO driver to access the database. All classes are in the NotORM namespace.
foreach ($db->application() as $application) { // get all applications
echo "$application[title]\n"; // print application title
}
Use a method call to get rows from the table. Use square brackets to access a column value.
$applications = $db->application()
->select("id, title")
->where("web LIKE ?", "https://%")
->order("title")
->limit(10)
;
foreach ($applications as $id => $application) {
echo "$application[title]\n";
}
The example gets the first ten applications with an HTTPS URL ordered by title.
$application = $db->application[31]; // get by primary key
$application = $db->application("title = ?", "Adminer")->fetch();
echo $application->author["name"] . "\n"; // get name of the application author
foreach ($application->application_tag() as $application_tag) { // get all tags of $application
echo $application_tag->tag["name"] . "\n"; // print the tag name
}
Simple work with relationships is a killer feature of NotORM. Method call ->() on a row object gets all rows from the referencing table, member access -> stands for the table referenced by the current row, and array access [] always gets a value in the row. Check out the performance of this code.
// get all applications ordered by author's name
foreach ($db->application()->order("author.name") as $application) {
echo $application->author["name"] . ": $application[title]\n";
}
If you need to filter or order rows by a column in a referenced table then simply use the dot notation.
echo $db->application()->max("id"); // get maximum ID
foreach ($db->application() as $application) {
// get count of each application's tags
echo $application->application_tag()->count("*") . "\n";
}
All grouping functions are supported.
$db = new NotORM\NotORM($pdo, $structure = null, $cache = null) | Get database representation |
$db->debug = true | Call error_log("$file:$line:$query; -- $parameters") before executing a query
|
$db->debug = $callback | Call $callback($query, $parameters) before executing a query; if $callback returns false then the query is not executed
|
$db->debugTimer = $callback | Call $callback() after executing a query
|
$db->rowClass = ClassName::class | Derive row objects from this class (defaults to NotORM\Row::class)
|
$db->jsonAsArray = true | Serialize results to a JSON array instead of an object indexed by primary keys |
$db->transaction = $command | Assign 'BEGIN', 'COMMIT' or 'ROLLBACK' to start or stop a transaction
|
$table = $db->$tableName() | Get representation of table $tableName
|
$table = $db->$tableName($where, ...$parameters) | Shortcut for $db->$tableName()->where()
|
$table->where($where, ...$parameters) | Set WHERE (explained later)
|
$table->and($where, ...$parameters) | Add AND condition
|
$table->or($where, ...$parameters) | Add OR condition
|
$table->order($columns, ...$moreColumns) | Set ORDER BY, can be expression ("column DESC, id DESC") or array (["column DESC", "id DESC"])
|
$table->order("") | Reset previously set order |
$table->select($columns, ...$moreColumns) | Set retrieved columns, can be expression ("col, MD5(col) AS hash")
|
$table->select("") | Reset previously set select columns |
$table->limit($limit, $offset = null) | Set LIMIT and OFFSET
|
$table->group($columns, $having = "") | Set GROUP BY and HAVING, retrieves the grouped columns if select is not used
|
$table->union($table2, $all = false) | Create UNION, following order and limit apply to the whole union
|
$table->lock($exclusive = true) | Append FOR UPDATE (default) or LOCK IN SHARE MODE (non-exclusive)
|
$array = $table->fetchPairs($key, $value) | Fetch all values to associative array |
$array = $table->fetchPairs($key) | Fetch all rows to associative array |
$array = $table->fetchPairs([$key1, $key2], $value) | Fetch to nested associative array $array[$key1][$key2], also without $value
|
count($table) | Get number of rows in result set |
(string) $table | Get SQL query |
$table($where, ...$parameters) | Shortcut for $table->where()
|
Repetitive calls of where, order and select are possible – each call appends values to the previous calls (where by AND). These methods plus group and limit provide a fluent interface, so $table->where()->order()->limit() is possible (in any order). All methods are lazy – the query is not executed until necessary. Methods select, order and group also accept NotORM\Literal with parameters.
The $where parameter can contain ? or :name which is bound by PDO to $parameters (so no manual escaping is required). The ...$parameters can be one array, one associative array or zero or more scalars. If the question mark and colon are missing but $parameters are still passed then the behavior is this:
$table->where("field", "x") | Translated to field = 'x' (with automatic escaping)
|
$table->where("field", null) | Translated to field IS NULL
|
$table->where("field", ["x", "y"]) | Translated to field IN ('x', 'y') (with automatic escaping)
|
$table->where("NOT field", ["x", "y"]) | Translated to NOT field IN ('x', 'y') (with automatic escaping)
|
$table->where("field", ["x", null]) | Translated to field IN ('x', NULL) OR field IS NULL
|
$table->where("field", []) | Matches no rows ("NOT field" matches all non-null values)
|
$table->where("field", $db->$tableName()) | Translated to "field IN (SELECT $primary FROM $tableName)"
|
$table->where("field", $db->$tableName()->select($column)) | Translated to "field IN (SELECT $column FROM $tableName)"
|
$table->where("field > ? AND field < ?", "x", "y") | Bound by PDO |
$table->where("(field1, field2)", [[1, 2], [3, 4]]) | Translated to (field1, field2) IN ((1, 2), (3, 4)) (with automatic escaping)
|
$table->where(["field" => "x", "field2 > ?" => 2]) | Translated to field = 'x' AND field2 > ? (with automatic escaping and binding)
|
$table->where("(field", "x")->and("field2", "y")->where(")") | Opens and closes parentheses: ((field = 'x') AND (field2 = 'y')), useful with or
|
MySQL executes the subquery first and passes the list of values to IN because it is usually faster.
If the dot notation is used for a column anywhere in the query ("$table.$column") then NotORM automatically creates a left join to the referenced table. Even references across several tables are possible ("$table1.$table2.$column"). Referencing tables can be accessed by colon: $applications->select("COUNT(application_tag:tag_id)").
The result also supports aggregation:
$table->count("*") | Get number of rows |
$table->count("DISTINCT $column") | Get number of distinct values |
$table->sum($column) | Get sum of values |
$table->min($column) | Get minimum value |
$table->max($column) | Get maximum value |
$table->aggregation("GROUP_CONCAT($column)") | Run any aggregation function |
foreach ($table as $id => $row) | Iterate all rows in result |
$row = $db->{$tableName}[$id] | Get single row with ID $id from table $tableName (null if the row does not exist)
|
$row = $table[$id] | Get row with primary key $id from the result
|
$row = $table[[$column => $value]] | Get first row with $column equal to $value
|
$row = $table->fetch() | Get next row from the result (null if there are no more rows)
|
$data = $table->fetch($column) | Get single column from next row |
$data = $row[$column] | Get data from column $column
|
$row2 = $row->$tableName | Get row from referenced table (null if the row does not exist)
|
$table2 = $row->$tableName() | Get result from referencing table |
$table2 = $row->$tableName($where, ...$parameters) | Shortcut for $row->$tableName()->where()
|
$table2 = $row->$tableName()->via($column) | Use non-default column (e.g. created_by)
|
foreach ($row as $column => $data) | Iterate all values in row |
count($row) | Get number of columns in row |
(string) $row | Get primary key value, the values of a multi-column primary key are separated by comma |
$row["author_id"] gets the numerical value of the column, $row->author gets row representing the author from the table of authors.
$row->$tableName() builds "SELECT * FROM $tableName WHERE {$myTable}_id = $row[$primary]". The returned object can be customized as usual table results (with order, limit, ...) and then executed. All results are lazy and execute only a single query for each set of rows (the result is stored in memory, so following modifications will not affect it).
Both results and rows can be passed to json_encode().
The object passed as the second argument of the NotORM\NotORM constructor must implement the NotORM\Structure interface:
getPrimary($table) | Get primary key of a table in $db->$table(), an array of columns for a multi-column primary key
|
getReferencingColumn($name, $table) | Get column holding foreign key in $table[$id]->$name()
|
getReferencingTable($name, $table) | Get target table in $table[$id]->$name()
|
getReferencedColumn($name, $table) | Get column holding foreign key in $table[$id]->$name
|
getReferencedTable($name, $table) | Get table holding foreign key in $table[$id]->$name
|
getSequence($table) | Get sequence name used to get the ID after insert (null if the name is not needed, '' to not get the ID)
|
NotORM defines a structure suitable for databases with some convention (this is the default structure):
$structure = new NotORM\Structure\Convention(
primary: 'id',
foreign: '%s_id',
table: '%s',
prefix: '',
sequence: null, // e.g. '%s_id_seq' in PostgreSQL
);
There is also an auto-discovery class for MySQL, SQLite, PostgreSQL, MS SQL and Oracle. It reads meta-information from the database, which slows things down (but it can be cached).
$structure = new NotORM\Structure\Discovery($pdo, foreign: '%s');
// cache the discovered keys, the same cache can also remember the used columns
$cache = new NotORM\Cache\File('notorm.cache');
$structure = new NotORM\Structure\Discovery($pdo, $cache);
$db = new NotORM\NotORM($pdo, $structure, $cache);
Clear the cache after changing primary or foreign keys of existing tables.
See more about structure.
The object passed as the third argument of the NotORM\NotORM constructor must implement the NotORM\Cache interface (default null stands for no cache).
load($key) | Get data stored in the cache (return null if there is no data) |
save($key, $data) | Save data to cache |
NotORM defines the following caches:
new NotORM\Cache\Session | Uses $_SESSION["NotORM"]
|
new NotORM\Cache\File($filename) | Serializes to $filename
|
new NotORM\Cache\IncludeFile($filename) | Includes $filename (very fast with OPcache)
|
new NotORM\Cache\Database($pdo) | Uses notorm table in the database
|
new NotORM\Cache\Memcache($memcache) | Uses NotORM. prefix in Memcache
|
new NotORM\Cache\Memcached($memcached) | Uses NotORM. prefix in Memcached
|
new NotORM\Cache\APCu | Uses NotORM. prefix in APCu
|
The table used by NotORM\Cache\Database must already exist (the script should not have privileges to create it anyway):
CREATE TABLE notorm (
id varchar(255) NOT NULL,
data text NOT NULL,
PRIMARY KEY (id)
)
NotORM does not escalate errors. A non-existing table produces an SQL error that is reported by PDO conforming to PDO::ATTR_ERRMODE. A non-existing column produces the same warning as an attempt to access a non-existing key in an array.
NotORM uses neither repetitive queries nor joins, because they can be slow.
One of the biggest bottlenecks in communication with databases is repetitive queries. Each round-trip to send a query costs some time, and sending queries in a loop is a very bad practice.
// this is a wrong approach
foreach ($pdo->query("SELECT * FROM application") as $application) {
$author = $pdo->query("SELECT * FROM author WHERE id = $application[author_id]")->fetch();
}
Joins are much better but they have drawbacks as well:
To explain the second point, consider the following query getting all applications with their tags:
SELECT application.*, tag.*
FROM application
LEFT JOIN application_tag ON application.id = application_tag.application_id
LEFT JOIN tag ON application_tag.tag_id = tag.id
ORDER BY application.id
| id | author_id | title | web | slogan | id | name |
|---|---|---|---|---|---|---|
| 31 | 11 | Adminer | http://www.adminer.org/ | Database management in single PHP file | 21 | PHP |
| 31 | 11 | Adminer | http://www.adminer.org/ | Database management in single PHP file | 22 | MySQL |
| 32 | 11 | JUSH | http://jush.sourceforge.net/ | JavaScript Syntax Highlighter | 23 | JavaScript |
| 33 | 12 | Nette | http://nettephp.com/ | Nette Framework for PHP 5 | 21 | PHP |
| 34 | 12 | Dibi | http://dibiphp.com/ | Database Abstraction Library for PHP 5 | 21 | PHP |
| 34 | 12 | Dibi | http://dibiphp.com/ | Database Abstraction Library for PHP 5 | 22 | MySQL |
Notice how both the application and tag data are transferred repetitively. This costs time and memory, and it is a huge problem if you transfer long data (like article text or blobs).
NotORM uses a different approach – it sends a constant number of simple queries (one query for each table type).
foreach ($db->application()->limit(4) as $application) {
echo "$application[title]\n";
foreach ($application->application_tag() as $application_tag) {
echo "\t" . $application_tag->tag["name"] . "\n";
}
}
This code, which gets all applications and their tags, sends just three simple queries:
SELECT * FROM application LIMIT 4;
SELECT * FROM application_tag WHERE (application_tag.application_id IN (31, 32, 33, 34));
SELECT * FROM tag WHERE (tag.id IN (21, 22, 23));
The volume of transferred data is minimized and the number of queries stays low no matter how many rows are processed.
Another bottleneck in database communication is selecting columns which are not used afterwards, usually by SELECT *. First of all, NotORM has the select method which can be used to limit retrieved columns. However, this method does not need to be used if you use a cache to store the information about used columns. Take this example:
$db = new NotORM\NotORM($pdo, cache: new NotORM\Cache\Session);
foreach ($db->application() as $application) {
echo "$application[title]\n";
}
The first query will be SELECT * FROM application. Then NotORM stores the information about used columns in the cache, and all subsequent requests will issue SELECT id, title FROM application. If you change the script and add more columns then one extra SELECT * will be issued, and all subsequent queries will be optimal again.
Please note that the cache key is ($table, $where) and that no columns are ever removed from the cache (except the case when the cached columns would cause an error). You have to clear the cache after removing some columns from the script to get optimal queries.
NotORM puts the values directly to the queries, for example IN (31, 32, 33, 34). Oracle parses each such query separately and keeps it in its shared pool, which is slow with many different values. Let Oracle replace the values by bind variables itself so that the queries differing only in the values share one parsed statement:
$pdo->exec("ALTER SESSION SET CURSOR_SHARING = FORCE");
NotORM uses a simple convention for table and column names by default:
| Tables | table |
|---|---|
| Primary keys | id |
| Foreign keys | table_id (target table) |
| Table prefix | (none) |
It is, however, possible to use any other convention – plural table names, primary key including the table name and so on. You just need to create an object implementing the NotORM\Structure interface or use a NotORM\Structure\Convention object with custom parameters:
$structure = new NotORM\Structure\Convention(
primary: "id_%s", // id_$table
foreign: "id_%s", // id_$table
table: "%ss", // {$table}s
prefix: "wp_", // wp_$table
);
$db = new NotORM\NotORM($pdo, $structure);
Simple conventions can be described by constructor parameters (%1$s or first %s stands for the first method argument, %2$s for the second). PostgreSQL also needs the sequence parameter (e.g. "%s_id_seq") to get the ID of inserted rows; '' disables getting it. Exceptions from these conventions can be defined by an extension class. Let's say that the database uses a common convention, but there are columns created_by_id and modified_by_id in several tables all linking to the table user:
class UserStructure extends NotORM\Structure\Convention {
function getReferencedTable(string $name, string $table): string {
return match ($name) {
"created_by", "modified_by" => "user",
default => parent::getReferencedTable($name, $table),
};
}
}
$structure = new UserStructure;
Another example is a reference to a parent. If there is a column parent_id referencing the same table then the following convention allows accessing parents easily:
class ParentStructure extends NotORM\Structure\Convention {
function getReferencedTable(string $name, string $table): string {
return match ($name) {
"parent" => $table,
default => parent::getReferencedTable($name, $table),
};
}
}
There is also an auto-discovery class NotORM\Structure\Discovery for MySQL, SQLite, PostgreSQL, MS SQL and Oracle which can work with tables in the database without any convention if they define foreign keys. It, however, costs some time to discover the primary and foreign keys from meta-information. Only single-column foreign keys are supported.
NotORM supports data modification (insert, update and delete). No data validation is performed by NotORM, but all database errors are reported by the standard PDO error reporting.
$row = $db->$tableName()->insert($array) | Insert a row into table $tableName and return the inserted row
|
$row = $db->$tableName()->insert($array, ...) | Insert multiple rows by a single INSERT and return the first row
|
$affected = $db->$tableName()->insertMulti([$array, ...]) | Insert multiple rows by a single INSERT and return the number of inserted rows
|
$affected = $db->$tableName()->insert($result) | Insert all rows of the result into $tableName using INSERT ... SELECT
|
$affected = $table->update($array) | Update all rows in table result |
$affected = $table->insertUpdate($unique, $insert, $update = []) | Insert row or update if it already exists (by $insert if $update is empty)
|
$affected = $table->delete() | Delete all rows in table result |
$affected = $row->update($array) | Update single row |
$affected = $row->update() | Update single row by real modifications ($row[$column] = $value or $row->$tableName = $row2)
|
$affected = $row->delete() | Delete single row |
$id = $db->$tableName()->insertId() | Get last insert ID |
$db->freeze = true | Disable persistence |
All methods return false in case of an error (if PDO does not throw an exception). The $array parameter holds column names in keys and data in values. Values are automatically quoted, DateTime objects are formatted as Y-m-d H:i:s, SQL literals can be passed as objects of class NotORM\Literal, for example:
$array = [
"title" => "NotORM",
"author_id" => null,
"created" => new NotORM\Literal("NOW()"),
];
NotORM\Literal also accepts question marks and parameters: new NotORM\Literal("NOW() - INTERVAL ? DAY", $days).
Rows inserted through a referencing result ($row->$tableName()->insert($array)) get the foreign key automatically.
Disabling persistence by freeze is useful, for example, before sending the data to the template.
// insert application
$application = $db->application()->insert([
"id" => 35, // omit or null for auto_increment
"author_id" => 11,
"title" => "NotORM",
]);
$application->application_tag()->insert(["tag_id" => 21]); // insert its tag
$application["web"] = "https://www.notorm.com/";
$application->update(); // only web will be saved, no other columns
$db->application()->insertUpdate(
["id" => 35], // unique key
["title" => "NotORM"], // insert values if the row doesn't exist
["modified" => new NotORM\Literal("NOW()")] // update values otherwise
);
$application->delete();
This code runs the following queries:
INSERT INTO application (id, author_id, title) VALUES (35, 11, 'NotORM');
INSERT INTO application_tag (tag_id, application_id) VALUES (21, 35);
UPDATE application SET web = 'https://www.notorm.com/' WHERE (id = 35);
INSERT INTO application (id, title) VALUES (35, 'NotORM') ON DUPLICATE KEY UPDATE modified = NOW();
DELETE FROM application WHERE (id = 35);
Databases other than MySQL try to insert the row first and update it if the insert fails on a duplicate key.
Does NotORM require primary keys in all tables?
No. If the column returned by getPrimary method does not exist in the table or if it is not included in retrieved data (specified by select method) then the returned rows are indexed by their order. Then it is not possible to reference other tables with ->() from this row.
Does NotORM support multi-column primary keys?
Yes. Method getPrimary can return an array of columns (NotORM\Structure\Discovery does it automatically) and $row->update() and $row->delete() then use all of them. The rows of such a table are indexed by their order and it is not possible to reference other tables with ->() from them. What NotORM does not support is multi-column foreign keys. So it is valid to define a two-column primary key in an M:N table, but it is not possible to use a foreign key pointing to (type, id_type) – this is rarely used. Methods getReferencingColumn and getReferencedColumn must return a single column.
Does NotORM automatically escape table and column names?
No, NotORM doesn't escape any identifiers (in contrast to data, which is always escaped), so it is your responsibility to escape an identifier if the column or table name collides with an SQL keyword. For example, if a column has the name order (which is an SQL keyword) then you have to call where("`order` IS NOT NULL") for MySQL. Returned column names are, however, always without quotes, so you always access $row["order"]. Tables are even more tricky – if the table has a name select then you have to call $db->{"`select`"}(). Some escaping can be also done in NotORM\Structure implementation. The best approach is to avoid using the SQL keywords for identifiers. If you want to sort the data based on a user supplied column then use a whitelist: if (in_array($_GET["order"], $allowedOrders)) { $result->order($_GET["order"]); }.
Does NotORM forbid some table names?
If the table name collides with a method of the row (starting with two underscores or update, delete, count, offsetExists, offsetGet, offsetSet, offsetUnset, jsonSerialize and getIterator) then it can be accessed as $row->__call('offsetGet', []). There are no magic readable properties used by NotORM.
Are referenced data always retrieved for all rows in the result?
Yes. If you use $row->$tableName or $row->$tableName() then relations for all rows in the result set will be retrieved. If you need the information only for some rows then you can retrieve it directly:
$first = true;
foreach ($db->application() as $application) {
if ($first) { // we want to display tags only for the first application in a list
// this would retrieve tags for all applications: $application_tags = $application->application_tag();
// this will retrieve tags only for the first application
$application_tags = $db->application_tag("application_id", $application["id"]);
// process $application_tags here
$first = false;
}
// process $application here
}
Is it possible to have more columns pointing to the same table?
Yes, it's used in the demo database – the table application has columns author_id and maintainer_id both pointing to the table author. Getting all applications maintained by some author is simple: $author->application()->via('maintainer_id'). Getting the maintainer of an application is slightly more involved. If you use NotORM\Structure\Discovery then you don't need to do anything, and you can simply use $application->maintainer_id['name']. If you use NotORM\Structure\Convention then you need to extend this class:
class SoftwareConvention extends NotORM\Structure\Convention {
function getReferencedTable(string $name, string $table): string {
return parent::getReferencedTable(match ($name) {
'maintainer' => 'author',
default => $name,
}, $table);
}
}
$db = new NotORM\NotORM($pdo, new SoftwareConvention);
$maintainer = $db->application[31]->maintainer;
How to create a plain array from a NotORM result?
You can use iterator_to_array:
// inner call converts the rows, outer call converts the fields
array_map('iterator_to_array', iterator_to_array($db->application()));
json_encode on the NotORM result. It creates an object indexed by primary keys; set $db->jsonAsArray = true to get an array.
What about memory consumption?
NotORM stores all data retrieved from the database in memory. This is the same approach as buffered queries use (enabled by default for MySQL). To work with extremely large result sets (for example, to generate Sitemaps), it is better to retrieve them in chunks, for example with $table->limit(1000). Your database server will be glad too:
$limit = 1000;
$id = 0;
do {
// "WHERE id > ?" is faster than a big offset
$applications = $db->application("id > ?", $id)->order("id")->limit($limit);
foreach ($applications as $id => $application) {
// process the result here
}
} while (count($applications) == $limit);
How to upgrade from NotORM 1?
NotORM 2 moved the classes to the namespace NotORM (e.g. NotORM_Result is now NotORM\Result), renamed insert_update, insert_multi and insert_id to insertUpdate, insertMulti and insertId, declares native types and fetch returns null instead of false after the last row. The script upgrade.php rewrites your code: php vendor/vrana/notorm/upgrade/upgrade.php src/ (with --dry-run to only print what would change). It changes the class and method names, adds the return types required in classes extending NotORM classes and compares fetch with null where it is sure that it's NotORM's. It reports what it can't decide, e.g. include "NotORM.php", which should be replaced by Composer's require "vendor/autoload.php". The files are rewritten in place, so commit them first and review the diff. The script installs PHP-Parser on its first run.
Object-relational mapping (ORM) systems usually require defining classes for entities (often mapping to tables), which is a boring and repetitive task. Some of them can generate these classes from the database (or vice versa), which is just more boring work – you have to run regeneration after each change.
ORM is also usually the wrong place for data validation – if you want consistent data then you need to validate it on the database level. Otherwise, it can get inconsistent, for example, after access from the database console. Most checks on the database level are simple:
varchar(40).NOT NULL.CHECK clause or by trigger.More complex checks such as e-mail validation can be processed in the form handler because they need to display the error message as close to the error as possible (by JavaScript or PHP).
An administration interface to manage data in the whole database can be created with Adminer Editor for free.
ORMs also usually do not help with efficient reading of relationship data, which is the most common task in many database applications. They sometimes solve the inefficiency by data caching, which is not an optimal approach for two reasons:
The best approach is to efficiently get data directly from the primary storage, which is exactly what NotORM does.
© 2010 Jakub Vrána, jakub@vrana.cz