DEV Community

Cover image for Building a Dynamic API in Symfony with Doctrine and MySQL
AddWeb Solution Pvt Ltd profile image Balasaranya Varadhalingam
Balasaranya Varadhalingam for AddWeb Solution Pvt Ltd

Posted on

Building a Dynamic API in Symfony with Doctrine and MySQL

#symfony #doctrine #restapi #mysql

Take your Symfony API to the next level by connecting it to a real database. In this blog, you’ll learn how to use Doctrine ORM and MySQL to store, retrieve, and serve dynamic data making your backend ready for React, Vue, Angular, or any modern frontend.

Building a Dynamic API in Symfony with Doctrine and MySQL

Index

  1. Introduction
    • Step 1: Setting Up Database Configuration in Symfony
    • Step 2: Installing Doctrine ORM
    • Step 3: Creating the Entity
    • Step 4: Running Migrations
      • Generate the migration file
      • Apply the migration to the database
      • Verify the table was created
    • Step 5: Adding and Retrieving Products from the Database
      • To Create the Fixtures Class
      • Load the Fixtures into the Database
      • Update the API Controller to Fetch from the Database
    • Step 6: Viewing the Data in React
  2. Stats
  3. Key Takeaways
  4. Interesting Facts
  5. FAQs
  6. Conclusion
  7. About the Author
  8. SEO Settings
  9. Hashtags

Introduction

APIs become far more valuable when they serve real, dynamic data instead of fixed, hard-coded responses. In this guide, we’ll extend our Symfony API to connect with a MySQL database, enabling it to store, retrieve, and return data in real time. Using Doctrine ORM, we’ll map database tables to PHP entities, run migrations to set up the schema, and integrate the data into our API endpoints. By the end, you’ll have a fully functional, database-powered API that’s ready to serve any frontend application.

“The man who moves a mountain begins by carrying away small stones.” - Confucius

Step 1: Setting Up Database Configuration in Symfony
Before we can store and retrieve data, Symfony needs to know how to connect to your database. This is configured in the .env file located in the root of your project.
Open .env in your editor and look for the DATABASE_URL line.

DATABASE_URL="mysql://app:!ChangeMe!@127.0.0.1:3306/app?serverVersion=8.0.32&charset=utf8mb4"
Enter fullscreen mode Exit fullscreen mode

Replace it with your MySQL connection details. For Example:

DATABASE_URL="mysql://root:root@127.0.0.1:3306/symfony-app?serverVersion=8.0.32&charset=utf8mb4"
Enter fullscreen mode Exit fullscreen mode

Explanation of each part:
root:root : The database username and password.
127.0.0.1:3306 : Host and port where MySQL is running.
symfony : The name of your database.
serverVersion=8.0.32 : The exact MySQL version you’re using.
charset=utf8mb4 : Character encoding for full Unicode support (including emojis).

Tip: Store sensitive credentials in .env.local instead of .env so they are not committed to version control.

Step 2: Installing Doctrine ORM
Doctrine is Symfony’s default ORM (Object Relational Mapper). It acts as a bridge between your PHP objects (entities) and the database tables. We’ll use it to create tables, store data, and fetch it in our API.

To install Doctrine ORM and related tools, run the following command:

composer require symfony/orm-pack
composer require --dev doctrine/doctrine-fixtures-bundle
Enter fullscreen mode Exit fullscreen mode

What these packages do:

  • symfony/orm-pack : Installs Doctrine ORM, database migrations, and configuration files.
  • doctrine/doctrine-fixtures-bundle : Allows you to load sample data for testing. (optional but useful during development)

Once installed, Symfony will create a default config/packages/doctrine.yaml file where Doctrine’s settings are stored. It will also automatically use the DATABASE_URL you set up in Step 1.

To verify that Doctrine is installed, run:

php bin/console doctrine:database:create
Enter fullscreen mode Exit fullscreen mode

If everything is configured correctly, you’ll see a message confirming that the database has been created.

Created database `symfony-app` for connection named default
Enter fullscreen mode Exit fullscreen mode

Step 3: Creating the Entity

In the previous blog, our /api/products endpoint returned hardcoded JSON data.
Now, we’ll make it dynamic by connecting it to a database table that stores real product entries.
To do this, we need a Product entity that Doctrine ORM will map to a database table.
An Entity in Symfony represents a database table, with each property mapping to a column. We’ll create a Product entity containing title, description, and createdAt fields.

Run the following command in your terminal:

php bin/console make:entity
Enter fullscreen mode Exit fullscreen mode

When prompted, enter:

Defining the Product entity with title, description, and createdAt fields.

Symfony will generate src/Entity/Product.php.
It will look like this:

<?php

namespace App\Entity;

use App\Repository\ProductRepository;
use Doctrine\DBAL\Types\Types;
use Doctrine\ORM\Mapping as ORM;

#[ORM\Entity(repositoryClass: ProductRepository::class)]
class Product
{
    #[ORM\Id]
    #[ORM\GeneratedValue]
    #[ORM\Column]
    private ?int $id = null;

    #[ORM\Column(length: 255)]
    private ?string $title = null;

    #[ORM\Column(type: Types::TEXT)]
    private ?string $description = null;

    #[ORM\Column]
    private ?\DateTimeImmutable $createdAt = null;

    public function getId(): ?int
    {
        return $this->id;
    }

    public function getTitle(): ?string
    {
        return $this->title;
    }

    public function setTitle(string $title): static
    {
        $this->title = $title;

        return $this;
    }

    public function getDescription(): ?string
    {
        return $this->description;
    }

    public function setDescription(string $description): static
    {
        $this->description = $description;

        return $this;
    }

    public function getCreatedAt(): ?\DateTimeImmutable
    {
        return $this->createdAt;
    }

    public function setCreatedAt(\DateTimeImmutable $createdAt): static
    {
        $this->createdAt = $createdAt;

        return $this;
    }
}
Enter fullscreen mode Exit fullscreen mode

Step 4: Running Migrations
Now that we’ve created the Product entity, we’ll make it an actual database table so our API can serve real records. Symfony uses Doctrine migrations to translate entity definitions into database schema changes. Running a migration will create the product table in MySQL with the fields we defined in step 3.
Generate the migration file

php bin/console make:migration
Enter fullscreen mode Exit fullscreen mode

Migration file generated successfully with make:migration.

This creates a new migration class in the migrations/ directory.
If you open it, you’ll see SQL statements that Doctrine will execute for example, creating the product table with id, title, description, and created_at columns.

Apply the migration to the database

php bin/console doctrine:migrations:migrate
Enter fullscreen mode Exit fullscreen mode

You’ll be asked to confirm. Type yes and press Enter.

Migration applied successfully with doctrine:migrations:migrate.
Verify the table was created
Log into MySQL:

mysql -u root -p
Enter fullscreen mode Exit fullscreen mode

Then:

USE symfony-app;
SHOW TABLES;
DESCRIBE product;
Enter fullscreen mode Exit fullscreen mode

You should see your product table with the columns we defined in the Product entity.

At this point, our Symfony app is ready to store and fetch product data from the database. In the next step, we’ll add some records so our API can return them instead of the placeholder JSON.

“Well done is better than well said.” - Benjamin Franklin

Step 5: Adding and Retrieving Products from the Database
We’ll use Doctrine Fixtures to insert sample records. Fixtures are a convenient way to load data into your database for development and testing.

Install orm-fixtures package using composer

composer require orm-fixtures --dev
Enter fullscreen mode Exit fullscreen mode

To Create the Fixtures Class
Run :

php bin/console make:fixtures
Enter fullscreen mode Exit fullscreen mode

When prompted:

The class name of the fixtures to create (e.g. AppFixtures):
 > ProductFixtures
Enter fullscreen mode Exit fullscreen mode

Symfony will create src/DataFixtures/ProductFixtures.php.
Update it like this:

<?php

namespace App\DataFixtures;

use App\Entity\Product;
use Doctrine\Bundle\FixturesBundle\Fixture;
use Doctrine\Persistence\ObjectManager;

class ProductFixtures extends Fixture
{
   public function load(ObjectManager $manager): void
   {
       $products = [
           ['title' => 'Sample Product', 'description' => 'This is a sample product description.'],
           ['title' => 'Another Product', 'description' => 'Another example description.'],
           ['title' => 'Third Product', 'description' => 'Yet another product for testing.'],
       ];

       foreach ($products as $data) {
           $product = new Product();
           $product->setTitle($data['title']);
           $product->setDescription($data['description']);
           // createdAt will be set automatically by the constructor
           $manager->persist($product);
       }

       $manager->flush();
   }
}
Enter fullscreen mode Exit fullscreen mode

We want every record to have a creation timestamp without having to set it in fixtures or forms.
So, update src/Entity/Product.php constructor.

public function __construct()
{
    $this->createdAt = new \DateTimeImmutable();
}
Enter fullscreen mode Exit fullscreen mode

Load the Fixtures into the Database

php bin/console doctrine:fixtures:load
Enter fullscreen mode Exit fullscreen mode

You’ll be asked to confirm. Type yes.
Doctrine will insert the sample records into the product table.

Update the API Controller to Fetch from the Database
Replace the hard-coded JSON in src/Controller/Api/ProductApiController.php with a database query:

<?php

namespace App\Controller\Api;

use App\Entity\Product;
use Doctrine\ORM\EntityManagerInterface;
use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
use Symfony\Component\HttpFoundation\JsonResponse;
use Symfony\Component\Routing\Annotation\Route;

class ProductApiController extends AbstractController
{
    #[Route('/api/products', name: 'api_products', methods: ['GET'])]
    public function index(EntityManagerInterface $em): JsonResponse
    {
        $products = $em->getRepository(Product::class)->findAll();

        $data = [];
        foreach ($products as $product) {
            $data[] = [
                'title' => $product->getTitle(),
                'description' => $product->getDescription(),
            ];
        }

        return $this->json($data);
    }
}
Enter fullscreen mode Exit fullscreen mode

Now, when you visit http://localhost:8000/api/products you’ll see data coming from the database instead of hard-coded arrays.

symfony product API result

Step 6: Viewing the Data in React
Now, instead of returning a fixed array, Symfony fetches records directly from the database using Doctrine.

Open your React app at: http://localhost:5173

React frontend now showing products retrieved from the Symfony API with data stored in the database.

You’ll see the same product list UI, but this time the content is coming from the database through Symfony’s API.

“RESTful APIs built with Symfony give you the freedom to power anything from a simple React app to an enterprise platform, all with the same clean backend.” - SensioLabs Engineering Team

Stats

  • Symfony’s official usage report shows that over 600,000 developers worldwide rely on Symfony and Doctrine ORM for building robust applications.
  • According to Doctrine’s GitHub repository, the ORM has been downloaded more than 1 billion times, making it one of the most widely used PHP database abstraction layers.

Key Takeaways

  • Symfony and Doctrine provide a clean way to connect APIs to a database.
  • Entities map PHP classes to database tables, making your code expressive and maintainable.
  • Doctrine Migrations handle schema updates safely without manual SQL.
  • Fixtures make it simple to preload sample data for development and testing.
  • By swapping out hard-coded JSON with real database queries, your API instantly becomes dynamic and production-ready.

Interesting Facts

  • Doctrine ORM is used in thousands of Symfony projects worldwide, making it one of the most battle-tested ORMs in the PHP ecosystem.
  • Symfony’s database layer can connect not only to MySQL but also PostgreSQL, SQLite, and even Oracle.
  • With Doctrine’s query builder, you can write expressive queries in PHP without switching between SQL and PHP code.

FAQs

Q1: Can I use PostgreSQL instead of MySQL with Symfony?
A: Yes. Doctrine supports multiple database engines. You only need to update your DATABASE_URL in .env

Q2: Do I need fixtures in production?
A: No. Fixtures are primarily for development and testing. In production, you typically insert data through your application or migrations.

Q3: What happens if I change my entity later?
A: You can run php bin/console make:migration and then php bin/console doctrine:migrations:migrate to safely update your database schema.

Conclusion

Moving from hard-coded data to a database-driven API is a key milestone in backend development. With Symfony, Doctrine, and MySQL, the process is straightforward and sets up a strong foundation for scalable applications. Once your API is connected to real data, you unlock endless possibilities whether that’s powering a React frontend, a mobile app, or other client systems. This step transforms your API from a demo into something ready for real-world use.

About the Author: Balasaranya Varadhalingam, Software Engineer at AddWebSolution, specializes in PHP, Symfony, and API development.

Top comments (0)