Support of basic interaction with Postgres database

New task and job to run Postgres database related tests.
Basic helper functions to make SQL operations easy to use.
Refactoring of sub-commands dependent to SQL.

Part of: https://gitlab.com/gitlab-org/gitaly/issues/2166

12 files changed, 308 insertions, 0 deletions

diff --git a/.gitlab-ci.yml b/.gitlab-ci.yml
index 2d00ce770..7717140be 100644
--- a/.gitlab-ci.yml
+++ b/.gitlab-ci.yml
@@ -219,6 +219,19 @@ praefect_sql_connect:
     - ./praefect -config config.praefect.toml sql-ping
     - ./praefect -config config.praefect.toml sql-migrate
 
+praefect_sql_test:
+  <<: *go_test_definition
+  services:
+    - postgres:9.6
+  variables:
+    PGHOST: postgres
+    PGPORT: 5432
+    PGUSER: postgres
+  script:
+    - go version
+    - git version
+    - make test-postgres
+
 lint:
   image: registry.gitlab.com/gitlab-org/gitlab-build-images:golangci-lint-alpine
   stage: test
diff --git a/Makefile b/Makefile
index a0cfa73ad..dd39c3df8 100644
--- a/Makefile
+++ b/Makefile
@@ -76,6 +76,10 @@ rspec: prepare-build
 rspec-gitlab-shell: prepare-build
 	cd $(BUILD_DIR) && $(MAKE) $@
 
+.PHONY: test-postgres
+test-postgres: prepare-build
+	cd $(BUILD_DIR) && $(MAKE) $@
+
 .PHONY: verify
 verify: prepare-build
 	cd $(BUILD_DIR) && $(MAKE) $@
diff --git a/_support/Makefile.template b/_support/Makefile.template
index d522ef53c..066ec4d96 100644
--- a/_support/Makefile.template
+++ b/_support/Makefile.template
@@ -149,6 +149,10 @@ rspec-gitlab-shell: {{ .GitlabShellDir }}/config.yml assemble-go prepare-tests
 	# rspec in {{ .GitlabShellRelDir }}
 	@cd  {{ .GitalyRubyDir }} && bundle exec bin/ruby-cd {{ .GitlabShellDir }} rspec
 
+.PHONY: test-postgres
+test-postgres: prepare-tests
+	@cd {{ .SourceDir }} && go test -tags postgres gitlab.com/gitlab-org/gitaly/internal/praefect/datastore/...
+
 .PHONY: verify
 verify: check-mod-tidy check-formatting notice-up-to-date check-proto rubocop
 
diff --git a/changelogs/unreleased/ps-sql-interaction.yml b/changelogs/unreleased/ps-sql-interaction.yml
new file mode 100644
index 000000000..6b8db25d3
--- /dev/null
+++ b/changelogs/unreleased/ps-sql-interaction.yml
@@ -0,0 +1,5 @@
+---
+title: Support of basic interaction with Postgres database
+merge_request: 1768
+author:
+type: added
diff --git a/internal/praefect/coordinator.go b/internal/praefect/coordinator.go
index f1b1d5492..22c0a83a8 100644
--- a/internal/praefect/coordinator.go
+++ b/internal/praefect/coordinator.go
@@ -206,6 +206,9 @@ func (c *Coordinator) createReplicaJobs(targetRepo *gitalypb.Repository, primary
 	return func() {
 		for _, jobID := range jobIDs {
 			if err := c.datastore.UpdateReplJob(jobID, datastore.JobStateReady); err != nil {
+				// TODO: in case of error the job remains in queue in 'pending' state and leads to:
+				//  - additional memory consumption
+				//  - stale state of one of the git data stores
 				c.log.WithField("job_id", jobID).WithError(err).Errorf("error when updating replication job to %d", datastore.JobStateReady)
 			}
 		}
diff --git a/internal/praefect/datastore/datastore_test.go b/internal/praefect/datastore/datastore_test.go
index 6e908ffb1..ffae23dd1 100644
--- a/internal/praefect/datastore/datastore_test.go
+++ b/internal/praefect/datastore/datastore_test.go
@@ -1,3 +1,5 @@
+// +build !postgres
+
 package datastore
 
 import (
diff --git a/internal/praefect/datastore/glsql/doc.go b/internal/praefect/datastore/glsql/doc.go
new file mode 100644
index 000000000..9983661ff
--- /dev/null
+++ b/internal/praefect/datastore/glsql/doc.go
@@ -0,0 +1,38 @@
+// Package glsql provides integration with SQL database. It contains a set
+// of functions and structures that help to interact with SQL database and
+// to write tests to check it.
+
+// A simple unit tests do not require any additional dependencies except mocks.
+// The tests to check interaction with a database require database to be up and
+// running.
+// You must provide PGHOST and PGPORT environment variables to run the tests.
+// PGHOST - is a host of the Postgres database to connect to.
+// PGPORT - is a port which is used by Postgres database to listen for incoming
+// connections.
+//
+// If you use Gitaly inside GDK or want to reuse Postgres instance from GDK
+// navigate to gitaly folder from GDK root and run command:
+// $ gdk env
+// it should print PGHOST and PGPORT for you.
+
+// To check if everything configured properly run the command:
+//
+// $ PGHOST=<host of db instance> \
+//   PGPORT=<port of db instance> \
+//   go test \
+//    -tags=postgres \
+//    -v \
+//    -count=1 \
+//    gitlab.com/gitlab-org/gitaly/internal/praefect/datastore/glsql \
+//    -run=^TestOpenDB$
+//
+// Once it is finished successfully you can be sure other tests would be able to
+// connect to the database and interact with it.
+//
+// As you may note there is a special build tag `postgres` in the `go` command
+// above. This build tag distinguishes tests that depend on the database from those
+// which are not. When adding a new tests with a dependency to database please add
+// this build tag to them. The example how to do this could be found in
+// internal/praefect/datastore/glsql/postgres_test.go file.
+
+package glsql
diff --git a/internal/praefect/datastore/glsql/init_test.go b/internal/praefect/datastore/glsql/init_test.go
new file mode 100644
index 000000000..34b024451
--- /dev/null
+++ b/internal/praefect/datastore/glsql/init_test.go
@@ -0,0 +1,18 @@
+// +build postgres
+
+package glsql
+
+import (
+	"log"
+	"os"
+	"testing"
+)
+
+func TestMain(m *testing.M) {
+	code := m.Run()
+	// Clean closes connection to database once all tests are done
+	if err := Clean(); err != nil {
+		log.Fatalln(err, "database disconnection failure")
+	}
+	os.Exit(code)
+}
diff --git a/internal/praefect/datastore/glsql/postgres.go b/internal/praefect/datastore/glsql/postgres.go
new file mode 100644
index 000000000..dcd81cbca
--- /dev/null
+++ b/internal/praefect/datastore/glsql/postgres.go
@@ -0,0 +1,32 @@
+// Package glsql (Gitaly SQL) is a helper package to work with plain SQL queries.
+package glsql
+
+import (
+	"database/sql"
+
+	// Blank import to enable integration of github.com/lib/pq into database/sql
+	_ "github.com/lib/pq"
+	migrate "github.com/rubenv/sql-migrate"
+	"gitlab.com/gitlab-org/gitaly/internal/praefect/config"
+	"gitlab.com/gitlab-org/gitaly/internal/praefect/datastore/migrations"
+)
+
+// OpenDB returns connection pool to the database.
+func OpenDB(conf config.DB) (*sql.DB, error) {
+	db, err := sql.Open("postgres", conf.ToPQString())
+	if err != nil {
+		return nil, err
+	}
+
+	if err := db.Ping(); err != nil {
+		return nil, err
+	}
+
+	return db, nil
+}
+
+// Migrate will apply all pending SQL migrations.
+func Migrate(db *sql.DB) (int, error) {
+	migrationSource := &migrate.MemoryMigrationSource{Migrations: migrations.All()}
+	return migrate.Exec(db, "postgres", migrationSource, migrate.Up)
+}
diff --git a/internal/praefect/datastore/glsql/postgres_test.go b/internal/praefect/datastore/glsql/postgres_test.go
new file mode 100644
index 000000000..ff056dabf
--- /dev/null
+++ b/internal/praefect/datastore/glsql/postgres_test.go
@@ -0,0 +1,39 @@
+// +build postgres
+
+package glsql
+
+import (
+	"os"
+	"strconv"
+	"testing"
+
+	"github.com/stretchr/testify/require"
+	"gitlab.com/gitlab-org/gitaly/internal/praefect/config"
+)
+
+func TestOpenDB(t *testing.T) {
+	dbCfg := config.DB{
+		Host: os.Getenv("PGHOST"),
+		Port: func() int {
+			pgPort := os.Getenv("PGPORT")
+			port, err := strconv.Atoi(pgPort)
+			require.NoError(t, err, "failed to parse PGPORT %q", pgPort)
+			return port
+		}(),
+		DBName:  "postgres",
+		SSLMode: "disable",
+	}
+
+	t.Run("failed to ping because of incorrect config", func(t *testing.T) {
+		badCfg := dbCfg
+		badCfg.Host = "not-existing.com"
+		_, err := OpenDB(badCfg)
+		require.Error(t, err, "opening of DB with incorrect configuration must fail")
+	})
+
+	t.Run("connected with proper config", func(t *testing.T) {
+		db, err := OpenDB(dbCfg)
+		require.NoError(t, err, "opening of DB with correct configuration must not fail")
+		require.NoError(t, db.Close())
+	})
+}
diff --git a/internal/praefect/datastore/glsql/testing.go b/internal/praefect/datastore/glsql/testing.go
new file mode 100644
index 000000000..794366162
--- /dev/null
+++ b/internal/praefect/datastore/glsql/testing.go
@@ -0,0 +1,118 @@
+package glsql
+
+import (
+	"database/sql"
+	"errors"
+	"fmt"
+	"os"
+	"strconv"
+	"strings"
+	"sync"
+	"testing"
+
+	"github.com/stretchr/testify/require"
+	"gitlab.com/gitlab-org/gitaly/internal/praefect/config"
+)
+
+var (
+	// testDB is a shared database connection pool that needs to be used only for testing.
+	// Initialization of it happens on the first call to GetDB and it remains open until call to Clean.
+	testDB         DB
+	testDBInitOnce sync.Once
+)
+
+// DB is a helper struct that should be used only for testing purposes.
+type DB struct {
+	*sql.DB
+}
+
+// Truncate removes all data from the list of tables and restarts identities for them.
+func (db DB) Truncate(t testing.TB, tables ...string) {
+	t.Helper()
+
+	tmpl := strings.Repeat("TRUNCATE TABLE %q RESTART IDENTITY;\n", len(tables))
+	params := make([]interface{}, len(tables))
+	for i, table := range tables {
+		params[i] = table
+	}
+	query := fmt.Sprintf(tmpl, params...)
+	_, err := db.DB.Exec(query)
+	require.NoError(t, err, "database truncation failed: %s", tables)
+}
+
+// Close removes schema if it was used and releases connection pool.
+func (db DB) Close() error {
+	if err := db.DB.Close(); err != nil {
+		return errors.New("failed to release connection pool: " + err.Error())
+	}
+	return nil
+}
+
+// GetDB returns a wrapper around the database connection pool.
+// Must be used only for testing.
+// The new database 'gitaly_test' will be re-created for each package that uses this function.
+// The best place to call it is in individual testing functions
+// It uses env vars:
+//   PGHOST - required, URL/socket/dir
+//   PGPORT - required, binding port
+//   PGUSER - optional, user - `$ whoami` would be used if not provided
+func GetDB(t testing.TB) DB {
+	t.Helper()
+
+	testDBInitOnce.Do(func() {
+		sqlDB := initGitalyTestDB(t)
+
+		_, mErr := Migrate(sqlDB)
+		require.NoError(t, mErr, "failed to run database migration")
+		testDB = DB{DB: sqlDB}
+	})
+	return testDB
+}
+
+func initGitalyTestDB(t testing.TB) *sql.DB {
+	t.Helper()
+
+	host, hostFound := os.LookupEnv("PGHOST")
+	require.True(t, hostFound, "PGHOST env var expected to be provided to connect to Postgres database")
+
+	port, portFound := os.LookupEnv("PGPORT")
+	require.True(t, portFound, "PGPORT env var expected to be provided to connect to Postgres database")
+	portNumber, pErr := strconv.Atoi(port)
+	require.NoError(t, pErr, "PGPORT must be a port number of the Postgres database listens for incoming connections")
+
+	// connect to 'postgres' database first to re-create testing database from scratch
+	dbCfg := config.DB{
+		Host:    host,
+		Port:    portNumber,
+		DBName:  "postgres",
+		SSLMode: "disable",
+		User:    os.Getenv("PGUSER"),
+	}
+
+	postgresDB, oErr := OpenDB(dbCfg)
+	require.NoError(t, oErr, "failed to connect to 'postgres' database")
+	defer func() { require.NoError(t, postgresDB.Close()) }()
+
+	_, dErr := postgresDB.Exec("DROP DATABASE IF EXISTS gitaly_test")
+	require.NoError(t, dErr, "failed to drop 'gitaly_test' database")
+
+	_, cErr := postgresDB.Exec("CREATE DATABASE gitaly_test WITH ENCODING 'UTF8'")
+	require.NoError(t, cErr, "failed to create 'gitaly_test' database")
+	require.NoError(t, postgresDB.Close(), "error on closing connection to 'postgres' database")
+
+	// connect to the testing database
+	dbCfg.DBName = "gitaly_test"
+	gitalyTestDB, err := OpenDB(dbCfg)
+	require.NoError(t, err, "failed to connect to 'gitaly_test' database")
+	return gitalyTestDB
+}
+
+// Clean removes created schema if any and releases DB connection pool.
+// It needs to be called only once after all tests for package are done.
+// The best place to use it is TestMain(*testing.M) {...} after m.Run().
+func Clean() error {
+	if testDB.DB != nil {
+		return testDB.Close()
+	}
+	return nil
+}
diff --git a/internal/praefect/datastore/glsql/testing_test.go b/internal/praefect/datastore/glsql/testing_test.go
new file mode 100644
index 000000000..ad576d1ba
--- /dev/null
+++ b/internal/praefect/datastore/glsql/testing_test.go
@@ -0,0 +1,32 @@
+// +build postgres
+
+package glsql
+
+import (
+	"testing"
+
+	"github.com/stretchr/testify/require"
+)
+
+func TestDB_Truncate(t *testing.T) {
+	db := GetDB(t)
+
+	_, err := db.Exec("CREATE TABLE truncate_tbl(id BIGSERIAL PRIMARY KEY)")
+	require.NoError(t, err)
+
+	res, err := db.Exec("INSERT INTO truncate_tbl VALUES (DEFAULT), (DEFAULT)")
+	require.NoError(t, err)
+	affected, err := res.RowsAffected()
+	require.NoError(t, err)
+	require.Equal(t, int64(2), affected, "2 rows must be inserted into the table")
+
+	db.Truncate(t, "truncate_tbl")
+
+	var count int
+	require.NoError(t, db.QueryRow("SELECT COUNT(*) FROM truncate_tbl").Scan(&count))
+	require.Equal(t, 0, count, "no rows must exist after TRUNCATE operation")
+
+	var id int
+	require.NoError(t, db.QueryRow("INSERT INTO truncate_tbl VALUES (DEFAULT) RETURNING id").Scan(&id))
+	require.Equal(t, 1, id, "sequence for primary key must be restarted")
+}