db: use GORM to backup and restore non-legacy tables (#6142)

15 files changed, 510 insertions, 154 deletions

diff --git a/internal/db/access_tokens.go b/internal/db/access_tokens.go
index b6cd5339..115f0105 100644
--- a/internal/db/access_tokens.go
+++ b/internal/db/access_tokens.go
@@ -11,8 +11,8 @@ import (
 	"github.com/jinzhu/gorm"
 	gouuid "github.com/satori/go.uuid"
 
+	"gogs.io/gogs/internal/cryptoutil"
 	"gogs.io/gogs/internal/errutil"
-	"gogs.io/gogs/internal/tool"
 )
 
 // AccessTokensStore is the persistent interface for access tokens.
@@ -56,6 +56,9 @@ type AccessToken struct {
 
 // NOTE: This is a GORM create hook.
 func (t *AccessToken) BeforeCreate() {
+	if t.CreatedUnix > 0 {
+		return
+	}
 	t.CreatedUnix = gorm.NowFunc().Unix()
 }
 
@@ -102,7 +105,7 @@ func (db *accessTokens) Create(userID int64, name string) (*AccessToken, error)
 	token := &AccessToken{
 		UserID: userID,
 		Name:   name,
-		Sha1:   tool.SHA1(gouuid.NewV4().String()),
+		Sha1:   cryptoutil.SHA1(gouuid.NewV4().String()),
 	}
 	return token, db.DB.Create(token).Error
 }
diff --git a/internal/db/access_tokens_test.go b/internal/db/access_tokens_test.go
index 92a2bfa2..8a2f3805 100644
--- a/internal/db/access_tokens_test.go
+++ b/internal/db/access_tokens_test.go
@@ -14,6 +14,22 @@ import (
 	"gogs.io/gogs/internal/errutil"
 )
 
+func TestAccessToken_BeforeCreate(t *testing.T) {
+	t.Run("CreatedUnix has been set", func(t *testing.T) {
+		token := &AccessToken{CreatedUnix: 1}
+		token.BeforeCreate()
+		assert.Equal(t, int64(1), token.CreatedUnix)
+		assert.Equal(t, int64(0), token.UpdatedUnix)
+	})
+
+	t.Run("CreatedUnix has not been set", func(t *testing.T) {
+		token := &AccessToken{}
+		token.BeforeCreate()
+		assert.Equal(t, gorm.NowFunc().Unix(), token.CreatedUnix)
+		assert.Equal(t, int64(0), token.UpdatedUnix)
+	})
+}
+
 func Test_accessTokens(t *testing.T) {
 	if testing.Short() {
 		t.Skip()
@@ -64,7 +80,7 @@ func test_accessTokens_Create(t *testing.T, db *accessTokens) {
 	if err != nil {
 		t.Fatal(err)
 	}
-	assert.Equal(t, gorm.NowFunc().Format(time.RFC3339), token.Created.Format(time.RFC3339))
+	assert.Equal(t, gorm.NowFunc().Format(time.RFC3339), token.Created.UTC().Format(time.RFC3339))
 
 	// Try create second access token with same name should fail
 	_, err = db.Create(token.UserID, token.Name)
@@ -177,5 +193,5 @@ func test_accessTokens_Save(t *testing.T, db *accessTokens) {
 	if err != nil {
 		t.Fatal(err)
 	}
-	assert.Equal(t, gorm.NowFunc().Format(time.RFC3339), token.Updated.Format(time.RFC3339))
+	assert.Equal(t, gorm.NowFunc().Format(time.RFC3339), token.Updated.UTC().Format(time.RFC3339))
 }
diff --git a/internal/db/backup.go b/internal/db/backup.go
new file mode 100644
index 00000000..a02d70d6
--- /dev/null
+++ b/internal/db/backup.go
@@ -0,0 +1,268 @@
+package db
+
+import (
+	"bufio"
+	"fmt"
+	"io"
+	"os"
+	"path/filepath"
+	"reflect"
+	"strings"
+
+	"github.com/jinzhu/gorm"
+	jsoniter "github.com/json-iterator/go"
+	"github.com/pkg/errors"
+	log "unknwon.dev/clog/v2"
+	"xorm.io/core"
+	"xorm.io/xorm"
+
+	"gogs.io/gogs/internal/conf"
+	"gogs.io/gogs/internal/osutil"
+)
+
+// getTableType returns the type name of a table definition without package name,
+// e.g. *db.LFSObject -> LFSObject.
+func getTableType(t interface{}) string {
+	return strings.TrimPrefix(fmt.Sprintf("%T", t), "*db.")
+}
+
+// DumpDatabase dumps all data from database to file system in JSON Lines format.
+func DumpDatabase(db *gorm.DB, dirPath string, verbose bool) error {
+	err := os.MkdirAll(dirPath, os.ModePerm)
+	if err != nil {
+		return err
+	}
+
+	err = dumpLegacyTables(dirPath, verbose)
+	if err != nil {
+		return errors.Wrap(err, "dump legacy tables")
+	}
+
+	for _, table := range tables {
+		tableName := getTableType(table)
+		if verbose {
+			log.Trace("Dumping table %q...", tableName)
+		}
+
+		err := func() error {
+			tableFile := filepath.Join(dirPath, tableName+".json")
+			f, err := os.Create(tableFile)
+			if err != nil {
+				return errors.Wrap(err, "create table file")
+			}
+			defer func() { _ = f.Close() }()
+
+			return dumpTable(db, table, f)
+		}()
+		if err != nil {
+			return errors.Wrapf(err, "dump table %q", tableName)
+		}
+	}
+
+	return nil
+}
+
+func dumpTable(db *gorm.DB, table interface{}, w io.Writer) error {
+	query := db.Model(table).Order("id ASC")
+	switch table.(type) {
+	case *LFSObject:
+		query = db.Model(table).Order("repo_id, oid ASC")
+	}
+
+	rows, err := query.Rows()
+	if err != nil {
+		return errors.Wrap(err, "select rows")
+	}
+	defer func() { _ = rows.Close() }()
+
+	for rows.Next() {
+		elem := reflect.New(reflect.TypeOf(table).Elem()).Interface()
+		err = db.ScanRows(rows, elem)
+		if err != nil {
+			return errors.Wrap(err, "scan rows")
+		}
+
+		err = jsoniter.NewEncoder(w).Encode(elem)
+		if err != nil {
+			return errors.Wrap(err, "encode JSON")
+		}
+	}
+	return nil
+}
+
+func dumpLegacyTables(dirPath string, verbose bool) error {
+	// Purposely create a local variable to not modify global variable
+	legacyTables := append(legacyTables, new(Version))
+	for _, table := range legacyTables {
+		tableName := getTableType(table)
+		if verbose {
+			log.Trace("Dumping table %q...", tableName)
+		}
+
+		tableFile := filepath.Join(dirPath, tableName+".json")
+		f, err := os.Create(tableFile)
+		if err != nil {
+			return fmt.Errorf("create JSON file: %v", err)
+		}
+
+		if err = x.Asc("id").Iterate(table, func(idx int, bean interface{}) (err error) {
+			return jsoniter.NewEncoder(f).Encode(bean)
+		}); err != nil {
+			_ = f.Close()
+			return fmt.Errorf("dump table '%s': %v", tableName, err)
+		}
+		_ = f.Close()
+	}
+	return nil
+}
+
+// ImportDatabase imports data from backup archive in JSON Lines format.
+func ImportDatabase(db *gorm.DB, dirPath string, verbose bool) error {
+	err := importLegacyTables(dirPath, verbose)
+	if err != nil {
+		return errors.Wrap(err, "import legacy tables")
+	}
+
+	for _, table := range tables {
+		tableName := strings.TrimPrefix(fmt.Sprintf("%T", table), "*db.")
+		err := func() error {
+			tableFile := filepath.Join(dirPath, tableName+".json")
+			if !osutil.IsFile(tableFile) {
+				log.Info("Skipped table %q", tableName)
+				return nil
+			}
+
+			if verbose {
+				log.Trace("Importing table %q...", tableName)
+			}
+
+			f, err := os.Open(tableFile)
+			if err != nil {
+				return errors.Wrap(err, "open table file")
+			}
+			defer func() { _ = f.Close() }()
+
+			return importTable(db, table, f)
+		}()
+		if err != nil {
+			return errors.Wrapf(err, "import table %q", tableName)
+		}
+	}
+
+	return nil
+}
+
+func importTable(db *gorm.DB, table interface{}, r io.Reader) error {
+	err := db.DropTableIfExists(table).Error
+	if err != nil {
+		return errors.Wrap(err, "drop table")
+	}
+
+	err = db.AutoMigrate(table).Error
+	if err != nil {
+		return errors.Wrap(err, "auto migrate")
+	}
+
+	rawTableName := db.NewScope(table).TableName()
+	skipResetIDSeq := map[string]bool{
+		"lfs_object": true,
+	}
+
+	scanner := bufio.NewScanner(r)
+	for scanner.Scan() {
+		elem := reflect.New(reflect.TypeOf(table).Elem()).Interface()
+		err = jsoniter.Unmarshal(scanner.Bytes(), elem)
+		if err != nil {
+			return errors.Wrap(err, "unmarshal JSON to struct")
+		}
+
+		err = db.Create(elem).Error
+		if err != nil {
+			return errors.Wrap(err, "create row")
+		}
+	}
+
+	// PostgreSQL needs manually reset table sequence for auto increment keys
+	if conf.UsePostgreSQL && !skipResetIDSeq[rawTableName] {
+		seqName := rawTableName + "_id_seq"
+		if _, err = x.Exec(fmt.Sprintf(`SELECT setval('%s', COALESCE((SELECT MAX(id)+1 FROM "%s"), 1), false);`, seqName, rawTableName)); err != nil {
+			return errors.Wrapf(err, "reset table %q.%q", rawTableName, seqName)
+		}
+	}
+	return nil
+}
+
+func importLegacyTables(dirPath string, verbose bool) error {
+	snakeMapper := core.SnakeMapper{}
+
+	skipInsertProcessors := map[string]bool{
+		"mirror":    true,
+		"milestone": true,
+	}
+
+	// Purposely create a local variable to not modify global variable
+	legacyTables := append(legacyTables, new(Version))
+	for _, table := range legacyTables {
+		tableName := strings.TrimPrefix(fmt.Sprintf("%T", table), "*db.")
+		tableFile := filepath.Join(dirPath, tableName+".json")
+		if !osutil.IsFile(tableFile) {
+			continue
+		}
+
+		if verbose {
+			log.Trace("Importing table %q...", tableName)
+		}
+
+		if err := x.DropTables(table); err != nil {
+			return fmt.Errorf("drop table %q: %v", tableName, err)
+		} else if err = x.Sync2(table); err != nil {
+			return fmt.Errorf("sync table %q: %v", tableName, err)
+		}
+
+		f, err := os.Open(tableFile)
+		if err != nil {
+			return fmt.Errorf("open JSON file: %v", err)
+		}
+		rawTableName := x.TableName(table)
+		_, isInsertProcessor := table.(xorm.BeforeInsertProcessor)
+		scanner := bufio.NewScanner(f)
+		for scanner.Scan() {
+			if err = jsoniter.Unmarshal(scanner.Bytes(), table); err != nil {
+				return fmt.Errorf("unmarshal to struct: %v", err)
+			}
+
+			if _, err = x.Insert(table); err != nil {
+				return fmt.Errorf("insert strcut: %v", err)
+			}
+
+			meta := make(map[string]interface{})
+			if err = jsoniter.Unmarshal(scanner.Bytes(), &meta); err != nil {
+				log.Error("Failed to unmarshal to map: %v", err)
+			}
+
+			// Reset created_unix back to the date save in archive because Insert method updates its value
+			if isInsertProcessor && !skipInsertProcessors[rawTableName] {
+				if _, err = x.Exec("UPDATE `"+rawTableName+"` SET created_unix=? WHERE id=?", meta["CreatedUnix"], meta["ID"]); err != nil {
+					log.Error("Failed to reset 'created_unix': %v", err)
+				}
+			}
+
+			switch rawTableName {
+			case "milestone":
+				if _, err = x.Exec("UPDATE `"+rawTableName+"` SET deadline_unix=?, closed_date_unix=? WHERE id=?", meta["DeadlineUnix"], meta["ClosedDateUnix"], meta["ID"]); err != nil {
+					log.Error("Failed to reset 'milestone.deadline_unix', 'milestone.closed_date_unix': %v", err)
+				}
+			}
+		}
+
+		// PostgreSQL needs manually reset table sequence for auto increment keys
+		if conf.UsePostgreSQL {
+			rawTableName := snakeMapper.Obj2Table(tableName)
+			seqName := rawTableName + "_id_seq"
+			if _, err = x.Exec(fmt.Sprintf(`SELECT setval('%s', COALESCE((SELECT MAX(id)+1 FROM "%s"), 1), false);`, seqName, rawTableName)); err != nil {
+				return fmt.Errorf("reset table %q' sequence: %v", rawTableName, err)
+			}
+		}
+	}
+	return nil
+}
diff --git a/internal/db/backup_test.go b/internal/db/backup_test.go
new file mode 100644
index 00000000..334b8d7d
--- /dev/null
+++ b/internal/db/backup_test.go
@@ -0,0 +1,146 @@
+// Copyright 2020 The Gogs Authors. All rights reserved.
+// Use of this source code is governed by a MIT-style
+// license that can be found in the LICENSE file.
+
+package db
+
+import (
+	"bytes"
+	"os"
+	"path/filepath"
+	"testing"
+	"time"
+
+	"github.com/jinzhu/gorm"
+	"github.com/pkg/errors"
+
+	"gogs.io/gogs/internal/cryptoutil"
+	"gogs.io/gogs/internal/lfsutil"
+	"gogs.io/gogs/internal/testutil"
+)
+
+func Test_dumpAndImport(t *testing.T) {
+	if testing.Short() {
+		t.Skip()
+	}
+
+	t.Parallel()
+
+	if len(tables) != 3 {
+		t.Fatalf("New table has added (want 3 got %d), please add new tests for the table and update this check", len(tables))
+	}
+
+	db := initTestDB(t, "dumpAndImport", tables...)
+	setupDBToDump(t, db)
+	dumpTables(t, db)
+	importTables(t, db)
+
+	// Dump and assert golden again to make sure data aren't changed.
+	dumpTables(t, db)
+}
+
+func setupDBToDump(t *testing.T, db *gorm.DB) {
+	t.Helper()
+
+	vals := []interface{}{
+		&AccessToken{
+			UserID:      1,
+			Name:        "test1",
+			Sha1:        cryptoutil.SHA1("2910d03d-c0b5-4f71-bad5-c4086e4efae3"),
+			CreatedUnix: 1588568886,
+			UpdatedUnix: 1588572486, // 1 hour later
+		},
+		&AccessToken{
+			UserID:      1,
+			Name:        "test2",
+			Sha1:        cryptoutil.SHA1("84117e17-7e67-4024-bd04-1c23e6e809d4"),
+			CreatedUnix: 1588568886,
+		},
+		&AccessToken{
+			UserID:      2,
+			Name:        "test1",
+			Sha1:        cryptoutil.SHA1("da2775ce-73dd-47ba-b9d2-bbcc346585c4"),
+			CreatedUnix: 1588568886,
+		},
+
+		&LFSObject{
+			RepoID:    1,
+			OID:       "ef797c8118f02dfb649607dd5d3f8c7623048c9c063d532cc95c5ed7a898a64f",
+			Size:      100,
+			Storage:   lfsutil.StorageLocal,
+			CreatedAt: time.Unix(1588568886, 0).UTC(),
+		},
+		&LFSObject{
+			RepoID:    2,
+			OID:       "ef797c8118f02dfb649607dd5d3f8c7623048c9c063d532cc95c5ed7a898a64f",
+			Size:      100,
+			Storage:   lfsutil.StorageLocal,
+			CreatedAt: time.Unix(1588568886, 0).UTC(),
+		},
+
+		&LoginSource{
+			Type:      LoginPAM,
+			Name:      "My PAM",
+			IsActived: true,
+			Config: &PAMConfig{
+				ServiceName: "PAM service",
+			},
+			CreatedUnix: 1588568886,
+			UpdatedUnix: 1588572486, // 1 hour later
+		},
+		&LoginSource{
+			Type:      LoginGitHub,
+			Name:      "GitHub.com",
+			IsActived: true,
+			Config: &GitHubConfig{
+				APIEndpoint: "https://api.github.com",
+			},
+			CreatedUnix: 1588568886,
+		},
+	}
+	for _, val := range vals {
+		err := db.Create(val).Error
+		if err != nil {
+			t.Fatal(err)
+		}
+	}
+}
+
+func dumpTables(t *testing.T, db *gorm.DB) {
+	t.Helper()
+
+	for _, table := range tables {
+		tableName := getTableType(table)
+
+		var buf bytes.Buffer
+		err := dumpTable(db, table, &buf)
+		if err != nil {
+			t.Fatalf("%s: %v", tableName, err)
+		}
+
+		golden := filepath.Join("testdata", "backup", tableName+".golden.json")
+		testutil.AssertGolden(t, golden, testutil.Update("Test_dumpAndImport"), buf.String())
+	}
+}
+
+func importTables(t *testing.T, db *gorm.DB) {
+	t.Helper()
+
+	for _, table := range tables {
+		tableName := getTableType(table)
+
+		err := func() error {
+			golden := filepath.Join("testdata", "backup", tableName+".golden.json")
+			f, err := os.Open(golden)
+			if err != nil {
+				return errors.Wrap(err, "open table file")
+			}
+			defer func() { _ = f.Close() }()
+
+			return importTable(db, table, f)
+		}()
+		if err != nil {
+			t.Fatalf("%s: %v", tableName, err)
+		}
+	}
+}
diff --git a/internal/db/db.go b/internal/db/db.go
index ec2dd6ec..a6e53683 100644
--- a/internal/db/db.go
+++ b/internal/db/db.go
@@ -122,15 +122,16 @@ func getLogWriter() (io.Writer, error) {
 	return w, nil
 }
 
+// NOTE: Lines are sorted in alphabetical order, each letter in its own line.
 var tables = []interface{}{
 	new(AccessToken),
 	new(LFSObject), new(LoginSource),
 }
 
-func Init() error {
+func Init() (*gorm.DB, error) {
 	db, err := openDB(conf.Database)
 	if err != nil {
-		return errors.Wrap(err, "open database")
+		return nil, errors.Wrap(err, "open database")
 	}
 	db.SingularTable(true)
 	db.DB().SetMaxOpenConns(conf.Database.MaxOpenConns)
@@ -139,7 +140,7 @@ func Init() error {
 
 	w, err := getLogWriter()
 	if err != nil {
-		return errors.Wrap(err, "get log writer")
+		return nil, errors.Wrap(err, "get log writer")
 	}
 	db.SetLogger(&dbutil.Writer{Writer: w})
 	if !conf.IsProdMode() {
@@ -168,7 +169,7 @@ func Init() error {
 		name := strings.TrimPrefix(fmt.Sprintf("%T", table), "*db.")
 		err = db.AutoMigrate(table).Error
 		if err != nil {
-			return errors.Wrapf(err, "auto migrate %q", name)
+			return nil, errors.Wrapf(err, "auto migrate %q", name)
 		}
 		log.Trace("Auto migrated %q", name)
 	}
@@ -179,7 +180,7 @@ func Init() error {
 
 	sourceFiles, err := loadLoginSourceFiles(filepath.Join(conf.CustomDir(), "conf", "auth.d"))
 	if err != nil {
-		return errors.Wrap(err, "load login source files")
+		return nil, errors.Wrap(err, "load login source files")
 	}
 
 	// Initialize stores, sorted in alphabetical order.
@@ -191,5 +192,5 @@ func Init() error {
 	TwoFactors = &twoFactors{DB: db}
 	Users = &users{DB: db}
 
-	return db.DB().Ping()
+	return db, db.DB().Ping()
 }
diff --git a/internal/db/login_sources.go b/internal/db/login_sources.go
index b620a442..bbc97062 100644
--- a/internal/db/login_sources.go
+++ b/internal/db/login_sources.go
@@ -47,8 +47,8 @@ var LoginSources LoginSourcesStore
 type LoginSource struct {
 	ID        int64
 	Type      LoginType
-	Name      string      `xorm:"UNIQUE"`
-	IsActived bool        `xorm:"NOT NULL DEFAULT false"`
+	Name      string      `xorm:"UNIQUE" gorm:"UNIQUE"`
+	IsActived bool        `xorm:"NOT NULL DEFAULT false" gorm:"NOT NULL"`
 	IsDefault bool        `xorm:"DEFAULT false"`
 	Config    interface{} `xorm:"-" gorm:"-"`
 	RawConfig string      `xorm:"TEXT cfg" gorm:"COLUMN:cfg"`
@@ -63,12 +63,18 @@ type LoginSource struct {
 
 // NOTE: This is a GORM save hook.
 func (s *LoginSource) BeforeSave() (err error) {
+	if s.Config == nil {
+		return nil
+	}
 	s.RawConfig, err = jsoniter.MarshalToString(s.Config)
 	return err
 }
 
 // NOTE: This is a GORM create hook.
 func (s *LoginSource) BeforeCreate() {
+	if s.CreatedUnix > 0 {
+		return
+	}
 	s.CreatedUnix = gorm.NowFunc().Unix()
 	s.UpdatedUnix = s.CreatedUnix
 }
diff --git a/internal/db/login_sources_test.go b/internal/db/login_sources_test.go
index 47bfd6d2..ac657add 100644
--- a/internal/db/login_sources_test.go
+++ b/internal/db/login_sources_test.go
@@ -14,6 +14,44 @@ import (
 	"gogs.io/gogs/internal/errutil"
 )
 
+func TestLoginSource_BeforeSave(t *testing.T) {
+	t.Run("Config has not been set", func(t *testing.T) {
+		s := &LoginSource{}
+		err := s.BeforeSave()
+		if err != nil {
+			t.Fatal(err)
+		}
+		assert.Empty(t, s.RawConfig)
+	})
+
+	t.Run("Config has been set", func(t *testing.T) {
+		s := &LoginSource{
+			Config: &PAMConfig{ServiceName: "pam_service"},
+		}
+		err := s.BeforeSave()
+		if err != nil {
+			t.Fatal(err)
+		}
+		assert.Equal(t, `{"ServiceName":"pam_service"}`, s.RawConfig)
+	})
+}
+
+func TestLoginSource_BeforeCreate(t *testing.T) {
+	t.Run("CreatedUnix has been set", func(t *testing.T) {
+		s := &LoginSource{CreatedUnix: 1}
+		s.BeforeCreate()
+		assert.Equal(t, int64(1), s.CreatedUnix)
+		assert.Equal(t, int64(0), s.UpdatedUnix)
+	})
+
+	t.Run("CreatedUnix has not been set", func(t *testing.T) {
+		s := &LoginSource{}
+		s.BeforeCreate()
+		assert.Equal(t, gorm.NowFunc().Unix(), s.CreatedUnix)
+		assert.Equal(t, gorm.NowFunc().Unix(), s.UpdatedUnix)
+	})
+}
+
 func Test_loginSources(t *testing.T) {
 	if testing.Short() {
 		t.Skip()
@@ -70,8 +108,8 @@ func test_loginSources_Create(t *testing.T, db *loginSources) {
 	if err != nil {
 		t.Fatal(err)
 	}
-	assert.Equal(t, gorm.NowFunc().Format(time.RFC3339), source.Created.Format(time.RFC3339))
-	assert.Equal(t, gorm.NowFunc().Format(time.RFC3339), source.Updated.Format(time.RFC3339))
+	assert.Equal(t, gorm.NowFunc().Format(time.RFC3339), source.Created.UTC().Format(time.RFC3339))
+	assert.Equal(t, gorm.NowFunc().Format(time.RFC3339), source.Updated.UTC().Format(time.RFC3339))
 
 	// Try create second login source with same name should fail
 	_, err = db.Create(CreateLoginSourceOpts{Name: source.Name})
diff --git a/internal/db/main_test.go b/internal/db/main_test.go
index c6cc6f9e..6813118e 100644
--- a/internal/db/main_test.go
+++ b/internal/db/main_test.go
@@ -35,7 +35,7 @@ func TestMain(m *testing.M) {
 		}
 	}
 
-	now := time.Now().Truncate(time.Second)
+	now := time.Now().UTC().Truncate(time.Second)
 	gorm.NowFunc = func() time.Time { return now }
 
 	os.Exit(m.Run())
diff --git a/internal/db/models.go b/internal/db/models.go
index 37783a13..d2868bb6 100644
--- a/internal/db/models.go
+++ b/internal/db/models.go
@@ -5,7 +5,6 @@
 package db
 
 import (
-	"bufio"
 	"database/sql"
 	"fmt"
 	"net/url"
@@ -14,8 +13,7 @@ import (
 	"strings"
 	"time"
 
-	"github.com/json-iterator/go"
-	"github.com/unknwon/com"
+	"github.com/jinzhu/gorm"
 	log "unknwon.dev/clog/v2"
 	"xorm.io/core"
 	"xorm.io/xorm"
@@ -123,10 +121,11 @@ func NewTestEngine() error {
 	return x.StoreEngine("InnoDB").Sync2(legacyTables...)
 }
 
-func SetEngine() (err error) {
+func SetEngine() (*gorm.DB, error) {
+	var err error
 	x, err = getEngine()
 	if err != nil {
-		return fmt.Errorf("connect to database: %v", err)
+		return nil, fmt.Errorf("connect to database: %v", err)
 	}
 
 	x.SetMapper(core.GonicMapper{})
@@ -142,7 +141,7 @@ func SetEngine() (err error) {
 			MaxDays: sec.Key("MAX_DAYS").MustInt64(3),
 		})
 	if err != nil {
-		return fmt.Errorf("create 'xorm.log': %v", err)
+		return nil, fmt.Errorf("create 'xorm.log': %v", err)
 	}
 
 	x.SetMaxOpenConns(conf.Database.MaxOpenConns)
@@ -159,7 +158,7 @@ func SetEngine() (err error) {
 }
 
 func NewEngine() (err error) {
-	if err = SetEngine(); err != nil {
+	if _, err = SetEngine(); err != nil {
 		return err
 	}
 
@@ -219,131 +218,3 @@ type Version struct {
 	ID      int64
 	Version int64
 }
-
-// DumpDatabase dumps all data from database to file system in JSON format.
-func DumpDatabase(dirPath string) error {
-	if err := os.MkdirAll(dirPath, os.ModePerm); err != nil {
-		return err
-	}
-
-	// Purposely create a local variable to not modify global variable
-	allTables := append(legacyTables, new(Version))
-	allTables = append(allTables, tables...)
-	for _, table := range allTables {
-		tableName := strings.TrimPrefix(fmt.Sprintf("%T", table), "*db.")
-		tableFile := path.Join(dirPath, tableName+".json")
-		f, err := os.Create(tableFile)
-		if err != nil {
-			return fmt.Errorf("create JSON file: %v", err)
-		}
-
-		if err = x.Asc("id").Iterate(table, func(idx int, bean interface{}) (err error) {
-			return jsoniter.NewEncoder(f).Encode(bean)
-		}); err != nil {
-			_ = f.Close()
-			return fmt.Errorf("dump table '%s': %v", tableName, err)
-		}
-		_ = f.Close()
-	}
-	return nil
-}
-
-// ImportDatabase imports data from backup archive.
-func ImportDatabase(dirPath string, verbose bool) (err error) {
-	snakeMapper := core.SnakeMapper{}
-
-	skipInsertProcessors := map[string]bool{
-		"mirror":    true,
-		"milestone": true,
-	}
-
-	// Purposely create a local variable to not modify global variable
-	allTables := append(legacyTables, new(Version))
-	allTables = append(allTables, tables...)
-	for _, table := range allTables {
-		tableName := strings.TrimPrefix(fmt.Sprintf("%T", table), "*db.")
-		tableFile := path.Join(dirPath, tableName+".json")
-		if !com.IsExist(tableFile) {
-			continue
-		}
-
-		if verbose {
-			log.Trace("Importing table '%s'...", tableName)
-		}
-
-		if err = x.DropTables(table); err != nil {
-			return fmt.Errorf("drop table '%s': %v", tableName, err)
-		} else if err = x.Sync2(table); err != nil {
-			return fmt.Errorf("sync table '%s': %v", tableName, err)
-		}
-
-		f, err := os.Open(tableFile)
-		if err != nil {
-			return fmt.Errorf("open JSON file: %v", err)
-		}
-		rawTableName := x.TableName(table)
-		_, isInsertProcessor := table.(xorm.BeforeInsertProcessor)
-		scanner := bufio.NewScanner(f)
-		for scanner.Scan() {
-			switch bean := table.(type) {
-			case *LoginSource:
-				meta := make(map[string]interface{})
-				if err = jsoniter.Unmarshal(scanner.Bytes(), &meta); err != nil {
-					return fmt.Errorf("unmarshal to map: %v", err)
-				}
-
-				tp := LoginType(com.StrTo(com.ToStr(meta["Type"])).MustInt64())
-				switch tp {
-				case LoginLDAP, LoginDLDAP:
-					bean.Config = new(LDAPConfig)
-				case LoginSMTP:
-					bean.Config = new(SMTPConfig)
-				case LoginPAM:
-					bean.Config = new(PAMConfig)
-				case LoginGitHub:
-					bean.Config = new(GitHubConfig)
-				default:
-					return fmt.Errorf("unrecognized login source type:: %v", tp)
-				}
-				table = bean
-			}
-
-			if err = jsoniter.Unmarshal(scanner.Bytes(), table); err != nil {
-				return fmt.Errorf("unmarshal to struct: %v", err)
-			}
-
-			if _, err = x.Insert(table); err != nil {
-				return fmt.Errorf("insert strcut: %v", err)
-			}
-
-			meta := make(map[string]interface{})
-			if err = jsoniter.Unmarshal(scanner.Bytes(), &meta); err != nil {
-				log.Error("Failed to unmarshal to map: %v", err)
-			}
-
-			// Reset created_unix back to the date save in archive because Insert method updates its value
-			if isInsertProcessor && !skipInsertProcessors[rawTableName] {
-				if _, err = x.Exec("UPDATE "+rawTableName+" SET created_unix=? WHERE id=?", meta["CreatedUnix"], meta["ID"]); err != nil {
-					log.Error("Failed to reset 'created_unix': %v", err)
-				}
-			}
-
-			switch rawTableName {
-			case "milestone":
-				if _, err = x.Exec("UPDATE "+rawTableName+" SET deadline_unix=?, closed_date_unix=? WHERE id=?", meta["DeadlineUnix"], meta["ClosedDateUnix"], meta["ID"]); err != nil {
-					log.Error("Failed to reset 'milestone.deadline_unix', 'milestone.closed_date_unix': %v", err)
-				}
-			}
-		}
-
-		// PostgreSQL needs manually reset table sequence for auto increment keys
-		if conf.UsePostgreSQL {
-			rawTableName := snakeMapper.Obj2Table(tableName)
-			seqName := rawTableName + "_id_seq"
-			if _, err = x.Exec(fmt.Sprintf(`SELECT setval('%s', COALESCE((SELECT MAX(id)+1 FROM "%s"), 1), false);`, seqName, rawTableName)); err != nil {
-				return fmt.Errorf("reset table '%s' sequence: %v", rawTableName, err)
-			}
-		}
-	}
-	return nil
-}
diff --git a/internal/db/repos_test.go b/internal/db/repos_test.go
index 57bcdbab..42eedbba 100644
--- a/internal/db/repos_test.go
+++ b/internal/db/repos_test.go
@@ -80,7 +80,7 @@ func test_repos_create(t *testing.T, db *repos) {
 	if err != nil {
 		t.Fatal(err)
 	}
-	assert.Equal(t, gorm.NowFunc().Format(time.RFC3339), repo.Created.Format(time.RFC3339))
+	assert.Equal(t, gorm.NowFunc().Format(time.RFC3339), repo.Created.UTC().Format(time.RFC3339))
 }
 
 func test_repos_GetByName(t *testing.T, db *repos) {
diff --git a/internal/db/testdata/backup/AccessToken.golden.json b/internal/db/testdata/backup/AccessToken.golden.json
new file mode 100644
index 00000000..e2085461
--- /dev/null
+++ b/internal/db/testdata/backup/AccessToken.golden.json
@@ -0,0 +1,3 @@
+{"ID":1,"UserID":1,"Name":"test1","Sha1":"56ed62d55225e9ae1275b1c4aa6e3de62f44e730","CreatedUnix":1588568886,"UpdatedUnix":1588572486}
+{"ID":2,"UserID":1,"Name":"test2","Sha1":"16fb74941e834e057d11c59db5d81cdae15be794","CreatedUnix":1588568886,"UpdatedUnix":0}
+{"ID":3,"UserID":2,"Name":"test1","Sha1":"09f170f4ee70ba035587f7df8319b2a3a3d2b74a","CreatedUnix":1588568886,"UpdatedUnix":0}
diff --git a/internal/db/testdata/backup/LFSObject.golden.json b/internal/db/testdata/backup/LFSObject.golden.json
new file mode 100644
index 00000000..45f4ec62
--- /dev/null
+++ b/internal/db/testdata/backup/LFSObject.golden.json
@@ -0,0 +1,2 @@
+{"RepoID":1,"OID":"ef797c8118f02dfb649607dd5d3f8c7623048c9c063d532cc95c5ed7a898a64f","Size":100,"Storage":"local","CreatedAt":"2020-05-04T05:08:06Z"}
+{"RepoID":2,"OID":"ef797c8118f02dfb649607dd5d3f8c7623048c9c063d532cc95c5ed7a898a64f","Size":100,"Storage":"local","CreatedAt":"2020-05-04T05:08:06Z"}
diff --git a/internal/db/testdata/backup/LoginSource.golden.json b/internal/db/testdata/backup/LoginSource.golden.json
new file mode 100644
index 00000000..08a5d1b6
--- /dev/null
+++ b/internal/db/testdata/backup/LoginSource.golden.json
@@ -0,0 +1,2 @@
+{"ID":1,"Type":4,"Name":"My PAM","IsActived":true,"IsDefault":false,"Config":null,"RawConfig":"{\"ServiceName\":\"PAM service\"}","CreatedUnix":1588568886,"UpdatedUnix":1588572486}
+{"ID":2,"Type":6,"Name":"GitHub.com","IsActived":true,"IsDefault":false,"Config":null,"RawConfig":"{\"APIEndpoint\":\"https://api.github.com\"}","CreatedUnix":1588568886,"UpdatedUnix":0}
diff --git a/internal/db/two_factors_test.go b/internal/db/two_factors_test.go
index bbf46f90..4f0ad9da 100644
--- a/internal/db/two_factors_test.go
+++ b/internal/db/two_factors_test.go
@@ -58,7 +58,7 @@ func test_twoFactors_Create(t *testing.T, db *twoFactors) {
 	if err != nil {
 		t.Fatal(err)
 	}
-	assert.Equal(t, gorm.NowFunc().Format(time.RFC3339), tf.Created.Format(time.RFC3339))
+	assert.Equal(t, gorm.NowFunc().Format(time.RFC3339), tf.Created.UTC().Format(time.RFC3339))
 
 	// Verify there are 10 recover codes generated
 	var count int64
diff --git a/internal/db/users_test.go b/internal/db/users_test.go
index e4d8fb86..2be196e3 100644
--- a/internal/db/users_test.go
+++ b/internal/db/users_test.go
@@ -129,8 +129,8 @@ func test_users_Create(t *testing.T, db *users) {
 	if err != nil {
 		t.Fatal(err)
 	}
-	assert.Equal(t, gorm.NowFunc().Format(time.RFC3339), user.Created.Format(time.RFC3339))
-	assert.Equal(t, gorm.NowFunc().Format(time.RFC3339), user.Updated.Format(time.RFC3339))
+	assert.Equal(t, gorm.NowFunc().Format(time.RFC3339), user.Created.UTC().Format(time.RFC3339))
+	assert.Equal(t, gorm.NowFunc().Format(time.RFC3339), user.Updated.UTC().Format(time.RFC3339))
 }
 
 func test_users_GetByEmail(t *testing.T, db *users) {