I am trying to introduce tests in my Laravel 8 project using Pest. The project uses its own database, but connects to another one as well. The latter is shared with a different system, accomplished by setting two connections in config/database.php, even though both databases reside within same MariaDB instance in production environment.
config/database.php :
(...)
'default' => env('DB_CONNECTION', 'mysql'),
'another' => 'another',
'connections' => [
'mysql' => [
'driver' => env('DATABASE_DRIVER', 'mysql'),
'url' => env('DATABASE_URL'),
'host' => env('DB_HOST', '127.0.0.1'),
'port' => env('DB_PORT', '3306'),
'database' => env('DB_DATABASE', 'laravel'),
'username' => env('DB_USERNAME', 'forge'),
'password' => env('DB_PASSWORD', ''),
'unix_socket' => env('DB_SOCKET', ''),
'charset' => 'utf8mb4',
'collation' => 'utf8mb4_unicode_ci',
'prefix' => '',
'prefix_indexes' => true,
'strict' => true,
'engine' => 'innodb',
'options' => extension_loaded('pdo_mysql') ? array_filter([
PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
]) : [],
],
'dummyname' => [
'driver' => env('DATABASE_DRIVER', 'mysql'),
'url' => env('DATABASE_URL'),
'host' => env('DB_HOST_ANOTHER', '127.0.0.1'),
'port' => env('DB_PORT_ANOTHER', '3306'),
'database' => env('DB_DATABASE_ANOTHER', 'another'),
'username' => env('DB_USERNAME_ANOTHER', 'forge'),
'password' => env('DB_PASSWORD_ANOTHER', ''),
'unix_socket' => env('DB_SOCKET', ''),
'charset' => 'utf8mb4',
'collation' => 'utf8mb4_unicode_ci',
'prefix' => '',
'prefix_indexes' => true,
'strict' => true,
'engine' => null,
'options' => extension_loaded('pdo_mysql') ? array_filter([
PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
]) : [],
'modes' => [
'STRICT_TRANS_TABLES',
'NO_ZERO_IN_DATE',
'NO_ZERO_DATE',
'ERROR_FOR_DIVISION_BY_ZERO',
'NO_AUTO_CREATE_USER',
'NO_ENGINE_SUBSTITUTION'
],
],
(...)
Since table names do not overlap and I created a relation between them using a pivot table in a MySQL database, I decided to use a single SQLite database in tests by setting phpunit.xml like :
<php>
<server name="APP_ENV" value="testing"/>
<server name="BCRYPT_ROUNDS" value="4"/>
<server name="CACHE_DRIVER" value="array"/>
<server name="DB_DATABASE" value="/tmp/tests.sqlite"/>
<server name="DB_DATABASE_ANOTHER" value="/tmp/tests.sqlite"/>
<server name="DATABASE_DRIVER" value="sqlite"/>
<server name="MAIL_MAILER" value="array"/>
<server name="QUEUE_CONNECTION" value="sync"/>
<server name="SESSION_DRIVER" value="array"/>
<server name="TELESCOPE_ENABLED" value="false"/>
</php>
There are models named Project and PollProject. The first one is Laravel's and the other's table is in the other database:
mysql.projects:
- id
- ...
another.poll_projects
- id
- ...
mysql.project_poll_project
- project_id
- poll_project_id
(UNIQUE index on both)
(FOREIGN KEY constraint on the first one)
Relation is defined like :
class Project extends Model
{
protected $connection = 'mysql';
public function pollProjects() {
$database = $this->getConnection()->getDatabaseName() . '.';
if ($database == '/tmp/tests.sqlite.') $database = '';
return $this->belongsToMany(PollProject::class, $database . 'project_poll_project', 'project_id', 'poll_project_id');
}
}
class PollProject extends Model
{
// database table settings
protected $connection = 'another';
protected $table = 'poll_projects';
public function projects() {
$foreign_connection = 'mysql';
return $this->belongsToMany(Project::class, "$foreign_connection.project_poll_project", 'poll_project_id', 'project_id');
}
}
I want to test permissions based on projects user is assigned to. I refresh database by attaching RefreshDatabase to TestCase.
tests/TestCase.php :
abstract class TestCase extends BaseTestCase
{
use CreatesApplication, RefreshDatabase;
}
Since Project can have multiple PollProjects I delegate creating objects to factories, then try to attach PollProject to Project.
tests/Feature/PollProjectRolesTest.php :
(...)
beforeEach(function () {
PollProject::factory()->count(2)->create();
$this->accessiblePollProject = PollProject::find(1);
$this->forbiddenPollProject = PollProject::find(2);
Project::factory()->count(1)->create();
$this->project = Project::find(1);
$this->project->pollProjects()->attach($this->accessiblePollProject);
die('It will not reach this point');
(...)
});
it('allows to list templates', function () {
expect(true)->toBeTrue();
});
Having created this test case and other ones (that use Pest\Laravel\get or test can()), running the test suite results in SQLite's locked database error:
root@5ea0190680a9:/var/www# php artisan test
WARN Tests\Unit\TemplatePolicyTest
✓ it allows superadmins to do anything
! it disallows viewing templates → This test did not perform any assertions /var/www/vendor/pestphp/pest/src/Factories/TestCaseFactory.php(223) : eval()'d code:5
! it disallows updating templates → This test did not perform any assertions /var/www/vendor/pestphp/pest/src/Factories/TestCaseFactory.php(223) : eval()'d code:5
! it disallows destroying templates → This test did not perform any assertions /var/www/vendor/pestphp/pest/src/Factories/TestCaseFactory.php(223) : eval()'d code:5
! it disallows ... templates → This test did not perform any assertions /var/www/vendor/pestphp/pest/src/Factories/TestCaseFactory.php(223) : eval()'d code:5
PASS Tests\Feature\PagesStatusesTest
✓ get '/' → assertRedirect '/home'
✓ get '/home' → assertRedirect '/login'
✓ get '/login' → assertSuccessful
✓ get '/admin/user' → assertRedirect '/login'
FAIL Tests\Feature\PollProjectRolesTest
⨯ it allows to list templates
---
• Tests\Feature\PollProjectRolesTest > it allows to list document templates
Illuminate\Database\QueryException
SQLSTATE[HY000]: General error: 5 database is locked (SQL: insert into "project_poll_project" ("poll_project_id", "project_id") values (1, 1))
at vendor/laravel/framework/src/Illuminate/Database/Connection.php:712
708▕ // If an exception occurs when attempting to run a query, we'll format the error
709▕ // message to include the bindings with SQL, which will make this exception a
710▕ // lot more helpful to the developer instead of just the database's errors.
711▕ catch (Exception $e) {
➜ 712▕ throw new QueryException(
713▕ $query, $this->prepareBindings($bindings), $e
714▕ );
715▕ }
716▕ }
8 vendor frames
9 tests/Feature/PollProjectRolesTest.php:27
Illuminate\Database\Eloquent\Relations\BelongsToMany::attach(Object(App\PollProject))
Tests: 1 failed, 4 risked, 5 passed
Time: 63.85s
I tried to use separate database files but this resulted in :
SQLSTATE[HY000]: General error: 1 no such table: project_poll_project
error in the same place as Laravel can't access database another while querying MySQL. :memory: as database location also resulted in no such table. Despite both databases set as :memory: they seem to be separate instances. As explained :
Specifying a shared cache using the URI format like you are using will cause all connections in the same process to use the same database
Does it mean there is more than one process handling the connection? Or Laravel uses some kind of connection pool? When I remove RefreshDatabase trait from TestCase with migrations already applied, the test setup gets through:
root@5ea0190680a9:/var/www# php artisan test
WARN Tests\Unit\DocumentTemplatePolicyTest
✓ it allows superadmins to do anything
! it disallows viewing templates → This test did not perform any assertions /var/www/vendor/pestphp/pest/src/Factories/TestCaseFactory.php(223) : eval()'d code:5
! it disallows updating templates → This test did not perform any assertions /var/www/vendor/pestphp/pest/src/Factories/TestCaseFactory.php(223) : eval()'d code:5
! it disallows destroying templates → This test did not perform any assertions /var/www/vendor/pestphp/pest/src/Factories/TestCaseFactory.php(223) : eval()'d code:5
! it disallows ... templates → This test did not perform any assertions /var/www/vendor/pestphp/pest/src/Factories/TestCaseFactory.php(223) : eval()'d code:5
PASS Tests\Feature\PagesStatusesTest
✓ get '/' → assertRedirect '/home'
✓ get '/home' → assertRedirect '/login'
✓ get '/login' → assertSuccessful
✓ get '/admin/user' → assertRedirect '/login'
It will not reach this point
CodePudding user response：
I realized that, while there might be no connection pool, there must be at least two connections involved as I defined these myself in config/database.php. I solved this by parametrizing the connection name in places I use it, so only one connection is used in tests.
I created one additional variable in phpunit.xml file, so only the default connection is used:
<server name="DB_CONNECTION_ANOTHER" value="mysql"/>
In the config file I changed:
'another' => 'another',
to
'another' => env('DB_CONNECTION_ANOTHER', 'another'),
Every occurrence of DB::connection('another') or Schema::('another') was changed to DB::connection(config('database.another')) and Schema::connection(config('database.another')), respectively.
Since models had defined private $connection = 'another'; as well, and this property is static, I had to define a constructor there to allow dynamic values:
public function __construct(array $attributes = [])
{
parent::__construct($attributes);
$this->connection = config('database.another');
}
This solved the issue for me.