Step 8: Describing the Data Structure

Describing the Data Structure

To deal with the database from PHP, we are going to depend on Doctrine, a set of libraries that help developers manage databases:

  1. $ symfony composer req "orm:^2"

This command installs a few dependencies: Doctrine DBAL (a database abstraction layer), Doctrine ORM (a library to manipulate our database content using PHP objects), and Doctrine Migrations.

Configuring Doctrine ORM

How does Doctrine know the database connection? Doctrine’s recipe added a configuration file, config/packages/doctrine.yaml, that controls its behavior. The main setting is the database DSN, a string containing all the information about the connection: credentials, host, port, etc. By default, Doctrine looks for a DATABASE_URL environment variable.

Almost all installed packages have a configuration under the config/packages/ directory. Most of the time, the defaults have been chosen carefully to work for most applications.

Understanding Symfony Environment Variable Conventions

You can define the DATABASE_URL manually in the .env or .env.local file. In fact, thanks to the package’s recipe, you’ll see an example DATABASE_URL in your .env file. But because the local port to PostgreSQL exposed by Docker can change, it is quite cumbersome. There is a better way.

Instead of hard-coding DATABASE_URL in a file, we can prefix all commands with symfony. This will detect services ran by Docker and/or SymfonyCloud (when the tunnel is open) and set the environment variable automatically.

Docker Compose and SymfonyCloud work seamlessly with Symfony thanks to these environment variables.

Check all exposed environment variables by executing symfony var:export:

  1. $ symfony var:export
  1. DATABASE_URL=postgres://main:[email protected]:32781/main?sslmode=disable&charset=utf8
  2. # ...

Remember the database service name used in the Docker and SymfonyCloud configurations? The service names are used as prefixes to define environment variables like DATABASE_URL. If your services are named according to the Symfony conventions, no other configuration is needed.

Note

Databases are not the only service that benefit from the Symfony conventions. The same goes for Mailer, for example (via the MAILER_DSN environment variable).

Changing the Default DATABASE_URL Value in .env

We will still change the .env file to setup the default DATABASE_URL to use PostgreSQL:

  1. --- a/.env
  2. +++ b/.env
  3. @@ -24,5 +24,5 @@ APP_SECRET=ce2ae8138936039d22afb20f4596fe97
  4.  #
  5.  # DATABASE_URL="sqlite:///%kernel.project_dir%/var/data.db"
  6.  # DATABASE_URL="mysql://db_user:[email protected]:3306/db_name?serverVersion=5.7"
  7. -DATABASE_URL="postgresql://db_user:[email protected]:5432/db_name?serverVersion=13&charset=utf8"
  8. +DATABASE_URL="postgresql://127.0.0.1:5432/db?serverVersion=13&charset=utf8"
  9.  ###< doctrine/doctrine-bundle ###

Why does the information need to be duplicated in two different places? Because on some Cloud platforms, at build time, the database URL might not be known yet but Doctrine needs to know the database’s engine to build its configuration. So, the host, username, and password do not really matter.

Creating Entity Classes

A conference can be described with a few properties:

  • The city where the conference is organized;
  • The year of the conference;
  • An international flag to indicate if the conference is local or international (SymfonyLive vs SymfonyCon).

The Maker bundle can help us generate a class (an Entity class) that represents a conference:

  1. $ symfony console make:entity Conference

This command is interactive: it will guide you through the process of adding all the fields you need. Use the following answers (most of them are the defaults, so you can hit the “Enter” key to use them):

  • city, string, 255, no;
  • year, string, 4, no;
  • isInternational, boolean, no.

Here is the full output when running the command:

  1.  created: src/Entity/Conference.php
  2.  created: src/Repository/ConferenceRepository.php
  4.  Entity generated! Now let's add some fields!
  5.  You can always add more fields later manually or by re-running this command.
  7.  New property name (press <return> to stop adding fields):
  8.  > city
  10.  Field type (enter ? to see all types) [string]:
  11.  >
  13.  Field length [255]:
  14.  >
  16.  Can this field be null in the database (nullable) (yes/no) [no]:
  17.  >
  19.  updated: src/Entity/Conference.php
  21.  Add another property? Enter the property name (or press <return> to stop adding fields):
  22.  > year
  24.  Field type (enter ? to see all types) [string]:
  25.  >
  27.  Field length [255]:
  28.  > 4
  30.  Can this field be null in the database (nullable) (yes/no) [no]:
  31.  >
  33.  updated: src/Entity/Conference.php
  35.  Add another property? Enter the property name (or press <return> to stop adding fields):
  36.  > isInternational
  38.  Field type (enter ? to see all types) [boolean]:
  39.  >
  41.  Can this field be null in the database (nullable) (yes/no) [no]:
  42.  >
  44.  updated: src/Entity/Conference.php
  46.  Add another property? Enter the property name (or press <return> to stop adding fields):
  47.  >
  50.   Success!
  52.  Next: When you're ready, create a migration with make:migration

The Conference class has been stored under the App\Entity\ namespace.

The command also generated a Doctrine repository class: App\Repository\ConferenceRepository.

The generated code looks like the following (only a small portion of the file is replicated here):

src/App/Entity/Conference.php

  1. namespace App\Entity;
  3. use App\Repository\ConferenceRepository;
  4. use Doctrine\ORM\Mapping as ORM;
  6. /**
  7.  * @ORM\Entity(repositoryClass=ConferenceRepository::class)
  8.  */
  9. class Conference
  10. {
  11.     /**
  12.      * @ORM\Id()
  13.      * @ORM\GeneratedValue()
  14.      * @ORM\Column(type="integer")
  15.      */
  16.     private $id;
  18.     /**
  19.      * @ORM\Column(type="string", length=255)
  20.      */
  21.     private $city;
  23.     // ...
  25.     public function getCity(): ?string
  26.     {
  27.         return $this->city;
  28.     }
  30.     public function setCity(string $city): self
  31.     {
  32.         $this->city = $city;
  34.         return $this;
  35.     }
  37.     // ...
  38. }

Note that the class itself is a plain PHP class with no signs of Doctrine. Annotations are used to add metadata useful for Doctrine to map the class to its related database table.

Doctrine added an id property to store the primary key of the row in the database table. This key (@ORM\Id()) is automatically generated (@ORM\GeneratedValue()) via a strategy that depends on the database engine.

Now, generate an Entity class for conference comments:

  1. $ symfony console make:entity Comment

Enter the following answers:

  • author, string, 255, no;
  • text, text, no;
  • email, string, 255, no;
  • createdAt, datetime, no.

Linking Entities

The two entities, Conference and Comment, should be linked together. A Conference can have zero or more Comments, which is called a one-to-many relationship.

Use the make:entity command again to add this relationship to the Conference class:

  1. $ symfony console make:entity Conference
  1.  Your entity already exists! So let's add some new fields!
  3.  New property name (press <return> to stop adding fields):
  4.  > comments
  6.  Field type (enter ? to see all types) [string]:
  7.  > OneToMany
  9.  What class should this entity be related to?:
  10.  > Comment
  12.  A new property will also be added to the Comment class...
  14.  New field name inside Comment [conference]:
  15.  >
  17.  Is the Comment.conference property allowed to be null (nullable)? (yes/no) [yes]:
  18.  > no
  20.  Do you want to activate orphanRemoval on your relationship?
  21.  A Comment is "orphaned" when it is removed from its related Conference.
  22.  e.g. $conference->removeComment($comment)
  24.  NOTE: If a Comment may *change* from one Conference to another, answer "no".
  26.  Do you want to automatically delete orphaned App\Entity\Comment objects (orphanRemoval)? (yes/no) [no]:
  27.  > yes
  29.  updated: src/Entity/Conference.php
  30.  updated: src/Entity/Comment.php

Note

If you enter ? as an answer for the type, you will get all supported types:

  1. Main types
  2.   * string
  3.   * text
  4.   * boolean
  5.   * integer (or smallint, bigint)
  6.   * float
  8. Relationships / Associations
  9.   * relation (a wizard will help you build the relation)
  10.   * ManyToOne
  11.   * OneToMany
  12.   * ManyToMany
  13.   * OneToOne
  15. Array/Object Types
  16.   * array (or simple_array)
  17.   * json
  18.   * object
  19.   * binary
  20.   * blob
  22. Date/Time Types
  23.   * datetime (or datetime_immutable)
  24.   * datetimetz (or datetimetz_immutable)
  25.   * date (or date_immutable)
  26.   * time (or time_immutable)
  27.   * dateinterval
  29. Other Types
  30.   * decimal
  31.   * guid
  32.   * json_array

Have a look at the full diff for the entity classes after adding the relationship:

  1. --- a/src/Entity/Comment.php
  2. +++ b/src/Entity/Comment.php
  3. @@ -36,6 +36,12 @@ class Comment
  4.       */
  5.      private $createdAt;
  7. +    /**
  8. +     * @ORM\ManyToOne(targetEntity=Conference::class, inversedBy="comments")
  9. +     * @ORM\JoinColumn(nullable=false)
  10. +     */
  11. +    private $conference;
  12. +
  13.      public function getId(): ?int
  14.      {
  15.          return $this->id;
  16. @@ -88,4 +94,16 @@ class Comment
  18.          return $this;
  19.      }
  20. +
  21. +    public function getConference(): ?Conference
  22. +    {
  23. +        return $this->conference;
  24. +    }
  25. +
  26. +    public function setConference(?Conference $conference): self
  27. +    {
  28. +        $this->conference = $conference;
  29. +
  30. +        return $this;
  31. +    }
  32.  }
  33. --- a/src/Entity/Conference.php
  34. +++ b/src/Entity/Conference.php
  35. @@ -2,6 +2,8 @@
  37.  namespace App\Entity;
  39. +use Doctrine\Common\Collections\ArrayCollection;
  40. +use Doctrine\Common\Collections\Collection;
  41.  use Doctrine\ORM\Mapping as ORM;
  43.  /**
  44. @@ -31,6 +33,16 @@ class Conference
  45.       */
  46.      private $isInternational;
  48. +    /**
  49. +     * @ORM\OneToMany(targetEntity=Comment::class, mappedBy="conference", orphanRemoval=true)
  50. +     */
  51. +    private $comments;
  52. +
  53. +    public function __construct()
  54. +    {
  55. +        $this->comments = new ArrayCollection();
  56. +    }
  57. +
  58.      public function getId(): ?int
  59.      {
  60.          return $this->id;
  61. @@ -71,4 +83,35 @@ class Conference
  63.          return $this;
  64.      }
  65. +
  66. +    /**
  67. +     * @return Collection|Comment[]
  68. +     */
  69. +    public function getComments(): Collection
  70. +    {
  71. +        return $this->comments;
  72. +    }
  73. +
  74. +    public function addComment(Comment $comment): self
  75. +    {
  76. +        if (!$this->comments->contains($comment)) {
  77. +            $this->comments[] = $comment;
  78. +            $comment->setConference($this);
  79. +        }
  80. +
  81. +        return $this;
  82. +    }
  83. +
  84. +    public function removeComment(Comment $comment): self
  85. +    {
  86. +        if ($this->comments->contains($comment)) {
  87. +            $this->comments->removeElement($comment);
  88. +            // set the owning side to null (unless already changed)
  89. +            if ($comment->getConference() === $this) {
  90. +                $comment->setConference(null);
  91. +            }
  92. +        }
  93. +
  94. +        return $this;
  95. +    }
  96.  }

Everything you need to manage the relationship has been generated for you. Once generated, the code becomes yours; feel free to customize it the way you want.

Adding more Properties

I just realized that we have forgotten to add one property on the Comment entity: attendees might want to attach a photo of the conference to illustrate their feedback.

Run make:entity once more and add a photoFilename property/column of type string, but allow it to be null as uploading a photo is optional:

  1. $ symfony console make:entity Comment

Migrating the Database

The project model is now fully described by the two generated classes.

Next, we need to create the database tables related to these PHP entities.

Doctrine Migrations is the perfect match for such a task. It has already been installed as part of the orm dependency.

A migration is a class that describes the changes needed to update a database schema from its current state to the new one defined by the entity annotations. As the database is empty for now, the migration should consist of two table creations.

Let’s see what Doctrine generates:

  1. $ symfony console make:migration

Notice the generated file name in the output (a name that looks like migrations/Version20191019083640.php):

migrations/Version20191019083640.php

  1. namespace DoctrineMigrations;
  3. use Doctrine\DBAL\Schema\Schema;
  4. use Doctrine\Migrations\AbstractMigration;
  6. final class Version20191019083640 extends AbstractMigration
  7. {
  8.     public function up(Schema $schema) : void
  9.     {
  10.         // this up() migration is auto-generated, please modify it to your needs
  11.         $this->addSql('CREATE SEQUENCE comment_id_seq INCREMENT BY 1 MINVALUE 1 START 1');
  12.         $this->addSql('CREATE SEQUENCE conference_id_seq INCREMENT BY 1 MINVALUE 1 START 1');
  13.         $this->addSql('CREATE TABLE comment (id INT NOT NULL, conference_id INT NOT NULL, author VARCHAR(255) NOT NULL, text TEXT NOT NULL, email VARCHAR(255) NOT NULL, created_at TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL, photo_filename VARCHAR(255) DEFAULT NULL, PRIMARY KEY(id))');
  14.         $this->addSql('CREATE INDEX IDX_9474526C604B8382 ON comment (conference_id)');
  15.         $this->addSql('CREATE TABLE conference (id INT NOT NULL, city VARCHAR(255) NOT NULL, year VARCHAR(4) NOT NULL, is_international BOOLEAN NOT NULL, PRIMARY KEY(id))');
  16.         $this->addSql('ALTER TABLE comment ADD CONSTRAINT FK_9474526C604B8382 FOREIGN KEY (conference_id) REFERENCES conference (id) NOT DEFERRABLE INITIALLY IMMEDIATE');
  17.     }
  19.     public function down(Schema $schema) : void
  20.     {
  21.         // ...
  22.     }
  23. }

Updating the Local Database

You can now run the generated migration to update the local database schema:

  1. $ symfony console doctrine:migrations:migrate

The local database schema is now up-to-date, ready to store some data.

Updating the Production Database

The steps needed to migrate the production database are the same as the ones you are already familiar with: commit the changes and deploy.

When deploying the project, SymfonyCloud updates the code, but also runs the database migration if any (it detects if the doctrine:migrations:migrate command exists).

Going Further

This work, including the code samples, is licensed under a Creative Commons BY-NC-SA 4.0 license.