Courses/Database Mastery with GORM

Standalone Course~30 min•14 challenges

Database Mastery with GORM: Models, Migrations & Queries

Every application stores data. The question is how elegantly you interact with it. GORM lets you work with databases using Go structs instead of raw SQL. In this course, you'll master models, migrations, relationships, advanced queries, scopes, hooks, and performance optimization — everything you need to build data-heavy applications with Grit.

What is GORM?

Databases speak SQL. Go speaks structs. An ORM bridges the gap — it translates between your Go code and the database, so you can create, read, update, and delete records using familiar Go syntax instead of writing SQL strings.

ORM (Object-Relational Mapping): A programming technique that maps database tables to objects (or structs) in your code. Instead of writing SELECT * FROM users WHERE id = 1, you write db.First(&user, 1). The ORM generates the SQL for you. This makes code more readable, portable across databases, and less prone to SQL injection.

GORM: Go's most popular ORM library. It supports PostgreSQL, MySQL, SQLite, and SQL Server. Features include auto-migration, associations (relationships), hooks (callbacks), preloading, scopes, soft deletes, and a powerful query builder. Grit uses GORM for all database operations.

Here's the same operation in raw SQL vs GORM:

Raw SQL vs GORM

// Raw SQL
db.Exec("INSERT INTO users (name, email, role) VALUES (?, ?, ?)", "John", "john@test.com", "USER")

// GORM — same result, but type-safe and readable
user := User{Name: "John", Email: "john@test.com", Role: "USER"}
db.Create(&user)
// user.ID is now set automatically

Challenge: ORM vs Raw SQL

What are 3 advantages of using an ORM like GORM over writing raw SQL? Think about readability, safety, and portability. Can you think of a situation where raw SQL would be better than an ORM?

Defining Models

A GORM model is a Go struct that represents a database table. Each field becomes a column. You control column behavior with struct tags — constraints, indexes, defaults, and JSON serialization names.

models/product.go

type Product struct {
    gorm.Model
    // gorm.Model adds: ID, CreatedAt, UpdatedAt, DeletedAt

    Name        string   // tag: not null; json name
    Slug        string   // tag: uniqueIndex, not null; json slug
    Price       float64  // tag: not null, default:0; json price
    Description string   // tag: type:text; json description
    Active      bool     // tag: default:true; json active
    CategoryID  uint     // tag: index; json category_id

    // Relationship
    Category    Category // GORM auto-loads via CategoryID
}

GORM struct tags use the format gorm:"tagvalue". Common tags:not null (required), uniqueIndex (no duplicates),default:value (default value), type:text (column type),index (database index for faster queries).

gorm.Model: A base struct that embeds 4 fields into your model: ID (uint, auto-incrementing primary key), CreatedAt (timestamp, set on create), UpdatedAt (timestamp, updated on every save), and DeletedAt (nullable timestamp for soft deletes). Every Grit model embeds this.

Common field types and their database column types:

Field Type Mapping

string    → VARCHAR(255)     // Short text (names, emails)
string    → TEXT              // Long text (with gorm type:text tag)
int       → INTEGER           // Whole numbers
uint      → INTEGER UNSIGNED  // Positive whole numbers (IDs)
float64   → DOUBLE PRECISION  // Decimal numbers (prices)
bool      → BOOLEAN           // true/false
time.Time → TIMESTAMP         // Dates and times

Challenge: Define a Blog Post Model

Define a GORM model for a Blog Post with these fields: Title (required string), Slug (unique string), Content (long text), Published (boolean, defaults to false), AuthorID (foreign key to User). Don't forget to embed gorm.Model.

Auto-Migration

Once you define a model, you need to create the corresponding database table. GORM's auto-migration reads your struct and creates (or updates) the table to match. No manual SQL needed.

database/database.go

func Migrate(db *gorm.DB) {
    db.AutoMigrate(
        &models.User{},
        &models.Product{},
        &models.Category{},
        &models.Blog{},
    )
}

In a Grit project, you run migrations with:

Terminal

# Run migrations (creates/updates tables)
grit migrate

# Fresh migration (drops all tables, re-creates from scratch)
grit migrate --fresh

What auto-migration does:

• Creates tables that don't exist
• Adds columns that are missing
• Creates indexes and constraints
• Does NOT delete columns (to prevent data loss)
• Does NOT change column types

Use grit migrate --fresh during development when you change column types or remove fields. In production, never use --fresh — it drops all your data. For production column changes, use manual SQL migrations.

Challenge: Add a Field and Migrate

Add a new Stock int field to your Product model. Run grit migrate. Open GORM Studio and check the products table. Did the new column appear? What's its default value?

CRUD Operations

CRUD stands for Create, Read, Update, Delete — the four fundamental database operations. GORM provides clean methods for each one. Let's see them all:

Create

// Create a single record
product := models.Product{
    Name:  "Widget",
    Price: 29.99,
    Active: true,
}
result := db.Create(&product)
// product.ID is now set (auto-increment)
// result.Error contains any error
// result.RowsAffected is 1

// Create multiple records
products := []models.Product{
    {Name: "Widget A", Price: 10.0},
    {Name: "Widget B", Price: 20.0},
    {Name: "Widget C", Price: 30.0},
}
db.Create(&products)

Read

// Find by primary key
var product models.Product
db.First(&product, 1)          // SELECT * FROM products WHERE id = 1

// Find by condition
db.Where("slug = ?", "widget").First(&product)

// Find all (with pagination)
var products []models.Product
db.Offset(0).Limit(20).Find(&products)

// Find all matching a condition
db.Where("active = ? AND price > ?", true, 10.0).Find(&products)

Update

// Update a single field
db.Model(&product).Update("price", 39.99)

// Update multiple fields
db.Model(&product).Updates(map[string]interface{}{
    "name":  "Super Widget",
    "price": 39.99,
})

// Update with struct (only non-zero fields)
db.Model(&product).Updates(models.Product{Name: "Super Widget", Price: 39.99})

Delete

// Soft delete (sets DeletedAt timestamp)
db.Delete(&product, id)
// Row still exists in database, but hidden from queries

// Permanently delete (skip soft delete)
db.Unscoped().Delete(&product, id)

// Restore a soft-deleted record
db.Model(&product).Unscoped().Update("deleted_at", nil)

Soft Delete: Instead of removing the row from the database, GORM sets the DeletedAt timestamp. All subsequent queries automatically exclude soft-deleted records (by adding WHERE deleted_at IS NULL). The data is still in the database and can be recovered. This is the default behavior when your model embeds gorm.Model.

Challenge: CRUD with GORM Studio

Using GORM Studio, perform these operations: (1) Create 3 products with different names and prices, (2) Query all products, (3) Update one product's price, (4) Soft-delete a product, (5) Query all products again — is the deleted one still visible? (6) Usedb.Unscoped().Find(&products) to see all products including deleted ones.

Relationships

Real applications have related data. A product belongs to a category. A category has many products. A post has many tags, and a tag has many posts. GORM handles all three relationship types: belongs_to, has_many, and many_to_many.

Belongs To (Product → Category)

type Product struct {
    gorm.Model
    Name       string
    Price      float64
    CategoryID uint       // Foreign key
    Category   Category   // GORM loads this automatically
}

type Category struct {
    gorm.Model
    Name string
}

// Query: find a product with its category
var product models.Product
db.Preload("Category").First(&product, 1)
// product.Category.Name is now available

Has Many (Category → Products)

type Category struct {
    gorm.Model
    Name     string
    Products []Product  // GORM loads all products for this category
}

// Query: find a category with all its products
var category models.Category
db.Preload("Products").First(&category, 1)
// category.Products is now a slice of Product structs

Many to Many (Post ↔ Tag)

type Post struct {
    gorm.Model
    Title string
    Tags  []Tag  // gorm many2many:post_tags
}

type Tag struct {
    gorm.Model
    Name  string
    Posts []Post  // gorm many2many:post_tags
}

// GORM auto-creates a "post_tags" join table
// Query: find a post with its tags
var post models.Post
db.Preload("Tags").First(&post, 1)

Preloading: Loading related data in a single query instead of making separate queries. Without preloading, accessing product.Category would be empty. With db.Preload("Category").Find(&products), GORM runs a second query to load all categories for the found products and attaches them to the struct. This solves the N+1 query problem.

The many_to_many relationship requires a GORM struct tag on the field. GORM auto-creates the join table (in this case post_tags with columns post_id andtag_id). You never need to define the join table manually.

Challenge: Build and Query Relationships

Create a Category model with a Name field. Add a CategoryID andCategory field to your Product model. Run grit migrate. Then: (1) Create a category called "Electronics", (2) Create 5 products in that category, (3) Query products with db.Preload("Category").Find(&products). Does the category name appear on each product?

Query Building

GORM's query builder lets you chain conditions to build complex queries. It's like writing SQL, but in Go — type-safe, composable, and readable.

Where, Order, Limit

// Find active products over $10, ordered by price (highest first)
var products []models.Product
db.Where("price > ? AND active = ?", 10.0, true).
    Order("price DESC").
    Limit(10).
    Find(&products)

// Multiple Where conditions (AND)
db.Where("category_id = ?", 1).
    Where("price BETWEEN ? AND ?", 10.0, 50.0).
    Find(&products)

// OR condition
db.Where("name LIKE ?", "%widget%").
    Or("name LIKE ?", "%gadget%").
    Find(&products)

Count, Select, Group

// Count records
var count int64
db.Model(&models.Product{}).
    Where("active = ?", true).
    Count(&count)

// Select specific columns (faster — less data transferred)
db.Select("name", "price").Find(&products)

// Group by with aggregate
type CategoryStats struct {
    CategoryID uint
    Total      int64
    AvgPrice   float64
}
var stats []CategoryStats
db.Model(&models.Product{}).
    Select("category_id, COUNT(*) as total, AVG(price) as avg_price").
    Group("category_id").
    Scan(&stats)

Not, Distinct, Pluck

// Not — exclude records
db.Not("active = ?", false).Find(&products)

// Distinct — unique values only
var names []string
db.Model(&models.Product{}).Distinct("name").Pluck("name", &names)

// Pluck — extract a single column into a slice
var prices []float64
db.Model(&models.Product{}).Where("active = ?", true).Pluck("price", &prices)

Challenge: Write Complex Queries

Write these queries using GORM's query builder: (1) Find the 5 most expensive active products, ordered by price descending, (2) Count the total number of products per category, (3) Find all products whose name contains "Pro" and cost more than $50, (4) Get the average price of all active products.

Scopes

When you find yourself writing the same Where clause in multiple places, it's time to create a scope. A scope is a reusable query function that you can chain onto any query.

Scope: A reusable query condition packaged as a function. The function takes a *gorm.DB and returns a *gorm.DB with additional conditions applied. Scopes can be composed — you can chain multiple scopes together to build complex queries from simple, named pieces.

scopes.go

// Define reusable scopes
func Active(db *gorm.DB) *gorm.DB {
    return db.Where("active = ?", true)
}

func Expensive(db *gorm.DB) *gorm.DB {
    return db.Where("price > ?", 100)
}

func InCategory(categoryID uint) func(db *gorm.DB) *gorm.DB {
    return func(db *gorm.DB) *gorm.DB {
        return db.Where("category_id = ?", categoryID)
    }
}

func Paginate(page, pageSize int) func(db *gorm.DB) *gorm.DB {
    return func(db *gorm.DB) *gorm.DB {
        offset := (page - 1) * pageSize
        return db.Offset(offset).Limit(pageSize)
    }
}

// Usage: compose scopes together
var products []models.Product
db.Scopes(Active, Expensive).Find(&products)

// With parameterized scopes
db.Scopes(Active, InCategory(3), Paginate(1, 20)).Find(&products)

Scopes make your code self-documenting. Compare db.Where("active = ?", true).Where("price > ?", 100) with db.Scopes(Active, Expensive). The second version tells you exactly what the query does without reading the conditions.

Challenge: Create a Published Scope

Create a Published scope for blog posts that filters to only published posts (published = true). Create a Recent scope that orders bycreated_at DESC and limits to 10. Use them together:db.Scopes(Published, Recent).Find(&posts). Does it return the 10 most recent published posts?

Hooks

Sometimes you need code to run automatically before or after a database operation. For example, auto-generating a slug from the title when creating a blog post, or sending a welcome email after creating a user. GORM hooks let you do this.

Hook (Callback): A method on your model that GORM calls automatically at specific points in the lifecycle of a database operation. Available hooks: BeforeCreate, AfterCreate,BeforeUpdate, AfterUpdate, BeforeSave (both create and update), AfterSave, BeforeDelete, AfterDelete.

hooks.go

import "strings"

// BeforeCreate: auto-generate slug from name
func (p *Product) BeforeCreate(tx *gorm.DB) error {
    if p.Slug == "" {
        p.Slug = strings.ToLower(strings.ReplaceAll(p.Name, " ", "-"))
    }
    return nil
}

// AfterCreate: log the creation
func (p *Product) AfterCreate(tx *gorm.DB) error {
    fmt.Printf("Product created: %s (ID: %d)
", p.Name, p.ID)
    return nil
}

// BeforeSave: validate price
func (p *Product) BeforeSave(tx *gorm.DB) error {
    if p.Price < 0 {
        return fmt.Errorf("price cannot be negative")
    }
    return nil
}

// BeforeDelete: prevent deleting products with orders
func (p *Product) BeforeDelete(tx *gorm.DB) error {
    var count int64
    tx.Model(&Order{}).Where("product_id = ?", p.ID).Count(&count)
    if count > 0 {
        return fmt.Errorf("cannot delete product with existing orders")
    }
    return nil
}

If a hook returns an error, the database operation is cancelled and the error is returned to the caller. This makes hooks perfect for validation — if a product's price is negative, the BeforeSave hook rejects the save before it reaches the database.

Challenge: Add a Slug Hook

Add a BeforeCreate hook to your Blog model that auto-generates a slug from the title. "My First Post" should become "my-first-post". Test it: create a blog post with only a title, then check if the slug was set automatically. Also add a BeforeSavehook that ensures the title is not empty.

Raw SQL

GORM's query builder handles 90% of your database needs. But sometimes you need the full power of SQL — complex aggregations, window functions, database-specific features, or performance-critical queries. GORM lets you run raw SQL when needed.

Raw Queries

// Raw SELECT — scan results into a struct
type CategoryRevenue struct {
    Category  string
    Total     int64
    AvgPrice  float64
    Revenue   float64
}

var results []CategoryRevenue
db.Raw("SELECT c.name as category, COUNT(*) as total, " +
    "AVG(p.price) as avg_price, SUM(p.price * p.stock) as revenue " +
    "FROM products p JOIN categories c ON p.category_id = c.id " +
    "GROUP BY c.name ORDER BY revenue DESC").
    Scan(&results)

// Raw UPDATE
db.Exec("UPDATE products SET price = price * 1.1 WHERE category_id = ?", categoryID)

// Raw DELETE
db.Exec("DELETE FROM sessions WHERE expires_at < NOW()")

// Check if raw query had an error
result := db.Exec("UPDATE products SET price = 0 WHERE id = ?", id)
if result.Error != nil {
    // Handle error
}
fmt.Println(result.RowsAffected) // Number of rows affected

Always use parameterized queries (? placeholders) even in raw SQL. Never concatenate user input into SQL strings. db.Raw("... WHERE id = ?", id) is safe. db.Raw("... WHERE id = " + id) is a SQL injection vulnerability.

Challenge: Write a Revenue Query

Write a raw SQL query that returns: (1) the average price per category, (2) the total number of products per category, and (3) the most expensive product in each category. Define a result struct to hold the data. Run it and check the results in GORM Studio.

Performance Tips

A slow database is a slow application. Here are the most impactful performance optimizations you can apply to your GORM queries:

1. Use Indexes — An index is like a book's table of contents. Without it, the database scans every row to find matches. With it, lookups are nearly instant.

Indexes

type Product struct {
    gorm.Model
    Name       string  // gorm:"index"          — faster lookups by name
    Slug       string  // gorm:"uniqueIndex"    — fast + no duplicates
    CategoryID uint    // gorm:"index"          — faster joins and filters
    Email      string  // gorm:"uniqueIndex"    — fast + unique constraint
}

// Composite index (search by category AND active together)
// gorm:"index:idx_category_active"

2. Avoid N+1 Queries — The most common performance killer. If you load 100 products and each one triggers a separate query to load its category, that's 101 queries. Use Preload to load everything in 2 queries.

N+1 Problem

// BAD: N+1 queries (1 for products + N for categories)
var products []models.Product
db.Find(&products)
for _, p := range products {
    // This triggers a separate query for each product!
    fmt.Println(p.Category.Name)
}

// GOOD: 2 queries total (1 for products, 1 for all categories)
db.Preload("Category").Find(&products)
for _, p := range products {
    fmt.Println(p.Category.Name) // Already loaded
}

3. Paginate Large Results — Never load all records at once. Always use Offset and Limit.

4. Select Only Needed Columns — If you only need the name and price, don't load the entire row.

Efficient Queries

// Only load what you need
db.Select("id", "name", "price").Find(&products)

// Paginate — never load all records
db.Scopes(Paginate(page, 20)).Find(&products)

// Count without loading data
var count int64
db.Model(&Product{}).Where("active = ?", true).Count(&count)

Challenge: Audit Your Queries

Review your product queries from the previous challenges. For each one, check: (1) Are you using Preload for relationships (avoiding N+1)? (2) Are you paginating large result sets? (3) Are you using Select to load only needed columns? (4) Do your frequently-filtered columns have indexes? Fix any issues you find.

Summary

You've mastered GORM — from basic models to advanced query optimization:

Models — Go structs with GORM tags define your database schema
Migrations — grit migrate creates/updates tables automatically
CRUD — Create, Read (First, Find, Where), Update, Delete (soft + hard)
Relationships — belongs_to, has_many, many_to_many with Preload
Query Building — Where, Or, Not, Order, Select, Group, Count
Scopes — reusable, composable query conditions
Hooks — automatic logic before/after database operations
Raw SQL — full SQL power when GORM isn't enough
Performance — indexes, Preload, pagination, Select

Challenge: Final Challenge: Product Analytics (GORM Query Builder)

Using GORM's query builder (no raw SQL), write queries that return:

Total number of products per category (using Group + Count)
Average price per category (using Group + Select with AVG)
The most expensive product overall (using Order + First)
All products with stock below 10 (using Where + Find)

Challenge: Final Challenge: Revenue Report (Raw SQL)

Using raw SQL, write a single query that returns a revenue report:

Category name
Total products in category
Average price in category
Total revenue (SUM of price * stock)
Most expensive product name in each category

Sort by total revenue descending. Define a result struct to hold the data.

Challenge: Final Challenge: Scopes + Hooks Combo

Create a complete Product model with: (1) A BeforeCreate hook that generates a slug and validates the price, (2) An Active scope, a InPriceRange scope, and a Paginate scope, (3) A query that uses all 3 scopes together with Preload for the Category relationship.

Challenge: Final Challenge: Performance Optimization

Take the queries from challenges 11 and 12. For each one: (1) Ensure all filtered columns have indexes, (2) Use Select to load only needed columns, (3) Use Preload instead of separate queries for relationships, (4) Add pagination to any query that could return many results. Measure the difference with GORM's debug mode: db.Debug().Find(&products).

Enjoying the course?

Help us grow — star us on GitHub, subscribe on YouTube, and follow on LinkedIn.

Star on GitHub Subscribe Follow

Need Help?

Book a 1-on-1 session with an expert on WhatsApp

Hire Us

Need a production app built with Grit? Let us build it for you

Sponsor

Support open-source development and keep Grit free

All Courses More Courses