Всем привет! Сегодня у нас на повестке дня работа с SQL-запросами, базами данных, какие есть варианты и как вообще правильно с ними работать в рамках BitrixFramework.

Разберем основы конфигурации, как подключать несколько БД на один проект, делать безопасные запросы и не тревожиться на счет инъекций.

Не стоит пугаться AI-шной картинки, это то как искусственный интеллект видит ER диаграмму. Материал писался исключительно белковой нейронкой ;-)

Конфигурируем БД

Первое, с чего начинаем, это конфигурация. В момент установки БУС-ика или Б24 мастер настройки у вас все спросит и сам пропишет нужные данные в файл конфигурации /bitrix/.settings.php. Посмотрим, что находится в секции connections:

return [
	// ...
	'connections' => [
		'value' => [
			'default' => [
				'className' => \Bitrix\Main\DB\MysqliConnection::class,
				'host' => 'localhost',
				'database' => 'busik',
				'login' => 'db_user',
				'password' => '***',
				'options' => 2,
			],
		],
		'readonly' => true,
	],
];

Ключ className задаёт класс соединения, который будет создаваться, а далее весь массив настроек передаётся в конструктор соответствующего класса.

Продукт гарантирует корректную работу для СУБД:

• Bitrix\Main\DB\MysqliConnection

• Bitrix\Main\DB\PgsqlConnection

В зависимости от ваших потребностей можно использовать также движки:

• Bitrix\Main\DB\MssqlConnection

• Bitrix\Main\DB\OracleConnection

И несколько key-value движков:

• Bitrix\Main\Data\HsphpReadConnection

• Bitrix\Main\Data\MemcacheConnection

• Bitrix\Main\Data\MemcachedConnection

• Bitrix\Main\Data\RedisConnection

Полный список доступных параметров можно посмотреть в самих классах различных движков и в документации: https://dev.1c-bitrix.ru/learning/course/index.php?COURSE_ID=43&LESSON_ID=2795#connections

Еще одно подключение к БД

При необходимости, а также непреодолимом желании, можно добавлять несколько соединений с БД. Для этого достаточно дополнить секцию connections в файле конфигурации bitrix/.settings.php:

return [
	// ...
	'connections' => [
		'value' => [
			'default' => [
				'className' => \Bitrix\Main\DB\MysqliConnection::class,
				'host' => 'localhost',
				'database' => 'busik',
				'login' => 'db_user',
				'password' => '***',
				'options' => 2,
			],
			'redis' => [
				'className' => \Bitrix\Main\Data\RedisConnection::class,
				'host' => 'rediska',
				'port' => '12345',
			],
		],
		'readonly' => true,
	],
];

Далее получить объект соединения можно через фасад приложения:

// по умолчанию `default`
$db = \Bitrix\Main\Application::getConnection();

// обращаемся по имени
$db = \Bitrix\Main\Application::getConnection('default');
$redis = \Bitrix\Main\Application::getConnection('redis');

Connection

После того как БД сконфигурирована, рассмотрим варианты взаимодействия с объектом соединения.

/**
 * @var \Bitrix\Main\DB\Connection $db
 */
$db = \Bitrix\Main\Application::getConnection();

/**
 * Простой запрос
 */
$resultIterator = $db->query('SELECT `ID`, `NAME` FROM b_user');

/**
 * Запрос с лимитом, в итоге выполнится запрос: SELECT `ID`, `NAME` FROM b_user LIMIT 0,10
 */
$resultIterator = $db->query('SELECT `ID`, `NAME` FROM b_user', 10);

/**
 * Запрос с лимитом и отступом, в итоге выполнится запрос: SELECT `ID`, `NAME` FROM b_user LIMIT 5,10
 */
$resultIterator = $db->query('SELECT `ID`, `NAME` FROM b_user', 5, 10);

/**
 * Итератор по результатам запроса
 */
foreach ($resultIterator as $row)
{
	$id = (int)$row['ID'];
	$name = (int)$row['NAME'];

	// ...
}

/**
 * Получаем значение первого столбца в выборке, в итоге выполниться запрос: SELECT `ID`, `NAME` FROM b_user LIMIT 0,1
 * Конструкция LIMIT добавится в запрос, а вот секция SELECT не модифицируется!
 */
$id = $db->queryScalar('SELECT `ID`, `NAME` FROM b_user');

/**
 * Выполнение запроса без получения результата, актуально для C*UD запросов
 */
$db->queryExecute('UPDATE b_user SET ACTIVE = "Y" WHERE DATE_REGISTER > "2024-01-01"');

ВАЖНО: методы query, queryScalar и queryExecute принимают аргумент binds, но этот аргумент не относится к подготовке запроса и защите от SQL-инъекции, как например PDOStatement::execute! Данный аргумент нужен для трекинга запросов, об этом поговорим далее. Для защиты от SQL инъекций нужно использовать класс SqlExpression или SqlHelper, об этом также поговорим далее.

Для выполнения INSERT запросов есть специальные методы, которые уже подготавливают значения перед выполнением запроса и позволяют избежать SQL-инъекций:

/**
 * @var \Bitrix\Main\DB\Connection $db
 */
$db = \Bitrix\Main\Application::getConnection();

/**
 * Единичное добавление, в итоге выполнится запрос:
 * INSERT INTO `my_table`(`NAME`, `CONTENT`) VALUES ('habr', 'про \" базы \'')
 */
$insertedId = $db->add('my_table', [
    'NAME' => 'habr',
    'CONTENT' => 'про " базы \'',
]);

/**
 * Множественное добавления строк, в итоге выполнится запрос:
 * INSERT INTO `my_table` (`NAME`, `CONTENT`) VALUES ('habr one', 'про \" базы \''), ('habr two', '\';SELECT * FROM b_user WHERE ID = 1')
 */
$lastInsertedId = $db->addMulti('my_table', [
    [
        'NAME' => 'habr one',
        'CONTENT' => 'про " базы \'',
    ],
    [
        'NAME' => 'habr two',
        'CONTENT' => "';SELECT * FROM b_user WHERE ID = 1",
    ],
]);

Помимо преобразования значений, методы add и addMulti также проверяют столбцы таблицы и исключают из запроса те, которых не существует:

/**
 * В итоге выполниться запрос:
 * INSERT INTO `b_user`(`NAME`) VALUES ('habr')
 */
$insertedId = $db->add('b_user', [
    'NAME' => 'habr',
    'NOT_EXISTS_COLUMN' => 'что я тут делаю?',
]);

Побочный эффект таких проверок: перед запросом на добавление первоначально выполняется запрос на чтение SELECT * FROM ... LIMIT 0. В коде данной логики используется статический кеш, поэтому запрос столбцов выполняется 1 раз за хит.

Стоит упомянуть что классы-наследники Bitrix\Main\DB\Connection поддерживают также DDL-методы. Подробно останавливаться на них не будем, т.к. сами по себе методы простые и используются редко, поэтому ограничимся лишь перечислением доступных методов:

• createTable

• createIndex

• createPrimaryIndex

• truncateTable

• renameTable

• dropColumn

• dropTable

SqlTracker

Для отладки SQL-запросов можно использовать Bitrix\Main\Diag\SqlTracker, внутри себя он будет собирать тайминги, трейс и дополнительную сопутствующую информацию.

Изолировано для конкретного куска кода использовать трекинг можно так:

<?php

/**
 * Начинаем и сбрасываем отладку (если вдруг она была начата до этого)
 */
$tracker = \Bitrix\Main\Application::getConnection()->startTracker(true);

/**
 * Исследуемый код
 */
$USER->Update(1, [
    'PHONE_NUMBER' => '+7-900-000-00-00',
]);

/**
 * Результаты
 */
foreach ($tracker->getQueries() as $query)
{
    print_r([
        // выполненный запрос
        $query->getSql(),
        // время выполнения
        $query->getTime(),
        // стэк до места выполнения запроса
        $query->getTrace(),
        // значение глобальной переменной BX_STATE (не используется в новом коде)
        $query->getState(),
        // ид ноды в случае работы БД в кластере
        $query->getNode(),
        // содержимое аргумента $binds передаваемое в методах Connection::query*
        $query->getBinds(),
    ]);
}

Result

При выполнении запросов на чтение (в том числе и через ORM), возвращает объект результата запроса \Bitrix\Main\DB\Result. Это еще не сами данные, поэтому разберемся как их читать.

Самое простое, это использовать объект как итератор:

$resultIterator = \Bitrix\Main\Application::getConnection()->query('SELECT * FROM b_user');

/**
 * Работаем как с итератором
 */
foreach ($resultIterator as $row)
{
	$id = $row['ID'];
}

/**
 * Равносильно записи выше
 */
while ($row = $resultIterator->fetch())
{
	$id = $row['ID'];
}

Метод fetch под капотом использует ряд модификаций и преобразований, чтобы удобнее было работать с данными. Если по каким-то причинам нужно получить сырые данные, сделать это можно с помощью метода fetchRaw.

Рассмотрим пример, чтобы понять разницу:

$resultIterator = \Bitrix\Main\Application::getConnection()->query('SELECT ID, ACTIVE, DATE_REGISTER FROM b_user');

while ($row = $resultIterator->fetch())
{
	/**
	 * [ID] => 1
	 * [ACTIVE] => Y
	 * [DATE_REGISTER] => Bitrix\Main\Type\DateTime Object
	 */
    print_r($row);
	break;
}

while ($row = $resultIterator->fetchRaw())
{
	/**
	 * [ID] => 2
	 * [ACTIVE] => Y
	 * [DATE_REGISTER] => 2021-12-29 09:50:14
	 */
    print_r($row);
	break;
}

Столбец DATE_REGISTER был преобразован в объект Bitrix\Main\Type\DateTime, т.к. перед выполнением чтения, объект запроса обратился к хелперу и получил список необходимых конвертеров (эту механику можно подглядеть в Bitrix\Main\DB\Result::__construct).

При необходимости, и непреодолимом желании, можно добавить свои конвертеры столбцов (column converters) и модификаторы выборки (fetch modifiers):

$resultIterator = \Bitrix\Main\Application::getConnection()->query('SELECT ID, ACTIVE, DATE_REGISTER FROM b_user');

/**
 * Конвертор работает только с одним столбцом
 */
$resultIterator->setConverters([
    'DATE_REGISTER' => static fn($value) => $value ? strtotime($value) : null,
]);

/**
 * Модификатор работает со строкой в целом
 */
$resultIterator->addFetchDataModifier(static function(array $row) {
    $row['ACTIVE_BOOL'] = $row['ACTIVE'] === 'Y';

    return $row;
});

foreach ($resultIterator as $row)
{
	/**
	 * [ID] => 1
	 * [ACTIVE] => Y
	 * [DATE_REGISTER] => 1640771366
	 * [ACTIVE_BOOL] => true
	 */
    print_r($row);
	break;
}

Также есть ряд вспомогательных методов:

/**
 * Кол-во строк в запросе
 */
$count = $resultIterator->getSelectedRowsCount();

/**
 * Объект драйвера
 * В случае с Mysql будет экземпляр класса \mysqli_result
 */
$dbResource = $resultIterator->getResource();

/**
 * Список выбранных столбцов
 */
$selectedFields = $resultIterator->getFields();

SqlHelper

Вся логика обработки SQL перед запросами лежит на классе Bitrix\Main\DB\SqlHelper, а точнее его конкретных реализациях для работы с конкретными базами. Работы с данным хелпером спрятана внутрь ORM и Connection классов, но при необходимости можно обратиться к нему напрямую.

Самые важные методы связаны непосредственно с безопасностью и экранированием:

/**
 * Хелпер, адаптированый под конкретную базу
 */
$helper = \Bitrix\Main\Application::getConnection()->getSqlHelper();

/**
 * Экранирование столбцов
 */
$helper->quote('id'); // `id`
$helper->quote('table_name.id'); // `table_name`.`id`
$helper->quote('не ` безопасная " строка'); // `не  безопасная " строка`

/**
 * Экранирование значения
 */
$safeValue = $helper->forSql('не " безопасная \' строка'); // не \" безопасная \' строка

/**
 * Конвертация значения пригодного для использования в SQL-запросе.
 * Отличие от `forSql` тут уже происходит обрамление строки в кавычки, для простого использования в запросе
 */
$safeSql = $helper->convertToDb('не " безопасная \' строка'); // 'не \" безопасная \' строка'
$safeSql = $helper->convertToDb(null); // 'NULL'
$safeSql = $helper->convertToDb(123); // '123'

/**
 * В случае если мы передаём второй аргумент `field`, то экранирование происходит уже с учетом типа указанного поля.
 * При обычной работе с хелпером, так делать не стоит, а лучше использовать конкретные методы `convertToDb*`
 * 
 * Пример работы экранирования с указанием поля можно подсмотреть в методе `Bitrix\Main\DB\SqlHelper::prepareInsert`
 */
$safeSql = $helper->convertToDb('строка', new \Bitrix\Main\ORM\Fields\FloatField('column_name')); // 0
$safeSql = $helper->convertToDb(123, new \Bitrix\Main\ORM\Fields\TextField('column_name')); // '123'

/**
 * Конвертация значения в строку пригодного для использования в SQL-запросе.
 * Отличие от `convertToDb` в обработке значения NULL, в данном случае он приводится к пустой строке!
 */
$safeSql = $helper->convertToDbString('строка'); // 'строка'
$safeSql = $helper->convertToDbString(null); // ''
$safeSql = $helper->convertToDbString(123); // '123'

/**
 * Вторым аргументом можно указать длину строки (чаще всего это размер столбца), тогда значение будет обрезано до нужного размера
 */
$safeSql = $helper->convertToDbString('Длинная и очень важная строка', 10); // 'Длинная и '

/**
 * Конвертация значения в строку для текстовых столбцов SQL
 * В отличие от `convertToDbString` не принимает второй аргумент `length`
 */
$safeSql = $helper->convertToDbText('строка'); // 'строка'
$safeSql = $helper->convertToDbText(null); // ''
$safeSql = $helper->convertToDbText(123); // '123'

/**
 * Конвертация бинарных данных пригодных для использования в SQL-запросе.
 */
$binaryData = base64_decode('iVBORw0KGgoAAA==');
$safeSql = $helper->convertToDbBinary($binaryData); // '�PNG\r\n\Z\n\0\0'

/**
 * Конвертация значения в целое число пригодного для использования в SQL-запросе.
 */
$safeSql = $helper->convertToDbInteger('строка'); // 0
$safeSql = $helper->convertToDbInteger(null); // 0
$safeSql = $helper->convertToDbInteger(123); // 123
$safeSql = $helper->convertToDbInteger(123.456); // 123

/**
 * В случае указания размера (аналогично бд в байтах), происходит проверка максимального и минимального значения.
 */
$safeSql = $helper->convertToDbInteger(10_000_000_000, 2); // 32767
$safeSql = $helper->convertToDbInteger(10_000_000_000, 4); // 2147483647
$safeSql = $helper->convertToDbInteger(10_000_000_000); // 10000000000

$safeSql = $helper->convertToDbInteger(-10_000_000_000, 2); // -32767
$safeSql = $helper->convertToDbInteger(-10_000_000_000, 4); // -2147483647
$safeSql = $helper->convertToDbInteger(-10_000_000_000); // -10000000000

/**
 * Конвертация значения в число с плавающей точкой пригодного для использования в SQL-запросе.
 */
$safeSql = $helper->convertToDbFloat('строка'); // '0'
$safeSql = $helper->convertToDbFloat(null); // '0'
$safeSql = $helper->convertToDbFloat(123); // '123'
$safeSql = $helper->convertToDbFloat(123.456); // '123.456'

/**
 * В случае если указывается второй аргумент `scale`, то число округляется до необходимой точности
 */
$safeSql = $helper->convertToDbFloat(123.456789, 0); // '123'
$safeSql = $helper->convertToDbFloat(123.456789, 1); // '123.5'
$safeSql = $helper->convertToDbFloat(123.456789, 11); // '123.456789'

/**
 * Конвертация значения в дату пригодного для использования в SQL-запросе.
 * 
 * Принимает метод либо null, либо экземпляр класса `Bitrix\Main\Type\Date` и его наследников
 * В ином случае будет выброшено исключение Bitrix\Main\ArgumentTypeException
 */
$safeSql = $helper->convertToDbDate(null); // NULL
$safeSql = $helper->convertToDbDate(new \Bitrix\Main\Type\Date('01.01.2015')); // '2015-01-01'
$safeSql = $helper->convertToDbDate(new \Bitrix\Main\Type\DateTime('01.01.2015 01:23:45')); // '2015-01-01'

/**
 * Конвертация значения в дату пригодного для использования в SQL-запросе.
 * 
 * Принимает метод либо null, либо экземпляр класса `Bitrix\Main\Type\Date` и его наследников
 * В ином случае будет выброшено исключение Bitrix\Main\ArgumentTypeException
 */
$safeSql = $helper->convertToDbDateTime(null); // NULL
$safeSql = $helper->convertToDbDateTime(new \Bitrix\Main\Type\Date('01.01.2015')); // '2015-01-01 00:00:00'
$safeSql = $helper->convertToDbDateTime(new \Bitrix\Main\Type\DateTime('01.01.2015 01:23:45')); // '2015-01-01 01:23:45'

Есть ряд методов для работы с датами:

/**
 * Получаем формат даты, корректный для текущей БД
 */
$helper->formatDate('DD.MM.YYYY HH:MI'); // %d.%m.%Y %H:%i

/**
 * Получаем функцию преобразования столбца в конкретный формат
 */
$helper->formatDate('DD.MM.YYYY HH:MI', $helper->quote('column_name')); // DATE_FORMAT(`column_name`, '%d.%m.%Y %H:%i')
$helper->formatDate('DD.MM.YYYY HH:MI', $helper->convertToDb('2024-01-01')); // DATE_FORMAT('2024-01-01', '%d.%m.%Y %H:%i')

/**
 * Добавить секунды к указанной дате
 */
$helper->addSecondsToDateTime(60); // DATE_ADD(NOW(), INTERVAL 60 SECOND)
$helper->addSecondsToDateTime(60, $helper->quote('column')); // DATE_ADD(`column`, INTERVAL 60 SECOND)
$helper->addSecondsToDateTime(60, $helper->convertToDb('2024-01-01')); // DATE_ADD('2024-01-01', INTERVAL 60 SECOND)

/**
 * Добавить дни к указанной дате
 */
$helper->addDaysToDateTime(60); // DATE_ADD(NOW(), INTERVAL 60 DAY)
$helper->addDaysToDateTime(60, $helper->quote('column')); // DATE_ADD(`column`, INTERVAL 60 DAY)
$helper->addDaysToDateTime(60, $helper->convertToDb('2024-01-01')); // DATE_ADD('2024-01-01', INTERVAL 60 DAY)

И для работы с SQL-функциями:

/**
 * Функции текущей даты и времени
 */
$helper->getCurrentDateFunction(); // CURDATE()
$helper->getCurrentDateTimeFunction(); // NOW()

$helper->getDatetimeToDateFunction($helper->quote('column_name')); // DATE(`column_name`)
$helper->getDatetimeToDateFunction($helper->convertToDb('2024-01-01')); // DATE('2024-01-01')

/**
 * Методы ниже для MySQL не производит никаких преобразований, т.к. она и так работает :)
 * Оба примера ниже приведены для PgSQL для наглядности преобразований:
 */
$helper->getCharToDateFunction(date('Y-m-d H:i:s')); // timestamp '2024-01-01 00:00:00'
$helper->getDateToCharFunction($helper->quote('column_name')); // TO_CHAR([column_name], 'YYYY-MM-DD HH24:MI:SS')

/**
 * Функция подстроки
 */
$helper->getSubstrFunction($helper->quote('column_name'), 1); // SUBSTR(`column_name`, 1)
$helper->getSubstrFunction($helper->quote('column_name'), 1, 10); // SUBSTR(`column_name`, 1, 10)

/**
 * Функция конкатенации (принимает неограниченное число аргументов)
 */
$helper->getConcatFunction(); // пустая строка ;)
$helper->getConcatFunction(1, 2, 3); // CONCAT(1, 2, 3)
$helper->getConcatFunction(
    $helper->quote('column_name'),
    $helper->convertToDb('delimiter'),
    $helper->quote('another_column'),
); // CONCAT(`column_name`, 'delimiter', `another_column`)

/**
 * Проверка на NULL
 */
$helper->getIsNullFunction($helper->quote('column_name'), 1); // IFNULL(`column_name`, 1)
$helper->getIsNullFunction($helper->quote('column_name'), $helper->convertToDb('value')); // IFNULL(`column_name`, 'value')

/**
 * Длинна строки
 */
$helper->getLengthFunction($helper->quote('column_name')); // LENGTH(`column_name`)

/**
 * Рандом
 */
$helper->getRandomFunction(); // rand()

/**
 * Хеширование
 */
$helper->getSha1Function($helper->quote('column_name')); // sha1(`column_name`)

/**
 * Полнотекстовый поиск
 */
$helper->getMatchFunction($helper->quote('column_name'), $helper->convertToDb('value')); // MATCH (`column_name`) AGAINST ('value' IN BOOLEAN MODE)

ВАЖНО: методы, описанные выше (и даты и функции) используют аргументы как есть, поэтому их необходимо экранировать вызывающему коду. Необходимо это для возможности указывать целые SQL-конструкции в той или иной функции.

Также в хелпере есть ряд методов для формирования запросов с префиксом prepare. Так или иначе эти методы используют друг друга, детально рассмотрим методы для мерджинга:

/**
 * Пытаемся добавить новую запись, но в случае конфликтов по `primaryFields` выполняем обновление указанных полей.
 * ВАЖНО: в данном случае MySQL никак не использует в запросе `primaryFields`, но подразумевается, что на указанные поля добавлен UNIQUE INDEX.
 *
 * В итоге получим такой запрос:
 *      INSERT INTO `b_user_counter` (`USER_ID`, `SITE_ID`, `CODE`, `CNT`)
 *      VALUES (1, 's1', 'counter_name', 10)
 *      ON DUPLICATE KEY UPDATE `CNT` = `CNT` + 10
 */
[ $sql ] = $helper->prepareMerge(
    tableName: 'b_user_counter',
    primaryFields: [
        'USER_ID',
        'SITE_ID',
        'CODE',
    ],
    insertFields: [
        'USER_ID' => 1,
        'SITE_ID' => 's1',
        'CODE' => 'counter_name',
        'CNT' => 10,
    ],
    updateFields: [
        'CNT' => new \Bitrix\Main\DB\SqlExpression('?# + ?i', 'CNT', 10),
    ],
);

/**
 * Аналогично пытаемся добавить новые записи, но данные берем из списка.
 *
 * В итоге получим такой запрос:
 *      INSERT INTO `b_user_counter` (`USER_ID`,`SITE_ID`,`CODE`,`CNT`)
 *      values (1, 's1', 'counter_name', 1),(2, 's1', 'counter_name', 1),(2, 's1', 'another_counter', 1)
 *      ON DUPLICATE KEY UPDATE `CNT` = `CNT` + 1"
 */
$sql = $helper->prepareMergeValues(
    tableName: 'b_user_counter',
    primaryFields: [
        'USER_ID',
        'SITE_ID',
        'CODE',
    ],
    insertRows: [
        [
            'USER_ID' => 1,
            'SITE_ID' => 's1',
            'CODE' => 'counter_name',
            'CNT' => 1,
        ],
        [
            'USER_ID' => 2,
            'SITE_ID' => 's1',
            'CODE' => 'counter_name',
            'CNT' => 1,
        ],
        [
            'USER_ID' => 2,
            'SITE_ID' => 's1',
            'CODE' => 'another_counter',
            'CNT' => 1,
        ],
    ],
    updateFields: [
        'CNT' => new \Bitrix\Main\DB\SqlExpression('?# + ?i', 'CNT', 1),
    ],
);

/**
 * Аналогично пытаемся добавить новую запись, но данные берем из подзапроса.
 *
 * В итоге получим такой запрос:
 *      INSERT INTO `b_user_counter` (`USER_ID`,`SITE_ID`,`CODE`,`CNT`)
 *      (SELECT * FROM my_counters)
 *      ON DUPLICATE KEY UPDATE `CNT` = `CNT` + 10
 */
$sql = $helper->prepareMergeSelect(
    tableName: 'b_user_counter',
    primaryFields: [
        'USER_ID',
        'SITE_ID',
        'CODE',
    ],
    selectFields: [
        'USER_ID',
        'SITE_ID',
        'CODE',
        'CNT',
    ],
    select: '(SELECT * FROM my_counters)',
    updateFields: [
        'CNT' => new \Bitrix\Main\DB\SqlExpression('?# + ?i', 'CNT', 10),
    ],
);

/**
 * Пытаемся добавить новую запись, но в случае конфликтов заменяем её на указанную.
 * В отличие от предыдущих методов, тут записи в случае конфликтов просто переписываются, без возможности гибкого апдейта
 *
 * В итоге получим такой запрос:
 *      REPLACE INTO `b_user_counter` (`USER_ID`, `SITE_ID`, `CODE`, `CNT`)
 *      VALUES (1, 's1', 'counter_name', 5), (2, 's1', 'counter_name', 10), (2, 's1', 'another_counter', 15)
 */
$sqlQueries = $helper->prepareMergeMultiple(
    tableName: 'b_user_counter',
    primaryFields: [
        'USER_ID',
        'SITE_ID',
        'CODE',
    ],
    insertRows: [
        [
            'USER_ID' => 1,
            'SITE_ID' => 's1',
            'CODE' => 'counter_name',
            'CNT' => 5,
        ],
        [
            'USER_ID' => 2,
            'SITE_ID' => 's1',
            'CODE' => 'counter_name',
            'CNT' => 10,
        ],
        [
            'USER_ID' => 2,
            'SITE_ID' => 's1',
            'CODE' => 'another_counter',
            'CNT' => 15,
        ],
    ],
);

/**
 * В ответ мы получаем массив запросов, т.к. в случае превышения максимального размера, хелпер сам разделит запрос на части.
 */
foreach ($sqlQueries as $sql)
{
    $db->query($sql);
}

Хелпер также содержит множество технических методов по типу конвертации значения из/в тип базы данных (см. пачку запросов с префиксом convert*). Использовать их в клиентском коде вам скорее всего не придётся, т.к. лучше использовать высокоуровневые инструменты: SqlExpression и ORM.

SqlExpression

Для более удобной работы с хелпером и в целом работы с запросами, существует класс Bitrix\Main\DB\SqlExpression. Механика максимально проста: в SQL-запрос выставляются плейсхолдеры, которые при формировании SQL экранируются.

Поддерживаются следующие преобразования:

1. ? - преобразование либо к строке, либо к дате (наглядно на примерах);

2. ?s - преобразование к строке;

3. ?i - преобразование к целому числу;

4. ?f - преобразование к дробному числу;

5. ?# - экранированием имён столбцов

Всевозможные комбинации рассмотрим сразу на примерах:

/**
 * Создаем новый объект и можем использовать его сразу в запросе
 */
$sql = new SqlExpression('SELECT * FROM b_user');
$result = Application::getConnection()->query($sql);

/**
 * Получить SQL-запрос можно двумя способами
 */
echo $sql->compile();
// равносильно
echo (string)$sql;

/**
 * Плейсхолдер внутри проверяет тип значения и выполняет необходимые преобразования
 * Ниже представлен список поддерживаемых плейсхолдеров.
 *
 * В итоге получим запрос:
 *      SELECT * FROM `b_user` WHERE (ID = 1 OR ID > 1.23) AND `NAME` = 'admin' AND DATE_REGISTER > '2024-01-01'
 */
$sql = new SqlExpression(
    'SELECT * FROM ?# WHERE (ID = ?i OR ID > ?f) AND `NAME` = ?s AND DATE_REGISTER > ?',
    'b_user',
    1.23,
	1.23,
    'admin',
	new \Bitrix\Main\Type\Date('01.01.2024'),
);

/**
 * В случае, если мы указываем NULL значение, все плейсхолдеры кроме ?# преобразуются к NULL значению.
 *
 * В итоге получим запрос:
 *      SELECT * FROM `` WHERE ID = NULL OR NAME = NULL
 */
$sql = new SqlExpression(
    'SELECT * FROM ?# WHERE ID = ?i OR NAME = ?',
    null,
    null,
    null,
);

/**
 * Для преобразования дат используется базовый плейсхолдер ?
 * В случае если по каким-то причинам нам нужно именно строковое представление даты, то нужно использовать модификатор ?s
 *
 * В итоге получим запрос:
 *      WHERE (DATE = '2024-01-01' OR DATE_TIME = '2024-01-01 00:00:00')
 *      AND (DATE = '01.01.2024' OR DATE_TIME = '01.01.2024 00:00:00')
 */
$sql = new SqlExpression(
    '
        WHERE (DATE = ? OR DATE_TIME = ?)
        AND (DATE = ?s OR DATE_TIME = ?s)
    ',
    new \Bitrix\Main\Type\Date('01.01.2024'),
    new \Bitrix\Main\Type\DateTime('01.01.2024'),
    new \Bitrix\Main\Type\Date('01.01.2024'),
    new \Bitrix\Main\Type\DateTime('01.01.2024'),
);

Помимо формирования запросов, SqlExpression можно также использовать в фильтрах ORM: https://dev.1c-bitrix.ru/learning/course/index.php?COURSE_ID=43&LESSON_ID=2244#ExpressionField

Транзакции

Для работы с транзакциями нужно использовать объект подключения Bitrix\Main\DB\Connection, причем внутри транзакции можно использовать как запросы с помощью Connection::query ,так и ORM-сущности, потому что внутри они также сваливаются к работе с тем же самым Connection::query.

$db = \Bitrix\Main\Application::getConnection();

try
{
    $db->startTransaction();

    $db->queryExecute('UPDATE my_table SET active = "N" WHERE age > 0');

    \Bitrix\Main\SiteTable::update('s1', [
        'ACTIVE' => 'N',
    ]);

    $db->commitTransaction();
}
catch (Throwable $e)
{
    $db->rollbackTransaction();

    throw $e;
}

ВАЖНО: таблеты ORM могут использовать другое соединение, переопределенное через метод DataManager::getConnectionName. В данном случае транзакция открывается в рамках конкретной БД.

Помимо обычных транзакций, поддерживаются также и вложенные транзакции. Механика работы точно такая же, но есть ньюанс решения проблемы частичного отката:

use Bitrix\Main\Application;
use Bitrix\Main\DB\Connection;
use Bitrix\Main\DB\SqlExpression;
use Bitrix\Main\DB\TransactionException;

function updateAccounts(int $userId, Connection $db)
{
    try
    {
        $db->startTransaction();

        // DataManager::update

        $db->commitTransaction();
    }
    catch (Throwable $e)
    {
        $db->rollbackTransaction();

        throw $e;
    }
}

function updateOrders(int $userId, Connection $db)
{
    try
    {
        $db->startTransaction();

        // Connection::queryExecute

        $db->commitTransaction();
    }
    catch (Throwable $e)
    {
        $db->rollbackTransaction();

        throw $e;
    }
}

$db = Application::getConnection();

try
{
    $db->startTransaction();

    updateOrders($userId, $db);
    updateAccounts($userId, $db);

    $db->commitTransaction();
}
catch (TransactionException $e)
{
    /**
     * Тут нам нужно решить, что делать с упавшей вложенной транзакцией.
     * Скорее всего, вам нужно откатить всю транзакция целиком и вызывать очередной ROLLBACK
     */
    $db->rollbackTransaction();
}
catch (Throwable $e)
{
    $db->rollbackTransaction();

    throw $e;
}

ORM

По работе с ORM у нас есть достаточно полная документация, поэтому переписывать её смысла не вижу, просто оставлю ссылку на неё. В случае если у вас есть вопросы/темы касаемо ORM, добро пожаловать в комментарии, готов их обсудить ;-)

Полезные ссылки

Собрал различные полезности по теме:

• https://dev.1c-bitrix.ru/learning/course/index.php?COURSE_ID=43&LESSON_ID=2795#connections

• https://dev.1c-bitrix.ru/learning/course/index.php?COURSE_ID=105&CHAPTER_ID=05748&LESSON_PATH=9209.5748

• https://dev.1c-bitrix.ru/learning/course/index.php?COURSE_ID=105&CHAPTER_ID=02379&LESSON_PATH=9209.2379

• https://dev.1c-bitrix.ru/learning/course/index.php?LESSON_ID=14380&COURSE_ID=105