Database Integration

Fuego is database-agnostic and works with any Go database library. This guide covers the most popular approaches: GORM (ORM), sqlc (type-safe SQL), and the standard database/sql package.

Choosing a Database Approach

GORM

Full-featured ORM with migrations, associations, and query building

sqlc

Type-safe SQL that compiles queries to Go code

database/sql

Standard library for direct SQL control

Approach	Best For	Trade-offs
GORM	Rapid development, complex relationships	More abstraction, larger binary
sqlc	Performance-critical, complex queries	Requires SQL knowledge
database/sql	Simple apps, maximum control	More boilerplate

Project Structure

Organize your database code using the repository pattern:

GORM Integration

Installation

go get -u gorm.io/gorm
go get -u gorm.io/driver/postgres  # or sqlite, mysql, sqlserver

Database Connection

// internal/database/database.go
package database

import (
    "fmt"
    "log"
    "os"
    "time"
    
    "gorm.io/driver/postgres"
    "gorm.io/gorm"
    "gorm.io/gorm/logger"
)

var DB *gorm.DB

// Connect initializes the database connection.
func Connect() error {
    dsn := os.Getenv("DATABASE_URL")
    if dsn == "" {
        dsn = "host=localhost user=postgres password=postgres dbname=fuego_app port=5432 sslmode=disable"
    }
    
    // Configure GORM logger
    gormLogger := logger.New(
        log.New(os.Stdout, "\r\n", log.LstdFlags),
        logger.Config{
            SlowThreshold:             200 * time.Millisecond,
            LogLevel:                  logger.Info,
            IgnoreRecordNotFoundError: true,
            Colorful:                  true,
        },
    )
    
    var err error
    DB, err = gorm.Open(postgres.Open(dsn), &gorm.Config{
        Logger: gormLogger,
    })
    if err != nil {
        return fmt.Errorf("failed to connect to database: %w", err)
    }
    
    // Configure connection pool
    sqlDB, err := DB.DB()
    if err != nil {
        return err
    }
    
    sqlDB.SetMaxIdleConns(10)
    sqlDB.SetMaxOpenConns(100)
    sqlDB.SetConnMaxLifetime(time.Hour)
    
    return nil
}

// Close closes the database connection.
func Close() error {
    sqlDB, err := DB.DB()
    if err != nil {
        return err
    }
    return sqlDB.Close()
}

Models

// internal/models/user.go
package models

import (
    "time"
    
    "gorm.io/gorm"
)

type User struct {
    ID        uint           `gorm:"primaryKey" json:"id"`
    Email     string         `gorm:"uniqueIndex;not null" json:"email"`
    Name      string         `gorm:"not null" json:"name"`
    Password  string         `gorm:"not null" json:"-"` // Excluded from JSON
    Role      string         `gorm:"default:user" json:"role"`
    Active    bool           `gorm:"default:true" json:"active"`
    CreatedAt time.Time      `json:"created_at"`
    UpdatedAt time.Time      `json:"updated_at"`
    DeletedAt gorm.DeletedAt `gorm:"index" json:"-"`
    
    // Associations
    Tasks []Task `gorm:"foreignKey:UserID" json:"tasks,omitempty"`
}

// TableName overrides the default table name.
func (User) TableName() string {
    return "users"
}

// internal/models/task.go
package models

import (
    "time"
    
    "gorm.io/gorm"
)

type Task struct {
    ID          uint           `gorm:"primaryKey" json:"id"`
    Title       string         `gorm:"not null" json:"title"`
    Description string         `json:"description"`
    Completed   bool           `gorm:"default:false" json:"completed"`
    Priority    int            `gorm:"default:0" json:"priority"`
    DueDate     *time.Time     `json:"due_date,omitempty"`
    UserID      uint           `gorm:"not null;index" json:"user_id"`
    CreatedAt   time.Time      `json:"created_at"`
    UpdatedAt   time.Time      `json:"updated_at"`
    DeletedAt   gorm.DeletedAt `gorm:"index" json:"-"`
    
    // Associations
    User User `gorm:"foreignKey:UserID" json:"user,omitempty"`
}

Migrations

// internal/database/migrations.go
package database

import (
    "myapp/internal/models"
)

// Migrate runs database migrations.
func Migrate() error {
    return DB.AutoMigrate(
        &models.User{},
        &models.Task{},
    )
}

Repository Pattern

// internal/repository/user_repository.go
package repository

import (
    "errors"
    
    "myapp/internal/database"
    "myapp/internal/models"
    "gorm.io/gorm"
)

type UserRepository struct{}

var Users = &UserRepository{}

// FindAll returns all users with optional pagination.
func (r *UserRepository) FindAll(page, pageSize int) ([]models.User, int64, error) {
    var users []models.User
    var total int64
    
    db := database.DB.Model(&models.User{})
    db.Count(&total)
    
    offset := (page - 1) * pageSize
    err := db.Offset(offset).Limit(pageSize).Find(&users).Error
    
    return users, total, err
}

// FindByID returns a user by ID.
func (r *UserRepository) FindByID(id uint) (*models.User, error) {
    var user models.User
    err := database.DB.First(&user, id).Error
    if errors.Is(err, gorm.ErrRecordNotFound) {
        return nil, nil
    }
    return &user, err
}

// FindByEmail returns a user by email.
func (r *UserRepository) FindByEmail(email string) (*models.User, error) {
    var user models.User
    err := database.DB.Where("email = ?", email).First(&user).Error
    if errors.Is(err, gorm.ErrRecordNotFound) {
        return nil, nil
    }
    return &user, err
}

// Create creates a new user.
func (r *UserRepository) Create(user *models.User) error {
    return database.DB.Create(user).Error
}

// Update updates an existing user.
func (r *UserRepository) Update(user *models.User) error {
    return database.DB.Save(user).Error
}

// Delete soft-deletes a user.
func (r *UserRepository) Delete(id uint) error {
    return database.DB.Delete(&models.User{}, id).Error
}

// FindWithTasks returns a user with their tasks.
func (r *UserRepository) FindWithTasks(id uint) (*models.User, error) {
    var user models.User
    err := database.DB.Preload("Tasks").First(&user, id).Error
    if errors.Is(err, gorm.ErrRecordNotFound) {
        return nil, nil
    }
    return &user, err
}

Using in Route Handlers

// app/api/users/route.go
package users

import (
    "myapp/internal/models"
    "myapp/internal/repository"
    "github.com/abdul-hamid-achik/fuego/pkg/fuego"
)

// GET /api/users - List all users with pagination
func Get(c *fuego.Context) error {
    page := c.QueryInt("page", 1)
    pageSize := c.QueryInt("page_size", 20)
    
    users, total, err := repository.Users.FindAll(page, pageSize)
    if err != nil {
        return c.JSON(500, map[string]string{"error": "failed to fetch users"})
    }
    
    return c.JSON(200, map[string]any{
        "users":     users,
        "total":     total,
        "page":      page,
        "page_size": pageSize,
    })
}

// POST /api/users - Create a new user
func Post(c *fuego.Context) error {
    var input struct {
        Email    string `json:"email"`
        Name     string `json:"name"`
        Password string `json:"password"`
    }
    
    if err := c.Bind(&input); err != nil {
        return c.JSON(400, map[string]string{"error": "invalid request body"})
    }
    
    // Check if email already exists
    existing, _ := repository.Users.FindByEmail(input.Email)
    if existing != nil {
        return c.JSON(409, map[string]string{"error": "email already exists"})
    }
    
    // Hash password (use bcrypt in production)
    hashedPassword := hashPassword(input.Password)
    
    user := &models.User{
        Email:    input.Email,
        Name:     input.Name,
        Password: hashedPassword,
    }
    
    if err := repository.Users.Create(user); err != nil {
        return c.JSON(500, map[string]string{"error": "failed to create user"})
    }
    
    return c.JSON(201, user)
}

// app/api/users/[id]/route.go
package users

import (
    "myapp/internal/repository"
    "github.com/abdul-hamid-achik/fuego/pkg/fuego"
)

// GET /api/users/:id
func Get(c *fuego.Context) error {
    id := c.ParamInt("id", 0)
    if id == 0 {
        return c.JSON(400, map[string]string{"error": "invalid user id"})
    }
    
    user, err := repository.Users.FindByID(uint(id))
    if err != nil {
        return c.JSON(500, map[string]string{"error": "failed to fetch user"})
    }
    if user == nil {
        return c.JSON(404, map[string]string{"error": "user not found"})
    }
    
    return c.JSON(200, user)
}

// PUT /api/users/:id
func Put(c *fuego.Context) error {
    id := c.ParamInt("id", 0)
    
    user, err := repository.Users.FindByID(uint(id))
    if err != nil || user == nil {
        return c.JSON(404, map[string]string{"error": "user not found"})
    }
    
    var input struct {
        Name   string `json:"name"`
        Active *bool  `json:"active"`
    }
    
    if err := c.Bind(&input); err != nil {
        return c.JSON(400, map[string]string{"error": "invalid request body"})
    }
    
    if input.Name != "" {
        user.Name = input.Name
    }
    if input.Active != nil {
        user.Active = *input.Active
    }
    
    if err := repository.Users.Update(user); err != nil {
        return c.JSON(500, map[string]string{"error": "failed to update user"})
    }
    
    return c.JSON(200, user)
}

// DELETE /api/users/:id
func Delete(c *fuego.Context) error {
    id := c.ParamInt("id", 0)
    
    if err := repository.Users.Delete(uint(id)); err != nil {
        return c.JSON(500, map[string]string{"error": "failed to delete user"})
    }
    
    return c.NoContent()
}

sqlc Integration

sqlc generates type-safe Go code from SQL queries - great for performance-critical applications.

Installation

go install github.com/sqlc-dev/sqlc/cmd/sqlc@latest

Configuration

# sqlc.yaml
version: "2"
sql:
  - engine: "postgresql"
    queries: "internal/database/queries/"
    schema: "internal/database/schema/"
    gen:
      go:
        package: "db"
        out: "internal/database/db"
        sql_package: "pgx/v5"
        emit_json_tags: true
        emit_prepared_queries: true
        emit_interface: true

Schema

-- internal/database/schema/001_users.sql
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    name VARCHAR(255) NOT NULL,
    password VARCHAR(255) NOT NULL,
    role VARCHAR(50) DEFAULT 'user',
    active BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE tasks (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    completed BOOLEAN DEFAULT false,
    priority INTEGER DEFAULT 0,
    due_date TIMESTAMP,
    user_id INTEGER NOT NULL REFERENCES users(id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_tasks_user_id ON tasks(user_id);

Queries

-- internal/database/queries/users.sql

-- name: GetUser :one
SELECT * FROM users WHERE id = $1 LIMIT 1;

-- name: GetUserByEmail :one
SELECT * FROM users WHERE email = $1 LIMIT 1;

-- name: ListUsers :many
SELECT * FROM users
ORDER BY created_at DESC
LIMIT $1 OFFSET $2;

-- name: CountUsers :one
SELECT COUNT(*) FROM users;

-- name: CreateUser :one
INSERT INTO users (email, name, password, role)
VALUES ($1, $2, $3, $4)
RETURNING *;

-- name: UpdateUser :one
UPDATE users
SET name = COALESCE($2, name),
    active = COALESCE($3, active),
    updated_at = CURRENT_TIMESTAMP
WHERE id = $1
RETURNING *;

-- name: DeleteUser :exec
DELETE FROM users WHERE id = $1;

-- name: GetUserWithTasks :many
SELECT 
    u.*,
    t.id as task_id,
    t.title as task_title,
    t.completed as task_completed
FROM users u
LEFT JOIN tasks t ON t.user_id = u.id
WHERE u.id = $1;

Generate Code

sqlc generate

Using sqlc in Handlers

// internal/database/database.go
package database

import (
    "context"
    "os"
    
    "github.com/jackc/pgx/v5/pgxpool"
    "myapp/internal/database/db"
)

var Pool *pgxpool.Pool
var Queries *db.Queries

func Connect(ctx context.Context) error {
    var err error
    Pool, err = pgxpool.New(ctx, os.Getenv("DATABASE_URL"))
    if err != nil {
        return err
    }
    
    Queries = db.New(Pool)
    return nil
}

// app/api/users/route.go
package users

import (
    "myapp/internal/database"
    "myapp/internal/database/db"
    "github.com/abdul-hamid-achik/fuego/pkg/fuego"
)

// GET /api/users
func Get(c *fuego.Context) error {
    page := c.QueryInt("page", 1)
    pageSize := int32(c.QueryInt("page_size", 20))
    offset := int32((page - 1)) * pageSize
    
    users, err := database.Queries.ListUsers(c.Context(), db.ListUsersParams{
        Limit:  pageSize,
        Offset: offset,
    })
    if err != nil {
        return c.JSON(500, map[string]string{"error": "failed to fetch users"})
    }
    
    count, _ := database.Queries.CountUsers(c.Context())
    
    return c.JSON(200, map[string]any{
        "users": users,
        "total": count,
        "page":  page,
    })
}

// POST /api/users
func Post(c *fuego.Context) error {
    var input struct {
        Email    string `json:"email"`
        Name     string `json:"name"`
        Password string `json:"password"`
    }
    
    if err := c.Bind(&input); err != nil {
        return c.JSON(400, map[string]string{"error": "invalid request body"})
    }
    
    user, err := database.Queries.CreateUser(c.Context(), db.CreateUserParams{
        Email:    input.Email,
        Name:     input.Name,
        Password: hashPassword(input.Password),
        Role:     "user",
    })
    if err != nil {
        return c.JSON(500, map[string]string{"error": "failed to create user"})
    }
    
    return c.JSON(201, user)
}

Transactions

GORM Transactions

// internal/repository/user_repository.go
func (r *UserRepository) CreateWithTasks(user *models.User, tasks []models.Task) error {
    return database.DB.Transaction(func(tx *gorm.DB) error {
        if err := tx.Create(user).Error; err != nil {
            return err
        }
        
        for i := range tasks {
            tasks[i].UserID = user.ID
        }
        
        if err := tx.Create(&tasks).Error; err != nil {
            return err
        }
        
        return nil
    })
}

sqlc Transactions

// internal/database/tx.go
func CreateUserWithTasks(ctx context.Context, user db.CreateUserParams, tasks []db.CreateTaskParams) error {
    tx, err := Pool.Begin(ctx)
    if err != nil {
        return err
    }
    defer tx.Rollback(ctx)
    
    qtx := Queries.WithTx(tx)
    
    createdUser, err := qtx.CreateUser(ctx, user)
    if err != nil {
        return err
    }
    
    for _, task := range tasks {
        task.UserID = createdUser.ID
        if _, err := qtx.CreateTask(ctx, task); err != nil {
            return err
        }
    }
    
    return tx.Commit(ctx)
}

Connection Middleware

Inject database connection into context:

// app/api/middleware.go
package api

import (
    "myapp/internal/database"
    "github.com/abdul-hamid-achik/fuego/pkg/fuego"
)

func Middleware() fuego.MiddlewareFunc {
    return func(next fuego.HandlerFunc) fuego.HandlerFunc {
        return func(c *fuego.Context) error {
            // Add database connection to context
            c.Set("db", database.DB)
            c.Set("queries", database.Queries)
            
            return next(c)
        }
    }
}

Testing with Database

// internal/repository/user_repository_test.go
package repository

import (
    "testing"
    
    "myapp/internal/database"
    "myapp/internal/models"
    "gorm.io/driver/sqlite"
    "gorm.io/gorm"
)

func setupTestDB(t *testing.T) {
    var err error
    database.DB, err = gorm.Open(sqlite.Open(":memory:"), &gorm.Config{})
    if err != nil {
        t.Fatal(err)
    }
    
    database.Migrate()
}

func TestUserRepository_Create(t *testing.T) {
    setupTestDB(t)
    
    user := &models.User{
        Email:    "[email protected]",
        Name:     "Test User",
        Password: "hashed",
    }
    
    err := Users.Create(user)
    if err != nil {
        t.Fatalf("failed to create user: %v", err)
    }
    
    if user.ID == 0 {
        t.Error("expected user ID to be set")
    }
}

func TestUserRepository_FindByEmail(t *testing.T) {
    setupTestDB(t)
    
    // Create test user
    user := &models.User{
        Email:    "[email protected]",
        Name:     "Find User",
        Password: "hashed",
    }
    Users.Create(user)
    
    // Find by email
    found, err := Users.FindByEmail("[email protected]")
    if err != nil {
        t.Fatalf("failed to find user: %v", err)
    }
    
    if found == nil {
        t.Fatal("expected to find user")
    }
    
    if found.Name != "Find User" {
        t.Errorf("expected name 'Find User', got '%s'", found.Name)
    }
}

Application Setup

// main.go
package main

import (
    "context"
    "log"
    "os"
    "os/signal"
    "syscall"
    
    "myapp/internal/database"
    "github.com/abdul-hamid-achik/fuego/pkg/fuego"
)

func main() {
    // Connect to database
    if err := database.Connect(); err != nil {
        log.Fatal("failed to connect to database:", err)
    }
    defer database.Close()
    
    // Run migrations
    if err := database.Migrate(); err != nil {
        log.Fatal("failed to run migrations:", err)
    }
    
    // Create Fuego app
    app := fuego.New()
    
    // Graceful shutdown
    ctx, cancel := signal.NotifyContext(context.Background(), 
        os.Interrupt, syscall.SIGTERM)
    defer cancel()
    
    go func() {
        <-ctx.Done()
        log.Println("shutting down...")
        database.Close()
        os.Exit(0)
    }()
    
    // Start server
    if err := app.Start(":8080"); err != nil {
        log.Fatal(err)
    }
}

Database Recommendations

PostgreSQL

Best for: Production applications, complex queries, full-text search, JSON data.

go get gorm.io/driver/postgres
# or
go get github.com/jackc/pgx/v5

SQLite

Best for: Development, testing, embedded applications, single-user apps.

go get gorm.io/driver/sqlite
# or  
go get modernc.org/sqlite

MySQL/MariaDB

Best for: Web applications, legacy systems, read-heavy workloads.

go get gorm.io/driver/mysql

For production applications, always use connection pooling, enable SSL/TLS, and run migrations as a separate step in your deployment pipeline.

Next Steps

Authentication

Store users and sessions securely

Examples

Complete working examples

Middleware

Add database middleware

Error Handling

Handle database errors gracefully

Getting Started

Core Concepts

Frontend

Advanced

Guides

Database Integration

Choosing a Database Approach

GORM

sqlc

database/sql

Project Structure

GORM Integration

Installation

Database Connection

Models

Migrations

Repository Pattern

Using in Route Handlers

sqlc Integration

Installation

Configuration

Schema

Queries

Generate Code

Using sqlc in Handlers

Transactions

GORM Transactions

sqlc Transactions

Connection Middleware

Testing with Database

Application Setup

Database Recommendations

Next Steps

Authentication

Examples

Middleware

Error Handling

Getting Started

Core Concepts

Frontend

Advanced

Guides

​Choosing a Database Approach

GORM

sqlc

database/sql

​Project Structure

​GORM Integration

​Installation

​Database Connection

​Models

​Migrations

​Repository Pattern

​Using in Route Handlers

​sqlc Integration

​Installation

​Configuration

​Schema

​Queries

​Generate Code

​Using sqlc in Handlers

​Transactions

​GORM Transactions

​sqlc Transactions

​Connection Middleware

​Testing with Database

​Application Setup

​Database Recommendations

​Next Steps

Authentication

Examples

Middleware

Error Handling

Choosing a Database Approach

Project Structure

GORM Integration

Installation

Database Connection

Models

Migrations

Repository Pattern

Using in Route Handlers

sqlc Integration

Installation

Configuration

Schema

Queries

Generate Code

Using sqlc in Handlers

Transactions

GORM Transactions

sqlc Transactions

Connection Middleware

Testing with Database

Application Setup

Database Recommendations

Next Steps