Doctrine DBAL and ORM in Layman's Terms

In this video we are going to take our first look at Doctrine.

Doctrine is all about our database. As database interaction is such a fundamental part of any web application it is important to get your head around Doctrine as soon as possible.

Initially, Doctrine seems quite scary and confusing. At least, that's how I found it.

To tell you the truth, the first time I built a Symfony2 web site (way back in the days of Symfony 2.0 beta), I largely bypassed Doctrine. Instead I resorted back to what is known as raw SQL - standard SELECT, UPDATE, DELETE type stuff.

DBAL What Now?

My problem was that I didn't understand all this fancy new terminology. DBAL this, ORM that... what is all this and why should I care? I know how to use MySQL just fine thank you very much.

Well, let's quickly cover both of these terms and once you know what they are, a lot of the scariness disappears.

The DBAL stands for Database Abstraction Layer.

Ahem. That sounds like lingo straight out of the computer science text book.

What it means though is that Doctrine is all about hiding the complexity of which type of underlying database we are interacting with.

When you think about it, this is actually really very cool. It allows us to swap and change between MySQL, SQLite, MSSQL and others without having to change anything but a few values in our parameters.yml file.

If you have ever had to support this kind of thing without Doctrine then you will be well aware of how much time this is saving you already. Not to mention that this is well tested and documented.

tl;dr; the DBAL abstracts or masks the complexity of which database type (MySQL, SQLite, MSSQL, etc) we are connecting to.

ORM SchmOh R M

The other confusing term is the ORM. An ORM is an Object Relational Mapper.

Holy moly, more comp. sci. technobabble.

The idea here is to take our database relationships - exactly the sort of thing you would have used in any of your previous PHP applications that made use of SQL (MySQL, or other database brand) and convert those bits of data and their relationships into Objects that we can use inside our code.

Somewhat confusingly, Doctrine doesn't refer to these bits of data as Objects, but rather as Entities.

Let's say we have a table in our database containing 10 boy's names. Each row in that table has an id and a name field.

We could first create a simple Object to represent a table row:

// src/AppBundle/Entity/Boy.php
namespace AppBundle\Entity;

class Boy
{
    protected $id;
    protected $name;
}

And then add in the Doctrine annotations to turn this into a valid Doctrine Entity:

// src/AppBundle/Entity/Boy.php
use Doctrine\ORM\Mapping as ORM;

/**
 * @ORM\Entity
 * @ORM\Table(name="boy")
 */
class Boy
{
    /**
     * @ORM\Column(type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    protected $id;

    /**
     * @ORM\Column(type="string", length=100)
     */
    protected $name;
}

Now that we have our Entity structure, Doctrine can read rows out of the boy table and populate (or hydrate as it's known in Doctrine) our Entity / Object correctly.

This is the high level concept of an Object Relational Map (ORM).

Doctrine takes the data in our database and uses the Entities we have defined to allow us instead to work with Objects representing that database data.

Still struggling? This StackOverflow post contains a really nice summary on "what is an ORM?"

DQL, It's Not Quite SQL

To further make things tricky, Doctrine removes our need to use traditional SQL statements entirely, and instead, comes with its own query syntax called the Doctrine Query Language (DQL).

Now, this is another point of contention for developers who are new to Symfony / Doctrine but have a good grasp on SQL. Why learn DQL - a proprietary syntax - when SQL is universal (well, close enough)? Why tie yourself to Doctrine?

The good news is - DQL is very, very similar to SQL. So much so that if you know SQL, using DQL will likely come very naturally to you.

There are some caveats, and some strange circumstances where things aren't quite one-to-one, but with a little exploration (read: Googling) / trial and error, it's usually quite simple to get a Doctrine query written.

Also, for the more common operations - creating, deleting, updating - there's no need to write any DQL at all.

That just leaves finding (or SELECT'ing), which has some convenience methods built in for free - find, findOne, findAll, and some others - which we can use without knowing any DQL at all. Instead, if we need to match things, find all products with a price of £10.00 or similar - we can just pass in a basic PHP array with our required parameters.

$repository = $this->getDoctrine()->getRepository('AppBundle:Product');
$products = $repository->findBy(
    array('price' => 10.00)
);

We can even shorten this further by adding Price to our findBy:

$repository = $this->getDoctrine()->getRepository('AppBundle:Product');
$products = $repository->findByPrice(10.00);

Sooner or later you will run into a situation where you can't use these short hand / convenience methods to get your desired result. When that situation arises, you will have to dive into DQL.

Thankfully, by then it shouldn't be as confusing as it might currently seem. Let's look at a bit of DQL which will hopefully calm your nerves:

$query = $em->createQuery('SELECT p FROM AppBundle\Entity\Product p WHERE p.price > 10.00');
$products = $query->getResult();

It looks like SQL but there's some obvious differences. I won't go into this too much at this stage, but hopefully you can see that DQL isn't that crazy.

Interested in learning more about DQL? The Doctrine 2 DQL documentation is well worth a read.

One Language To Rule Them All

The really nice part of these two concepts is that by learning a little DQL, and by using Doctrine to mask / abstract away what underlying database brand (MySQL, SQLite, etc) we are using, we can write our queries using DQL, the Query Builder, or the convenience methods (findAll, findBy, etc) and not have to worry about changing any syntax at all.

The usual counter argument to this is how often do we actually change our underlying database? Well, admittedly not that much. One often mentioned situation would be using SQLite for our Test environment versus MySQL in production.

So there's that :)