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

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

Введение

Почти каждое современное веб-приложение взаимодействует с базой данных. Laravel упрощает взаимодействие с базами данных на различных поддерживаемых базах данных с использованием чистого SQL, конструктора запросов и ORM Eloquent. На данный момент 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, а также все необходимые для них зависимости, такие как драйвер ODBC Microsoft SQL.

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

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

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

mysql://root:[email protected]/forge?charset=UTF-8

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

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

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

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

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

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

'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 всегда вернет массив результатов. Каждый результат внутри массива будет объектом 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');

Выполнение общего оператора

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

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

Выполнение необработанного оператора

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

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

Внимание Поскольку неподготовленные операторы не привязывают параметры, они могут быть уязвимы для SQL-инъекций. Никогда не допускайте использование значений, контролируемых пользователем, в неподготовленном операторе.

Неявные фиксации

При использовании методов 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 может вызывать замыкание или обратный вызов на ваш выбор, когда он тратит слишком много времени на запросы к базе данных в течение одного запроса. Для начала укажите порог времени выполнения запроса (в миллисекундах) и замыкание для метода 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();

Примечание Методы транзакций фасада DB управляют транзакциями как для конструктора запросов, так и для ORM Eloquent.

Подключение к базе данных через 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. Вы должны прослушивать это событие в провайдере событий вашего приложения, чтобы отправить уведомление вам или вашей команде разработчиков:

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', '[email protected]')
                ->notify(new DatabaseApproachingMaxConnections(
                    $event->connectionName,
                    $event->connections
                ));
    });
}