Doctrine and Database Management Systems

There are many database management systems (DBMS) on the market. Those systems can be divided into two groups: traditional relational databases utilizing SQL language for querying and manipulating data, and NoSQL databases utilizing "not only SQL" methods for accessing and managing the data. In each particular project you may prefer certain DBMS because of its capabilities and competitive advantages.

Relational Databases

In a relational database, you have a collection of tables (relations) consisting of rows. A row may have one or several columns. A row (or several rows) of a table may be linked to a row (or several rows) of another table, thus forming a relationship between data.

For example, assume you have a blog website whose database contains two tables: the post table and the comment table. The post table would have columns named id, title, content, author, date_created; and the comment table would have columns named id, post_id, author, content, and date_created. The post table is related to comment table as one-to-many, because one post has zero or more (many) comments, while a certain comment may belong to a single post only.

Graphically, the above mentioned tables, their columns and relationship are shown in figure D.1 below.

Figure D.1. Tables and relationship between tables. Single post has many comments

On the market, there is a number of major relational databases. Among them: SQLite, MySQL, PostgreSQL, Oracle, Microsoft SQL Server, etc.

Each database system has its own features specific to that DBMS and which are not part of other systems. For example:

SQLite is designed as an embed extension of PHP engine and doesn't require installation, however it works well for simple sites only;
MySQL is a free system which is very simple in installation and administration and good for using in systems varying from small to middle scale;
Commercial Oracle DBMS is mainly targeted on large-scale systems and has sophisticated administrative tools;
PostgreSQL supports indefinitely large databases and can be considered as an open-source replacement of Oracle.

Doctrine library is designed to work with all major databases using a unified programming interface. This programming interface is implemented in two levels:

At the lower level, Doctrine provides the unified mechanism for building SQL queries to any supported relational database and manipulating database schema. This mechanism is implemented in the Database Abstraction Layer (DBAL) component.
At the higher level, the Object Relational Mapper (ORM) component of Doctrine provides an ability to query and manage database data in object-oriented way, by mapping the tables to PHP classes. This component also provides its custom database query language called DQL allowing to build queries in object-oriented style.

Typically, you use the API provided by high-level ORM component. At the same time, you can easily work with lower-level DBAL component, if you find that more suitable for your particular needs.

Doctrine is database-agnostic. In theory, when you use Doctrine you are able to abstract of database type and switch between databases more easily than when you use your database-dependent solution.

SQL vs. DQL

When using a relational database system, you typically use SQL language as a standard way for accessing database data and managing database schema. However, each DBMS usually has it own specific SQL language extensions (dialects).

Doctrine library is designed to work with all major relational database systems that use SQL language, but it is obvious that it supports only some subset of their functionality and SQL language capabilities.

Doctrine is built on top of PHP PDO extension (and other database-specific PHP extensions, like sqlite, mysqli, oci8, etc.). Those extensions provide drivers for all major relational database systems. You specify which driver to use when configuring a database connection.

If you are not familiar with SQL, a good point for learning its syntax is W3Schools Tutorials.

⁵³⁾ The PHP Data Objects (PDO) extension defines a lightweight, consistent interface for accessing databases in PHP. To make it in database-agnostic way, PDO uses the concept of database drivers. Each database driver that implements the PDO interface can expose database-specific features as regular extension functions.

Since the Object Relational Mapper component of Doctrine is designed to work with objects instead of tables, it provides its own "object-oriented" query language called DQL ⁵⁴. It is similar to SQL in sense that it allows to write and execute queries to database, but result of a query is an array of objects rather than an array of table rows.

⁵⁴⁾ DQL stands for Doctrine Query Language.

NoSQL Databases

In contrast to a relational database system, a NoSQL database system - as its name assumes - uses a not-only-SQL method of accessing the data. This means that each NoSQL system may provide its own custom methods and API for accessing and manipulating the data. Technically, NoSQL databases can be divided in the following groups:

Document Store. A document database operates the concept of documents and their fields. This is useful, for example, if you have an hierarchical document tree in a content management (CMS) system. Documents are addressed in the database via a unique key that represents that document. One of the other defining characteristics of a document-oriented database is that, beyond the simple key-document lookup that you can use to retrieve a document, the database will offer an API or query language that will allow retrieval of documents based on their contents.
Column Store. Frequently used in web indexing. A column-oriented DBMS is a database management system that stores data tables as sections of columns of data rather than as rows of data. In comparison, most relational DBMSs store data in rows. This column-oriented DBMS has advantages for data warehouses, customer relationship management (CRM) systems, and library card catalogues, and other ad hoc inquiry systems where aggregates are computed over large numbers of similar data items.
Key-Value Store. This is the simplest data storage using unique keys for accessing certain data. Such database systems provide a simple key-value lookup mechanism.
and others.

Doctrine provides support only to the Document Store subset of the NoSQL database systems. Column store and key-value store database systems typically have very specific field of applications, and not covered by Doctrine.

Document Databases

Doctrine supports a number of NoSQL document store databases: MongoDB, CouchDB, OrientDB and PHPCR.

For example, in a blog website, you would have a document named post and a document named comment. The post document would have fields named id, title, content, author, date_created; and the comment document would have fields named id, author, content and date_created. This is very similar to the tables you would have in a relational database.

In this book, we do not address the Doctrine-provided API to the NoSQL document databases. If you want to learn about these capabilities, please refer to the corresponding sections of Doctrine project documentation.