Home

This document describes a concept for a new Data Access Layer design.

Our design goals are:

• Design around existing challenges in ORM, ActiveRecord and SQL Builders.
• Come up with a clean concept before coding.
• Implementation must be simple for new developers and powerful enough for seasoned geeks.
• Educate developers on efficient database use instead of resorting to "hacks" (pre-fetching etc).
• Any Query. Should be usable with any database schema and produce any request.
• PDO/SQL Vendor support as well as NoSQL vendor support.
• Take full advantage of Vendor features where possible (sub-queries, derived query, expressions)
• Never execute hidden queries.
• Keep low overheads. Small code footprint.
• Allow to fully separate "Business Models" from "Persistence logic".
• Provide reliable way to Unit-test Business Logic and all operations without need of default database.
• Minimum requirements, PHP 5.5+.
• Full unit test coverage, standard compliance, detailed documentation.
• License under MIT.

Intro: Reinventing the faulty ORM concept. (+sub-queries, joins, data-sets, events, active record and n+1 problem)

as posted https://www.reddit.com/r/PHP/comments/4f2epw/reinventing_the_faulty_orm_concept_subqueries/

I have been reading about a lot of pain related to ORM (Object Relational Mappers) and its limitations, so I wanted to brainstorm my concept with you guys on a better DAL (Database Access Layer) design.

The similar concepts are used by Slick (Scala) and DJango DataSets (Python) as well as jOOQ (Java) but I haven't seen anything similar in PHP. I don't want to "fix" or "improve" ORM, but replace all the concept fundamentally with something that's easy to use, flexible to extend and what would scale well. This Concept can take full advantage of SQL database feature as well as cloud-databases which have introduced SQL query language (DocumentDB, MemSQL, Clusterpoint) by shifting the data heavy-lifting towards databases.

Eventually I'd like to convert this concept into a standalone PHP package and distribute it under MIT license.

Please read my 6-point design concept and give me some feedback / criticism:

1. DataSet

ORMs today work on "table" level, which is reverse-engineered from SQL schema. Instead I propose that we work with DataSet. They are very similar to DJango QuerySet and represent a query from your database defined through some joins, unions, conditions, sub-queries (before execution!). DataSet can be converted into data-stream and iterated through. They would always have "id" and have ability to "update" its records.

In other words - DataSet is a object-oriented PHP-based version of SQL VIEW.

A new DataSet can be derived from existing by adding more joins or conditions, e.g. User -> AdminUser.

$admin = new User();
$admin -> addCondition('isAdmin', true);

but also it can be defined through Union/Grouping existing DataSets (folks who do reports will appreciate this!)

2. Active Record with explicit load/save

ActiveRecord is a good concept, but in my proposal it's working with DataSets, rather then tables. This gives us a good option to load records that strictly fall into the set, create new records or edit existing ones.

$u = new AdminUser();
$u->load(20);
$u['name'] = John
$u->save()

I think that loading and saving records must be explicit. This allows developer to have much greater control on those operations. This also allows us to add "hooks" for those events (e.g. beforeSave) for validation and updating related records.

3. Converting actions into queries

The actions of loading and saving should exist as "operations" which developers can take advantage of before they hit the database engine. Such an action can be converted into a sub-query or modified before executing:

$u = new AdminUser();
$ds = $u->getDataSet();
$ds->set('age = age + 1');
$ds->update();

This principle is used extensively in Slick. Above example executes multi-row update. Most NoSQL databases already support multi-row updates, we just need to create a nice object-oriented interface for it.

4. Relations

Relations between DataSets are different to relations between tables, because they are more expressive. User may have a relation "activity" which represents all the operations he have performed. This particular users activity would be a sub-set of all user activity DataSet.

$user -> hasMany('Activity');

By calling $user->ref('Activity') you receive not a bunch of lazy-loaded records, but a single DataSet object, which you can iterate/stream or access through active record.

$u->load(20);
$u->ref('Activity')->delete();

This simple syntax can be used to delete all activity for user 20.

5. Expressions

Similarly how you can use functions in Excel, you should be able to define "expressions" in your data-set. In practice they would be using SubQueries or raw SQL expressions. We can use 4.Relations and 3.Convert DataSet into query to automatically build sub-query without dropping any SQL code at all:

$act = $user->ref('Activity');
$act->addCondition('month(date) = month(now)');

$user -> addExpression('activity_this_month')->set( $act->count() );

$user->load(20);
echo $user['activity_this_month'];

Because $act->count() is actually a sub-query, then value for "activity_this_month" would not need a separate query, but will be inserted into our main query which we use during load().

6. Active Fields

Finally, ability to define expressions can quickly litter our queries and we need a way to define which columns we need through "Active Fields" for DataSet. This affects load() by querying less columns if we do not plan to use them.

Conclusion

The above concept does share some similarities with the ORM, but it is different. I have been crawling google trying to find a right way to describe it, but haven't found anything interesting.

The actual implementation of a concept has a potential to replace a much hated ORM concept. It can once and for all provide a good solution to ORM's N+1 problem and scalability issues.

What do you think? Is this worth implementing?