Base de datos: Primeros pasos

• Introducción
  • Configuración
  • Conexiones de lectura y escritura
• Ejecutando consultas SQL
  • Uso de múltiples conexiones de base de datos
  • Escuchando eventos de consulta
  • Monitoreo del tiempo acumulado de consultas
• Transacciones de base de datos
• Conectándose a la CLI de la base de datos
• Inspeccionando sus bases de datos
• Monitoreando sus bases de datos

#Introducción

Casi todas las aplicaciones web modernas interactúan con una base de datos. Laravel facilita enormemente la interacción con bases de datos a través de una variedad de sistemas soportados, usando SQL en bruto, un constructor de consultas fluido y el Eloquent ORM. Actualmente, Laravel ofrece soporte oficial para cinco bases de datos:

#Configuración

La configuración para los servicios de base de datos de Laravel se encuentra en el archivo de configuración config/database.php de su aplicación. En este archivo, puede definir todas sus conexiones de base de datos, así como especificar cuál conexión debe usarse por defecto. La mayoría de las opciones de configuración dentro de este archivo se basan en los valores de las variables de entorno de su aplicación. Se proporcionan ejemplos para la mayoría de los sistemas de base de datos soportados por Laravel en este archivo.

Por defecto, la configuración de entorno de ejemplo de Laravel está lista para usarse con Laravel Sail, que es una configuración Docker para desarrollar aplicaciones Laravel en su máquina local. Sin embargo, usted es libre de modificar su configuración de base de datos según sea necesario para su base de datos local.

#Configuración de SQLite

Las bases de datos SQLite se almacenan en un solo archivo en su sistema de archivos. Puede crear una nueva base de datos SQLite usando el comando touch en su terminal: touch database/database.sqlite. Después de crear la base de datos, puede configurar fácilmente sus variables de entorno para apuntar a esta base de datos colocando la ruta absoluta al archivo en la variable de entorno DB_DATABASE:

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

Para habilitar las restricciones de claves foráneas en conexiones SQLite, debe establecer la variable de entorno DB_FOREIGN_KEYS en true:

DB_FOREIGN_KEYS=true

#Configuración de Microsoft SQL Server

Para usar una base de datos Microsoft SQL Server, debe asegurarse de tener instaladas las extensiones PHP sqlsrv y pdo_sqlsrv, así como cualquier dependencia que puedan requerir, como el controlador ODBC de Microsoft SQL.

#Configuración usando URLs

Normalmente, las conexiones de base de datos se configuran usando múltiples valores de configuración como host, database, username, password, etc. Cada uno de estos valores tiene su propia variable de entorno correspondiente. Esto significa que al configurar la información de conexión de su base de datos en un servidor de producción, debe gestionar varias variables de entorno.

Algunos proveedores de bases de datos gestionadas, como AWS y Heroku, proporcionan una única "URL" de base de datos que contiene toda la información de conexión en una sola cadena. Un ejemplo de URL de base de datos podría ser algo como lo siguiente:

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

Estas URLs suelen seguir una convención estándar de esquema:

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

Para mayor comodidad, Laravel soporta estas URLs como una alternativa a configurar su base de datos con múltiples opciones. Si la opción de configuración url (o la variable de entorno correspondiente DATABASE_URL) está presente, se usará para extraer la información de conexión y credenciales de la base de datos.

#Conexiones de lectura y escritura

A veces puede desear usar una conexión de base de datos para sentencias SELECT y otra para sentencias INSERT, UPDATE y DELETE. Laravel facilita esto, y las conexiones adecuadas siempre se usarán ya sea que utilice consultas en bruto, el constructor de consultas o el Eloquent ORM.

Para ver cómo deben configurarse las conexiones de lectura/escritura, veamos este ejemplo:

'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' => '',
],

Note que se han añadido tres claves al arreglo de configuración: read, write y sticky. Las claves read y write tienen valores de tipo arreglo que contienen una única clave: host. El resto de las opciones de base de datos para las conexiones read y write se combinarán con el arreglo principal de configuración mysql.

Solo necesita colocar elementos en los arreglos read y write si desea sobrescribir los valores del arreglo principal mysql. Así, en este caso, 192.168.1.1 se usará como host para la conexión de "lectura", mientras que 192.168.1.3 se usará para la conexión de "escritura". Las credenciales de la base de datos, el prefijo, el conjunto de caracteres y todas las demás opciones del arreglo principal mysql se compartirán entre ambas conexiones. Cuando existen múltiples valores en el arreglo de configuración host, se elegirá aleatoriamente un host de base de datos para cada solicitud.

#La opción sticky

La opción sticky es un valor opcional que puede usarse para permitir la lectura inmediata de registros que han sido escritos en la base de datos durante el ciclo de la solicitud actual. Si la opción sticky está habilitada y se ha realizado una operación de "escritura" en la base de datos durante el ciclo de la solicitud actual, cualquier operación de "lectura" posterior usará la conexión de "escritura". Esto asegura que cualquier dato escrito durante el ciclo de la solicitud pueda ser leído inmediatamente desde la base de datos en esa misma solicitud. Usted decide si este comportamiento es el deseado para su aplicación.

#Ejecutando consultas SQL

Una vez que haya configurado su conexión de base de datos, puede ejecutar consultas usando el facade DB. El facade DB proporciona métodos para cada tipo de consulta: select, update, insert, delete y statement.

#Ejecutando una consulta Select

Para ejecutar una consulta SELECT básica, puede usar el método select en el facade DB:

<?php

namespace App\Http\Controllers;

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

class UserController extends Controller
{
    /**
     * Mostrar una lista de todos los usuarios de la aplicación.
     */
    public function index(): View
    {
        $users = DB::select('select * from users where active = ?', [1]);

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

El primer argumento pasado al método select es la consulta SQL, mientras que el segundo argumento son los parámetros que deben enlazarse a la consulta. Normalmente, estos son los valores de las restricciones de la cláusula where. El enlace de parámetros protege contra inyección SQL.

El método select siempre devolverá un array de resultados. Cada resultado dentro del arreglo será un objeto PHP stdClass que representa un registro de la base de datos:

use Illuminate\Support\Facades\DB;

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

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

#Seleccionando valores escalares

A veces, la consulta a la base de datos puede devolver un único valor escalar. En lugar de tener que obtener el resultado escalar de un objeto registro, Laravel permite recuperar este valor directamente usando el método scalar:

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

#Seleccionando múltiples conjuntos de resultados

Si su aplicación llama a procedimientos almacenados que devuelven múltiples conjuntos de resultados, puede usar el método selectResultSets para obtener todos los conjuntos devueltos por el procedimiento almacenado:

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

#Usando enlaces nombrados

En lugar de usar ? para representar sus parámetros enlazados, puede ejecutar una consulta usando enlaces nombrados:

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

#Ejecutando una sentencia Insert

Para ejecutar una sentencia insert, puede usar el método insert en el facade DB. Al igual que select, este método acepta la consulta SQL como primer argumento y los enlaces como segundo argumento:

use Illuminate\Support\Facades\DB;

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

#Ejecutando una sentencia Update

El método update debe usarse para actualizar registros existentes en la base de datos. El método devuelve el número de filas afectadas por la sentencia:

use Illuminate\Support\Facades\DB;

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

#Ejecutando una sentencia Delete

El método delete debe usarse para eliminar registros de la base de datos. Al igual que update, el método devuelve el número de filas afectadas:

use Illuminate\Support\Facades\DB;

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

#Ejecutando una sentencia general

Algunas sentencias de base de datos no devuelven ningún valor. Para este tipo de operaciones, puede usar el método statement en el facade DB:

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

#Ejecutando una sentencia sin preparar

A veces puede querer ejecutar una sentencia SQL sin enlazar ningún valor. Puede usar el método unprepared del facade DB para lograr esto:

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

#Commits implícitos

Al usar los métodos statement y unprepared del facade DB dentro de transacciones, debe tener cuidado de evitar sentencias que causen commits implícitos. Estas sentencias harán que el motor de base de datos confirme indirectamente toda la transacción, dejando a Laravel sin conocimiento del nivel de transacción de la base de datos. Un ejemplo de tal sentencia es crear una tabla:

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

Consulte el manual de MySQL para una lista de todas las sentencias que disparan commits implícitos.

#Uso de múltiples conexiones de base de datos

Si su aplicación define múltiples conexiones en el archivo de configuración config/database.php, puede acceder a cada conexión mediante el método connection proporcionado por el facade DB. El nombre de conexión pasado al método connection debe corresponder a una de las conexiones listadas en su archivo config/database.php o configuradas en tiempo de ejecución usando el helper config:

use Illuminate\Support\Facades\DB;

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

Puede acceder a la instancia PDO subyacente sin procesar de una conexión usando el método getPdo en una instancia de conexión:

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

#Escuchando eventos de consulta

Si desea especificar un closure que se invoque para cada consulta SQL ejecutada por su aplicación, puede usar el método listen del facade DB. Este método puede ser útil para registrar consultas o depurar. Puede registrar su closure de escucha de consultas en el método boot de un service provider:

<?php

namespace App\Providers;

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

class AppServiceProvider extends ServiceProvider
{
    /**
     * Registrar cualquier servicio de la aplicación.
     */
    public function register(): void
    {
        // ...
    }

    /**
     * Inicializar cualquier servicio de la aplicación.
     */
    public function boot(): void
    {
        DB::listen(function (QueryExecuted $query) {
            // $query->sql;
            // $query->bindings;
            // $query->time;
        });
    }
}

#Monitoreo del tiempo acumulado de consultas

Un cuello de botella común en el rendimiento de aplicaciones web modernas es el tiempo que pasan consultando bases de datos. Afortunadamente, Laravel puede invocar un closure o callback de su elección cuando se gasta demasiado tiempo consultando la base de datos durante una sola solicitud. Para comenzar, proporcione un umbral de tiempo de consulta (en milisegundos) y un closure al método whenQueryingForLongerThan. Puede invocar este método en el método boot de un service provider:

<?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
{
    /**
     * Registrar cualquier servicio de la aplicación.
     */
    public function register(): void
    {
        // ...
    }

    /**
     * Inicializar cualquier servicio de la aplicación.
     */
    public function boot(): void
    {
        DB::whenQueryingForLongerThan(500, function (Connection $connection, QueryExecuted $event) {
            // Notificar al equipo de desarrollo...
        });
    }
}

#Transacciones de base de datos

Puede usar el método transaction proporcionado por el facade DB para ejecutar un conjunto de operaciones dentro de una transacción de base de datos. Si se lanza una excepción dentro del closure de la transacción, la transacción se revertirá automáticamente y la excepción se volverá a lanzar. Si el closure se ejecuta correctamente, la transacción se confirmará automáticamente. No necesita preocuparse por revertir o confirmar manualmente al usar el método transaction:

use Illuminate\Support\Facades\DB;

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

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

#Manejo de deadlocks

El método transaction acepta un segundo argumento opcional que define el número de veces que una transacción debe reintentarse cuando ocurre un deadlock. Una vez agotados estos intentos, se lanzará una excepción:

use Illuminate\Support\Facades\DB;

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

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

#Uso manual de transacciones

Si desea iniciar una transacción manualmente y tener control completo sobre los rollbacks y commits, puede usar el método beginTransaction proporcionado por el facade DB:

use Illuminate\Support\Facades\DB;

DB::beginTransaction();

Puede revertir la transacción mediante el método rollBack:

DB::rollBack();

Finalmente, puede confirmar una transacción mediante el método commit:

DB::commit();

#Conectándose a la CLI de la base de datos

Si desea conectarse a la CLI de su base de datos, puede usar el comando Artisan db:

php artisan db

Si es necesario, puede especificar un nombre de conexión de base de datos para conectarse a una conexión que no sea la conexión por defecto:

php artisan db mysql

#Inspeccionando sus bases de datos

Usando los comandos Artisan db:show y db:table, puede obtener información valiosa sobre su base de datos y sus tablas asociadas. Para ver una descripción general de su base de datos, incluyendo su tamaño, tipo, número de conexiones abiertas y un resumen de sus tablas, puede usar el comando db:show:

php artisan db:show

Puede especificar qué conexión de base de datos debe inspeccionarse proporcionando el nombre de la conexión a través de la opción --database:

php artisan db:show --database=pgsql

Si desea incluir el conteo de filas de las tablas y detalles de vistas de base de datos en la salida del comando, puede proporcionar las opciones --counts y --views, respectivamente. En bases de datos grandes, obtener el conteo de filas y detalles de vistas puede ser lento:

php artisan db:show --counts --views

#Resumen de tabla

Si desea obtener un resumen de una tabla individual dentro de su base de datos, puede ejecutar el comando Artisan db:table. Este comando proporciona una visión general de una tabla de base de datos, incluyendo sus columnas, tipos, atributos, claves e índices:

php artisan db:table users

#Monitoreando sus bases de datos

Usando el comando Artisan db:monitor, puede indicar a Laravel que despache un evento Illuminate\Database\Events\DatabaseBusy si su base de datos está manejando más de un número especificado de conexiones abiertas.

Para comenzar, debe programar el comando db:monitor para que se ejecute cada minuto. El comando acepta los nombres de las configuraciones de conexión de base de datos que desea monitorear, así como el número máximo de conexiones abiertas que se tolerarán antes de despachar un evento:

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

Programar este comando por sí solo no es suficiente para activar una notificación que le alerte sobre el número de conexiones abiertas. Cuando el comando detecta una base de datos que tiene un conteo de conexiones abiertas que excede su umbral, se despachará un evento DatabaseBusy. Debe escuchar este evento dentro del EventServiceProvider de su aplicación para enviar una notificación a usted o a su equipo de desarrollo:

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

/**
 * Registrar cualquier otro evento para su aplicación.
 */
public function boot(): void
{
    Event::listen(function (DatabaseBusy $event) {
        Notification::route('mail', 'dev@example.com')
                ->notify(new DatabaseApproachingMaxConnections(
                    $event->connectionName,
                    $event->connections
                ));
    });
}