Database Schema Designer

• Entities (users, products, orders)
• Key relationships (users have orders, orders have items)
• Scale hints (high-traffic, millions of records)
• GORM-specific features needed (soft deletes, hooks, custom types)

Triggers

Key Terms

Quick Reference

Process Overview

Your Data Requirements
    |
    v
+-----------------------------------------------------+
| Phase 1: ANALYSIS                                   |
| * Identify entities and relationships               |
| * Determine access patterns (read vs write heavy)   |
| * Choose SQL or NoSQL based on requirements         |
+-----------------------------------------------------+
    |
    v
+-----------------------------------------------------+
| Phase 2: DESIGN                                     |
| * Normalize to 3NF (SQL) or embed/reference (NoSQL) |
| * Define primary keys and foreign keys              |
| * Choose appropriate data types                     |
| * Add constraints (UNIQUE, CHECK, NOT NULL)         |
+-----------------------------------------------------+
    |
    v
+-----------------------------------------------------+
| Phase 3: OPTIMIZE                                   |
| * Plan indexing strategy                            |
| * Consider denormalization for read-heavy queries   |
| * Add timestamps (created_at, updated_at)           |
+-----------------------------------------------------+
    |
    v
+-----------------------------------------------------+
| Phase 4: MIGRATE                                    |
| * Generate migration scripts (up + down)            |
| * Ensure backward compatibility                     |
| * Plan zero-downtime deployment                     |
+-----------------------------------------------------+
    |
    v
Production-Ready Schema

Commands

Workflow: Start with design schema → iterate with normalize → optimize with add indexes → evolve with migration

Core Principles

Anti-Patterns

Verification Checklist

After designing a schema:

• Every table has a primary key
• All relationships have foreign key constraints
• ON DELETE strategy defined for each FK
• Indexes exist on all foreign keys
• Indexes exist on frequently queried columns
• Appropriate data types (DECIMAL for money, etc.)
• NOT NULL on required fields
• UNIQUE constraints where needed
• CHECK constraints for validation
• created_at and updated_at timestamps
• Migration scripts are reversible
• Tested on staging with production data

Normal Forms in GORM

First Normal Form (1NF)

// BAD: Multiple values in single field
type Order struct {
    ID         uint
    ProductIDs string // "101,102,103" - violates 1NF
}

// GOOD: Separate model for items
type Order struct {
    gorm.Model
    CustomerID uint
    Items      []OrderItem `gorm:"foreignKey:OrderID"`
}

type OrderItem struct {
    gorm.Model
    OrderID   uint
    ProductID uint
    Quantity  int
    Price     decimal.Decimal `gorm:"type:decimal(10,2)"`
}

Relationships in GORM

// One-to-Many
type User struct {
    gorm.Model
    Email  string  `gorm:"uniqueIndex;not null"`
    Orders []Order `gorm:"foreignKey:UserID"`
}

// Many-to-Many
type Product struct {
    gorm.Model
    Name       string `gorm:"not null"`
    Categories []Category `gorm:"many2many:product_categories;"`
}

// Self-referencing
type Employee struct {
    gorm.Model
    Name      string
    ManagerID *uint
    Manager   *Employee  `gorm:"foreignKey:ManagerID"`
    Team      []Employee `gorm:"foreignKey:ManagerID"`
}

When to Denormalize

// Denormalized for performance
type Order struct {
    gorm.Model
    UserID     uint

    // Denormalized fields (cached)
    TotalAmount decimal.Decimal `gorm:"type:decimal(10,2)"`
    ItemCount   int
    UserEmail   string `gorm:"type:varchar(255)"` // Cached from User
}

// Use hooks to maintain denormalized data
func (o *Order) BeforeSave(tx *gorm.DB) error {
    // Calculate total from items
    var total decimal.Decimal
    tx.Model(&OrderItem{}).Where("order_id = ?", o.ID).
        Select("SUM(price * quantity)").Scan(&total)
    o.TotalAmount = total
    return nil
}

String Types

type Product struct {
    // PostgreSQL VARCHAR
    Name        string `gorm:"type:varchar(100);not null"`

    // PostgreSQL TEXT
    Description string `gorm:"type:text"`

    // Fixed length CHAR
    CountryCode string `gorm:"type:char(2)"`

    // With size constraint
    Email       string `gorm:"size:255;uniqueIndex"`
}

Numeric Types

import "github.com/shopspring/decimal"

type FinancialRecord struct {
    // Integer types
    ID          uint    `gorm:"primaryKey"`           // BIGSERIAL
    Quantity    int32   `gorm:"type:integer"`         // INTEGER
    BigNumber   int64   `gorm:"type:bigint"`          // BIGINT
    SmallCount  int16   `gorm:"type:smallint"`        // SMALLINT

    // Money - ALWAYS use decimal
    Price       decimal.Decimal `gorm:"type:decimal(10,2);not null"`

    // Float - only for scientific data
    Temperature float64 `gorm:"type:real"`            // REAL
    Precision   float64 `gorm:"type:double precision"` // DOUBLE PRECISION
}

Date/Time Types

import (
    "time"
    "database/sql/driver"
)

type Event struct {
    gorm.Model // Includes CreatedAt, UpdatedAt, DeletedAt

    // PostgreSQL DATE
    EventDate   time.Time `gorm:"type:date"`

    // PostgreSQL TIME
    StartTime   time.Time `gorm:"type:time"`

    // PostgreSQL TIMESTAMP WITH TIME ZONE
    ScheduledAt time.Time `gorm:"type:timestamptz"`

    // Custom nullable time
    CompletedAt *time.Time `gorm:"type:timestamptz"`
}

PostgreSQL-Specific Types

import (
    "github.com/lib/pq"
    "github.com/google/uuid"
)

type AdvancedModel struct {
    // UUID
    ID       uuid.UUID      `gorm:"type:uuid;default:gen_random_uuid();primaryKey"`

    // Arrays
    Tags     pq.StringArray `gorm:"type:text[]"`
    Numbers  pq.Int64Array  `gorm:"type:integer[]"`

    // JSON/JSONB
    Metadata json.RawMessage `gorm:"type:jsonb"`

    // Boolean
    IsActive bool `gorm:"type:boolean;default:true"`

    // ENUM (requires type creation)
    Status   string `gorm:"type:order_status;default:'pending'"`
}

Custom Types

// Custom type for PostgreSQL ENUM
type OrderStatus string

const (
    StatusPending   OrderStatus = "pending"
    StatusProcessing OrderStatus = "processing"
    StatusCompleted OrderStatus = "completed"
)

func (s *OrderStatus) Scan(value interface{}) error {
    *s = OrderStatus(value.(string))
    return nil
}

func (s OrderStatus) Value() (driver.Value, error) {
    return string(s), nil
}

Defining Indexes in GORM

type User struct {
    gorm.Model

    // Single column index
    Email    string `gorm:"index;not null"`

    // Unique index
    Username string `gorm:"uniqueIndex"`

    // Composite index
    FirstName string `gorm:"index:idx_name"`
    LastName  string `gorm:"index:idx_name"`

    // Index with options
    Phone    string `gorm:"index:,unique,sort:desc"`

    // Foreign key (auto-indexed by GORM)
    TeamID   uint `gorm:"index"`
}

// Custom index creation
db.Exec("CREATE INDEX CONCURRENTLY idx_users_email_lower ON users(LOWER(email))")

PostgreSQL Index Types

// B-Tree (default) - ranges, equality
type Product struct {
    Price decimal.Decimal `gorm:"index;type:decimal(10,2)"`
}

// GIN index for JSONB
type Document struct {
    Data json.RawMessage `gorm:"type:jsonb;index:,type:gin"`
}

// GiST for full-text search
db.Exec("CREATE INDEX idx_search ON products USING GiST(to_tsvector('english', name || ' ' || description))")

// Partial index
db.Exec("CREATE INDEX idx_active_users ON users(email) WHERE deleted_at IS NULL")

Query Optimization with GORM

// Use index hints
var users []User

// Efficient: uses index
db.Where("email = ?", "[email protected]").Find(&users)

// Efficient: composite index usage
db.Where("first_name = ? AND last_name = ?", "John", "Doe").Find(&users)

// Inefficient: full table scan
db.Where("LOWER(email) = ?", "[email protected]").Find(&users)

// Better: create functional index
db.Exec("CREATE INDEX idx_email_lower ON users(LOWER(email))")

Analyzing Query Performance

// Enable query logging
db = db.Debug()

// Explain query plan
var result []map[string]interface{}
db.Raw("EXPLAIN ANALYZE SELECT * FROM users WHERE email = ?", "[email protected]").Scan(&result)

// Check index usage
db.Raw(`
    SELECT schemaname, tablename, indexname, idx_scan
    FROM pg_stat_user_indexes
    WHERE tablename = 'users'
`).Scan(&result)

Index Best Practices for GORM

Primary Keys

// Auto-increment (default)
type User struct {
    ID uint `gorm:"primaryKey"` // BIGSERIAL in PostgreSQL
}

// UUID primary key
import "github.com/google/uuid"

type Document struct {
    ID uuid.UUID `gorm:"type:uuid;default:gen_random_uuid();primaryKey"`
}

// Composite primary key
type Enrollment struct {
    StudentID uint `gorm:"primaryKey"`
    CourseID  uint `gorm:"primaryKey"`
    Grade     string
}

Foreign Key Constraints

// Basic foreign key
type Order struct {
    gorm.Model
    UserID uint `gorm:"not null"`
    User   User `gorm:"foreignKey:UserID"`
}

// With ON DELETE behavior
type OrderItem struct {
    gorm.Model
    OrderID uint  `gorm:"not null"`
    Order   Order `gorm:"constraint:OnDelete:CASCADE,OnUpdate:CASCADE"`
}

// Optional relationship (nullable FK)
type Post struct {
    gorm.Model
    AuthorID *uint   // Nullable
    Author   *User   `gorm:"constraint:OnDelete:SET NULL"`
}

Unique Constraints

type Account struct {
    gorm.Model

    // Single column unique
    Email string `gorm:"uniqueIndex;not null"`

    // Composite unique
    Domain   string `gorm:"uniqueIndex:idx_domain_username"`
    Username string `gorm:"uniqueIndex:idx_domain_username"`
}

Check Constraints (PostgreSQL)

// Using table comment for check constraints
type Product struct {
    gorm.Model
    Price    decimal.Decimal `gorm:"type:decimal(10,2)"`
    Discount int
}

// Add check constraints via migration
db.Exec(`
    ALTER TABLE products
    ADD CONSTRAINT check_price CHECK (price >= 0),
    ADD CONSTRAINT check_discount CHECK (discount BETWEEN 0 AND 100)
`)

Custom Validation with Hooks

type User struct {
    gorm.Model
    Age   int
    Email string
}

// BeforeCreate hook for validation
func (u *User) BeforeCreate(tx *gorm.DB) error {
    if u.Age < 0 || u.Age > 150 {
        return errors.New("invalid age")
    }
    if !strings.Contains(u.Email, "@") {
        return errors.New("invalid email format")
    }
    return nil
}

One-to-Many

// Parent model
type Customer struct {
    gorm.Model
    Name   string
    Orders []Order `gorm:"foreignKey:CustomerID"`
}

// Child model
type Order struct {
    gorm.Model
    CustomerID uint
    Customer   Customer `gorm:"constraint:OnDelete:CASCADE"`
    Items      []OrderItem
}

// Usage with preloading
var customer Customer
db.Preload("Orders.Items").First(&customer, 1)

Many-to-Many

// Automatic junction table
type Student struct {
    gorm.Model
    Name    string
    Courses []Course `gorm:"many2many:enrollments;"`
}

type Course struct {
    gorm.Model
    Name     string
    Students []Student `gorm:"many2many:enrollments;"`
}

// Custom junction table with extra fields
type Enrollment struct {
    StudentID   uint      `gorm:"primaryKey"`
    CourseID    uint      `gorm:"primaryKey"`
    EnrolledAt  time.Time
    Grade       string
    CompletedAt *time.Time
}

// Override default junction table
func (Enrollment) TableName() string {
    return "student_courses"
}

Self-Referencing

type Category struct {
    gorm.Model
    Name       string
    ParentID   *uint
    Parent     *Category  `gorm:"foreignKey:ParentID"`
    Children   []Category `gorm:"foreignKey:ParentID"`
}

// Usage
var rootCategories []Category
db.Where("parent_id IS NULL").Preload("Children.Children").Find(&rootCategories)

Polymorphic Associations

// Polymorphic model
type Comment struct {
    gorm.Model
    Body            string
    CommentableID   uint
    CommentableType string
}

// Models that can have comments
type Post struct {
    gorm.Model
    Title    string
    Comments []Comment `gorm:"polymorphic:Commentable;"`
}

type Video struct {
    gorm.Model
    URL      string
    Comments []Comment `gorm:"polymorphic:Commentable;"`
}

// Usage
post := Post{Title: "Hello"}
db.Create(&post)

comment := Comment{
    Body:            "Great post!",
    CommentableID:   post.ID,
    CommentableType: "posts",
}
db.Create(&comment)

Has One

type User struct {
    gorm.Model
    Profile Profile
}

type Profile struct {
    gorm.Model
    UserID uint   `gorm:"uniqueIndex"`
    User   User
    Bio    string
}

Eager Loading Strategies

// Preload specific relations
db.Preload("Orders.Items.Product").Find(&customers)

// Conditional preloading
db.Preload("Orders", "status = ?", "pending").Find(&customers)

// Nested preloading with conditions
db.Preload("Orders", func(db *gorm.DB) *gorm.DB {
    return db.Order("created_at DESC").Limit(10)
}).Find(&customers)

GORM Hooks

type User struct {
    gorm.Model
    Email        string
    PasswordHash string
    LastLoginAt  *time.Time
}

// BeforeCreate - runs before INSERT
func (u *User) BeforeCreate(tx *gorm.DB) error {
    u.Email = strings.ToLower(strings.TrimSpace(u.Email))
    return nil
}

// AfterCreate - runs after INSERT
func (u *User) AfterCreate(tx *gorm.DB) error {
    // Send welcome email
    go sendWelcomeEmail(u.Email)
    return nil
}

// BeforeUpdate - runs before UPDATE
func (u *User) BeforeUpdate(tx *gorm.DB) error {
    if tx.Statement.Changed("Email") {
        u.Email = strings.ToLower(strings.TrimSpace(u.Email))
    }
    return nil
}

// BeforeSave - runs before both CREATE and UPDATE
func (u *User) BeforeSave(tx *gorm.DB) error {
    // Validation logic
    if u.Email == "" {
        return errors.New("email cannot be empty")
    }
    return nil
}

Scopes for Reusable Queries

// Define reusable query scopes
func Active(db *gorm.DB) *gorm.DB {
    return db.Where("deleted_at IS NULL AND is_active = ?", true)
}

func OrderByCreated(db *gorm.DB) *gorm.DB {
    return db.Order("created_at DESC")
}

func WithinDateRange(start, end time.Time) func(db *gorm.DB) *gorm.DB {
    return func(db *gorm.DB) *gorm.DB {
        return db.Where("created_at BETWEEN ? AND ?", start, end)
    }
}

// Usage
var users []User
db.Scopes(Active, OrderByCreated).Find(&users)
db.Scopes(WithinDateRange(startDate, endDate)).Find(&orders)

Transaction Management

// Manual transaction
tx := db.Begin()
defer func() {
    if r := recover(); r != nil {
        tx.Rollback()
    }
}()

if err := tx.Create(&order).Error; err != nil {
    tx.Rollback()
    return err
}

if err := tx.Create(&payment).Error; err != nil {
    tx.Rollback()
    return err
}

tx.Commit()

// Using Transaction function
err := db.Transaction(func(tx *gorm.DB) error {
    if err := tx.Create(&order).Error; err != nil {
        return err
    }
    if err := tx.Create(&payment).Error; err != nil {
        return err
    }
    return nil
})

Advanced Query Techniques

// Subqueries
var users []User
db.Where("id IN (?)",
    db.Table("orders").Select("user_id").Where("total > ?", 100),
).Find(&users)

// Raw SQL with scan
type Result struct {
    Date  time.Time
    Total float64
}

var results []Result
db.Raw(`
    SELECT DATE(created_at) as date, SUM(total) as total
    FROM orders
    WHERE created_at > ?
    GROUP BY DATE(created_at)
`, lastMonth).Scan(&results)

// Locking
db.Clauses(clause.Locking{Strength: "UPDATE"}).Find(&user, 1)

GORM AutoMigrate (Development)

// Simple auto-migration for development
func InitDB() (*gorm.DB, error) {
    db, err := gorm.Open(postgres.Open(dsn), &gorm.Config{})
    if err != nil {
        return nil, err
    }

    // AutoMigrate creates/updates tables
    err = db.AutoMigrate(
        &User{},
        &Order{},
        &Product{},
        &OrderItem{},
    )
    return db, err
}

// Custom migration with raw SQL
func CustomMigration(db *gorm.DB) error {
    return db.Exec(`
        CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_created
        ON orders(created_at DESC) WHERE deleted_at IS NULL
    `).Error
}

Production Migrations with golang-migrate

# Install golang-migrate
go install -tags 'postgres' github.com/golang-migrate/migrate/v4/cmd/migrate@latest

# Create migration files
migrate create -ext sql -dir migrations -seq create_users_table

-- migrations/000001_create_users_table.up.sql
CREATE TABLE IF NOT EXISTS users (
    id BIGSERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    deleted_at TIMESTAMPTZ
);

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_deleted_at ON users(deleted_at);

-- migrations/000001_create_users_table.down.sql
DROP TABLE IF EXISTS users;

Zero-Downtime Migration Strategy

// Step 1: Add nullable column
type User struct {
    gorm.Model
    Email    string
    Phone    *string `gorm:"type:varchar(20)"` // Nullable initially
}

// Step 2: Backfill data
func BackfillPhones(db *gorm.DB) error {
    return db.Model(&User{}).
        Where("phone IS NULL").
        Update("phone", "").Error
}

// Step 3: Make non-nullable
func MakePhoneRequired(db *gorm.DB) error {
    return db.Exec(`
        ALTER TABLE users
        ALTER COLUMN phone SET NOT NULL
    `).Error
}

Migration Management Code

package migrations

import (
    "database/sql"
    "embed"

    "github.com/golang-migrate/migrate/v4"
    "github.com/golang-migrate/migrate/v4/database/postgres"
    "github.com/golang-migrate/migrate/v4/source/iofs"
)

//go:embed *.sql
var fs embed.FS

func RunMigrations(db *sql.DB) error {
    driver, err := postgres.WithInstance(db, &postgres.Config{})
    if err != nil {
        return err
    }

    source, err := iofs.New(fs, ".")
    if err != nil {
        return err
    }

    m, err := migrate.NewWithInstance("iofs", source, "postgres", driver)
    if err != nil {
        return err
    }

    // Run migrations
    if err := m.Up(); err != nil && err != migrate.ErrNoChange {
        return err
    }

    return nil
}

Best Practices

Query Analysis in PostgreSQL

// Enable query logging in GORM
db = db.Debug()

// Analyze query performance
var explain []map[string]interface{}
db.Raw("EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM users WHERE email = ?", email).Scan(&explain)

// Check slow queries
db.Raw(`
    SELECT query, calls, mean_exec_time, max_exec_time
    FROM pg_stat_statements
    WHERE mean_exec_time > 100
    ORDER BY mean_exec_time DESC
    LIMIT 10
`).Scan(&slowQueries)

Solving N+1 Query Problem

// BAD: N+1 queries
var users []User
db.Find(&users)
for _, user := range users {
    var orders []Order
    db.Where("user_id = ?", user.ID).Find(&orders)
}

// GOOD: Eager loading with Preload
var users []User
db.Preload("Orders").Find(&users)

// BETTER: Selective preloading
db.Preload("Orders", "status = ?", "pending").
   Preload("Orders.Items").
   Find(&users)

// BEST: Joins for read-only data
var results []struct {
    UserName  string
    OrderID   uint
    Total     decimal.Decimal
}
db.Table("users").
    Select("users.name as user_name, orders.id as order_id, orders.total").
    Joins("LEFT JOIN orders ON orders.user_id = users.id").
    Scan(&results)

Connection Pool Optimization

sqlDB, err := db.DB()

// SetMaxIdleConns sets the maximum number of connections in the idle pool
sqlDB.SetMaxIdleConns(10)

// SetMaxOpenConns sets the maximum number of open connections
sqlDB.SetMaxOpenConns(100)

// SetConnMaxLifetime sets the maximum amount of time a connection may be reused
sqlDB.SetConnMaxLifetime(time.Hour)

// SetConnMaxIdleTime sets the maximum amount of time a connection may be idle
sqlDB.SetConnMaxIdleTime(10 * time.Minute)

Query Optimization Techniques

// 1. Select only needed columns
db.Select("id", "email", "name").Find(&users)

// 2. Use pagination
db.Offset(offset).Limit(pageSize).Find(&users)

// 3. Batch operations
db.CreateInBatches(users, 100)

// 4. Use prepared statements
stmt := db.Session(&gorm.Session{PrepareStmt: true})
stmt.Where("email = ?", email).Find(&user)

// 5. Disable default transaction for read queries
db.Session(&gorm.Session{SkipDefaultTransaction: true}).Find(&users)

PostgreSQL-Specific Optimizations

// Partial indexes for soft deletes
db.Exec(`
    CREATE INDEX idx_active_users
    ON users(email)
    WHERE deleted_at IS NULL
`)

// Use BRIN indexes for time-series data
db.Exec(`
    CREATE INDEX idx_orders_created_brin
    ON orders USING BRIN(created_at)
`)

// Table partitioning for large tables
db.Exec(`
    CREATE TABLE orders_2024 PARTITION OF orders
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01')
`)

// Materialized views for complex aggregations
db.Exec(`
    CREATE MATERIALIZED VIEW daily_sales AS
    SELECT DATE(created_at) as date,
           COUNT(*) as order_count,
           SUM(total) as total_sales
    FROM orders
    GROUP BY DATE(created_at)
`)

Caching Strategy

import "github.com/go-redis/redis/v8"

type CachedDB struct {
    db    *gorm.DB
    cache *redis.Client
}

func (c *CachedDB) GetUser(id uint) (*User, error) {
    key := fmt.Sprintf("user:%d", id)

    // Check cache
    val, err := c.cache.Get(ctx, key).Result()
    if err == nil {
        var user User
        json.Unmarshal([]byte(val), &user)
        return &user, nil
    }

    // Fetch from DB
    var user User
    if err := c.db.First(&user, id).Error; err != nil {
        return nil, err
    }

    // Cache for 1 hour
    data, _ := json.Marshal(user)
    c.cache.Set(ctx, key, data, time.Hour)

    return &user, nil
}

Extension Points

1. PostgreSQL Advanced Features: JSONB operations, full-text search, PostGIS for geospatial
2. Advanced Patterns: Time-series with TimescaleDB, event sourcing, CQRS, multi-tenancy with RLS
3. GORM Plugins: Soft delete, optimistic locking, sharding, tracing
4. Performance Monitoring: pg_stat_statements, pgBadger, GORM prometheus metrics
5. Testing Strategies: Testcontainers for Go, fixture management, transaction rollback tests