Ebooks

Symfony DBAL tutorial

Symfony DBAL tutorial shows how to use the Doctrine DBAL component to work with a database in a Symfony application.

Symfony

Symfony is a set of reusable PHP components and a PHP framework for web projects. Symfony was published as free software in 2005. Fabien Potencier is the original author of Symfony. Symfony was inspired by Ruby on Rails, Django, and the Spring framework.

Doctrine DBAL component

Doctrine Database Abstraction Layer (DBAL) is an abstraction layer that sits on top of PDO and offers an intuitive and flexible API for communicating with the most popular relational databases. DBAL library makes it easy to execute queries and perform other database actions.

Symfony DBAL example

In the following example, we create a simple Symfony application that reads data using DBAL. We use MySQL database.

$ composer create-project symfony/skeleton symdbal

With composer, we create a new Symfony skeleton project.

$ cd symdbal

We go to the project directory.

$ composer require symfony/orm-pack

We install the symfony/orm-pack, which contains the DBAL API.

$ composer require maker --dev

In addition, we install the maker component. The maker package provides scaffolding.

$ composer require server --dev

We install the development web server.

countries_mysql.sql
CREATE TABLE countries(id BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT, 
    name VARCHAR(100), population INT);

INSERT INTO countries(name, population) VALUES('China', 1382050000);
INSERT INTO countries(name, population) VALUES('India', 1313210000);
INSERT INTO countries(name, population) VALUES('USA', 324666000);
INSERT INTO countries(name, population) VALUES('Indonesia', 260581000);
INSERT INTO countries(name, population) VALUES('Brazil', 207221000);
INSERT INTO countries(name, population) VALUES('Pakistan', 196626000);
INSERT INTO countries(name, population) VALUES('Nigeria', 186988000);
INSERT INTO countries(name, population) VALUES('Bangladesh', 162099000);
INSERT INTO countries(name, population) VALUES('Nigeria', 186988000);
INSERT INTO countries(name, population) VALUES('Russia', 146838000);
INSERT INTO countries(name, population) VALUES('Japan', 126830000);
INSERT INTO countries(name, population) VALUES('Mexico', 122273000);
INSERT INTO countries(name, population) VALUES('Philippines', 103738000);

This is some test data. It creates a small table in MySQL. We can execute the file with the source command.

.env
...
DATABASE_URL=mysql://user12:s$cret@localhost:3306/mydb

In the .env file, we configure the database URL.

$ php bin/console make:controller HomeController

The HomeController is created with the Symfony maker.

src/Controller/HomeController.php
<?php

namespace App\Controller;

use Doctrine\DBAL\Connection;
use Symfony\Component\Routing\Annotation\Route;
use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;

class HomeController extends AbstractController
{
    /**
     * @Route("/home", name="home")
     */
    public function index(Connection $conn)
    {
        $queryBuilder = $conn->createQueryBuilder();
        $data = $queryBuilder->select('*')->from('countries')->execute()->fetchAll();

        return $this->json([
            'data' => $data
        ]);
    }
}

The controller method executes a query that fetches all rows from the countries table and returns it as JSON data. Note that for simplicity reasons, we have placed the query builder into the controller. In production applications, there should also be a service layer and a repository.

public function index(Connection $conn)
{

The DBAL Connection object is injected into the method variable.

$queryBuilder = $conn->createQueryBuilder();

We create a QueryBuilder from the connection.

$data = $queryBuilder->select('*')->from('countries')->execute()->fetchAll();

We execute the query and fetch all rows.

return $this->json([
    'data' => $data
]);

The data is returned in JSON format.

$ php bin/console server:run

The web server is started.

$ curl localhost:8000/home
{"data":[{"id":"1","name":"China","population":"1382050000"},
{"id":"2","name":"India","population":"1313210000"},
{"id":"3","name":"USA","population":"324666000"},
{"id":"4","name":"Indonesia","population":"260581000"},
{"id":"5","name":"Brazil","population":"207221000"},
{"id":"6","name":"Pakistan","population":"196626000"},
{"id":"7","name":"Nigeria","population":"186988000"},
{"id":"8","name":"Bangladesh","population":"162099000"},
{"id":"9","name":"Nigeria","population":"186988000"},
{"id":"10","name":"Russia","population":"146838000"},
{"id":"11","name":"Japan","population":"126830000"},
{"id":"12","name":"Mexico","population":"122273000"},
{"id":"13","name":"Philippines","population":"103738000"}]}

We use the curl tool to execute the GET request.

In this tutorial we have worked with Doctrine DBAL in Symfony.

You might also be interested in the following related tutorials: Introduction to Symfony, Doctrine DBAL QueryBuilder tutorial, Symfony form tutorial, Symfony translation tutorial, PHP tutorial.