Laravel fails to connect into sqlite when in testing and uses the development postgresql settings

user_4706711 0
Viewed 2168

I have the following phpunit test:

namespace  Test\Database\Integration\Repositories;

use Tests\TestCase;
use Illuminate\Foundation\Testing\RefreshDatabase;
use App\Repositories\RoverRepository;
use App\Model\Rover;
use App\Model\Grid;

class RoverRepositoryTest extends TestCase
{
    use RefreshDatabase;

    /**
     * Undocumented variable
     *
     * @var RoverRepository|null
     */
    private $repository=null;

    /**
     * Undocumented variable
     *
     * @var Grid|null;
     */
    private $grid=null;

    public function setUp(): void
    {
        parent::setUp();

        $this->runDatabaseMigrations();

        $grid=factory(Grid::class)->create([
            'width'=>5,
            'height'=>5
        ]);

        $rover=factory(Rover::class, 5)->create([
            'grid_id' => $grid->id,
            'grid_pos_x' => rand(0, $grid->width),
            'grid_pos_y' => rand(0, $grid->height),
        ]);

        $this->grid=$grid;
        //How do I run Migrations and generate the db?

        $this->repository = new RoverRepository();
    }

    public function tearDown(): void
    {
        parent::tearDown();
        //How do I nuke my Database?
    }

   /**
     * Testing Base Search
     *
     * @return void
    */
    public function testBasicSearch(): void
    {
        $this->assertTrue(true);
    }
}

And my phpunit.xml is set as:

<?xml version="1.0" encoding="UTF-8"?>
<phpunit backupGlobals="false"
         backupStaticAttributes="false"
         bootstrap="vendor/autoload.php"
         colors="true"
         convertErrorsToExceptions="true"
         convertNoticesToExceptions="true"
         convertWarningsToExceptions="true"
         processIsolation="false"
         stopOnFailure="false">
    <testsuites>
        <testsuite name="Unit">
            <directory suffix="Test.php">./tests/Unit</directory>
        </testsuite>

        <testsuite name="Feature">
            <directory suffix="Test.php">./tests/Feature</directory>
        </testsuite>
        <testsuite name="Database">
            <directory suffix="Test.php">./tests/Database</directory>
        </testsuite>
    </testsuites>
    <filter>
        <whitelist processUncoveredFilesFromWhitelist="true">
            <directory suffix=".php">./app</directory>
        </whitelist>
    </filter>
    <php>
        <env name="APP_ENV" value="testing"/>
        <env name="BCRYPT_ROUNDS" value="4"/>
        <env name="CACHE_DRIVER" value="array"/>
        <env name="MAIL_DRIVER" value="array"/>
        <env name="QUEUE_CONNECTION" value="sync"/>
        <env name="SESSION_DRIVER" value="array"/>
        <env name="DB_CONNECTION" value="sqlite"/>
        <env name="DATABASE_URL" value="localhost"/>
        <env name="DB_DATABASE" value=":memory:"/>
    </php>
</phpunit>

But for some reason when I launch my test with the command:

1) Test\Database\Integration\Repositories\RoverRepositoryTest::testBasicSearch
Illuminate\Database\QueryException: SQLSTATE[08006] [7] FATAL:  password authentication failed for user "laravel" (SQL: select tablename from pg_catalog.pg_tables where schemaname = 'public')

/var/www/html/vendor/laravel/framework/src/Illuminate/Database/Connection.php:664
/var/www/html/vendor/laravel/framework/src/Illuminate/Database/Connection.php:624
/var/www/html/vendor/laravel/framework/src/Illuminate/Database/Connection.php:333
/var/www/html/vendor/laravel/framework/src/Illuminate/Database/Schema/PostgresBuilder.php:108
/var/www/html/vendor/laravel/framework/src/Illuminate/Database/Schema/PostgresBuilder.php:35
/var/www/html/vendor/laravel/framework/src/Illuminate/Database/Console/Migrations/FreshCommand.php:79
/var/www/html/vendor/laravel/framework/src/Illuminate/Database/Console/Migrations/FreshCommand.php:46
/var/www/html/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php:32
/var/www/html/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php:90
/var/www/html/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php:34
/var/www/html/vendor/laravel/framework/src/Illuminate/Container/Container.php:576
/var/www/html/vendor/laravel/framework/src/Illuminate/Console/Command.php:183
/var/www/html/vendor/symfony/console/Command/Command.php:255
/var/www/html/vendor/laravel/framework/src/Illuminate/Console/Command.php:170
/var/www/html/vendor/symfony/console/Application.php:921
/var/www/html/vendor/symfony/console/Application.php:273
/var/www/html/vendor/symfony/console/Application.php:149
/var/www/html/vendor/laravel/framework/src/Illuminate/Console/Application.php:90
/var/www/html/vendor/laravel/framework/src/Illuminate/Console/Application.php:182
/var/www/html/vendor/laravel/framework/src/Illuminate/Foundation/Console/Kernel.php:275
/var/www/html/vendor/laravel/framework/src/Illuminate/Foundation/Testing/PendingCommand.php:136
/var/www/html/vendor/laravel/framework/src/Illuminate/Foundation/Testing/PendingCommand.php:220
/var/www/html/vendor/laravel/framework/src/Illuminate/Foundation/Testing/Concerns/InteractsWithConsole.php:56
/var/www/html/vendor/laravel/framework/src/Illuminate/Foundation/Testing/RefreshDatabase.php:55
/var/www/html/vendor/laravel/framework/src/Illuminate/Foundation/Testing/RefreshDatabase.php:18
/var/www/html/vendor/laravel/framework/src/Illuminate/Foundation/Testing/TestCase.php:105
/var/www/html/vendor/laravel/framework/src/Illuminate/Foundation/Testing/TestCase.php:72
/var/www/html/tests/Database/Integration/Repositories/RoverRepositoryTest.php:31

Caused by
PDOException: SQLSTATE[08006] [7] FATAL:  password authentication failed for user "laravel"

/var/www/html/vendor/laravel/framework/src/Illuminate/Database/Connectors/Connector.php:70
/var/www/html/vendor/laravel/framework/src/Illuminate/Database/Connectors/Connector.php:46
/var/www/html/vendor/laravel/framework/src/Illuminate/Database/Connectors/PostgresConnector.php:33
/var/www/html/vendor/laravel/framework/src/Illuminate/Database/Connectors/ConnectionFactory.php:182
/var/www/html/vendor/laravel/framework/src/Illuminate/Database/Connection.php:918
/var/www/html/vendor/laravel/framework/src/Illuminate/Database/Connection.php:943
/var/www/html/vendor/laravel/framework/src/Illuminate/Database/Connection.php:399
/var/www/html/vendor/laravel/framework/src/Illuminate/Database/Connection.php:325
/var/www/html/vendor/laravel/framework/src/Illuminate/Database/Connection.php:657
/var/www/html/vendor/laravel/framework/src/Illuminate/Database/Connection.php:624
/var/www/html/vendor/laravel/framework/src/Illuminate/Database/Connection.php:333
/var/www/html/vendor/laravel/framework/src/Illuminate/Database/Schema/PostgresBuilder.php:108
/var/www/html/vendor/laravel/framework/src/Illuminate/Database/Schema/PostgresBuilder.php:35
/var/www/html/vendor/laravel/framework/src/Illuminate/Database/Console/Migrations/FreshCommand.php:79
/var/www/html/vendor/laravel/framework/src/Illuminate/Database/Console/Migrations/FreshCommand.php:46
/var/www/html/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php:32
/var/www/html/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php:90
/var/www/html/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php:34
/var/www/html/vendor/laravel/framework/src/Illuminate/Container/Container.php:576
/var/www/html/vendor/laravel/framework/src/Illuminate/Console/Command.php:183
/var/www/html/vendor/symfony/console/Command/Command.php:255
/var/www/html/vendor/laravel/framework/src/Illuminate/Console/Command.php:170
/var/www/html/vendor/symfony/console/Application.php:921
/var/www/html/vendor/symfony/console/Application.php:273
/var/www/html/vendor/symfony/console/Application.php:149
/var/www/html/vendor/laravel/framework/src/Illuminate/Console/Application.php:90
/var/www/html/vendor/laravel/framework/src/Illuminate/Console/Application.php:182
/var/www/html/vendor/laravel/framework/src/Illuminate/Foundation/Console/Kernel.php:275
/var/www/html/vendor/laravel/framework/src/Illuminate/Foundation/Testing/PendingCommand.php:136
/var/www/html/vendor/laravel/framework/src/Illuminate/Foundation/Testing/PendingCommand.php:220
/var/www/html/vendor/laravel/framework/src/Illuminate/Foundation/Testing/Concerns/InteractsWithConsole.php:56
/var/www/html/vendor/laravel/framework/src/Illuminate/Foundation/Testing/RefreshDatabase.php:55
/var/www/html/vendor/laravel/framework/src/Illuminate/Foundation/Testing/RefreshDatabase.php:18
/var/www/html/vendor/laravel/framework/src/Illuminate/Foundation/Testing/TestCase.php:105
/var/www/html/vendor/laravel/framework/src/Illuminate/Foundation/Testing/TestCase.php:72
/var/www/html/tests/Database/Integration/Repositories/RoverRepositoryTest.php:31

ERRORS!
Tests: 1, Assertions: 0, Errors: 1.

For the production setup I use the following .env file for thw database:

DB_CONNECTION=pgsql
DB_HOST=postgresql
DB_PORT=5432
DB_DATABASE=laravel
DB_USERNAME=laravel
DB_PASSWORD=IDontTellYouThisIsNotAnActuallPassword

SO I have theese 2 questions:

  • Why my settings for sqlite are being ingored?
  • How do I enforce to use the inmemory sqlite when testing?

Edit 1

I made a file named .env.testing:

DB_CONNECTION=sqlite
DATABASE_URL=localhost
DB_DATABASE=:memory:

And changed my phpunit.xml into:

<?xml version="1.0" encoding="UTF-8"?>
<phpunit backupGlobals="false"
         backupStaticAttributes="false"
         bootstrap="vendor/autoload.php"
         colors="true"
         convertErrorsToExceptions="true"
         convertNoticesToExceptions="true"
         convertWarningsToExceptions="true"
         processIsolation="false"
         stopOnFailure="false">
    <testsuites>
        <testsuite name="Unit">
            <directory suffix="Test.php">./tests/Unit</directory>
        </testsuite>

        <testsuite name="Feature">
            <directory suffix="Test.php">./tests/Feature</directory>
        </testsuite>
        <testsuite name="Database">
            <directory suffix="Test.php">./tests/Database</directory>
        </testsuite>
    </testsuites>
    <filter>
        <whitelist processUncoveredFilesFromWhitelist="true">
            <directory suffix=".php">./app</directory>
        </whitelist>
    </filter>
    <php>
        <env name="APP_ENV" value="testing"/>
        <env name="BCRYPT_ROUNDS" value="4"/>
        <env name="CACHE_DRIVER" value="array"/>
        <env name="MAIL_DRIVER" value="array"/>
        <env name="QUEUE_CONNECTION" value="sync"/>
        <env name="SESSION_DRIVER" value="array"/>
    </php>
</phpunit>

Also the settings for sqlite in laravel's are:

'sqlite' => [
            'driver' => 'sqlite',
            'url' => env('DATABASE_URL'),
            'database' => env('DB_DATABASE', database_path('database.sqlite')),
            'prefix' => '',
            'foreign_key_constraints' => env('DB_FOREIGN_KEYS', true),
        ],

Edit 2

I tried as suggested:

namespace  Test\Database\Integration\Repositories;

use Tests\TestCase;
use Illuminate\Foundation\Testing\RefreshDatabase;
use Illuminate\Foundation\Testing\DatabaseMigrations;
use App\Repositories\RoverRepository;
use App\Model\Rover;
use App\Model\Grid;

class RoverRepositoryTest extends TestCase
{
    use RefreshDatabase;
    use DatabaseMigrations;

    /**
     * Undocumented variable
     *
     * @var RoverRepository|null
     */
    private $repository=null;

    /**
     * Undocumented variable
     *
     * @var Grid|null;
     */
    private $grid=null;

    public function setUp(): void
    {
        parent::setUp();

        $this->runDatabaseMigrations();

        $grid=factory(Grid::class)->create([
            'width'=>5,
            'height'=>5
        ]);

        $rover=factory(Rover::class, 5)->create([
            'grid_id' => $grid->id,
            'grid_pos_x' => rand(0, $grid->width),
            'grid_pos_y' => rand(0, $grid->height),
        ]);

        $this->grid=$grid;
        //How do I run Migrations and generate the db?

        $this->repository = new RoverRepository();
    }

    public function tearDown(): void
    {
        parent::tearDown();
        //How do I nuke my Database?
    }

   /**
     * Testing Base Search
     *
     * @return void
    */
    public function testBasicSearch(): void
    {
        $this->assertTrue(true);
    }
}

And then I run the following commands:

php artisan config:clear
php artisan config:clear --env=testing
php artisan config:clear --env=development
php artisan config:cache

Still the error continues.

Edit 3

After lots of testing I empty the .env file. Then I run the command:

php artisan config:cache

And the error has changed into:

PHPUnit 7.5.13 by Sebastian Bergmann and contributors.

E                                                                   1 / 1 (100%)

Time: 111 ms, Memory: 18.00 MB

There was 1 error:

1) Test\Database\Integration\Repositories\RoverRepositoryTest::testBasicSearch
Mockery\Exception\BadMethodCallException: Received Mockery_1_Illuminate_Console_OutputStyle::askQuestion(), but no expectations were specified

/var/www/html/vendor/symfony/console/Style/SymfonyStyle.php:222
/var/www/html/vendor/laravel/framework/src/Illuminate/Console/Command.php:332
/var/www/html/vendor/laravel/framework/src/Illuminate/Console/ConfirmableTrait.php:31
/var/www/html/vendor/laravel/framework/src/Illuminate/Database/Console/Migrations/FreshCommand.php:34
/var/www/html/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php:32
/var/www/html/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php:90
/var/www/html/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php:34
/var/www/html/vendor/laravel/framework/src/Illuminate/Container/Container.php:576
/var/www/html/vendor/laravel/framework/src/Illuminate/Console/Command.php:183
/var/www/html/vendor/symfony/console/Command/Command.php:255
/var/www/html/vendor/laravel/framework/src/Illuminate/Console/Command.php:170
/var/www/html/vendor/symfony/console/Application.php:921
/var/www/html/vendor/symfony/console/Application.php:273
/var/www/html/vendor/symfony/console/Application.php:149
/var/www/html/vendor/laravel/framework/src/Illuminate/Console/Application.php:90
/var/www/html/vendor/laravel/framework/src/Illuminate/Console/Application.php:182
/var/www/html/vendor/laravel/framework/src/Illuminate/Foundation/Console/Kernel.php:275
/var/www/html/vendor/laravel/framework/src/Illuminate/Foundation/Testing/PendingCommand.php:136
/var/www/html/vendor/laravel/framework/src/Illuminate/Foundation/Testing/PendingCommand.php:220
/var/www/html/vendor/laravel/framework/src/Illuminate/Foundation/Testing/Concerns/InteractsWithConsole.php:56
/var/www/html/vendor/laravel/framework/src/Illuminate/Foundation/Testing/RefreshDatabase.php:55
/var/www/html/vendor/laravel/framework/src/Illuminate/Foundation/Testing/RefreshDatabase.php:18
/var/www/html/vendor/laravel/framework/src/Illuminate/Foundation/Testing/TestCase.php:105
/var/www/html/vendor/laravel/framework/src/Illuminate/Foundation/Testing/TestCase.php:72

Also if I keep only the sqlite settings and restore into the original .env I get the error:

PHPUnit 7.5.13 by Sebastian Bergmann and contributors.

E                                                                   1 / 1 (100%)

Time: 115 ms, Memory: 18.00 MB

There was 1 error:

1) Test\Database\Integration\Repositories\RoverRepositoryTest::testBasicSearch
InvalidArgumentException: Database [pgsql] not configured.

/var/www/html/vendor/laravel/framework/src/Illuminate/Database/DatabaseManager.php:152
/var/www/html/vendor/laravel/framework/src/Illuminate/Database/DatabaseManager.php:115
/var/www/html/vendor/laravel/framework/src/Illuminate/Database/DatabaseManager.php:86
/var/www/html/vendor/laravel/framework/src/Illuminate/Database/Console/Migrations/FreshCommand.php:77
/var/www/html/vendor/laravel/framework/src/Illuminate/Database/Console/Migrations/FreshCommand.php:46
/var/www/html/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php:32
/var/www/html/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php:90
/var/www/html/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php:34
/var/www/html/vendor/laravel/framework/src/Illuminate/Container/Container.php:576
/var/www/html/vendor/laravel/framework/src/Illuminate/Console/Command.php:183
/var/www/html/vendor/symfony/console/Command/Command.php:255
/var/www/html/vendor/laravel/framework/src/Illuminate/Console/Command.php:170
/var/www/html/vendor/symfony/console/Application.php:921
/var/www/html/vendor/symfony/console/Application.php:273
/var/www/html/vendor/symfony/console/Application.php:149
/var/www/html/vendor/laravel/framework/src/Illuminate/Console/Application.php:90
/var/www/html/vendor/laravel/framework/src/Illuminate/Console/Application.php:182
/var/www/html/vendor/laravel/framework/src/Illuminate/Foundation/Console/Kernel.php:275
/var/www/html/vendor/laravel/framework/src/Illuminate/Foundation/Testing/PendingCommand.php:136
/var/www/html/vendor/laravel/framework/src/Illuminate/Foundation/Testing/PendingCommand.php:220
/var/www/html/vendor/laravel/framework/src/Illuminate/Foundation/Testing/Concerns/InteractsWithConsole.php:56
/var/www/html/vendor/laravel/framework/src/Illuminate/Foundation/Testing/DatabaseMigrations.php:16
/var/www/html/vendor/laravel/framework/src/Illuminate/Foundation/Testing/TestCase.php:109
/var/www/html/vendor/laravel/framework/src/Illuminate/Foundation/Testing/TestCase.php:72

Meaning that the .env.testing is being completely ignored if .env is provided.

laravel environment-variables phpunit sqlite php
5 Answers

When implementing the SQLite database for the testing, you do not need to specify the database URL since it is running in the memory.

Remove the line from phpunit.xml 

<env name="DATABASE_URL" value="localhost"/>

Your config might be caching. Run the command 

php artisan config:clear

Now re-run the test.

Remove the code from the setUp() function

$this->runDatabaseMigrations();

To migrate the database in the SQLite, you can use 

use Illuminate\Foundation\Testing\DatabaseMigrations;

Edit: For config/database.php

For using SQLite, you need to configure in the database.php.

//config/database.php 
...
'sqlite' => [
    'driver' => 'sqlite',
    'database' => storage_path('database/database.sqlite'),
    'prefix' => '',
    'foreign_key_constraints' => env('DB_FOREIGN_KEYS', true),
    ],
...
user_83980850

it seems like your tests are still using the sql database.

when doing tests using an SQLite database, I suggest that you verify these things:

  1. your database.sqlite must be created in the database folder and don't forget to adjust the file rights.

  2. you must include these 2 traits: 

// this trait is used to wrap every query in a transaction where this one will be deleted at the end of the test
use DatabaseTransactions;

// this one allow Laravel to prepare the database
use DatabaseMigrations;

if it's still not working, i suggest that you stop your server, execute the current artisan command php artisan config:clear and restart it.

EDIT

i just saw that your phpunit.xml contains some error, delete all the env tags and add only these:

<env name="DB_CONNECTION" value="sqlite"/>
<env name="DB_DATABASE" value=":memory:"/>
<env name="APP_ENV" value="testing"/>
<env name="CACHE_DRIVER" value="array"/>
<env name="MAIL_DRIVER" value="array"/>
<env name="QUEUE_DRIVER" value="sync"/>
<env name="SESSION_DRIVER" value="array"/>

creating a .env.testing is not needed

user_85768780

Make sure your .env file has the same password as your database.

user_111132590

I faced the same problem.

I searched and see this topic.

and I wanna put the answer for future search.

I tried many things and after a while, I understand on my new linux OS, I didn't install the sqlite driver.

sudo apt-get install php-sqlite

on windows, just uncomment the SQLite extension in php.ini by removing the simicolon ; before that line.

so the problem solved simply.

user_130999640

Do you use any enviromental variable with the VERY SAME NAME with the one specified in .env? Sometimes theese can cause a huge conflict with the ones specified in .env ovveriding them.

In your case it may be this case either via useing Homestead or docker with docker-compose, in theese cases is good idea to set a unique prefix identifying the use of it. For example insteat of using names such as DB_CONNECTION name it DOCKER_DB_CONNECTION hence no conflict at all.

Also in case of a docker-compose this is a bad idea:

version: '3.1'
services:
  develop:
    image: ddesyllas/php-dev:dev-n-build
    volumes:
      - ".:/var/www/html"
      - "./.laravel.env:/var/www/html/.env"
    links:
      - memcache
    environment:
      DB_CONNECTION: pgsql
      DB_HOST : postgresql
      DB_PORT : 5432
      DB_DATABASE: ${DOCKER_POSTGRES_DB}
      DB_USERNAME: ${DOCKER_POSTGRES_USER}
      DB_PASSWORD: ${DOCKER_POSTGRES_PASSWORD}

  nginx:
    image: nginx:alpine
    ports:
      - 7880:7880
    links:
      - "develop:develop"
    volumes:
      - ".:/var/www/html"
      - "./docker/nginx.conf:/etc/nginx/nginx.conf:ro"


  postgresql:
    image: postgres:alpine
    volumes:
      - './docker/misc_volumes/postgresql:/var/lib/postgresql/data'
    environment:
      POSTGRES_USER: ${DOCKER_POSTGRES_USER}
      POSTGRES_DB: ${DOCKER_POSTGRES_DB}
      POSTGRES_PASSWORD: ${DOCKER_POSTGRES_PASSWORD}

  memcache:
    image: memcached:alpine

Instead use enviromental variable names as specified in the following docker-compose.yml is a good idea:

version: '3.1'
services:
  develop:
    image: ddesyllas/php-dev:dev-n-build
    volumes:
      - ".:/var/www/html"
      - "./.laravel.env:/var/www/html/.env"
    links:
      - memcache
    environment:
      DOCKER_DB_CONNECTION: pgsql
      DOCKER_DB_HOST : postgresql
      DOCKER_DB_PORT : 5432
      DOCKER_DB_DATABASE: ${DOCKER_POSTGRES_DB}
      DOCKER_DB_USERNAME: ${DOCKER_POSTGRES_USER}
      DOCKER_DB_PASSWORD: ${DOCKER_POSTGRES_PASSWORD}

  nginx:
    image: nginx:alpine
    ports:
      - 7880:7880
    links:
      - "develop:develop"
    volumes:
      - ".:/var/www/html"
      - "./docker/nginx.conf:/etc/nginx/nginx.conf:ro"


  postgresql:
    image: postgres:alpine
    volumes:
      - './docker/misc_volumes/postgresql:/var/lib/postgresql/data'
    environment:
      POSTGRES_USER: ${DOCKER_POSTGRES_USER}
      POSTGRES_DB: ${DOCKER_POSTGRES_DB}
      POSTGRES_PASSWORD: ${DOCKER_POSTGRES_PASSWORD}

  memcache:
    image: memcached:alpine

Do you noticed that I renamed the enviromental variables like this:

  • DB_CONNECTION => DOCKER_DB_CONNECTION
  • DB_HOST => DOCKER_DB_HOST
  • DB_PORT => DOCKER_DB_PORT
  • DB_DATABASE => DOCKER_DB_DATABASE
  • DB_USERNAME => DOCKER_DB_USERNAME
  • DB_PASSWORD => DOCKER_DB_PASSWORD
user_47067110
Related