Doctrine Mapping Matrix

PHP | Wednesday, 19 July 2023
The following table shows an overview of Doctrine's type abstraction. The matrix contains the mapping information for how a specific Doctrine type is mapped to the database and back to PHP.
+-------------------+---------------+-----------------------------------------------------------------------------------------------+
| Doctrine          | PHP           | Database vendor                                                                               |
|                   |               +--------------------------+---------+----------------------------------------------------------+
|                   |               | Name                     | Version | Type                                                     |
+===================+===============+==========================+=========+==========================================================+
| **smallint**      | ``integer``   | **MySQL**                | *all*   | ``SMALLINT`` ``UNSIGNED`` [10]  ``AUTO_INCREMENT`` [11]  |
|                   |               +--------------------------+---------+----------------------------------------------------------+
|                   |               | **PostgreSQL**           | *all*   | ``SMALLINT``                                             |
|                   |               +--------------------------+---------+----------------------------------------------------------+
|                   |               | **Oracle**               | *all*   | ``NUMBER(5)``                                            |
|                   |               +--------------------------+---------+----------------------------------------------------------+
|                   |               | **SQL Server**           | *all*   | ``SMALLINT`` ``IDENTITY`` [11]                           |
|                   |               +--------------------------+---------+----------------------------------------------------------+
|                   |               | **SQLite**               | *all*   | ``INTEGER`` [15]                                         |
+-------------------+---------------+--------------------------+---------+----------------------------------------------------------+
| **integer**       | ``integer``   | **MySQL**                | *all*   | ``INT`` ``UNSIGNED`` [10]  ``AUTO_INCREMENT`` [11]       |
|                   |               +--------------------------+---------+----------------------------------------------------------+
|                   |               | **PostgreSQL**           | *all*   | ``INT`` [12]                                             |
|                   |               |                          |         +----------------------------------------------------------+
|                   |               |                          |         | ``SERIAL`` [11]                                          |
|                   |               +--------------------------+---------+----------------------------------------------------------+
|                   |               | **Oracle**               | *all*   | ``NUMBER(10)``                                           |
|                   |               +--------------------------+---------+----------------------------------------------------------+
|                   |               | **SQL Server**           | *all*   | ``INT`` ``IDENTITY`` [11]                                |
|                   |               +--------------------------+---------+----------------------------------------------------------+
|                   |               | **SQLite**               | *all*   | ``INTEGER`` [15]                                         |
+-------------------+---------------+--------------------------+---------+----------------------------------------------------------+
| **bigint**        | ``string``    | **MySQL**                | *all*   | ``BIGINT`` ``UNSIGNED`` [10]  ``AUTO_INCREMENT`` [11]    |
|                   | [8]           +--------------------------+---------+----------------------------------------------------------+
|                   |               | **PostgreSQL**           | *all*   | ``BIGINT`` [12]                                          |
|                   |               |                          |         +----------------------------------------------------------+
|                   |               |                          |         | ``BIGSERIAL`` [11]                                       |
|                   |               +--------------------------+---------+----------------------------------------------------------+
|                   |               | **Oracle**               | *all*   | ``NUMBER(20)``                                           |
|                   |               +--------------------------+---------+----------------------------------------------------------+
|                   |               | **SQL Server**           | *all*   | ``BIGINT`` ``IDENTITY`` [11]                             |
|                   |               +--------------------------+---------+----------------------------------------------------------+
|                   |               | **SQLite**               | *all*   | ``INTEGER`` [15]                                         |
+-------------------+---------------+--------------------------+---------+----------------------------------------------------------+
| **decimal** [7]   | ``string``    | **MySQL**                | *all*   | ``NUMERIC(p, s)`` ``UNSIGNED`` [10]                      |
|                   | [9]           +--------------------------+---------+----------------------------------------------------------+
|                   |               | **PostgreSQL**           | *all*   | ``NUMERIC(p, s)``                                        |
|                   |               +--------------------------+         |                                                          |
|                   |               | **Oracle**               |         |                                                          |
|                   |               +--------------------------+         |                                                          |
|                   |               | **SQL Server**           |         |                                                          |
|                   |               +--------------------------+         |                                                          |
|                   |               | **SQLite**               |         |                                                          |
+-------------------+---------------+--------------------------+---------+----------------------------------------------------------+
| **float**         | ``float``     | **MySQL**                | *all*   | ``DOUBLE PRECISION`` ``UNSIGNED`` [10]                   |
|                   |               +--------------------------+---------+----------------------------------------------------------+
|                   |               | **PostgreSQL**           | *all*   | ``DOUBLE PRECISION``                                     |
|                   |               +--------------------------+         |                                                          |
|                   |               | **Oracle**               |         |                                                          |
|                   |               +--------------------------+         |                                                          |
|                   |               | **SQL Server**           |         |                                                          |
|                   |               +--------------------------+         |                                                          |
|                   |               | **SQLite**               |         |                                                          |
+-------------------+---------------+--------------------------+---------+----------------------------------------------------------+
| **string**        | ``string``    | **MySQL**                | *all*   | ``VARCHAR(n)`` [3]                                       |
| [2]  [5]          |               +--------------------------+         |                                                          |
|                   |               | **PostgreSQL**           |         |                                                          |
|                   |               +--------------------------+         +----------------------------------------------------------+
|                   |               | **SQLite**               |         |                                                          |
|                   |               +--------------------------+---------+----------------------------------------------------------+
|                   |               | **Oracle**               | *all*   | ``VARCHAR2(n)`` [3]                                      |
|                   |               |                          |         +----------------------------------------------------------+
|                   |               |                          |         | ``CHAR(n)`` [4]                                          |
|                   |               +--------------------------+---------+----------------------------------------------------------+
|                   |               | **SQL Server**           | *all*   | ``NVARCHAR(n)`` [3]                                      |
|                   |               |                          |         +----------------------------------------------------------+
|                   |               |                          |         | ``NCHAR(n)`` [4]                                         |
+-------------------+---------------+--------------------------+---------+----------------------------------------------------------+
| **ascii_string**  | ``string``    | **SQL Server**           |         | ``VARCHAR(n)``                                           |
|                   |               |                          |         | ``CHAR(n)``                                              |
+-------------------+---------------+--------------------------+---------+----------------------------------------------------------+
| **text**          | ``string``    | **MySQL**                | *all*   | ``TINYTEXT`` [16]                                        |
|                   |               |                          |         +----------------------------------------------------------+
|                   |               |                          |         | ``TEXT`` [17]                                            |
|                   |               |                          |         +----------------------------------------------------------+
|                   |               |                          |         | ``MEDIUMTEXT`` [18]                                      |
|                   |               |                          |         +----------------------------------------------------------+
|                   |               |                          |         | ``LONGTEXT`` [19]                                        |
|                   |               +--------------------------+---------+----------------------------------------------------------+
|                   |               | **PostgreSQL**           | *all*   | ``TEXT``                                                 |
|                   |               +--------------------------+         |                                                          |
|                   |               | **Oracle**               | *all*   | ``CLOB``                                                 |
|                   |               +--------------------------+         |                                                          |
|                   |               | **SQLite**               |         |                                                          |
|                   |               +--------------------------+---------+----------------------------------------------------------+
|                   |               | **SQL Server**           | *all*   | ``VARCHAR(MAX)``                                         |
+-------------------+---------------+--------------------------+---------+----------------------------------------------------------+
| **guid**          | ``string``    | **MySQL**                | *all*   | ``CHAR(36)`` [1]                                         |
|                   |               +--------------------------+         |                                                          |
|                   |               | **Oracle**               |         |                                                          |
|                   |               +--------------------------+         |                                                          |
|                   |               | **SQLite**               |         |                                                          |
|                   |               +--------------------------+---------+----------------------------------------------------------+
|                   |               | **SQL Server**           | *all*   | ``UNIQUEIDENTIFIER``                                     |
|                   |               +--------------------------+         |                                                          |
|                   |               | **PostgreSQL**           | *all*   | ``UUID``                                                 |
+-------------------+---------------+--------------------------+---------+----------------------------------------------------------+
| **binary**        | ``resource``  | **MySQL**                | *all*   | ``VARBINARY(n)`` [3]                                     |
| [2]  [6]          |               +--------------------------+         |                                                          |
|                   |               | **SQL Server**           |         +----------------------------------------------------------+
|                   |               +--------------------------+         | ``BINARY(n)`` [4]                                        |
|                   |               | **Oracle**               | *all*   | ``RAW(n)``                                               |
|                   |               +--------------------------+---------+----------------------------------------------------------+
|                   |               | **PostgreSQL**           | *all*   | ``BYTEA`` [15]                                           |
|                   |               +--------------------------+---------+----------------------------------------------------------+
|                   |               | **SQLite**               | *all*   | ``BLOB`` [15]                                            |
+-------------------+---------------+--------------------------+---------+----------------------------------------------------------+
| **blob**          | ``resource``  | **MySQL**                | *all*   | ``TINYBLOB`` [16]                                        |
|                   |               |                          |         +----------------------------------------------------------+
|                   |               |                          |         | ``BLOB`` [17]                                            |
|                   |               |                          |         +----------------------------------------------------------+
|                   |               |                          |         | ``MEDIUMBLOB`` [18]                                      |
|                   |               |                          |         +----------------------------------------------------------+
|                   |               |                          |         | ``LONGBLOB`` [19]                                        |
|                   |               +--------------------------+---------+----------------------------------------------------------+
|                   |               | **Oracle**               | *all*   | ``BLOB``                                                 |
|                   |               +--------------------------+         |                                                          |
|                   |               | **SQLite**               |         |                                                          |
|                   |               +--------------------------+---------+----------------------------------------------------------+
|                   |               | **SQL Server**           | *all*   | ``VARBINARY(MAX)``                                       |
|                   |               +--------------------------+---------+----------------------------------------------------------+
|                   |               | **PostgreSQL**           | *all*   | ``BYTEA``                                                |
+-------------------+---------------+--------------------------+---------+----------------------------------------------------------+
| **boolean**       | ``boolean``   | **MySQL**                | *all*   | ``TINYINT(1)``                                           |
|                   |               +--------------------------+---------+----------------------------------------------------------+
|                   |               | **PostgreSQL**           | *all*   | ``BOOLEAN``                                              |
|                   |               +--------------------------+         |                                                          |
|                   |               | **SQLite**               |         |                                                          |
|                   |               +--------------------------+---------+----------------------------------------------------------+
|                   |               | **SQL Server**           | *all*   | ``BIT``                                                  |
|                   |               +--------------------------+         |                                                          |
|                   |               | **Oracle**               | *all*   | ``NUMBER(1)``                                            |
+-------------------+---------------+--------------------------+---------+----------------------------------------------------------+
| **date**          | ``\DateTime`` | **MySQL**                | *all*   | ``DATE``                                                 |
|                   |               +--------------------------+         |                                                          |
|                   |               | **PostgreSQL**           |         |                                                          |
|                   |               +--------------------------+         |                                                          |
|                   |               | **Oracle**               |         |                                                          |
|                   |               +--------------------------+         |                                                          |
|                   |               | **SQLite**               |         |                                                          |
|                   |               +--------------------------+---------+                                                          |
|                   |               | **SQL Server**           | "all"   |                                                          |
+-------------------+---------------+--------------------------+---------+----------------------------------------------------------+
| **datetime**      | ``\DateTime`` | **MySQL**                | *all*   | ``DATETIME`` [13]                                        |
|                   |               +--------------------------+---------+----------------------------------------------------------+
|                   |               | **SQL Server**           | *all*   | ``DATETIME``                                             |
|                   |               +--------------------------+         |                                                          |
|                   |               | **SQLite**               |         |                                                          |
|                   |               +--------------------------+---------+----------------------------------------------------------+
|                   |               | **PostgreSQL**           | *all*   | ``TIMESTAMP(0) WITHOUT TIME ZONE``                       |
|                   |               +--------------------------+---------+----------------------------------------------------------+
|                   |               | **Oracle**               | *all*   | ``TIMESTAMP(0)``                                         |
+-------------------+---------------+--------------------------+---------+----------------------------------------------------------+
| **datetimetz**    | ``\DateTime`` | **MySQL**                | *all*   | ``DATETIME``  [14]  [15]                                 |
|                   |               +--------------------------+         |                                                          |
|                   |               | **SQLite**               |         |                                                          |
|                   |               +--------------------------+---------+                                                          |
|                   |               | **SQL Server**           | "all"   |                                                          |
|                   |               +--------------------------+---------+----------------------------------------------------------+
|                   |               | **PostgreSQL**           | *all*   | ``TIMESTAMP(0) WITH TIME ZONE``                          |
|                   |               +--------------------------+         |                                                          |
|                   |               | **Oracle**               |         |                                                          |
+-------------------+---------------+--------------------------+---------+----------------------------------------------------------+
| **time**          | ``\DateTime`` | **MySQL**                | *all*   | ``TIME``                                                 |
|                   |               +--------------------------+         |                                                          |
|                   |               | **SQLite**               |         |                                                          |
|                   |               +--------------------------+---------+----------------------------------------------------------+
|                   |               | **PostgreSQL**           | *all*   | ``TIME(0) WITHOUT TIME ZONE``                            |
|                   |               +--------------------------+---------+----------------------------------------------------------+
|                   |               | **Oracle**               | *all*   | ``DATE`` [15]                                            |
|                   |               +--------------------------+---------+----------------------------------------------------------+
|                   |               | **SQL Server**           | "all"   | ``TIME(0)``                                              |
+-------------------+---------------+--------------------------+---------+----------------------------------------------------------+
| **array** [1]     | ``array``     | **MySQL**                | *all*   | ``TINYTEXT`` [16]                                        |
+-------------------+               |                          |         +----------------------------------------------------------+
| **simple array**  |               |                          |         | ``TEXT`` [17]                                            |
| [1]               |               |                          |         +----------------------------------------------------------+
|                   |               |                          |         | ``MEDIUMTEXT`` [18]                                      |
|                   |               |                          |         +----------------------------------------------------------+
|                   |               |                          |         | ``LONGTEXT`` [19]                                        |
|                   |               +--------------------------+---------+----------------------------------------------------------+
|                   |               | **PostgreSQL**           | *all*   | ``TEXT``                                                 |
|                   |               +--------------------------+         |                                                          |
|                   |               | **Oracle**               | *all*   | ``CLOB``                                                 |
|                   |               +--------------------------+         |                                                          |
|                   |               | **SQLite**               |         |                                                          |
|                   |               +--------------------------+---------+----------------------------------------------------------+
|                   |               | **SQL Server**           | *all*   | ``VARCHAR(MAX)``                                         |
+-------------------+---------------+--------------------------+---------+----------------------------------------------------------+
| **json**          | ``mixed``     | **MySQL**                | *all*   | ``JSON``                                                 |
|                   |               +--------------------------+---------+----------------------------------------------------------+
|                   |               | **PostgreSQL**           | *all*   | ``JSON`` [20]                                            |
|                   |               |                          |         +----------------------------------------------------------+
|                   |               |                          |         | ``JSONB`` [21]                                           |
|                   |               +--------------------------+---------+----------------------------------------------------------+
|                   |               | **Oracle**               | *all*   | ``CLOB`` [1]                                             |
|                   |               +--------------------------+         |                                                          |
|                   |               | **SQLite**               |         |                                                          |
|                   |               +--------------------------+---------+----------------------------------------------------------+
|                   |               | **SQL Server**           | *all*   | ``VARCHAR(MAX)`` [1]                                     |
+-------------------+---------------+--------------------------+---------+----------------------------------------------------------+
| **object** [1]    | ``object``    | **MySQL**                | *all*   | ``TINYTEXT`` [16]                                        |
|                   |               |                          |         +----------------------------------------------------------+
|                   |               |                          |         | ``TEXT`` [17]                                            |
|                   |               |                          |         +----------------------------------------------------------+
|                   |               |                          |         | ``MEDIUMTEXT`` [18]                                      |
|                   |               |                          |         +----------------------------------------------------------+
|                   |               |                          |         | ``LONGTEXT`` [19]                                        |
|                   |               +--------------------------+---------+----------------------------------------------------------+
|                   |               | **PostgreSQL**           | *all*   | ``TEXT``                                                 |
|                   |               +--------------------------+         |                                                          |
|                   |               | **Oracle**               | *all*   | ``CLOB``                                                 |
|                   |               +--------------------------+         |                                                          |
|                   |               | **SQLite**               |         |                                                          |
|                   |               +--------------------------+---------+----------------------------------------------------------+
|                   |               | **SQL Server**           | *all*   | ``VARCHAR(MAX)``                                         |
+-------------------+---------------+--------------------------+---------+----------------------------------------------------------+
Notes
[1] Requires hint in the column comment for proper reverse engineering of the appropriate Doctrine type mapping.
[2] n is the length attribute set in the column definition (defaults to 255 if omitted).
[3] Chosen if the column definition has the fixed attribute set to false (default).
[4] Chosen if the column definition has the fixed attribute set to true.
[5] Silently maps to the vendor specific text type if the given length attribute for n exceeds the maximum length the related platform allows. If this is the case, please see [15] .
[6] Silently maps to the vendor specific blob type if the given length attribute for n exceeds the maximum length the related platform allows. If this is the case, please see [15] .
[7] p is the precision and s the scale set in the column definition. The precision defaults to 10 and the scale to 0 if not set.
[8] Returns PHP string type value instead of integer because of maximum integer value implications on non 64bit platforms.
[9] Returns PHP string type value instead of double because of PHP's limitation in preserving the exact precision when casting to double.
[10] Used if unsigned attribute is set to true in the column definition (default false).
[11] Used if autoincrement attribute is set to true in the column definition (default false).
[12] Chosen if the column definition has the autoincrement attribute set to false (default).
[13] Chosen if the column definition does not contain the version option inside the platformOptions attribute array or is set to false which marks it as a non-locking information column.
[14] Fallback type as the vendor does not support a native date time type with timezone information. This means that the timezone information gets lost when storing a value.
[15] Cannot be safely reverse engineered to the same Doctrine type as the vendor does not have a native distinct data type for this mapping. Using this type with this vendor can therefore have implications on schema comparison (online vs offline schema) and PHP type safety (data conversion from database to PHP value) because it silently falls back to its appropriate Doctrine type.
[16] Chosen if the column length is less or equal to 2 ^ 8 - 1 = 255.
[17] Chosen if the column length is less or equal to 2 ^ 16 - 1 = 65535.
[18] Chosen if the column length is less or equal to 2 ^ 24 - 1 = 16777215.
[19] Chosen if the column length is less or equal to 2 ^ 32 - 1 = 4294967295 or empty.
[20] Chosen if the column definition does not contain the jsonb option inside the platformOptions attribute array or is set to false.
[21] Chosen if the column definition contains the jsonb option inside the platformOptions attribute array and is set to true.
Andrei Pall

Linux Software Engineering

Doctrine Mapping Matrix

Doctrine Mapping Matrix

Related Posts
