Explain how Drupal database system works

 Database server requirements

Required MySQL 5.5.3/MariaDB 5.5.20/Percona Server 5.5.8 or higher with InnoDB as the primary storage engine, and requires the PDO database extension.

Drupal 9 requires MariaDB 10.3+ or MySQL/Percona 5.7.8+.

Environment requirements of Drupal 9

If you are running Drupal 9 on Nginx, at least version 0.7.x is required.

If you are running Drupal 9 on Apache, at least version 2.4.7 is required.

Drupal 9 requires at least PHP 7.3. PHP 7.4 is also supported but not required. PHP 8 is supported from Drupal 9.1.0

Database backend requirements

If using Drupal 9 with MySQL or Percona, version 5.7.8+ is required.

If using Drupal 9 with MariaDB, version 10.3.7+ is required.

If using Drupal 9 with SQLite, version 3.26+ is required. (PHP 7.4 does not use the system provided SQLite, so take extra care to make sure your PHP is compiled with at least this version).

If using Drupal 9 with PostgreSQL, version 10 is required with the pg_trgm extension. 

Drupal Database

Drupal stores information in a database, each type of information has its own database table. For example the basic information about the nodes of your site are stored in the Node table, and if you use the CCK module to add fields to your content type, the field information is stored in separate tables . Comments and Users also have their own database tables, and roles, permissions, and other settings are also stored in database tables.

Database abstraction layer

Drupal's database abstraction layer provides a unified database query API that can query different underlying databases. It is built upon PHP's PDO (PHP Data Objects) database API, and inherits much of its syntax and semantics.

The Drupal database layer is built on top of the PHP's PDO library. 

Because different databases require different sorts of interaction, the Drupal database layer requires a driver for each database type. A driver consists of a series of files located in includes/database/driver, where driver is a string representing the unique key for that driver. In most cases the driver key is the lowercase version of the database name, such as "mysql", "pgsql", or "mycustomdriver".

Connections

A connection is an object of class DatabaseConnection, which inherits from the PDO class. 

To access (and open if necessary) a connection object, use:

$database = \Drupal::database();

// Or

$database = \Drupal::service('database');

If services are not yet available, \Drupal\Core\Database\Database::getConnection() can get a database connection.

Database Configuration

Database connection is via the $databases array in settings.php.

The $databases array is a nested array of at least three levels. The first level defines the database keys. The second defines the database targets. The value of each target is the connection information for that key/target. Some examples should make that clearer.

$databases['default']['default'] = array(

  'driver' => 'mysql',

  'database' => 'drupaldb',

  'username' => 'username',

  'password' => 'secret',

  'host' => 'localhost',

);

Instantiating a Database Connection Object

Database interaction should be done via a database connection object.

The best way to instantiate a database connection object is via the Service Container.

Example:

$database = \Drupal::database();

// Or

$database = \Drupal::service('database');