David Négrier CTO

In this tutorial, we are going to see how to get started with TDBM 5.1 with a Symfony application.

TDBM? What is that?

TDBM is a PHP ORM. It is actually a database first ORM. TDBM understands your database model and generates PHP classes for you to access your database.

If you are wondering WHY you would want a DB first ORM, a good starting point is my article about the release of TDBM 5.

TDBM has been developed by TheCodingMachine since 2006 (!) and we are proud to use it on most of our projects. So in case you are wondering, yes, it is production ready.

Getting started with Symfony

TDBM is a framework agnostic ORM, but plays well along with Symfony (you can use it with Laravel too!).

In this tutorial, I'll assume you have a basic knowledge of Symfony 4. I'll be using the symfony command line tool to start the application as described in the "setup" documentation. Your mileage may vary.

So let's get started by creating a new project:

$ symfony new tdbm-tutorial

Now, let's install TDBM using the dedicated Symfony bundle:

$ composer require thecodingmachine/tdbm-bundle ^5.1

Symfony will warn you that TDBM comes with a community developed bundle:

  -  WARNING  thecodingmachine/tdbm-bundle (>=5.0): From github.com/symfony/recipes-contrib:master
    The recipe for this package comes from the "contrib" repository, which is open to community contributions.
    Review the recipe at https://github.com/symfony/recipes-contrib/tree/master/thecodingmachine/tdbm-bundle/5.0

That's ok! Press "Y" and let's keep going.

If you are paying close attention to the list of packages installed, you may have spotted Doctrine packages. "Doctrine? But I'm installing TDBM. I don't need Doctrine!"
Well... Doctrine comes with several layers. Doctrine DBAL is the low level database abstraction layer, while Doctrine ORM is the ORM you all know. TDBM uses Doctrine DBAL internally. It turns out Symfony provides only one bundle for both DBAL and the ORM. That's a shame, but that's the way it is. For now, let's forget about this Doctrine ORM package and let's pretend it is not there 😊

Configuring the database

Since TDBM uses Doctrine DBAL, configuring the database connection is exactly the same as what you are used to do in Doctrine:

  • Modify your DATABASE_URL config in .env file
  • Configure the driver (pdo_mysql) and server_version (for me it is 5.7) in config/packages/doctrine.yaml




        driver: 'pdo_mysql'
        server_version: '5.7'
# ...

Note: if you have configured a database that does not yet exist, you can create it using:

$ bin/console doctrine:database:create

Configuring the namespaces

Now, let's review the TDBM configuration.

Check the config/packages/tdbm.yaml file:


    bean_namespace: App\Beans
    dao_namespace: App\Daos

TDBM needs 2 namespaces:

  • one for the "beans"; in TDBM speech, a bean is roughly equivalent to an entity in Doctrine ORM
  • one for the "DAOs"; in TDBM speech, a DAO (data access object) is the equivalent of a repository in Doctrine ORM

Those namespaces need to be autoloadable by Composer.

By default, composer.json contains


    "autoload": {
        "psr-4": {
            "App\\": "src/"

So any namespace starting with App\ can be used. If you are using a custom "vendor" (a namespace that does not start with App\), you will have to configure TDBM namespaces accordingly.

Creating a data model

TDBM needs an existing model to get started. If you already have that, you can skip this section.

Here, we will learn how to create a database model the easy way. We will be using Doctrine migrations. It is a great package to keep track of the changes in your database model incrementally. You can create "patches" (called migrations) and apply them automatically.

First, we need to install Doctrine migrations:

$ composer require doctrine/doctrine-migrations-bundle

Now, let's create the migration:

$ bin/console doctrine:migrations:generate

You should now see a new file src/Migrations/VersionXXXXXXXXXXXXXX.php in your project directory. Let's edit it.

The up() method of the migration class is supposed to contain your SQL.

But instead of running "CREATE TABLE" SQL statements manually (you can do that, that is perfectly fine), we will be using the "TDBM fluid schema builder" to create our database in a breeze.

The "TDBM fluid schema hydrator" is a utility package to create a database model using PHP. It provides a great developer experience through an API that was carefully thought to take advantage of your IDE autocompletion.

Here is a demo of autocompletion in action:

fluid schema builder demo

Note: the TDBM fluid schema builder is not part of TDBM. It is available as a standalone package that we need to install.
$ composer require thecodingmachine/tdbm-fluid-schema-builder

So in our case, let's build a simple data model. We will create an database storing articles. Articles have an author. And this author is a user that has a number of roles.

Database model


use Doctrine\DBAL\Schema\Schema;
use Doctrine\Migrations\AbstractMigration;
use TheCodingMachine\FluidSchema\TdbmFluidSchema;

final class VersionXXXXXXXXXXXXXX extends AbstractMigration
    public function getDescription() : string
        return 'DB model initialization';

    public function up(Schema $schema) : void
        $db = new TdbmFluidSchema($schema);

            ->uuid() // This is a shortcut to create an ID column with a UUID format


        $db->junctionTable('users', 'roles'); // Create a pivot table named 'users_roles' between 'users' and 'roles'

            ->id() // This is a shortcut to create an autoincremented ID
            ->column('author_id')->references('users') // author_id is a foreign key to the users tables

    public function down(Schema $schema) : void
        throw new \RuntimeException('Never go down, always up :)');

Let's apply this migration:

$ bin/console doctrine:migrations:migrate

All right! We have a data model! We can finally start playing with TDBM!

Generating beans and DAOs

TDBM generates PHP files from your data model.

To trigger the generation, we use the tdbm:generate command:

$ bin/console tdbm:generate

When the command is done running, you should see a number of files created:

As you can see, there is one bean and one DAO class per table created.

The beans and DAOs are split in 2 classes. For instance, for the "article" table, you have:

  • Article and Generated\AbstractArticle
  • ArticleDao and Generated\AbstractArticleDao

This split is used to make it easy for you to work on the same files as TDBM.

Article and ArticleDao are yours. You can modify those classes as you wish, and add any methods in those classes. Generated\AbstractArticle and Generated\AbstractArticleDao belongs to TDBM. Never ever change code in those classes as TDBM will overwrite them when you run the tdbm:generate command.

Another noteworthy thing to notice. Your database model has a users_roles table, but there is no UserRole bean. TDBM detects that the users_roles table purpose is to create a many-to-many relationship. We will see later how to use this relationship.

Also, did you spot the "MigrationVersion" class? It is generated from the "migration_versions" table. This table belongs to Doctrine migrations. Is is used to keep track of which migrations have been applied. You can safely ignore it. It will be automatically removed in a future version of TDBM.

Filling the database

Before querying the database, we will need to fill it. Let's build some fixtures!

We will install the Doctrine fixtures bundle:

$ composer require --dev doctrine/doctrine-fixtures-bundle

The bundle creates a "AppFixtures" class for us. Let's put some code in it!


namespace App\DataFixtures;

use App\Beans\Article;
use App\Beans\Role;
use App\Beans\User;
use App\Daos\ArticleDao;
use App\Daos\RoleDao;
use App\Daos\UserDao;
use DateTimeImmutable;
use Doctrine\Bundle\FixturesBundle\Fixture;
use Doctrine\Common\Persistence\ObjectManager;

class AppFixtures extends Fixture
     * @var UserDao
    private $userDao;
     * @var RoleDao
    private $roleDao;
     * @var ArticleDao
    private $articleDao;

    public function __construct(UserDao $userDao, RoleDao $roleDao, ArticleDao $articleDao)
        $this->userDao = $userDao;
        $this->roleDao = $roleDao;
        $this->articleDao = $articleDao;

    public function load(ObjectManager $manager)
        // Note: the ObjectManager class is part of Doctrine ORM.
        // We don't need it at all since we are using TDBM.

        // Beans can be created using the "new" keyword.
        $adminRole = new Role('ADMIN');
        $userRole = new Role('USER');

        // In order to save the bean in database, you must use 
        // the "save" method of the DAO

        // Any non-nullable column must be passed to the constructor.
        // It is therefore impossible to create an object in PHP 
        // that is missing required DB "columns"
        $admin = new User('admin', 'admin@example.com', 'my_password');

        // The many-to-many relationship generates "getRoles", "setRole", 
        // "addRole" and "hasRole" methods on the user bean


        $article = new Article($admin, 'My title', 
                               'My body', new DateTimeImmutable());

Pay a close attention to the code above.

You can notice that:

  • Beans can be created with the new keyword. Even if they have been generated by TDBM, they behave link plain simple PHP objects.
  • The constructor of the beans requires you to pass each non-nullable column. So you cannot create a bean with missing information for the database.
  • By default, beans come with getters and setters for each columns (more on that later)
  • DAOs are injected in the constructor
  • Beans cannot save themselves in the database (they have no save() method because it is not their responsibility). You use the DAO's save() method to persist a bean in database.

Are we done? Let's apply the fixture:

$ bin/console doctrine:fixtures:load

 Careful, database "tdbm_tutorial" will be purged. Do you want to continue? (yes/no) [no]:
 > yes

   > purging database
   > loading App\DataFixtures\AppFixtures


Let's check the database content in your preferred DB tool:


Wait... Victory? Really? Our password is stored in clear text! This is a security hazard. We clearly want to store it hashed in the database!

We could obviously pass a hashed password to the constructor of the User class but let's rather change the class so that is hashes the passwords itself.

Remember? The User class is split in 2: User (this is your part), and AbstractUser (this is TDBM's part).

By default the User class is empty.

Let's edit User and override the setPassword method:


class User extends AbstractUser
    public function setPassword(string $password) : void
        parent::setPassword(password_hash($password,  PASSWORD_DEFAULT));

Internally, the User constructor calls the setter methods. So our job is done!

Let's run the fixtures again:

$ bin/console doctrine:database:drop --force
$ bin/console doctrine:database:create
$ bin/console doctrine:migrations:migrate
$ bin/console doctrine:fixtures:load
Even if the "doctrine:fixtures:load" command says it will purge the tables, this is not true. It is therefore advised to drop the database and apply all migrations before reloading the features.

Let's have a look at our password column:

Way better! The password is properly encoded!

Fighting the anemic data model

We might want to improve the User class just a little bit more. You might have noticed that there is a getter and a setter generated for each column in the database. The bean therefore exposes its inner state, which is not always proper object oriented design.

Hopefully, you can configure which getters and setters are generated or not. For instance, in our User class, we might want to remove the getPassword() method and replace it with a method to verify the password (like verify($password): bool).

We will not dive into the details on how to do that in this tutorial. If you are interested, you can jump to this article.

Querying the database

So far, we learned how to create a data model and how to populate it with beans. But we have not yet made any query in the database.

Let's play a bit with the query mechanism!

We will build a simple REST controller to query our database.

I'm very lazy so I will use the Symfony Maker bundle for that.

$ composer require --dev symfony/maker-bundle
$ bin/console make:controller

   Choose a name for your controller class (e.g. GentlePuppyController):
   > ArticleController

   created: src/Controller/ArticleController.php

Fetching a bean by ID

The first route we will write will fetch an article by ID.

Each DAO comes with a getById method. Let's use that!


class ArticleController extends AbstractController
     * @var ArticleDao
    private $articleDao;

    public function __construct(ArticleDao $articleDao)
        $this->articleDao = $articleDao;

     * @Route("/article/{id}", name="article")
    public function index($id)
        $article = $this->articleDao->getById($id);

        return $this->json($article);

Each table comes with its DAO. We inject the ArticleDao in the controller's constructor.

Now, we can call $this->articleDao->getById($id) to get a bean and simply serialize it to JSON.


  "id": 1,
  "author": {
    "uuid": "b259f27a-f26a-44bb-a81e-04ed38a53272",
    "login": "admin",
    "email": "admin@example.com",
    "password": "$2y$10$.uI2aMZxQ2hGvckrS.H0WOuKemkpXTQrvKwH9k7hI.JHCS4Q3gNnS"
  "title": "My title",
  "body": "My body",
  "updateDate": "2019-08-07T15:28:36+02:00"

As you can see, TDBM will automatically serialize the article bean to a meaningful JSON.

By default, TDBM will:

  • export all columns of the object
  • export all columns of the objects linked, but only one level deep

In the example above, we see that the details of the author linked to the article are also exported.

Of course, you can change this default behaviour.

Let's see what are our options to remove the encoded password in the export of the user.

First option: overload jsonSerialize()

The simplest option is to overload the jsonSerialize(). Each abstract bean class comes with a jsonSerialize method used to serialize the JSON. We can simply override it!

class User extends AbstractUser
    // ...

    public function jsonSerialize(bool $stopRecursion = false)
        $result = parent::jsonSerialize($stopRecursion);
        return $result;

Second option: Annotate the DB schema with JSON annotations

As an alternative, you can also simply add a @JsonIgnore annotation in the comments of the password column. When TDBM builds beans and DAOs, he will notice the annotation and ignore the password column in the JSON export.

Event better, since we are using the TDBM fuild schema builder, the JSON annotation can be added by calling a single method when we build the data model!


    // ...
    // jsonSerialize()->ignore() adds a @JsonIgnore annotation automatically

Of course, after modifying the database model, you must regenerate the beans and DAOs:

$ bin/console tdb:generate
There are many more @JSONxxx annotations available to tailor JSON serialization to your needs. Check out the TDBM documentation.
If you need to customize JSON export a lot, or if you have different end-points that have very different needs, we strongly recommend having a look at GraphQL. TDBM integrates natively with the GraphQLite library. The TDBM + GraphQLite combo is extremely powerful.

Fetching a list of all beans

Fetching the list of all the beans is pretty easy as well. All DAOs come with a findAll method that returns a list of all the beans.

So listing all articles is as easy as:


class ArticleController extends AbstractController
    // ...

     * @Route("/article", name="articleList")
    public function list()
        $articles = $this->articleDao->findAll();

        return $this->json($articles);

Paginating the results

If you have thousands of articles, returning the list of all articles might eat a lot of resources. In this case, you will want to paginate the results.

This is quite easy to do with TDBM:


class ArticleController extends AbstractController
    // ...

     * @Route("/article", name="articleList")
    public function list(Request $request)
        $offset = (int) $request->query->get('offset');
        $limit = (int) $request->query->get('limit');

        $articles = $this->articleDao->findAll()->take($offset, $limit);

        return $this->json($articles);

Did you notice the take method? Paginating is that easy.

If you are wondering how this works, here are a few details:

The findAll() does not return an array of Article beans. Instead, it returns a ResultIterator.

A ResultIterator behaves like a regular array:

  • you can loop on it using foreach (because it implements the Iterator interface)
  • you can access items using the array notation ($resultIterator[0] is valid syntax)

However, the ResultIterator comes with a number of advantages:

  • it is lazy loaded: when you call the findAll method, no query is sent to the database. Instead, the database query is only executed when we need the data (i.e. when we start a foreach loop on the iterator)
  • because it is lazy loaded, we can alter a ResultIterator before it is executed. If you call the take method, that will automatically add a LIMIT X OFFSET Y to the query.

The ResultIterator comes with a number of useful methods:

  • $resultIterator->count(): returns the total number of records in this dataset.
  • $resultIterator->map(fn() {...}): perform a "map" of all beans
  • withOrder(): sorts all columns of the result iterator
The take method comes from the Porpaginas interface for pagination. Porpaginas is a standard for pagination developed by Benjamin Eberlei.

Applying filters

We saw how to retrieve all beans from a table, but of course, most of the time, we will want to apply filters.

Let's say I want to retrieve all articles containing some word and updated after a certain date.

In SQL, my query would look like this:

SELECT * FROM articles 
    WHERE (title LIKE :search OR body LIKE :search) 
          AND update_date >= :from_date

To perform the same query in TDBM, we will add a method to the ArticleDao class. Remember? The XxxDao classes are the one we use to fetch the beans.

class ArticleDao extends AbstractArticleDao
     * @return Article[]|ResultIterator
    public function search(string $search, DateTimeImmutable $fromDate): ResultIterator
        return $this->find(
            '(title LIKE :search OR body LIKE :search) 
             AND update_date >= :from_date',
                'search' => '%'.$search.'%',
                'from_date' => $fromDate->format('Y-m-d')

We use the find() method to perform queries with filters.

  • The first parameter accepts the string that will go in the "WHERE" clause. This is pure SQL. Just like with prepared statements, you can use ":foo" to add a placeholder
  • The second argument takes the array of placeholder values to be fed to the query
  • find() returns a ResultIterator.
  • We don't need to tell find we are looking for Article beans. Indeed, this is the find method of the ArticleDao so it will return Article instances.

The find() method is protected. This means you can only call it from a DAO. This is important because find() takes SQL as its input. TDBM is an ORM so it is supposed to abstract the notion of schema from the user. By making the find() method protected, we ensure that any SQL snippet used in the code is located in the DAO classes. This is best practice as it helps you keep the rest of your code free from the database implementation details.

Most ORMs come with a specialized query language. Doctrine comes with DQL, Hibernate (in the Java world) comes with HQL... TDBM comes with none of those. This is a design decision and it is done on purpose. Indeed, SQL is already hard to learn and we did not want to add the burden of having to learn a SQL variant to use TDBM. So when you write a SQL snippet in TDBM, this is pure SQL.

The absence of query builder

Let's change slightly the previous example. What if I wanted a list of all articles filtered on a search term OR on a update date or both?

Basically, if a search term is passed, I want to filter on it. If a "from date" is passed, I want to filter on it. But if no data is passed, I don't want to apply the filter.

It is of course quite easy to do by constructing the SQL using some form of concatenation.

$sqlParts = [];
if ($fromDate !== null) {
    $sqlParts[] = 'update_date >= :from_date'; 
if ($search !== null) {
    $sqlParts[] = '(title LIKE :search OR body LIKE :search)'; 
$where = implode(' AND ', $sqlParts);
Please don't do that! Concatenating SQL is usually a dangerous practice and very error-prone.

Other ORMs (like Doctrine ORM or Laravel) come with a query builder. Using a query builder, you can build your queries using objects instead of concatenating strings.

But learning to use a query builder is like learning a specialized query language: it is yet another abstraction on top of SQL (and TDBM strives to be close enough to SQL).

So TDBM does not come with a query builder at all.

Instead, it comes with an incredible feature we call "magic parameters".

The idea is simple: if you pass "null" as a parameter, it means that you do not want the parameter to be part of the query at all.

Let's rewrite our search method:

class ArticleDao extends AbstractArticleDao
     * @return Article[]|ResultIterator
    public function search(?string $search, ?DateTimeImmutable $fromDate): ResultIterator
        return $this->find(
            '(title LIKE :search OR body LIKE :search) AND update_date >= :from_date',
                'search' => ($search !== null) ? '%'.$search.'%' : null,
                'from_date' => ($fromDate !== null) ? $fromDate->format('Y-m-d') : null

Hey! It is almost exactly the same code!

We only changed the parameters of the method to be nullable and we are passing null to the find method for any parameter we want to discard.

So let's imagine that your $search parameter is null. Suddenly, your filter will transform into: update_date >= :from_date. The part (title LIKE :search OR body LIKE :search) AND will be automatically dropped by TDBM.

Since TDBM does all the work of removing the useless parts of the SQL request, you don't need to "build" the query. No query builder needed, and a much simpler code!

How is this possible? TDBM parses the SQL query, makes a representation of it as a tree (we call that an Abstract Syntax Tree) and removes parts of the tree that are not needed. The complete logic is actually distributed in a third-party package called MagicQuery. You can read more about this feature here and here (french link).

Note: if you want to disable parameter dropping from the SQL query (in case you want to explicitly compare the parameter to null), you can simply append an exclamation mark to the parameter name. status = :status! will turn into status IS NULL if the status variable is null.

Auto-generated queries

It would be great if we could automatically generate the queries in the DAOs. It is obviously not possible, since by looking at the data model, you cannot know what queries the user will want to execute.

But if you think carefully about it, the data model contains indexes. If you are putting an index on a column, it is likely that you are planning to run a query with a filter on that column, right?

TDBM notices that, and it automatically generates findXXX methods in the abstract DAOs for every index it finds.

Let's have a look at our data model. The users table has a login column that has a unique index on it.

TDBM will automatically generate a $userDao->findOneByLogin($login) method for us!

This will obviously not answer all your needs, but it saves some work. And it encourages a good database design. When you realize that by creating an index, you are removing some PHP code from your project (and of course speeding up your application), you are less likely to forget creating that index!


This TDBM tutorial is almost over. Let's jump to the final part: running a query with joins.

Let's assume we want to find all the articles written by some role (for instance by an administrator).

Explicit JOIN

The most classical way of doing this is to write the SQL JOIN yourself. We will see later that TDBM has a way more powerful way of dealing with joins, but let's first see the classical way:

class ArticleDao extends AbstractArticleDao
     * @return Article[]|ResultIterator
    public function findByRole(string $role): ResultIterator
        return $this->findFromSql(
            'articles JOIN users ON articles.author_id = users.uuid
            JOIN users_roles ur ON users.uuid = ur.user_id
            JOIN roles ON roles.name = ur.role_id',
            'roles.name = :roleName',
            [ 'roleName' => $role ]);

Here, you can see we are not using the find method anymore but the findFromSql method. This method is very similar to find except it expects an additional parameter: the FROM part of the SQL query.

You can write your FROM query with all the joins and you are done.

Implicit JOIN

But TDBM comes with a much more powerful mechanism for JOINs: it has an automatic join detection mechanism. We call it "Magic Join".

Let's see how this works in practice by rewriting the findByRole method using magic joins:

class ArticleDao extends AbstractArticleDao
     * @return Article[]|ResultIterator
    public function findByRole(string $role): ResultIterator
        return $this->find(
            'roles.name = :roleName',
            [ 'roleName' => $role ]);

That's it.

And it works!!!

Now, you might be asking... where did the join go?

Here is what is happening behind the scene:

  • In the WHERE clause, we are using the 'roles.name' column.
  • TDBM notices we want to perform JOIN on the "roles" table.
  • TDBM analyzes the database schema and creates internally a graph of the relationship between all the tables
  • It then tries to find the shortest route between the articles tables and the roles table
  • It follows the shortest path to create the JOINs.

Mission complete!

It obviously assumes that the shortest path is the path you want. If this assumption seems bold to you, you can trust our experience here. Having played with TDBM on more that 100 projects, I can tell you that this is almost always the case. And of course, you don't have to use magic joins if you don't feel like it!

Note: as with anything that is "magic", use "Magic Join" with great care. If your database model is evolving a lot, the shortest path between 2 tables might vary. This would completely change the generated query and wreak havoc on your application. We highly recommend using integration tests with magic query if you want to avoid these kind of nasty surprises. Or stick with the explicit joins by using only findFromSql.

A last trick: Instead of passing SQL to the find method, you can directly pass beans. TDBM will understand you want to filter on this bean.

So you can rewrite the findByRole method to something even shorter!

class ArticleDao extends AbstractArticleDao
     * @return Article[]|ResultIterator
    public function findByRole(Role $role): ResultIterator
        return $this->find($role);

Yes. This works too. I particularly like this syntax because it is very short, and yet very expressive.

The find can accept a number of other arguments but this is behind the scope of this tutorial. If you want to learn more, everything is detailed in the documentation.

Aggregate queries are out of scope

TDBM job is to match tables to objects. It comes with a fairly powerful filtering and joining mechanism (we will talk about it later).

However, from the moment you try to run a query that aggregates data (think "SUM", "AVG", "GROUP BY"...), you are returning values that are not directly mappable to beans. Therefore, any aggregate query is out of TDBM scope. If you want to do aggregate queries, run directly SQL queries.

You are not completely on your own though. Most of the awesome features provided by TDBM come from the underlying mouf/magic-query library. You can use this library to benefit from "magic parameters" and "magic join" features.

Wrapping up

That's it for today folks!

We hope this tutorial will give you enough to get started and enjoy using TDBM!

There are a lot of other gems hidden in TDBM and a single tutorial is not enough to cover them.

For instance we did not cover:

Do not hesitate to dive in the documentation for more details.

Also, now is a good time to thank all the maintainers of the packages we are using in TDBM, and in particular the maintainers of the Doctrine DBAL library (the low level database abstraction library TDBM is built upon).

And as any other open-source project, we definitely welcome contributions!

Want to stay tuned on the latest TDBM releases or PHP related news? Follow me on Twitter!

About the author

David is CTO and co-founder of TheCodingMachine and WorkAdventure. He is the co-editor of PSR-11, the standard that provides interoperability between dependency injection containers. He is also the lead developper of GraphQLite, a framework-agnostic PHP library to implement a GraphQL API easily.