How to Store Images in SQLite with PHP

Time to read: 11 minutes

August 11, 2023
Written by
Matthew Setter
Twilion
Reviewed by
Dhruv Patel
Twilion

Recently, my colleague Diane Phan showed how to store multimedia files in SQLite using Python. It was a fun tutorial which got me thinking about how to do something similar in PHP.

After some thought, I came up with a revised implementation, a simplistic API that can upload, delete, and view images. Thanks to PHP's wonderful Image Magick (Imagick) extension, it also retrieves image information such as the height, width, size, depth, and density.

If that sounds like something you'd like to build, then let's get going.

Prerequisites

To follow along with this tutorial, you won't need much, just the following:

How will the application work?

There isn't a lot to it to be honest, at least in principle. It's a small API built with PHP's Mezzio framework. It will have four routes:

  • One to retrieve a list of all images stored in the database
  • One to upload an image and store it in the database
  • One to delete an image
  • One to download an image

When an image is uploaded, the image's binary data, along with several other properties, including the image's colour space, depth, format, height, and width, will be extracted using PHP's ImageMagick extension and persisted to a SQLite database. The uploaded image is then deleted, avoiding the need to be concerned with fancy directory and file naming conventions or structures.

Two things are worth noting. Firstly, despite the simplicity of the API, I've still tried to follow a number of API design best practices, such as accepting and responding with JSON.

Secondly, SQLite isn't strictly necessary. You could use any other database that has a BLOB type or supports storing binary data, such as PostgreSQL, MySQL, Microsoft SQLServer, and Oracle. However, SQLite is trivially simple to get started with, as, among other reasons, it doesn't require installing and configuring a server. Rather, everything's contained in a single file. What's more, you don't need to configure security up front if you don't want or need to. So, for the purposes of this tutorial, it's an excellent fit!

If this is your first time developing with the Mezzio framework, it's a little different to other frameworks, but rather akin to the Slim framework. It might be a little different from what you're used to, but you'll get used to it pretty quickly. Grab a copy of my book, if you'd like to learn more about it, or check out the documentation.

Scaffold a new project

With the prerequisites in place, the first thing to do is to scaffold a new application by running the following command.

composer create-project mezzio/mezzio-skeleton sqlite-image-storage

When prompted, answer the questions as follows:

  What type of installation would you like?
  [1] Minimal (no default middleware, templates, or assets; configuration only)
  [2] Flat (flat source code structure; default selection)
  [3] Modular (modular source code structure; recommended)
  Make your selection (2): 2

  Which container do you want to use for dependency injection?
  [1] Pimple (supported by laminas)
  [2] laminas-servicemanager (supported by laminas)
  [3] Symfony DI Container
  [4] PHP-DI
  [5] chubbyphp-container
  Make your selection or type a composer package name and version (laminas-servicemanager (supported by laminas)): 2

  Which router do you want to use?
  [1] FastRoute (supported by laminas)
  [2] laminas-router (supported by laminas)
  Make your selection or type a composer package name and version (FastRoute (supported by laminas)): 1

  Which template engine do you want to use?
  [1] Plates (supported by laminas)
  [2] Twig (supported by laminas)
  [3] laminas-view installs laminas-servicemanager (supported by laminas)
  [n] None of the above
  Make your selection or type a composer package name and version (n): n

  Which error handler do you want to use during development?
  [1] Whoops (supported by laminas)
  [n] None of the above
  Make your selection or type a composer package name and version (Whoops (supported by laminas)): 1

With the application scaffolded, change into the application's top-level directory and install the external dependencies with Composer.

cd sqlite-image-storage
composer update

If/when prompted, answer the questions as follows.

Please select which config file you wish to inject 'Mezzio\Tooling\ConfigProvider' into:
[0] Do not inject
[1] config/config.php
Make your selection (default is 1): 1

Remember this option for other packages of the same type? (Y/n) Y

Add the application's dependencies

The next thing to do is to install the required dependencies. There aren't many, just the following three:

DependencyDescription
laminas-inputfilter and laminas-validatorTogether, these two packages simplify filtering and validating the uploaded image.
laminas-doctrine-ormThis package does most of the hard work of integrating Doctrine into the application; which will be used to interact with SQLite. All we'll need to do is to add in a configuration file.


Before we can install them, we first need to change the project's minimum stability to "dev", so that laminas-doctrine-orm can be installed. To do that, add the following line to composer.json.

"minimum-stability": "dev",

Then, in a new terminal session/tab, run the following commands to install the packages, and enable Mezzio's development mode, which is very helpful if we encounter any issues.

composer development-enable
composer require -W \
    laminas/laminas-inputfilter \
    laminas/laminas-validator \
    "skar/laminas-doctrine-orm:dev-master"

If prompted with the following questions, answer them as follows:

Please select which config file you wish to inject 'Laminas\Validator\ConfigProvider' into:
[0] Do not inject
[1] config/config.php
Make your selection (default is 1): 1

Remember this option for other packages of the same type? (Y/n) Y

Finally, you need to enable the Doctrine command-line commands. In config/config.php, add the following line to the top of the array passed when initialising the new ConfigAggregator(). For example:

$aggregator = new ConfigAggregator([
        \Mezzio\Twig\ConfigProvider::class,
        \Laminas\InputFilter\ConfigProvider::class,
        \Laminas\Filter\ConfigProvider::class,
        \Laminas\Validator\ConfigProvider::class,
        \Mezzio\Tooling\ConfigProvider::class,
        \Mezzio\Twig\ConfigProvider::class,
        \Mezzio\Helper\ConfigProvider::class,
        \Mezzio\Router\FastRouteRouter\ConfigProvider::class,
        \Laminas\HttpHandlerRunner\ConfigProvider::class,
        \Skar\LaminasDoctrineORM\ConfigProvider::class,
        // … other config provider classes

Configure Doctrine

The application will connect to the SQLite database using Doctrine. If this is your first time using Doctrine there's not much to learn. While it might have a reputation as being a bit unwieldy, you'll be up and running in no time!

First, create a new file in config/autoload named doctrine.global.php, and in that file paste the configuration below.

<?php

declare(strict_types=1);

use Doctrine\DBAL\Driver\PDO\SQLite\Driver;
use Doctrine\ORM\Mapping\Driver\AttributeDriver;

return [
    'doctrine' => [
        'connection' => [
            'orm_default' => [
                'driver_class' => Driver::class,
                'params' => [
                    'host'         => 'localhost',
                    'path'  => __DIR__ . "/../../data/database.sqlite3"
                ],
            ],
        ],
        'driver' => [
            'orm_default' => [
                'drivers' => [
                    'App\Entity' => [
                        'class' => AttributeDriver::class,
                        'cache' => 'array',
                        'paths' => [
                            __DIR__ . '/../../src/App/Entity',
                        ],
                    ],
                ],
            ],
        ],
    ],
];

The file provides the required configuration for Doctrine, telling it two things:

  • The SQLite database file is stored in the data directory and named database.sqlite3. The database will be created, shortly, when the migrations run.
  • The Doctrine entities are located in the src/App/src/Entity directory. These are classes that tell Doctrine how the image data should be stored in the database.

Create the Doctrine entities

Now, create a new directory named Entity in the src/App/ directory. In that directory, create a new file named Image.php, and paste the code below into the file.

<?php

declare(strict_types=1);

namespace App\Entity;

use App\Repository\ImageRepository;
use Doctrine\DBAL\Types\Types;
use Doctrine\ORM\Mapping as ORM;
use Imagick;

use function base64_encode;
use function implode;
use function json_decode;
use function sprintf;
use function stream_get_contents;

#[ORM\Entity(repositoryClass: ImageRepository::class)]
#[ORM\Table(name: 'image')]
class Image
{
    #[ORM\Id]
    #[ORM\GeneratedValue(strategy: 'IDENTITY')]
    #[ORM\Column(name: 'id', type: Types::INTEGER, unique: true, nullable: false)]
    protected int|null $id;

    #[ORM\Column(name: 'name', type: Types::STRING, length: 200, unique: true, nullable: false)]
    private ?string $name;

    #[ORM\Column(name: 'data', type: Types::BLOB, unique: true, nullable: false)]
    /** @var resource Stores the image's data */
    private $data;

    #[ORM\Column(name: 'width', type: Types::INTEGER, unique: false, nullable: true)]
    private ?int $width;

    #[ORM\Column(name: 'height', type: Types::INTEGER, unique: false, nullable: true)]
    private ?int $height;

    #[ORM\Column(name: 'density', type: Types::JSON, unique: false, nullable: true)]
    private ?string $density;

    #[ORM\Column(name: 'orientation', type: Types::INTEGER, unique: false, nullable: true)]
    private ?int $orientation;

    #[ORM\Column(name: 'format', type: Types::STRING, unique: false, nullable: true)]
    private ?string $format;

    #[ORM\Column(name: 'depth', type: Types::STRING, unique: false, nullable: true)]
    private ?int $depth;

    #[ORM\Column(name: 'colour_space', type: Types::STRING, unique: false, nullable: true)]
    private ?int $colourSpace;

    #[ORM\Column(name: 'size', type: Types::INTEGER, unique: false, nullable: true)]
    private ?int $size;

    public function __construct(
        ?string $name,
        ?string $data,
        ?int $height = null,
        ?int $width = null,
        ?string $density = null,
        ?string $format = null,
        ?int $depth = null,
        ?int $colourSpace = null,
        ?int $size = null,
        ?int $id = null,
    ) {
        $this->id              = $id;
        $this->name            = $name;
        $this->data            = $data;
        $this->height          = $height;
        $this->width           = $width;
        $this->density         = $density;
        $this->format          = $format;
        $this->depth           = $depth;
        $this->colourSpace = $colourSpace;
        $this->size            = $size;
    }

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

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

    public function getData(): ?string
    {
        if ($this->data !== null) {
            return base64_encode(stream_get_contents($this->data));
        }

        return $this->data;
    }

    public function getSize(): ?int
    {
        return $this->size;
    }

    public function getWidth(): ?int
    {
        return $this->width;
    }

    public function getHeight(): ?int
    {
        return $this->height;
    }

    public function getDimensions(): string
    {
        return sprintf(
            '%d x %d',
            $this->getWidth() ?? 0,
            $this->getHeight() ?? 0,
        );
    }

    public function getDensity(): ?string
    {
        return implode('x', json_decode($this->density));
    }

    public function getFormat(): ?string
    {
        return $this->format;
    }

    public function getDepth(): ?int
    {
        return $this->depth;
    }

    public function getColourSpace(): string
    {
        return match ($this->colourSpace) {
            Imagick::COLORSPACE_CMY => 'CMY',
            Imagick::COLORSPACE_CMYK => 'CMYK',
            Imagick::COLORSPACE_GRAY => 'Gray',
            Imagick::COLORSPACE_HSB => 'HSB',
            Imagick::COLORSPACE_HSL => 'HSL',
            Imagick::COLORSPACE_HWB => 'HWB',
            Imagick::COLORSPACE_OHTA => 'OHTA',
            Imagick::COLORSPACE_RGB => 'RGB',
            Imagick::COLORSPACE_SRGB => 'SRGB',
            Imagick::COLORSPACE_TRANSPARENT => 'Transparent',
            Imagick::COLORSPACE_XYZ => 'XYZ',
            Imagick::COLORSPACE_YCBCR => 'YCBCR',
            Imagick::COLORSPACE_YCC => 'YCC',
            Imagick::COLORSPACE_YIQ => 'YIQ',
            Imagick::COLORSPACE_YPBPR => 'YPBPR',
            Imagick::COLORSPACE_YUV => 'YUV',
            default => 'Unknown',
        };
    }

    public function __toArray(): array
    {
        return [
            'colorSpace' => $this->getColourSpace(),
            'data'           => $this->getData(),
            'depth'          => $this->getDepth(),
            'format'         => $this->getFormat(),
            'height'         => $this->getHeight(),
            'id'             => $this->getId(),
            'name'           => $this->getName(),
            'size'           => $this->getSize(),
            'width'          => $this->getWidth(),
        ];
    }
}

The entity defines the following properties:

PropertyDescription
idThis stores a unique, autogenerated, id for the image in the database.
nameThis stores the image's name, the same as the name of the file when it was uploaded.
dataThis stores the image's contents as a blob. There are advantages and disadvantages to storing binary data in a database. However, there's no harm in creating a small app that stores the information there.
formatThis stores the image's format.
widthThis stores the image's width.
heightThis stores the image's height.
depthThis stores the image's bit depth, or, quoting the FADGI, the number of bits used to represent each pixel in an image.
sizeThis stores the size of the image in bytes.


Next, in src/App/ create a new directory named Repository. Then, in that new directory, create a new file named ImageRepository.php. In that new file, add the following code.

<?php

declare(strict_types=1);

namespace App\Repository;

use Doctrine\ORM\EntityRepository;

class ImageRepository extends EntityRepository
{
}

Note that there are no methods in the class, which might leave you wondering why it's been added. It's required for integrating the Image entity with Doctrine Migrations, so that it can create and execute migrations on the SQLite database.

Then, in src/App/Repository, create another new file named ImageRepositoryFactory.php. In it, add the code below.

<?php

declare(strict_types=1);

namespace App\Repository;

use App\Entity\Image;
use Doctrine\ORM\EntityManager;
use Psr\Container\ContainerInterface;

class ImageRepositoryFactory
{
    public function __invoke(ContainerInterface $container): ImageRepository
    {
        /** @var EntityManager $entityManager */
        $entityManager = $container->get(EntityManager::class);

        return $entityManager->getRepository(Image::class);
    }
}

This class handles instantiating an ImageRepository class, when one is requested from the DI (dependency injection) container.

Then, in src/App/ConfigProvider.php, update the getDependencies() function to match the following.

public function getDependencies(): array
{
    return [
        'factories'  => [
            Handler\HomePageHandler::class => Handler\HomePageHandlerFactory::class,
            Repository\ImageRepository::class => Repository\ImageRepositoryFactory::class 
        ],
    ];
}

This change registers the ImageRepository class as a service in the DI container.

Provision the SQLite database

With the entity created, it's now time to run a database migration to initialise the database. To do that, first, create the migrations directory and create a new migration file by running the following command.

mkdir data/migrations
composer mezzio doctrine:migrations:diff

The generated migration contains the difference between the current, empty, database schema and the Image entity. As it's the first migration, the migration will create the schema from scratch. Future migrations will only add new columns or change the properties of existing ones.

Next, run the following command to execute the migration against the database.

composer mezzio doctrine:migrations:migrate

If prompted with the following question, answer them as follows:

WARNING! You are about to execute a migration in database "main" that could result in schema changes and data loss. Are you sure you wish to continue? (yes/no) [yes]:
 > yes

If you open data/database.sqlite3 in your preferred database tool, you'll see that it's been provisioned with four tables, however only one is of interest: image. It has columns to store an image's name, binary data, width, height, and a number of other properties for the uploaded images.

cid  name          type          notnull  dflt_value  pk
---  ------------  ------------  -------  ----------  --
0    id            INTEGER       1                    1 
1    name          VARCHAR(200)  1                    0 
2    data          BLOB          1                    0 
3    width         INTEGER       0        NULL        0 
4    height        INTEGER       0        NULL        0 
5    density       CLOB          0        NULL        0 
6    orientation   INTEGER       0        NULL        0 
7    format        VARCHAR(255)  0        NULL        0 
8    depth         VARCHAR(255)  0        NULL        0 
9    colour_space  VARCHAR(255)  0        NULL        0 
10   size          INTEGER       0        NULL        0

Update the default route to list available images

Now, let's refactor the default route that was created during the scaffolding process, to retrieve a list of all the images currently stored in the database, and return them as an array of JSON objects.

To do that, update src/App/Handler/HomePageHandler.php to match the code below.

<?php

declare(strict_types=1);

namespace App\Handler;

use App\Entity\Image;
use Doctrine\ORM\EntityManager;
use Laminas\Diactoros\Response\EmptyResponse;
use Laminas\Diactoros\Response\JsonResponse;
use Psr\Http\Message\ResponseInterface;
use Psr\Http\Message\ServerRequestInterface;
use Psr\Http\Server\RequestHandlerInterface;

use function array_filter;
use function array_walk;

use const ARRAY_FILTER_USE_KEY;

class HomePageHandler implements RequestHandlerInterface
{
    public function __construct(private EntityManager $entityManager) {}

    public function handle(ServerRequestInterface $request): ResponseInterface
    {
        $response = $this->entityManager
            ->getRepository(Image::class)
            ->findAll();

        if (! empty($response)) {
            $data = [];
            foreach ($response as $item) {
                $data[] = array_filter(
                    $item->__toArray(),
                    fn (string $key) => $key !== 'data',
                    ARRAY_FILTER_USE_KEY
                );
            }

            return new JsonResponse($data);
        }

        return new EmptyResponse();
    }
}

The class' constructor takes a Doctrine EntityManager object providing the class the ability to interact with the database.

In the handle() method, the EntityManager retrieves all of the images from the database. Any images in the database are returned as an array of Image objects where each one is populated with the details of an image from the database.

The array of images is then filtered to remove the data property. This is so that when the image data is returned in JSON format, the image's binary data doesn't overwhelm the output.

After that, the filtered array is used to initialise and return a JsonResponse object. This object simplifies creating a JSON response by setting the content type to application/json; charset=utf-8. The response's body will be a JSON representation of the filtered array data and it will have an HTTP 200 status code.

If no images were returned, the response will not have a body, but it will have a 204 status code instead.

Add the ability to upload images

Now, let's add the ability to upload an image to the database. To do that, first, generate a new handler class by running the following command.

vendor/bin/laminas --ansi mezzio:handler:create \
    --no-register \
    "App\Handler\UploadImageHandler"

The command will generate two new files; one named UploadHandler.php and the other named UploadHandlerFactory.php in src/App/Handler. The first is the class that handles requests to upload images. The second is the class that instantiates it, when it is requested from the DI container.

Update src/App/Handler/UploadHandler.php to match the code below.

<?php

declare(strict_types=1);

namespace App\Handler;

use App\Entity\Image;
use Doctrine\ORM\EntityManager;
use Imagick;
use Laminas\Diactoros\Response\JsonResponse;
use Laminas\Diactoros\StreamFactory;
use Laminas\Diactoros\UploadedFileFactory;
use Laminas\Filter\File\RenameUpload;
use Laminas\Filter\StringToLower;
use Laminas\InputFilter\FileInput;
use Laminas\InputFilter\Input;
use Laminas\InputFilter\InputFilter;
use Laminas\Validator\File\FilesSize;
use Laminas\Validator\File\IsImage;
use Laminas\Validator\File\MimeType;
use Laminas\Validator\File\UploadFile;
use Laminas\Validator\InArray;
use Psr\Http\Message\ResponseInterface;
use Psr\Http\Message\ServerRequestInterface;
use Psr\Http\Message\UploadedFileInterface;
use Psr\Http\Server\RequestHandlerInterface;

use function array_merge_recursive;
use function json_encode;
use function pathinfo;
use function sprintf;
use function unlink;

class UploadImageHandler implements RequestHandlerInterface
{
    private InputFilter $inputFilter;

    public function __construct(
        private EntityManager $entityManager,
        private array $uploadConfig
    ) {
        $image = new FileInput('image');

        $image
            ->getValidatorChain()
            ->attach(new UploadFile())
            ->attach(new IsImage())
            ->attach(new FilesSize(['max' => "5M"]))
            ->attach(new MimeType([
                'image/avif',
                'image/gif',
                'image/jpeg',
                'image/png',
                'image/webp',
            ]));

        $image
            ->getFilterChain()
            ->attach(new RenameUpload([
                'overwrite'                => true,
                'randomize'                => true,
                'stream_factory'           => new StreamFactory(),
                'target'                   => __DIR__ . "/../../uploads/",
                'upload_file_factory'  => new UploadedFileFactory(),
                'use_upload_extension' => true,
                'use_upload_name'          => true,
            ]));

        $optimise = new Input('optimise');
        $optimise
            ->setAllowEmpty(true)
            ->setRequired(false)
            ->getValidatorChain()
            ->attach(new InArray([
                'haystack' => ['yes', 'no'],
            ]));
        $optimise
            ->getFilterChain()
            ->attach(new StringToLower());

        $this->inputFilter = (new InputFilter())
            ->add($image)
            ->add($optimise);
    }

    public function handle(ServerRequestInterface $request): ResponseInterface
    {
        $post = array_merge_recursive(
            $request->getParsedBody(),
            $request->getUploadedFiles()
        );

        $this->inputFilter->setData($post);

        if ($this->inputFilter->isValid()) {
            /** @var UploadedFileInterface $uploadedImage */
            $uploadedImage = $this->inputFilter->getValue('image');
            $fileName          = $uploadedImage->getStream()->getMetadata("uri");
            $imagick = new Imagick($fileName);

            $imageFilename = $uploadedImage->getClientFilename();
            if ($this->inputFilter->getValue('optimise') === 'yes') {
                $imagick->setImageFormat("avif");
                $filenameParts = pathinfo($imageFilename);
                $imageFilename = sprintf("%s.avif", $filenameParts['filename']);
            }

            $image = new Image(
                name: $imageFilename,
                data: $imagick->getImageBlob(),
                height: $imagick->getImageHeight(),
                width: $imagick->getImageWidth(),
                density: json_encode($imagick->getImageResolution()),
                format: $imagick->getImageFormat(),
                depth: $imagick->getImageDepth(),
                colourSpace: $imagick->getColorspace(),
                size: $imagick->getImageLength(),
            );
            $this->entityManager->persist($image);
            $this->entityManager->flush();
            unlink($fileName);

            return new JsonResponse('Image was uploaded successfully.');
        }

        $data = [
            'error'   => 'Image was not uploaded.',
            'reasons' => $this->inputFilter->getMessages(),
        ];
        return new JsonResponse($data);
    }
}

This class is more detailed than the previous one. In the constructor it initialises a InputFilter object. These objects simplify filtering and validating input, regardless of the input's source.

To that object, two Input objects are added, a FileInput named $image and an Input named $optimise. These contain the input filtering and validation rules of the two fields (image and optimise) that can be supplied when making a request to upload an image.

$image's validation rules state that it:

  • Has to be uploaded via HTTP POST
  • Can only be an AVIF, WebP, GIF, JPEG, or PNG image
  • Must be no larger than 5 MB in size

Then, after the image is successfully uploaded the RenameUpload filter moves it to the uploads directory and gives it a randomly assigned name. This is a handy thing to do for security reasons, among others.

$optimise's validation rules state that it is an optional field, with two allowed values: yes and no. The field's value is then converted to lowercase with the StringToLower filter. making it that much easier to use, later on.

In the handle() method the form data is retrieved and validated with the input filter. If the data satisfies the validation criteria, the uploaded image's data and metadata are retrieved and used to initialise a new Image object, which is then persisted to the database.

Finally, the uploaded file is deleted. A message confirming the successful upload of the image is set as the response's body.

If, however, the POST data doesn't satisfy the validation criteria, the reasons why, retrieved from the input filter, are set as the body of the response, along with a message confirming that the image was not uploaded successfully.

Then, update Update src/App/Handler/UploadHandlerFactory.php to match the code below.

<?php

declare(strict_types=1);

namespace App\Handler;

use Doctrine\ORM\EntityManager;
use Psr\Container\ContainerInterface;
use Psr\Log\LoggerInterface;

class UploadImageHandlerFactory
{
    public function __invoke(ContainerInterface $container): UploadImageHandler
    {
        $config           = $container->get('config');
        $uploadConfig = $config['upload'];

        return new UploadImageHandler(
            $container->get(EntityManager::class),
            $uploadConfig,
        );
    }
}

Configure the uploads handler

The next thing to do is to configure the uploads handler, so that you keep configuration out of code, one of the principles of the Twelve Factor App. To do that, create a new file named app.global.php in the config/autoload directory. In the file, paste the code below.

<?php

declare(strict_types=1);

return [
    'upload' => [
        'max_file_size' => '5MB',
        'upload_dir'        => __DIR__ . "/../../data/uploads/",
    ],
];

The configuration sets the path where images will be temporarily uploaded to the data/uploads directory, and sets the maximum file size to 5 MB.

Create the uploads directory

With the UploadHandler created, create a new directory named uploads in the data directory, where the images will be temporarily uploaded.

Add the ability to download an image

Now, let's add the ability to download an image. To do that, first run the following command to create a new handler class.

vendor/bin/laminas --ansi mezzio:handler:create \
    --no-factory --no-register \
    "App\Handler\DownloadImageHandler"

This will generate a new file named DownloadImageHandler.php in src/App/src/Handler. Then, update the file's code to match the code below.

<?php

declare(strict_types=1);

namespace App\Handler;

use App\Entity\Image;
use Doctrine\ORM\EntityManager;
use Laminas\Diactoros\Response;
use Laminas\Diactoros\Response\JsonResponse;
use Laminas\Filter\Digits;
use Laminas\InputFilter\Input;
use Laminas\InputFilter\InputFilter;
use Psr\Http\Message\ResponseInterface;
use Psr\Http\Message\ServerRequestInterface;
use Psr\Http\Server\RequestHandlerInterface;

use function is_array;
use function sprintf;

class DownloadImageHandler implements RequestHandlerInterface
{
    private InputFilter $inputFilter;

    public function __construct(private EntityManager $entityManager) 
    {
        $fileId = new Input('id');
        $fileId
            ->getFilterChain()
            ->attach(new Digits());

        $this->inputFilter = new InputFilter();
        $this->inputFilter->add($fileId);
    }

    public function handle(ServerRequestInterface $request): ResponseInterface
    {
        $result = $this->getImage($request);

        if ($result instanceof Image) {
            $response = (new Response())
                ->withHeader('Content-Length', $result->getSize())
                ->withHeader('Content-Type', "image/{$result->getFormat()}");
            $response->getBody()->write(base64_decode($result->getData()));

            return $response;
        }

        if (is_array($result)) {
            return new JsonResponse($result, 400);
        }

        return new JsonResponse("Image could not be retrieved", 404);
    }

    private function getImage(ServerRequestInterface $request): Image|null|array
    {
        $this->inputFilter->setData($request->getAttributes());

        if ($this->inputFilter->isValid()) {
            $fileId = $this->inputFilter->getValue('id');

            $image = $this->entityManager
                ->getRepository(Image::class)
                ->findOneBy(['id' => $fileId]);

            return $image;
        }

        return $this->inputFilter->getMessages();
    }
}

This handler is notably simpler than the previous one. The constructor takes a Doctrine Entity Manager so that it can search the database for images, and initialises an input filter to validate request data. The input filter only requires one field, named id, to be present. It doesn't apply validation rules, but does filter the provided value, removing anything but digits.

Then, in the handle() method, it initialises the input filter with the request's attributes. Request attributes can contain a wide range of potential information. However, it will contain a key named id which will have a numeric value which the download route must contain for it to match the route's definition. We'll come to that a little later on.

If the validation criteria are met, then the Entity Manager attempts to retrieve the image from the database using the provided id. If the image was successfully retrieved, its data is set as the body of the request, and its length and format are used to set the response's Content-Length and Content-Type headers, respectively.

Add the ability to delete an image

Finally, let's add the ability to delete an image. To do that, first run the following command to create a new handler class.

vendor/bin/laminas --ansi mezzio:handler:create \
    --no-factory --no-register \
    "App\Handler\DeleteImageHandler"

Similar to the two previous handlers, this will generate a new file named DeleteImageHandler.php in src/App/src/Handler. Then, update the file's code to match the code below.

<?php

declare(strict_types=1);

namespace App\Handler;

use App\Entity\Image;
use Doctrine\ORM\EntityManager;
use Laminas\Diactoros\Response\HtmlResponse;
use Laminas\Diactoros\Response\JsonResponse;
use Laminas\Diactoros\Response\RedirectResponse;
use Laminas\Filter\Digits;
use Laminas\Filter\StringToLower;
use Laminas\Filter\StripNewlines;
use Laminas\Filter\StripTags;
use Laminas\InputFilter\Input;
use Laminas\InputFilter\InputFilter;
use Mezzio\Flash\FlashMessageMiddleware;
use Mezzio\Flash\FlashMessagesInterface;
use Mezzio\Template\TemplateRendererInterface;
use Psr\Http\Message\ResponseInterface;
use Psr\Http\Message\ServerRequestInterface;
use Psr\Http\Server\RequestHandlerInterface;

use function sprintf;

class DeleteImageHandler implements RequestHandlerInterface
{
    private InputFilter $inputFilter;

    public function __construct(private EntityManager $entityManager) 
    {
        $fileId = new Input('id');
        $fileId->getFilterChain()
            ->attach(new Digits());

        $this->inputFilter = new InputFilter();
        $this->inputFilter->add($fileId);
    }

    public function handle(ServerRequestInterface $request): ResponseInterface
    {
        $this->inputFilter->setData($request->getAttributes());
        if ($this->inputFilter->isValid()) {
            $fileId = $this->inputFilter->getValue('id');
            $image = $this->entityManager
                ->getRepository(Image::class)
                ->findOneBy(['id' => $fileId]);

            if (! $image instanceof Image) {
                $message = sprintf('Could not retrieve file with ID: %s.', $request->getAttribute('id'));
                return new JsonResponse($message);
            }

            $this->entityManager->remove($image);
            $this->entityManager->flush();

            return new JsonResponse('Image was deleted.');
        }

        return new JsonResponse('Could not delete the image.');
    }
}

This handler is pretty similar to the DownloadImageHandler. However, instead of attempting to retrieve and return the image in the response, this one uses the class' Entity Manager to delete the image from the database, before sending a message in the response that the image was deleted. If the image could not be deleted, the response's body contains a message saying that instead.

Update the dependency injection (DI) container's configuration

With all of the handler classes created, you now need to register each one of them with the application's DI container. That way, they can be used to handle requests to the respective routes, which will be defined shortly.

To do that, update the getDependencies() method in src/App/src/ConfigProvider.php to match the following.

public function getDependencies(): array
{
    return [
            'invokables' => [],
            'factories'  => [
                Handler\HomePageHandler::class => ReflectionBasedAbstractFactory::class,
                Handler\DeleteImageHandler::class => ReflectionBasedAbstractFactory::class,
                Handler\UploadImageHandler::class => Handler\UploadImageHandlerFactory::class,
                Handler\DownloadImageHandler::class => ReflectionBasedAbstractFactory::class,
                Repository\ImageRepository::class => Repository\ImageRepositoryFactory::class,
            ],
    ];
}

Then, add the following use statement to the top of the file and below namespace App;.

use Laminas\ServiceManager\AbstractFactory\ReflectionBasedAbstractFactory;

Update the routing table

Now, there's one last job to go, which is adding routes for deleting, downloading, and uploading images to the application's routing table. To do that, update the body of the static function in config/routes.php with the following code.

$app->get('/', App\Handler\HomePageHandler::class, 'home');
$app->delete('/{id:\d+}', App\Handler\DeleteImageHandler::class, 'image.delete');
$app->get('/{id:\d+}', App\Handler\DownloadImageHandler::class, 'image.download');
$app->post('/', App\Handler\UploadImageHandler::class, 'image.upload');

This adds four routes to the routing table. The first, the default, was already there. The second is the route to delete an image, and is handled by the DeleteImageHandler. To match, requests must use the DELETE request method, and contain an image's id, which can only be a numerical value.

The third route lets images be downloaded and is handled by the DownloadImageHandler. As with the delete image route, the route must contain an image's id which can only be a numerical value. Finally, comes the route for uploading an image which is handled by the UploadImageHandler and must use the POST request method.

Test that the application works

Now, let's see the completed application in action. Start the application by running the following command.

composer serve

Then, let's start by uploading an image to the database. Run the command below in a new terminal session/window, after replacing <<path/to/your/image>> with the full path to an image of your choice.

curl -X POST http://localhost:8080/ -F image=@<<path/to/your/image>>

You should see the following output.

"Image was uploaded successfully."

Next, let's retrieve a list of all the images stored in the database. To do that, run the command below.

curl http://localhost:8080/

You should see JSON output written to the terminal that is similar to the output below.

{"colorSpace":"Unknown"

If you're using Linux or macOS, pipe the output to jq to see it nicely formatted.

Next, let's download an image. Run the following command, replacing <<IMAGE ID>> with the value of the id property in the output from the previous command.

curl http://localhost:8080/<<IMAGE ID>>

Finally, let's delete the uploaded image. Run the following command, replacing <<IMAGE ID>> with the value of the id property in the output from retrieving a list of all the images stored in the database.

curl -X DELETE http://localhost:8080/<<IMAGE ID>>

You should see the following output written to the terminal.

"Image was deleted."

That's how to store images in an SQLite database with PHP

While there were quite a few steps to get through, and perhaps a few new technologies to try, you've now built a fun little web-based application that can upload, optimise, and store images in a SQLite database using PHP. What's more, it performs filtering and validation, which you can build on at your leisure.

If you've not used SQLite that much previously, I hope you see that, in many respects, it's equally as capable as much more well known databases, such as MySQL, PostgreSQL, and MS SQLServer.

If you had any issues following along, grab a copy of the code from GitHub.

Matthew Setter is a PHP/Go Editor in the Twilio Voices team and a PHP, Go, and Rust developer. He’s also the author of Mezzio Essentials and Deploy With Docker Compose. When he's not writing PHP code, he's editing great PHP articles here at Twilio. You can find him at msetter[at]twilio.com, on LinkedIn, Twitter, and GitHub.

Related Posts

Related Resources

A newspaper article

Twilio Docs

From APIs to SDKs to sample apps

API reference documentation, SDKs, helper libraries, quickstarts, and tutorials for your language and platform.
An Open book

Resource Center

The latest ebooks, industry reports, and webinars

Learn from customer engagement experts to improve your own communication.
User group reactions

Ahoy

Twilio's developer community hub

Best practices, code samples, and inspiration to build communications and digital engagement experiences.