[RFC] DB Abstraction Layer

[geggleto]

I know we all love different DB ORM layers... I think it would be nice to have some sort of standard so we can easily swap vendors when building our applications.

I think this ties in with the concept of application bundles as well.

I think a good base would simply expose a set of methods for CRUD... This is very similar to a Table Row Gateway.

Example implementation 1

interface CRUDInterface {
    public function getPdo(); /* returns \Pdo */
    public function select($columns = '', $from = '', $where = ''); /* returns array[] */
    public function update(array $properties, $table = '', $where = '');  /* returns int */
    public function delete($table = '', $where = ''); /* returns int */
    public function insert(array $properties, $table = ''); /* returns int */
}

Example implementation 2

interface CRUDInterface {
    public function getPdo(); /* returns \Pdo */
    public function setTable($table = '');
    public function select($columns = '', $where = ''); /* returns array[] */
    public function update(array $properties, $where = '');  /* returns int */
    public function delete($where = ''); /* returns int */
    public function insert(array $properties); /* returns int */
}

What get's tricky is the return types of these and what the method signatures should be.

The Goal

The goal of this would not be to get different DB ORM Vendor's to adopt this interface. We are simply attempting to define a set of methods that we can all use with adapter libraries. I don't think Community bundles can happen without a standardized DB implementation.

Looking forward to your comments!

[ArtBIT]

I hate to nitpick here, but method names that are defined in this interface make sense in the context of a database interface, but since there's CRUD in the name of the interface, CRUD friendly method names would be more appropriate: create, read, update and delete. Further more, getPdo and setTable should belong to another interface, extending the base CRUD interface, and $where parameter needs to be more than a simple string in order to be abstract enough to work with different database engines.

Heh, sorry about the first handbrake, but let's test this democratic process ;)

[marcj]

I'd rather define a standard EntityManager interface instead of a crud. Defining such a Crud interface is extremely difficult because all arguments are usually very different depending on the framework. Some want to have a string for $where, some want a Criteria object, some array etc.

[geggleto]

@marcj hmmm wouldn't an EntityManager be quite difficult to standardize?

[marcj]

I guess less difficult than crud :)

[geggleto]

@marcj The idea is to have an interface that I can type-hint against in different projects.

I am not longer writing code to a specific Database ORM... but instead to the Interface and then I can pop-out/in any ORM with the correct adapter.

I am quite sick and tired of having to redo Persistence layer code because someone want's to switch to a new ORM. Let's write code against 1 interface, then just use adapters to bridge to different ORMs. An Entity Manager would be way out of scope.

[marcj]

Yeah but persistence layer are usually quite different from its architecture. Some are table based, some objects/entities. $columns is very SQL related. Other storage may prefer using $fields. :P I guess it's not possible to find a consensus. Perhaps on a more specific pattern like EntityManager for ORMs.

[geggleto]

SQLCrudInterface ;) ...

[OddGreg]

So the goal, as stated, is to define a common interface for use in a library of adapters, yes? Somehow I feel that any reference to PDO would be outside of a reasonable expectation from an interface, but because PDO is ubiquitous then perhaps getPDO() and/or setPDO() methods would be left to the needs of the implementation.

May I presume that the layer at which this interface becomes useful at the tail of this chain:

database_lib<--database_abstraction_layer<--database_abstraction_interface<--database_adapter

Is this the intent? If so, then would an example chain resemble:

sqlite<--PDO<--our_crud_interface_standard<--???

At this point, I'm not suggesting anything at all, just looking for clarity because I feel like I'm missing something, and I don't want to complicate the issue.

[geggleto]

@OddGreg your example of sqllite <--- Pdo / ORM <-- Common Interface is exactly the use case.

I am mostly going for a common interface that can sit in front of PDO or an ORM [most ORMs use PDO don't they?] ... Reason being ... ORMs are of different qualities... and some scale better than others, some you just end up frustrated with... But with ORMs you are more or less married with them atm unless you rewrite a significant part of your code-base...

I guess the community either didn't understand my intention OR just hates the idea of a unified DB interface that isn't PDO. Don't get me wrong, I love PDO... however I don't often need fancy objects, just arrays usually since I mostly work with APIs.

So there would be a few different implementations...

• PDO
• Eloquent
• Spot2 ...etc

Then if I build a library that requires Db access, I type-hint the interface and include it into any other project. Because I use the type-hint I can use any of the different ORMs... I might have a different ORM for each project I work on because of reasons. It will greatly aid in code-reuse if we can come to some form of agreement on what it should look like.

[TorbenKoehn]

I'd not define any part of a ORM/ODM/DBAL in any PSR/CDS that exists, ever.

A DBAL/ORM/ODM is way too specific to be standardized.

Never forget: Once you have a standard, Developers need to implement them in order to be an accepted library anywhere. Once you standardize things like this, you'll stop real good advances and new interfaces with new libraries since everyone has almost the same.

A CrudInterface may be interesting, but it shouldn't be coupled to DBAL/ORM/ODM in any way.

<?php

namespace Psr;

class CrudInterface
{

    public function create(array $data);
    public function read($id);
    public function update($id, array $data);
    public function delete($id);
}

but I think even this is already way too much implementation-specific (Bound to IDs and Data-Arrays)

We don't need a standard for everything and CDS shouldn't become a half framework.

[odan]

It looks like this RFC is not about a "ORM", it's more about a SQL Query Builder.

Here are some example projects: https://laravel.com/docs/5.1/queries https://github.com/FaaPz/Slim-PDO https://github.com/fpdo/fluentpdo https://github.com/cakephp/database

[sagikazarmark]

Okay, lemme react step by step.

• Title says DB Abstraction, still, it's talking about CRUD. How is CRUD a DB abstraction layer?
• EntityManager is still too high level abstraction. How to solve storage, persistence? EntityManager suggests an ORM: relations kind of assume a relational database. This is still not a DB abstraction layer.
• Then SQL Query Builder standard? Why not an HTML Builder standard then?

Standards are good if you want to provide compatibility between two or more things. However, the level of abstraction still matters, and in this case (from my POV) everything above a persistence layer standard (which would be the (tm) DB abstraction layer standard) should not be subject of standardization IMO.