README

English | 中文

Efficient UPSERT (insert or update) capabilities for Doctrine ORM with automatic SQL generation and multi-database support.

Table of Contents

• Features
• Requirements
• Installation
• Quick Start
  • Configuration
  • Basic Usage
  • Batch UPSERT
  • Low-Level UPSERT
• Entity Requirements
• Advanced Usage
  • Custom Update Time Handling
  • Extending with Custom Providers
• API Reference
  • UpsertManager
  • Supported Platforms
• Contributing
• License
• Changelog

Features

• Single and Batch UPSERT Operations
  • MySQL: INSERT ... ON DUPLICATE KEY UPDATE
  • SQLite: INSERT ... ON CONFLICT ... DO UPDATE SET
• Automatic Database Platform Detection - Works seamlessly with MySQL and SQLite
• Smart Unique Constraint Detection - Automatically identifies unique constraints from entity metadata
• Extensible Provider System - Custom UPSERT providers via ProviderInterface
• Safe Error Handling - Prevents EntityManager closure on errors
• Automatic Timestamp Management - Handles create_time and update_time fields intelligently

Requirements

• PHP >= 8.1
• doctrine/orm >= 3.0
• doctrine/dbal >= 4.0
• symfony >= 6.4

Installation

composer require tourze/doctrine-upsert-bundle

Quick Start

Configuration

Register the bundle in config/bundles.php:

return [
    Tourze\DoctrineUpsertBundle\DoctrineUpsertBundle::class => ['all' => true],
];

Basic Usage

use App\Entity\User;
use Tourze\DoctrineUpsertBundle\Service\UpsertManager;

// Inject UpsertManager (or use dependency injection)
public function __construct(
    private UpsertManager $upsertManager
) {}

// Single entity upsert
$user = new User();
$user->setEmail('user@example.com');
$user->setName('John Doe');

// Returns the persisted entity (may be different from input)
$persistedUser = $this->upsertManager->upsert($user);

Batch UPSERT

// Batch upsert with array data
$userData = [
    ['email' => 'user1@example.com', 'name' => 'User 1'],
    ['email' => 'user2@example.com', 'name' => 'User 2'],
];

$affectedRows = $this->upsertManager->executeBatch($userData, User::class);

Low-Level UPSERT

// Direct SQL execution
$insertData = ['email' => 'user@example.com', 'name' => 'John'];
$updateData = ['name' => 'John Doe Updated']; // Optional

$affectedRows = $this->upsertManager->execute('users', $insertData, $updateData);

Entity Requirements

Your entities must have unique constraints defined for UPSERT operations:

use Doctrine\ORM\Mapping as ORM;

#[ORM\Entity]
#[ORM\Table(name: 'users')]
#[ORM\UniqueConstraint(name: 'user_email_unique', columns: ['email'])]
class User
{
    #[ORM\Id]
    #[ORM\GeneratedValue]
    #[ORM\Column(type: 'integer')]
    private int $id;

    #[ORM\Column(type: 'string', unique: true)]
    private string $email;

    #[ORM\Column(type: 'string')]
    private string $name;

    // Or use individual unique columns
    // #[ORM\Column(type: 'string', unique: true)]
    // private string $username;
}

Advanced Usage

Custom Update Time Handling

The bundle automatically handles update_time fields:

class User
{
    #[ORM\Column(type: 'datetime')]
    private \DateTime $createTime;

    #[ORM\Column(type: 'datetime')]
    private \DateTime $updateTime;

    public function setUpdateTime(\DateTime $updateTime): self
    {
        $this->updateTime = $updateTime;
        return $this;
    }
}

Extending with Custom Providers

Create custom database platform support:

use Tourze\DoctrineUpsertBundle\Service\ProviderInterface;

class PostgreSQLUpsertProvider implements ProviderInterface
{
    public function supports(string $platform): bool
    {
        return $platform === 'postgresql';
    }

    public function getUpsertQuery(string $table, array $insertData, array $updateData): string
    {
        // Implementation for PostgreSQL UPSERT
        return "INSERT INTO {$table} ... ON CONFLICT ... DO UPDATE SET ...";
    }
}

API Reference

UpsertManager

• upsert(object $entity, bool $fetchAgain = true): object - Upsert single entity
• execute(string $table, array $insertData, array $updateData = []): int - Execute raw upsert
• executeBatch(array $data, string $repositoryClass): int - Batch upsert operation

Supported Platforms

• MySQL: Uses INSERT ... ON DUPLICATE KEY UPDATE
• SQLite: Uses INSERT ... ON CONFLICT ... DO UPDATE SET

Contributing

1. Fork the repository
2. Create a feature branch
3. Follow PSR-12 coding standards
4. Add tests for new features
5. Submit a pull request

License

MIT License © tourze

Changelog

See [CHANGELOG.md] or Git history for version updates and changes.