Database Structure and Migrations

We have built static pages. Now, let's talk about the backend, database structure and data, and how Laravel deals with that.

You don't need to manually create DB tables with columns. For that, Laravel uses a concept called migrations. You describe your migration schema in a migration file and then run the migrations, creating tables with columns.

The migrations are added in the database/migrations folder. When you create a new Laravel project, there are three migration files. The only important Migration file when learning Laravel is the first one where the Users table is created. Usually, one Migration file would create one table. The framework creates three tables in one migration file, all corresponding to users.

database/migrations/0001_01_01_000000_create_users_table.php:

public function up(): void
{
    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();
    });
 
    Schema::create('password_reset_tokens', function (Blueprint $table) {
        $table->string('email')->primary();
        $table->string('token');
        $table->timestamp('created_at')->nullable();
    });
 
    Schema::create('sessions', function (Blueprint $table) {
        $table->string('id')->primary();
        $table->foreignId('user_id')->nullable()->index();
        $table->string('ip_address', 45)->nullable();
        $table->text('user_agent')->nullable();
        $table->longText('payload');
        $table->integer('last_activity')->index();
    });
}

They are pretty readable, even if you don't know how Migrations work. First, you define a table name and all the columns inside a closure.

Let's see what fields are inside the users table:

• id field, which is automatically a primary key and auto-incremented
• String for name and email fields. Also, email is unique, so it cannot be repeated.
• The rememberToken() is a Laravel-specific field for checking the "Remember me" checkbox.
• The timestamps() is a shortcut for two fields: created_at and updated_at. They are filled in automatically. We will see that later.

Switch to MySQL and Execute Migrations

If you use the default database driver SQLite, migrations are run automatically after creating a new project, as you saw in the first lesson about installation:

But let's try to use MySQL and create a fresh database.

I've created an empty MySQL DB manually in an SQL client called TablePlus:

How do we connect to it from Laravel?

DB configuration options are stored in the config/database.php file. We can see the connections key with different available DB drivers out-of-the-box: SQLite, MySQL, MariaDB, PostgreSQL, and SQL Server.

config/database.php:

return [
 
    'default' => env('DB_CONNECTION', 'sqlite'),
 
    'connections' => [
 
        'sqlite' => [
            'driver' => 'sqlite',
            'url' => env('DB_URL'),
            'database' => env('DB_DATABASE', database_path('database.sqlite')),
 
            // ... other settings
        ],
 
        'mysql' => [
            'driver' => 'mysql',
            'url' => env('DB_URL'),
            'host' => env('DB_HOST', '127.0.0.1'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE', 'laravel'),
            'username' => env('DB_USERNAME', 'root'),
            'password' => env('DB_PASSWORD', ''),
 
            // ... other settings
        ],
 
        'mariadb' => [
            'driver' => 'mariadb',
 
            // ... other settings
        ],
 
        'pgsql' => [
            'driver' => 'pgsql',
 
            // ... other settings
        ],
 
        'sqlsrv' => [
            'driver' => 'sqlsrv',
 
            // ... other settings
        ],
 
    ],
];

Pay attention to this config value that defines the default connection:

'default' => env('DB_CONNECTION', 'sqlite'),

This env() helper function takes values from the .env file, which are called environment variables. If the .env value isn't set, the second parameter is the default value, which is sqlite in our case.

This is our current .env file for SQLite, generated after Laravel installation:

.env

DB_CONNECTION=sqlite
# DB_HOST=127.0.0.1
# DB_PORT=3306
# DB_DATABASE=laravel
# DB_USERNAME=root
# DB_PASSWORD=

So, only the connection is specified as sqlite, and all other values are commented out, as they are not needed for SQLite.

If we want to use MySQL instead of SQLite, we should first change the DB_CONNECTION value in the .env to mysql.

Then, uncomment the DB_xxxxx keys and set their values to your DB server details. In my case, locally, it's this:

.env:

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

Warning: this .env file should NOT be pushed to the GitHub repository or published anywhere on the server. It's in .gitignore in the default Laravel. Every environment (local, staging, production) should have its own .env file with different credentials, which are not visible to the public.

Now, Laravel should be working with our MySQL database, which is called project. Let's create DB tables in it from Migration files.

To run migrations, use the artisan command php artisan migrate in the Terminal. This command will run all the migrations from the database/migrations folder.

In the DB client, we can now see all the tables created.

Also, there's a special DB table called migrations, which stores the information about which migrations have already been executed:

So, if we run php artisan migrate again now, we will get a message "Nothing to migrate":

That's because Laravel checks the migrations DB table and tries to execute only new migration files that were not previously executed.

Ok, great, so we've migrated from SQLite to MySQL with all the default Laravel DB tables. Now, it is time to create a new custom DB table.

Create a New Migration File

In our small project, we will work with blog categories and posts.

In this lesson, let's create a Migration for the DB table categories. We can do it using an artisan command php artisan make:migration. Then, as a parameter, we specify "create [table name] table" in quotation marks.

php artisan make:migration "create categories table"

You may also see a few other syntax options, like separating the actions with the underscore symbol instead of words with quotes:

php artisan make:migration create_categories_table

The command creates a new Migration class inside the database/migrations folder with two default columns: id() and timestamps().

database/migrations/xxx_create_categories_table.php:

public function up(): void
{
    Schema::create('categories', function (Blueprint $table) {
        $table->id();
        // ... Your custom fields should go here
        $table->timestamps();
    });
}

For example, a category will have only a name, so we use a string name as a column.

database/migrations/xxx_create_categories_table.php:

public function up(): void
{
    Schema::create('categories', function (Blueprint $table) {
        $table->id();
        $table->string('name'); 
        $table->timestamps();
    });
}

You can check the official Laravel docs for all possible column types and syntax options.

Let's create that categories table in our MySQL DB by running php artisan migrate:

Great, our table is in the DB!

Here's the GitHub commit for this change.

"Undo": Rolling Back Migrations

Every migration class has two methods: up() and down().

database/migrations/xxx_create_categories_table.php:

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
 
return new class extends Migration
{
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        Schema::create('categories', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->timestamps();
        });
    }
 
    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        Schema::dropIfExists('categories');
    }
};

The down() method can be run to roll back the migrations by executing php artisan migrate:rollback. How does that command work?

In the migrations DB table, the executed migrations are saved with their batch number.

Whenever we run php artisan migrate, new records are inserted for all the executed migrations with a new auto-incremented batch number. In the case of categories DB table migration, we have a batch number 2 for it.

So, the php artisan migrate:rollback command will only roll back migrations of the last batch.

In our case, it would only execute the down() method in the migration for the categories table, as the last batch had number 2. So, it would drop the table categories and remove its batch records from the migrations DB table like they never existed.

If you're following along and executing the commands, don't run migrate:rollback on your server. I just wanted to explain how it works. Instead, we will move forward and create more tables in the next lesson.

Migration System Is Too Complex?

This is a personal note. I've seen some people comment that such a migration system is too complex. Instead, you could create tables manually with an SQL client like TablePlus or phpMyAdmin, right?

But what about when you need to deploy to production? Or if you work in a team and need to share the schema of the tables.

Exporting/importing the SQL is very inconvenient. So, migrations help transfer all the DB schema changes and check their history quickly across all environments: local, staging, and production.

So, we've created DB tables. In the next lesson, we will learn how to get data from them with Eloquent ORM and how to show it on the page with Controllers.