immugorm: Simplifying immudb Queries with GORM

An ORM (Object-Relational Mapping) is a programming technique that bridges the gap between object-oriented programming languages and relational databases. In traditional relational databases, data is stored in tables consisting of rows and columns. To interact with this data, developers write SQL (Structured Query Language) queries that retrieve, insert, update, or delete data. However, object-oriented programming languages (like Python, Java, or Go) represent data as objects, which can have attributes and methods.

Advantages of Using ORMs

An ORM allows developers to treat database tables as if they were object classes in their code, and each row in a table as an instance of that class. Instead of writing raw SQL queries to interact with the database, the developer interacts with objects in their code, and the ORM translates these interactions into SQL queries behind the scenes. This offers a layer of abstraction that can improve both productivity and maintainability. More specifically, ORMs provide developers with the following distinct advantages:

• Productivity: With ORMs, developers can focus more on business logic rather than spending time crafting complex SQL queries. By providing simple APIs, developers can perform CRUD (Create, Read, Update, Delete) operations in just a few lines of code without needing to know the intricacies of SQL syntax. This can significantly speed up development, especially for applications that frequently interact with a database.
  
  
• Maintainability: Using objects rather than SQL queries makes the codebase easier to maintain and understand, particularly for developers who may not be experts in SQL. If the database schema changes (e.g., renaming a table or changing a column type), developers can often make adjustments at the ORM level without rewriting SQL queries throughout the application.
  
  
• Security: ORMs help mitigate common security risks, such as SQL injection. In raw SQL, user inputs can inadvertently manipulate the query if they are not properly sanitized, leading to vulnerabilities. ORMs handle input sanitization internally, reducing the risk of injection attacks, as the ORM typically uses parameterized queries.
  
  
• Portability: Applications built with ORMs tend to be more portable across different database systems. ORMs abstract away the differences between various SQL dialects (such as immudb, PostgreSQL, MySQL, etc.). If an application needs to switch from one database system to another, developers can often do so with minimal changes to the code, as the ORM will handle the nuances of the new database's syntax.

GORM: An ORM for Go (Golang)

One example of an ORM is GORM, a popular library widely used in the Go (Golang) ecosystem due to its simplicity, flexibility, and powerful features. It offers a rich set of features, such as:

• CRUD Operations: Simple methods for Create, Read, Update, and Delete operations.
• Associations: Supports one-to-one, one-to-many, and many-to-many relationships.
• Migrations: Automatically migrates schema changes to the database.
• Query Builders: Chainable query methods and support for raw SQL.
• Lifecycle Hooks: Custom logic before/after operations (e.g., BeforeCreate, AfterUpdate).
• Preloading/Eager Loading: Efficiently load related data to prevent N+1 query issues.
• Transactions: Easy transaction management with Begin(), Commit(), Rollback().
• Custom Data Types: Map Go types to custom database types.
• Pagination: Manage large datasets using Limit() and Offset().

immugorm: Bringing Tamper-Proof Immutability to GORM

GORM simplifies integrations with external databases through custom connectors, extending the basic catalog of databases which are supported natively (Postgres, MySQL, SQLite, etc...).

The immugorm plugin (https://github.com/codenotary/immugorm) brings the benefit of tamper-proof immutability to GORM, thus allowing users to run queries against an immudb server instance. With immugorm, executing complex queries such as aggregations, becomes as simple as calling a few functions.

The following snippet demonstrates how to connect to an immudb instance using GORM (with the immugorm connector) and execute a simple aggregation query.

type Product struct {
    ID    int    `gorm:"primarykey"`
    Type  string
    Code  string
    Price uint
}

// open a database connection
db, err := gorm.Open(immugorm.Open("immudb://immudb:immudb@127.0.0.1:3322/defaultdb?sslmode=disable", 
           &immugorm.ImmuGormConfig{Verify: false}), 
           &gorm.Config{Logger: logger.Default.LogMode(logger.Info)},
)
if err != nil {
    log.Fatal(err)
}

// migrate the schema
err = db.AutoMigrate(&Product{})
if err != nil {
    log.Fatal(err)
}

// insert some products
products := []Product{
   {Type: "type-a", Code: "000", Price: 50},
   {Type: "type-a", Code: "001", Price: 80},
   // Additional product entries...
}
err = db.Create(&products).Error
if err != nil {
    log.Fatal(err)
}

// select total price of all products grouped by type
var productType string
var totalAmount uint

err = db.Model(&Product{}).Select("type, sum(price) as total_price").Where("price > ?", 11).Group("type").Row().Scan(&productType, &totalAmount)
if err != nil {
    log.Fatal(err)
}

As you can observe, GORM handles the entire SQL query generation process, including table creation, record insertion, and query execution, all without requiring you to write any SQL code.

You can find comprehensive information on executing GORM queries in the official documentation here: https://gorm.io/docs/index.html.