Step 7: Setting up a Database

Setting up a Database

The Conference Guestbook website is about gathering feedback during conferences. We need to store the comments contributed by the conference attendees in a permanent storage.

A comment is best described by a fixed data structure: an author, their email, the text of the feedback, and an optional photo. The kind of data that can be best stored in a traditional relational database engine.

PostgreSQL is the database engine we will use.

Adding PostgreSQL to Docker Compose

On our local machine, we have decided to use Docker to manage services. Create a docker-compose.yaml file and add PostgreSQL as a service:

docker-compose.yaml

  1. version: '3'
  3. services:
  4.     database:
  5.         image: postgres:13-alpine
  6.         environment:
  7.             POSTGRES_USER: main
  8.             POSTGRES_PASSWORD: main
  9.             POSTGRES_DB: main
  10.         ports: [5432]

This will install a PostgreSQL server and configure some environment variables that control the database name and credentials. The values do not really matter.

We also expose the PostgreSQL port (5432) of the container to the local host. That will help us access the database from our machine.

Note

The pdo_pgsql extension should have been installed when PHP was set up in a previous step.

Starting Docker Compose

Start Docker Compose in the background (-d):

  1. $ docker-compose up -d

Wait a bit to let the database start up and check that everything is running fine:

  1. $ docker-compose ps
  3.         Name                      Command              State            Ports
  4. ---------------------------------------------------------------------------------------
  5. guestbook_database_1   docker-entrypoint.sh postgres   Up      0.0.0.0:32780->5432/tcp

If there are no running containers or if the State column does not read Up, check the Docker Compose logs:

  1. $ docker-compose logs

Accessing the Local Database

Using the psql command-line utility might prove useful from time to time. But you need to remember the credentials and the database name. Less obvious, you also need to know the local port the database runs on the host. Docker chooses a random port so that you can work on more than one project using PostgreSQL at the same time (the local port is part of the output of docker-compose ps).

If you run psql via the Symfony CLI, you don’t need to remember anything.

The Symfony CLI automatically detects the Docker services running for the project and exposes the environment variables that psql needs to connect to the database.

Thanks to these conventions, accessing the database via symfony run is much easier:

  1. $ symfony run psql

Note

If you don’t have the psql binary on your local host, you can also run it via docker-compose:

  1. $ docker-compose exec database psql main

Dumping and Restoring Database Data

Use pg_dump to dump the database data:

  1. $ symfony run pg_dump --data-only > dump.sql

And restore the data:

  1. $ symfony run psql < dump.sql

Warning

Never call docker-compose down if you don’t want to lose data. Or backup first.

Adding PostgreSQL to SymfonyCloud

For the production infrastructure on SymfonyCloud, adding a service like PostgreSQL should be done in the currently empty .symfony/services.yaml file:

.symfony/services.yaml

  1. db:
  2.     type: postgresql:13
  3.     disk: 1024
  4.     size: S

The db service is a PostgreSQL database (same version as for Docker) that we want to provision on a small container with 1GB of disk.

We also need to “link” the DB to the application container, which is described in .symfony.cloud.yaml:

.symfony.cloud.yaml

  1. relationships:
  2.     database: "db:postgresql"

The db service of type postgresql is referenced as database on the application container.

The last step is to add the pdo_pgsql extension to the PHP runtime:

.symfony.cloud.yaml

  1. runtime:
  2.     extensions:
  3.         - pdo_pgsql
  4.         # other extensions here

Here is the full diff for the .symfony.cloud.yaml changes:

patch_file

  1. --- a/.symfony.cloud.yaml
  2. +++ b/.symfony.cloud.yaml
  3. @@ -4,6 +4,7 @@ type: php:7.4
  5.  runtime:
  6.      extensions:
  7. +        - pdo_pgsql
  8.          - apcu
  9.          - mbstring
  10.          - sodium
  11. @@ -21,6 +22,9 @@ build:
  13.  disk: 512
  15. +relationships:
  16. +    database: "db:postgresql"
  17. +
  18.  web:
  19.      locations:
  20.          "/":

Commit these changes and then re-deploy to SymfonyCloud:

  1. $ git add .
  2. $ git commit -m'Configuring the database'
  3. $ symfony deploy

Accessing the SymfonyCloud Database

PostgreSQL is now running both locally via Docker and in production on SymfonyCloud.

As we have just seen, running symfony run psql automatically connects to the database hosted by Docker thanks to environment variables exposed by symfony run.

If you want to connect to PostgreSQL hosted on the production containers, you can open an SSH tunnel between the local machine and the SymfonyCloud infrastructure:

  1. $ symfony tunnel:open --expose-env-vars

By default, SymfonyCloud services are not exposed as environment variables on the local machine. You must explicitly do so by using the --expose-env-vars flag. Why? Connecting to the production database is a dangerous operation. You can mess with real data. Requiring the flag is how you confirm that this is what you want to do.

Now, connect to the remote PostgreSQL database via symfony run psql as before:

  1. $ symfony run psql

When done, don’t forget to close the tunnel:

  1. $ symfony tunnel:close

Tip

To run some SQL queries on the production database instead of getting a shell, you can also use the symfony sql command.

Exposing Environment Variables

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

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

  1. $ symfony var:export
  3. PGHOST=127.0.0.1
  4. PGPORT=32781
  5. PGDATABASE=main
  6. PGUSER=main
  7. PGPASSWORD=main
  8. # ...

The PG* environment variables are read by the psql utility. What about the others?

When a tunnel is open to SymfonyCloud with the --expose-env-vars flag set, the var:export command returns remote environment variables:

  1. $ symfony tunnel:open --expose-env-vars
  2. $ symfony var:export
  3. $ symfony tunnel:close

Describing your Infrastructure

You might not have realized it yet, but having the infrastructure stored in files alongside of the code helps a lot. Docker and SymfonyCloud use configuration files to describe the project infrastructure. When a new feature needs an additional service, the code changes and the infrastructure changes are part of the same patch.

Going Further

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