Modeling Data for an API with Symfony and Doctrine (Part 1/2)

I’ve been preparing a session to introduce co-workers to Symfony. I started by documenting commands to follow during the presentation but decided to kill two birds with one stone by spending the weekend crafting a thorough post for my shiny new blog.

By the end of this post it should be clear how easy and powerful it is to create and maintain your application data with Symfony and Doctrine.

In a future post, we will add fixtures to seed the database with user data, and then install API Platform to add a REST API.

Here’s what we’ll do:

• Create a bare-bones Symfony application
• Install the ORM (Doctrine) dependencies
• Configure the database connection string
• Create the database
• Create a User entity (model)
• Use annotations to customize the table name and declare email unique
• Install the schema directly from the entity class
• Wipe the schema
• Create a migration file and run it to build the users table
• Add additional fields to the User entity
• Create and run another migration file that updates the users table
• Create a Role entity with a many to one relation to a User
• Look at annotations to control how the join table and fields are named
• Create and run another migration to update the database (modify users, add roles & users_roles)

Ready? Let’s begin…

Prerequisites

You will need an environment with PHP, Composer, and a supported database like PostgreSQL, MariaDB, or MySQL installed.

Generate a new Symfony project skeleton

Let’s start by creating a Symfony app with composer.

composer create-project symfony/skeleton users-api
cd users-api/

This is the simplest method to create a Symfony project however there are other ways including using the Symfony CLI with options such as designating the LTS version.

Database Setup

Now it’s time to install symfony/orm-pack.

This includes Doctrine, an ORM with a feature set that rivals ORMs of any language.

composer require symfony/orm-pack

Note: bin/console is a relative path from the project root to a PHP script. Some environments may require you to prepend it with php. i.e. php bin/console ...

Update .env file with database credentials

When the bundle is installed, it will add a DATABASE_URL environment var to the .env file. Update the value with your database information.

DATABASE_URL=mysql://root:@127.0.0.1:3306/lando_users?serverVersion=5.7

Create the database

If your database server is running and the DATABASE_URL environment variable is correct, then you should be able to create the named database by running the following command:

bin/console doctrine:database:create

Before we get started with the data modeling, let’s install symfony/maker-bundle.

This provides the make command which is Symfony’s CLI tool for scaffolding entities, controllers, etc.

This is only required for development so be sure to use the --dev flag.

composer require --dev symfony/maker-bundle

Entities

Entities in the context of Symfony and Doctrine are your database models. Doctrine uses a Data Mapper Pattern in which an entity represents a table but clearly separated from the business logic. Entities are typically annotated properties with nothing more than getters and setters.

In contrast, another popular ORM Eloquent uses an Active Record pattern where the data object is more tightly coupled with business logic.

As an example, an Eloquent object is saved with $obj->save() while a Doctrine object is passed into a service to be saved. i.e. em->persist($obj).

While Doctrine may score some points here in respect to the Single Responsibility Principle, but the way it handles relations and migrations make it stand apart from not only its PHP based peers, but top ORMs in other languages too!

Create User Entity

With Doctrine, your entity class IS your schema! It is pretty useful when you’re able to reference a single class for the schema of your database. Not just for you, but for your IDE and linting tools as well! This is a feature you don’t realize the power of till you’ve worked with it for a while and suddenly don’t have it anymore when moving into another framework.

Running make:entity command will send you through a dialog which will allow you to add fields to your model. Don’t worry if you forget a field, or get it wrong, running the command a second time on the same entity will add to the existing one.

bin/console make:entity User

Create the following fields:

• email (string, 255)
• password (string, 64)
• firstName (string, 64)
• lastName (string, 64)
• created (datetime)

Once you’re done adding fields, press enter and it will have created 2 files:

• src/Entity/User.php: Data model class with properties and getters/setters.
• src/Repository/UserRepository.php: Data access class for queries beyond what Doctrine provides by default.

Manually Update src/Entity/User.php:

• id unsigned: @ORM\Column(type="integer", options={"unsigned":true})
• email unique: @ORM\Column(type="string", length=128, unique=true)

Name the Table

By default, Doctrine will name the table as the name of the entity. In our case, the table would be named user.

If you prefer the plural version, or an entirely different table name, you can add an annotation at the top of the class.

/**
 * @ORM\Table(name="users")
 * @ORM\Entity(repositoryClass="App\Repository\UserRepository")
 */
class User
{
    ...

Create the Schema from Entities

Now it’s time to create the tables in the database.

The following command will create a database schema based on the current data modeling defined in your entities.

bin/console doctrine:schema:create

When developing, it’s likely you will need to re-initialize the database. In this case you can drop before creating the scema again.

bin/console doctrine:schema:drop --force
bin/console doctrine:schema:create

Migrations

Migrations are files containing SQL schema updates, named in a manner they can be ordered chronologically. Each instance tracks the last run migration so they can only the required updates as new files are added.

There are 2 actions you take with migrations:

1. Create the migration (Create the code that will update the database)
2. Run the migration (Update the database)

Creating a Migration:

Running bin/console make:migration analyzes the difference between your current database and the entity file, then automatically generates a migration file containing ONLY the SQL required to get your database from where it is now, to what it should be based on your entities.

For example: If you have an empty database and run bin/console make:migration at this point, a migration file will be created which contains SQL to create the users table along with each field in Users.php.

If however your database matched your entity file, and you added a password field to Users.php, running bin/console make:migration would only contain the SQL to add the password field.

While other ORMs require you to add table alters and other SQL in migrations manually, Doctrine automatically generates the code based on a diff analysis between your entities and database.

Let’s do it! First, take a look in src/Migrations. Notice there isn’t any migration files there.

Now run the following command.

bin/console make:migration

Now look in the migration directory again. You should have a file named something like: src/Migrations/Versionyyyymmddhhmmii.php. In that file you have up and down methods.

If you’ve been following along, you probably don’t have much in there. This is because we had already built our schema directly with bin/console doctrine:schema:create, and not a migration.

Let’s fix this:

• Delete the migration file we just generated
• Delete the schema by running: bin/console doctrine:schema:drop --force
• Generate a new migration file: bin/console make:migration

Have another look at the migration file and you will see the SQL to create the users table is there.

Your database is still empty as we haven’t run the migration yet. We only created the migration file.

Running Migrations

Use the following command to run all migration which need to be run:

bin/console doctrine:migrations:migrate

Pro Tip: You can abbreviate the words in each section of the command. i.e. bin/console doc:mig:mig. I typically keep a Makefile which calls these commands as their full version, but this shortcut saves a few strokes and likely many typos when I don’t have to manually type all characters.

Scenario

Up until now it didn’t matter if we had built our schema with doctrine:migrations:migrate or bin/console doctrine:schema:create because we can wipe our dev systems without consequence and go directly to the latest schema.

Imagine now that our app has been deployed to production.

While we can continue to standup our dev systems with schema:create, using migrations:migrate is an absolute must to update production without errors and data loss.

The scenario at this point is to add an active field to the User entity.

Adding Another Field

Let’s add an active field of the boolean type.

bin/console make:entity User

The entity has been updated but our database has not.

Add an Index

Too often database indices are an after thought. You should be thinking about them as you design your models. Think about which fields queries will be based on. In other words, consider adding indices on fields used in the WHERE and ORDER BY clauses of your queries… or in the case of an ORM, which fields you create conditions on.

Note: While indices can improve performance with queries (reads), they can reduce performance for writes. All this is beyond the scope of this post but I recommend studying more about this topic of you aren’t familiar already.

The most common query on the user table will be based on user id and email.

Consider common queries our application may run against the users table:

# Query a user by id
SELECT * FROM users WHERE id=1;

# Authentication Query
SELECT * FROM users WHERE email='me@example.com' AND password='hashedpass...';

# Active users query orderd by last name
SELECT * FROM users WHERE active=1 ORDER BY last_name;

# Autocomplete query based on last name
SELECT * FROM users WHERE active=1 AND last_name LIKE 'mila%'

The fields of interest here are:

• id
• email
• active
• last_name

The id field is already indexed because it’s the primary key of our table.

The email field is already indexed because we added a unique constraint which adds an index behind the scenes.

This means we need to focus on active and last_name.

When searching by last name, it’s likely we’re only going to be interested in active users, so let’s add a combined index of active and last name.

We’ll use the annotation @ORM\Index nested inside the @ORM\Table annotation.

The top of your user class should now look something like this:

/**
 * @ORM\Table(name="users",
 *   indexes={
 *   @ORM\Index(name="user_active_lname", columns={"active", "last_name"})
 * })
 * @ORM\Entity(repositoryClass="App\Repository\UserRepository")
 * @ApiResource
 */
class User
{
    ...

Create Another Migration

Before generating another migration, it is critical that you do this on a copy of the database that is using the production schema.

Remember, a Doctrine migration analyzes the existing database and automatically generates the queries required to make that database match the entities.

bin/console make:migration

Commit that code and be sure to run the migration locally, and on the remote environments once deployed.

bin/console doctrine:migrations:migrate

Creating Relations Between Entities

Now let’s create a Role entity which we will relate to the User entity in a later step.

bin/console make:entity Role

Create the following fields:

• name (string, 64)
• role (string, 16)

Once complete, you will see a new Role.php in your entities directory.

Adding a Relation

When associating users to roles, we’ll use a ManyToMany. For this relationship, an additional table will be required to map user ids to role ids.

Doctrine has a concept of an owning side of a relation, and while it doesn’t make that much of a difference in a ManyToMany relationship, I prefer in this case to set the User as the owning side. This is relevant because when using make:entity, you add the field to the owning side of the relation.

bin/console make:entity User

• name: roles
• type: relation
• related to: Role
• relation: ManyToMany
• Select yes when prompted to add a users property to Role.

Once complete, inspect your User.php entity class and notice the addition of the following detail which as added to the code:

• The $roles property was added with annotation mapping it to the Role entity
• $this->roles is initialized in the constructor as a new ArrayCollection()
• Methods getRoles(), addRole() and remoteRole() have been added.
• You will find the same relations have been added to the new Role entity.

    /**
     * @ORM\ManyToMany(targetEntity="App\Entity\Role", inversedBy="users")
     */
    private $roles;

    public function __construct()
    {
        $this->roles = new ArrayCollection();
    }

    ...

    /**
     * @return Collection|Role[]
     */
    public function getRoles(): Collection
    {
        return $this->roles;
    }

    public function addRole(Role $role): self
    {
        if (!$this->roles->contains($role)) {
            $this->roles[] = $role;
            $role->addUser($this);
        }

        return $this;
    }

    public function removeRole(Role $role): self
    {
        if ($this->roles->contains($role)) {
            $this->roles->removeElement($role);
            $role->removeUser($this);
        }

        return $this;
    }

Doctrine will automatically name the join table and fields, but you do have control over that yourself.

Take a look at the JoinTable annotation I added above the $roles property which defines the join table name, field names. It also adds a unique constraint (index) to the user to role id relationship.

    /**
     * @ORM\JoinTable(name="users_roles",
     *      joinColumns={@ORM\JoinColumn(name="user_id", referencedColumnName="id")},
     *      inverseJoinColumns={@ORM\JoinColumn(name="role_id", referencedColumnName="id")})
     */
     private $roles;

Without this code, it would have created a join table named user_role. The field names would have remained the same but this demonstrates where you can modify field names.

Note: User and Role have additional functionality in a traditional Symfony application. This app does not use Symfony’s security features and while we’re not concerned with these features, these could be extended with little effort and be used with the security bundle.

Manually Update src/Entity/Role.php:

Let’s make a few updates to the Role.php entity class before updating our schema:

• Name the table by adding this annotation just above class as we did with User.

  • @ORM\Table(name="roles")

• Add an index on user_id under the Table annotation you just added.

  • @ORM\Index(name="ur_uid", columns={"user_id"}),

• Set the id to be an unsigned int.

  • id unsigned: @ORM\Column(type="integer", options={"unsigned":true})

• Set the role field to be unique.

  • role unique: @ORM\Column(type="string", length=16, unique=true)

Create and Run the Migration

All that is left is to create and run the migration.

First, create the migration file:

# Create the migration file
bin/console make:migration

It’s always a good idea to look over the migration file before committing it to your repository, or running it.

Once you confirm it looks good, run it:

bin/console doctrine:migrations:migrate

That’s it. You can inspect your database and confirm it has the tables defined in your User entity.

Conclusion

There is plenty more to learn about Doctrine, the workflow demonstrated here speaks to the power Doctrine allows in modeling and maintaining consistency between your application and database.