Database

iSPA CMS supports both MySQL/MariaDB and PostgreSQL simultaneously, separated by domain.

Architecture

MySQL Configuration

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=ispa_cms
DB_USERNAME=root
DB_PASSWORD=

Docker Compose uses MariaDB 10:

services:
  mysql:
    image: 'mariadb:10'
    ports:
      - '${FORWARD_DB_PORT:-3306}:3306'
    environment:
      MYSQL_ROOT_PASSWORD: '${DB_PASSWORD}'
      MYSQL_DATABASE: '${DB_DATABASE}'

PostgreSQL Configuration

Environment variables use the PTG_ prefix:

GAME_DB_CONNECTION=pgsql
PTG_HOST=127.0.0.1
PTG_PORT=5432
PTG_DATABASE=ispa_dev
PTG_USERNAME=ispa_dev_user
PTG_PASSWORD=
PTG_SEARCH_PATH=public
PTG_SSLMODE=prefer

Docker Compose with the pgsql profile:

services:
  pgsql:
    image: 'postgres:16'
    profiles:
      - pgsql
    ports:
      - '${FORWARD_PGSQL_PORT:-5432}:5432'

Activate:

COMPOSE_PROFILES=pgsql docker-compose up

Driver-Aware Code

Migrations and commands automatically detect the database driver:

• Foreign keys: MySQL uses SET FOREIGN_KEY_CHECKS=0, PostgreSQL uses SET session_replication_role = replica.
• UPDATE IGNORE: MySQL supports natively, PostgreSQL uses ON CONFLICT DO NOTHING.

if (config('database.default') === 'pgsql') {
    DB::statement('SET session_replication_role = replica');
} else {
    DB::statement('SET FOREIGN_KEY_CHECKS=0');
}

Laravel Config

'pgsql' => [
    'driver'         => 'pgsql',
    'host'           => env('PTG_HOST', '127.0.0.1'),
    'port'           => env('PTG_PORT', '5432'),
    'database'       => env('PTG_DATABASE', 'forge'),
    'username'       => env('PTG_USERNAME', 'forge'),
    'password'       => env('PTG_PASSWORD', ''),
    'charset'        => 'utf8',
    'search_path'    => env('PTG_SEARCH_PATH', 'public'),
    'sslmode'        => env('PTG_SSLMODE', 'prefer'),
],