База данных: Начало работы

• Введение
  • Настройка
  • Подключения для чтения и записи
• Выполнение SQL-запросов
  • Использование нескольких подключений к базе данных
  • Прослушивание событий запросов
  • Мониторинг суммарного времени запросов
• Транзакции базы данных
• Подключение к CLI базы данных
• Просмотр ваших баз данных
• Мониторинг ваших баз данных

#Введение

Почти каждое современное веб-приложение взаимодействует с базой данных. Laravel упрощает работу с базами данных на множестве поддерживаемых систем, используя сырой SQL, флюентный конструктор запросов и Eloquent ORM. В настоящее время Laravel официально поддерживает пять баз данных:

#Настройка

Конфигурация сервисов базы данных Laravel находится в файле конфигурации вашего приложения config/database.php. В этом файле вы можете определить все подключения к базам данных, а также указать, какое подключение использовать по умолчанию. Большинство параметров конфигурации в этом файле управляются значениями переменных окружения вашего приложения. Примеры для большинства поддерживаемых Laravel систем баз данных приведены в этом файле.

По умолчанию, пример конфигурации окружения Laravel готов к использованию с Laravel Sail — Docker-конфигурацией для разработки Laravel-приложений на локальной машине. Однако вы можете свободно изменять конфигурацию базы данных в соответствии с требованиями вашей локальной базы.

#Конфигурация SQLite

Базы данных SQLite хранятся в одном файле на вашей файловой системе. Вы можете создать новую базу SQLite с помощью команды touch в терминале: touch database/database.sqlite. После создания базы вы можете легко настроить переменные окружения, указав абсолютный путь к базе в переменной DB_DATABASE:

DB_CONNECTION=sqlite
DB_DATABASE=/absolute/path/to/database.sqlite

Чтобы включить ограничение внешних ключей для подключений SQLite, установите переменную окружения DB_FOREIGN_KEYS в значение true:

DB_FOREIGN_KEYS=true

#Конфигурация Microsoft SQL Server

Для использования базы данных Microsoft SQL Server убедитесь, что у вас установлены PHP-расширения sqlsrv и pdo_sqlsrv, а также все необходимые зависимости, например драйвер Microsoft SQL ODBC.

#Конфигурация с использованием URL

Обычно подключения к базе данных настраиваются с помощью нескольких параметров конфигурации, таких как host, database, username, password и т.д. Для каждого из этих параметров существует соответствующая переменная окружения. Это означает, что при настройке подключения к базе на продакшн-сервере вам нужно управлять несколькими переменными окружения.

Некоторые управляемые провайдеры баз данных, такие как AWS и Heroku, предоставляют единый "URL" базы данных, который содержит всю информацию для подключения в одной строке. Пример такого URL может выглядеть примерно так:

mysql://root:password@127.0.0.1/forge?charset=UTF-8

Эти URL обычно следуют стандартной схеме:

driver://username:password@host:port/database?options

Для удобства Laravel поддерживает такие URL как альтернативу настройке базы с помощью множества параметров. Если присутствует опция конфигурации url (или соответствующая переменная окружения DATABASE_URL), она будет использована для извлечения информации о подключении и учетных данных.

#Подключения для чтения и записи

Иногда может потребоваться использовать одно подключение к базе для SELECT-запросов, а другое — для INSERT, UPDATE и DELETE. Laravel упрощает это, и правильные подключения будут использоваться независимо от того, используете ли вы сырые запросы, конструктор запросов или Eloquent ORM.

Чтобы увидеть, как настраиваются подключения для чтения и записи, рассмотрим следующий пример:

'mysql' => [
    'read' => [
        'host' => [
            '192.168.1.1',
            '196.168.1.2',
        ],
    ],
    'write' => [
        'host' => [
            '196.168.1.3',
        ],
    ],
    'sticky' => true,
    'driver' => 'mysql',
    'database' => 'database',
    'username' => 'root',
    'password' => '',
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    'prefix' => '',
],

Обратите внимание, что в массив конфигурации добавлены три ключа: read, write и sticky. Ключи read и write содержат массивы с единственным ключом host. Остальные параметры базы данных для подключений read и write будут объединены из основного массива конфигурации mysql.

Вам нужно указывать значения в массивах read и write только если вы хотите переопределить параметры из основного массива mysql. В данном случае, 192.168.1.1 будет использоваться как хост для подключения "read", а 192.168.1.3 — для подключения "write". Учетные данные базы, префикс, кодировка и все остальные параметры из основного массива mysql будут общими для обоих подключений. Если в массиве host указано несколько значений, для каждого запроса будет случайным образом выбран один из хостов.

#Опция sticky

Опция sticky — это необязательный параметр, который позволяет сразу читать записи, записанные в базу данных в текущем цикле запроса. Если опция sticky включена и в текущем цикле был выполнен "write"-операция, все последующие "read"-операции будут использовать подключение "write". Это гарантирует, что данные, записанные в течение запроса, могут быть сразу же прочитаны из базы в том же запросе. Решение о необходимости такого поведения принимает разработчик.

#Выполнение SQL-запросов

После настройки подключения к базе вы можете выполнять запросы с помощью фасада DB. Фасад DB предоставляет методы для каждого типа запросов: select, update, insert, delete и statement.

#Выполнение SELECT-запроса

Для выполнения базового SELECT-запроса используйте метод select фасада DB:

<?php

namespace App\Http\Controllers;

use App\Http\Controllers\Controller;
use Illuminate\Support\Facades\DB;
use Illuminate\View\View;

class UserController extends Controller
{
    /**
     * Показать список всех пользователей приложения.
     */
    public function index(): View
    {
        $users = DB::select('select * from users where active = ?', [1]);

        return view('user.index', ['users' => $users]);
    }
}

Первым аргументом метода select передается SQL-запрос, а вторым — параметры для привязки к запросу. Обычно это значения условий where. Привязка параметров защищает от SQL-инъекций.

Метод select всегда возвращает array результатов. Каждый элемент массива — это объект PHP stdClass, представляющий запись из базы:

use Illuminate\Support\Facades\DB;

$users = DB::select('select * from users');

foreach ($users as $user) {
    echo $user->name;
}

#Выбор скалярных значений

Иногда запрос к базе возвращает одно скалярное значение. Вместо того, чтобы извлекать его из объекта записи, Laravel позволяет получить это значение напрямую с помощью метода scalar:

$burgers = DB::scalar(
    "select count(case when food = 'burger' then 1 end) as burgers from menu"
);

#Выбор нескольких наборов результатов

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

[$options, $notifications] = DB::selectResultSets(
    "CALL get_user_options_and_notifications(?)", $request->user()->id
);

#Использование именованных привязок

Вместо использования ? для параметров вы можете выполнять запрос с именованными привязками:

$results = DB::select('select * from users where id = :id', ['id' => 1]);

#Выполнение INSERT-запроса

Для выполнения insert-запроса используйте метод insert фасада DB. Как и select, этот метод принимает SQL-запрос первым аргументом и параметры привязки вторым:

use Illuminate\Support\Facades\DB;

DB::insert('insert into users (id, name) values (?, ?)', [1, 'Marc']);

#Выполнение UPDATE-запроса

Метод update используется для обновления существующих записей в базе. Метод возвращает количество затронутых строк:

use Illuminate\Support\Facades\DB;

$affected = DB::update(
    'update users set votes = 100 where name = ?',
    ['Anita']
);

#Выполнение DELETE-запроса

Метод delete используется для удаления записей из базы. Как и update, метод возвращает количество затронутых строк:

use Illuminate\Support\Facades\DB;

$deleted = DB::delete('delete from users');

#Выполнение общего SQL-запроса

Некоторые SQL-запросы не возвращают значения. Для таких операций используйте метод statement фасада DB:

DB::statement('drop table users');

#Выполнение неподготовленного запроса

Иногда нужно выполнить SQL-запрос без привязки параметров. Для этого используйте метод unprepared фасада DB:

DB::unprepared('update users set votes = 100 where name = "Dries"');

#Неявные коммиты

При использовании методов statement и unprepared фасада DB внутри транзакций будьте осторожны с запросами, вызывающими неявные коммиты. Такие запросы приводят к косвенному коммиту всей транзакции, из-за чего Laravel теряет контроль над уровнем транзакции. Пример такого запроса — создание таблицы:

DB::unprepared('create table a (col varchar(1) null)');

Обратитесь к руководству MySQL для списка всех запросов, вызывающих неявные коммиты.

#Использование нескольких подключений к базе данных

Если ваше приложение определяет несколько подключений в файле config/database.php, вы можете получить доступ к каждому из них через метод connection фасада DB. Имя подключения, передаваемое в метод connection, должно соответствовать одному из подключений, перечисленных в config/database.php или настроенных во время выполнения с помощью хелпера config:

use Illuminate\Support\Facades\DB;

$users = DB::connection('sqlite')->select(/* ... */);

Вы можете получить доступ к сырому экземпляру PDO подключения через метод getPdo у объекта подключения:

$pdo = DB::connection()->getPdo();

#Прослушивание событий запросов

Если вы хотите указать замыкание, вызываемое для каждого SQL-запроса, выполняемого вашим приложением, используйте метод listen фасада DB. Это полезно для логирования запросов или отладки. Зарегистрируйте слушатель запросов в методе boot сервис-провайдера:

<?php

namespace App\Providers;

use Illuminate\Database\Events\QueryExecuted;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\ServiceProvider;

class AppServiceProvider extends ServiceProvider
{
    /**
     * Зарегистрировать сервисы приложения.
     */
    public function register(): void
    {
        // ...
    }

    /**
     * Инициализировать сервисы приложения.
     */
    public function boot(): void
    {
        DB::listen(function (QueryExecuted $query) {
            // $query->sql;
            // $query->bindings;
            // $query->time;
        });
    }
}

#Мониторинг суммарного времени запросов

Распространённой проблемой производительности современных веб-приложений является время, затрачиваемое на запросы к базе данных. К счастью, Laravel может вызвать замыкание или callback, если время выполнения запросов превышает заданный порог в течение одного запроса. Для начала укажите порог времени (в миллисекундах) и замыкание в методе whenQueryingForLongerThan. Вызовите этот метод в методе boot сервис-провайдера:

<?php

namespace App\Providers;

use Illuminate\Database\Connection;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\ServiceProvider;
use Illuminate\Database\Events\QueryExecuted;

class AppServiceProvider extends ServiceProvider
{
    /**
     * Зарегистрировать сервисы приложения.
     */
    public function register(): void
    {
        // ...
    }

    /**
     * Инициализировать сервисы приложения.
     */
    public function boot(): void
    {
        DB::whenQueryingForLongerThan(500, function (Connection $connection, QueryExecuted $event) {
            // Уведомить команду разработки...
        });
    }
}

#Транзакции базы данных

Вы можете использовать метод transaction фасада DB для выполнения набора операций в рамках транзакции базы данных. Если в замыкании транзакции возникает исключение, транзакция автоматически откатывается, а исключение повторно выбрасывается. Если замыкание выполняется успешно, транзакция автоматически коммитится. Вам не нужно вручную управлять откатом или коммитом при использовании метода transaction:

use Illuminate\Support\Facades\DB;

DB::transaction(function () {
    DB::update('update users set votes = 1');

    DB::delete('delete from posts');
});

#Обработка дедлоков

Метод transaction принимает необязательный второй аргумент — количество попыток повторного выполнения транзакции при возникновении дедлока. Если попытки исчерпаны, выбрасывается исключение:

use Illuminate\Support\Facades\DB;

DB::transaction(function () {
    DB::update('update users set votes = 1');

    DB::delete('delete from posts');
}, 5);

#Ручное использование транзакций

Если вы хотите начать транзакцию вручную и полностью контролировать откаты и коммиты, используйте метод beginTransaction фасада DB:

use Illuminate\Support\Facades\DB;

DB::beginTransaction();

Вы можете откатить транзакцию с помощью метода rollBack:

DB::rollBack();

И, наконец, вы можете зафиксировать транзакцию с помощью метода commit:

DB::commit();

#Подключение к CLI базы данных

Если вы хотите подключиться к CLI вашей базы данных, используйте Artisan-команду db:

php artisan db

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

php artisan db mysql

#Просмотр ваших баз данных

С помощью Artisan-команд db:show и db:table вы можете получить ценную информацию о вашей базе данных и связанных с ней таблицах. Чтобы увидеть обзор базы данных, включая её размер, тип, количество открытых подключений и сводку по таблицам, используйте команду db:show:

php artisan db:show

Вы можете указать, какое подключение к базе данных следует просмотреть, передав имя подключения через опцию --database:

php artisan db:show --database=pgsql

Если вы хотите включить в вывод команды количество строк в таблицах и детали представлений базы данных, используйте опции --counts и --views соответственно. На больших базах данных получение количества строк и информации о представлениях может быть медленным:

php artisan db:show --counts --views

#Обзор таблицы

Если вы хотите получить обзор отдельной таблицы в базе, выполните Artisan-команду db:table. Эта команда предоставляет общую информацию о таблице базы данных, включая её столбцы, типы, атрибуты, ключи и индексы:

php artisan db:table users

#Мониторинг ваших баз данных

С помощью Artisan-команды db:monitor вы можете настроить Laravel на отправку события Illuminate\Database\Events\DatabaseBusy, если количество открытых подключений к базе превышает заданный порог.

Для начала запланируйте выполнение команды db:monitor каждую минуту. Команда принимает имена конфигураций подключений к базе, которые вы хотите мониторить, а также максимальное количество открытых подключений, при превышении которого будет отправлено событие:

php artisan db:monitor --databases=mysql,pgsql --max=100

Простое планирование команды недостаточно для получения уведомлений о количестве открытых подключений. Когда команда обнаружит базу с числом открытых подключений выше порога, будет отправлено событие DatabaseBusy. Вы должны прослушивать это событие в EventServiceProvider вашего приложения, чтобы отправлять уведомления вам или вашей команде разработки:

use App\Notifications\DatabaseApproachingMaxConnections;
use Illuminate\Database\Events\DatabaseBusy;
use Illuminate\Support\Facades\Event;
use Illuminate\Support\Facades\Notification;

/**
 * Зарегистрировать другие события для вашего приложения.
 */
public function boot(): void
{
    Event::listen(function (DatabaseBusy $event) {
        Notification::route('mail', 'dev@example.com')
                ->notify(new DatabaseApproachingMaxConnections(
                    $event->connectionName,
                    $event->connections
                ));
    });
}