sqlite3: how to configure PRAGMAs like auto_vacuum and page_size?

[miguno]

As per the sqlite3 docs, these per-DB configs should ideally be set on an empty database. How can I do this with goose?

Context

Here's what I tried with goose v3.22.0 and sqlite3 3.43.2.

Create the migration file:

$ cat 20240916201442_sqlite_per_db_pragmas.sql
-- +goose NO TRANSACTION

-- +goose Up
PRAGMA journal_mode = 'wal';
PRAGMA auto_vacuum = 2;
PRAGMA page_size = 8192;

Migrate the database:

$ GOOSE_DRIVER=sqlite3 GOOSE_DBSTRING=./foo.db
2024/09/17 01:30:44 OK   20240916201442_sqlite_per_db_pragmas.sql (630µs)
2024/09/17 01:30:44 goose: successfully migrated database to version: 20240916201442

Actual behavior

Verify whether the PRAGMAs were set. As it turns out, only PRAGMA journal_mode = 'wal' was applied. The other two PRAGMAs were not. The order of the PRAGMAs in the migration file don't make a difference, fwiw.

$ sqlite3 -header -table foo.db "PRAGMA journal_mode; PRAGMA auto_vacuum; PRAGMA page_size;"
+--------------+
| journal_mode |
+--------------+
| wal          |
+--------------+
+-------------+
| auto_vacuum |
+-------------+
| 0           |
+-------------+
+-----------+
| page_size |
+-----------+
| 4096      |
+-----------+

Expected behavior

$ sqlite3 -header -table foo.db "PRAGMA journal_mode; PRAGMA auto_vacuum; PRAGMA page_size;"
+--------------+
| journal_mode |
+--------------+
| wal          |
+--------------+
+-------------+
| auto_vacuum |
+-------------+
| 2           |
+-------------+
+-----------+
| page_size |
+-----------+
| 8192      |
+-----------+