Meeting 14 - Database & Eloquent Deep Dive

Goal: Master Laravel's database layer: configuration, migrations, seeders, factories, Eloquent model features, relationships, querying patterns, scopes, attribute casting, accessors & mutators, soft deletes, transactions, pagination, and performance tips.

1. Overview Layers

Layer	Tool	Purpose
Connection	`config/database.php`	Define DB drivers & connections
Schema	Migrations	Version controlled structure changes
Seed Data	Seeders / Factories	Populate sample / test data
Data Mapping	Eloquent Models	Represent tables & relationships
Querying	Eloquent Builder / Query Builder	Fluent querying API
Performance	Indexes / Caching / Batching	Optimize big workloads

2. Configuration

config/database.php excerpt:

php

'default' => env('DB_CONNECTION', 'mysql'),
'connections' => [
  'mysql' => [
    'driver' => 'mysql',
    'host' => env('DB_HOST','127.0.0.1'),
    'port' => env('DB_PORT','3306'),
    'database' => env('DB_DATABASE','forge'),
    'username' => env('DB_USERNAME','forge'),
    'password' => env('DB_PASSWORD',''),
    'unix_socket' => env('DB_SOCKET',''),
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    'prefix' => '',
    'strict' => true,
    'engine' => null,
  ],
],

'default' => env('DB_CONNECTION', 'mysql'),
'connections' => [
  'mysql' => [
    'driver' => 'mysql',
    'host' => env('DB_HOST','127.0.0.1'),
    'port' => env('DB_PORT','3306'),
    'database' => env('DB_DATABASE','forge'),
    'username' => env('DB_USERNAME','forge'),
    'password' => env('DB_PASSWORD',''),
    'unix_socket' => env('DB_SOCKET',''),
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    'prefix' => '',
    'strict' => true,
    'engine' => null,
  ],
],

Multiple connections: define another (e.g., reporting) and specify at runtime: Model::on('reporting')->get() or DB::connection('reporting')->select(...).

3. Migrations

Create migration:

bash

php artisan make:migration create_posts_table

php artisan make:migration create_posts_table

Sample migration:

php

Schema::create('posts', function (Blueprint $table) {
  $table->id();
  $table->foreignId('user_id')->constrained()->cascadeOnDelete();
  $table->string('title');
  $table->text('body');
  $table->timestamps();
  $table->softDeletes(); // adds deleted_at
});

Schema::create('posts', function (Blueprint $table) {
  $table->id();
  $table->foreignId('user_id')->constrained()->cascadeOnDelete();
  $table->string('title');
  $table->text('body');
  $table->timestamps();
  $table->softDeletes(); // adds deleted_at
});

Run & rollback:

bash

php artisan migrate
php artisan migrate:rollback

php artisan migrate
php artisan migrate:rollback

Batch rollback last 3: php artisan migrate:rollback --step=3. Fresh rebuild: php artisan migrate:fresh --seed.

4. Seeders & Factories

Seeder:

bash

php artisan make:seeder PostSeeder

php artisan make:seeder PostSeeder

database/seeders/PostSeeder.php:

php

public function run(): void
{
  \App\Models\Post::factory(50)->create();
}

public function run(): void
{
  \App\Models\Post::factory(50)->create();
}

Factory:

bash

php artisan make:factory PostFactory --model=Post

php artisan make:factory PostFactory --model=Post

database/factories/PostFactory.php excerpt:

php

public function definition(): array
{
  return [
    'user_id' => User::factory(),
    'title' => fake()->sentence(),
    'body' => fake()->paragraph(5),
  ];
}

public function definition(): array
{
  return [
    'user_id' => User::factory(),
    'title' => fake()->sentence(),
    'body' => fake()->paragraph(5),
  ];
}

Run all seeders: php artisan db:seed (calls DatabaseSeeder). Combine: migrate --seed.

5. Eloquent Model Essentials

app/Models/Post.php:

php

class Post extends Model
{
  use HasFactory, SoftDeletes;

  protected $fillable = ['user_id','title','body'];
  protected $casts = [ 'published_at' => 'datetime', 'meta' => 'array' ];
  protected $hidden = ['internal_notes'];
  protected $appends = ['excerpt'];

  // Accessor (Laravel 10+ method name style)
  protected function excerpt(): Attribute
  {
    return Attribute::get(fn() => Str::limit(strip_tags($this->body), 120));
  }

  // Scope
  public function scopePublished($query)
  {
    return $query->whereNotNull('published_at');
  }
}

class Post extends Model
{
  use HasFactory, SoftDeletes;

  protected $fillable = ['user_id','title','body'];
  protected $casts = [ 'published_at' => 'datetime', 'meta' => 'array' ];
  protected $hidden = ['internal_notes'];
  protected $appends = ['excerpt'];

  // Accessor (Laravel 10+ method name style)
  protected function excerpt(): Attribute
  {
    return Attribute::get(fn() => Str::limit(strip_tags($this->body), 120));
  }

  // Scope
  public function scopePublished($query)
  {
    return $query->whereNotNull('published_at');
  }
}

Disable timestamps: public $timestamps = false; Table name override: protected $table = 'blog_posts'; Primary key override: protected $primaryKey = 'uuid'; + add $incrementing = false; $keyType = 'string';

6. Relationships

Type	Example	Definition (Model)	Inverse
One To Many	User -> Post	`public function posts(){ return $this->hasMany(Post::class); }`	`belongsTo(User::class)`
One To One	User -> Profile	`hasOne(Profile::class)`	`belongsTo(User::class)`
Many To Many	Post <-> Tag	`belongsToMany(Tag::class)`	`belongsToMany(Post::class)`
Polymorphic One	Image -> (User, Post)	`morphOne(Image::class,'imageable')`	`morphTo()`
Polymorphic Many	Comment -> (Post, Video)	`morphMany(Comment::class,'commentable')`	`morphTo()`
Polymorphic ManyToMany	Tag <-> (Post, Video)	`morphToMany(Tag::class,'taggable')`	`morphedByMany(Post::class,'taggable')`

Diagram: Eloquent Relations

Pivot extras:

php

return $this->belongsToMany(Tag::class)->withTimestamps()->withPivot('added_by');

return $this->belongsToMany(Tag::class)->withTimestamps()->withPivot('added_by');

Query with constraints:

php

User::whereHas('posts', fn($q) => $q->published())->get();

User::whereHas('posts', fn($q) => $q->published())->get();

Eager load counts:

php

User::withCount(['posts as published_posts_count' => fn($q) => $q->published()])->get();

User::withCount(['posts as published_posts_count' => fn($q) => $q->published()])->get();

7. Query Builder vs Eloquent Builder

Raw Query Builder:

php

DB::table('posts')->where('active',1)->orderBy('created_at','desc')->limit(10)->get();

DB::table('posts')->where('active',1)->orderBy('created_at','desc')->limit(10)->get();

Eloquent (preferred for models):

php

Post::query()->where('active',1)->latest()->take(10)->get();

Post::query()->where('active',1)->latest()->take(10)->get();

Chunking large sets:

php

Post::where('active',1)->chunk(200, function($posts){
  foreach($posts as $post){ /* process */ }
});

Post::where('active',1)->chunk(200, function($posts){
  foreach($posts as $post){ /* process */ }
});

Streaming via cursor (lower memory):

php

foreach(Post::where('active',1)->cursor() as $post){ /* ... */ }

foreach(Post::where('active',1)->cursor() as $post){ /* ... */ }

8. Global & Local Scopes

Local scope example already shown (scopePublished). Use: Post::published()->get().

Global scope:

php

class PublishedScope implements Scope
{
  public function apply(Builder $builder, Model $model): void
  {
    $builder->whereNotNull($model->getTable().'.published_at');
  }
}

class Post extends Model
{
  protected static function booted()
  {
    static::addGlobalScope(new PublishedScope);
  }
}

class PublishedScope implements Scope
{
  public function apply(Builder $builder, Model $model): void
  {
    $builder->whereNotNull($model->getTable().'.published_at');
  }
}

class Post extends Model
{
  protected static function booted()
  {
    static::addGlobalScope(new PublishedScope);
  }
}

Remove global scope temporarily: Post::withoutGlobalScope(PublishedScope::class)->get();

9. Attribute Casting

Cast	Purpose	Example
`datetime`	Carbon date handling	`'published_at' => 'datetime'`
`immutable_datetime`	CarbonImmutable	`'deleted_at' => 'immutable_datetime'`
`array`	JSON to array	`'meta' => 'array'`
`encrypted`	Auto encrypt/decrypt	`'secret' => 'encrypted'`
`asCollection`	JSON -> collection	Custom caster
`decimal:2`	Format decimals	`'price' => 'decimal:2'`

Custom cast class (implements CastsAttributes):

php

class MoneyCast implements CastsAttributes
{
  public function get($model, string $key, $value, array $attributes)
  { return $value / 100; }
  public function set($model, string $key, $value, array $attributes)
  { return (int) round($value * 100); }
}

class MoneyCast implements CastsAttributes
{
  public function get($model, string $key, $value, array $attributes)
  { return $value / 100; }
  public function set($model, string $key, $value, array $attributes)
  { return (int) round($value * 100); }
}

Usage: protected $casts = ['price' => MoneyCast::class];

10. Accessors & Mutators (Modern Syntax)

php

protected function title(): Attribute
{ return Attribute::make( get: fn($v) => Str::title($v), set: fn($v) => strtolower($v) ); }

protected function title(): Attribute
{ return Attribute::make( get: fn($v) => Str::title($v), set: fn($v) => strtolower($v) ); }

Legacy style (still works):

php

public function getTitleAttribute($value){ return Str::title($value); }
public function setTitleAttribute($value){ $this->attributes['title'] = strtolower($value); }

public function getTitleAttribute($value){ return Str::title($value); }
public function setTitleAttribute($value){ $this->attributes['title'] = strtolower($value); }

11. Soft Deletes

Enable in migration ($table->softDeletes();) + model uses SoftDeletes trait. Querying:

php

Post::withTrashed()->find($id);
Post::onlyTrashed()->where('title','like','Draft%')->get();
Post::find($id)->restore();
Post::find($id)->forceDelete();

Post::withTrashed()->find($id);
Post::onlyTrashed()->where('title','like','Draft%')->get();
Post::find($id)->restore();
Post::find($id)->forceDelete();

12. Transactions (Why They Matter)

Transactions bundle multiple SQL statements into an "all-or-nothing" unit of work. If any step fails, the database reverts to the state before the transaction began. This prevents partial writes (e.g., order created but line items missing, wallet deducted but invoice not recorded) that can corrupt business invariants.

12.1 ACID Principles

Principle	Meaning	Practical Example
Atomicity	All statements commit or none do	Order + OrderItems + Payment row all succeed or all rollback
Consistency	DB moves from one valid state to another	Constraints (FK, unique) always satisfied
Isolation	Concurrent transactions do not interfere incorrectly	Two users updating same balance don't corrupt it
Durability	Once committed, data persists even after crashes	Payment record remains after server reboot

12.2 Laravel Usage Patterns

Basic closure form (auto commit / rollback):

php

DB::transaction(function () use ($request) {
  $order = Order::create($request->validated());
  $order->items()->createMany($this->buildItems($request));
  $order->recalculateTotals();
});

DB::transaction(function () use ($request) {
  $order = Order::create($request->validated());
  $order->items()->createMany($this->buildItems($request));
  $order->recalculateTotals();
});

Manual form when you need finer granularity:

php

DB::beginTransaction();
try {
  $wallet->debit($amount);
  $transfer = $wallet->transfers()->create([...]);
  DB::commit();
} catch (Throwable $e) {
  DB::rollBack();
  report($e);
  throw $e; // rethrow so caller knows it failed
}

DB::beginTransaction();
try {
  $wallet->debit($amount);
  $transfer = $wallet->transfers()->create([...]);
  DB::commit();
} catch (Throwable $e) {
  DB::rollBack();
  report($e);
  throw $e; // rethrow so caller knows it failed
}

Nested logic: if you call DB::transaction inside another, Laravel uses savepoints (where supported) so inner failures roll back to the savepoint instead of the outer root.

12.3 Common Use Cases

Financial operations (credits, debits, wallet top-up).
Inventory reservation & order creation.
Multi-table writes (user + profile + roles assignment).
Event sourcing snapshots + events append.
Generating sequential numbers with locking.

12.4 Isolation Levels (RDBMS Concept)

Most MySQL installations default to REPEATABLE READ; Postgres often READ COMMITTED. Phenomena to avoid:

Phenomenon	Description	Mitigation
Dirty Read	Read uncommitted data	Use READ COMMITTED or higher
Non-Repeatable Read	Same row returns different values in one tx	REPEATABLE READ
Phantom Read	New rows appear in range scans	SERIALIZABLE or explicit locking

You can set isolation per transaction (MySQL example):

php

DB::statement('SET TRANSACTION ISOLATION LEVEL READ COMMITTED');
DB::transaction(function(){ /* ... */ });

DB::statement('SET TRANSACTION ISOLATION LEVEL READ COMMITTED');
DB::transaction(function(){ /* ... */ });

(Keep explicit isolation changes rare; rely on sensible defaults.)

12.5 Locking Rows

When updating counters / balances:

php

DB::transaction(function(){
  $wallet = Wallet::where('id', 5)->lockForUpdate()->first();
  $wallet->balance -= 1000;
  $wallet->save();
});

DB::transaction(function(){
  $wallet = Wallet::where('id', 5)->lockForUpdate()->first();
  $wallet->balance -= 1000;
  $wallet->save();
});

lockForUpdate() prevents other transactions from modifying the same row until commit (pessimistic lock). Alternatively use optimistic locking (custom version column + compare & retry) for high-read, low-write contention.

12.6 Handling Deadlocks & Retries

Deadlocks can occur with concurrent different update orders. Pattern:

php

function withRetry(int $times, Closure $callback) {
  beginning:
  try { return DB::transaction($callback); }
  catch (Throwable $e) {
    if ($times-- > 0 && str_contains($e->getMessage(), 'Deadlock')) {
      usleep(random_int(10_000, 100_000));
      goto beginning; // simple retry; or loop
    }
    throw $e;
  }
}

function withRetry(int $times, Closure $callback) {
  beginning:
  try { return DB::transaction($callback); }
  catch (Throwable $e) {
    if ($times-- > 0 && str_contains($e->getMessage(), 'Deadlock')) {
      usleep(random_int(10_000, 100_000));
      goto beginning; // simple retry; or loop
    }
    throw $e;
  }
}

Use sparingly; better to order updates consistently (e.g., always lock rows by ascending primary key).

12.7 Events After Commit

Model events fire inside the transaction; dispatching jobs that depend on committed data risks race conditions. Use afterCommit() for jobs:

php

DB::transaction(function(){
  $order = Order::create([...]);
  SendOrderEmail::dispatch($order)->afterCommit();
});

DB::transaction(function(){
  $order = Order::create([...]);
  SendOrderEmail::dispatch($order)->afterCommit();
});

Or globally in a model event: SendOrderEmail::dispatch($order)->afterCommit();

12.8 Outbox Pattern (Integrations)

To ensure external side-effects (e.g., publish to Kafka) are not lost:

Inside the transaction, insert into an outbox table (status=pending, payload=JSON).
Commit.
Separate worker polls outbox, sends messages, marks processed.

This guarantees durability even if the app crashes after commit but before calling an API.

12.9 Anti‑Patterns / Pitfalls

Pitfall	Issue	Better Approach
Long-running transactions	Holds locks; increases contention	Move slow I/O (HTTP calls) outside transaction
Doing external API calls inside transaction	If API hangs, DB locks persist	Persist minimal state, commit, then dispatch job
Using `env()` / config changes mid-transaction	Irrelevant but signals mixing concerns	Keep transaction logic pure DB state changes
Catching & ignoring exceptions	Silent data corruption	Always rethrow or handle explicitly
Large batch writes without batching	Transaction log bloat	Chunk into smaller transactions

12.10 Quick Decision Guide

Need	Tool
Multiple dependent writes	Wrap in `DB::transaction`
Prevent concurrent modification	`lockForUpdate()` inside transaction
High read / low contention	Optimistic version check (manual)
External side effects reliability	Outbox + worker
Guarantee post‑commit job	`->afterCommit()` on job

12.11 Summary

Transactions protect invariants. Always ask: "What if step 3 fails after step 2 succeeded?" If the answer is "data inconsistency", wrap it in a transaction.

13. Pagination & Cursor Pagination

Standard:

php

Post::paginate(15);          // length-aware
Post::simplePaginate(50);    // simpler, faster for large offsets

Post::paginate(15);          // length-aware
Post::simplePaginate(50);    // simpler, faster for large offsets

Cursor pagination (no large OFFSET cost):

php

Post::orderBy('id')->cursorPaginate(50);

Post::orderBy('id')->cursorPaginate(50);

14. Performance Tips

Area	Tip
N+1 Queries	Use `with()` eager loading & debug with Telescope / Laravel Debugbar
Large Exports	Use chunk / cursor / queue jobs
Index Usage	Add DB indexes for foreign keys & frequently filtered columns
Caching	Cache expensive aggregates (`Cache::remember()`)
Select Columns	Limit columns: `Post::select('id','title')`
Batch Inserts	Use `insert([...])` for large seed operations
Queue Processing	Offload heavy work (emails, reports)
Observers	Avoid heavy logic in model events (delegate to jobs)

15. Putting It Together (Mini Flow)

Request -> Controller -> Post::published()->with('user','tags')->paginate(15)
        -> Transform (Resource / DTO) -> JSON / Blade View

Request -> Controller -> Post::published()->with('user','tags')->paginate(15)
        -> Transform (Resource / DTO) -> JSON / Blade View

Resource example:

php

class PostResource extends JsonResource
{
  public function toArray($request)
  {
    return [
      'id' => $this->id,
      'title' => $this->title,
      'excerpt' => $this->excerpt,
      'author' => $this->user->name,
      'tags' => $this->tags->pluck('name'),
      'published_at' => optional($this->published_at)->toIso8601String(),
    ];
  }
}

class PostResource extends JsonResource
{
  public function toArray($request)
  {
    return [
      'id' => $this->id,
      'title' => $this->title,
      'excerpt' => $this->excerpt,
      'author' => $this->user->name,
      'tags' => $this->tags->pluck('name'),
      'published_at' => optional($this->published_at)->toIso8601String(),
    ];
  }
}

Use in controller:

php

return PostResource::collection(Post::published()->with('user','tags')->paginate());

return PostResource::collection(Post::published()->with('user','tags')->paginate());

16. Summary

Eloquent abstracts common CRUD, but performance and correctness rely on good schema design, indexes, efficient querying, and clear separation (resources, services, jobs). Use scopes & casts to keep controllers thin, and always measure before optimizing.

Meeting 14 - Database & Eloquent Deep Dive ​

1. Overview Layers ​

2. Configuration ​

3. Migrations ​

4. Seeders & Factories ​

5. Eloquent Model Essentials ​

6. Relationships ​

7. Query Builder vs Eloquent Builder ​

8. Global & Local Scopes ​

9. Attribute Casting ​

10. Accessors & Mutators (Modern Syntax) ​

11. Soft Deletes ​

12. Transactions (Why They Matter) ​

12.1 ACID Principles ​

12.2 Laravel Usage Patterns ​

12.3 Common Use Cases ​

12.4 Isolation Levels (RDBMS Concept) ​

12.5 Locking Rows ​

12.6 Handling Deadlocks & Retries ​

12.7 Events After Commit ​

12.8 Outbox Pattern (Integrations) ​

12.9 Anti‑Patterns / Pitfalls ​

12.10 Quick Decision Guide ​

12.11 Summary ​

13. Pagination & Cursor Pagination ​

14. Performance Tips ​

15. Putting It Together (Mini Flow) ​

16. Summary ​

Meeting 14 - Database & Eloquent Deep Dive

1. Overview Layers

2. Configuration

3. Migrations

4. Seeders & Factories

5. Eloquent Model Essentials

6. Relationships

7. Query Builder vs Eloquent Builder

8. Global & Local Scopes

9. Attribute Casting

10. Accessors & Mutators (Modern Syntax)

11. Soft Deletes

12. Transactions (Why They Matter)

12.1 ACID Principles

12.2 Laravel Usage Patterns

12.3 Common Use Cases

12.4 Isolation Levels (RDBMS Concept)

12.5 Locking Rows

12.6 Handling Deadlocks & Retries

12.7 Events After Commit

12.8 Outbox Pattern (Integrations)

12.9 Anti‑Patterns / Pitfalls

12.10 Quick Decision Guide

12.11 Summary

13. Pagination & Cursor Pagination

14. Performance Tips

15. Putting It Together (Mini Flow)

16. Summary