database

package

v0.1.0 Latest Latest Go to latest Published: Nov 1, 2026 License: MIT Imports: 21 Imported by: 0

Details

Valid go.mod file
Redistributable license
Tagged version
Stable version
Learn more about best practices

Repository

github.com/arkoren/arkoren

Links

Open Source Insights

README ¶

Arkoren Database

arkoren.dev/core/service/database defines the SQL Driver contract and the typed Database facade. It uses Arkoren's foundation modules and core services, with no third-party dependencies.

Applications select an independently versioned driver module, then create a facade with database.New(driver) for typed Find, Select, Cursor, Exec, Paginate, CursorPaginate, Each, and transactions.

Installation

From your application's module directory, install core and the selected driver:

go get arkoren.dev/core
go get arkoren.dev/driver/sqlite

Import arkoren.dev/core/service/database, arkoren.dev/foundation/query, and your selected driver package, such as arkoren.dev/driver/sqlite/sqlitedatabase. The database guide shows construction, lifecycle wiring, and your first migrated tables.

Query builder

Exec, Select, Find, and Cursor accept any query.Statementer: a query.Query, or query.Raw(sql, args...) for SQL the builder cannot express. They compile it for the driver's dialect. Drivers report their dialect by implementing Dialect() on the Driver interface. The bundled Postgres, MySQL, and SQLite drivers report theirs.

Raw SQL always uses ? placeholders; the dialect rewrites them ($n on Postgres). Select, Find, and Cursor reject a published query that returns no rows (an INSERT, UPDATE, or DELETE without Returning) with query.ErrInvalidQuery. A RawStatement is opaque and always accepted.

The examples use db := database.New(driver) with your selected SQL driver, your User and Event row types, and an I/O context ctx. To collect active users, call Select:

users, err := db.Select[User](ctx, query.Table("users", func(statement *query.Builder) {
	statement.Select("id", "name")
	statement.Where("active", true)
}))

To fetch one row using hand-written SQL, pass its values separately:

user, err := db.Find[User](ctx, query.Raw("SELECT * FROM users WHERE id = ?", id))

To change a row, call Exec; affected reports the affected-row count:

affected, err := db.Exec(ctx, query.Table("users", func(statement *query.Builder) {
	statement.Where("id", id)
	statement.Update(map[string]any{"name": name})
}))

To traverse results without collecting them all, create a cursor:

events := db.Cursor[Event](ctx, query.Table("events"))

Select collects rows; Cursor returns a lazy sequence whose Iter method yields rows and errors as they are read. The cursor joins iteration/scan failures with close failures before yielding the final error. If you stop iteration early, it still closes rows, but cleanup failure can only reach telemetry: an iterator cannot yield after you stop it. Use db.Driver().Query and explicitly handle Rows.Close when your application must return an early-stop cleanup error; that raw path bypasses facade scopes.

For hand-written SQL with many parameters, query.RawNamed binds :name placeholders from a map, such as one built by query.Values, and runs through the facade like any other statement:

_, err := db.Exec(ctx, query.RawNamed(
	"UPDATE users SET name = :name WHERE id = :id", query.Values(user)))

Pagination and batching

Paginate, CursorPaginate, and Each take a query.Query (not raw SQL text) and read its orders with Inspect. Raw SQL stays available as a source through query.FromRaw, or through db.Driver().

The following examples use your Post row type with id, title, and created_at columns. To show numbered pages, read parameters from an existing HTTP request req, then call Paginate. It counts with Query.Count and fetches the page:

params, err := request.Page(req, pagination.WithMaxPerPage(50))
page, err := db.Paginate[Post](req.Context(), query.Table("posts", func(statement *query.Builder) {
	statement.Where("published", true)
	statement.OrderByDesc("created_at")
	statement.OrderBy("id")
}), params)

For next/previous navigation without a count query, use CursorPaginate. It fetches one extra row to detect another window, and encodes the ordered column values into next_cursor and prev_cursor:

params, err := request.Cursor(req)
feed, err := db.CursorPaginate[Post](req.Context(), query.Table("posts", func(statement *query.Builder) {
	statement.OrderByDesc("created_at")
	statement.OrderByDesc("id")
}), params)

A bad or mismatched cursor returns a 400 problem. To paginate in a job without HTTP, build the parameters directly; Normalize is applied for you:

page, err := db.Paginate[Post](ctx,
	query.Table("posts", func(statement *query.Builder) {
		statement.OrderBy("id")
	}),
	pagination.PageParams{Page: 2, PerPage: 50})

For a grouped report, use FromRaw as the source. It takes ? placeholders on every dialect. Here your Row type has AuthorID and Posts integer fields tagged db:"author_id" and db:"posts", and params is a pagination.PageParams:

report, err := db.Paginate[Row](ctx,
	query.FromRaw(query.Raw(`SELECT author_id, COUNT(*) AS posts
FROM posts WHERE created_at >= ? GROUP BY author_id`, since), func(statement *query.Builder) {
		statement.As("r")
		statement.OrderByDesc("posts")
		statement.OrderBy("author_id")
	}),
	params)

To process a large export in bounded memory, use Each. It reads batches of 500 rows with keyset queries:

posts := db.Each[Post](ctx, query.Table("posts", func(statement *query.Builder) {
	statement.OrderBy("id")
}), 500)

Like Cursor, posts.Iter() yields each row with an error result. Stop on an iteration error before processing that row.

Errors: ErrNoOrder (no OrderBy), ErrCursorColumn (an ordered column has no field in T), ErrCursorValue (a key value is NULL or unusable), and ErrInvalidBatchSize (Each size below one). A malformed cursor, different ordering, or invalid signature fails with pagination.ErrCursorDecode. Unsigned tokens can be edited into another valid position.

Cursors are plain base64 JSON by default, so a client can forge a position (only the position; the query still applies). To sign them, build a codec and set Builder.CursorCodec:

codec, err := pagination.NewCursorCodec(secret, oldSecret) // validated key.Shared values; exactly 32 bytes each
if err != nil {
	return err // Do not silently fall back to unsigned cursors.
}
db := database.New(driver, func(connection *database.Builder) {
	connection.CursorCodec(codec)
})

A database with a codec rejects unsigned cursors, so enabling it invalidates cursors clients hold. Signing protects integrity, not secrecy or authorization. Cursors do not bind a user, tenant, filter, dataset, or expiry; retain authorization filters on every query. Both encoders reject tokens above the decoders' 4096-byte complete-token limit, including the signature when signing is enabled.

Caveats:

The last OrderBy column must be unique (usually id), or rows that share the other keys can repeat or vanish between pages. Uniqueness is your responsibility; the facade does not verify it.
Ordered columns must be fields of T, matched by db tag (or lowercase field name). A qualified column such as p.id matches id.
NULL keys are unsupported: use non-nullable ordered columns or explicitly filter NULL rows out of the selected dataset. Every fetched key is checked, including lookahead and short final batches. These checks cannot discover NULL partitions already excluded by a seek. SQL nullable values retain their driver.Valuer method sets for non-NULL continuation binding.
All three methods need a published SELECT query. They derive their own Limit (Paginate also Offset), so the builder must not have either: a preset Limit or Offset fails with database.ErrPaged before any query runs (Each yields it as its first item).
FromRaw queries are wrapped as subqueries. Optimizer barriers inside the raw SQL (GROUP BY, DISTINCT, LIMIT) can force the database to materialize the whole result, which slows deep pages and Count.
Each runs separate queries, not one snapshot. A consistent view requires a transaction with snapshot-preserving isolation; PostgreSQL read committed still takes a new snapshot per statement. Cancellation is checked between buffered rows, not only before the next query.
Paginate and CursorPaginate reapply fixed default limits (100 rows per page, and page 10,000 for offset pages). Higher request-parser limits do not raise those facade limits.

Build errors are returned before the driver is called.

Telemetry privacy

Database spans include bounded operation labels and known table names, not bound arguments or error messages. Query text is disabled by default. If you enable connection.QueryText(true) in a construction callback, spans include compiled SQL verbatim. SQL literals and comments are not redacted, even when bound arguments use placeholders. Enable text only for statements safe to export to your telemetry backend.

Transactions

WithTransaction puts the transaction in the context it hands to fn. Keep using the *Database you already have, and pass that context. Here insertOrder is your insert query and orders.Reserve is your application's reservation operation using the same database:

err := db.WithTransaction(ctx, func(ctx context.Context) error {
	if _, err := db.Exec(ctx, insertOrder); err != nil {
		return err // rolls back
	}

	return orders.Reserve(ctx, items) // joins this transaction
})

fn returning nil commits. An error rolls back and is returned. A panic rolls back and is re-raised.
Exec, Select, Find, Cursor, Paginate, CursorPaginate, Each, and Lock run in the transaction their context carries, when it belongs to the same driver. A call made with the outer ctx runs outside it.
Create one *Database per driver. Two *Database values on one driver share transactions; a *Database on another driver does not join.
A WithTransaction inside a transaction is a nested transaction. It rolls back only its own work, and that work is final only when the outermost transaction commits. The SQL drivers implement it with savepoints. ErrNestedTransaction means the driver cannot nest.
Using the context after the transaction ends fails with sql.ErrTxDone. A transaction is one connection: do not use its context from several goroutines at once.
Synchronous code that receives the context, such as event.Bus listeners, joins the transaction. A goroutine that outlives it gets sql.ErrTxDone.
db.AfterCommit(ctx, fn) runs fn after the outermost transaction commits, in registration order, with the context the outermost WithTransaction was called with. An error, panic, or failed commit discards the hooks, and so does a rollback of the nested transaction that registered them. Outside a transaction fn runs immediately. It is best effort: a crash after the commit loses the hooks, so use it for cache invalidation, broadcasts, and notifications, not for work that must not be lost. Every hook runs even when one fails; their errors are joined and returned by the outermost WithTransaction wrapped with database.ErrAfterCommit, which means the transaction committed: don't retry it. A panic in fn propagates out of the outermost WithTransaction and skips later hooks.
There are no transaction options (read-only, isolation).

Sessions

db.WithSession(ctx, fn) reserves one physical connection without beginning a transaction. Use its callback context for all protected SQL, locks, and explicit transactions. Loss of the connection fails subsequent SQL rather than silently reconnecting or replaying work. An error does not undo earlier autocommit writes. Nested sessions borrow the connection but expire independently; new locks outside transactions end with their acquiring callback, while transaction locks last until the outermost transaction ends. Close cursors before further SQL.

Use each callback synchronously from one goroutine. After return, session contexts fail with database.ErrSessionDone, and transaction contexts with sql.ErrTxDone. Driver() and native accessors bypass both scopes and facade telemetry. See the session and lock guide and API reference.

Documentation ¶

Overview ¶

Package database defines the SQL database contract and a typed facade that runs published SQL queries into typed rows, with pinned sessions, transactions, and telemetry.

Queries are built with arkoren.dev/foundation/query, either with the structured query.Query or with query.Raw for SQL text, and run through Database.Exec, Database.Select, Database.Find, and Database.Cursor. They compile for the dialect a driver reports through Driver.Dialect. Named parameters use query.RawNamed through the facade; Database.Driver remains available for deliberate raw driver access, which bypasses facade compilation, scope routing, and telemetry.

Database.Paginate returns an offset page with its total, Database.CursorPaginate returns a keyset window with opaque cursors, and Database.Each streams every row in batches. All three take a query.Query with an ORDER BY, for example one started from query.FromRaw for raw SQL, and fail with ErrNoOrder without one. Database.CursorPaginate and Database.Each read the ordered columns from db-tagged fields of the result type (ErrCursorColumn) and reject NULL key values (ErrCursorValue), including lookahead and short final batches. Every ordered value in the selected dataset must be non-NULL; checks cannot detect NULL rows excluded by a SQL seek. Use non-nullable keys or explicit NULL exclusion. The last ORDER BY column must be unique; this is the caller's responsibility, not something the facade verifies. All three own the query's Limit, and offset pagination also sets Offset. A query that already has either fails with ErrPaged. Database.Each rejects a batch size below one with ErrInvalidBatchSize. Offset and cursor Items fields are eager collection.Slice values for one window. Their Map, Filter, and GroupBy operations do not execute SQL or change server totals, page limits, or the original cursor boundaries. Page helpers detach outer item storage; referenced element objects remain shared.

Every Database.Exec, Database.Select, Database.Find, Database.Cursor, Database.Lock, and Database.TryLock emits a client span and a db.client.operation.duration measurement when the selected telemetry (default: telemetry.Default) is started. Spans carry the operation and table, never bound arguments or error text. Query text is disabled by default. Builder.QueryText(true) exports compiled SQL verbatim, including any literals and comments; enable it only when those statements are safe to export.

Sessions ¶

Database.WithSession retains one physical connection without beginning a transaction. Use its callback context for every protected operation, including transactions that must share session-local state or a named lock:

err := db.WithSession(ctx, func(ctx context.Context) error {
	return db.WithTransaction(ctx, func(ctx context.Context) error {
		_, err := db.Exec(ctx, updateCounter)
		return err
	})
})

Each transaction keeps its own commit. A session error does not undo prior autocommit work, and connection loss never reconnects or replays the callback. Nested sessions borrow the connection and preserve an enclosing transaction. New nontransactional locks belong to their session callback and are released when it ends; transaction acquisitions belong to the outermost transaction even when first taken in a nested session or transaction. Outer session locks survive child transactions. Locks are not reentrant, including on one session.

Session drivers, contexts, and cursors expire when their callback returns: later I/O fails with ErrSessionDone. Use the callback synchronously from one goroutine. Cleanup closes cursors and releases owned locks after cancellation or panic, before returning the connection; uncertain cleanup discards it. Arbitrary raw SQL session state is not reset. Database.Driver and native accessors bypass session routing, just as they bypass transactions.

Network raw held locks each consume a connection. Root lock waiters release contended connections between attempts, but already-pinned scopes cannot. Use Database.TryLock and end a contended session when other work needs the capacity. Pool acquisition and network I/O still respect the caller context; no guarantee prevents raw holders from exhausting all pool connections.

Transactions ¶

Database.WithTransaction passes fn a context that carries the transaction. Every operation given that context runs inside it, on any Database over the same driver, so code that receives only the context joins the transaction without knowing about it. A call with a different driver's Database, or with any other context, does not join. Use one Database per driver: Databases join by comparing their driver values, and a driver that cannot be compared joins only through the same *Database.

err := db.WithTransaction(ctx, func(ctx context.Context) error {
	if _, err := db.Exec(ctx, insertOrder); err != nil {
		return err
	}

	return orders.Reserve(ctx, items) // joins; a WithTransaction inside is nested
})

A Database.WithTransaction inside a transaction is a nested transaction, which rolls back only its own work; a driver that cannot nest fails with ErrNestedTransaction. Database.Driver and Database.DriverAs always return the root driver, never a transaction driver.

Database.AfterCommit registers a best-effort callback that runs once the outermost transaction commits, in registration order, and is discarded on rollback. Hooks of a nested transaction wait for the outermost commit and are discarded if the nested transaction rolls back. Without a transaction the callback runs immediately. Hooks are lost if the process dies after the commit, so use them for cache invalidation, broadcasts, and notifications, not for work that must not be lost. Every hook runs; their errors come back from the outermost WithTransaction wrapped with ErrAfterCommit, which means the transaction committed and must not be retried. Drivers use the same marker for cleanup failures after a confirmed commit; committed events and hooks still run. An error returned by the body still requests rollback, even if it bears a marker from another transaction.

The context is valid only while fn runs: using it afterwards fails with database/sql.ErrTxDone. Do not use it from several goroutines at once, because a transaction runs on one connection. Code that runs synchronously with the context, such as listeners that an event bus invokes before returning, joins the transaction too; hand work that must outlive it a fresh context.

Events ¶

With Builder.Events, the outermost Database.WithTransaction publishes TransactionCommitted ("database.transaction.committed") after the commit and before the after-commit hooks, or TransactionRolledBack ("database.transaction.rolled-back") with the error. Listeners receive the context WithTransaction was called with, outside the transaction. Any enclosing session remains active. Cleanup failure after a confirmed commit publishes the committed event, not a rollback. Nested transactions publish nothing.

db := database.New(driver, func(connection *database.Builder) {
	connection.Events(app.Events())
})

New configures the facade with Builder policies, not a Config input. Policies run directly once in order on a fresh draft before system resolution. Nil entries panic; expanded nil/empty slices supply no edits. QueryText starts disabled and requires an explicit opt-in. Publication detaches settings, not the driver, codec, telemetry or events. Construction performs no lifecycle, transaction, session or query I/O.

Index ¶

Variables
type Builder
type BuilderFunc
type Database
- func New(driver Driver, configure ...BuilderFunc) *Database
type Driver
type JSON
- func (jsonValue *JSON[T]) Scan(src any) error
- func (jsonValue JSON[T]) Value() (driver.Value, error)
type Rows
type TransactionEvent

Constants ¶

This section is empty.

Variables ¶

View Source

var (
	// ErrNoRows reports that [Driver.Find] matched no row. Drivers also keep
	// the native error, so errors.Is matches [sql.ErrNoRows] as well.
	ErrNoRows = errors.New("no database rows were found")

	// ErrEmptyLockName reports that [Driver.Lock] received an empty name.
	ErrEmptyLockName = errors.New("lock name cannot be empty")

	// ErrNestedTransaction reports that a driver cannot nest transactions:
	// [Driver.WithTransaction] was called on a transaction driver that has no
	// way to start a transaction inside its own. The driver wraps it with the
	// reason, so match it with errors.Is.
	ErrNestedTransaction = errors.New("nested transactions are not supported")

	// ErrAfterCommit marks failed hooks or driver cleanup after a confirmed
	// commit. The data committed: do not retry that transaction. The original
	// errors remain discoverable with errors.Is. An error returned by a
	// transaction body still requests rollback, even if it wraps this marker
	// from work on another database.
	//
	// Example:
	//
	// 	if errors.Is(err, database.ErrAfterCommit) {
	// 		logger.WarnContext(ctx, "post-commit work failed", "error", err)
	// 	}
	ErrAfterCommit = errors.New("after-commit work failed")

	// ErrSessionDone reports use of a session callback's driver or context
	// after that callback returned. No query is sent and no fresh connection
	// is acquired. Transactions retain their sql.ErrTxDone outcome instead.
	//
	// Example:
	//
	// 	if errors.Is(err, database.ErrSessionDone) {
	// 		logger.WarnContext(ctx, "work outlived its database session")
	// 	}
	ErrSessionDone = errors.New("database session has ended")
)

View Source

var (
	// TransactionCommitted is published after a confirmed outermost commit,
	// before hooks. Post-commit driver cleanup errors do not change this event
	// to a rollback; the caller still receives ErrAfterCommit.
	TransactionCommitted = event.NewTopic[TransactionEvent]("database.transaction.committed")

	// TransactionRolledBack is published when an outermost transaction
	// fails before confirmed commit: its body failed, its context ended, or
	// the commit failed. A callback-origin ErrAfterCommit still counts as a
	// body failure rather than proof this transaction committed.
	TransactionRolledBack = event.NewTopic[TransactionEvent]("database.transaction.rolled-back")
)

View Source

var (
	// ErrNoOrder reports that a paginated or batched query has no ORDER BY.
	// Stable pages need a total order, so add OrderBy with a unique
	// tiebreaker column last.
	ErrNoOrder = errors.New("paginated query requires an order")

	// ErrCursorColumn reports that an ordered column has no matching field
	// in the result type, so its value cannot be read for a cursor.
	ErrCursorColumn = errors.New("order column has no field in result type")

	// ErrCursorValue reports that a key value of a cursor row cannot be
	// used in a keyset comparison, for example because it is NULL.
	ErrCursorValue = errors.New("invalid cursor key value")

	// ErrPaged reports that the query already has a Limit or Offset.
	// Pagination sets both itself, so a preset value would be overwritten
	// or skip rows. Omit them when declaring the query.
	ErrPaged = errors.New("builder already has limit or offset")

	// ErrInvalidBatchSize reports a batch size below one in [Database.Each].
	ErrInvalidBatchSize = errors.New("batch size must be positive")
)

Functions ¶

This section is empty.

Types ¶

type Builder ¶

type Builder struct {
	// contains filtered or unexported fields
}

Builder configures New before publication. Callbacks run directly once, synchronously in order on one fresh draft. Nil callback entries panic; expanded nil or empty callback slices supply no edits. Retaining the draft cannot change published settings. Methods return the same draft.

Example:

database.New(driver, func(connection *database.Builder) {
	connection.Namespace("billing")
})

func (*Builder) CursorCodec ¶

func (connection *Builder) CursorCodec(codec *pagination.CursorCodec) *Builder

CursorCodec signs the cursors of Database.CursorPaginate with codec and rejects cursors it did not sign with pagination.ErrCursorDecode. Switching an existing deployment to a codec invalidates cursors clients already hold.