Atlas.Query: Simple. Sensible. SQL.

I am happy to announce that Atlas.Query is now stable and ready for production
use! Installaton is as easy as composer require atlas/query ~1.0.

With Atlas.Query and any PDO instance, you can build and execute your queries in a single fluent series of method calls:

use Atlas\Query\Select;

$rows = Select::new($pdo)
    ->columns('*')
    ->from('posts')
    ->where('id IN ', $ids)
    ->fetchAll();

foreach ($rows as $row) {
    // ...
}

If you prefer, you can exercise fine control over your PDO connection, use a query factory, or build your queries in smaller steps:

use Atlas\Pdo\Connection;
use Atlas\Query\QueryFactory;

$connection = Connection::new(
    'mysql:host=localhost;dbname=testdb',
    'username',
    'password'
);

$queryFactory = new QueryFactory();

$select = $queryFactory->newSelect($connection);
$select->columns('*');
$select->from('posts');
$select->where('id = ', $id);

$row = $select->fetchOne();

Atlas.Query provides the full power of SQL at your fingertips …

$select
    ->columns(...)
    ->from(...)
    ->join(...)
    ->where(...)
    ->groupBy(...)
    ->having(...)
    ->orderBy(...)
    ->limit(...)
    ->offset(...);

… along with UNIONs, paging, sub-selects, inline value binding, and all sorts of fetch and yield styles.

Atlas.Query comes with INSERT, UPDATE, and DELETE builders as well:

use Atlas\Query\Insert;

$insert = Insert::new($pdo);

// insert a row ...
$insert->into('posts')
    ->columns([
        'title' => $title,
        'body' => $body,
    ])
    ->raw('created_at', 'NOW()')
    ->perform();

// ... and get back the autoincrement value:
$post_id = $insert->getLastInsertId();

Do you work on different project with different database backends? Atlas.Query lets you use the same interface for them all, while not restricting you to a common subset of functionality. MySQL, PostgreSQL, SQLite, and SQL Server are all supported explicitly.

And if you discover you need more than just a query system, you’ll have a clear refactoring path towards Atlas.Orm. If you are looking for a modern, stable, easy-to-use query system, try Atlas.Query in your project!


You can read the Reddit commentary on this post here.

Atlas 3.x (“Cassini”) and PHPStorm Completion

I’m proud to announce the release of Atlas.Orm 3.0.0-beta1, along with releases of the supporting Mapper, Table, Query, Cli, and Pdo packages. (Atlas is a data-mapper for your persistence model, not your domain model, in PHP.)

The goal for this release round was “better IDE return typehinting support” and I am happy to say that it has been a great success, though it did take some substantial renaming of classes. Unfortunately, this results in a big break from the prior alpha release; if you already have alpha-based data source skeleton classes, you will need to regenerate them with the new class names. Barring the unforeseen, this marks the first, last, and only time that regeneration will be necessary.

I.

I am not a heavy user of IDEs; I lean more toward text editors like Sublime. However, I work with people who use the PHPStorm IDE extensively, and I have come to appreciate some of its features.

One of those features is code autocompletion. For example, if you type $foo = new Foo(), and then refer to $foo later, the IDE will pop up a list of methods and properties on that object.

This is very convenient, except that the IDE has to know what class is being referenced, in order to figure out what the hinting should be. If you are using a non- or loosely-return-typed factory/locator/container, which is what everything in PHP land does, the IDE cannot know by default how to map the requested name to an actual class. In this example …

class Factory
{
    public static function new($class)
    {
        return new $class();
    }
}

$foo = Factory::new(Foo::CLASS);

… the IDE has no idea what the return from the new() method is, or ought to be, so it cannot give you autocompletion hints on $foo.

That idiom is exactly what Atlas MapperLocator::get() and TableLocator::get() use: the get() param is a class name, and the locator then retains and returns an instance of that class. Likewise, the overarching Atlas class methods all take a mapper class name as their first param, which Atlas uses to figure out which mapper to use for that method call.

You can typehint those methods to abstract classes or interfaces, but then the IDE will not recognize any custom extensions or overrides on the returned concrete classes. What is needed is a way to determine the return type from the get() param, rather than from the method’s return typehint.

II.

Lucky for us, the PHPStorm IDE allows for a .phpstorm.meta.php file (or a collection of files under a .phpstorm.meta.php/ directory) where you can map the factory method inputs to their return types. (See here for the documentation.)

On working with it, though, I found it to be a little inflexible. I expected to be able to map a string directly to a literal class name, but that didn’t really work. The IDE never seemed to pick up the return typehinting. Further, with class names the way they are in the 1.x, 2.x, and 3.x-alpha releases, I would need to add a series of param-to-type mappings for every single data source class (i.e., about 10 entries for each data source type). I imagined that would become cumbersome.

To adapt to this, I decided to modify the Atlas class naming with PHPStorm’s ‘@’ metadata token in mind. The ‘@’ token, per the above documentation, gets replaced with the factory method parameter value, making it perfect as a class name prefix. However, you can’t do any string manipulations on it; you cannot, say, call substr('@', 0, -6) . 'Record' and give it “FooMapper” to get back “FooRecord”. It would have to be ‘@Record’ or nothing.

This leads to the biggest change in Atlas since its inception: the data source mapper classes are no longer suffixed with “Mapper”. Previously, if you had a foo table, you would expect a mapper class name like App\DataSource\Foo\FooMapper. With this naming change, the mapper class name is now App\DataSource\Foo\Foo. That makes the data source mapper class name a good base as a prefix for all the other data source type classes, which in turn means the ‘@’ token can be used without need for string manipulation, and it works with only a very few lines of PHPStorm metadata code.

You can see the resulting .phpstorm.meta.php resource bundled with Atlas 3.x here. Put that at the root of your project, and PHPStorm will now successfully typehint all the Atlas method returns. (You might have to restart PHPStorm for it to take full effect.)

III.

There was still another category of problems, though. While the overarching Atlas class now had IDE completion, the underlying Mapper and Table packages were missing some hints on their classes and methods.

For example, the base Mapper::fetchRecord() method is typehinted to return a Record, but the child FooMapper::fetchRecord() actually returns a FooRecord. The properties on the FooRecord are specific to the underlying FooRow from the table and any FooRelationships on the mapper, and that needs to be indicated by the IDE.

Likewise, the base Mapper::select() class is typehinted to return the base MapperSelect class; in turn, the MapperSelect::fetchRecord() method is typehinted to return a Record. But a $fooMapper->select()->fetchRecord() call actually returns a FooRecord. Those too need to be indicated by the IDE.

I ended up solving this in two steps:

  1. Each data source type now gets its own type-specific select class; whereas FooMapper::select() used to return a general-purpose Select, it now returns a FooSelect instance extended from the general-purpose select. This type-specific select class is generated by Atlas.Cli tooling.

  2. On the type-specific mapper and select classes, the Atlas.Cli tooling now generates a docblock of @method declarations with overriding type-specific returns. (You can see an example of this in the Atlas.Testing package Author and AuthorSelect classes.) PHPStorm picks up those docblocks and uses them to provide return typehints when those classes are used.

With those two additions to the Atlas.Cli skeleton generator, autocompletion in the PHPStorm IDE appears to be complete.

IV.

Of course, “there are no solutions, there are only tradeoffs.” This situation is no different. Yes, Atlas now has a relatively straightforward IDE completion approach, but at these costs:

  • If IDE completion is not important to you, these very significant changes from the alpha version to the beta version will feel useless or even counter-productive.

  • There are two more classes per data-source type: one type-specific MapperSelect, and one type-specific TableSelect (to typehint the Row returns).

  • Not having a “Mapper” suffix on the actual data-source mapper class may feel wrong or counter-intuitive.

For some, these tradeoffs will not be worth the effort to transition from the alpha release to the beta. However, after working with the beta for a while, I think they end up being a net benefit overall.