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.

2 files changed, 30 insertions, 10 deletions

diff --git a/database/db.go b/database/db.go
index c72d28cf..05fc64cb 100644
--- a/database/db.go
+++ b/database/db.go
@@ -9,14 +9,15 @@ import (
 	"path"
 	"slices"
 
+	"gorm.io/driver/mysql"
+	"gorm.io/driver/sqlite"
+	"gorm.io/gorm"
+	"gorm.io/gorm/logger"
+
 	"x-ui/config"
 	"x-ui/database/model"
 	"x-ui/util/crypto"
 	"x-ui/xray"
-
-	"gorm.io/driver/sqlite"
-	"gorm.io/gorm"
-	"gorm.io/gorm/logger"
 )
 
 var db *gorm.DB
@@ -114,12 +115,22 @@ func isTableEmpty(tableName string) (bool, error) {
 }
 
 func InitDB(dbPath string) error {
-	dir := path.Dir(dbPath)
-	err := os.MkdirAll(dir, fs.ModePerm)
+	dbConfig, err := config.GetDatabaseConfig()
 	if err != nil {
 		return err
 	}
 
+	if dbConfig.Connection != "mysql" {
+		// Connection is sqlite
+		// Need to create the directory if it doesn't exist
+
+		dir := path.Dir(dbPath)
+		err = os.MkdirAll(dir, fs.ModePerm)
+		if err != nil {
+			return err
+		}
+	}
+
 	var gormLogger logger.Interface
 
 	if config.IsDebug() {
@@ -131,9 +142,18 @@ func InitDB(dbPath string) error {
 	c := &gorm.Config{
 		Logger: gormLogger,
 	}
-	db, err = gorm.Open(sqlite.Open(dbPath), c)
-	if err != nil {
-		return err
+
+	if dbConfig.Connection == "mysql" {
+		db, err = gorm.Open(mysql.Open(dbPath), c)
+		if err != nil {
+			return err
+		}
+	} else {
+		// Connection is sqlite
+		db, err = gorm.Open(sqlite.Open(dbPath), c)
+		if err != nil {
+			return err
+		}
 	}
 
 	if err := initModels(); err != nil {
diff --git a/database/model/model.go b/database/model/model.go
index 2e7095d3..767e12a9 100644
--- a/database/model/model.go
+++ b/database/model/model.go
@@ -86,7 +86,7 @@ func (i *Inbound) GenXrayInboundConfig() *xray.InboundConfig {
 
 type Setting struct {
 	Id    int    `json:"id" form:"id" gorm:"primaryKey;autoIncrement"`
-	Key   string `json:"key" form:"key"`
+	Key   string `json:"key" form:"key" gorm:"column:key"`
 	Value string `json:"value" form:"value"`
 }