Practice: Change DB Structure in "Live" Project

In this lesson, you will see an example of restructuring the database.

Imagine a scenario:

1. You have Posts that belong to one Category
2. At some point, you decide to have multiple categories

So, from one-to-many to many-to-many. But the project is already live. What should you do?

Step 1: What Changes Are Needed?

First, let's identify the behavior changes.

Change 1. When viewing a post, instead of showing a single category, it should list all categories with a foreach loop.

Change 2. When showing posts by a category, the query should also be changed.

Where do we start, and how do we do it safely?

Step 2: Automated Tests

Let's start with a different question: "How will you test everything after you make the changes in the code?"

To answer that question, I have a personal rule of thumb. For making any big structure refactorings in a big project: first, write automated tests.

You need to write tests of all the features and how they should work after the change. With this, you won't need to test everything manually because you will totally forget something.

So, the plan is this:

1. Write automated feature tests for the current structure, ensure that those tests are green and successful
2. Change the relationship and everything in the code. Then, those automated tests should fail. That's expected.
3. And then, we need to change those tests to represent a new reality.

So if in the test the article is created with belongsTo relationship, it should be created with belongsToMany now. However, the scenario in the test should be the same. We expect to see the list of posts.

When the updated tests are green and successful, we are confident that we did an okay job. At least it minimizes the risks.

Even if you have no tests in that project, it's worth writing them specifically for this case.

If you haven't written automated tests before, we have a few courses, articles, and videos related to testing.

In this lesson, we won't be writing any tests, I just wanted to emphasize the idea of what you should do, as a part of the restructuring plan.

Step 3: DB Structure Change

The next step is to actually change the data. So first, we need to create a pivot table with article categories, and then we need to migrate the data.

In this case, it's just one SQL query inside of migration. It is inserting into category_post by selecting everything from the posts table. And that's it the data is migrated. Then, the third thing we need to do is drop that column in category_id.

This is an example of one migration in Laravel; you can do whatever sequence of actions you need.

database/migrations/xxx_create_category_post_table.php:

public function up(): void
{
    Schema::create('category_post', function (Blueprint $table) {
        $table->foreignId('category_id')->constrained();
        $table->foreignId('post_id')->constrained();
    });
 
    DB::insert('INSERT INTO category_post (category_id, post_id) SELECT category_id, id from posts');
 
    Schema::table('posts', function (Blueprint $table) {
        $table->dropForeign(['category_id']);
        $table->dropColumn('category_id');
    });
}

Check if your database driver supports every method used in the migration.

Now, if we check the database, the category_post is created with the data, and in the posts table, there is no category_id field.

Step 4: Code Change

We changed the database. Now, we must change the code. First, the relationships in the Models.

app/Models/Post.php:

use Illuminate\Database\Eloquent\Relations\BelongsToMany;
 
class Post extends Model
{
    protected $fillable = [
        'title',
        'category_id', 
    ];
 
    public function category(): BelongsTo 
    {
        return $this->belongsTo(Category::class);
    }
 
    public function categories(): BelongsToMany 
    {
        return $this->belongsToMany(Category::class);
    }
}

app/Models/Category.php:

use Illuminate\Database\Eloquent\Relations\BelongsToMany;
 
class Category extends Model
{
    use HasFactory;
 
    protected $fillable = [
        'name',
    ];
 
    public function posts(): HasMany 
    {
        return $this->hasMany(Post::class);
    }
 
    public function posts(): BelongsToMany 
    {
        return $this->belongsToMany(Post::class);
    }
}

Then, we must change how we get a list of posts by category.

use App\Models\Post;
use App\Models\Category;
use Illuminate\Contracts\View\View;
use Illuminate\Database\Eloquent\Builder;
 
class PostController extends Controller
{
    public function index(): View
    {
        $posts = Post::with('category') 
            ->when(request('category'), function (Builder $query) {
                return $query->where('category_id', request('category'));
            })->get(); 
 
        $posts = Post::with('categories') 
            ->when(request('category'), function (Builder $query) {
                return $query->whereRelation('categories','category_id', request('category'));
            })->get(); 
 
        $categories = Category::all();
 
        return view('posts.index', compact('posts', 'categories'));
    }
 
    // ...
}

In the View where you show a category now, it must be a foreach loop of categories.

// Other layout styles...
 
@foreach($posts as $post)
    <div class="pt-4">
        <div>
            <a href="{{ route('posts.show', $post) }}" class="text-xl font-semibold uppercase hover:underline">{{ $post->title }}</a>
        </div>
        <div>
            <span class="font-semibold">Category:</span> {{ $post->category->name }} 
            @foreach($post->categories as $category) 
                <span class="rounded-md bg-violet-200 text-violet-600 text-xs px-2 py-1">{{ $category->name }}</span>
            @endforeach 
        </div>
        <div>
            {{ str($post->body)->limit() }}
        </div>
    </div>
@endforeach
 
// Other layout styles...

The same goes for when showing a single post.

// Other layout styles...
 
<div class="p-6 text-gray-900">
    <div>
        <span class="font-semibold">Category:</span> {{ $post->category->name }} 
        @foreach($post->categories as $category) 
            <span class="rounded-md bg-violet-200 text-violet-600 text-xs px-2 py-1">{{ $category->name }}</span>
        @endforeach 
    </div>
 
    <div class="mt-4">
        {{ $post->body }}
    </div>
</div>
 
// Other layout styles...

After manually assigning a second category to the post, it is also shown.

And that's it! Ideally, the tests for this refactor should be green by now.