PHP Doctrine: How to connect to SQLite database

Introduction

In this tutorial, we will explore how to use the Doctrine ORM (Object-Relational Mapping) to connect to a SQLite database using PHP. Doctrine is a powerful ORM that allows developers to interact with databases in an object-oriented way. By using Doctrine with SQLite, we can leverage its features for rapid development while maintaining the simplicity of an SQLite database. We’ll start with the basics and progressively advance to more complex concepts.

Install Doctrine

Before we connect to an SQLite database, we need to install Doctrine. We’ll use Composer to manage our dependencies. If you haven’t installed Composer, you can find the instructions on its official website. With Composer installed, run the following command to add Doctrine to your project:

composer require doctrine/orm

Bootstrap Doctrine

To use Doctrine, we’ll need to configure an EntityManager, which is the central access point of Doctrine and manages the persistence of objects. The following bootstrap file sets up the configuration:

<?php

use Doctrine\ORM\Tools\Setup;
use Doctrine\ORM\EntityManager;

require_once 'vendor/autoload.php';

// Create a simple default Doctrine ORM configuration for Annotations
$isDevMode = true;
$config = Setup::createAnnotationMetadataConfiguration(array(__DIR__.'/src'), $isDevMode);

// or if you prefer YAML or XML
// $config = Setup::createXMLMetadataConfiguration(array(__DIR__.'/config/xml'), $isDevMode);
// $config = Setup::createYAMLMetadataConfiguration(array(__DIR__.'/config/yaml'), $isDevMode);

// database configuration parameters
$conn = array(
    'driver' => 'pdo_sqlite',
    'path' => __DIR__.'/db.sqlite',
);

// obtaining the entity manager
$entityManager = EntityManager::create($conn, $config);

Define Entities

With Doctrine, we represent our database tables as PHP classes known as entities. Here’s an example of an entity:

<?php

/**
 * @Entity @Table(name="products")
 **/
class Product
{
    /** @Id @Column(type="integer") @GeneratedValue **/
    protected $id;

    /** @Column(type="string") **/
    protected $name;

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

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

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

Make sure you follow the Doctrine entity rules when defining your class. An entity must have at least one field with the ‘@Id’ annotation to be used as a primary key.

Create Database Schema

Once you have defined your entities, you can use cli-config.php and Doctrine’s command-line tools to create the SQLite database schema. The following is an example of a cli-config.php file:

<?php

use Doctrine\ORM\Tools\Console\ConsoleRunner;

require_once 'bootstrap.php';

// replace with mechanism to retrieve EntityManager in your app
$entityManager = GetEntityManager();

return ConsoleRunner::createHelperSet($entityManager);

After setting that up, run the following command to generate your database schema:

php vendor/bin/doctrine orm:schema-tool:create

Performing Actions on the Database

With your database set up, you can create, read, update, and delete records (CRUD operations). Here’s how to create and save a new product:

<?php

// ... bootstrap.php and Entity/Product.php included above

$product = new Product();
$product->setName('iPhone X');

$entityManager->persist($product);
$entityManager->flush();

echo "Created Product with ID " . $product->getId() . "\n";

To read data, use the EntityManager’s find or repository methods:

<?php

// ... bootstrap.php and Entity/Product.php included above

$productRepository = $entityManager->getRepository('Product');
$products = $productRepository->findAll();

foreach ($products as $product) {
    echo $product->getId() . ' - ' . $product->getName() . "\n";
}

Updating data is as simple as modifying the object and calling flush:

<?php

// ... bootstrap.php and Entity/Product.php included above

$product = $entityManager->find('Product', $productId);
$product->setName('New Product Name');
$entityManager->flush();

Deleting records is just as straightforward:

<?php

// ... bootstrap.php and Entity/Product.php included above

$product = $entityManager->find('Product', $productId);
$entityManager->remove($product);
$entityManager->flush();

Advanced: Using Query Builder

When you need more complex queries, you can use Doctrine’s Query Builder. Below is an example of how to use Query Builder to create custom queries:

<?php

// ... bootstrap.php and Entity/Product.php included above

$queryBuilder = $entityManager->createQueryBuilder();
$queryBuilder->select('p')
    ->from('Product', 'p')
    ->where('p.name = :name')
    ->setParameter('name', 'iPhone X');

$result = $queryBuilder->getQuery()->getResult();

foreach ($result as $product) {
    echo $product->getName();
}

Handling Transactional Operations

Transactional operations are vital in maintaining data integrity. Doctrine transactions are managed using the EntityManager transaction methods:

<?php

// ... bootstrap.php and Entity/Product.php included above

try {
    $entityManager->beginTransaction();

    $product1 = new Product();
    $product1->setName('Product 1');
    $entityManager->persist($product1);

    $product2 = new Product();
    $product2->setName('Product 2');
    $entityManager->persist($product2);

    // More operations...

    $entityManager->flush();
    $entityManager->commit();

    echo "Both products saved!";
} catch (Exception $e) {
    $entityManager->rollback();
    echo "Transaction failed!";
}

The entities are only saved to the database if the transaction is committed. If there’s an error, the transaction will be rolled back, and no changes will be persisted.

Conclusion

Throughout this tutorial, we have seen how to set up Doctrine ORM with a SQLite database in PHP, define entities, and perform various database operations through Doctrine’s EntityManager. By mastering these fundamental principles, you can create robust PHP applications with an effective database layer using Doctrine and SQLite.