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.
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 is5.7
) inconfig/packages/doctrine.yaml
.env
DATABASE_URL=mysql://root:mypassword@127.0.0.1:3306/my_db_name
config/packages/doctrine.yaml
doctrine:
dbal:
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:
config/packages/tdbm.yaml
tdbm:
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
composer.json
{
"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:
$ 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
.
src/Migrations/VersionXXXXXXXXXXXXXX.php
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);
$db->table('users')
->uuid() // This is a shortcut to create an ID column with a UUID format
->column('login')->string(50)->unique()->notNull()
->column('email')->string(100)->unique()->notNull()
->column('password')->string(256)->notNull();
$db->table('roles')
->column('name')->string(50)->primaryKey();
$db->junctionTable('users', 'roles'); // Create a pivot table named 'users_roles' between 'users' and 'roles'
$db->table('articles')
->id() // This is a shortcut to create an autoincremented ID
->column('title')->string(50)->notNull()
->column('body')->text()->notNull()
->column('author_id')->references('users') // author_id is a foreign key to the users tables
->column('update_date')->datetimeImmutable();
}
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
andGenerated\AbstractArticle
ArticleDao
andGenerated\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!
src/DataFixtures/AppFixtures.php
<?php
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
$this->roleDao->save($adminRole);
$this->roleDao->save($userRole);
// 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
$admin->addRole($adminRole);
$admin->addRole($userRole);
$this->userDao->save($admin);
$article = new Article($admin, 'My title',
'My body', new DateTimeImmutable());
$this->articleDao->save($article);
}
}
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'ssave()
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
Woot!
Let's check the database content in your preferred DB tool:
Victory!
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:
src/Beans/User.php
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
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!
src/Controller/ArticleController.php
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.
http://localhost/myapp/article/1
{
"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);
unset($result['password']);
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!
src/Migrations/VersionXXXXXXXXXXXXXX.php
$db->table('users')
->uuid()
// ...
// jsonSerialize()->ignore() adds a @JsonIgnore annotation automatically
->column('password')->string(256)->notNull()->jsonSerialize()->ignore();
Of course, after modifying the database model, you must regenerate the beans and DAOs:
$ bin/console tdb:generate
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:
src/Controller/ArticleController.php
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:
src/Controller/ArticleController.php
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 theIterator
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 aforeach
loop on the iterator) - because it is lazy loaded, we can alter a
ResultIterator
before it is executed. If you call thetake
method, that will automatically add aLIMIT 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 beanswithOrder()
: sorts all columns of the result iterator
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 aResultIterator
.- We don't need to tell
find
we are looking forArticle
beans. Indeed, this is thefind
method of theArticleDao
so it will returnArticle
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.
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);
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).
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!
Joins
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 theroles
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!
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).
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:
- Modeling inheritance
- Memory management and batch processing
- Sorting, fetching only one record...
- GraphQL integration
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.