jackgodb

package module
v1.3.0 Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: Sep 11, 2026 License: Unlicense Imports: 21 Imported by: 0

README

jackgodb

A generic ORM + DAO layer for Go, built on top of bun. It adds typed generic data-access objects, per-record dirty tracking, map-based dynamic query building, and a schema-to-model code generator — so your models expose CRUD without writing SQL for the common paths.

Supported backends: MySQL, SQLite, and PostgreSQL (single code path, dialect-aware).

  • Module: github.com/raxisau/jackgodb
  • Requires: Go 1.26+
  • License: The Unlicense (public domain) — see LICENSE

Installation

go get github.com/raxisau/jackgodb@latest

Core concepts

Type Purpose
DAO Wraps a *bun.DB plus the active dialect (mysql / sqlite / pgsql). Created by the Open*DBConnection helpers.
ModelDAO[T] A typed, generic DAO for one model. Knows the column alias map and how to construct new attached records.
ModelRecord[T] Embedded in your model struct. Provides dirty tracking, CRUD, reflection-based get/set, and the dynamic query builder.

Your model embeds both a bun.BaseModel (table name/alias) and ModelRecord[*T]:

package demo

import (
	"context"
	"log/slog"
	"time"

	"github.com/raxisau/jackgodb"
	"github.com/uptrace/bun"
)

type StatusesDAO struct {
	*jackgodb.ModelDAO[*Statuses]
}

type Statuses struct {
	bun.BaseModel `bun:"table:reg_statuses,alias:RSTAT"`

	jackgodb.ModelRecord[*Statuses] `bun:"-" json:"-"`

	ID                  int64     `bun:"id,pk,autoincrement" json:"id,omitempty"`
	StatusCode          string    `bun:"f_status_code,unique" json:"f_status_code,omitempty"`
	StatusDescription   *string   `bun:"f_status_description" json:"f_status_description,omitempty"`
	StatusReasonDefault *string   `bun:"f_status_reason_default" json:"f_status_reason_default,omitempty"`
	StatusURL           *string   `bun:"f_status_url" json:"f_status_url,omitempty"`
	CreatedAt           time.Time `bun:"f_created_at" json:"f_created_at,omitzero"`
	Comments            *string   `bun:"f_comments" json:"f_comments,omitempty"`
}

var aliasStatuses = map[string]string{
	"id":                  "id",
	"statusCode":          "f_status_code",
	"statusDescription":   "f_status_description",
	"statusReasonDefault": "f_status_reason_default",
	"statusURL":           "f_status_url",
	"createdAt":           "f_created_at",
	"comments":            "f_comments",
}

func NewStatusesDAO(dao *jackgodb.DAO) *StatusesDAO {
	return &StatusesDAO{
		ModelDAO: jackgodb.NewModelDAO(
			dao,
			aliasStatuses,
			func() *Statuses {
				return &Statuses{}
			}),
	}
}

func (dao *StatusesDAO) NewStatuses() *Statuses {
	record := dao.New()
	return record
}

func (m *Statuses) LoadByName(statusCode string) *Statuses {

	record := m.Self
	record.SetID(0)

	if statusCode == "" {
		return nil
	}

	err := m.Dao.Db.NewSelect().
		Model(record).
		Where("f_status_code=?", statusCode).
		Limit(1).
		Scan(context.Background())

	if err != nil {
		slog.Error("1. Statuses.LoadByName", "error", err)
		return nil
	}

	return m
}

The alias map translates the keys you pass to SetValue / GetValue / Dirty / ToWhere into real column names, so application code never has to spell out f_-prefixed column names.

A complete working reference model lives in internal/demo/statuses.go.

Quickstart

package main

import (
	"context"
	"fmt"
	"log/slog"
	"os"

	"github.com/raxisau/jackgodb"
	"github.com/raxisau/jackgodb/internal/demo"
)

func main() {
	setUpLogging()

	// Empty DSN = in-memory SQLite. See "Connections" for MySQL/PostgreSQL.
	dao := jackgodb.OpenSQLiteDBConnection("")
	defer dao.Close()

	statusDao := demo.NewStatusesDAO(dao)

	// Create the table (bun DDL; in production use migrations).
	ctx := context.Background()
	_, err := dao.Db.NewCreateTable().Model((*demo.Statuses)(nil)).IfNotExists().Exec(ctx)
	if err != nil {
		panic(err)
	}

	// Insert
	rec := statusDao.NewStatuses()
	rec.SetValue("statusCode", "ACTIVE")
	rec.SetValue("statusDescription", "rec.SetValue statusDescription")
	rec.SetValue("statusReasonDefault", "rec.SetValue statusReasonDefault")
	rec.SetValue("statusURL", "http://rec.SetValuestatusURL")
	rec.SetValue("comments", "rec.SetValue comments")
	id := rec.Save() // returns the new primary key

	// Load
	loaded := statusDao.NewStatuses()
	loaded.Load(id)
	fmt.Println(loaded.ToJSONIndent())

	// Update — use SetValue, which marks the column dirty
	loaded.SetValue("statusCode", "RETIRED")
	loaded.Save()

	loaded.Load(id)
	fmt.Println(loaded.ToJSONIndent())

	// Delete
	loaded.Delete()
	slog.Info("server stopped")
}

func setUpLogging() *slog.Logger {
	// https://pkg.go.dev/log/slog#example-SetLogLoggerLevel-Log
	opts := &slog.HandlerOptions{Level: slog.LevelDebug}

	log := slog.New(slog.NewJSONHandler(os.Stdout, opts))
	slog.SetDefault(log)
	slog.Info("Starting System")

	return log
}

Connections

dao := jackgodb.OpenMySQLDBConnection("user:pass@tcp(127.0.0.1:3306)/mydb")
dao := jackgodb.OpenSQLiteDBConnection("")                                  // in-memory (shared cache)
dao := jackgodb.OpenSQLiteDBConnection("file:data.db?cache=shared&mode=rwc")
dao := jackgodb.OpenPostgreSQLDBConnection("postgres://user:pass@127.0.0.1:5432/mydb")
  • Pool sizing uses the package constants MaxOpenConns (25), MaxIdleConns (25) and ConnMaxLifetime (MaxLifetime, 5 minutes).
  • Set jackgodb.SQLDebug = true before opening a connection to attach bun's debug query hook (bundebug, verbose) and log every statement.
  • dao.Close() closes the underlying pool. A DAO also exposes Db (the raw *bun.DB) when you need bun directly, and DbIn(start, count) which renders a placeholder list for IN (...) clauses (?,? everywhere; $3,$4,$5-style on PostgreSQL).

CRUD reference

All methods below are promoted from the embedded ModelRecord[T]. They return row counts / ids (int64) and log errors via log/slog instead of returning them — see Known limitations.

Method Behaviour
Save() int64 Insert() when the primary key is zero, otherwise Update(). Returns the new id or rows affected.
Insert() int64 Inserts only the dirty columns (all columns when none are dirty). Returns the new primary key (via LastInsertId on MySQL; via RETURNING on SQLite and PostgreSQL).
InsertIgnore() int64 INSERT IGNORE / ON CONFLICT DO NOTHING equivalent. Existing rows are left untouched.
Update() int64 Updates only dirty columns by primary key. No-ops (logs an error) when the record is clean.
Delete() int64 Deletes by primary key. Returns rows affected.
Load(id int64) T SELECT ... WHERE pk = id LIMIT 1, scanning into the record. On failure the id is reset to 0.

Dirty tracking:

Method Behaviour
Dirty(column string) Mark a column (by alias or column name) as modified.
IsClean() / IsDirty() bool Whether any column is dirty.
Clean() Reset all columns to clean (done automatically after attach and update).

Direct struct field assignment is not tracked. To update a record, change values via SetValue (which marks the column dirty) or call Dirty yourself.

Value API

ok := rec.SetValue("statusCode", "ACTIVE") // alias-resolved, type-coerced, marks dirty
val := rec.GetValue("statusCode")          // any

id := rec.GetID()   // reads the pk column as int64
rec.SetID(id)       // writes the pk column

SetValue coerces the incoming value to the field's type via ConvertTo and only marks the column dirty when the value actually changed. Supported targets: string, int, int64, float64, time.Time, and their nullable (*) forms; anything else falls back through AnyToString / AnyToInt64 helpers.

API bridging

// Struct -> map[string]any, keyed by json tags.
asMap := rec.ToAPI()

// map[string]any -> struct. If the payload contains the pk column the record is loaded
// from the database first, so it works as an upsert-style PATCH endpoint.
rec.FromAPI(map[string]any{
	"id":         3,
	"statusCode": "ACTIVE",
})

Dynamic queries with ToWhere

ToWhere(query, args) builds bun SELECT clauses from plain maps/lists — useful when the request shape comes from JSON:

var rows []*Statuses
query := statusDao.Db.NewSelect().Model(&rows)
rec.ToWhere(query, map[string]any{
	"where": map[string]any{
		"statusCode":           "ACTIVE",               // parameterized: WHERE f_status_code = ?
		"statusUrl != ?":       "https://spam.example", // keys with "?" bind values as params
		"comments IS NOT NULL": nil,                    // value-less keys run as raw SQL
	},
	"order": map[string]any{"createdAt": "DESC"},
	"limits": []any{0, 50},             // offset, limit
})
err := query.Scan(context.Background())
statusDao.AttachList(rows) // attach loaded rows so they get full record behaviour
Key Accepted shapes
limits int (limit only) · []any{offset, limit} · map[string]any{"<offset>": limit}
order "column" · map[string]any{"column": "ASC"/"DESC"} (anything but DESC = ASC) · []any of columns
where "column" (raw) · map[string]any{"column": value} or {"sql with ?": value} · []any of raw clauses

Keys are resolved through the alias map and converted to real column names; values are bound as query parameters, but string/list keys are interpolated as SQL — never pass untrusted input there (see Known limitations).

Code generation with modelgen

cmd/modelgen turns a MySQL-flavoured CREATE TABLE statement into a ready-to-use model file (bun tags, alias map, DAO constructor):

# From a file, print to stdout
go run ./cmd/modelgen -file stdout schema.sql

# Or pipe it in
cat schema.sql | go run ./cmd/modelgen -file infer   # writes <snake_case_name>.go
go run ./cmd/modelgen -file model/statuses.go schema.sql

Conventions the generator understands:

  • Table names prefixed tbl and column names prefixed fld / f_ are stripped before Pascal-casing (tblUser -> User, f_status_code -> StatusCode).
  • Types: int* -> int64, tinyint(1) -> bool, decimal/float/double -> float64, date/datetime/timestamp -> time.Time, text/varchar/json -> string; nullable columns become pointer types.
  • PRIMARY KEY and UNIQUE KEY lines are detected; auto_increment produces the autoincrement bun tag.
  • A column named f_updated_at (or fldLastUpdated) is forced to time.Time and tagged nullzero,notnull,default:current_timestamp.

Model metadata rules

ModelRecord caches metadata per concrete type (table name, primary key, column list, column -> field index) by reading your bun tags:

  • Fields need a bun tag to participate; bun:"-" fields are ignored (as embedded ModelRecord is).
  • The model must declare a table (table:... on any field's bun tag, normally the bun.BaseModel) and a column tagged pk. If either is missing, metadata is nil and value/ CRUD calls log "model metadata is nil".
  • Tag options: pk marks the primary key (kept out of dirty tracking); scanonly columns are readable but never written.
  • A column named fldLastUpdated tagged default:current_timestamp is treated as database-managed and excluded from inserts/updates.

Package API summary

// db.go
func OpenMySQLDBConnection(dsn string) *DAO
func OpenSQLiteDBConnection(dsn string) *DAO
func OpenPostgreSQLDBConnection(dsn string) *DAO
const MaxOpenConns, MaxIdleConns = 25, 25
const MaxLifetime = 5 * time.Minute
var SQLDebug bool

// dao.go
func NewDAO(db *bun.DB) *DAO
func (dao *DAO) Close()
func (dao *DAO) DbIn(start, count int) string
func NewModelDAO[T DBModel](dao *DAO, alias map[string]string, newRecord func() T) *ModelDAO[T]
func (dao *ModelDAO[T]) New() T
func (dao *ModelDAO[T]) Attach(record T)
func (dao *ModelDAO[T]) AttachList(records []T)

// orm.go — promoted onto your model via ModelRecord[T]
func (m) Save() / Insert() / InsertIgnore() / Update() / Delete() int64
func (m) Load(id int64) T
func (m) SetValue(column string, value any) bool
func (m) GetValue(column string) any
func (m) GetID() int64 / SetID(id int64)
func (m) Dirty(column string) / IsClean() / IsDirty() bool / Clean()
func (m) ToAPI() map[string]any
func (m) ToJSON() string
func (m) ToJSONIndent() string
func (m) FromAPI(data map[string]any) T
func (m) ToWhere(query *bun.SelectQuery, args map[string]any) *bun.SelectQuery

// converter.go
func ConvertTo(value any, targetType reflect.Type) (any, error)
func ParseUTCDateTime(value string) (time.Time, error)
func AnyToTime(value any) time.Time
func AnyToInt(value any) int
func AnyToInt64(value any) int64
func AnyToString(value any) string
func StringValue(*string) string

Known limitations

  • Errors are logged, not returned. CRUD methods write failures to log/slog and return 0 / empty records; callers cannot distinguish "not found" from a connection failure. Treat this as the library's biggest rough edge.
  • Raw SQL surface in ToWhere. String/list where and order keys are interpolated as SQL. Parameterize everything user-controlled or validate against the alias map first.
  • No context parameter. Queries run on context.Background(); there is no cancellation or timeout plumbing yet.
  • Update() no-ops on clean records (logging an error), so field writes must go through SetValue/Dirty.
  • modelgen targets MySQL-style DDL (backtick-quoted identifiers, auto_increment, inline PRIMARY KEY/UNIQUE KEY lines).

Project layout

cmd/jackgodb/    Demo binary (in-memory SQLite; creates the demo table)
cmd/modelgen/    CREATE TABLE -> Go model code generator
internal/demo/   Reference model (Statuses) showing the expected shape
db.go            Connection openers for MySQL / SQLite / PostgreSQL
dao.go           DAO and generic ModelDAO[T]
orm.go           ModelRecord[T]: metadata, dirty tracking, CRUD, ToWhere
converter.go     Type coercion and date parsing helpers

Development

make jgdb   # Run the demo
make test   # Run the tests
make cover  # check the coverage from tests

Documentation

Index

Constants

View Source 
const (
	DialectMySQL      = "mysql"
	DialectSQLite     = "sqlite"
	DialectPostgreSQL = "pgsql"
)
View Source 
const (
	MaxOpenConns = 25
	MaxIdleConns = 25
	MaxLifetime  = 5 * time.Minute
)

Variables

View Source 
var (
	StringType = reflect.TypeFor[string]()
	TimeType   = reflect.TypeFor[time.Time]()
)
View Source 
var (
	SQLDebug = false
)

Functions

func AnyToInt 

func AnyToInt(v any) int

func AnyToInt64 

func AnyToInt64(v any) int64

func AnyToString 

func AnyToString(value any) string

func AnyToTime 

func AnyToTime(value any) time.Time

func ConvertTo 

func ConvertTo(value any, targetType reflect.Type) (any, error)

func ParseUTCDateTime 

func ParseUTCDateTime(value string) (time.Time, error)

func StringValue 

func StringValue(value *string) string

Types

type DAO 

type DAO struct {
	Db *bun.DB
	// contains filtered or unexported fields
}

func NewDAO 

func NewDAO(db *bun.DB) *DAO

func OpenMySQLDBConnection 

func OpenMySQLDBConnection(dsn string) *DAO

func OpenPostgreSQLDBConnection added in v1.2.0 

func OpenPostgreSQLDBConnection(dsn string) *DAO

func OpenSQLiteDBConnection 

func OpenSQLiteDBConnection(dsn string) *DAO

func (*DAO) Close 

func (dao *DAO) Close()

func (*DAO) DbIn 

func (dao *DAO) DbIn(start, count int) string

type DBModel 

type DBModel interface {
	GetID() int64
	SetID(int64)
}

type ModelDAO 

type ModelDAO[T DBModel] struct {
	Db *bun.DB
	// contains filtered or unexported fields
}

func NewModelDAO 

func NewModelDAO[T DBModel](dao *DAO, alias map[string]string, newRecord func() T) *ModelDAO[T]

func (*ModelDAO[T]) Attach 

func (dao *ModelDAO[T]) Attach(record T)

func (*ModelDAO[T]) AttachList 

func (dao *ModelDAO[T]) AttachList(recordList []T)

func (*ModelDAO[T]) New 

func (dao *ModelDAO[T]) New() T

type ModelDAOAttacher 

type ModelDAOAttacher[T DBModel] interface {
	AttachModelDAO(*ModelDAO[T], T)
}

type ModelMetadata 

type ModelMetadata struct {
	TableName     string         `json:"TableName,omitempty"`
	PKField       string         `json:"PKField,omitempty"`
	PKColumn      string         `json:"PKColumn,omitempty"`
	Columns       []string       `json:"Columns,omitempty"`
	FieldByColumn map[string]int `json:"FieldByColumn,omitempty"`
}

type ModelRecord 

type ModelRecord[T DBModel] struct {
	Dao *ModelDAO[T]

	Self T
	// contains filtered or unexported fields
}

func (*ModelRecord[T]) AttachModelDAO 

func (m *ModelRecord[T]) AttachModelDAO(dao *ModelDAO[T], self T)

func (*ModelRecord[T]) Clean 

func (m *ModelRecord[T]) Clean()

func (*ModelRecord[T]) Delete 

func (m *ModelRecord[T]) Delete() int64

func (*ModelRecord[T]) Dirty 

func (m *ModelRecord[T]) Dirty(column string)

func (*ModelRecord[T]) FromAPI 

func (m *ModelRecord[T]) FromAPI(apiData map[string]any) T

func (*ModelRecord[T]) GetID 

func (m *ModelRecord[T]) GetID() int64

func (*ModelRecord[T]) GetModelMetadata 

func (m *ModelRecord[T]) GetModelMetadata() *ModelMetadata

func (*ModelRecord[T]) GetValue 

func (m *ModelRecord[T]) GetValue(columnName string) any

func (*ModelRecord[T]) Insert 

func (m *ModelRecord[T]) Insert() int64

func (*ModelRecord[T]) InsertIgnore 

func (m *ModelRecord[T]) InsertIgnore() int64

func (*ModelRecord[T]) IsClean 

func (m *ModelRecord[T]) IsClean() bool

func (*ModelRecord[T]) IsDirty 

func (m *ModelRecord[T]) IsDirty() bool

func (*ModelRecord[T]) Load 

func (m *ModelRecord[T]) Load(id int64) T

func (*ModelRecord[T]) Save 

func (m *ModelRecord[T]) Save() int64

func (*ModelRecord[T]) SetID 

func (m *ModelRecord[T]) SetID(id int64)

func (*ModelRecord[T]) SetValue 

func (m *ModelRecord[T]) SetValue(columnName string, value any) bool

func (*ModelRecord[T]) ToAPI 

func (m *ModelRecord[T]) ToAPI() map[string]any

func (*ModelRecord[T]) ToJSON added in v1.3.0 

func (m *ModelRecord[T]) ToJSON() string

func (*ModelRecord[T]) ToJSONIndent added in v1.3.0 

func (m *ModelRecord[T]) ToJSONIndent() string

func (*ModelRecord[T]) ToWhere 

func (m *ModelRecord[T]) ToWhere(query *bun.SelectQuery, args map[string]any) *bun.SelectQuery

func (*ModelRecord[T]) Update 

func (m *ModelRecord[T]) Update() int64

Source Files

View all Source files

Directories

Path Synopsis
cmd
jackgodb command
modelgen command
internal
demo

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.