feat: Add MySQL database support (#3024)MySQL-databases

* feat: Add MySQL database support

- Add MySQL database support with environment-based configuration
- Fix MySQL compatibility issue with 'key' column name
- Maintain SQLite as default database
- Add proper validation for MySQL configuration
- Test and verify compatibility with existing database
- Replaced raw SQL queries using JSON_EACH functions with standard GORM queries
- Modified functions to handle JSON parsing in Go code instead of database since JSON_EACH is not available on MySQL or MariaDB:
  - getAllEmails()
  - GetClientTrafficByID()
  - getFallbackMaster()
  - MigrationRemoveOrphanedTraffics()

The system now supports both MySQL and SQLite databases, with SQLite remaining as the default option. MySQL connection is only used when explicitly configured through environment variables.

* refactor: prefix env variables of database with XUI_ to support direct environment usage without .env file

All database configuration environment variables now start with the XUI_ prefix to avoid conflicts and allow configuration via system-level environment variables, not just the .env file.

1 files changed, 49 insertions, 0 deletions

diff --git a/config/config.go b/config/config.go
index 70be5ae6..942de7fb 100644
--- a/config/config.go
+++ b/config/config.go
@@ -3,6 +3,7 @@ package config
 import (
 	_ "embed"
 	"fmt"
+	"log"
 	"os"
 	"strings"
 )
@@ -62,7 +63,55 @@ func GetDBFolderPath() string {
 	return dbFolderPath
 }
 
+// DatabaseConfig holds the database configuration
+type DatabaseConfig struct {
+	Connection string
+	Host       string
+	Port       string
+	Database   string
+	Username   string
+	Password   string
+}
+
+// GetDatabaseConfig returns the database configuration from environment variables
+func GetDatabaseConfig() (*DatabaseConfig, error) {
+	config := &DatabaseConfig{
+		Connection: strings.ToLower(os.Getenv("XUI_DB_CONNECTION")),
+		Host:       os.Getenv("XUI_DB_HOST"),
+		Port:       os.Getenv("XUI_DB_PORT"),
+		Database:   os.Getenv("XUI_DB_DATABASE"),
+		Username:   os.Getenv("XUI_DB_USERNAME"),
+		Password:   os.Getenv("XUI_DB_PASSWORD"),
+	}
+
+	if config.Connection == "mysql" {
+		if config.Host == "" || config.Database == "" || config.Username == "" {
+			return nil, fmt.Errorf("missing required MySQL configuration: host, database, and username are required")
+		}
+		if config.Port == "" {
+			config.Port = "3306"
+		}
+	}
+
+	return config, nil
+}
+
 func GetDBPath() string {
+	config, err := GetDatabaseConfig()
+	if err != nil {
+		log.Fatalf("Error getting database config: %v", err)
+	}
+
+	if config.Connection == "mysql" {
+		return fmt.Sprintf("%s:%s@tcp(%s:%s)/%s?charset=utf8&parseTime=True&loc=Local",
+			config.Username,
+			config.Password,
+			config.Host,
+			config.Port,
+			config.Database)
+	}
+
+	// Connection is sqlite
 	return fmt.Sprintf("%s/%s.db", GetDBFolderPath(), GetName())
 }