Migrate fresh fails when SQLite database is on WAL mode

[renatofrota]

Laravel Version

11.26.0

PHP Version

8.3.12

Database Driver & Version

SQLite 3.37.2

Description

Apparently, Laravel reuses the same SQLite connection to drop all tables and then perform migrations. Somewhere in the process, the journal_mode defaults to 'delete'. If the database was previously in 'wal' mode, the commands to populate the database will fail. On a retry, it succeeds (however, the journal_mode defaults back to 'delete' without notifying the user).

Steps To Reproduce

• Install Laravel and select SQLite as database

• Connect to the database and set Journal Mode to WAL (Write Ahead Log)

$ sqlite3 database/database.sqlite 
SQLite version 3.37.2 2022-01-06 13:25:41
Enter ".help" for usage hints.
sqlite> pragma journal_mode = wal;
wal
sqlite> .quit
$

Now, try migrate:fresh. It fails on first attempt.

$ php artisan migrate:fresh --seed

  Dropping all tables ............................................................................................... 3.45ms DONE


   Illuminate\Database\QueryException 

  SQLSTATE[HY000]: General error: 11 database disk image is malformed (Connection: sqlite, SQL: select name from sqlite_master where type = 'table' and name not like 'sqlite_%' order by name)

  at vendor/laravel/framework/src/Illuminate/Database/Connection.php:825
    821▕                     $this->getName(), $query, $this->prepareBindings($bindings), $e
    822▕                 );
    823▕             }
    824▕ 
  ➜ 825▕             throw new QueryException(
    826▕                 $this->getName(), $query, $this->prepareBindings($bindings), $e
    827▕             );
    828▕         }
    829▕     }

      +42 vendor frames 

  43  artisan:13
      Illuminate\Foundation\Application::handleCommand()

Succeeds in the subsequent attempt.

   INFO  Preparing database.  

  Creating migration table ......................................................................................... 11.38ms DONE

   INFO  Running migrations.  

  0001_01_01_000000_create_users_table ............................................................................ 101.75ms DONE
  0001_01_01_000001_create_cache_table ............................................................................. 17.57ms DONE
  0001_01_01_000002_create_jobs_table .............................................................................. 38.40ms DONE


   INFO  Seeding database.  

[vjaykoogu]

@renatofrota This behavior is likely happening because the connection is reused, and some SQLite operations like dropping tables or transactions might revert the journal_mode to DELETE. On retries, the migration works because SQLite's default journaling behavior (i.e., DELETE mode) gets applied.

Solution to Ensure WAL Mode Persists

To avoid issues with journal_mode flipping back to DELETE, you can enforce WAL mode after each migration step or ensure it is always explicitly set during your migration workflow. Here's how you can handle this:

1. Force WAL Mode After Migrations

You can use the after connection hook in config/database.php to ensure that the journal_mode is set back to WAL after each connection:

'sqlite' => [
    'driver' => 'sqlite',
    'database' => env('DB_DATABASE', database_path('database.sqlite')),
    'prefix' => '',
    'foreign_key_constraints' => env('DB_FOREIGN_KEYS', true),
    'after' => function ($connection) {
        $connection->getPdo()->exec("PRAGMA journal_mode = WAL;");
    },
],

2. Set WAL Mode in the Migration File

You can enforce the WAL mode in your migrations. At the beginning of the migration, add a call to set the journal mode:

use Illuminate\Support\Facades\DB;
use Illuminate\Database\Migrations\Migration;

class SetJournalModeToWAL extends Migration
{
    public function up()
    {
        DB::statement("PRAGMA journal_mode = WAL;");
    }

    public function down()
    {
        DB::statement("PRAGMA journal_mode = DELETE;");
    }
}

This ensures that the migration starts with WAL mode explicitly set, avoiding any accidental reversion.

3. Enforce WAL Mode in the Migration Command

Another way to prevent SQLite from switching back to DELETE mode is to enforce WAL mode during the migrate command itself.

You can modify your Laravel service provider (e.g., AppServiceProvider) to automatically switch to WAL mode whenever migrations are executed:

use Illuminate\Support\Facades\Schema;

public function boot()
{
    Schema::defaultStringLength(191);

    // Ensure SQLite uses WAL mode during migrations
    if (config('database.default') === 'sqlite') {
        DB::statement("PRAGMA journal_mode = WAL;");
    }
}

4. Use a Custom Migration Command

You can also create a custom Artisan command that wraps the default migrate command and sets WAL mode before running migrations:

1. Create a custom Artisan command:

php artisan make:command MigrateWithWal

2. In the generated command file (app/Console/Commands/MigrateWithWal.php), add the following:

use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;

class MigrateWithWal extends Command
{
    protected $signature = 'migrate:wal';
    protected $description = 'Run migrations with SQLite WAL mode enabled';

    public function handle()
    {
        // Set journal_mode to WAL
        DB::statement("PRAGMA journal_mode = WAL;");

        // Run migrations
        $this->call('migrate');
    }
}

This command will ensure that WAL mode is always enforced before running migrations.

[renatofrota]

Thank you, @vjaykoogu, for suggesting ways to circumvent the issue.

However, the existence of them does not mean this is not something to be fixed in Laravel, @crynobone.

Please consider my arguments.

No commands can make the journal_mode to change, except an explicit call to statement pragma journal_mode. It is a persistent pragma, saved to the database file headers.

I have traced down the problem to (Illuminate\Database\Schema\SQLiteBuilder)->dropAllTables() and refreshDatabaseFile() methods.

/**
 * Drop all tables from the database.
 *
 * @return void
 */
public function dropAllTables()
{
    if ($this->connection->getDatabaseName() !== ':memory:') {
        return $this->refreshDatabaseFile();
    }

    $this->connection->select($this->grammar->compileEnableWriteableSchema());

    $this->connection->select($this->grammar->compileDropAllTables());

    $this->connection->select($this->grammar->compileDisableWriteableSchema());

    $this->connection->select($this->grammar->compileRebuild());
}

/**
 * Empty the database file.
 *
 * @return void
 */
public function refreshDatabaseFile()
{
    file_put_contents($this->connection->getDatabaseName(), '');
}

The 2nd method above (called by the first) replaces all the database.sqlite file contents with an empty string. This causes the problem.

The command that would be run if it were a memory database:

$sqlite3 database/database.sqlite
sqlite> pragma writable_schema=1;
sqlite> delete from sqlite_master where type in ('table', 'index', 'trigger');
sqlite> pragma writable_schema=0;
sqlite> vacuum;
sqlite> pragma journal_mode;
wal

The last command above is a query to the journal_mode (to confirm it does not change from wal to delete).

And after running these manually, migrate:fresh works fine.

So, IMHO, it's a Laravel bug. Why not proceed with this regular way to empty the database, in a safe way (keeping the file headers), as it is done to the SQLite databases running in memory?

[geidelguerra]

Agree with @renatofrota. This is unexpected behavior from Laravel. If I set the journal_mode to WAL in the config, no command should change that configuration.