source: server/db/db.go

package db

import (
        "bufio"
        "database/sql"
        "embed"
        "fmt"
        "log"
        "os"
        "path/filepath"
        "strings"

        "github.com/lib/pq"
)

var DB *sql.DB

// The SQL scripts are compiled into the binary so that -init works no matter
// which directory the program is started from.
//
//go:embed schema_creation.sql advanced_db.sql data_load.sql
var sqlScripts embed.FS

func Connect() error {
        loadEnvFile()

        host := getenv("DBHOST", "localhost")
        port := getenv("DBPORT", "5432")
        user := getenv("DBUSER", "postgres")
        pass := getenv("DBPASSWORD", "")
        name := getenv("DBNAME", "postgres")

        dsn := fmt.Sprintf(
                "host=%s port=%s user=%s password=%s dbname=%s sslmode=disable options='--search_path=project,public'",
                host, port, user, pass, name,
        )

        // Optional SSH tunnel, the same thing DBeaver's "SSH" tab does. When
        // SSH_HOST is set, DBHOST/DBPORT are resolved from the SSH server's side
        // (for the faculty server that is usually localhost:5432).
        if sshHost := os.Getenv("SSH_HOST"); sshHost != "" {
                dialer, err := newSSHDialer(sshHost)
                if err != nil {
                        return err
                }
                connector, err := pq.NewConnector(dsn)
                if err != nil {
                        return fmt.Errorf("pq.NewConnector: %w", err)
                }
                connector.Dialer(dialer)
                DB = sql.OpenDB(connector)
        } else {
                var err error
                DB, err = sql.Open("postgres", dsn)
                if err != nil {
                        return fmt.Errorf("sql.Open: %w", err)
                }
        }
        if err := DB.Ping(); err != nil {
                return fmt.Errorf("db ping (host=%s port=%s user=%s dbname=%s): %w",
                        host, port, user, name, err)
        }
        return nil
}

// runScript executes one of the embedded .sql scripts as a single statement.
func runScript(name string) error {
        content, err := sqlScripts.ReadFile(name)
        if err != nil {
                return fmt.Errorf("read embedded %s: %w", name, err)
        }
        if _, err := DB.Exec(string(content)); err != nil {
                return fmt.Errorf("exec %s: %w", name, err)
        }
        return nil
}

// RunSQLFile executes a .sql file from disk as a single statement.
func RunSQLFile(path string) error {
        content, err := os.ReadFile(path)
        if err != nil {
                return fmt.Errorf("read %s: %w", path, err)
        }
        if _, err := DB.Exec(string(content)); err != nil {
                return fmt.Errorf("exec %s: %w", path, err)
        }
        return nil
}

// InitSchema runs schema_creation.sql, advanced_db.sql (P7) and data_load.sql.
// Destructive: drops the `project` schema. Intended for the -init flag.
func InitSchema() error {
        for _, name := range []string{"schema_creation.sql", "advanced_db.sql"} {
                log.Printf("Running %s ...", name)
                if err := runScript(name); err != nil {
                        return err
                }
        }
        if err := LoadData(); err != nil {
                return err
        }
        log.Println("Database initialised.")
        return nil
}

// LoadData reloads the sample data without touching the schema.
func LoadData() error {
        log.Println("Running data_load.sql ...")
        return runScript("data_load.sql")
}

// loadEnvFile looks for a .env file in the working directory and in every
// parent directory, so the program can be started from the repo root, from
// server/, or from anywhere else inside the checkout. Variables already set
// in the real environment always win over the file, which is what lets you
// point the prototype at the faculty database with DBHOST=... ./eduberza
func loadEnvFile() {
        dir, err := os.Getwd()
        if err != nil {
                return
        }
        for {
                path := filepath.Join(dir, ".env")
                if applyEnvFile(path) {
                        return
                }
                parent := filepath.Dir(dir)
                if parent == dir {
                        return // reached the filesystem root
                }
                dir = parent
        }
}

// applyEnvFile reports whether the file existed and was read.
func applyEnvFile(path string) bool {
        f, err := os.Open(path)
        if err != nil {
                return false
        }
        defer f.Close()

        s := bufio.NewScanner(f)
        for s.Scan() {
                line := strings.TrimSpace(s.Text())
                if line == "" || strings.HasPrefix(line, "#") {
                        continue
                }
                key, value, ok := strings.Cut(line, "=")
                if !ok {
                        continue
                }
                key = strings.TrimSpace(key)
                // Do not clobber variables that are already set in the environment.
                if _, exists := os.LookupEnv(key); exists {
                        continue
                }
                os.Setenv(key, strings.Trim(strings.TrimSpace(value), `"'`))
        }
        if err := s.Err(); err != nil {
                log.Printf("warning: could not fully read %s: %v", path, err)
        }
        return true
}

func getenv(key, def string) string {
        if v := os.Getenv(key); v != "" {
                return v
        }
        return def
}