Disable foreign keys in sqlite on specific tests

Hello! In order to avoid having to do too much setup, I want to disable foreign key checks in sqlite. I'm aware of the DB_FOREIGN_KEYS option inside config/database.php, but it's global and I only want them off in certain tests. On the other hand I've tried using PRAGMA foreign_keys=off using the DB facade, but it doesn't have any visible effect. Any ideas?

tisuchi

2 years ago

Level 70

@nlsnightmare Yes, this is how you can achieve it.

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

        // Disable foreign key checks
        DB::statement(DB::raw('PRAGMA foreign_keys = OFF;'));
    }

    protected function tearDown(): void
    {
        // Enable foreign key checks if necessary
        DB::statement(DB::raw('PRAGMA foreign_keys = ON;'));

        parent::tearDown();
    }

    /** @test */
    public function your_specific_test()
    {
        // Your test logic here
    }

⚠️ Make sure that the command is actually executed on the SQLite database.

nlsnightmare

2 years ago

Level 2

@tisuchi Using DB::raw inside a DB::statement results in a TypeError, and I've already tried the PRAGMA approach with no success.. any other ideas?

Tray2

2 years ago

Level 73

@nlsnightmare Just that this is a very bad idea in my book, the constraints are there for a reason, and disabling them just cause you are lazy in setting up the world properly in your tests is just plain bad. Sooner or later you will very likely move the a MySQL database for your tests, and then your tests will fail, or you will have some inconsistencies in your data do to the tests not doing the job properly.

nlsnightmare

2 years ago

Level 2

I want to test a very specific part of a large system, and setting up the world wouldn't really make much sense here. My test in essence is: "when you have the x data in your database, the y query should return z results". The foreign keys here might as well be strings of random values, and the logic wouldn't change a bit.

1 like

Please or to participate in this conversation.