PostgreSQL support/documentation

[JJGadgets]

Is your feature request related to a problem? Please describe.
It is unclear whether this project supports Postgres for the backing database. The following lines suggest that setting the driver name to postgres works, but there is a lack of official soft-serve documentation (AFAIK, only the README.md) that clarifies this:

soft-serve/server/config/file.go

Lines 84 to 91 in e610867

	# The database configuration.
	db:
	# The database driver to use.
	# Valid values are "sqlite" and "postgres".
	driver: "{{ .DB.Driver }}"
	# The database data source name.
	# This is driver specific and can be a file path or connection string.
	data_source: "{{ .DB.DataSource }}"

soft-serve/server/config/config.go

Lines 89 to 96 in 05ecf8d

	// DBConfig is the database connection configuration.
	type DBConfig struct {
	// Driver is the driver for the database.
	Driver string `env:"DRIVER" yaml:"driver"`
	// DataSource is the database data source name.
	DataSource string `env:"DATA_SOURCE" yaml:"data_source"`
	}

Additionally, no Postgres driver seems to be imported in the db.go file:

soft-serve/server/db/db.go

Lines 3 to 13 in 05ecf8d

	import (
	"context"
	"database/sql"
	"errors"
	"fmt"
	"github.com/charmbracelet/log"
	"github.com/charmbracelet/soft-serve/server/config"
	"github.com/jmoiron/sqlx"
	_ "modernc.org/sqlite" // sqlite driver
	)

My current soft-serve deployment, despite being told to use Postgres as shown, seems to not connect to my Postgres database at all, confirmed via checking the soft-serve.db modified timestamp and Cilium's Hubble UI:
https://github.com/JJGadgets/Biohazard/blob/075fa64be3fa309eef11e156a3bdebce9d8df09a/kube/deploy/apps/soft-serve/app/hr.yaml#L41
https://github.com/JJGadgets/Biohazard/blob/075fa64be3fa309eef11e156a3bdebce9d8df09a/kube/deploy/apps/soft-serve/app/secrets.yaml#L9

Additional context
I am deploying soft-serve on my Kubernetes cluster. To clarify, I have Kubernetes-native clustered storage (Rook-Ceph) available on my Kubernetes nodes directly, but I would like my soft-serve deployment to persist the repo files on my NAS over NFS protocol, so I can access them from the NAS outside soft-serve if my cluster is down.

However, NFS does not provide a reliable connection for SQLite to be stored and used without risk of corruption. Since I already have a highly available Postgres deployment on my Kubernetes cluster with its own backup solution, I would rather use that for the backing database instead of SQLite, while keeping my repo files on the NFS NAS.

Describe the solution you'd like
Clarification on Postgres support with soft-serve to be added to documentation, and if not currently implemented, for Postgres support to be implemented if it's not too disruptive (e.g. if current SQL queries don't use SQLite specifics).

Describe alternatives you've considered
For my specific deployment scenario (as mentioned in Additional Context), having a separate SOFT_SERVE_DB_PATH configuration option specific for the SQLite file(s) to be stored would be nice.

This way, a separate volume can be mounted for SQLite backed by "SQLite-friendly" storage solutions (e.g. Rook-Ceph, iSCSI, NVMeOF etc) while the repo files can be stored on e.g. NFS.

[aymanbagabas]

Hi @JJGadgets, Postgres support was merged into main recently but not released yet. We will add more documentation for configuring the database. Subscribe to the repository to get notified once we release the new version.

For now, if you're running the latest nightly release or using main, you could set SOFT_SERVE_DB_DRIVER=postgres and SOFT_SERVE_DB_DATA_SOURCE=postgres://USER[:PASSWORD]@HOST:5432/soft_serve?sslmode=disable to use Postgres. Make sure to create the database soft_serve first 🙂
Keep in mind that if you already using Soft Serve, you would have to migrate your database from SQLite to Postgres manually.

[JJGadgets]

Appreciate it, I have Soft Serve using one of the main branch images and it's successfully backed by Postgres. README looks accurate to me too. Thanks for the work!