Documentation
¶
Overview ¶
Package db opens the panel database (SQLite by default, or PostgreSQL), applies embedded goose migrations and provides transaction helpers shared by the repositories in internal/store.
Repository SQL is written for SQLite. On PostgreSQL every query issued through DB or a Querier from DB.Wrap is adapted on the fly: placeholders are rebound, LIKE becomes ILIKE and Go booleans are bound as 0/1 (see postgresQuery). The PostgreSQL schema (migrations_pg) mirrors the SQLite one: BIGINT for INTEGER, BYTEA for BLOB, CITEXT for NOCASE columns and an ICU collation named "nocase" so COLLATE NOCASE works as is.
Index ¶
- Variables
- func DSN(path string, busy time.Duration) string
- func IsCheckViolation(err error) bool
- func IsForeignKeyViolation(err error) bool
- func IsTx(q Querier) bool
- func IsUniqueViolation(err error) bool
- func Migrations() fs.FS
- func MigrationsPG() fs.FS
- func Rebind(query string) (string, error)
- func Savepoint(ctx context.Context, q Querier, fn func() error) error
- func SyncIDSequence(ctx context.Context, q Querier, table string) error
- type DB
- func (d *DB) Dialect() Dialect
- func (d *DB) Exec(query string, args ...any) (sql.Result, error)
- func (d *DB) ExecContext(ctx context.Context, query string, args ...any) (sql.Result, error)
- func (d *DB) LatestSchemaVersion() (int64, error)
- func (d *DB) Migrate(ctx context.Context) ([]MigrationResult, error)
- func (d *DB) Path() string
- func (d *DB) PrepareContext(ctx context.Context, query string) (*sql.Stmt, error)
- func (d *DB) Query(query string, args ...any) (*sql.Rows, error)
- func (d *DB) QueryContext(ctx context.Context, query string, args ...any) (*sql.Rows, error)
- func (d *DB) QueryRow(query string, args ...any) *sql.Row
- func (d *DB) QueryRowContext(ctx context.Context, query string, args ...any) *sql.Row
- func (d *DB) SchemaVersion(ctx context.Context) (int64, error)
- func (d *DB) WithTx(ctx context.Context, fn func(tx *sql.Tx) error) (err error)
- func (d *DB) Wrap(q Querier) Querier
- type Dialect
- type MigrationResult
- type Options
- type PostgresOptions
- type Querier
Constants ¶
This section is empty.
Variables ¶
var ErrMixedPlaceholders = errors.New("db: query mixes ? and ?N placeholders")
ErrMixedPlaceholders is returned for a query that mixes plain ('?') and numbered ('?N') placeholders. SQLite numbers a plain '?' after the largest index seen so far, which has no faithful PostgreSQL equivalent.
Functions ¶
func DSN ¶
DSN builds the modernc.org/sqlite connection string for path with the pragmas every connection must carry.
func IsCheckViolation ¶
IsCheckViolation reports whether err is a CHECK constraint failure.
func IsForeignKeyViolation ¶
IsForeignKeyViolation reports whether err is a FOREIGN KEY constraint failure.
func IsTx ¶ added in v0.11.0
IsTx reports whether q is a transaction, also when Wrap adapted it for the dialect (a PostgreSQL transaction is a dialectQuerier around the *sql.Tx), so callers can join it instead of opening a second one.
func IsUniqueViolation ¶
IsUniqueViolation reports whether err is a UNIQUE or PRIMARY KEY constraint failure.
func Migrations ¶
Migrations returns the embedded goose migration files for SQLite.
func MigrationsPG ¶ added in v0.9.0
MigrationsPG returns the embedded goose migration files for PostgreSQL.
func Rebind ¶ added in v0.9.0
Rebind converts SQLite parameter placeholders in query to PostgreSQL ones: plain '?' become sequential '$1', '$2', ... and numbered '?N' become '$N' (so a value bound once can be referenced several times). A query that mixes both styles is rejected with ErrMixedPlaceholders.
Single-quoted literals ('...'), double-quoted identifiers ("..."), line comments (-- ...) and block comments (/* ... */) are copied verbatim.
func Savepoint ¶ added in v0.11.0
Savepoint runs fn so that a failing statement in it (typically a unique violation the caller recovers from) leaves the enclosing transaction usable: PostgreSQL aborts the whole transaction on any error, SQLite does not. On PostgreSQL inside a transaction fn runs under a SAVEPOINT that is rolled back when fn fails; otherwise fn is simply called.
func SyncIDSequence ¶ added in v0.11.0
SyncIDSequence moves the id sequence of table past its largest id after rows were inserted with explicit ids (legacy ids preserved on import). Only PostgreSQL needs it: SQLite's AUTOINCREMENT tracks explicit ids itself.
Types ¶
type DB ¶
DB wraps *sql.DB for the panel database.
func Open ¶
Open opens (creating if needed) the SQLite database at path and verifies the connection. It does not run migrations; call Migrate.
func OpenPostgres ¶ added in v0.9.0
OpenPostgres opens a connection pool to a PostgreSQL database at dsn and verifies it.
func (*DB) Dialect ¶ added in v0.9.0
Dialect returns the database engine dialect (sqlite or postgres).
func (*DB) ExecContext ¶ added in v0.9.0
ExecContext executes a query, adapting it to PostgreSQL when needed.
func (*DB) LatestSchemaVersion ¶
LatestSchemaVersion returns the newest embedded migration version.
func (*DB) Migrate ¶
func (d *DB) Migrate(ctx context.Context) ([]MigrationResult, error)
Migrate applies all pending migrations and returns those applied.
func (*DB) PrepareContext ¶ added in v0.9.0
PrepareContext prepares a statement, adapting the query to PostgreSQL when needed. Arguments of the returned statement are passed to the driver as-is (no bool conversion); prefer ExecContext/QueryContext.
func (*DB) QueryContext ¶ added in v0.9.0
QueryContext executes a query, adapting it to PostgreSQL when needed.
func (*DB) QueryRowContext ¶ added in v0.9.0
QueryRowContext executes a query, adapting it to PostgreSQL when needed.
func (*DB) SchemaVersion ¶
SchemaVersion returns the current goose schema version (0 when unmigrated).
func (*DB) WithTx ¶
WithTx runs fn inside a transaction. It commits when fn returns nil and rolls back otherwise (also on panic, which is re-raised). Pass tx through Wrap before running repository queries on it.
Transactions run one at a time, on both engines: SQLite takes its write lock at BEGIN IMMEDIATE, and on PostgreSQL every transaction first takes the same transaction-scoped advisory lock. Repositories rely on this for read-check-write sequences (e.g. "name not taken, then insert") that READ COMMITTED alone would let interleave.
type Dialect ¶ added in v0.9.0
type Dialect string
Dialect identifies the underlying SQL database engine.
func DialectOf ¶ added in v0.11.0
DialectOf reports the dialect a Querier obtained from DB or DB.Wrap runs on. Raw *sql.DB and *sql.Tx values report SQLite, which is what Wrap returns them as.
func (Dialect) InJSONArray ¶ added in v0.11.0
InJSONArray returns the condition "expr IN (...)" over the elements of a JSON array bound to a single parameter (json_each on SQLite, jsonb_array_elements_text on PostgreSQL), so long lists such as the online device ids need one bind instead of one per element. On PostgreSQL the elements are cast to pgType (e.g. "bigint"; "" keeps text).
type MigrationResult ¶
MigrationResult describes one applied migration.
type Options ¶
type Options struct {
// MaxOpenConns caps the pool (default 8). WAL allows concurrent readers;
// writers are serialized by SQLite (BEGIN IMMEDIATE + busy_timeout).
MaxOpenConns int
// BusyTimeout is how long a writer waits for the lock (default 5s).
BusyTimeout time.Duration
}
Options tune Open. The zero value is suitable for production.
type PostgresOptions ¶ added in v0.9.0
type PostgresOptions struct {
MaxOpenConns int
MaxIdleConns int
ConnMaxLifetime time.Duration
ConnMaxIdleTime time.Duration
}
PostgresOptions configures connection pooling for PostgreSQL.
type Querier ¶
type Querier interface {
ExecContext(ctx context.Context, query string, args ...any) (sql.Result, error)
QueryContext(ctx context.Context, query string, args ...any) (*sql.Rows, error)
QueryRowContext(ctx context.Context, query string, args ...any) *sql.Row
}
Querier is the subset of *sql.DB / *sql.Tx used by repositories, so the same repository code runs inside or outside a transaction.
Source Files
Directories
¶
| Path | Synopsis |
|---|---|
|
Package dbtest opens panel databases for tests: a SQLite file in the test's temporary directory by default, or a fresh PostgreSQL database when RDPANEL_TEST_PG_DSN is set, so the same suites run on both engines (CI runs the PostgreSQL pass against a postgres service container).
|
Package dbtest opens panel databases for tests: a SQLite file in the test's temporary directory by default, or a fresh PostgreSQL database when RDPANEL_TEST_PG_DSN is set, so the same suites run on both engines (CI runs the PostgreSQL pass against a postgres service container). |