Bitrix Framework

General API classes architecture for handling databases:

• Connection pool Bitrix\Main\Data\ConnectionPool manages connections, with parameters configured in the settings file .settings.php. The pool contains default connection and can have set of additional designated connections. For example, connections to another database.
• Specific connection classes inherit an abstract class Bitrix\Main\DB\Connection. Various types of databases have different specific classes.
• Specific classes generate SQL queries, inheriting Bitrix\Main\DB\SqlHelper. They help generate a query without modifying syntax for specific database.
• Specific classes for handling the query result, inheriting Bitrix\Main\DB\Result.

These classes allow handling databases at the low level. This, however, is necessary in a small amount of cases. It's preferable to work via ORM, that allows to code at the domain logic level.

You can get connection via applications, which will serve as an entry point, among other things. You can get instances of "key" objects from this entry point for this app, necessary for all (or almost all) pages or components of this application.

$connection = Bitrix\Main\Application::getConnection();
$sqlHelper = $connection->getSqlHelper();

$sql = "SELECT ID FROM b_user WHERE LOGIN = '".$sqlHelper->forSql($login, 50)."'";

$recordset = $connection->query($sql);
while ($record = $recordset->fetch())
{
   ***

Accordingly, an application is used to execute a query: standard query, query with limit on records, scalar query or a "custom" query.

$result1 = $connection->query($sql);
$result2 = $connection->query($sql, $limit);
$result3 = $connection->query($sql, $offset, $limit);

$cnt = $connection->queryScalar("SELECT COUNT(ID) FROM table");

$connection->queryExecute("INSERT INTO table (NAME, SORT) VALUES ('Название', 100)")

$connection = Bitrix\Main\Application::getConnection();
$sqlHelper = $connection->getSqlHelper();

$sql = "SELECT ID FROM b_user WHERE LOGIN = '".$sqlHelper->forSql($login, 50)."' ";

$recordset = $connection->query($sql);
while ($record = $recordset->fetch())
{
   ***

Type-safe data is returned immediately as a type, but not as strings or numerals.

Modifying the result:

$connection = \Bitrix\Main\Application::getConnection();
$recordset = $connection->query("select * from b_iblock_element", 10);
$recordset->addFetchDataModifier(
   function ($data)
   {
       $data["NAME"] .= "!";
       return $data;
   }
);

while ($record = $recordset->fetch(\Bitrix\Main\Text\Converter::getHtmlConverter()))
   {
      $data[] = $record;
   }

Result can be modified, immediately apply converter to results and, for example, prepare data for printing in XML, and etc.

When designing your own database, a question arises, which data type to use: datetime or timestamp? When handling a database, converting the time to GMT is a minimal requirement. But, to depend less on server settings, it's better to use datetime. In this case, you will have to get current time value in PHP.

Within ORM you can work with several databases. For this, create several records for handling the database in the file /bitrix/.settings.php section connections:

'default' =>
    array(
        'className' => '\\Bitrix\\Main\\DB\\MysqlConnection',
        'host' => 'localhost', 
        'database' => 'site', 
        'login' => 'user', 
        'password' => 'passwd',
        'options' => 2,
        ),
'old_db' =>
    array(
        'className' => '\\Bitrix\\Main\\DB\\MysqlConnection',
         'host' => 'localhost',
         'database' => 'old_db',
         'login' => 'user',
         'password' => 'passwd',
         'options' => 2,
 ), 

In this manner, there are 2 connected databases: default (by default, Bitrix Framework uses this connection) and old_db (indicates connection parameters to 2nd database). You need to query old_db as follows:

$connection = Bitrix\Main\Application::getConnection('old_db');
$sqlHelper = $connection->getSqlHelper();

$sql = "SELECT ID FROM b_user WHERE LOGIN = '".$sqlHelper->forSql($login, 50)."' ";

$recordset = $connection->query($sql);
while ($record = $recordset->fetch())
{
   *** 

The parameter include_after_connected must indicate path to file to be connected and executed after the first connection with additional database:

'old_db' => array(
  'className' => '\\Bitrix\\Main\\DB\\MysqlConnection',
  'host' => 'localhost',
  'database' => 'old_db',
  'login' => 'user',
  'password' => 'passwd',
  'options' => 2,
  'include_after_connected' => $_SERVER["DOCUMENT_ROOT"].'/local/php_interface/after_connect_d7_old_db.php',
),

The file after_connect_d7_old_db.php

<?
$connectionOld = \Bitrix\Main\Application::getConnection("old_db");
$connectionOld->queryExecute("SET NAMES 'utf8'");
$connectionOld->queryExecute("SET collation_connection = 'utf8_unicode_ci'");
?>

For binding ORM entity to a specific connection, re-define the method Bitrix\Main\ORM\Data\DataManager::getConnectionName() inside your *Table class.

In rare cases, there is a necessity to connect databases that cannot use inbuilt driver for PHP (for example, customer has an extremely outdated (or otherwise, most recent) DMBS version. To add support of the new database type to Bitrix Framework, you need the following:

• Create connection class (descendant Bitrix\Main\DB\Connection). Inside, define all the basic database operations: connection, disconnection, executing arbitrary query, handling transactions.
• Create SQL-helper class (descendant Bitrix\Main\DB\SqlHelper) and return its instance in the method createSqlHelper. Class is designed for the most low-level database work: adding screening, handling dates, granting access to base SQL-functions and etc.
• Create class for retrieval result (descendant Bitrix\Main\DB\Result). Inside, define wrapper methods for traditional functions with the fetch result.