Library

upper/db

Overview

upper/db is developed as "A productive data access layer for Go" - a Data Access Layer (DAL) library for Go that supports PostgreSQL, CockroachDB, MySQL, SQLite, and MongoDB. While providing ORM-like features, it offers tools for handling common database operations while remaining unobtrusive in advanced scenarios. It enables collaboration with different data sources through a unified API regardless of SQL or NoSQL databases, with a design that emphasizes the balance between productivity and flexibility.

Details

upper/db v4 is the culmination of several years of experience using Go, SQL databases, and various APIs in production. It has moved from vanity import paths (upper.io/db.v3) to a more familiar github.com/upper/db/v4 path. It comprehensively supports features necessary for enterprise-level application development, including custom SQL query construction through SQL builders, implementation of advanced patterns through db.Record and Store interfaces, and unified APIs supporting multiple databases.

Key Features

• Multi-database Support: Unified support for PostgreSQL, CockroachDB, MySQL, SQLite, MongoDB
• Unified API: Consistent interface regardless of SQL or NoSQL
• SQL Builder: Custom query construction for advanced cases
• ORM-like Features: Convenient abstraction through db.Record and Store interfaces
• Non-intrusive Design: Remains unobtrusive in advanced scenarios
• Productivity Focus: Enables productive development immediately from start

Pros and Cons

Pros

• Unified operation of multiple databases and improved development efficiency
• Consistent API design supporting both SQL and NoSQL
• Selective use of raw SQL and SQL builders
• Low learning cost due to simple and intuitive API
• Reliability through enterprise-level production track record
• Simple and clear design philosophy characteristic of Go

Cons

• Go language exclusive with no multi-language support
• Lightweight design without heavyweight ORM features
• Complex relationship management requires manual implementation
• Advanced database-specific features may not be abstracted
• May not be suitable for migrating large legacy systems
• Limited functionality as a complete ORM replacement

Reference Pages

• upper/db Official Site
• upper/db GitHub Repository
• upper/db v4 Documentation

Code Examples

Setup

# Initialize Go module
go mod init upper-db-example

# Install upper/db v4
go get github.com/upper/db/v4

# Add database drivers (according to the database you use)
go get github.com/upper/db/v4/adapter/postgresql  # PostgreSQL
go get github.com/upper/db/v4/adapter/mysql       # MySQL
go get github.com/upper/db/v4/adapter/sqlite      # SQLite
go get github.com/upper/db/v4/adapter/mongo       # MongoDB

// go.mod
module upper-db-example

go 1.21

require (
    github.com/upper/db/v4 v4.8.0
    github.com/upper/db/v4/adapter/postgresql v4.8.0
    github.com/upper/db/v4/adapter/mysql v4.8.0
    github.com/upper/db/v4/adapter/sqlite v4.8.0
    github.com/upper/db/v4/adapter/mongo v4.8.0
)

Basic Usage

package main

import (
    "fmt"
    "log"
    "time"

    "github.com/upper/db/v4"
    "github.com/upper/db/v4/adapter/postgresql"
    "github.com/upper/db/v4/adapter/sqlite"
)

// User model
type User struct {
    ID        uint      `db:"id,omitempty"`
    Username  string    `db:"username"`
    Email     string    `db:"email"`
    CreatedAt time.Time `db:"created_at,omitempty"`
}

// Post model  
type Post struct {
    ID       uint   `db:"id,omitempty"`
    Title    string `db:"title"`
    Content  string `db:"content"`
    AuthorID uint   `db:"author_id"`
    User     *User  `db:"-"` // For relations (not a DB field)
}

func main() {
    // PostgreSQL connection
    postgresSession, err := postgresql.Open(postgresql.ConnectionURL{
        Database: "testdb",
        Host:     "localhost",
        User:     "testuser",
        Password: "testpass",
    })
    if err != nil {
        log.Fatal("PostgreSQL connection error:", err)
    }
    defer postgresSession.Close()

    // SQLite connection (for comparison)
    sqliteSession, err := sqlite.Open(sqlite.ConnectionURL{
        Database: "test.db",
    })
    if err != nil {
        log.Fatal("SQLite connection error:", err)
    }
    defer sqliteSession.Close()

    // Database operation examples
    err = demonstrateBasicOperations(postgresSession)
    if err != nil {
        log.Fatal("Operation error:", err)
    }
}

func demonstrateBasicOperations(sess db.Session) error {
    // Get table references
    usersCol := sess.Collection("users")
    postsCol := sess.Collection("posts")

    // Create user
    user := User{
        Username:  "john_doe",
        Email:     "[email protected]",
        CreatedAt: time.Now(),
    }

    id, err := usersCol.Insert(user)
    if err != nil {
        return fmt.Errorf("user insertion error: %w", err)
    }

    fmt.Printf("Created user ID: %v\n", id)

    // Search user
    var foundUser User
    err = usersCol.Find(db.Cond{"username": "john_doe"}).One(&foundUser)
    if err != nil {
        return fmt.Errorf("user search error: %w", err)
    }

    fmt.Printf("Found user: %+v\n", foundUser)

    // Create post
    post := Post{
        Title:    "My First Post",
        Content:  "This is the content of my first post.",
        AuthorID: foundUser.ID,
    }

    _, err = postsCol.Insert(post)
    if err != nil {
        return fmt.Errorf("post insertion error: %w", err)
    }

    // Get multiple users
    var users []User
    err = usersCol.Find().All(&users)
    if err != nil {
        return fmt.Errorf("user list retrieval error: %w", err)
    }

    fmt.Printf("Total users: %d\n", len(users))

    return nil
}

Advanced Queries with SQL Builder

package main

import (
    "fmt"
    "log"

    "github.com/upper/db/v4"
    "github.com/upper/db/v4/adapter/postgresql"
)

type UserWithPostCount struct {
    ID        uint   `db:"id"`
    Username  string `db:"username"`
    Email     string `db:"email"`
    PostCount int    `db:"post_count"`
}

type PostWithAuthor struct {
    ID          uint   `db:"id"`
    Title       string `db:"title"`
    Content     string `db:"content"`
    AuthorName  string `db:"author_name"`
    AuthorEmail string `db:"author_email"`
}

func demonstrateSQLBuilder(sess db.Session) error {
    // Get SQL builder
    sqlBuilder := sess.SQL()

    // JOIN query for users and post counts
    var usersWithCounts []UserWithPostCount
    err := sqlBuilder.
        Select("u.id", "u.username", "u.email", "COUNT(p.id) as post_count").
        From("users u").
        LeftJoin("posts p ON u.id = p.author_id").
        GroupBy("u.id", "u.username", "u.email").
        OrderBy("post_count DESC").
        All(&usersWithCounts)

    if err != nil {
        return fmt.Errorf("JOIN query error: %w", err)
    }

    fmt.Printf("Users and post counts:\n")
    for _, user := range usersWithCounts {
        fmt.Printf("  %s (%s): %d posts\n", user.Username, user.Email, user.PostCount)
    }

    // JOIN query for posts and author information
    var postsWithAuthors []PostWithAuthor
    err = sqlBuilder.
        Select("p.id", "p.title", "p.content", "u.username as author_name", "u.email as author_email").
        From("posts p").
        Join("users u ON p.author_id = u.id").
        Where("p.title LIKE ?", "%First%").
        OrderBy("p.id DESC").
        All(&postsWithAuthors)

    if err != nil {
        return fmt.Errorf("post JOIN query error: %w", err)
    }

    fmt.Printf("\nPosts and author information:\n")
    for _, post := range postsWithAuthors {
        fmt.Printf("  %s by %s (%s)\n", post.Title, post.AuthorName, post.AuthorEmail)
    }

    // Conditional update
    res, err := sqlBuilder.
        Update("users").
        Set("email = ?", "[email protected]").
        Where("username = ?", "john_doe").
        Exec()

    if err != nil {
        return fmt.Errorf("update error: %w", err)
    }

    rowsAffected, _ := res.RowsAffected()
    fmt.Printf("\nRows updated: %d\n", rowsAffected)

    return nil
}

// Example using complex search conditions
func demonstrateAdvancedQueries(sess db.Session) error {
    postsCol := sess.Collection("posts")

    // Search with complex conditions
    var posts []Post
    err := postsCol.Find(
        db.Or(
            db.Cond{"title LIKE": "%Go%"},
            db.Cond{"title LIKE": "%Database%"},
        ),
        db.Cond{"author_id >": 0},
    ).OrderBy("-id").Limit(10).All(&posts)

    if err != nil {
        return fmt.Errorf("advanced search error: %w", err)
    }

    fmt.Printf("Search results: %d items\n", len(posts))

    // Pagination
    const pageSize = 5
    page := 2

    var paginatedPosts []Post
    err = postsCol.Find().
        OrderBy("id").
        Limit(pageSize).
        Offset((page - 1) * pageSize).
        All(&paginatedPosts)

    if err != nil {
        return fmt.Errorf("pagination error: %w", err)
    }

    fmt.Printf("Posts on page %d: %d items\n", page, len(paginatedPosts))

    return nil
}

func main() {
    sess, err := postgresql.Open(postgresql.ConnectionURL{
        Database: "testdb",
        Host:     "localhost",
        User:     "testuser",
        Password: "testpass",
    })
    if err != nil {
        log.Fatal("Connection error:", err)
    }
    defer sess.Close()

    if err := demonstrateSQLBuilder(sess); err != nil {
        log.Fatal("SQL builder error:", err)
    }

    if err := demonstrateAdvancedQueries(sess); err != nil {
        log.Fatal("Advanced query error:", err)
    }
}

Multi-database Support and Transactions

package main

import (
    "fmt"
    "log"

    "github.com/upper/db/v4"
    "github.com/upper/db/v4/adapter/postgresql"
    "github.com/upper/db/v4/adapter/mysql"
    "github.com/upper/db/v4/adapter/sqlite"
    "github.com/upper/db/v4/adapter/mongo"
)

// Database abstraction interface
type DatabaseService interface {
    CreateUser(user User) (interface{}, error)
    GetUserByUsername(username string) (*User, error)
    GetAllUsers() ([]User, error)
    Close() error
}

// PostgreSQL implementation
type PostgreSQLService struct {
    session db.Session
}

func NewPostgreSQLService() (*PostgreSQLService, error) {
    sess, err := postgresql.Open(postgresql.ConnectionURL{
        Database: "testdb",
        Host:     "localhost", 
        User:     "testuser",
        Password: "testpass",
    })
    if err != nil {
        return nil, err
    }
    return &PostgreSQLService{session: sess}, nil
}

func (s *PostgreSQLService) CreateUser(user User) (interface{}, error) {
    return s.session.Collection("users").Insert(user)
}

func (s *PostgreSQLService) GetUserByUsername(username string) (*User, error) {
    var user User
    err := s.session.Collection("users").Find(db.Cond{"username": username}).One(&user)
    if err != nil {
        return nil, err
    }
    return &user, nil
}

func (s *PostgreSQLService) GetAllUsers() ([]User, error) {
    var users []User
    err := s.session.Collection("users").Find().All(&users)
    return users, err
}

func (s *PostgreSQLService) Close() error {
    return s.session.Close()
}

// MongoDB implementation (NoSQL example)
type MongoService struct {
    session db.Session
}

func NewMongoService() (*MongoService, error) {
    sess, err := mongo.Open(mongo.ConnectionURL{
        Database: "testdb",
        Host:     "localhost",
    })
    if err != nil {
        return nil, err
    }
    return &MongoService{session: sess}, nil
}

func (s *MongoService) CreateUser(user User) (interface{}, error) {
    return s.session.Collection("users").Insert(user)
}

func (s *MongoService) GetUserByUsername(username string) (*User, error) {
    var user User
    err := s.session.Collection("users").Find(db.Cond{"username": username}).One(&user)
    if err != nil {
        return nil, err
    }
    return &user, nil
}

func (s *MongoService) GetAllUsers() ([]User, error) {
    var users []User
    err := s.session.Collection("users").Find().All(&users)
    return users, err
}

func (s *MongoService) Close() error {
    return s.session.Close()
}

// Transaction processing example
func demonstrateTransactions(sess db.Session) error {
    return sess.Tx(func(tx db.Session) error {
        usersCol := tx.Collection("users")
        postsCol := tx.Collection("posts")

        // Create user
        user := User{
            Username: "tx_user",
            Email:    "[email protected]",
        }

        userID, err := usersCol.Insert(user)
        if err != nil {
            return fmt.Errorf("user creation error in transaction: %w", err)
        }

        // Create post
        post := Post{
            Title:    "Transaction Post",
            Content:  "Created within a transaction",
            AuthorID: userID.(uint),
        }

        _, err = postsCol.Insert(post)
        if err != nil {
            return fmt.Errorf("post creation error in transaction: %w", err)
        }

        fmt.Println("Transaction processing completed")
        return nil
    })
}

// Batch processing example
func demonstrateBatchOperations(sess db.Session) error {
    usersCol := sess.Collection("users")

    // Batch insertion
    users := []User{
        {Username: "batch_user1", Email: "[email protected]"},
        {Username: "batch_user2", Email: "[email protected]"},
        {Username: "batch_user3", Email: "[email protected]"},
    }

    // Insert each user
    for _, user := range users {
        _, err := usersCol.Insert(user)
        if err != nil {
            return fmt.Errorf("batch insertion error: %w", err)
        }
    }

    // Batch update
    res, err := sess.SQL().
        Update("users").
        Set("email = CONCAT(username, '@batch.com')").
        Where("username LIKE ?", "batch_%").
        Exec()

    if err != nil {
        return fmt.Errorf("batch update error: %w", err)
    }

    rowsAffected, _ := res.RowsAffected()
    fmt.Printf("Batch updated rows: %d\n", rowsAffected)

    return nil
}

func main() {
    // PostgreSQL service
    pgService, err := NewPostgreSQLService()
    if err != nil {
        log.Fatal("PostgreSQL service creation error:", err)
    }
    defer pgService.Close()

    // Operations with unified interface
    services := []DatabaseService{pgService}

    for i, service := range services {
        fmt.Printf("=== Database Service %d ===\n", i+1)

        // Create user
        user := User{
            Username: fmt.Sprintf("service_user_%d", i+1),
            Email:    fmt.Sprintf("service%[email protected]", i+1),
        }

        _, err := service.CreateUser(user)
        if err != nil {
            log.Printf("User creation error: %v", err)
            continue
        }

        // Get user
        foundUser, err := service.GetUserByUsername(user.Username)
        if err != nil {
            log.Printf("User retrieval error: %v", err)
            continue
        }

        fmt.Printf("Created and retrieved user: %+v\n", foundUser)
    }

    // Transaction and batch processing demos
    if err := demonstrateTransactions(pgService.session); err != nil {
        log.Printf("Transaction error: %v", err)
    }

    if err := demonstrateBatchOperations(pgService.session); err != nil {
        log.Printf("Batch processing error: %v", err)
    }
}