how do I mock the DB facade in laravel?

Question

I'm writing my unit tests and by default they should not hit the database. by general rule I always use eloquent to get the results, but some more complex queries I have to use the raw DB

I have this function:

public function GetPassword($email)
{
    $result = DB::table('vin_user_active')
        ->select(
            "vin_user_active.id",
            "vin_user_active.password",
            DB::raw('COALESCE(
                vin_user_active.pass_update_date <=
                CURRENT_TIMESTAMP -
                INTERVAL vin_org_active.password_expiration_days DAY, 0
        ) AS password_expired')
        )
        ->join('vin_org_active', "vin_user_active.org", "=", "vin_org_active.id")
        ->where("email", "=", $email)
        ->first();

    return $result;
}

Right now I'm mocking the GetPassword function, but 1. I think the function should be private, not public. 2. The coverage is just %50 because is skipping the whole function.

How would I mock it? right now I have this

$this->db =Mockery::mock('Illuminate\Database\Query\Builder')->makePartial();

    DB::shouldReceive('table')
        ->once()
        ->with("vin_user_active")
        ->andReturn($this->db);

    DB::shouldReceive('raw')
        ->once()
        ->with(Mockery::any())
        ->andReturn(true);

    DB::shouldReceive('select')
        ->once()
        ->with("vin_user_active.id,
            vin_user_active.password,
            DB::raw('COALESCE(
                vin_user_active.pass_update_date <=
                CURRENT_TIMESTAMP -
                INTERVAL vin_org_active.password_expiration_days DAY, 0
        ) AS password_expired'")
        ->andReturn($this->db);

I honestly have no idea what I'm doing, I've never mocked so many levels of function calls.

any idea?

Condemn

You should NOT mock laravel facades, also, you can hit the database in your tests, only thing to take note at is to use transactions so that your db queries are not persisted. – Premonition

For more info check, laracasts.com/series/phpunit-testing-in-laravel – Premonition

I try to stay away from touching the database in unit testing (apart from that I have my integration tests that uses database transactions) – Condemn

I understand, but what is the point of mocking the database and wasting all that time to stay away from touching the database? – Premonition

@MahmoudTantawy if you have thousands of tests, you can not wait until real queries are running, you need to mock them like any other side effects (API calls, etc), so it is bad to touch real database in tests. – Alphabetical

@MahmoudTantawy I have an issue with MySQL sort (ie. its not defined). The data returned may not always be in the same order if I use MySQL so I want to mock the data in a way (sorted) that replicates the bug so that I can confidently verify that its fixed. I don't suppose you got anywhere this this Daniel? – Waken

So much one can learn in a year, now i see how my comment was kinda stupid. – Premonition

@Waken to replicate randomness is hard, but may be this will let you understand more #6663337 – Premonition

Answer 1

Actually it was simple enough

    DB::shouldReceive("raw")
        ->set('query', 'query test')
        ->andReturn(true);

Condemn

Answer 2

Quite late answer but there you go:

DB::spy()

Landry

This answer could benefit from giving a little more information. – Ticking