PHPackages                             redant/doctrine-dbal-sharding - PHPackages - PHPackages  [Skip to content](#main-content)[PHPackages](/)[Directory](/)[Categories](/categories)[Trending](/trending)[Leaderboard](/leaderboard)[Changelog](/changelog)[Analyze](/analyze)[Collections](/collections)[Log in](/login)[Sign up](/register)

1. [Directory](/)
2. /
3. [Database & ORM](/categories/database)
4. /
5. redant/doctrine-dbal-sharding

ActiveLibrary[Database & ORM](/categories/database)

redant/doctrine-dbal-sharding
=============================

Sharding support for Doctrine DBAL 3.x

09PHP

Since Feb 4Pushed 4y ago2 watchersCompare

[ Source](https://github.com/redantnl/doctrine-dbal-sharding)[ Packagist](https://packagist.org/packages/redant/doctrine-dbal-sharding)[ RSS](/packages/redant-doctrine-dbal-sharding/feed)WikiDiscussions main Synced 2d ago

READMEChangelogDependenciesVersions (1)Used By (0)

Doctrine DBAL Sharding
======================

[](#doctrine-dbal-sharding)

Doctrine DBAL is a powerful database abstraction layer with many features. In its 3.0 version, sharding support was removed. This package provides the, slightly modified, DBAL 2.x Sharding code for use with DBAL 3.x.

Upgrading
---------

[](#upgrading)

- Change the namespace from `Doctrine\DBAL\Sharding` to `RedAnt\DBALSharding`.
- The configuration key `shardChoser` and all associated classes were renamed to `shardChooser`.

Sharding
========

[](#sharding)

This package contains some functionality to simplify the development of horizontally sharded applications. In this release it contains a `ShardManager` interface. This interface allows to programmatically select a shard to send queries to. At the moment there are no functionalities yet to dynamically pick a shard based on ID, query or database row yet. That means the sharding extension is primarily suited for:

- multi-tenant applications or
- applications with completely separated datasets (example: weather data).

Both kind of application will work with both DBAL and ORM.

```
Horizontal sharding is an evasive architecture that will affect your application code and using this
extension to Doctrine will not make it work "magically".

```

You have to understand and integrate the following drawbacks:

- Pre-generation of IDs that are unique across all shards required.
- No transaction support across shards.
- No foreign key support across shards (meaning no "real" relations).
- Very complex (or impossible) to query aggregates across shards.
- Denormalization: Composite keys required where normalized non-sharded db schemas don't need them.
- Schema Operations have to be done on all shards.

The primary questions in a sharding architecture are:

- Where is my data located?
- Where should I save this new data to find it later?

To answer these questions you generally have to craft a function that will tell you for a given ID, on which shard the data for this ID is located. To simplify this approach you will generally just pick a table which is the root of a set of related data and decide for the IDs of this table. All the related data that belong to this table are saved on the same shard.

Take for example a multi-user blog application with the following tables:

- Blog \[id, name\]
- Post \[id, blog\_id, subject, body, author\_id\]
- Comment \[id, post\_id, comment, author\_id\]
- User \[id, username\]

A sensible sharding architecture will split the application by blog. That means all the data for a particular blog will be on a single shard and scaling is done by putting the amount of blogs on many different database servers.

Now users can post and comment on different blogs that reside on different shards. This makes the database schema above slightly tricky, because both `author_id` columns cannot have foreign keys to `User (id)`. Instead the User table is located in an entirely different "dimension" of the application in terms of the sharding architecture.

To simplify working with this kind of multi-dimensional database schema, you can replace the author\_ids with something more "meaningful", for example the e-mail address of the users if that is always known. The "user" table can then be separated from the database schema above and put on a second horizontally scaled sharding architecture.

As you can see, even with just the four tables above, sharding actually becomes quite complex to think about.

The rest of this section discusses Doctrine sharding functionality in technical detail.

ID Generation
-------------

[](#id-generation)

To solve the issue of unique ID-generation across all shards are several approaches you should evaluate:

### Use GUID/UUIDs

[](#use-guiduuids)

The most simple ID-generation mechanism for sharding are universally unique identifiers. These are 16-byte (128-bit) numbers that are guaranteed to be unique across different servers. You can `read up on UUIDs on Wikipedia `\_.

The drawback of UUIDs is the segmentation they cause on indexes. Because UUIDs are not sequentially generated, they can have negative impact on index access performance. Additionally they are much bigger than numerical primary keys (which are normally 4-bytes in length).

At the moment Doctrine DBAL drivers MySQL and SQL Server support the generation of UUID/GUIDs. You can use the following bit of code to generate them across platforms:

```