godatabase

godatabase

A small PostgreSQL layer over sqlx for Go services that keep their SQL in .sql files instead of in string literals.

• Named queries from disk. db.Get(ctx, &u, "user_by_email", email) loads {SqlDir}/user_by_email.sql, caches it, and runs it. SQL lives in files your DBA can read; Go code references it by name.
• A circuit breaker on every call. All queries go through gobreaker, so a database that starts failing trips the breaker instead of letting every request pile up on a dead connection.
• Relations without an ORM. Tag a struct field as a pointer or slice of another model and Populate generates the to_jsonb / jsonb_agg sub-selects from your actual foreign keys, then unmarshals the result into typed Go fields.
• Dynamic filters and pagination built onto a base query without hand-concatenating WHERE clauses.

Requires Go 1.22+ and PostgreSQL. Install with:

go get github.com/jeyem/godatabase

Connecting

Connect creates the target database if it does not exist, pings it, and hands back a *DB. Create one and share it — every method is safe for concurrent use, and there is no global state.

db, err := godatabase.Connect(ctx, &godatabase.ConnectOption{
    URL:         "postgres://user:pass@localhost:5432/myapp?sslmode=disable",
    SqlDir:      "./sql",           // where the .sql files live
    MaxRequests: 1,                 // gobreaker: probes allowed while half-open
    Interval:    30 * time.Second,  // gobreaker: counter reset interval
    Timeout:     10 * time.Second,  // gobreaker: how long it stays open
})
if err != nil {
    return err
}
defer db.Close()

The breaker opens after 4 consecutive failures. To observe transitions, pass a StateChan:

states := make(chan gobreaker.State, 8)
go func() {
    for s := range states {
        metrics.RecordBreakerState(s)
    }
}()
// ConnectOption{..., StateChan: states}

The send is non-blocking, so transitions are dropped if nobody is reading. Buffer the channel or drain it in a goroutine.

Models

A type becomes usable with Fetch and Populate by implementing Model:

type Model interface {
    TableName() string  // the SQL table
    FetchQuery() string // the .sql file name used by Fetch
}

Declare the methods on the value receiver, not a pointer. Reflection resolves the model from both a Post (inside a []Post) and a *Post (a single destination), and only value receivers satisfy both:

func (p Post) TableName() string  { return "posts" }
func (p Post) FetchQuery() string { return "posts_fetch" }

Named queries

Every query is a file under SqlDir. Reads take positional parameters:

-- sql/user_by_email.sql
SELECT * FROM users WHERE email = $1;

var u User
err := db.Get(ctx, &u, "user_by_email", "a@b.com")   // one row
err = db.Select(ctx, &users, "active_users")         // many rows
rows, err := db.Query(ctx, "user_report", since)     // raw *sqlx.Rows

Writes take named parameters, bound from a struct or map:

-- sql/user_insert.sql
INSERT INTO users (id, email, name) VALUES (:id, :email, :name);

res, err := db.Exec(ctx, "user_insert", u)

Transactions use the Tx* variants, which take an *sqlx.Tx from db.Beginx():

tx, err := db.Beginx()
if err != nil {
    return err
}
defer tx.Rollback()

if _, err := db.TxExec(ctx, tx, "user_insert", u); err != nil {
    return err
}
return tx.Commit()

Fetch by id

Fetch infers its query name from the destination's FetchQuery() and expands the ids through sqlx.In, so it handles one id or many with the same file. The query must use a ? bindvar, not $1 — sqlx.In expands ? and then rebinds to $N:

-- sql/posts_fetch.sql
SELECT * FROM posts WHERE id IN (?);

var post Post
err := db.Fetch(ctx, &post, id)             // single

var posts []Post
err = db.Fetch(ctx, &posts, id1, id2, id3)  // many

Filters and pagination

Filters is a map appended to a base query as a WHERE (or AND, if the query already has one), inserted before any trailing ORDER BY / LIMIT / GROUP BY:

err := db.SelectPage(ctx, &posts, "posts_list", godatabase.Filters{
    "status":      "published",       // status = $n
    "author_id":   []uuid.UUID{a, b}, // author_id IN ($n, $n)
    "deleted_at":  nil,               // deleted_at IS NULL
    "views >":     100,               // views > $n
    "title ILIKE": "%go%",            // title ILIKE $n
}, godatabase.Paginate{Limit: 20, Offset: 40})

The key is "column" or "column operator". Supported operators are =, !=, <>, >, <, >=, <=, LIKE, ILIKE, NOT LIKE, NOT ILIKE, IN, NOT IN. With no operator the value's type decides: nil becomes IS NULL, a slice becomes IN (...), anything else becomes = $n. An empty slice degrades to a constant (1 = 0 for IN, 1 = 1 for NOT IN) rather than invalid SQL. Column names are validated against an identifier regex, and values are always bound as parameters — filter keys are not a SQL injection vector, but they are also not a place to put expressions.

Conditions are sorted by key, so placeholder numbering is deterministic. Positional args you pass yourself are numbered first, and the filters continue from there.

The same filters work with GetWhere (single row), SelectWhere / SelectPage (slices), and QueryWhere / QueryPage (raw rows).

Relations

Declare a relation as a pointer to another model (belongs-to) or a slice of one (has-many), paired with a types.JSONText field that receives the raw JSON. The two are matched by tag: the db tag of the JSONText field must equal the json tag of the typed field.

type Post struct {
    ID       uuid.UUID `db:"id"        json:"id"`
    Title    string    `db:"title"     json:"title"`
    AuthorID uuid.UUID `db:"author_id" json:"author_id"`

    AuthorJSON types.JSONText `db:"author" json:"-"`     // raw column
    Author     *User          `db:"-"      json:"author"` // hydrated
}

type User struct {
    ID    uuid.UUID `db:"id"    json:"id"`
    Email string    `db:"email" json:"email"`

    PostsJSON types.JSONText `db:"posts" json:"-"`
    Posts     []Post         `db:"-"     json:"posts"`
}

Populate reads the foreign keys from the database, generates the sub-selects, runs the query, and unmarshals the JSON into Author / Posts:

var u User
err := db.Populate(ctx, &u, godatabase.Filters{"id": userID})
// u.Posts is filled in

var posts []Post
err = db.PopulatePage(ctx, &posts, godatabase.Filters{"status": "published"},
    godatabase.Paginate{Limit: 20})
// each post's Author is filled in

This needs FK metadata, which it reads through a named query you supply as relations.sql:

-- sql/relations.sql
SELECT
    tc.constraint_name,
    tc.table_name,
    kcu.column_name,
    ccu.table_name  AS foreign_table_name,
    ccu.column_name AS foreign_column_name
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
    ON  tc.constraint_name = kcu.constraint_name
    AND tc.table_schema    = kcu.table_schema
JOIN information_schema.constraint_column_usage ccu
    ON  tc.constraint_name = ccu.constraint_name
    AND tc.table_schema    = ccu.table_schema
WHERE tc.constraint_type = 'FOREIGN KEY'
  AND tc.table_name = $1;

Results are cached per table. db.BuildSelect(ctx, model) returns the generated SQL as a string if you want to inspect or extend it.

Generation is one level deep and assumes a single foreign key between any two tables. Anything more complicated — self-joins, multiple FKs to the same table, nested relations — should be a hand-written .sql file.

Things to know

Not every read hydrates. Get, Fetch, GetWhere, and Populate / PopulatePage unmarshal JSONText fields into their typed siblings. Select, SelectWhere, SelectPage, and the Query* methods do not — they leave the JSON raw. Call it yourself when you need it:

err := db.SelectWhere(ctx, &posts, "posts_list", filters)
err = godatabase.UnmarshalJSONTextFields(&posts)

Mismatched tags fail silently. If a JSONText field's db tag doesn't exactly match the target field's json tag, hydration is skipped with no error and the field stays zero. So does a JSON payload that fails to unmarshal — it is logged, not returned.

Timestamps get a fixup. Postgres renders timestamps inside jsonb as 2006-01-02T15:04:05.999999, which encoding/json won't parse into a time.Time. Any key ending in _at is rewritten to RFC3339 before unmarshalling. Name your timestamp columns *_at and they just work; name one created and it won't parse.

The SQL builders are string-based, not a parser. ApplyFilters finds WHERE and trailing keywords with regexes, so it can be fooled by a subquery that contains them. For complex queries, write the .sql file by hand.

Escape hatches. db.Conn() returns the underlying *sqlx.DB, and db.Beginx() / db.BeginTxx() return transactions. Queries you run on those handles bypass the circuit breaker.

Testing

DropDatabase(ctx, url) terminates active backends and drops the database — pair it with Connect (which creates on demand) for integration test setup and teardown:

db, err := godatabase.Connect(ctx, &godatabase.ConnectOption{URL: testURL, SqlDir: "../sql"})
defer godatabase.DropDatabase(context.Background(), testURL)

The package's own tests cover the database-independent logic and need no Postgres:

go test ./...
go test -run TestApplyFilters ./...

License

GPL-3.0. See LICENSE.