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 full-featured visual database browser and editor embedded directly into your API at /studio.

Data Browser

Paginated grid with sorting, full-text search, column filtering, and relationship navigation

CRUD Operations

Create, edit, and delete records through modal forms. Bulk deletion support

Raw SQL Editor

Execute queries with read/write detection and DDL blocking for safety

Schema Export

Export schemas as SQL, JSON, YAML, DBML, or PNG/PDF entity-relationship diagrams

Data Export

Export data as JSON, CSV (ZIP), or SQL INSERT statements

Data Import

Import data from JSON, CSV, SQL, or Excel (.xlsx) files into existing tables

Schema Import

Import schemas from SQL, JSON, YAML, or DBML files to create tables

Go Model Generation

Generate Go model structs with proper GORM tags from your database schema

Enable or disable it in your .env:

.env

GORM_STUDIO_ENABLED=true

Configuration

GORM Studio is mounted in routes.go with all registered models. When you generate a new resource, the CLI automatically injects the model using the /* grit:studio */ marker.

internal/routes/routes.go

studio.Mount(router, db, []interface{}{
    &models.User{},
    &models.Post{},  // auto-injected by grit generate
    /* grit:studio */
}, studio.Config{
    Prefix:     "/studio",
    ReadOnly:   false,        // set true to disable mutations
    DisableSQL: false,        // set true to hide SQL editor
})

Security

GORM Studio includes built-in security: table name validation against registered models, parameterized queries, DDL blocking (DROP, ALTER, TRUNCATE, CREATE), CSV formula injection prevention, and SRI hashes for embedded assets.

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

Docker Setup Deployment