How to use multiple database tests

I have a multiple data base app.

General data in mysql connection
Two tables in sqlite connection

database.php

'connections' => [

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

        'mysql' => [
            'driver' => 'mysql',
            'url' => env('DATABASE_URL'),
            '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' => '',
            'prefix_indexes' => true,
            'strict' => true,
            'engine' => null,
            'options' => extension_loaded('pdo_mysql') ? array_filter([
                PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
            ]) : [],
        ],

.env.testing

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=sitelight_test
DB_USERNAME=laravel
DB_PASSWORD='KKKKKKKKKKKK'
FORWARD_DB_PORT=3306

SQLITE_LOG_DATABASE=log_errors_test.sqlite

Model

class LogAnalyzerError extends Model
{
    use HasUlids;
    use HasFactory;

    protected $connection = 'sqlite_log';

    protected $table = 'log_analyzer_errors';

    public $incrementing = false;

    protected $guarded = [];

    protected $casts = [
        'json' => 'array',
    ];

    public function commandCenter(): BelongsTo
    {
        return $this->belongsTo(CommandCenter::class);
    }
}

When I try to create a test that uses a connection sqlite_log get error.

Illuminate\Database\QueryException: SQLSTATE[HY000]: General error: 1 table "log_analyzer_errors" already exists (Connection: sqlite_log, SQL: create table "log_analyzer_errors" ("id" varchar not null, "command_center_id" integer not null, "json" text not null, "created_at" datetime, "updated_at" datetime))
at vendor/laravel/framework/src/Illuminate/Database/Connection.php:574
at vendor/laravel/framework/src/Illuminate/Database/Connection.php:809
at vendor/laravel/framework/src/Illuminate/Database/Connection.php:776
at vendor/laravel/framework/src/Illuminate/Database/Connection.php:569
at vendor/laravel/framework/src/Illuminate/Database/Schema/Blueprint.php:110
at vendor/laravel/framework/src/Illuminate/Database/Schema/Builder.php:552
at vendor/laravel/framework/src/Illuminate/Database/Schema/Builder.php:406
at database/migrations/2023_12_16_074247_create_logger_errors_table.php:10
at vendor/laravel/framework/src/Illuminate/Database/Migrations/Migrator.php:493
at vendor/laravel/framework/src/Illuminate/Database/Migrations/Migrator.php:410
at vendor/laravel/framework/src/Illuminate/Database/Migrations/Migrator.php:419
at vendor/laravel/framework/src/Illuminate/Database/Migrations/Migrator.php:216
at vendor/laravel/framework/src/Illuminate/Console/View/Components/Task.php:37
at vendor/laravel/framework/src/Illuminate/Database/Migrations/Migrator.php:756
at vendor/laravel/framework/src/Illuminate/Database/Migrations/Migrator.php:216
at vendor/laravel/framework/src/Illuminate/Database/Migrations/Migrator.php:181
at vendor/laravel/framework/src/Illuminate/Database/Migrations/Migrator.php:124
at vendor/laravel/framework/src/Illuminate/Database/Console/Migrations/MigrateCommand.php:90
at vendor/laravel/framework/src/Illuminate/Database/Migrations/Migrator.php:633
at vendor/laravel/framework/src/Illuminate/Database/Console/Migrations/MigrateCommand.php:83
at vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php:36
at vendor/laravel/framework/src/Illuminate/Container/Util.php:41
at vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php:93
at vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php:35
at vendor/laravel/framework/src/Illuminate/Container/Container.php:662
at vendor/laravel/framework/src/Illuminate/Console/Command.php:211
at vendor/symfony/console/Command/Command.php:326
at vendor/laravel/framework/src/Illuminate/Console/Command.php:180
at vendor/laravel/framework/src/Illuminate/Console/Concerns/CallsCommands.php:67
at vendor/laravel/framework/src/Illuminate/Console/Concerns/CallsCommands.php:28
at vendor/laravel/framework/src/Illuminate/Database/Console/Migrations/FreshCommand.php:53
at vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php:36
at vendor/laravel/framework/src/Illuminate/Container/Util.php:41
at vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php:93
at vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php:35
at vendor/laravel/framework/src/Illuminate/Container/Container.php:662
at vendor/laravel/framework/src/Illuminate/Console/Command.php:211
at vendor/symfony/console/Command/Command.php:326
at vendor/laravel/framework/src/Illuminate/Console/Command.php:180
at vendor/symfony/console/Application.php:1078
at vendor/symfony/console/Application.php:324
at vendor/symfony/console/Application.php:175
at vendor/laravel/framework/src/Illuminate/Console/Application.php:162
at vendor/laravel/framework/src/Illuminate/Foundation/Console/Kernel.php:400
at vendor/laravel/framework/src/Illuminate/Testing/PendingCommand.php:296
at vendor/laravel/framework/src/Illuminate/Testing/PendingCommand.php:481
at vendor/laravel/framework/src/Illuminate/Foundation/Testing/Concerns/InteractsWithConsole.php:80
at vendor/laravel/framework/src/Illuminate/Foundation/Testing/RefreshDatabase.php:73
at vendor/laravel/framework/src/Illuminate/Foundation/Testing/RefreshDatabase.php:23
at vendor/laravel/framework/src/Illuminate/Foundation/Testing/LazilyRefreshDatabase.php:27
at vendor/laravel/framework/src/Illuminate/Database/Connection.php:765
at vendor/laravel/framework/src/Illuminate/Database/Connection.php:407
at vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php:2751
at vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php:2740
at vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php:3294
at vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php:2739
at vendor/laravel/framework/src/Illuminate/Database/Eloquent/Builder.php:737
at vendor/laravel/framework/src/Illuminate/Database/Eloquent/Builder.php:721
at vendor/laravel/framework/src/Illuminate/Database/Concerns/BuildsQueries.php:333
at vendor/laravel/framework/src/Illuminate/Database/Eloquent/Builder.php:566
at vendor/laravel/framework/src/Illuminate/Support/Traits/ForwardsCalls.php:23
at vendor/laravel/framework/src/Illuminate/Database/Eloquent/Model.php:2334
at vendor/laravel/framework/src/Illuminate/Database/Eloquent/Model.php:2346

any ideas?

LaryAI

2 years ago

Level 58

It looks like you're encountering an issue where the log_analyzer_errors table is being attempted to be created when it already exists in your SQLite database. This can happen during testing when the database is not properly reset between test runs.

To solve this issue, you can use the RefreshDatabase trait in your test class, which will roll back the database to a clean state before each test. Here's how you can apply it:

use Illuminate\Foundation\Testing\RefreshDatabase;

class YourTest extends TestCase
{
    use RefreshDatabase;

    // Your test methods here
}

Make sure that your .env.testing file is properly configured to use the SQLite database for testing. The RefreshDatabase trait will use the default database connection specified in your .env.testing file.

If you want to specify a different connection for testing, you can set the DB_CONNECTION environment variable in your .env.testing file to the name of the testing connection you want to use, such as sqlite_log.

Additionally, ensure that your database.php configuration file is set up to use an in-memory SQLite database for testing, which can be done by setting the database key to :memory: for the sqlite_log connection when the environment is testing:

'sqlite_log' => [
    'driver' => 'sqlite',
    'database' => env('DB_ENV') === 'testing' ? ':memory:' : database_path(env('SQLITE_LOG_DATABASE', 'log_errors.sqlite')),
    'prefix' => '',
],

Remember to adjust the DB_ENV environment variable in your .env.testing file to testing or use app()->environment('testing') to check the environment within the configuration file.

By using an in-memory database for testing, you avoid issues with file-based SQLite databases not being properly reset between tests. The in-memory database is fast and will be reset to its initial state before each test.

If you still encounter issues, you may need to manually run the migration for the sqlite_log connection before your tests. You can do this by calling the artisan command with the --database option in your test setup method:

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

    // Run the migrations for the sqlite_log connection
    $this->artisan('migrate', ['--database' => 'sqlite_log']);
}

This will ensure that the migrations are applied to the correct database connection before your tests run.

abkrim

2 years ago

Level 13

@LaryAI

<?php

namespace Tests;

use Illuminate\Foundation\Testing\TestCase as BaseTestCase;

abstract class TestCase extends BaseTestCase
{
    use CreatesApplication;

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

        $this->artisan('migrate', ['--database' => 'sqlite_log']);
    }

    protected function getPackageProviders($app): array
    {
        return [
            \Spatie\LaravelRay\RayServiceProvider::class,
        ];
    }
}

Error

   ERROR  Fatal error: Access level to Pest\Concerns\Testable::setUp() must be public (as in class Tests\TestCase) in /Users/abkrim/Sites/sitelight/vendor/pestphp/pest/src/Concerns/Testable.php on line 189.

Please or to participate in this conversation.