Infrastructure

Database & Migrations

Grit uses GORM as its ORM and PostgreSQL as the primary database. Define your models as Go structs and run migrations with a dedicated command.

PostgreSQL Setup

PostgreSQL runs via Docker Compose. After starting the containers, your database is ready at localhost:5432.

terminal

$ docker compose up -d postgres

Connection String

The database connection is configured via the DATABASE_URL environment variable in your .env file. The format follows the standard PostgreSQL connection string:

.env

DATABASE_URL=postgres://grit:grit@localhost:5432/myapp?sslmode=disable

The URL format breakdown:

Part	Value	Description
protocol	postgres://	PostgreSQL driver
user:password	grit:grit	Auth credentials (change in production)
host:port	localhost:5432	Database server address
database	myapp	Database name (matches your project name)
sslmode	disable	Use "require" in production

GORM Database Connection

Grit generates a database connection module at apps/api/internal/database/database.go. It opens a GORM connection with PostgreSQL and configures connection pooling:

apps/api/internal/database/database.go

package database

import (
    "fmt"
    "log"

    "gorm.io/driver/postgres"
    "gorm.io/gorm"
    "gorm.io/gorm/logger"
)

// Connect establishes a database connection using the provided DSN.
func Connect(dsn string) (*gorm.DB, error) {
    db, err := gorm.Open(postgres.Open(dsn), &gorm.Config{
        Logger: logger.Default.LogMode(logger.Info),
    })
    if err != nil {
        return nil, fmt.Errorf("failed to connect to database: %w", err)
    }

    sqlDB, err := db.DB()
    if err != nil {
        return nil, fmt.Errorf("failed to get underlying sql.DB: %w", err)
    }

    // Connection pool settings
    sqlDB.SetMaxIdleConns(10)
    sqlDB.SetMaxOpenConns(100)

    log.Println("Database connected successfully")
    return db, nil
}

Connection Pooling

GORM uses Go's built-in database/sql connection pool under the hood. Grit configures these defaults:

Setting	Default	Description
MaxIdleConns	10	Maximum idle connections kept open
MaxOpenConns	100	Maximum total open connections

You can also set ConnMaxLifetime and ConnMaxIdleTime for long-running production applications. See the Go database/sql docs for details.

Migrations

Grit uses a smart migration system that only creates tables which don't exist yet. Migrations run as a separate command before starting the server:

terminal

$ cd apps/api && go run cmd/migrate/main.go

For full details on how migrations work, fresh migrations, foreign key ordering, and the typical workflow, see the Migrations guide.

Defining Models

Models live in apps/api/internal/models/. Each model is a Go struct with GORM tags that define the database schema. Here is the User model that ships with every Grit project:

apps/api/internal/models/user.go

package models

import (
    "time"

    "golang.org/x/crypto/bcrypt"
    "gorm.io/gorm"
)

const (
    RoleAdmin  = "admin"
    RoleEditor = "editor"
    RoleUser   = "user"
)

type User struct {
    ID              uint           `gorm:"primarykey" json:"id"`
    Name            string         `gorm:"size:255;not null" json:"name" binding:"required"`
    Email           string         `gorm:"size:255;uniqueIndex;not null" json:"email" binding:"required,email"`
    Password        string         `gorm:"size:255;not null" json:"-"`
    Role            string         `gorm:"size:20;default:user" json:"role"`
    Avatar          string         `gorm:"size:500" json:"avatar"`
    Active          bool           `gorm:"default:true" json:"active"`
    EmailVerifiedAt *time.Time     `json:"email_verified_at"`
    CreatedAt       time.Time      `json:"created_at"`
    UpdatedAt       time.Time      `json:"updated_at"`
    DeletedAt       gorm.DeletedAt `gorm:"index" json:"-"`
}

// BeforeCreate hashes the password before saving.
func (u *User) BeforeCreate(tx *gorm.DB) error {
    if u.Password != "" {
        hashedPassword, err := bcrypt.GenerateFromPassword(
            []byte(u.Password), bcrypt.DefaultCost,
        )
        if err != nil {
            return err
        }
        u.Password = string(hashedPassword)
    }
    return nil
}

GORM Struct Tags

Common GORM struct tags used in Grit models:

Tag	Example	Effect
primarykey	gorm:"primarykey"	Marks as primary key
size	gorm:"size:255"	Sets VARCHAR length
not null	gorm:"not null"	Adds NOT NULL constraint
uniqueIndex	gorm:"uniqueIndex"	Creates unique index
index	gorm:"index"	Creates regular index
default	gorm:"default:user"	Sets default column value
type	gorm:"type:text"	Sets explicit column type
foreignKey	gorm:"foreignKey:UserID"	Defines foreign key relationship

Common GORM Operations

These are the most common database operations you will use in your Grit handlers and services. GORM provides a fluent, chainable API.

Create

Create a record

user := models.User{
    Name:  "John Doe",
    Email: "john@example.com",
    Password: "secret123",
}

result := db.Create(&user)
if result.Error != nil {
    return fmt.Errorf("creating user: %w", result.Error)
}
// user.ID is now populated

Find (Single Record)

Find by ID or condition

// Find by primary key
var user models.User
db.First(&user, 1) // SELECT * FROM users WHERE id = 1

// Find by condition
db.Where("email = ?", "john@example.com").First(&user)

// Check if record exists
if errors.Is(result.Error, gorm.ErrRecordNotFound) {
    // User not found
}

Find (Multiple Records)

Query lists with pagination

var users []models.User

// All records
db.Find(&users)

// With conditions
db.Where("active = ?", true).Find(&users)

// With pagination
db.Offset(0).Limit(20).Order("created_at DESC").Find(&users)

// Count total for pagination
var count int64
db.Model(&models.User{}).Where("active = ?", true).Count(&count)

Update

Update records

// Update single field
db.Model(&user).Update("name", "Jane Doe")

// Update multiple fields
db.Model(&user).Updates(models.User{
    Name: "Jane Doe",
    Role: "admin",
})

// Update with map (includes zero-value fields)
db.Model(&user).Updates(map[string]interface{}{
    "active": false,
    "name":   "Jane Doe",
})

Delete

Delete records

// Soft delete (sets deleted_at, record still in DB)
db.Delete(&user, 1)

// Hard delete (permanently removes from DB)
db.Unscoped().Delete(&user, 1)

// Delete by condition
db.Where("active = ? AND created_at < ?", false, cutoffDate).Delete(&models.User{})

Preload (Relationships)

Eager-load relationships

// Define a Post model with relationship
type Post struct {
    ID        uint           `gorm:"primarykey" json:"id"`
    Title     string         `gorm:"size:255;not null" json:"title"`
    Body      string         `gorm:"type:text" json:"body"`
    UserID    uint           `json:"user_id"`
    User      User           `json:"user"`
    CreatedAt time.Time      `json:"created_at"`
}

// Preload the User relationship
var posts []Post
db.Preload("User").Find(&posts)

// Nested preload
db.Preload("User").Preload("Comments").Find(&posts)

Indexing

Proper indexing is critical for query performance. GORM creates indexes from struct tags during AutoMigrate:

Index examples

type Product struct {
    ID       uint   `gorm:"primarykey"`
    Name     string `gorm:"size:255;index"`           // Regular index
    SKU      string `gorm:"size:100;uniqueIndex"`     // Unique index
    Category string `gorm:"size:100;index:idx_cat_price"` // Composite index
    Price    float64 `gorm:"index:idx_cat_price"`     // Same composite index
    DeletedAt gorm.DeletedAt `gorm:"index"`           // Soft delete index
}

Add indexes to columns you frequently filter, sort, or join on. The DeletedAt field should always have an index since GORM adds a WHERE deleted_at IS NULL condition to every query on soft-deletable models.

SQLite for Quick Testing

If you want to prototype without Docker or PostgreSQL, GORM supports SQLite as a drop-in replacement. Add the SQLite driver and swap the connection:

SQLite connection

import (
    "gorm.io/driver/sqlite"
    "gorm.io/gorm"
)

func ConnectSQLite(dbPath string) (*gorm.DB, error) {
    db, err := gorm.Open(sqlite.Open(dbPath), &gorm.Config{})
    if err != nil {
        return nil, fmt.Errorf("failed to connect to SQLite: %w", err)
    }
    return db, nil
}

// Usage:
// db, err := ConnectSQLite("test.db")     // file-based
// db, err := ConnectSQLite(":memory:")     // in-memory (tests)

Note: SQLite is great for prototyping and unit tests, but always test with PostgreSQL before deploying. Some PostgreSQL-specific features (like JSONB columns, array types, and certain index types) are not available in SQLite.

GORM Studio

Every Grit project includes GORM Studio — a visual database browser embedded directly into your API at /studio. Browse tables, view records, inspect relationships, and run queries without leaving your browser.

Enable or disable it in your .env:

.env

GORM_STUDIO_ENABLED=true

Access GORM Studio at http://localhost:8080/studio when the API is running. Disable it in production by setting the variable to false.

Docker Setup Deployment