Skip to content

v0.3.0-beta - Subqueries, CTEs, Set Operations & WrapDB()

Pre-release
Pre-release

Choose a tag to compare

View all tags
@kolkov kolkov released this 25 Oct 18:55
· 146 commits to main since this release
v0.3.0-beta
29e84c6

v0.3.0-beta - Advanced SQL Features

Transform Relica from production-ready query builder to advanced SQL powerhouse

Relica v0.3.0-beta adds powerful SQL features that bring enterprise-grade query capabilities to your Go applications while maintaining zero production dependencies and 89.9% test coverage.

🎯 What's New

🔍 Subqueries

Build complex queries with nested SELECT statements:

// EXISTS for efficient existence checks (5x faster than IN)
orderCheck := db.Builder().Select("1").From("orders").Where("orders.user_id = users.id")
db.Builder().Select("*").From("users").Where(relica.Exists(orderCheck)).All(&users)

// IN subqueries for filtering
sub := db.Builder().Select("user_id").From("orders").Where("total > ?", 100)
db.Builder().Select("*").From("users").Where(relica.In("id", sub)).All(&users)

// FROM subqueries for complex aggregations
stats := db.Builder().Select("user_id", "COUNT(*) as cnt").From("orders").GroupBy("user_id")
db.Builder().FromSelect(stats, "top_users").Where("cnt > ?", 10).All(&results)

Performance: EXISTS is 5x faster than IN (109ns vs 516ns query building)

📚 Full Guide - IN, EXISTS, FROM, scalar subqueries with performance tips

🔀 Set Operations

Combine results from multiple queries:

// UNION ALL (2-3x faster than UNION)
active := db.Builder().Select("name").From("users").Where("status = ?", 1)
archived := db.Builder().Select("name").From("archived_users")
active.UnionAll(archived).All(&allNames)

// INTERSECT - find overlapping data
allUsers := db.Builder().Select("id").From("users")
orderUsers := db.Builder().Select("user_id").From("orders")
allUsers.Intersect(orderUsers).All(&activeUsers)

// EXCEPT - find differences
allUsers.Except(bannedUsers).All(&activeUsers)

Performance: UNION ALL is 2-3x faster than UNION (no duplicate removal overhead)

Database Support:

  • PostgreSQL 9.1+ ✓ (all operations)
  • MySQL 8.0+ ✓ (UNION, UNION ALL)
  • MySQL 8.0.31+ ✓ (all operations)
  • SQLite 3.25+ ✓ (all operations)

📚 Full Guide - UNION, INTERSECT, EXCEPT with compatibility matrix

🌳 Common Table Expressions (CTEs)

Simplify complex queries and handle hierarchical data:

// Basic CTE - reusable query expressions
orderTotals := db.Builder().
    Select("user_id", "SUM(total) as total").
    From("orders").
    GroupBy("user_id")

db.Builder().
    With("order_totals", orderTotals).
    Select("*").
    From("order_totals").
    Where("total > ?", 1000).
    All(&premiumUsers)

// Recursive CTE - organizational hierarchies
anchor := db.Builder().
    Select("id", "name", "manager_id", "1 as level").
    From("employees").
    Where("manager_id IS NULL")

recursive := db.Builder().
    Select("e.id", "e.name", "e.manager_id", "h.level + 1").
    From("employees e").
    InnerJoin("hierarchy h", "e.manager_id = h.id")

db.Builder().
    WithRecursive("hierarchy", anchor.UnionAll(recursive)).
    Select("*").
    From("hierarchy").
    OrderBy("level").
    All(&orgChart)

Use Cases: Org charts, category trees, bill of materials, graph traversal

📚 Full Guide - WITH clauses, recursive CTEs for hierarchical data

🪟 Window Functions

Advanced analytics with ranking, running totals, and more:

// Ranking within partitions
db.Builder().
    SelectExpr("user_id", "country", "total",
        "RANK() OVER (PARTITION BY country ORDER BY total DESC) as rank").
    From("orders").
    All(&rankedOrders)

// Running totals
db.Builder().
    SelectExpr("date", "amount",
        "SUM(amount) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as running_total").
    From("transactions").
    All(&totals)

Functions: RANK(), ROW_NUMBER(), DENSE_RANK(), LAG(), LEAD(), NTILE(), and more

📚 Full Guide - Complete reference with frame specifications

🔗 WrapDB() - External Connection Integration

Integrate Relica with existing database infrastructure:

// Your existing connection pool
sqlDB, _ := sql.Open("postgres", dsn)
sqlDB.SetMaxOpenConns(100)
sqlDB.SetMaxIdleConns(50)
sqlDB.SetConnMaxLifetime(time.Hour)

// Wrap with Relica query builder
db := relica.WrapDB(sqlDB, "postgres")

// Use all Relica features
db.Builder().
    Select("u.id", "u.name").
    From("users u").
    LeftJoin("orders o", "o.user_id = u.id").
    GroupBy("u.id").
    All(&users)

// You own the connection lifecycle
defer sqlDB.Close()  // NOT db.Close()

Benefits:

  • ✅ Single connection pool (no duplication)
  • ✅ Gradual migration path
  • ✅ Custom pool configuration before wrapping
  • ✅ Enterprise integration without architectural changes

Production Validated: IrisMX (10K+ concurrent users) requested and validated this feature

📊 Performance Highlights

Metric Result Details
EXISTS vs IN 5x faster 109ns vs 516ns (query building)
UNION ALL vs UNION 2-3x faster No duplicate removal overhead
N+1 Query Reduction 3-18x faster SQLite 6.6x, PostgreSQL 18x, MySQL 3x
Memory Efficiency 100x reduction With pagination (LIMIT/OFFSET)
Batch INSERT 3.3x faster vs individual inserts

🧪 Quality Metrics

  • ✅ Test Coverage: 89.9% (310+ tests)
  • ✅ Dependencies: 0 (production), 2 (tests only)
  • ✅ Databases: PostgreSQL, MySQL 8.0+, SQLite 3.25+
  • ✅ Go Version: 1.25+
  • ✅ CI/CD: All checks passing ✓

📚 Documentation

New User Guides (v0.3.0)

Additional Resources

🚀 Installation

go get github.com/coregx/relica@v0.3.0-beta

🙏 Acknowledgments

  • IrisMX Team - First production user, WrapDB() feature request and validation
  • Community - Feedback and contributions
  • Professor Ancha Baranova - Invaluable support

🎯 What's Next?

v0.4.0-beta (Q1 2026) - Production Hardening:

  • Query optimizer with auto-index hints
  • Query analyzer (EXPLAIN integration)
  • Performance tuning enhancements
  • Security hardening

v1.0.0 (Q2 2026) - Production Stable Release

See ROADMAP.md for details.

Full Changelog: https://github.com/coregx/relica/blob/main/CHANGELOG.md

"If you want magic, use GORM. If you want control, use Relica."

Assets 2
Loading