Repository: alexjustesen/speedtest-tracker (language: php)

Issue:
[Bug] Provide ability to set database schema (PostgreSQL)
### Pre-work

- [x] I have read the [docs](https://docs.speedtest-tracker.dev).
- [x] I have searched open and closed issues.
- [x] I agree to follow this project's [Code of Conduct](https://www.contributor-covenant.org/version/2/1/code_of_conduct/code_of_conduct.md).

### Description

If using PostgreSQL, it is possible to separate database tables per application by using schemas. I have a central PostgreSQL cluster for all my systems and use a single database `production` with one schema per application. I have installed all tables for speedtest-tracker in the database schema `speedtesttracker`.

Usually the correct schema is automatically selected when the schema name is the same as the database username. This is because the search path is set to the following by default within PostgreSQL:

```
SHOW search_path;
search_path
--------------
 "$user", public
```

However this does not work with speedtest-tracker, as `/app/www/bootstrap/cache/config.php` sets the following:

```
'search_path' => 'public'
```

There is no other way to select the PostgreSQL schema. Usually this should be possible by using the ENV with `DB_SCHEMA`. This variable is not available as per https://docs.speedtest-tracker.dev/getting-started/database-drivers

For additional references, here is my full config:

```
services:
  app:
    image: lscr.io/linuxserver/speedtest-tracker:1.4.2
    restart: unless-stopped
    ports:
      - <REDACTED>:80
    environment:
      - PUID=1000
      - PGID=1000
      - APP_KEY=<REDACTED>
      - DISPLAY_TIMEZONE=Europe/Berlin
      - DB_CONNECTION=pgsql
      - DB_HOST=<REDACTED>
      - DB_PORT=5432
      - DB_DATABASE=production
      - DB_SCHEMA=speedtesttracker
      - DB_USERNAME=speedtesttracker
      - DB_PASSWORD=<REDACTED>
      - SPEEDTEST_SCHEDULE=<REDACTED>
      - SPEEDTEST_SERVERS=<REDACTED>
      - PRUNE_RESULTS_OLDER_THAN=365
    volumes:
      - app_data:/config

volumes:
  app_data:
```

The system does not start correctly, as the database tables are not found (see screenshot and logs below).

![Image](https://github.com/user-attachments/assets/7b66471c-6187-42d6-82b2-2876529bbe1a)

I currently work around this by executing the following command after each container start:

`sed -i "s/'search_path' => 'public',/'search_path' => 'speedtesttracker, public',/g" /app/www/bootstrap/cache/config.php`

### Expected Behavior

I am able to set the PostgreSQL schema by setting `DB_SCHEMA=speedtesttracker` in the environment.

### Steps to Reproduce

1. Use a central PostgreSQL system with a single database and application separation by utilizing database schemas.
2. Fire up a container with the config above. The database schema setting is not available.

### Deployment Environment

Docker Compose

### Application Information

```json
{"environment":{"application_name":"Speedtest Tracker","laravel_version":"12.9.2","php_version":"8.3.19","composer_version":"2.8.8","environment":"production","debug_mode":false,"url":"localhost","maintenance_mode":false,"timezone":"UTC","locale":"en"},"cache":{"config":true,"events":true,"routes":true,"views":true},"drivers":{"broadcasting":"null","cache":"database","database":"pgsql","logs":"stderr","mail":"log","queue":"database","session":"database"},"storage":{"\/app\/www\/public\/storage":false},"filament":{"version":"v3.3.10","packages":"filament, forms, notifications, support, tables","views":"NOT PUBLISHED","blade_icons":"CACHED","panel_components":"CACHED"},"livewire":{"livewire":"v3.6.3"},"speedtest_tracker":{"version":"v1.4.2"}}
```

### What browser(s) are you seeing the problem on?

all

### Logs

```shell
[2025-04-13 19:26:27] production.ERROR: SQLSTATE[42P01]: Undefined table: 7 ERROR:  relation "cache" does not exist
LINE 1: select * from "cache" where "key" in ($1)
                      ^ (Connection: pgsql, SQL: select * from "cache" where "key" in (laravel_cache_illuminate:queue:restart))
```


Discussion / hints:

Fix patch:
diff --git a/config/database.php b/config/database.php
index 1fc80662a..ba9b4389a 100644
--- a/config/database.php
+++ b/config/database.php
@@ -1,10 +1,175 @@
 <?php
 
+use Illuminate\Support\Str;
+
 return [
 
+    /*
+    |--------------------------------------------------------------------------
+    | Default Database Connection Name
+    |--------------------------------------------------------------------------
+    |
+    | Here you may specify which of the database connections below you wish
+    | to use as your default connection for database operations. This is
+    | the connection which will be utilized unless another connection
+    | is explicitly specified when you execute a query / statement.
+    |
+    */
+
+    'default' => env('DB_CONNECTION', 'sqlite'),
+
+    /*
+    |--------------------------------------------------------------------------
+    | Database Connections
+    |--------------------------------------------------------------------------
+    |
+    | Below are all of the database connections defined for your application.
+    | An example configuration is provided for each database system which
+    | is supported by Laravel. You're free to add / remove connections.
+    |
+    */
+
+    'connections' => [
+
+        'sqlite' => [
+            'driver' => 'sqlite',
+            'url' => env('DB_URL'),
+            'database' => env('DB_DATABASE', database_path('database.sqlite')),
+            'prefix' => '',
+            'prefix_indexes' => null,
+            'foreign_key_constraints' => env('DB_FOREIGN_KEYS', true),
+            'busy_timeout' => null,
+            'journal_mode' => null,
+            'synchronous' => null,
+        ],
+
+        'mysql' => [
+            'driver' => 'mysql',
+            'url' => env('DB_URL'),
+            'host' => env('DB_HOST', '127.0.0.1'),
+            'port' => env('DB_PORT', '3306'),
+            'database' => env('DB_DATABASE', 'laravel'),
+            'username' => env('DB_USERNAME', 'root'),
+            'password' => env('DB_PASSWORD', ''),
+            'unix_socket' => env('DB_SOCKET', ''),
+            'charset' => env('DB_CHARSET', 'utf8mb4'),
+            'collation' => env('DB_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'),
+            ]) : [],
+        ],
+
+        'mariadb' => [
+            'driver' => 'mariadb',
+            'url' => env('DB_URL'),
+            'host' => env('DB_HOST', '127.0.0.1'),
+            'port' => env('DB_PORT', '3306'),
+            'database' => env('DB_DATABASE', 'laravel'),
+            'username' => env('DB_USERNAME', 'root'),
+            'password' => env('DB_PASSWORD', ''),
+            'unix_socket' => env('DB_SOCKET', ''),
+            'charset' => env('DB_CHARSET', 'utf8mb4'),
+            'collation' => env('DB_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'),
+            ]) : [],
+        ],
+
+        'pgsql' => [
+            'driver' => 'pgsql',
+            'url' => env('DB_URL'),
+            'host' => env('DB_HOST', '127.0.0.1'),
+            'port' => env('DB_PORT', '5432'),
+            'database' => env('DB_DATABASE', 'laravel'),
+            'username' => env('DB_USERNAME', 'root'),
+            'password' => env('DB_PASSWORD', ''),
+            'charset' => env('DB_CHARSET', 'utf8'),
+            'prefix' => '',
+            'prefix_indexes' => true,
+            'search_path' => env('DB_SEARCH_PATH', 'public'),
+            'sslmode' => 'prefer',
+        ],
+
+        'sqlsrv' => [
+            'driver' => 'sqlsrv',
+            'url' => env('DB_URL'),
+            'host' => env('DB_HOST', 'localhost'),
+            'port' => env('DB_PORT', '1433'),
+            'database' => env('DB_DATABASE', 'laravel'),
+            'username' => env('DB_USERNAME', 'root'),
+            'password' => env('DB_PASSWORD', ''),
+            'charset' => env('DB_CHARSET', 'utf8'),
+            'prefix' => '',
+            'prefix_indexes' => true,
+            // 'encrypt' => env('DB_ENCRYPT', 'yes'),
+            // 'trust_server_certificate' => env('DB_TRUST_SERVER_CERTIFICATE', 'false'),
+        ],
+
+    ],
+
+    /*
+    |--------------------------------------------------------------------------
+    | Migration Repository Table
+    |--------------------------------------------------------------------------
+    |
+    | This table keeps track of all the migrations that have already run for
+    | your application. Using this information, we can determine which of
+    | the migrations on disk haven't actually been run on the database.
+    |
+    */
+
     'migrations' => [
         'table' => 'migrations',
-        'update_date_on_publish' => false, // disable to preserve original behavior for existing applications
+        'update_date_on_publish' => true,
+    ],
+
+    /*
+    |--------------------------------------------------------------------------
+    | Redis Databases
+    |--------------------------------------------------------------------------
+    |
+    | Redis is an open source, fast, and advanced key-value store that also
+    | provides a richer body of commands than a typical key-value system
+    | such as Memcached. You may define your connection settings here.
+    |
+    */
+
+    'redis' => [
+
+        'client' => env('REDIS_CLIENT', 'phpredis'),
+
+        'options' => [
+            'cluster' => env('REDIS_CLUSTER', 'redis'),
+            'prefix' => env('REDIS_PREFIX', Str::slug(env('APP_NAME', 'laravel'), '_').'_database_'),
+            'persistent' => env('REDIS_PERSISTENT', false),
+        ],
+
+        'default' => [
+            'url' => env('REDIS_URL'),
+            'host' => env('REDIS_HOST', '127.0.0.1'),
+            'username' => env('REDIS_USERNAME'),
+            'password' => env('REDIS_PASSWORD'),
+            'port' => env('REDIS_PORT', '6379'),
+            'database' => env('REDIS_DB', '0'),
+        ],
+
+        'cache' => [
+            'url' => env('REDIS_URL'),
+            'host' => env('REDIS_HOST', '127.0.0.1'),
+            'username' => env('REDIS_USERNAME'),
+            'password' => env('REDIS_PASSWORD'),
+            'port' => env('REDIS_PORT', '6379'),
+            'database' => env('REDIS_CACHE_DB', '1'),
+        ],
+
     ],
 
 ];


Test patch: