database: add PostgreSQL custom schema support (#6695)

Co-authored-by: Homura37 <git@gvip.xyz>
Co-authored-by: Homura <16538800+Homura37@users.noreply.github.com>
Co-authored-by: Joe Chen <jc@unknwon.io>

3 files changed, 16 insertions, 18 deletions

diff --git a/internal/db/db.go b/internal/db/db.go
index a0623344..7d2c12e2 100644
--- a/internal/db/db.go
+++ b/internal/db/db.go
@@ -6,7 +6,6 @@ package db
 
 import (
 	"fmt"
-	"net/url"
 	"path/filepath"
 	"strings"
 	"time"
@@ -74,13 +73,8 @@ func parseDSN(opts conf.DatabaseOpts) (dsn string, err error) {
 
 	case "postgres":
 		host, port := parsePostgreSQLHostPort(opts.Host)
-		if host[0] == '/' { // looks like a unix socket
-			dsn = fmt.Sprintf("postgres://%s:%s@:%s/%s%ssslmode=%s&host=%s",
-				url.QueryEscape(opts.User), url.QueryEscape(opts.Password), port, opts.Name, concate, opts.SSLMode, host)
-		} else {
-			dsn = fmt.Sprintf("postgres://%s:%s@%s:%s/%s%ssslmode=%s",
-				url.QueryEscape(opts.User), url.QueryEscape(opts.Password), host, port, opts.Name, concate, opts.SSLMode)
-		}
+		dsn = fmt.Sprintf("user='%s' password='%s' host='%s' port='%s' dbname='%s' sslmode='%s' search_path='%s'",
+			opts.User, opts.Password, host, port, opts.Name, opts.SSLMode, opts.Schema)
 
 	case "mssql":
 		host, port := parseMSSQLHostPort(opts.Host)
diff --git a/internal/db/db_test.go b/internal/db/db_test.go
index 8c894125..e16c9486 100644
--- a/internal/db/db_test.go
+++ b/internal/db/db_test.go
@@ -96,11 +96,12 @@ func Test_parseDSN(t *testing.T) {
 				Type:     "postgres",
 				Host:     "/tmp/pg.sock",
 				Name:     "gogs",
+				Schema:   "test",
 				User:     "gogs@local",
 				Password: "pa$$word",
 				SSLMode:  "disable",
 			},
-			expDSN: "postgres://gogs%40local:pa%24%24word@:5432/gogs?sslmode=disable&host=/tmp/pg.sock",
+			expDSN: "user='gogs@local' password='pa$$word' host='/tmp/pg.sock' port='5432' dbname='gogs' sslmode='disable' search_path='test'",
 		},
 		{
 			name: "postgres: tcp",
@@ -108,11 +109,12 @@ func Test_parseDSN(t *testing.T) {
 				Type:     "postgres",
 				Host:     "127.0.0.1",
 				Name:     "gogs",
+				Schema:   "test",
 				User:     "gogs@local",
 				Password: "pa$$word",
 				SSLMode:  "disable",
 			},
-			expDSN: "postgres://gogs%40local:pa%24%24word@127.0.0.1:5432/gogs?sslmode=disable",
+			expDSN: "user='gogs@local' password='pa$$word' host='127.0.0.1' port='5432' dbname='gogs' sslmode='disable' search_path='test'",
 		},
 
 		{
diff --git a/internal/db/models.go b/internal/db/models.go
index 6e4fd2a7..82968ae3 100644
--- a/internal/db/models.go
+++ b/internal/db/models.go
@@ -7,7 +7,6 @@ package db
 import (
 	"database/sql"
 	"fmt"
-	"net/url"
 	"os"
 	"path"
 	"path/filepath"
@@ -90,13 +89,8 @@ func getEngine() (*xorm.Engine, error) {
 	case "postgres":
 		conf.UsePostgreSQL = true
 		host, port := parsePostgreSQLHostPort(conf.Database.Host)
-		if host[0] == '/' { // looks like a unix socket
-			connStr = fmt.Sprintf("postgres://%s:%s@:%s/%s%ssslmode=%s&host=%s",
-				url.QueryEscape(conf.Database.User), url.QueryEscape(conf.Database.Password), port, conf.Database.Name, Param, conf.Database.SSLMode, host)
-		} else {
-			connStr = fmt.Sprintf("postgres://%s:%s@%s:%s/%s%ssslmode=%s",
-				url.QueryEscape(conf.Database.User), url.QueryEscape(conf.Database.Password), host, port, conf.Database.Name, Param, conf.Database.SSLMode)
-		}
+		connStr = fmt.Sprintf("user='%s' password='%s' host='%s' port='%s' dbname='%s' sslmode='%s' search_path='%s'",
+			conf.Database.User, conf.Database.Password, host, port, conf.Database.Name, conf.Database.SSLMode, conf.Database.Schema)
 		driver = "pgx"
 
 	case "mssql":
@@ -123,6 +117,10 @@ func NewTestEngine() error {
 		return fmt.Errorf("connect to database: %v", err)
 	}
 
+	if conf.UsePostgreSQL {
+		x.SetSchema(conf.Database.Schema)
+	}
+
 	x.SetMapper(core.GonicMapper{})
 	return x.StoreEngine("InnoDB").Sync2(legacyTables...)
 }
@@ -134,6 +132,10 @@ func SetEngine() (*gorm.DB, error) {
 		return nil, fmt.Errorf("connect to database: %v", err)
 	}
 
+	if conf.UsePostgreSQL {
+		x.SetSchema(conf.Database.Schema)
+	}
+
 	x.SetMapper(core.GonicMapper{})
 
 	var logPath string