goquent

goquent ORM

This package provides a minimal ORM built on top of goquent-query-builder. It supports MySQL and PostgreSQL.

Usage

import (
       "context"
       "github.com/faciam-dev/goquent/orm"
       "github.com/faciam-dev/goquent/orm/conv"
       "log"
)

db, _ := orm.OpenWithDriver(orm.MySQL, "root:password@tcp(localhost:3306)/testdb?parseTime=true")
// PostgreSQL example
// db, _ := orm.OpenWithDriver(orm.Postgres, "postgres://user:pass@localhost/testdb?sslmode=disable")
ctx := context.Background()
u, _ := orm.SelectOne[User](ctx, db, "SELECT * FROM users WHERE id = ?", 1)
rows, _ := orm.SelectAll[map[string]any](ctx, db, "SELECT * FROM users")

_, _ = orm.Insert(ctx, db, User{Name: "sam", Age: 18})
// Struct Update/Upsert requires PK-tagged fields, for example: ID int64 `db:"id,pk"`
_, _ = orm.Update(ctx, db, User{ID: 1, Name: "Alice"}, orm.Columns("name"), orm.WherePK())
_, _ = orm.Update(ctx, db, map[string]any{"id": 1, "name": "Bob"}, orm.Table("users"), orm.PK("id"), orm.WherePK())
user := new(User)
err := db.Model(user).Where("id", 1).First(user)

var row map[string]any
err = db.Table("users").Where("id", 1).FirstMap(&row)

// fetch a typed value from a map
id, err := conv.Value[uint64](row, "id")
if err != nil {
    log.Fatal(err)
}

var rows []map[string]any
err = db.Table("users").Where("age", ">", 20).GetMaps(&rows)

var users []User
err = db.Model(&User{}).Where("age", ">", 20).Get(&users)

// insert a record using a struct and get its auto-increment id
newID, err := db.Table("users").InsertGetId(User{Name: "sam", Age: 18})
if err != nil {
    log.Fatal(err)
}
// zero-value fields are inserted unless tagged with `omitempty`

// specify a custom primary key column when needed
altID, err := db.Table("accounts").PrimaryKey("account_id").InsertGetId(map[string]any{"name": "jane"})
if err != nil {
    log.Fatal(err)
}

Generic API

The generic API is the small typed layer around SelectOne, SelectAll, Insert, Update, and Upsert. Use it when you already have the SQL for a read, or when a write is a straightforward single-row operation. Use db.Model(...).Where(...).Get(...) or db.Table(...).Where(...).FirstMap(...) when you want query-builder composition instead.

type UserRow struct {
    ID   int64  `db:"id,pk"`
    Name string `db:"name"`
    Age  int    `db:"age"`
}

user, err := orm.SelectOne[UserRow](ctx, db, "SELECT id, name, age FROM users WHERE id = ?", 1)
_, err = orm.Update(ctx, db, UserRow{ID: 1, Name: "Alice"}, orm.Columns("name"), orm.WherePK())

Map writes use the same helpers, but require explicit table and primary-key options:

_, err := orm.Update(
    ctx,
    db,
    map[string]any{"id": 1, "name": "Bob"},
    orm.Table("users"),
    orm.PK("id"),
    orm.WherePK(),
)

See docs/orm/generic-crud.md for the full guide.

For advanced cases without abandoning the generic path, use orm.Scope plus SelectOneBy, SelectAllBy, UpdateBy, and DeleteBy:

func WithProfile() orm.Scope {
    return func(q *query.Query) *query.Query {
        return q.Join("profiles", "users.id", "=", "profiles.user_id")
    }
}

users, err := orm.SelectAllBy[UserRow](
    ctx,
    db,
    db.Model(&UserRow{}),
    WithProfile(),
    func(q *query.Query) *query.Query {
        return q.Select("users.id", "users.name", "users.age")
    },
)

Boolean dialect compatibility

goquent absorbs differences between MySQL's TINYINT(1) and PostgreSQL's BOOLEAN. The default BoolCompat policy accepts 0/1, t/f, and true/false when scanning into bool, sql.NullBool, or *bool fields. The policy can be changed globally or per field:

db, _ := orm.OpenWithDriverOptions(orm.MySQL, dsn, orm.WithBoolScanPolicy(orm.BoolStrict))

type row struct {
    Nullable bool         `db:"nullable,boolstrict"`
    Flag     sql.NullBool `db:"flag,boollenient"`
}

Use BoolStrict to only allow bool and 0/1 values. BoolLenient additionally accepts any non-zero number and strings like "yes", "on", or "off".

Transactions are handled via Transaction:

err := db.Transaction(func(tx orm.Tx) error {
    return tx.Table("users").Where("id", 1).First(&user)
})

Context-aware transactions are also available:

ctx := context.Background()
err := db.TransactionContext(ctx, func(tx orm.Tx) error {
    return tx.Table("users").Where("id", 1).First(&user)
})

Manual transaction control is also available:

ctx := context.Background()
tx, err := db.BeginTx(ctx, nil)
if err != nil {
    log.Fatal(err)
}
if _, err = tx.Table("users").Insert(User{Name: "sam"}); err != nil {
    tx.Rollback()
    log.Fatal(err)
}
if err = tx.Commit(); err != nil {
    log.Fatal(err)
}

Column comparisons

Values passed to Where are always treated as literals. To compare one column against another, use WhereColumn:

err := db.Table("profiles").
    WhereColumn("profiles.user_id", "users.id").
    Where("profiles.bio", "=", "go developer").
    FirstMap(&row)

Project Structure

The repository follows the Onion Architecture:

./cmd/        - Entry points
./internal/   - Application code
  ├── domain        - Business logic
  ├── usecase       - Application workflows
  ├── infrastructure - External implementations
  └── interface     - HTTP handlers or adapters

The orm directory contains the lightweight ORM used by the project.

Development

1. Start the test databases:

   make db-up
   

2. Run the integration suite:

   make test-integration
   

   This matches the GitHub Actions CI test job.
3. Stop the databases when finished:

   make db-down
   

The tests automatically create the required tables. You can override the default DSNs with TEST_MYSQL_DSN and TEST_POSTGRES_DSN if needed.

Benchmarks

Run benchmarks with go test -bench . ./tests. Results on a GitHub Codespace (Go 1.23) show ~1.5x speedup over GORM for scanning operations.

PostgreSQL Support

The driver now includes a PostgresDialect. Use orm.OpenWithDriver(orm.Postgres, dsn) with a valid PostgreSQL DSN to connect.

Custom Drivers

Register a driver and optionally its SQL dialect so the ORM can infer quoting rules:

orm.RegisterDriverWithDialect("mysql-custom", &mysql.MySQLDriver{}, driver.MySQLDialect{})
db, err := orm.OpenWithDriver("mysql-custom", dsn)

License

This project is licensed under the MIT License. See the LICENSE file for details.