Integrating a Database to Your Symfony App

Welcome to our Symfony course. In this lesson, we will be adding an important feature to your Symfony application: database integration using Doctrine ORM.

Persistent data storage is crucial for modern web applications. Integrating a database into your Symfony project will allow you to store, retrieve, and manage data, making your app more dynamic.

By the end of this lesson, you will have set up Doctrine and connected it to a SQLite database in your Symfony project. Let’s get started!

Before diving into the implementation, let's understand Doctrine and ORM (Object-Relational Mapping).

Doctrine is a powerful ORM library that maps PHP classes to database tables. It simplifies database interactions, allowing you to work with PHP objects instead of writing SQL queries. This keeps your code cleaner and easier to maintain.

ORM is a technique that helps manage database operations like inserting, updating, deleting, and querying data using the object-oriented structure of PHP. By using ORM, you avoid raw SQL and focus on handling data as PHP objects.

Before setting up a database in Symfony, ensure the following requirements are met:

1. PHP and SQLite Extension: Make sure your PHP version supports SQLite. For this lesson, we're using PHP 8.1. You can install the php8.1-sqlite3 package to add SQLite support:

sudo apt-get install php8.1-sqlite3

2. Symfony ORM Pack: You need to install the symfony/orm-pack package, which includes all the necessary dependencies for Doctrine ORM:

composer require symfony/orm-pack

Once these are installed, you’re ready to proceed.

Now that the required packages are installed, we need to configure Doctrine to connect to an SQLite database. The configuration file is located at config/packages/doctrine.yaml.

Here’s a basic configuration to connect Symfony to an SQLite database:

doctrine:
    dbal:
        url: '%env(resolve:DATABASE_URL)%'
        # IMPORTANT: You MUST configure your server version,
        # either here or in the DATABASE_URL env var (see .env file)
        #server_version: '13'
    orm:
        auto_generate_proxy_classes: true
        naming_strategy: doctrine.orm.naming_strategy.underscore_number_aware
        auto_mapping: true
        mappings:
            App:
                is_bundle: false
                type: annotation
                dir: '%kernel.project_dir%/src/Entity'
                prefix: 'App\Entity'
                alias: App

This file configures Doctrine, allowing it to interact with an SQLite database. Here’s what it does:

• DBAL: The Database Abstraction Layer (DBAL) tells Symfony which database driver to use. Here, it's SQLite, and the URL is resolved from an environment variable.
• ORM: Object-Relational Mapping configuration. It tells Doctrine to automatically map PHP classes (entities) to database tables and where to find these entity classes.
  • auto_generate_proxy_classes: Enables automatic generation of proxy classes, useful for development environments where you frequently change entity classes.
  • naming_strategy: Defines the strategy Doctrine uses to create database table and column names from entity and field names, keeping them consistent and understandable.
  • auto_mapping: Automatically maps all entity directories found within your app, simplifying setup in projects using default conventions.
  • mappings: Specifies where Doctrine should look for entity classes, defining the connection between your PHP code and the database schema.

With this, Doctrine can now interact with your SQLite database.

Next, we will create an entity representing the Todo task we want to store in the database.

<?php

namespace App\Entity;

use Doctrine\ORM\Mapping as ORM;

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

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

    /**
     * @ORM\Column(type="text", nullable=true)
     */
    private $description;

    /**
     * @ORM\Column(type="string", nullable=true)
     */
    private $file;

    // Getters and setters...
}

The above code demonstrates a simple entity class named Todo.

• @ORM\Entity: Marks this class as an entity, linked to a database table.
• Fields:
  • id: Primary key with auto-increment.
  • title: A string column with a maximum length of 100 characters.
  • description: A text column that can be left empty.
  • file: An optional string column.

This structure allows Doctrine to map this PHP object to a corresponding database table automatically.

The repository class allows us to manage database operations for the Todo entity. Here’s how we can create it:

<?php

namespace App\Repository;

use App\Entity\ToDo;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\Persistence\ManagerRegistry;

class ToDoRepository extends ServiceEntityRepository
{
    public function __construct(ManagerRegistry $registry)
    {
        parent::__construct($registry, ToDo::class);
    }
}

By creating a ToDoRepository, we encapsulate all the database operations related to the Todo entity in a single, reusable location. This repository will handle interactions with the todos table, such as fetching, saving, and deleting Todo entities. It extends ServiceEntityRepository, which provides convenient methods for these common database operations. The ManagerRegistry interface ensures our repository is correctly linked to the ToDo entity and its associated database table. This approach keeps our code organized and makes it easier to manage Todo data efficiently.

With our Todo entity and TodoRepository effectively set up, we now need to ensure our Symfony application's entry point is configured to utilize Doctrine and manage our database schema. The index.php file in the public directory is the entry point of your application. It is responsible for starting your application, by setting up the Kernel and handling HTTP requests and responses.

Here's how to set it up:

<?php

use App\Kernel;
use Doctrine\ORM\Tools\SchemaTool;

require_once dirname(__DIR__).'/vendor/autoload_runtime.php';

return function (array $context) {
    $kernel = new Kernel($context['APP_ENV'], (bool) $context['APP_DEBUG']);
    $kernel->boot();

    // Get the Entity Manager from the container
    $entityManager = $kernel->getContainer()->get('doctrine.orm.entity_manager');

    // Initialize Doctrine's SchemaTool to manage the database schema
    $schemaTool = new SchemaTool($entityManager);

    // Retrieve metadata for all entities
    $metadata = $entityManager->getMetadataFactory()->getAllMetadata();

    if (!empty($metadata)) {
        // Update the schema to reflect the current entity metadata
        $schemaTool->updateSchema($metadata, true);
    }

    return $kernel;
};

In this merged setup, we initialize the Symfony Kernel, retrieve the Doctrine Entity Manager, and use SchemaTool to manage the database schema. This ensures that the necessary tables are created or updated according to the entity metadata.

Once you set up your Todo entity and configure Doctrine, it will automatically create the necessary tables in your SQLite database. Here is an example of what you might see when you inspect the database:

Tables found in the database:
- todos
- sqlite_sequence

Schema for table 'todos':
  - id (INTEGER)
  - title (VARCHAR(255))
  - description (CLOB)

Schema for table 'sqlite_sequence':
  - name ()
  - seq ()

The todos table corresponds to our Todo entity. It contains the following columns:

• id: An integer that automatically increments for each new Todo.
• title: A string with a maximum length of 100 characters.
• description: A text field that can hold longer entries or be left blank.
• file: An optional string field.

The sqlite_sequence table is automatically created by SQLite. It keeps track of the auto-incremented values for tables with such columns.

With this setup, both the todos table and the internal sqlite_sequence table are created and managed efficiently, enabling smooth database operations for your Todo entity.

Now it's your turn! Head over to the practice exercises and configure the Doctrine settings in your Symfony project. This hands-on practice will reinforce your understanding and give you the confidence to set up database connectivity in any Symfony application.