Build an app with Laravel 11 and MySQL

Laravel 11 is a highly popular PHP framework known for its robust features that facilitate the development of complex web applications. It simplifies tasks like authentication and database management, particularly with MySQL databases. In this tutorial, we'll demonstrate how to construct a library app using Laravel 11 and MySQL, integrating with PlanetScale, a cloud-native MySQL-compatible database platform offering scalability and high availability features.

This app will feature two main functionalities: authentication and loan management, allowing logged-in users to borrow and return books while enforcing constraints to prevent over-borrowing.

To get started, ensure you have the following prerequisites:

1. Basic understanding of PHP and Laravel 11.
2. PHP version 8.1 or higher installed.
3. Composer installed globally.
4. A free PlanetScale account.

Setting up the PlanetScale database:

1. Navigate to your PlanetScale dashboard and create a new database, specifying a name like "library-app."
2. While the database is being provisioned, you can configure it to automatically copy migration data to new branches by enabling the "Automatically copy migration data" option in the Settings section, ensuring Laravel is selected as the migration framework.
3. Once the database is created, go to the Overview section and click "Get connection strings" to obtain the database authentication credentials. Copy these credentials, as they are only displayed once and are crucial for connecting your application to the database.

With the database set up, let's scaffold the Laravel 11 application:

Open your terminal and run the following command to create a new Laravel 11 application named "library-app":

composer create-project laravel/laravel library-app

This command initializes a new Laravel 11 project in a directory named "library-app." Once the command completes, you can navigate into this directory to begin developing your library app.

Explanation:

• composer create-project laravel/laravel library-app: This Composer command creates a new Laravel project named "library-app" by fetching the necessary dependencies and setting up the project structure.

By following these steps, you'll have a Laravel 11 application ready to be customized for your library management needs, leveraging the power and flexibility of Laravel along with the scalability and reliability of MySQL through PlanetScale.

To connect your Laravel 11 application to your PlanetScale database, you need to update the .env file with the database connection information you obtained earlier. Here is the updated content for the .env file:

DB_CONNECTION=mysql
DB_HOST=<YOUR_DB_HOST>
DB_PORT=3306
DB_DATABASE=<YOUR_DB_DATABASE>
DB_USERNAME=<YOUR_DB_USERNAME>
DB_PASSWORD=<YOUR_DB_PASSWORD>
MYSQL_ATTR_SSL_CA=/etc/ssl/cert.pem

Explanation:

• DB_CONNECTION: Specifies the database connection driver, in this case, MySQL.
• DB_HOST: Replace <YOUR_DB_HOST> with the actual host provided by PlanetScale.
• DB_PORT: The port number for the MySQL connection.
• DB_DATABASE: Replace <YOUR_DB_DATABASE> with the actual database name you created.
• DB_USERNAME: Replace <YOUR_DB_USERNAME> with the actual database username.
• DB_PASSWORD: Replace <YOUR_DB_PASSWORD> with the actual database password.
• MYSQL_ATTR_SSL_CA: Specifies the path to the SSL certificate for secure connections.

After updating the .env file, run the following command to check the connection to the database, create tables, and publish the schema:

php artisan migrate

This command ensures a successful connection to the database, creates the necessary tables, and publishes the schema.

Adding Authentication to your Laravel 11 app

Next, add authentication to your Laravel 11 application using Laravel Breeze. Install it with the following command:

composer require laravel/breeze --dev

Then, install Laravel Breeze with the Blade stack:

php artisan breeze:install

During the installation, choose Blade as the stack and respond with "no" to using dark mode support and Pest.

Update the database and install frontend dependencies:

php artisan migrate
npm install

This command installs and configures the authentication views, routes, controllers, and other resources for your application.

Now, create the models for the application. In addition to the automatically created User model, we need two more models: Book and Loan.

Create the Book model with controller, factory, migration, and seeder using the following command:

php artisan make:model Book -cfms

For the Loan model, create it with controller and migration using:

php artisan make:model Loan -mc

Now, update the migrations for the Book and Loan models. Open the migration files in database/migrations and adjust them as described.

For the create_books_table.php migration, update the up function to include:

Schema::create('books', function (Blueprint $table) {
    $table->id();
    $table->string('title');
    $table->string('author');
    $table->string('year');
    $table->integer('copies_in_circulation');
    $table->timestamps();
});

For the create_loans_table.php migration, update the up function to include:

Schema::create('loans', function (Blueprint $table) {
    $table->id();
    $table->foreignId('book_id');
    $table->foreignId('user_id');
    $table->integer('number_borrowed');
    $table->dateTime('return_date');
    $table->boolean('is_returned')->default(false);
    $table->timestamps();
});

Next, update the BookFactory.php file in database/factories to generate random book data:

return [
    'title' => $this->faker->sentence(),
    'author' => $this->faker->name,
    'year' => $this->faker->year(),
    'copies_in_circulation' => $this->faker->numberBetween(1, 30)
];

Finally, update the BookSeeder.php file in database/seeders to populate the books:

Book::factory()->times(30)->create();

Run the migrations and seed your database with the following command:

php artisan migrate --seed

Now, your PlanetScale database will be populated with 30 new books, and your Laravel 11 application is ready to manage library loans and authentication.

Fillable properties, relationships, helper functions

To enhance your models for better separation of concerns and streamlined controller functions, follow these steps:

User Model (app/Models/User.php):

1. Open app/Models/User.php and add the following import statement at the top of the file:

use Illuminate\Database\Eloquent\Relations\HasMany;

1. Update the model by adding the following functions:

/**
 * Get the active loans for the user.
 *
 * @return \Illuminate\Database\Eloquent\Collection
 */
public function activeLoans()
{
    return $this->loans()->where('is_returned', false)->get();
}

/**
 * Define the relationship with loans.
 *
 * @return \Illuminate\Database\Eloquent\Relations\HasMany
 */
public function loans(): HasMany
{
    return $this->hasMany(Loan::class);
}

These functions establish the relationship between the User model and the Loan model, as a user can have multiple loans. The activeLoans function retrieves only the active loans for the user.

Book Model (app/Models/Book.php):

1. Open app/Models/Book.php and update the code as follows:

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\HasMany;

class Book extends Model
{
    /**
     * Check if the book can be borrowed.
     *
     * @return bool
     */
    public function canBeBorrowed(): bool
    {
        return $this->availableCopies() > 0;
    }

    /**
     * Get the total number of active loans for the book.
     *
     * @return int
     */
    private function activeLoans(): int
    {
        return $this->loans()->where('is_returned', false)->sum('number_borrowed');
    }

    /**
     * Define the relationship with loans.
     *
     * @return \Illuminate\Database\Eloquent\Relations\HasMany
     */
    public function loans(): HasMany
    {
        return $this->hasMany(Loan::class);
    }

    /**
     * Get the number of available copies for borrowing.
     *
     * @return int
     */
    public function availableCopies(): int
    {
        return $this->copies_in_circulation - $this->activeLoans();
    }
}

These updates add functionality to the Book model, including the ability to check if a book can be borrowed (canBeBorrowed), retrieving active loans for the book (activeLoans), and determining the number of available copies for borrowing (availableCopies).

Loan Model (app/Models/Loan.php):

1. Open app/Models/Loan.php and update the code as follows:

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\BelongsTo;

class Loan extends Model
{
    /**
     * The attributes that are mass assignable.
     *
     * @var array
     */
    protected $fillable = [
        'number_borrowed',
        'return_date',
        'book_id',
        'user_id',
    ];

    /**
     * Get the user associated with the loan.
     *
     * @return \Illuminate\Database\Eloquent\Relations\BelongsTo
     */
    public function user(): BelongsTo
    {
        return $this->belongsTo(User::class);
    }

    /**
     * Get the book associated with the loan.
     *
     * @return \Illuminate\Database\Eloquent\Relations\BelongsTo
     */
    public function book(): BelongsTo
    {
        return $this->belongsTo(Book::class);
    }

    /**
     * Mark the loan as returned.
     *
     * @return void
     */
    public function terminate(): void
    {
        $this->is_returned = true;
        $this->save();
    }
}

These updates to the Loan model include specifying the $fillable properties for mass assignment, defining the inverse relationships with the User and Book models (user and book), and adding the terminate method to mark a loan as returned.

By following these steps, your models are now equipped with fillable properties, relationships, and helper functions, contributing to better separation of concerns and more concise controller functions.

• Author: Ahmed Bouchefra Follow @ahmedbouchefra

• Date: 30 Jan 2024