BreezeORM

require github.com/nelthaarion/breezeorm v0.1.0

import (
    "database/sql"
    "context"
    _ "github.com/lib/pq"        // your driver
    "github.com/nelthaarion/breezeorm/pkg/orm"
    "github.com/nelthaarion/breezeorm/pkg/dialect"
    "github.com/nelthaarion/breezeorm/pkg/query"
)

// 1 · Model definition
type User struct {
    ID        int64     `db:"id,pk,autoincrement"`
    Email     string    `db:"email,unique" validate:"required,email"`
    Name      string    `db:"name"         validate:"required,min=2,max=100"`
    Active    bool      `db:"active,default=true"`
    CreatedAt time.Time `db:"created_at"`
}

func main() {
    // 2 · Open
    sqlDB, _ := sql.Open("postgres", "postgres://user:pass@localhost/mydb")
    db := orm.Open(sqlDB, dialect.Postgres{})
    defer db.Close()

    ctx := context.Background()

    // 3 · Query
    users, err := orm.Model[User](db).
        Where(query.Predicate{Column: "active", Op: query.OpEq, Value: true}).
        OrderBy(query.OrderTerm{Column: "created_at", Desc: true}).
        Limit(20).
        Find(ctx)

    // 4 · Insert
    u := &User{Email: "alice@example.com", Name: "Alice", Active: true}
    err = orm.Model[User](db).Create(ctx, u)
}

// base is a reusable query — its internal builder is a value type,
// so branches never share mutable state.
base := orm.Model[User](db).
    Where(query.Predicate{Column: "active", Op: query.OpEq, Value: true})

admins := base.Where(query.Predicate{Column: "role", Op: query.OpEq, Value: "admin"})
recent := base.OrderBy(query.OrderTerm{Column: "created_at", Desc: true}).Limit(10)
// base is untouched; admins and recent are independent queries

db := orm.Open(
    sqlDB,
    dialect.Postgres{},
    orm.WithPlugins(plugins.NewSoftDelete("deleted_at")),
    orm.WithCompiledQueryCacheSize(4000),
    orm.WithScanPlanCacheSize(4000),
    orm.WithExecutorOptions(
        execution.WithStmtCacheSize(512),
        execution.WithDefaultTimeout(5*time.Second),
    ),
)
defer db.Close()

// Comparison operators
query.Predicate{Column: "age",     Op: query.OpGte,     Value: 18}
query.Predicate{Column: "name",    Op: query.OpLike,    Value: "%alice%"}
query.Predicate{Column: "email",   Op: query.OpILike,   Value: "%@example.com"}
query.Predicate{Column: "role",    Op: query.OpIn,      Value: []any{"admin", "mod"}}
query.Predicate{Column: "deleted", Op: query.OpIsNull}
query.Predicate{Column: "score",   Op: query.OpBetween, Value: [2]any{50, 100}}

// Logical combinators
query.And(pred1, pred2, pred3)
query.Or(pred1, pred2)
query.Not(pred1)

// Raw SQL escape hatch (named params)
query.Raw("score > :min AND category = :cat", "min", 80, "cat", "premium")

// All active users, newest first
users, err := orm.Model[User](db).
    Where(query.Predicate{Column: "active", Op: query.OpEq, Value: true}).
    OrderBy(query.OrderTerm{Column: "created_at", Desc: true}).
    Find(ctx)

// Single row by ID
user, err := orm.Model[User](db).
    Where(query.Predicate{Column: "id", Op: query.OpEq, Value: 42}).
    First(ctx)

// Count admins
n, err := orm.Model[User](db).
    Where(query.Predicate{Column: "role", Op: query.OpEq, Value: "admin"}).
    Count(ctx)

// Select specific columns only
users, err := orm.Model[User](db).
    Select(query.SelectExpr{Expr: "id"}, query.SelectExpr{Expr: "email"}).
    Find(ctx)

// DISTINCT rows
users, err := orm.Model[User](db).Distinct().Find(ctx)

// Pessimistic lock (SELECT … FOR UPDATE)
user, err := orm.Model[User](db).
    Where(query.Predicate{Column: "id", Op: query.OpEq, Value: 1}).
    Lock(dialect.LockForUpdate).
    First(ctx)

// Single insert
u := &User{Email: "bob@example.com", Name: "Bob"}
err := orm.Model[User](db).Create(ctx, u)

// Batch insert — one transaction, multi-row VALUES
users := []User{{Name: "Alice"}, {Name: "Bob"}, {Name: "Carol"}}
n, err := orm.Model[User](db).CreateBatch(ctx, users)

// Update — target specific rows
n, err := orm.Model[User](db).
    Where(query.Predicate{Column: "id", Op: query.OpEq, Value: 42}).
    UpdateAll(ctx,
        query.Assignment{Column: "name",   Value: "Alice Updated"},
        query.Assignment{Column: "active", Value: false},
    )

// Delete
n, err := orm.Model[User](db).
    Where(query.Predicate{Column: "active", Op: query.OpEq, Value: false}).
    Delete(ctx)

q := orm.Model[User](db)

// INNER JOIN
q = q.InnerJoin("orders", "o",
    query.Predicate{Column: "o.user_id", Op: query.OpEq, Value: "users.id"})

// LEFT JOIN
q = q.LeftJoin("profiles", "p",
    query.Predicate{Column: "p.user_id", Op: query.OpEq, Value: "users.id"})

// RIGHT / FULL / CROSS
q = q.RightJoin("departments", "d", on)
q = q.FullJoin("audit_log",   "a", on)
q = q.CrossJoin("tags",         "t")

// Simple: load User.Posts
users, err := orm.Model[User](db).
    Preload("Posts").
    Find(ctx)

// Nested: User.Posts.Comments
users, err := orm.Model[User](db).
    Preload("Posts.Comments").
    Find(ctx)

// Conditional: only published posts
users, err := orm.Model[User](db).
    Preload("Posts",
        query.WithPreloadWhere(
            query.Predicate{Column: "published", Op: query.OpEq, Value: true},
        ),
        query.WithPreloadLimit(10),
    ).
    Find(ctx)

// Batch mode: single WHERE IN instead of per-row query
users, err := orm.Model[User](db).
    Preload("Posts", query.WithBatchPreload()).
    Find(ctx)

// Page 3, 20 items per page — equivalent to LIMIT 20 OFFSET 40
users, err := orm.Model[User](db).
    OrderBy(query.OrderTerm{Column: "id"}).
    Page(3, 20).
    Find(ctx)

// Or manually
users, err := orm.Model[User](db).
    Limit(20).Offset(40).Find(ctx)

// After(cursor) translates to a keyset WHERE clause
// based on the current OrderBy terms — stable under inserts.
users, err := orm.Model[User](db).
    OrderBy(query.OrderTerm{Column: "created_at", Desc: true}).
    Limit(20).
    After(lastSeenCursor).
    Find(ctx)

err := transaction.Run(ctx, db.SQLDB(), nil, transaction.DefaultRetryPolicy(),
    func(txCtx context.Context) error {
        // Any ORM call here reuses the transaction from txCtx.
        if err := orm.Model[Order](db).Create(txCtx, order); err != nil {
            return err // triggers automatic rollback
        }
        _, err := orm.Model[Inventory](db).
            Where(query.Predicate{Column: "sku", Op: query.OpEq, Value: order.SKU}).
            UpdateAll(txCtx, query.Assignment{Column: "stock", Value: newStock})
        return err
    })

// Nested transaction — becomes a SAVEPOINT automatically
err = transaction.Run(txCtx, db.SQLDB(), nil, transaction.DefaultRetryPolicy(),
    func(innerCtx context.Context) error {
        // Runs as SAVEPOINT sp_1 inside the outer transaction
        return orm.Model[AuditLog](db).Create(innerCtx, log)
    })

type Product struct {
    ID          int64      `db:"id,pk,autoincrement"`
    SKU         string     `db:"sku,unique"            validate:"required"`
    Name        string     `db:"name"                  validate:"required,min=2,max=200"`
    Price       float64    `db:"price"                 validate:"min=0"`
    Category    string     `db:"category"              validate:"required"`
    InStock     bool       `db:"in_stock,default=true"`
    Description string     `db:"description"           validate:"max=2000"`
    CreatedAt   time.Time  `db:"created_at,generated"`
    UpdatedAt   time.Time  `db:"updated_at,generated"`
    DeletedAt   *time.Time `db:"deleted_at"`  // soft-delete column
}

type User struct {
    Email    string `validate:"required,email"`
    Name     string `validate:"required,min=2,max=100"`
    Age      int    `validate:"min=0,max=150"`
    Website  string `validate:"url"`
    UUID     string `validate:"uuid"`
    Slug     string `validate:"regex=^[a-z0-9-]+$"`
    Category string `validate:"custom=allowed_category"`
}

// Register a custom validator
validation.RegisterCustom("allowed_category", func(v any) error {
    allowed := map[string]bool{"tech": true, "finance": true}
    if !allowed[fmt.Sprint(v)] {
        return fmt.Errorf("invalid category")
    }
    return nil
})

// Validate returns *ValidationError with a map of field→error
if err := validation.Validate(&user); err != nil {
    var ve *validation.ValidationError
    if errors.As(err, &ve) {
        for field, e := range ve.Fields {
            fmt.Printf("%s: %v\n", field, e)
        }
    }
}

// Implement any combination of these on your model:
type BeforeCreate interface { BeforeCreate(ctx context.Context) error }
type AfterCreate  interface { AfterCreate(ctx context.Context) error }
type BeforeUpdate interface { BeforeUpdate(ctx context.Context) error }
type AfterUpdate  interface { AfterUpdate(ctx context.Context) error }
type BeforeDelete interface { BeforeDelete(ctx context.Context) error }
type AfterDelete  interface { AfterDelete(ctx context.Context) error }
type BeforeSave   interface { BeforeSave(ctx context.Context) error }
type AfterSave    interface { AfterSave(ctx context.Context) error }

// Example: hash password before create
func (u *User) BeforeCreate(ctx context.Context) error {
    hashed, err := bcrypt.GenerateFromPassword([]byte(u.Password), 14)
    if err != nil { return err }
    u.Password = string(hashed)
    u.CreatedAt = time.Now()
    return validation.Validate(u) // wire validation here
}

type QueryLogger struct{ plugins.NoopPlugin }

func (QueryLogger) Name() string { return "query_logger" }

func (QueryLogger) AfterExecute(_ context.Context, sql string, ns int64, err error) {
    log.Printf("[sql] %s | %dµs | err=%v", sql, ns/1000, err)
}

db := orm.Open(sqlDB, dialect.Postgres{},
    orm.WithPlugins(
        plugins.NewSoftDelete("deleted_at"),
        QueryLogger{},
    ),
)

m := migrations.New(sqlDB, "schema_migrations")

m.Register(
    migrations.Migration{
        Version: "20260701000000",
        Name:    "create_users",
        Up: func(ctx context.Context, tx *sql.Tx) error {
            _, err := tx.ExecContext(ctx, `
                CREATE TABLE users (
                    id         BIGSERIAL PRIMARY KEY,
                    email      TEXT UNIQUE NOT NULL,
                    name       TEXT NOT NULL,
                    active     BOOLEAN NOT NULL DEFAULT true,
                    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
                )`)
            return err
        },
        Down: func(ctx context.Context, tx *sql.Tx) error {
            _, err := tx.ExecContext(ctx, "DROP TABLE IF EXISTS users")
            return err
        },
        Seed: func(ctx context.Context, tx *sql.Tx) error {
            _, err := tx.ExecContext(ctx,
                "INSERT INTO users (email, name) VALUES ($1, $2)",
                "admin@example.com", "Admin",
            )
            return err
        },
    },
)

// Apply all pending migrations
err := m.Up(ctx)

// Roll back the most recent migration
err = m.Down(ctx)

orm.Open(sqlDB, dialect.Postgres{})    // $1 placeholders, RETURNING, ILIKE
orm.Open(sqlDB, dialect.MySQL{})       // ? placeholders, ON DUPLICATE KEY
orm.Open(sqlDB, dialect.SQLite{})      // ? placeholders, WAL-friendly
orm.Open(sqlDB, dialect.SQLServer{})  // @p1 placeholders, TOP N syntax

// This file is generated — do not edit by hand.
func ScanUser(rows scanner.Rows, dest *User) error {
    return rows.Scan(&dest.ID, &dest.Email, &dest.Name, &dest.Active, &dest.CreatedAt)
}

func init() {
    scanner.RegisterFastScan[User](queryCacheKey, ScanUser)
}