Laravel - Working With Database

Laravel - Working With Database

Laravel provides a powerful and elegant way to interact with your databases. It supports several database systems out of the box and offers multiple ways to perform database operations: raw SQL queries, a fluent Query Builder, and the Eloquent ORM (Object-Relational Mapper).

Supported Databases:

• MySQL / MariaDB
• PostgreSQL
• SQLite
• SQL Server

Database Configuration

Database connection configuration is primarily managed in the .env file at the root of your Laravel project. Laravel’s config/database.php file uses these environment variables to set up connections.

Example .env configuration for MySQL:

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=your_database_name
DB_USERNAME=your_username
DB_PASSWORD=your_password

Ensure you replace your_database_name, your_username, and your_password with your actual database credentials. After changing your .env file, you might need to clear the configuration cache:

php artisan config:clear

Database Migrations (Schema Management)

Before performing CRUD operations, you need a database table. Laravel’s migrations are like version control for your database, allowing you to easily define and modify your database schema. You can create a migration using:

php artisan make:migration create_students_table

Then, define your table structure in the generated migration file (e.g., in database/migrations/xxxx_xx_xx_xxxxxx_create_students_table.php):

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    public function up(): void
    {
        Schema::create('students', function (Blueprint $table) {
            $table->id(); // Auto-incrementing BIGINT primary key 'id'
            $table->string('name', 100);
            $table->string('email')->unique();
            $table->timestamps(); // Adds 'created_at' and 'updated_at' columns
        });
    }

    public function down(): void
    {
        Schema::dropIfExists('students');
    }
};

Run the migration to create the table:

php artisan migrate

Running Raw SQL Queries

Laravel allows you to run raw SQL queries using the DB facade. While generally less preferred than the Query Builder or Eloquent for security and maintainability, it’s available when needed.

Warning: When using raw SQL, always use parameter binding to protect against SQL injection vulnerabilities.

Insert Records:

Use DB::insert() for INSERT statements. It returns true or false.

use Illuminate\Support\Facades\DB;

DB::insert('INSERT INTO students (name, email, created_at, updated_at) VALUES (?, ?, ?, ?)',
    ['Alice Smith', 'alice@example.com', now(), now()]
);

Retrieve Records:

Use DB::select() for SELECT statements. It returns an array of stdClass objects.

$students = DB::select('SELECT * FROM students WHERE id = ?', [1]);

foreach ($students as $student) {
    // echo $student->name;
}

Update Records:

Use DB::update() for UPDATE statements. It returns the number of affected rows.

$affectedRows = DB::update('UPDATE students SET name = ? WHERE email = ?',
    ['Alicia Smith', 'alice@example.com']
);

Delete Records:

Use DB::delete() for DELETE statements. It returns the number of affected rows.

$deletedRows = DB::delete('DELETE FROM students WHERE id = ?', [1]);

General Statements:

For other types of statements (e.g., CREATE TABLE, DROP TABLE if not using migrations), use DB::statement():

DB::statement('DROP TABLE IF EXISTS old_table');

The Query Builder

Laravel’s database query builder provides a convenient, fluent interface for creating and running database queries. It can be used to perform most database operations in your application and works on all supported database systems.

It offers better security (automatic PDO parameter binding) and is more expressive than raw SQL queries.

Insert Records:

DB::table('students')->insert([
    'name' => 'Bob Johnson',
    'email' => 'bob@example.com',
    'created_at' => now(),
    'updated_at' => now()
]);

// Insert multiple records
DB::table('students')->insert([
    ['name' => 'Charlie Brown', 'email' => 'charlie@example.com', 'created_at' => now(), 'updated_at' => now()],
    ['name' => 'Diana Prince', 'email' => 'diana@example.com', 'created_at' => now(), 'updated_at' => now()]
]);

Retrieve Records:

// Get all students
$allStudents = DB::table('students')->get();

// Get a single student by ID
$student = DB::table('students')->where('id', 2)->first();

// Get students matching a condition
$activeStudents = DB::table('students')->where('name', 'like', 'A%')->get();

// Pluck a single column's values
$studentNames = DB::table('students')->pluck('name');

Update Records:

$affected = DB::table('students')
              ->where('id', 2)
              ->update(['name' => 'Robert Johnson']);

Delete Records:

$deleted = DB::table('students')->where('id', 3)->delete();

// Delete all records (use with caution!)
// DB::table('students')->delete();

// Better way to truncate a table (resets auto-incrementing ID)
// DB::table('students')->truncate();

Eloquent ORM

Laravel’s Eloquent ORM is an elegant, ActiveRecord implementation for working with your database. Each database table has a corresponding “Model” which is used to interact with that table. Models allow you to query for data in your tables, as well as insert new records into the table.

While the Query Builder is powerful, Eloquent adds an object-oriented layer, making interactions more intuitive, especially when dealing with relationships between tables. This topic is extensive and typically covered in its own chapter.

Example: Student Management CRUD Operations

Let’s outline how you might implement the CRUD operations for a ‘student’ entity using the Query Builder within a controller. First, ensure you have a students table (see Migrations section above).

Create a StudentController:

php artisan make:controller StudentController

Then, add methods to app/Http/Controllers/StudentController.php (these are simplified examples):

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\View\View;
use Illuminate\Http\RedirectResponse;

class StudentController extends Controller
{
    // Display a list of students
    public function index(): View
    {
        $students = DB::table('students')->orderBy('name')->get();
        return view('students.index', ['students' => $students]);
    }

    // Show the form for creating a new student
    public function create(): View
    {
        return view('students.create');
    }

    // Store a newly created student in storage
    public function store(Request $request): RedirectResponse
    {
        $validated = $request->validate([
            'name' => 'required|string|max:100',
            'email' => 'required|email|unique:students,email',
        ]);

        DB::table('students')->insert([
            'name' => $validated['name'],
            'email' => $validated['email'],
            'created_at' => now(),
            'updated_at' => now(),
        ]);

        return redirect()->route('students.index')->with('success', 'Student created successfully.');
    }

    // Show the form for editing the specified student
    public function edit(int $id): View
    {
        $student = DB::table('students')->where('id', $id)->first();
        if (!$student) {
            abort(404);
        }
        return view('students.edit', ['student' => $student]);
    }

    // Update the specified student in storage
    public function update(Request $request, int $id): RedirectResponse
    {
        $validated = $request->validate([
            'name' => 'required|string|max:100',
            'email' => 'required|email|unique:students,email,' . $id, // Ignore current student's email for unique check
        ]);

        $affected = DB::table('students')
            ->where('id', $id)
            ->update([
                'name' => $validated['name'],
                'email' => $validated['email'],
                'updated_at' => now(),
            ]);

        if ($affected) {
            return redirect()->route('students.index')->with('success', 'Student updated successfully.');
        }
        return redirect()->route('students.index')->with('error', 'Student not found or no changes made.');
    }

    // Remove the specified student from storage
    public function destroy(int $id): RedirectResponse
    {
        $deleted = DB::table('students')->where('id', $id)->delete();
        if ($deleted) {
            return redirect()->route('students.index')->with('success', 'Student deleted successfully.');
        }
        return redirect()->route('students.index')->with('error', 'Student not found.');
    }
}

You would then define routes in routes/web.php to map URLs to these controller actions, and create the corresponding Blade views (students/index.blade.php, students/create.blade.php, etc.).

Common Learning Obstacle: Forgetting Parameter Binding

When using raw SQL (DB::select, DB::insert, etc.), a frequent mistake is to directly embed user input into the query string. This opens your application to SQL injection attacks. Always use ? placeholders and pass the values as an array in the second argument. The Query Builder and Eloquent handle this for you automatically.

Additional Resources:

• Laravel Documentation on Database: https://laravel.com/docs/database
• Query Builder: https://laravel.com/docs/queries
• Eloquent ORM: https://laravel.com/docs/eloquent
• Migrations: https://laravel.com/docs/migrations