Database: Getting Started

Introducción

Laravel makes connecting with databases and running queries extremely simple across a variety of database back-ends using either raw SQL, the fluent query builder, and the Eloquent ORM. Currently, Laravel supports four database systems:

  • MySQL
  • Postgres
  • SQLite
  • SQL Server

Configuración

Laravel hace que conectarse con bases de datos y correr consultas sea extremadamente simple. The database configuration for your application is located at config/database.php. En este archivo se puede definir todas las conexiones de base de datos, así como especificar qué conexión se debe utilizar de forma predeterminada. Ejemplos para todos los sistemas de bases de datos soportadas se encuentran en este archivo.

By default, Laravel's sample environment configuration is ready to use with Laravel Homestead, which is a convenient virtual machine for doing Laravel development on your local machine. Of course, you are free to modify this configuration as needed for your local database.

Lectura / escritura de conexiones

A veces puedes querer utilizar una conexión de base de datos para declaraciones SELECT, y otras para INSERT, UPDATE y DELETE. Laravel hace esto muy sencillo, y las conexiones apropiadas serán siempre usadas tanto si utilizas consultas directas (raw queries), el generador de consultas o el ORM Eloquent.

Para ver como leer / escribir, las conexiones deben ser configuradas antes, veamos este ejemplo:

'mysql' => [
    'read' => [
        'host' => '192.168.1.1',
    ],
    'write' => [
        'host' => '196.168.1.2'
    ],
    'driver'    => 'mysql',
    'database'  => 'database',
    'username'  => 'root',
    'password'  => '',
    'charset'   => 'utf8',
    'collation' => 'utf8_unicode_ci',
    'prefix'    => '',
],

Ten en cuenta que se han añadido dos elementos al array de configuración: read (lectura) y write (escritura). Ambos elementos tendrán un array que contendrá un único elemento: host. El resto de las opciones de base de datos para las conexiones read y write se combinarán con el array principal mysql.

Entonces, solo necesitamos colocar los elementos read y write si deseamos reemplazar los valores en el array principal. Así que, en este caso, 192.168.1.1 se utilizará como la conexión de "lectura", mientras que 192.168.1.2 se utilizará como la conexión "escritura". Los credenciales de la base de datos, prefijo, conjunto de caracteres y otras opciones del array principal mysql serán compartidas a través de ambas conexiones.

Running Raw SQL Queries

Una vez que hayas configurado tu conexión de base de datos, podrás ejecutar las consultas usando la clase DB. The DB facade provides methods for each type of query: select, update, insert, and statement.

Ejecutar una consulta Select

To run a basic query, we can use the select method on the DB facade:

<?php

namespace App\Http\Controllers;

use DB;
use App\Http\Controllers\Controller;

class UserController extends Controller
{
    /**
     * Show a list of all of the application's users.
     *
     * @return Response
     */
    public function index()
    {
        $users = DB::select('select * from users where active = ?', [1]);

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

The first argument passed to the select method is the raw SQL query, while the second argument is any parameter bindings that need to be bound to the query. Typically, these are the values of the where clause constraints. Parameter binding provides protection against SQL injection.

El método select siempre retornará un array de resultados. Each result within the array will be a PHP StdClass object, allowing you to access the values of the results:

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

Using Named Bindings

Instead of using ? to represent your parameter bindings, you may execute a query using named bindings:

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

Ejecutar una sentencia Insert

To execute an insert statement, you may use the insert method on the DB facade. Like select, this method takes the raw SQL query as its first argument, and bindings as the second argument:

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

Ejecutar una sentencia Update

The update method should be used to update existing records in the database. The number of rows affected by the statement will be returned by the method:

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

Ejecutar una sentencia Delete

The delete method should be used to delete records from the database. Like update, the number of rows deleted will be returned:

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

Ejecugar una sentencia general

Some database statements should not return any value. For these types of operations, you may use the statement method on the DB facade:

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

Escuchar eventos de consulta

If you would like to receive each SQL query executed by your application, you may use the listen method. This method is useful for logging queries or debugging. You may register your query listener in a service provider:

<?php

namespace App\Providers;

use DB;
use Illuminate\Support\ServiceProvider;

class AppServiceProvider extends ServiceProvider
{
    /**
     * Bootstrap any application services.
     *
     * @return void
     */
    public function boot()
    {
        DB::listen(function($sql, $bindings, $time) {
            //
        });
    }

    /**
     * Register the service provider.
     *
     * @return void
     */
    public function register()
    {
        //
    }
}

Transacciones de bases de datos

To run a set of operations within a database transaction, you may use the transaction method on the DB facade. If an exception is thrown within the transaction Closure, the transaction will automatically be rolled back. If the Closure executes successfully, the transaction will automatically be committed. You don't need to worry about manually rolling back or committing while using the transaction method:

DB::transaction(function () {
    DB::table('users')->update(['votes' => 1]);

    DB::table('posts')->delete();
});

Manually Using Transactions

If you would like to begin a transaction manually and have complete control over rollbacks and commits, you may use the beginTransaction method on the DB facade:

DB::beginTransaction();

You can rollback the transaction via the rollBack method:

DB::rollBack();

Por último, puedes hacer la transacción persistente utilizando el método commit:

DB::commit();

Note: Using the DB facade's transaction methods also controls transactions for the query builder and Eloquent ORM.

Using Multiple Database Connections

When using multiple connections, you may access each connection via the connection method on the DB facade. The name passed to the connection method should correspond to one of the connections listed in your config/database.php configuration file:

$users = DB::connection('foo')->select(...);

You may also access the raw, underlying PDO instance using the getPdo method on a connection instance:

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