Base de datos: Primeros pasos

• Introducción
  • Configuración
  • Conexiones de Lectura y Escritura
• Ejecución de Consultas SQL
  • Uso de Múltiples Conexiones de Base de Datos
  • Escucha de Eventos de Consulta
  • Monitoreo del Tiempo Total de Consulta
• Transacciones de Base de Datos
• Conexión a la CLI de la Base de Datos
• Inspeccionar tus Bases de Datos
• Monitoreo de tus Bases de Datos

Introducción

Casi todas las aplicaciones web modernas interactúan con una base de datos. Laravel facilita la interacción con bases de datos de manera extremadamente simple a través de una variedad de bases de datos compatibles mediante SQL sin procesar, un constructor de consultas fluido y el ORM Eloquent. Actualmente, Laravel brinda soporte de primera mano para cinco bases de datos:

Configuración

La configuración de los servicios de base de datos de Laravel se encuentra en el archivo de configuración config/database.php de tu aplicación. En este archivo, puedes definir todas tus conexiones de base de datos, así como especificar cuál debería ser la conexión utilizada por defecto. La mayoría de las opciones de configuración en este archivo se basan en los valores de las variables de entorno de tu aplicación. Se proporcionan ejemplos para la mayoría de los sistemas de bases de datos compatibles con 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 de Docker para desarrollar aplicaciones Laravel en tu máquina local. Sin embargo, eres libre de modificar tu configuración de base de datos según sea necesario para tu base de datos local.

Configuración de SQLite

Las bases de datos SQLite están contenidas en un solo archivo en tu sistema de archivos. Puedes crear una nueva base de datos SQLite usando el comando touch en tu terminal: touch database/database.sqlite. Después de crear la base de datos, puedes configurar fácilmente tus variables de entorno para que apunten a esta base de datos colocando la ruta absoluta de la base de datos en la variable de entorno DB_DATABASE:

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

Para habilitar las restricciones de clave externa para las conexiones SQLite, debes 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 de Microsoft SQL Server, debes asegurarte 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 Utilizando URLs

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

Algunos proveedores de bases de datos administradas, como AWS y Heroku, proporcionan una única "URL" de base de datos que contiene toda la información de conexión para la base de datos en una sola cadena. Una URL de base de datos de ejemplo podría verse algo así:

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

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

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

Por conveniencia, Laravel admite estas URL como una alternativa para configurar tu base de datos con múltiples opciones de configuración. Si la opción de configuración url (o la correspondiente variable de entorno DATABASE_URL) está presente, se utilizará para extraer la conexión de la base de datos y la información de las credenciales.

Conexiones de Lectura y Escritura

En ocasiones, es posible que desees usar una conexión de base de datos para las declaraciones SELECT y otra para las declaraciones INSERT, UPDATE y DELETE. Laravel facilita esto, y las conexiones adecuadas siempre se usarán ya sea que estés utilizando consultas SQL sin procesar, el generador de consultas o el ORM Eloquent.

Para ver cómo se deben configurar 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' => '',
],

Ten en cuenta que se han agregado tres claves al array de configuración: read, write y sticky. Las claves read y write tienen valores de array que contienen una única clave: host. El resto de las opciones de la base de datos para las conexiones read y write se fusionarán desde el array de configuración principal mysql.

Solo necesitas colocar elementos en los arrays read y write si deseas anular los valores del array principal mysql. En este caso, 192.168.1.1 se utilizará como el host para la conexión "read", mientras que 192.168.1.3 se utilizará para la conexión "write". Las credenciales de la base de datos, el prefijo, el conjunto de caracteres y todas las demás opciones en el array principal mysql se compartirán en ambas conexiones. Cuando existen múltiples valores en el array de configuración host, se elige aleatoriamente un host de la base de datos para cada solicitud.

La opción sticky

La opción sticky es un valor opcional que se puede usar para permitir la lectura inmediata de registros que se han escrito en la base de datos durante el ciclo de 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 solicitud actual, cualquier operación adicional de "lectura" utilizará la conexión de "escritura". Esto garantiza que cualquier dato escrito durante el ciclo de solicitud pueda leerse inmediatamente desde la base de datos durante esa misma solicitud. Depende de ti decidir si este es el comportamiento deseado para tu aplicación.

Ejecución de Consultas SQL

Una vez que hayas configurado tu conexión de base de datos, puedes ejecutar consultas utilizando la fachada DB. La fachada DB proporciona métodos para cada tipo de consulta: select, update, insert, delete y statement.

Ejecutar una Consulta Select

Para ejecutar una consulta SELECT básica, puedes utilizar el método select en la fachada DB:

<?php
 
namespace App\Http\Controllers;
 
use App\Http\Controllers\Controller;
use Illuminate\Support\Facades\DB;
use Illuminate\View\View;
 
class UserController extends Controller
{
    /**
     * Muestra 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 enlaces de parámetros que deben vincularse a la consulta. Por lo general, estos son los valores de las restricciones de la cláusula where. La vinculación de parámetros proporciona protección contra la inyección SQL.

El método select siempre devolverá un array de resultados. Cada resultado dentro del array 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;
}

Selección de Valores Escalares

En ocasiones, tu consulta a la base de datos puede dar como resultado un único valor escalar. En lugar de verse obligado a recuperar el resultado escalar de la consulta desde un objeto de registro, Laravel te permite recuperar este valor directamente utilizando el método scalar:

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

Selección de Múltiples Conjuntos de Resultados

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

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

Uso de Bindings Nombrados

En lugar de utilizar ? para representar tus vinculaciones de parámetros, puedes ejecutar una consulta utilizando vinculaciones con nombre:

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

Ejecutar una Declaración Insert

Para ejecutar una declaración insert, puedes utilizar el método insert en la fachada DB. Al igual que select, este método acepta la consulta SQL como su primer argumento y los enlaces como su segundo argumento:

use Illuminate\Support\Facades\DB;
 
DB::insert('insert into users (id, name) values (?, ?)', [1, 'Marc']);

Ejecutar una Declaración Update

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

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

Ejecutar una Declaración Delete

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

use Illuminate\Support\Facades\DB;
 
$deleted = DB::delete('delete from users');

Ejecutar una Declaración General

Algunas declaraciones de base de datos no devuelven ningún valor. Para estos tipos de operaciones, puedes usar el método statement en la fachada DB:

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

Ejecutar una Declaración No Preparada

A veces, es posible que desees ejecutar una declaración SQL sin vincular ningún valor. Puedes usar el método unprepared de la fachada DB para lograr esto:

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

Advertencia Dado que las declaraciones sin procesar no vinculan parámetros, pueden ser vulnerables a la inyección SQL. Nunca debes permitir valores controlados por el usuario dentro de una declaración sin procesar.

Compromisos Implícitos

Cuando uses los métodos statement y unprepared de la fachada DB dentro de transacciones, debes tener cuidado de evitar declaraciones que causen compromisos implícitos. Estas declaraciones harán que el motor de la base de datos comprometa indirectamente toda la transacción, dejando a Laravel sin conocimiento del nivel de transacción de la base de datos. Un ejemplo de tal declaración es crear una tabla de base de datos:

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

Consulta el manual de MySQL para obtener una lista de todas las declaraciones que desencadenan compromisos implícitos.

Uso de Múltiples Conexiones de Base de Datos

Si tu aplicación define múltiples conexiones en tu archivo de configuración config/database.php, puedes acceder a cada conexión mediante el método connection proporcionado por la fachada DB. El nombre de conexión pasado al método connection debe coincidir con una de las conexiones enumeradas en tu archivo de configuración config/database.php o configuradas en tiempo de ejecución mediante el ayudante config:

use Illuminate\Support\Facades\DB;
 
$users = DB::connection('sqlite')->select(/* ... */);

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

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

Escucha de Eventos de Consulta

Si deseas especificar un cierre que se invoca por cada consulta SQL ejecutada por tu aplicación, puedes usar el método listen de la fachada DB. Este método puede ser útil para registrar consultas o para fines de depuración. Puedes registrar tu cierre de escucha de consultas en el método boot de un proveedor de servicios:

<?php
 
namespace App\Providers;
 
use Illuminate\Database\Events\QueryExecuted;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\ServiceProvider;
 
class AppServiceProvider extends ServiceProvider
{
    /**
     * Registra cualquier servicio de la aplicación.
     */
    public function register(): void
    {
        // ...
    }
 
    /**
     * Inicia cualquier servicio de la aplicación.
     */
    public function boot(): void
    {
        DB::listen(function (QueryExecuted $query) {
            // $query->sql;
            // $query->bindings;
            // $query->time;
        });
    }
}

Monitoreo del Tiempo Total de Consulta

Un cuello de botella común en las aplicaciones web modernas es la cantidad de tiempo que pasan consultando bases de datos. Afortunadamente, Laravel puede invocar un cierre o callback de tu elección cuando pasa demasiado tiempo consultando la base de datos durante una sola solicitud. Para empezar, proporciona un umbral de tiempo de consulta (en milisegundos) y un cierre al método whenQueryingForLongerThan. Puedes invocar este método en el método boot de un proveedor de servicios:

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

Puedes usar el método transaction proporcionado por la fachada DB para ejecutar un conjunto de operaciones dentro de una transacción de base de datos. Si se lanza una excepción dentro del cierre de la transacción, la transacción se revertirá automáticamente y la excepción se volverá a lanzar. Si el cierre se ejecuta correctamente, la transacción se confirmará automáticamente. No necesitas preocuparte 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 se debe intentar nuevamente una transacción cuando ocurre un bloqueo. 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 deseas comenzar una transacción manualmente y tener un control completo sobre las reversiones y confirmaciones, puedes usar el método beginTransaction proporcionado por la fachada DB:

use Illuminate\Support\Facades\DB;
 
DB::beginTransaction();

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

DB::rollBack();

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

DB::commit();

Nota Los métodos de transacción de la fachada DB controlan las transacciones tanto para el constructor de consultas como para Eloquent ORM.

Conexión a la CLI de la Base de Datos

Si deseas conectarte a la CLI de tu base de datos, puedes usar el comando db de Artisan:

php artisan db

Si es necesario, puedes especificar un nombre de conexión de base de datos para conectarte a una conexión de base de datos que no sea la conexión predeterminada:

php artisan db mysql

Inspeccionar tus Bases de Datos

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

php artisan db:show

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

php artisan db:show --database=pgsql

Si deseas incluir recuentos de filas de tablas y detalles de vistas de bases de datos en la salida del comando, puedes proporcionar las opciones --counts y --views, respectivamente. En bases de datos grandes, recuperar recuentos de filas y detalles de vistas puede ser lento:

php artisan db:show --counts --views

Resumen de Tablas

Si deseas obtener una descripción general de una tabla específica dentro de tu base de datos, puedes ejecutar el comando db:table de Artisan. Este comando proporciona una descripción general de una tabla de base de datos, incluidas sus columnas, tipos, atributos, claves e índices:

php artisan db:table users

Monitoreo de tus Bases de Datos

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

Para empezar, debes 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 deseas supervisar, así como el número máximo de conexiones abiertas que se deben tolerar 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 te alerte sobre la cantidad de conexiones abiertas. Cuando el comando encuentra una base de datos que tiene un recuento de conexiones abiertas que supera tu umbral, se despachará un evento DatabaseBusy. Debes escuchar este evento dentro del EventServiceProvider de tu aplicación para enviar una notificación a ti o a tu equipo de desarrollo:

use App\Notifications\DatabaseApproachingMaxConnections;
use Illuminate\Database\Events\DatabaseBusy;
use Illuminate\Support\Facades\Event;
use Illuminate\Support\Facades\Notification;
 
/**
 * Registra cualquier otro evento para tu aplicación.
 */
public function boot(): void
{
    Event::listen(function (DatabaseBusy $event) {
        Notification::route('mail', '[email protected]')
                ->notify(new DatabaseApproachingMaxConnections(
                    $event->connectionName,
                    $event->connections
                ));
    });
}