Laravel - Working With Database

Hello there, aspiring developers! I'm excited to take you on a journey through the fascinating world of Laravel and database management. As someone who's been teaching computer science for years, I can assure you that mastering database operations is like learning to ride a bicycle - it might seem daunting at first, but once you get the hang of it, you'll be zooming through your projects with ease!

Connecting to Database

Let's start with the basics - connecting to a database. Think of this as introducing your Laravel application to its new best friend, the database. They need to get acquainted before they can start working together!

Configuration

First things first, we need to tell Laravel where to find our database and how to access it. Laravel makes this super easy with its configuration file. Open up the .env file in your project root, and you'll see something like this:

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=laravel
DB_USERNAME=root
DB_PASSWORD=

These lines are like giving Laravel a map to your database. Let's break it down:

• DB_CONNECTION: This is the type of database you're using. MySQL is the default, but Laravel supports others too.
• DB_HOST: Where your database lives. For local development, it's usually your own computer (127.0.0.1).
• DB_PORT: The door number Laravel should knock on to access the database.
• DB_DATABASE: The name of your database.
• DB_USERNAME and DB_PASSWORD: Your secret handshake to get into the database.

Testing the Connection

Now that we've introduced Laravel to our database, let's make sure they're getting along. We can do this with a simple artisan command:

php artisan tinker

This opens up Laravel's interactive shell. Now, let's try to connect:

DB::connection()->getPdo();

If you see a long string of numbers and letters, congratulations! Your Laravel application and database are now best buddies.

Creating Tables

Now that we're connected, it's time to create some tables. In the database world, tables are like spreadsheets where we store our data.

Migrations

Laravel uses something called "migrations" to create and modify tables. Think of migrations as a time machine for your database - they allow you to travel back and forth in your database's history.

To create a migration, use this artisan command:

php artisan make:migration create_users_table

This creates a new file in the database/migrations folder. Open it up, and you'll see something like this:

public function up()
{
    Schema::create('users', function (Blueprint $table) {
        $table->id();
        $table->string('name');
        $table->string('email')->unique();
        $table->timestamp('email_verified_at')->nullable();
        $table->string('password');
        $table->rememberToken();
        $table->timestamps();
    });
}

This is like a blueprint for your table. Let's break it down:

• $table->id(): Creates an auto-incrementing ID column.
• $table->string('name'): Creates a column for storing names.
• $table->string('email')->unique(): Creates a unique email column.
• $table->timestamp('email_verified_at')->nullable(): Creates a nullable timestamp column.
• $table->string('password'): Creates a column for storing passwords.
• $table->rememberToken(): Creates a column for storing "remember me" tokens.
• $table->timestamps(): Creates created_at and updated_at columns.

To actually create this table in your database, run:

php artisan migrate

And voila! Your table is created.

Inserting Data

Now that we have a table, let's put some data in it. There are a few ways to do this in Laravel.

Using Eloquent

Eloquent is Laravel's built-in ORM (Object-Relational Mapping). It's like a translator between your PHP code and your database. Here's how you can use it to insert data:

use App\Models\User;

$user = new User;
$user->name = 'John Doe';
$user->email = '[email protected]';
$user->password = bcrypt('password');
$user->save();

In this example, we're creating a new User object, setting its properties, and then saving it to the database. It's as simple as that!

Using Query Builder

If you prefer a more SQL-like approach, you can use Laravel's Query Builder:

DB::table('users')->insert([
    'name' => 'John Doe',
    'email' => '[email protected]',
    'password' => bcrypt('password'),
]);

This method is a bit more direct - we're telling Laravel exactly which table to insert into and what data to use.

Retrieving Data

Now that we've put data into our database, let's learn how to get it back out.

Using Eloquent

To retrieve all users:

$users = User::all();

To find a specific user:

$user = User::find(1);

To retrieve users with conditions:

$users = User::where('name', 'John Doe')->get();

Using Query Builder

To retrieve all users:

$users = DB::table('users')->get();

To find a specific user:

$user = DB::table('users')->where('id', 1)->first();

To retrieve users with conditions:

$users = DB::table('users')->where('name', 'John Doe')->get();

Updating Data

Updating data is just as easy as inserting it. Let's look at a few methods.

Using Eloquent

$user = User::find(1);
$user->name = 'Jane Doe';
$user->save();

Using Query Builder

DB::table('users')
    ->where('id', 1)
    ->update(['name' => 'Jane Doe']);

Deleting Data

Finally, let's learn how to remove data from our database.

Using Eloquent

$user = User::find(1);
$user->delete();

Using Query Builder

DB::table('users')->where('id', 1)->delete();

And there you have it! You've just learned the basics of working with databases in Laravel. Remember, practice makes perfect. Don't be afraid to experiment and try out different queries. Before you know it, you'll be a database wizard!

Here's a quick reference table of the methods we've covered:

Operation	Eloquent	Query Builder
Insert	$user = new User; $user->save();	DB::table('users')->insert([]);
Retrieve All	User::all();	DB::table('users')->get();
Retrieve One	User::find(1);	DB::table('users')->where('id', 1)->first();
Update	$user->save();	DB::table('users')->where('id', 1)->update([]);
Delete	$user->delete();	DB::table('users')->where('id', 1)->delete();

Happy coding, future database masters!

Credits: Image by storyset