Integrating a Database to Your App

Welcome to another course on Symfony. During this course, we will be adding enterprise-level features to your Symfony application, beginning with database integration using Doctrine ORM . Modern web applications rely heavily on efficient data storage, retrieval, and management. By adding a database to your Symfony application, you will enable persistent data storage, making your application more robust and dynamic. By the end of this lesson, you will be able to set up Doctrine in your Symfony project and configure it to connect to an SQLite database . Let's dive in!

What Is Doctrine and ORM Mapping?

Let's first understand what Doctrine and ORM (Object-Relational Mapping) are. ORM stands for Object-Relational Mapping. It is a technique that helps you manage the database operations (such as insert, update, delete, and query) using the object-oriented paradigm of PHP. Rather than dealing with raw SQL queries, you use PHP objects to interact with your data, making code cleaner and easier to understand. Doctrine is a powerful ORM library that allows you to map your PHP classes to database tables automatically. It abstracts the database operations and enables you to work with PHP objects instead of SQL statements. This makes the development process more intuitive and maintainable.

How Doctrine Works to Create Your Database Schema

As you map your PHP classes to database tables using Doctrine, the Doctrine ORM library handles the heavy lifting of generating and managing the corresponding database schema. This means that Doctrine translates the configuration and annotations you provide in your PHP classes into the actual SQL commands needed to create and maintain your database tables. Here's a high-level overview of the process: Entity Definition : You define your entity classes in PHP, specifying the fields and their types, along with any necessary configurations like primary keys and table names. Metadata Extraction : Doctrine extracts metadata from these entity classes, capturing information about how they should be represented in the database. Schema Tool : Using the extracted metadata, Doctrine's SchemaTool component generates the SQL statements required to create or update the database schema. Schema Management : When the application starts, these SQL statements are executed, ensuring that the database schema is up to date with your entity definitions. This can include creating new tables, adding or modifying columns, and more. By automating these steps, Doctrine ensures that your database schema always matches your application's domain model, reducing the need for manual SQL management and streamlining development.

Join the 1M+ learners on CodeSignal

Be a part of our community of 1M+ users who develop and demonstrate their skills on CodeSignal

Before you can integrate a database with Symfony using Doctrine, ensure you have the following:

PHP and SQLite Extension: Ensure your PHP version is compatible with the required SQLite extension. In this course, we are using PHP 8.1. You need the php8.1-sqlite3 package, which includes the necessary SQLite libraries. You can install this extension using your operating system's package manager (e.g., apt for Ubuntu):

Shell

sudo apt-get install php8.1-sqlite3

sudo apt-get install php8.1-sqlite3

Symfony ORM Pack: You need to install the symfony/orm-pack package, which provides all required dependencies for Doctrine ORM integration. You can do this using the following composer command:

Shell

composer require symfony/orm-pack

composer require symfony/orm-pack

These requirements are essential to set up and configure Doctrine ORM in your Symfony project. Once you have them in place, you can proceed to the configuration steps.

Configuring Doctrine in Symfony

The first step in integrating Doctrine into your Symfony project is to set up the configuration in the doctrine.yaml file, located in the config/packages/ directory.

Below is the doctrine.yaml configuration that sets up a connection to an SQLite database:

YAML

doctrine:
    dbal:
        driver: 'pdo_sqlite' # Type of database driver to use
        url: 'sqlite:///%kernel.project_dir%/var/data/db.sqlite' # Path to the SQLite database file

    orm:
        auto_generate_proxy_classes: true # Automatically create proxy classes
        auto_mapping: true # Automatically find and map entities
        mappings:
            App:
                is_bundle: false # This is not a Symfony bundle
                type: attribute # Use PHP attributes for entity mapping
                dir: '%kernel.project_dir%/src/Entity' # Folder where entity classes are stored
                prefix: 'App\Entity' # Namespace for entity classes
                alias: App # Shortcut name for the mapping

doctrine:
    dbal:
        driver: 'pdo_sqlite' # Type of database driver to use
        url: 'sqlite:///%kernel.project_dir%/var/data/db.sqlite' # Path to the SQLite database file

    orm:
        auto_generate_proxy_classes: true # Automatically create proxy classes
        auto_mapping: true # Automatically find and map entities
        mappings:
            App:
                is_bundle: false # This is not a Symfony bundle
                type: attribute # Use PHP attributes for entity mapping
                dir: '%kernel.project_dir%/src/Entity' # Folder where entity classes are stored
                prefix: 'App\Entity' # Namespace for entity classes
                alias: App # Shortcut name for the mapping

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

The DBAL (Database Access Layer) section specifies the type of database and its connection URL, essentially pointing Doctrine to where the database file is located at var/data/db.sqlite within your project directory.
The ORM (Object-Relational Mapping) section sets up how our PHP classes (entities) map to database tables. It includes configurations to automatically generate necessary proxy classes and map entities based on their attributes.
Additionally, it specifies where to find these entity classes and their namespace.

This setup ensures that the Doctrine ORM can seamlessly manage database operations using our PHP objects.

Now, let's see how we can map our Todo entity to a database table using Doctrine.

PHP

<?php

namespace App\Entity;

use Doctrine\ORM\Mapping as ORM;

#[ORM\Entity(repositoryClass: "App\Repository\TodoRepository")]
#[ORM\Table(name: "todos")]
class Todo
{
    #[ORM\Id]
    #[ORM\GeneratedValue]
    #[ORM\Column(type: "integer")]
    private $id;

    #[ORM\Column(type: "string", length: 255)]
    private $title;

    #[ORM\Column(type: "text", nullable: true)]
    private $description;

    // Getters and setters...
}

<?php

namespace App\Entity;

use Doctrine\ORM\Mapping as ORM;

#[ORM\Entity(repositoryClass: "App\Repository\TodoRepository")]
#[ORM\Table(name: "todos")]
class Todo
{
    #[ORM\Id]
    #[ORM\GeneratedValue]
    #[ORM\Column(type: "integer")]
    private $id;

    #[ORM\Column(type: "string", length: 255)]
    private $title;

    #[ORM\Column(type: "text", nullable: true)]
    private $description;

    // Getters and setters...
}

The code above demonstrates a simple entity class named Todo.

The attribute #[ORM\Entity(repositoryClass: "App\Repository\TodoRepository")] and #[ORM\Table(name: "todos")] indicate that this class is an entity, specify its repository class, and map it to the todos table in the database.
The id property is configured as the primary key with auto-generated values.
The title property is mapped to a string column with a maximum length of 255 characters.
The description property is mapped to a nullable text column, allowing longer text entries or null values if not provided.

This setup enables Doctrine to handle the database operations for Todo objects, making it easier to manage your data with PHP classes.

After defining the entity, it's important to create a repository for our Todo entity. A repository is a designated class that handles the retrieval of data from the database. It serves as a middle layer between your application logic and the database, encapsulating the logic needed to access data. This aligns with the Model part of the MVC (Model-View-Controller) pattern, acting as the data layer for our application.

Here's how you can create the TodoRepository:

PHP

<?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);
    }
}

<?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);
    }
}

In this code:

The TodoRepository class extends ServiceEntityRepository, a base class provided by Doctrine to help with common data retrieval tasks.
The __construct method accepts a ManagerRegistry object, which provides access to various Doctrine components. This registry is passed to the parent class, along with the Todo entity class, to set up the repository.
This repository will now handle all the database interactions for the Todo entity, allowing us to write clean and maintainable code.

With both the Todo entity and its repository in place, Doctrine can effectively manage the database operations for Todo objects, adhering to the MVC pattern by clearly separating the data handling logic.

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.

The Kernel is the core of the Symfony application. It manages the request lifecycle, loads services, and routes requests to the correct controller. In this file, we will initialize the Kernel, integrate Doctrine's Entity Manager, and manage the database schema.

Let's break down how to configure it to initialize Doctrine and manage the database schema.

First, let's set up the entry point to initialize the Symfony application by creating a new Kernel instance and booting it.

PHP

<?php

use App\Kernel;

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

return function (array $context) {
    // Create a new Kernel instance and boot it
    $kernel = new Kernel($context['APP_ENV'], (bool) $context['APP_DEBUG']);
    $kernel->boot();

    return $kernel;
};

In this code:

We require the Composer autoloader to load all installed packages.
We create and start (boot) a new instance of the Kernel, which is responsible for initializing various services, loading bundles, and handling the request lifecycle in the Symfony application. This ensures that the application's environment and debug settings are correctly applied.

Next, we will integrate the Doctrine Entity Manager, which handles all interactions with the database.

PHP

<?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');

    return $kernel;
};

In this code:

We get the Entity Manager from the Symfony container. This manager handles all database interactions using Doctrine.

Finally, we will configure the SchemaTool to manage the database schema, ensuring that the necessary tables are created or updated based on the entity metadata.

PHP

<?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();

    $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 code:

We set up SchemaTool, a Doctrine tool to manage the database schema.
We retrieve metadata for all entities, which provides information about how the objects are mapped to the database.
The updateSchema method is used to create or update the schema based on the entity metadata, ensuring that the schema is always in sync with our PHP objects.

This setup ensures that your application's database schema is correctly managed whenever the application starts up.

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:

text

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

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 255 characters.
description: A text field that can hold longer entries or be left blank.

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.

In this lesson, we successfully set up Doctrine ORM in your Symfony project. We:

Installed the necessary packages.
Configured Doctrine to connect to an SQLite database.
Defined a Todo entity and mapped it to a database table.
Created a TodoRepository to handle data retrieval.
Configured the Symfony application's entry point to ensure our database schema is automatically updated based on our entity definitions.

With this setup, your application will now create and update the database schema based on your entities every time it starts. However, we haven't yet added any database logic to our service layer. This means that while the database structure is ready, our application doesn't yet perform any actual database operations.

If you try to add any items to the application right now, it won't work because we haven't implemented the logic to handle these operations just yet. In this unit's tasks, we will only explore how the database schema is being created, not performing any operations. We'll cover how to add this logic in our next lesson, enabling our application to perform real database interactions.

Stay tuned!