Ebooks

Symfony DBAL tutorial

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

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 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 annotations
$ composer require maker

In addition, we install additional two packages: annotations and maker. The annotations package contains a library which parses documentation strings; we need this for routing. 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.

$ bin/console make:controller MyController

A MyController is created with bin/console.

src/Controller/MyController.php
<?php

namespace App\Controller;

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

class MyController extends Controller
{
    /**
     * @Route("/getdata", name="getdata")
     */
    public function data(Connection $conn)
    {
        $data = $conn->fetchAll("SELECT * FROM countries LIMIT 5");

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

The controller method executes a query that fetches five rows from the table and returs it as JSON data.

public function data(Connection $conn)
{

The DBAL Connection object is injected into the method variable.

$data = $conn->fetchAll("SELECT * FROM countries LIMIT 5");

We use the fetchAll() method to execute the query.

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

The data is returned in JSON format.

$ bin/console server:start

The web server is started.

$ curl localhost:8000/getdata
{"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"}]}

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, Symfony form tutorial, Symfony translation tutorial, PHP tutorial.