dbx

Lightweight SQL toolkit for Go on top of database/sql:

• query builder (non-ORM)
• PostgreSQL + SQLite support
• named parameters
• model CRUD
• transactions, savepoints, upsert, returning

Status

Production-oriented, pre-v1 API (v0.x recommended).

Compatibility

• Go: 1.22+
• Module path: github.com/pafthang/dbx

Features

• Core DB wrapper:
  • Open, MustOpen, NewFromDB, WithContext
  • Begin, BeginTx, Transactional, TransactionalContext
  • Savepoint, RollbackTo, Release
• Query API:
  • NewQuery, Bind, Execute
  • All, One, Row, Rows, Column, Pluck, Map
• Select builder:
  • Select, From, joins, Where, GroupBy, Having, OrderBy
  • Limit, Offset, Page, PerPage
  • Union, UnionAll
  • CTE: With, WithRecursive, WithRaw
  • analytics helpers: Count, CountDistinct, Exists
• DML:
  • Insert, InsertMany, Update, Delete, Upsert
  • InsertReturning, InsertManyReturning, UpdateReturning, DeleteReturning
  • UpsertOnConflict via OnConflict DSL
• Model CRUD:
  • Model(...).Insert/Update/Delete/Save
  • PK tag support (db:"id,pk")
  • auto PK fill on insert
• Operational tooling:
  • query/exec hooks (QueryLogFunc, ExecLogFunc)
  • trace hook (TraceHook)
  • optional prepared statement cache (EnableStatementCache)
• Generic helpers:
  • SelectAll[T], SelectOne[T], SelectOneOrZero[T]
  • QueryAll[T], QueryOne[T]

Installation

go get github.com/pafthang/dbx

Drivers

SQLite (CGO-free):

import _ "modernc.org/sqlite"

PostgreSQL:

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

Quick Start

Connect

db, err := dbx.Open("sqlite", ":memory:")
if err != nil {
	panic(err)
}
defer db.Close()

Raw query with named params

var names []string
err := db.NewQuery("SELECT name FROM users WHERE status={:status}").
	Bind(dbx.Params{"status": "active"}).
	Column(&names)

Select builder

type User struct {
	ID    int64  `db:"id"`
	Name  string `db:"name"`
	Email string `db:"email"`
}

var users []User
err := db.Select("id", "name", "email").
	From("users").
	Where(dbx.And(
		dbx.HashExp{"status": "active"},
		dbx.Like("name", "al"),
	)).
	OrderBy("id DESC").
	Page(1, 20).
	All(&users)

Model CRUD

type User struct {
	ID    int64  `db:"id,pk"`
	Name  string `db:"name"`
	Email string `db:"email"`
}

func (User) TableName() string { return "users" }

u := &User{Name: "alice", Email: "a@example.com"}
_ = db.Model(u).Insert()
_ = db.Model(u).Update("name")
_ = db.Model(u).Delete()

Query Builder Guide

Joins

Supported join methods:

• Join
• InnerJoin
• LeftJoin
• RightJoin
• FullJoin
• CrossJoin
• JoinSubquery
• LeftJoinSubquery
• InnerJoinSubquery

sub := db.Select("user_id").From("events").Where(dbx.HashExp{"kind": "login"})

err := db.Select("u.id", "u.name").
	From("users u").
	LeftJoin("profiles p", dbx.Raw("p.user_id = u.id")).
	JoinSubquery(sub, "e", dbx.Raw("e.user_id = u.id")).
	Where(dbx.HashExp{"u.status": "active"}).
	All(&users)

CTE

active := db.Select("id", "name").
	From("users").
	Where(dbx.HashExp{"status": "active"})

err := db.Select("id", "name").
	With("active_users", active).
	From("active_users").
	All(&users)

Aggregation helpers

total, _ := db.Select("id").From("users").Count()
distinctNames, _ := db.Select("name").From("users").CountDistinct("name")
exists, _ := db.Select("id").From("users").Where(dbx.HashExp{"email": "a@example.com"}).Exists()

Column and map extraction

var names []string
_ = db.Select("name").From("users").Pluck(&names)

statusByName := map[string]string{}
_ = db.Select("name", "status").From("users").Map(&statusByName)

DML Guide

Upsert

_ = db.Upsert("users", dbx.Params{
	"email": "a@example.com",
	"name":  "alice-updated",
}, "email").Execute()

OnConflict DSL

_ = db.UpsertOnConflict("users", dbx.Params{
	"email": "a@example.com",
	"name":  "alice",
}, dbx.Conflict("email").DoUpdateColumns("name")).Execute()

Returning

var id int64
var name string
_ = db.UpdateReturning("users", dbx.Params{"name": "alice-v2"}, dbx.HashExp{"id": 1}, "id", "name").
	Row(&id, &name)

Bulk insert

_ = db.InsertMany("users", []dbx.Params{
	{"email": "a@example.com", "name": "alice"},
	{"email": "b@example.com", "name": "bob"},
}).Execute()

Bulk insert with returning:

rows, _ := db.InsertManyReturning("users", []dbx.Params{
	{"email": "a@example.com", "name": "alice"},
	{"email": "b@example.com", "name": "bob"},
}, "id", "name").Rows()
defer rows.Close()

Transactions and Savepoints

_ = db.Transactional(func(tx *dbx.Tx) error {
	if err := tx.Savepoint("sp1"); err != nil {
		return err
	}
	if _, err := tx.Insert("users", dbx.Params{"name": "temp"}).Execute(); err != nil {
		return err
	}
	if err := tx.RollbackTo("sp1"); err != nil {
		return err
	}
	return tx.Release("sp1")
})

Generic Helpers

users, _ := dbx.SelectAll[User](db.Select("id", "name").From("users"))
user, _ := dbx.SelectOne[User](db.Select("id", "name").From("users").Where(dbx.HashExp{"id": 1}))

Observability and Performance Features

Query/Exec hooks

• QueryLogFunc
• ExecLogFunc

Trace hook

db.TraceHook = func(ctx context.Context, sql string, exec bool) (context.Context, func(error)) {
	start := time.Now()
	return ctx, func(err error) {
		_ = time.Since(start)
		_ = err
		_ = sql
		_ = exec
	}
}

Statement cache

db = db.EnableStatementCache(128)

Dialect Notes

• Supported dialects:
  • SQLite (sqlite, sqlite3)
  • PostgreSQL (postgres, pgx)
• Placeholders:
  • SQLite: ?
  • PostgreSQL: $1, $2, ...
• Quoting:
  • SQLite: backticks
  • PostgreSQL: double quotes
• FULL JOIN:
  • available in builder API
  • verify target DB support in your deployment
• RETURNING:
  • supported by this package where DB supports it

Testing

Run all tests:

go test ./...

Run benchmarks:

go test -run '^$' -bench . -benchmem

PostgreSQL integration tests require DBX_PG_DSN.

Local PostgreSQL via Docker:

docker compose up -d postgres
export DBX_PG_DSN='postgres://dbx:dbx@localhost:55432/dbx_test?sslmode=disable'
go test ./... -run PostgresIntegration

License

MIT. See LICENSE.