Doctrine Database Abstraction Layer

Last update: May 25, 2022
  • 1. Testing of MSSQL on Windows with AppVeyor

    This is an initial draft [POC] for testing of MSSQL on windows with AppVeyor

    This PR creates the initial draft configuration files for AppVeyor CI testing, adjustments may be needed to meet project configuration needs.

    For more information see issue #2616

    Reviewed by photodude at 2017-01-26 16:46
  • 2. MariaDB 10.2 initial support

    Initial support for MariaDB 10.2.7+.

    Doctrine currently does not support MariaDB 10.2.7+. Schema creation/update is triggering infinite schema diffs

    Updated: Title updated from 'MariaDB 10.2 support (will also fix version detection), version detection should be considered as new feature.


    • [x] 1. MariaDb version detection (strip '5.5.5-' prefix)

    To circumvent a limitation in the replication protocol, MariaDB 10+ use a prefixed version with '5.5.5-' (for instance 5.5.5-Mariadb-10.0.8-xenial). This cause issues with the current detection mechanism with both Mysqli and PDOMysql dbal implementations (always detect 5.5.5). While mariadb strips the prefix automatically in their libmariadb-client, most (if not all) of php installations relies on mysqlnd, so the removal of the prefix 5.5.5- must be made in userland (MysqliConnection.php and AbstractMySQLDriver.php). See for reference.

    • [x] ~2. MariaDb 10.2 support BLOB/TEXT defaults values (since 10.2.1).~

    Update: Removed from this P/R... will be made available in a subsequent P/R (due to architecture decision to be made - current doctrine implementation needs to be reworked)

    Note that Mysql (as of 5.7.19) is not yet supporting defaults for BLOB and TEXT.

    • [x] UPDATED ~3. MariaDb 10.2 support JSON type.~

    Removed from P/R by @lcobucci, probably to reflect the fact that MariaDB alias JSON to MEDIUMTEXT

    ~The JSON type have been introduced in 10.2.7 as an alias to LONGTEXT. See To ensure portability between MySQL/Mariadb, the best is to prefer TEXT type over JSON when defining schema (till a real JSON type appears in Maria).~

    • [x] 4. MariaDb 10.2 supports CURRENT_TIME, CURRENT_DATE as default values

    Note that MySQL (as of 5.7.19) does not support default values CURRENT_TIME and CURRENT_DATE as default values for TIME and DATE columns (CURRENT_TIMESTAMP for DATETIME is supported).

    • [x] ~5. Default literal values must be escaped.~

    Update: Removed from this PR in favor of #2850... Current doctrine implementation does not handle escaping of defaults (backslashes...), adding it in this P/R should be done for all platforms at once and thus introduce more BC-risks (bugs...). Implementing separately looks safer and should help having this P/R sooner. Both P/R can be done independently.

    ~While table comments are automatically quoted (MySQLPlatform::quoteStringLiteral()) the default column values were not. Defaults values for string literals are now properly quoted.~

    • [x] 6. MariaDB 10.2.7 information changes schema for default values.

    In order to allow expressions as default values and distinguish them from literals, Mariadb now quotes default values in information_schema.column table. This changes brings a lot of incompatibilities between the (Oracle-)Mysql/MariaDB platforms.

    Instead of creating a specific SchemaManager for MariaDB, the solution taken in this P/R is to map the introduced changes in the current MySQLSchemaManager (method: getMariaDb1027ColumnDefault()).

    From MariaDB 10.2.7, information schema changes includes:

    • NULL is now quoted as 'NULL' (see exceptions in Unquoted null is technically possible)
    • String defaults are quoted (To save the string 'NULL', set the default as ''NULL'').
    • CURRENT_TIMESTAMP, CURRENT_DATE, CURRENT_TIME defaults are silently changed to 'current_timestamp()', 'currdate()', 'currtime()'. To prevent schema diff, they are mapped back to their original values.
    • (UPDATED) Partial support for default literal escaping have been done. Currently limited on schema introspection. #2850 will bring the support for backslashes in defaults later on but the code is ready.

    See for reference and, for history.

    Will fix:

    • (invalid detection of column default values)
    • (schema update will alter all tables with columns_default='null')
    • [x] 7. Support for MariaDB 10.2 reserved keywords.

    • [x] 8. Added MariaDB 10.2 in travis (both pdo_mysql and mysqli)

    Possible BC-breaks:

    None found. Since the extraction of defaults escaping in #2850, there should be no impact on other platforms.

    Portability concerns

    • Portability of CURRENT_TIME and CURRENT_DATE, mariadb 10.2 supports them, but no way to get back to mysql.

    Issues that will be fixed by this P/R:

    DBAL related:

    • (invalid detection of column default values)

    ORM related:

    • (schema update will alter all tables with columns_default='null')
    • (Doctrine schema update command shows always few queries to be executed)

    Fix (or might fix) the following issues :

    • belgattitude/openstore-schema-core#1

    Other related open P/R

    • (Escaping of default values)
    Reviewed by belgattitude at 2017-08-26 22:13
  • 3. Introduce a Transaction object

    This is a proposal for a Transaction object, as suggested by @guilhermeblanco and @beberlei in doctrine/doctrine2#949; this is a foundation for the proposed ORM feature. This supersedes #571.

    This PR makes the following changes to Connection:

    • Adds the following method:
      • getTransactionManager() : returns the TransactionManager
    • Deprecates the following methods, in favour of their counterparts on Transaction:
      • commit()
      • rollBack()
      • setRollbackOnly()
      • isRollbackOnly()
    • Deprecates the following methods, in favour of their counterparts on TransactionManager:
      • beginTransaction()
      • isTransactionActive()
      • getTransactionNestingLevel()
      • setNestTransactionsWithSavepoints()
      • getNestTransactionsWithSavepoints()

    The new way of dealing with transactions is now:

    $transactionManager = $connection->getTransactionManager();
    $transaction = $transactionManager->beginTransaction();
    // ...

    A fluent API is available to configure a transaction:

    $transaction = $transactionManager->createTransaction()

    Calls to commit() and rollback() are automatically propagated to nested transactions:

    $transaction1 = $transactionManager->beginTransaction();
    $transaction2 = $transactionManager->beginTransaction();
    $transaction1->commit(); // will commit $transaction2 then $transaction1

    Overall, it's not a complicated change, does not introduce any BC break, and passes all existing tests.

    Reviewed by BenMorel at 2014-07-21 23:14
  • 4. Support for backslashes in default values

    It is now possible to use \, or other escaped characters, in default values for a column.

    Previously this lead to a confusion when diffing actual and expected schema leading to perpetual out of sync schema.

    Reviewed by PowerKiKi at 2017-09-09 15:25
  • 5. Add native JSON type support for MySQL >=5.7.8

    As of MySQL 5.7.8, MySQL supports a native JSON data type that enables efficient access to data in JSON (JavaScript Object Notation) documents. The JSON data type provides these advantages over storing JSON-format strings in a string column:

    Reviewed by ismailbaskin at 2015-12-18 13:41
  • 6. [DBAL-831] Fix explicit case sensitive identifiers in Oracle

    This is a patch for

    It includes the following bugfixes:

    • Makes normalization of internally stored assets names consistent in Doctrine\DBAL\Schema\Table
    • Fixes SQL generation for autoincrement columns with explicitly quoted table/column names in Oracle.
    • Oracle identifiers that have been stored with explicit quotation are now correctly reverse-engineered by explicitly quoting them again in the schema manager (to preserve case). This only applies to identifiers that are not all uppercase (which indicated that they have been stored with a non-standard case).
    Reviewed by deeky666 at 2014-09-11 17:25
  • 7. Migrate jobs away from Travis to Github Actions


    • [x] mssql
    • [x] sqlite
    • [x] ibm_db2 (this one looks really nasty)
    • [x] setup dependency on sqlite job
    • [x] decide what to do about PostgreSQL 9.2 (I can't get it to work)
    • [x] add some 7.3 jobs for MariaDB / PG ?
    • [x] remove scripts from tests/travis?
    • [x] find replacement for cron that runs with dev dependencies
    • [x] make relevant jobs required
    • [x] make sure code coverage hasn't decreased
    • [x] test only the latest version of a platform when not using the latest version of PHP
    Reviewed by greg0ire at 2020-09-28 15:10
  • 8. Fix identifier escaping when used as string literal for T-SQL

    | Q | A |------------- | ----------- | Type | bug | BC Break | no | Fixed issues | #4283


    Alternative to closes to fix it correctly and with proper functional tests.

    The issue was that MSSQL expects string literal (quoted by ') for EXEC sp_addextendedproperty instead of identifier name (quoted by []).

    Reviewed by mvorisek at 2020-10-19 08:47
  • 9. Identify retryable transaction errors and cause them to raise specific exception types

    So this PR now only contains the explicit exceptions so they can be catched RDMBS agnostic.

    Below description can be provided externally.

    It is best practice to implement retry logic for transactions that are aborted because of deadlocks or timeouts. This makes such method available inside the DBAL and also adds detection for errors where retrying makes sense in the different database drivers.

    Deadlocks and timeouts are caused by lock contention and you often can design your application to reduce the likeliness that such an error occurs. But it's impossible to guarantee that such error conditions will never occur. This is why implementing retrying logic for such errors is actually a must when you have to ensure the application does not fail in edge cases or high load. Some references where something similar has already been discussed and implemented:

    I chose the name retryable because it is consistent with transactional. I think the implementation is quite straight forward and fits very well with the DBAL design.

    In our case we had seldomly errors like

    • Doctrine\\DBAL\\Exception\\DriverException: An exception occurred while executing 'UPDATE product SET modified = ? WHERE id = ?' with params [\"2014-10-15 16:28:55\", \"460315800000\"]:\n\nSQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction
    • Doctrine\\DBAL\\Exception\\DriverException: An exception occurred while executing 'INSERT INTO ... VALUES (...)' with params [\"...\", \"...\"]:\n\nSQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction

    As you can see even the exception message suggests to retry the transaction. This is now easily possible with

    $retryWrapper = new RetryWrapper(function () use ($con) {
        return $con->update('tablename', array('field' => 'data'), array('id' => 'myid'));
    $affectedRows = $retryWrapper();
    Reviewed by Tobion at 2014-11-05 17:09
  • 10. Fix that MysqliStatement cannot handle streams

    | Q | A |------------- | ----------- | Type | bug | BC Break | no | Fixed issues |

    The binary and blob types map BLOB/BINARY/VARBINARY columns to PHP streams. Internally, they use the ParameterType::LARGE_OBJECT (i. e. \PDO::PARAM_LOB) binding type, which suggests that efficient handling of PHP stream resources was intended.

    However, at least when using the mysqli driver, stream resources passed into insert() or update() are simply cast to strings. As a result, a literal string like "Resource id #126" will end up in the database.

    This PR fixes the issue by correctly processing streams in the MysqliStatement when they are passed with the ParameterType::LARGE_OBJECT binding type. It uses the mysqli::send_long_data() method to pass stream data in chunks to the MySQL server, thus keeping the memory footprint low. This method does not (despite claims to the contrary) allow to bypass the max_allowed_package size.

    Update: The pdo_mysql driver was already capable of handling streams this way. Now this is covered by tests.

    Helpful documentation

    • - see first "Note"
    Reviewed by mpdude at 2018-07-13 21:32
  • 11. Php Inspections (EA Ultimate): minor code tweaks

    | Q | A |------------- | ----------- | Type | improvement | BC Break | no | Fixed issues | n/a

    Minor code tweaks by Php Inspections (EA Ultimate)

    Reviewed by kalessil at 2018-06-29 11:01
  • 12. Deprecate ignoring foreign keys DDL on non-InnoDB MySQL

    I'm working on replacing CreateSchemaSqlCollector and DropSchemaSqlCollector with another set of SQL builders to enable seamless support for foreign keys on SQLite (see

    There will be a new platform method that will generate SQL to create a set of tables that may reference each other via foreign keys at once.

    The default implementation in the AbstractPlatform class will first create all tables without foreign keys and then create all foreign keys. This way, the foreign key dependencies will be satisfied at every step of the migration.

    The implementation for the SQLite platform will just create the tables with the foreign keys because:

    1. SQLite doesn't support adding foreign keys to an existing table.
    2. SQLite doesn't require the table referenced by a foreign key to exist when the table is created.

    In order to support the feature being deprecated, I'll have to create another implementation of the above method for MySQL which would ignore foreign keys in the new method if the target engine will ignore them.

    While it's not a big deal, I believe this functionality is pointless: if the application that owns the schema doesn't require the support for foreign keys across all target platforms and storage engines, it should enforce referential integrity itself, at which point it won't require it enforced by the database platform. If it does need them, it should use only the platforms and the engines that support them.

    Reviewed by morozov at 2022-05-27 03:36
  • 13. Dropping Default Contraints with SQLServer doesn't work

    Bug Report

    | Q | A |------------ | ------ | Version | 3.3.4


    When dropping a default constraint, DBAL generates a random name for the constaint to be dropped, which obviously doesn't work. This happens in ChangeColumn.php. This calls getAlterTableSQL in compile. If it is determined that the default constraint has to be removed then getAlterTableDropDefaultConstraintClause (In SQLServerPlatform.php) is called which calls generateDefaultConstraintName. This generates a random name for a default constraint, instead of finding it.

    I stumbled upon this through laravel which ideally would use it's own compileDropDefaultConstraint. That (SQL) code works and could also be used in DBAL as it's MIT Licensed. However as a query on the sys.columns table is needed, a simple "ALTER TABLE " . $code won't do the trick.

    Current behaviour

    Dropping default constraints on SQL Server fails with "Constraint does not exist".

    How to reproduce

    Create a Database/Table/User on an SQL Server. The Table should have a column with a default value. Try to remove the default constraint via ChangeColumn.

    Expected behaviour

    The default constraint is removed.

    Reviewed by Pixdigit at 2022-05-25 08:58
  • 14. PoC: Implementation for a more flexible type system

    | Q | A |------------- | ----------- | Type | improvement | Fixed issues |


    Following up the discussion in and i would like to propose a solution for for following problems:

    • Complexity of AbstractPlatform is very high as it contains a lot of methods
    • Flexibility for platform specific type conversions is only given if there is a method in AbstractPlatform to support it, which sometimes requires changes to AbstractPlatform in order to create a new driver

    My PR moves type conversions from AbstractPlatform to separate classes, making them more reusable and allowing overrides for specific platform implementations.

    This PR demonstrates the idea using the BooleanType. If the concept is good then i would update the PR to apply the same strategy to all other types.

    Reviewed by Deltachaos at 2022-05-19 12:04
  • 15. Transaction nesting level of primary connection is not increased in PrimaryReadReplicaConnection

    Bug Report

    Any version


    When beginTransaction is called in PrimaryReadReplicaConnection the nesting level of PrimaryReadReplicaConnection is increased, but not the nesting level of the primary connection. So if you use the primary connection directly and call beginTransaction there, you will get a There is already an active transaction.

    Current behaviour

    The PDO connection underneath is throwing a There is already an active transaction exception.

    How to reproduce

    1. Create a PrimaryReadReplicaConnection PRRC with a primary connection A and replica B
    2. Begin transaction in PRRC
    3. Begin transaction in connection A
    4. Error: There is already an active transaction

    Expected behaviour

    The primary connection should understand, that a transaction is already started and continue without error.

    Reviewed by mansurs at 2022-05-19 07:36
  • 16. Migration generates unnecessary queries

    Bug Report

    | Q | A |------------ | ------ | BC Break | yes | Version | 3.3.1 - dev-master, in 3.3.0 there is only 1 (different) sql


    After upgrading from DBAL 2.x and to latest 3.x version, I have ran a diff command and a weird migration was generated. Same output is generated in o:s:u --dump-sql

    ALTER TABLE product_badge CHANGE slug slug VARCHAR(15) NOT NULL COLLATE `utf8_bin`
    ALTER TABLE parameter CHANGE slug slug VARCHAR(15) NOT NULL COLLATE `utf8_bin`
    ALTER TABLE parameter_value CHANGE slug slug VARCHAR(15) NOT NULL COLLATE `utf8_bin`
    ALTER TABLE product CHANGE slug slug VARCHAR(15) NOT NULL COLLATE `utf8_bin`
    ALTER TABLE product_brand CHANGE slug slug VARCHAR(15) NOT NULL COLLATE `utf8_bin`
    ALTER TABLE customer CHANGE slug slug VARCHAR(15) NOT NULL COLLATE `utf8_bin`

    In my code there is

    #[ORM\Column(length: 15, unique: true, options: ['collation' => 'utf8_bin'])]
    private string $slug;

    and database structure match the parameters, even when I execute the statements, next migration will be the same.

    I'm using PHP 8.1, MariaDB 10.3.34

    In version 3.3.0 it works just fine, but it will generate me only this query, because I guess there was some bug with enums, because name is a enum, it disappears when I use type SystemMetaName|string, so I locked my project to that version for now. It is no longer in 3.3.1+, but there are that queries with utf8_bin collation.

    ALTER TABLE system_meta CHANGE name name VARCHAR(255) NOT NULL
    #[ORM\Column(unique: true)]
    private SystemMetaName $name;

    There is a diff but I can't see the probable cause of this bug.

    My dbal settings are:

    		charset: utf8mb4
    			charset: utf8mb4
    			collate: utf8mb4_unicode_ci
    		driver: pdo_mysql
    				class: Ramsey\Uuid\Doctrine\UuidBinaryType
    				commented: false

    Settings are from nettrine implementation in Nette Framework

    Reviewed by Rixafy at 2022-05-01 23:05
  • 17. SQLite should properly check if foreign keys are supported and/or omit foreign key statements when a table diff is analyzed

    Bug Report

    | Q | A |------------ | ------ | Version | 3.3.5


    while investigating some differences reported by the doctrine:migration:diff command I figured out that there is every time a diff is reported for tables containing foreign keys because of ManyToOne relations. when inspecting the generated migration file I've seen that the up method contains foreign keys while the down method does not. as far as I could see the code sometimes checks if foreign keys are supported before generating the relevant statements and sometimes not.

    as foreign keys are a feature that can be turned on/off for an SQLite database it probably makes sense to replace the hardcoded false in SqlitePlatform in method supportsForeignKeyConstraints with a call to PRAGMA foreign_keys; where the result of 0 means it is disabled so the method should return false while the result 1 should return true.

    additionally, the getAlterTableSQL in SqlitePlatform should probably check if foreign keys are supported to generate the correct statements not containing the foreign keys. currently, they are generated regardless if SQLite supports it or not.

    Current behaviour

    table diff migration generates a migration containing foreign keys for SQLite even if the method supportsForeignKeyConstraints returns every time false.

    How to reproduce

    1. create two entities with a ManyToOne relation
    2. run symfony console doc:mig:diff --from-empty-schema
    3. run symfony console doc:mig:mig
    4. run symfony console doc:mig:diff

    step 4 now generates a diff containing foreign key statements where I actually would expect that nothing is created

    Expected behaviour

    omit the foreign key statements in the create table statement for a diff OR determine if SQLite supports foreign keys so that the check is database feature dependent OR add checks if foreign keys are supported in more places which are currently assume they are supported (mainly in the area for the TableDiff handling

    Reviewed by BigMichi1 at 2022-04-26 12:55
Related tags
:gem: Simple MySQLi Abstraction Layer + Doctrine/DBAL support

?? Simple MySQLi Class This is a simple MySQL Abstraction Layer compatible with PHP 7+ that provides a simple and secure interaction with your databas

Mar 16, 2022
Database Abstraction Layer, Schema Introspection, Schema Generation, Query Builders

Cycle DBAL Secure, multiple SQL dialects (MySQL, PostgreSQL, SQLite, SQLServer), schema introspection, schema declaration, smart identifier wrappers,

Apr 22, 2022
ORM layer that creates models, config and database on the fly

RedBeanPHP 5 RedBeanPHP is an easy to use ORM tool for PHP. Automatically creates tables and columns as you go No configuration, just fire and forget

May 17, 2022
Laravel 5 - Repositories to abstract the database layer

Laravel 5 Repositories Laravel 5 Repositories is used to abstract the data layer, making our application more flexible to maintain. See versions: 1.0.

May 24, 2022
A Redis based, fully automated and scalable database cache layer for Laravel
A Redis based, fully automated and scalable database cache layer for Laravel

Lada Cache A Redis based, fully automated and scalable database cache layer for Laravel Contributors wanted! Have a look at the open issues and send m

May 24, 2022
Doctrine Object Relational Mapper (ORM)

3.0.x 2.9.x 2.8.x Doctrine 2 is an object-relational mapper (ORM) for PHP 7.1+ that provides transparent persistence for PHP objects. It sits on top o

May 28, 2022
A drop-in Doctrine ORM 2 implementation for Laravel 5+ and Lumen
A drop-in Doctrine ORM 2 implementation for Laravel 5+ and Lumen

Laravel Doctrine ORM A drop-in Doctrine ORM 2 implementation for Laravel 5+ $scientist = new Scientist( 'Albert', 'Einstein' ); $scientist->a

May 19, 2022
🔌 A Doctrine DBAL Driver implementation on top of Swoole Coroutine PostgreSQL extension

Swoole Coroutine PostgreSQL Doctrine DBAL Driver A Doctrine\DBAL\Driver implementation on top of Swoole\Coroutine\PostgreSQL. Getting started Install

Apr 1, 2022
Psalm Stubs for doctrine/mongodb-odm library

doctrine-mongodb-psalm-plugin A Doctrine plugin for Psalm (requires Psalm v4). Installation: $ composer require --dev runtothefather/doctrine-mongodb-

Apr 12, 2021
Provides integration for Doctrine with various Symfony components.

Doctrine Bridge The Doctrine bridge provides integration for Doctrine with various Symfony components. Resources Contributing Report issues and send P

May 21, 2022
Doctrine PHP mapping driver

WORK IN PROGRESS! Doctrine PHP mapping driver Alternative mapping driver that allows to write mappings in PHP. Documentation Associations examples TOD

Aug 15, 2021
Doctrine extension to persist spatial data objects.

doctrine-Spatial Doctrine-spatial is a doctrine extension. It implements spatial types and functions. As exemple, this extension can help you to know

May 17, 2022
Monadic Doctrine repositories helper classes and services.

Doctrine Repository Monadic Helper Description This project provides the necessary classes and services to use Doctrine repositories in a more functio

Jan 9, 2022
PostgreSQL enhancements for Doctrine

PostgreSQL enhancements for Doctrine. Provides support for advanced data types (json, jssnb, arrays), text search, array operators and jsonb specific functions.

May 22, 2022
Pure PHP NoSQL database with no dependency. Flat file, JSON based document database.
Pure PHP NoSQL database with no dependency. Flat file, JSON based document database.

Please give it a Star if you like the project ?? ❤️ SleekDB - A NoSQL Database made using PHP Full documentation: SleekDB i

May 20, 2022
SleekwareDB is a NoSQL database storage service. A database storage service that can be used for various platforms and is easy to integrate.
SleekwareDB is a NoSQL database storage service. A database storage service that can be used for various platforms and is easy to integrate.

SleekwareDB is a NoSQL database storage service. A database storage service that can be used for various platforms and is easy to integrate. NoSQL API

May 23, 2022
The lightweight PHP database framework to accelerate development
The lightweight PHP database framework to accelerate development

The lightweight PHP database framework to accelerate development Features Lightweight - Less than 100 KB, portable with only one file Easy - Extremely

May 24, 2022
[READ ONLY] Subtree split of the Illuminate Database component (see laravel/framework)

Illuminate Database The Illuminate Database component is a full database toolkit for PHP, providing an expressive query builder, ActiveRecord style OR

May 17, 2022
SQL database access through PDO.

Aura.Sql Provides an extension to the native PDO along with a profiler and connection locator. Because ExtendedPdo is an extension of the native PDO,

May 8, 2022