Working with the database

Set up your database with migrations and learn Laravel's database tools. Create tables programmatically and explore different ways to interact with data.

View code on GitHub

View course demo

Alright, we have these sample chirps that we're passing to our view layer. Again, this is our controller—the C of the MVC structure—and we're passing this to our V, our view layer. But these chirps are just hardcoded values that we're passing from this controller. They don't actually exist in the database.

So when you're creating an application, when you're putting it out onto the web, most of the time you need a database in order to save things that can then live forever—or at least until someone deletes their chirp.

Laravel's Database Magic

Good news: Laravel already set up a database for you! Remember when you chose SQLite during installation? It's been waiting patiently at database/database.sqlite. SQLite is perfect for learning—it's a real database that lives in a single file, no server required and it works with your local development environment seamlessly.

Step 1: Meet Migrations

Migrations are like version control for your database. Instead of manually creating tables, you write PHP code that describes your database structure. Laravel can then create, modify, or rollback your database changes automatically.

Let's create a migration for our chirps table:

1php artisan make:migration create_chirps_table

php artisan make:migration create_chirps_table

This creates a new file in database/migrations/ with a timestamp and name. Open it up:

 1<?php
 2 
 3use Illuminate\Database\Migrations\Migration;
 4use Illuminate\Database\Schema\Blueprint;
 5use Illuminate\Support\Facades\Schema;
 6 
 7return new class extends Migration
 8{
 9    public function up(): void
10    {
11        Schema::create('chirps', function (Blueprint $table) {
12            $table->id();
13            $table->timestamps();
14        });
15    }
16 
17    public function down(): void
18    {
19        Schema::dropIfExists('chirps');
20    }
21};

<?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('chirps', function (Blueprint $table) {
            $table->id();
            $table->timestamps();
        });
    }

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

Step 2: Design Your Table

Let's add the columns we need for chirps:

1public function up(): void
2{
3    Schema::create('chirps', function (Blueprint $table) {
4        $table->id();
5        $table->foreignId('user_id')->nullable()->constrained()->cascadeOnDelete();
6        $table->string('message', 255);
7        $table->timestamps();
8    });
9}

public function up(): void
{
    Schema::create('chirps', function (Blueprint $table) {
        $table->id();
        $table->foreignId('user_id')->nullable()->constrained()->cascadeOnDelete();
        $table->string('message', 255);
        $table->timestamps();
    });
}

What's happening here:

id() - Creates an auto-incrementing ID (primary key)
foreignId('user_id') - Links each chirp to a user
nullable() - Makes the user_id optional for now (we'll fix this when we add authentication)
constrained() - Creates a foreign key constraint
cascadeOnDelete() - If a user is deleted, delete their chirps too
string('message', 255) - The chirp text (max 255 characters)
timestamps() - Adds created_at and updated_at columns automatically

Note: Making user_id nullable isn't recommended for production, but it lets us see our app working in the browser before we tackle authentication. Laravel gives us a User model out of the box, but to keep things simple, we'll build up to authentication gradually. We'll make this field required when we add user registration and login.

Step 3: Run the Migration

Time to create the table:

1php artisan migrate

php artisan migrate

You'll see output like:

INFO  Running migrations.

2024_01_15_123456_create_chirps_table ................... 28ms DONE

Your database now has a chirps table! 🎉

Step 4: Explore Your Database

Want to see what you just created? You can use any SQLite viewer, but Laravel provides a handy tool called Tinker:

1php artisan tinker

php artisan tinker

Then type:

1\DB::select('SELECT * FROM chirps');

\DB::select('SELECT * FROM chirps');

You'll see an empty array [] because we haven't added any chirps yet. Type exit to leave Tinker.

For a visual approach, tools like TablePlus, DBeaver, or even VS Code extensions can open your database/database.sqlite file and show you the tables.

Step 5: Understanding the Users Table

Notice that user_id column? It references the users table. Good news—Laravel already created this table for you! Check out database/migrations/ and you'll see a create_users_table migration that ran when you first set up the project.

The users table has:

id
name
email
password
remember_token
timestamps

This is why we can link chirps to users—the infrastructure is already there!

Manual Data (Just to See It Work)

Before we move forward, I do want to manually create a chirp with php artisan tinker, mostly to show you that there's an easier way to do it in the next lesson—an easier way to interact with our database, even with the tools that Laravel gives us through models.

But also to show you that Tinker or any way that we're interacting with our database is a great use case for something like AI. Within Laravel Boost, there's tools that allow you to work with Tinker or allow your AI assistants to work with Tinker. You don't need to know all of this syntax for how to interact with the database through Tinker, but it's helpful to know what your AI, or even what specific commands might do.

Let's manually add a test chirp:

1php artisan tinker

php artisan tinker

 1// We're entering into the chirps table for selecting the chirps table
 2// and then inserting a message
 3\DB::table('chirps')->insert([
 4    'user_id' => null,  // Because we don't have a user, we can just leave it off
 5    'message' => 'My first chirp in the database!',
 6    'created_at' => now(),  // Laravel doesn't give these by default when using DB
 7    'updated_at' => now()
 8]);
 9 
10// And we get this true back. So that means that this was successful,
11// this chirp was now entered into the database!
12 
13// Check it worked (all chirps)
14\DB::table('chirps')->get();

// We're entering into the chirps table for selecting the chirps table
// and then inserting a message
\DB::table('chirps')->insert([
    'user_id' => null,  // Because we don't have a user, we can just leave it off
    'message' => 'My first chirp in the database!',
    'created_at' => now(),  // Laravel doesn't give these by default when using DB
    'updated_at' => now()
]);

// And we get this true back. So that means that this was successful,
// this chirp was now entered into the database!

// Check it worked (all chirps)
\DB::table('chirps')->get();

And there we go. Here's our first chirp. Congratulations! You just entered something into the database using a command line tool. Type exit to leave Tinker.

Note: This might seem tedious to interact with the database in this way. The good news is we will explore easier ways to interact (even within Tinker) in the next lesson. Laravel Boost also provides AI Assistants an easy tool to use Tinker within your Laravel application.

Migration Rollbacks

Made a mistake? Migrations can be reversed:

1php artisan migrate:rollback

php artisan migrate:rollback

This runs the down() method, dropping the chirps table. You can then modify your migration and run php artisan migrate again.

Warning: Rolling back will delete any data in those tables!

What We've Built

You now have:

A real database (SQLite)
A chirps table with proper structure
A foreign key relationship to users
Version-controlled database changes

But typing SQL queries is no fun. Next, we'll create a Model that lets us work with chirps using beautiful, expressive PHP code. No more SQL strings!

Pro Tips

Migration files are timestamped - This ensures they run in the correct order
Never edit old migrations - Create new ones to modify existing tables
Keep migrations in version control - Your team needs them too!
php artisan migrate:fresh - Drops all tables and re-runs migrations (careful in production!)

Ready to make working with the database feel like writing poetry? Let's create our Chirp model!