Enhance database initialization in db.go (#2645)

- Updated GORM configuration to skip default transactions and prepare statements.
- Modified the database connection string to include caching and journal mode settings.
- Executed several PRAGMA statements to optimize SQLite performance and enable foreign key support.

These changes improve database handling and performance in the application.

Co-authored-by: Zakhar Izmaylov <ptdev@kedruss.ru>

1 files changed, 24 insertions, 2 deletions

diff --git a/database/db.go b/database/db.go
index 300a73c0..2058729f 100644
--- a/database/db.go
+++ b/database/db.go
@@ -82,9 +82,31 @@ func InitDB(dbPath string) error {
 	}
 
 	c := &gorm.Config{
-		Logger: gormLogger,
+		Logger:                 gormLogger,
+		SkipDefaultTransaction: true,
+		PrepareStmt:            true,
 	}
-	db, err = gorm.Open(sqlite.Open(dbPath), c)
+
+	dsn := dbPath + "?cache=shared&_journal_mode=WAL&_synchronous=NORMAL"
+	db, err = gorm.Open(sqlite.Open(dsn), c)
+	if err != nil {
+		return err
+	}
+
+	sqlDB, err := db.DB()
+	if err != nil {
+		return err
+	}
+
+	_, err = sqlDB.Exec("PRAGMA cache_size = -64000;")
+	if err != nil {
+		return err
+	}
+	_, err = sqlDB.Exec("PRAGMA temp_store = MEMORY;")
+	if err != nil {
+		return err
+	}
+	_, err = sqlDB.Exec("PRAGMA foreign_keys = ON;")
 	if err != nil {
 		return err
 	}