SymfonyWorld Online 2020
100% online
30+ talks + workshops
Live + Replay watch talks later

Step 8: Describing the Data Structure

5.0 version
Maintained

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

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.

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
2
DATABASE_URL=postgres://main:[email protected]:32781/main?sslmode=disable&charset=utf8
# ...

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_DSN to use PostgreSQL:

1
2
3
4
5
6
7
8
9
--- a/.env
+++ b/.env
@@ -25,5 +25,5 @@ APP_SECRET=447c9fa8420eb53bbd4492194b87de8f
 # For an SQLite database, use: "sqlite:///%kernel.project_dir%/var/data.db"
 # For a PostgreSQL database, use: "postgresql://db_user:[email protected]:5432/db_name?serverVersion=11&charset=utf8"
 # IMPORTANT: You MUST configure your server version, either here or in config/packages/doctrine.yaml
-DATABASE_URL=mysql://db_user:[email protected]:3306/db_name?serverVersion=5.7
+DATABASE_URL=postgresql://127.0.0.1:5432/db?serverVersion=11&charset=utf8
 ###< 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
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
 created: src/Entity/Conference.php
 created: src/Repository/ConferenceRepository.php

 Entity generated! Now let's add some fields!
 You can always add more fields later manually or by re-running this command.

 New property name (press <return> to stop adding fields):
 > city

 Field type (enter ? to see all types) [string]:
 >

 Field length [255]:
 >

 Can this field be null in the database (nullable) (yes/no) [no]:
 >

 updated: src/Entity/Conference.php

 Add another property? Enter the property name (or press <return> to stop adding fields):
 > year

 Field type (enter ? to see all types) [string]:
 >

 Field length [255]:
 > 4

 Can this field be null in the database (nullable) (yes/no) [no]:
 >

 updated: src/Entity/Conference.php

 Add another property? Enter the property name (or press <return> to stop adding fields):
 > isInternational

 Field type (enter ? to see all types) [boolean]:
 >

 Can this field be null in the database (nullable) (yes/no) [no]:
 >

 updated: src/Entity/Conference.php

 Add another property? Enter the property name (or press <return> to stop adding fields):
 >



  Success!


 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
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
namespace App\Entity;

use App\Repository\ConferenceRepository;
use Doctrine\ORM\Mapping as ORM;

/**
 * @ORM\Entity(repositoryClass=ConferenceRepository::class)
 */
class Conference
{
    /**
     * @ORM\Id()
     * @ORM\GeneratedValue()
     * @ORM\Column(type="integer")
     */
    private $id;

    /**
     * @ORM\Column(type="string", length=255)
     */
    private $city;

    // ...

    public function getCity(): ?string
    {
        return $this->city;
    }

    public function setCity(string $city): self
    {
        $this->city = $city;

        return $this;
    }

    // ...
}

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
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
 Your entity already exists! So let's add some new fields!

 New property name (press <return> to stop adding fields):
 > comments

 Field type (enter ? to see all types) [string]:
 > OneToMany

 What class should this entity be related to?:
 > Comment

 A new property will also be added to the Comment class...

 New field name inside Comment [conference]:
 >

 Is the Comment.conference property allowed to be null (nullable)? (yes/no) [yes]:
 > no

 Do you want to activate orphanRemoval on your relationship?
 A Comment is "orphaned" when it is removed from its related Conference.
 e.g. $conference->removeComment($comment)

 NOTE: If a Comment may *change* from one Conference to another, answer "no".

 Do you want to automatically delete orphaned App\Entity\Comment objects (orphanRemoval)? (yes/no) [no]:
 > yes

 updated: src/Entity/Conference.php
 updated: src/Entity/Comment.php

Note

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
Main types
  * string
  * text
  * boolean
  * integer (or smallint, bigint)
  * float

Relationships / Associations
  * relation (a wizard will help you build the relation)
  * ManyToOne
  * OneToMany
  * ManyToMany
  * OneToOne

Array/Object Types
  * array (or simple_array)
  * json
  * object
  * binary
  * blob

Date/Time Types
  * datetime (or datetime_immutable)
  * datetimetz (or datetimetz_immutable)
  * date (or date_immutable)
  * time (or time_immutable)
  * dateinterval

Other Types
  * decimal
  * guid
  * json_array

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
--- a/src/Entity/Comment.php
+++ b/src/Entity/Comment.php
@@ -36,6 +36,12 @@ class Comment
      */
     private $createdAt;

+    /**
+     * @ORM\ManyToOne(targetEntity=Conference::class, inversedBy="comments")
+     * @ORM\JoinColumn(nullable=false)
+     */
+    private $conference;
+
     public function getId(): ?int
     {
         return $this->id;
@@ -88,4 +94,16 @@ class Comment

         return $this;
     }
+
+    public function getConference(): ?Conference
+    {
+        return $this->conference;
+    }
+
+    public function setConference(?Conference $conference): self
+    {
+        $this->conference = $conference;
+
+        return $this;
+    }
 }
--- a/src/Entity/Conference.php
+++ b/src/Entity/Conference.php
@@ -2,6 +2,8 @@

 namespace App\Entity;

+use Doctrine\Common\Collections\ArrayCollection;
+use Doctrine\Common\Collections\Collection;
 use Doctrine\ORM\Mapping as ORM;

 /**
@@ -31,6 +33,16 @@ class Conference
      */
     private $isInternational;

+    /**
+     * @ORM\OneToMany(targetEntity=Comment::class, mappedBy="conference", orphanRemoval=true)
+     */
+    private $comments;
+
+    public function __construct()
+    {
+        $this->comments = new ArrayCollection();
+    }
+
     public function getId(): ?int
     {
         return $this->id;
@@ -71,4 +83,35 @@ class Conference

         return $this;
     }
+
+    /**
+     * @return Collection|Comment[]
+     */
+    public function getComments(): Collection
+    {
+        return $this->comments;
+    }
+
+    public function addComment(Comment $comment): self
+    {
+        if (!$this->comments->contains($comment)) {
+            $this->comments[] = $comment;
+            $comment->setConference($this);
+        }
+
+        return $this;
+    }
+
+    public function removeComment(Comment $comment): self
+    {
+        if ($this->comments->contains($comment)) {
+            $this->comments->removeElement($comment);
+            // set the owning side to null (unless already changed)
+            if ($comment->getConference() === $this) {
+                $comment->setConference(null);
+            }
+        }
+
+        return $this;
+    }
 }

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
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
namespace DoctrineMigrations;

use Doctrine\DBAL\Schema\Schema;
use Doctrine\Migrations\AbstractMigration;

final class Version20191019083640 extends AbstractMigration
{
    public function up(Schema $schema) : void
    {
        // this up() migration is auto-generated, please modify it to your needs
        $this->addSql('CREATE SEQUENCE comment_id_seq INCREMENT BY 1 MINVALUE 1 START 1');
        $this->addSql('CREATE SEQUENCE conference_id_seq INCREMENT BY 1 MINVALUE 1 START 1');
        $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))');
        $this->addSql('CREATE INDEX IDX_9474526C604B8382 ON comment (conference_id)');
        $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))');
        $this->addSql('ALTER TABLE comment ADD CONSTRAINT FK_9474526C604B8382 FOREIGN KEY (conference_id) REFERENCES conference (id) NOT DEFERRABLE INITIALLY IMMEDIATE');
    }

    public function down(Schema $schema) : void
    {
        // ...
    }
}

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).


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