Common database service

[nsa-yoda]

As the primary developer for @sphireinc, I created a common Database interface and related base-level implementations for:

1. Redis
2. ScyllaDB (can be abused for Cassandra)
3. SurrealDB
4. Clickhouse
5. MySQL
6. SQLite
7. Aerospike
8. Kafka
9. Postgres

For the most part, they're just thin wrappers that create a connection given the common interface. For the implementations, I would remove the HealthCheck stuff and anything else that comes from the "ServerStatusCommon.types" package, and probably any of the Query functions as they're largely internal and not general.

This is the interface:

package database

import "github.com/sphireinc/ServerStatusCommon/types"

// Service represents a service that interacts with a database.
type Service interface {
	// Health returns a map of health status information.
	// The keys and values in the map are service-specific.
	Health() (types.HealthCheck, bool)

	// Close terminates the database connection.
	// It returns an error if the connection cannot be closed.
	Close() error

	//	// Query executes a predefined query and returns the results as a slice of maps.
	//	// Each map represents a row with column names as keys and corresponding values.
	//	Query(fileName string) (CommonRows, error)
}

and, the Scylla implementation:

package database

import (
	"fmt"
	"github.com/gocql/gocql"
	"github.com/labstack/echo/v4"
	"github.com/scylladb/gocqlx/v2"
	"github.com/scylladb/gocqlx/v2/qb"
	"github.com/scylladb/gocqlx/v2/table"
	"github.com/sphireinc/ServerStatusCommon/types"
	"log"
	"os"
)

type Health struct {
	Key              string `db:"key"`
	Bootstrapped     string `db:"bootstrapped"`
	BroadcastAddress string `db:"broadcast_address"`
	CqlVersion       string `db:"cql_version"`
	DataCenter       string `db:"data_center"`
	HostID           string `db:"host_id"`
	ListenAddress    string `db:"listen_address"`
	Rack             string `db:"rack"`
	ReleaseVersion   string `db:"release_version"`
	RPCAddress       string `db:"rpc_address"`
	SchemaVersion    string `db:"schema_version"`
}

var HealthMetadata = table.Metadata{
	Name: "system.local",
	Columns: []string{"key", "bootstrapped", "broadcast_address", "cql_version",
		"data_center", "host_id", "listen_address", "rack", "release_version", "rpc_address", "schema_version"},
	PartKey: []string{"key"},
}

var HealthTable = table.New(HealthMetadata)

// ScyllaService represents the ScyllaDB database service.
type ScyllaService struct {
	session gocqlx.Session
	logger  echo.Logger
}

var scyllaInstance *ScyllaService

// NewScyllaService creates a new Scylla service instance.
func NewScyllaService(logger echo.Logger) Service {
	logger.Info("creating new ScyllaService")

	// Reuse Connection
	if scyllaInstance != nil {
		return scyllaInstance
	}

	cluster := gocql.NewCluster(os.Getenv("SCYLLA_HOSTS"))
	cluster.Keyspace = os.Getenv("SCYLLA_KEYSPACE")
	cluster.Consistency = gocql.Quorum
	cluster.Authenticator = gocql.PasswordAuthenticator{
		Username: os.Getenv("SCYLLA_USERNAME"),
		Password: os.Getenv("SCYLLA_PASSWORD"),
	}

	session, err := gocqlx.WrapSession(cluster.CreateSession())
	if err != nil {
		log.Fatal(err)
	}

	scyllaInstance = &ScyllaService{
		session: session,
		logger:  logger,
	}
	logger.Info("created new ScyllaService")
	return scyllaInstance
}

// Close closes the database connection.
func (s *ScyllaService) Close() error {
	log.Printf("Disconnected from database")
	s.session.Close()
	return nil
}

// Query executes a predefined query and returns the results as *gocqlx.Iter.
// It returns an error if the query fails or if there are no rows.
func (s *ScyllaService) Query(action string, tbl *table.Table, params any, where qb.Cmp) (*gocqlx.Iterx, error) {
	var stmt string
	var names []string
	var q *gocqlx.Queryx

	switch action {
	case "INSERT":
		stmt, names = qb.Insert(tbl.Name()).Columns(tbl.Metadata().Columns...).ToCql()
	case "UPDATE":
		stmt, names = qb.Update(tbl.Name()).Set(tbl.Metadata().Columns...).Where(where).ToCql()
	case "SELECT":
		stmt, names = qb.Select(tbl.Name()).Columns(tbl.Metadata().Columns...).ToCql()
	default:
		return nil, fmt.Errorf("unsupported action: %s", action)
	}

	q = s.session.Query(stmt, names).BindStruct(params)
	iter := q.Iter()

	if iter.NumRows() == 0 {
		_ = iter.Close()
		return nil, fmt.Errorf("no rows found")
	}

	return iter, nil
}

// Health checks the health of the database connection by pinging the database.
func (s *ScyllaService) Health() (types.HealthCheck, bool) {
	s.logger.Info("checking database health for ScyllaDB")

	response := types.NewHealthCheck("scylla", "datastore")

// // [CODE OMITTED] // //

	return response, true
}

Would something like this be beneficial for the framework? If so, I would be more than happy to "donate" the watered down code, no strings attached.

[anthdm]

@nsa-yoda This looks really interesting. Let me look into this. How we will interact with the database is still somewhat of a question mark for me.

[nsa-yoda]

@anthdm sounds good! I was thinking of still keeping db.New() and just adding more consts (like DriverSqlite3) while integrating the additional dbs. I'll keep an eye on this issue awaiting your decision on how to move forward.