postgres_integration_test.go

package dbx

import (
	"os"
	"testing"

	_ "github.com/jackc/pgx/v5/stdlib"
)

func openPostgres(t *testing.T) *DB {
	t.Helper()

	dsn := os.Getenv("DBX_PG_DSN")
	if dsn == "" {
		t.Skip("DBX_PG_DSN is not set; skipping postgres integration test")
	}

	db, err := Open("pgx", dsn)
	if err != nil {
		t.Fatalf("open postgres: %v", err)
	}
	t.Cleanup(func() { _ = db.Close() })
	return db
}

func TestPostgresIntegrationSelectModelUpsert(t *testing.T) {
	db := openPostgres(t)

	mustExec(t, db.NewQuery(`DROP TABLE IF EXISTS users`))
	mustExec(t, db.NewQuery(`CREATE TABLE users (
		id BIGSERIAL PRIMARY KEY,
		name TEXT NOT NULL,
		email TEXT NOT NULL UNIQUE,
		status TEXT NOT NULL DEFAULT 'new'
	)`))

	u := &testUser{Name: "alice", Email: "a@pg.io"}
	if err := db.Model(u).Insert(); err != nil {
		t.Fatalf("model insert: %v", err)
	}
	if u.ID == 0 {
		t.Fatalf("expected generated id, got %d", u.ID)
	}

	mustExec(t, db.Upsert("users", Params{
		"name":   "alice-v2",
		"email":  "a@pg.io",
		"status": "active",
	}, "email"))

	var out []struct {
		ID     int64  `db:"id"`
		Name   string `db:"name"`
		Status string `db:"status"`
	}
	err := db.Select("id", "name", "status").
		From("users").
		Where(HashExp{"email": "a@pg.io"}).
		All(&out)
	if err != nil {
		t.Fatalf("select all: %v", err)
	}
	if len(out) != 1 || out[0].Name != "alice-v2" || out[0].Status != "active" {
		t.Fatalf("unexpected row: %#v", out)
	}
}