Symfony SQLite Tutorial

last modified March 3, 2025

Symfony SQLite tutorial shows how to use SQLite with Symfony 7.2. We create routes for JSON output, use fixtures to populate the database, and display data in HTML.

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 heavily inspired by the Spring Framework.

SQLite

SQLite is a lightweight, serverless, self-contained SQL database engine. It is ideal for small applications, prototypes, and testing.

Setting up the project

We start by creating a new Symfony project and installing the necessary dependencies.

$ composer create-project symfony/skeleton symfony-sqlite "^7.2"
$ cd symfony-sqlite

We create a new Symfony 7.2 project and navigate to the project directory.

$ composer require symfony/orm-pack
$ composer require --dev symfony/maker-bundle

We install the ORM pack for database support and the maker bundle for generating code.

$ composer require symfony/validator
$ composer require symfony/serializer

We install the validator and serializer components for data validation and JSON output.

Configuring SQLite

We configure SQLite in the .env file.

DATABASE_URL="sqlite:///%kernel.project_dir%/var/data.db"

This sets up SQLite to use a file named data.db in the var directory.

$ php bin/console doctrine:database:create

We create the SQLite database.

Creating an Entity

We create a Product entity to store product data.

$ php bin/console make:entity Product

We create the Product entity with fields: name (string) and price (decimal).

<?php

declare(strict_types=1);

namespace App\Entity;

use Doctrine\ORM\Mapping as ORM;

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

    #[ORM\Column(length: 255)]
    private string $name;

    #[ORM\Column(type: 'decimal', precision: 10, scale: 2)]
    private string $price;

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

    public function getName(): string
    {
        return $this->name;
    }

    public function setName(string $name): void
    {
        $this->name = $name;
    }

    public function getPrice(): string
    {
        return $this->price;
    }

    public function setPrice(string $price): void
    {
        $this->price = $price;
    }
}

The Product entity now uses Decimal for the price field to ensure precision for monetary values.

$ php bin/console make:migration
$ php bin/console doctrine:migrations:migrate

We generate and run the migration to create the database table.

Using Fixtures

We use fixtures to populate the database with sample data.

$ composer require --dev doctrine/doctrine-fixtures-bundle

We install the fixtures bundle.

<?php

declare(strict_types=1);

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 = [
            ['name' => 'Product A', 'price' => '19.99'],
            ['name' => 'Product B', 'price' => '29.99'],
            ['name' => 'Product C', 'price' => '39.99'],
        ];

        foreach ($products as $data) {
            $product = new Product();
            $product->setName($data['name']);
            $product->setPrice($data['price']);
            $manager->persist($product);
        }

        $manager->flush();
    }
}

The ProductFixtures class populates the database with sample products. Note that the price values are now passed as strings.

$ php bin/console doctrine:fixtures:load

We load the fixtures into the database.

Creating Routes for JSON Output

We create routes to output product data in JSON format.

<?php

declare(strict_types=1);

namespace App\Controller;

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

class ProductController extends AbstractController
{
    #[Route('/products', name: 'product_list', methods: ['GET'])]
    public function list(EntityManagerInterface $em): JsonResponse
    {
        $products = $em->getRepository(Product::class)->findAll();
        $data = [];

        foreach ($products as $product) {
            $data[] = [
                'id' => $product->getId(),
                'name' => $product->getName(),
                'price' => $product->getPrice(),
            ];
        }

        return $this->json($data);
    }

    #[Route('/products/{id}', name: 'product_show', methods: ['GET'])]
    public function show(Product $product): JsonResponse
    {
        return $this->json([
            'id' => $product->getId(),
            'name' => $product->getName(),
            'price' => $product->getPrice(),
        ]);
    }
}

The ProductController provides two routes: one to list all products and another to show a single product by ID. The price is now returned as a string.

Displaying Data in HTML

We create a route to display product data in HTML.

{% extends 'base.html.twig' %}

{% block title %}Product List{% endblock %}

{% block body %}
<h1>Product List</h1>
<table>
    <thead>
        <tr>
            <th>ID</th>
            <th>Name</th>
            <th>Price</th>
        </tr>
    </thead>
    <tbody>
        {% for product in products %}
        <tr>
            <td>{{ product.id }}</td>
            <td>{{ product.name }}</td>
            <td>{{ product.price }}</td>
        </tr>
        {% endfor %}
    </tbody>
</table>
{% endblock %}

The Twig template displays the product list in an HTML table. The price is displayed as a string.

#[Route('/products/html', name: 'product_list_html', methods: ['GET'])]
public function listHtml(EntityManagerInterface $em): Response
{
    $products = $em->getRepository(Product::class)->findAll();
    return $this->render('product/list.html.twig', ['products' => $products]);
}

We add a route to render the product list in HTML.

Running the Example

$ php bin/console server:start

We start the development server.

$ curl localhost:8000/products
[{"id":1,"name":"Product A","price":"19.99"}, ...]
$ curl localhost:8000/products/1
{"id":1,"name":"Product A","price":"19.99"}

We test the JSON routes with curl.

$ curl localhost:8000/products/html

We view the product list in HTML by visiting the URL in a browser.

In this tutorial, we used SQLite with Symfony 7.2, created routes for JSON output, used fixtures to populate the database, and displayed data in HTML.

List all Symfony tutorials.