Working with Relational Databases

The gosoline framework provides three layered packages for working with relational databases, each building on top of the previous one:

Architecture Overview

sqlh (HTTP Layer)      → Exposes CRUD operations via HTTP endpoints
    ↑
sqlr (Repository Layer) → Provides ORM-like experience with entities
    ↑
sqlc (Client Layer)     → Core database client and query builder

sqlc - SQL Client

The sqlc package is the foundation layer providing direct access to relational databases. It handles connection management, query building, and execution.

When to use sqlc:

• You need fine-grained control over SQL queries
• Building custom queries with complex joins or aggregations
• Working with raw SQL or requiring maximum performance
• Implementing custom data access patterns

Key capabilities:

• Database connection management for MySQL and PostgreSQL
• Fluent query builder for SELECT, INSERT, UPDATE, DELETE operations
• Expression-based WHERE conditions with type-safe operators
• Transaction support with automatic rollback on error
• Prepared statement caching

sqlr - SQL Repository

The sqlr package provides an ORM-like experience built on top of sqlc. It uses Go generics to offer type-safe CRUD operations for entities.

When to use sqlr:

• You want type-safe entity operations without writing SQL
• Working with domain entities that map to database tables
• Need automatic handling of timestamps and auto-increment IDs
• Loading related entities through relationships

Key capabilities:

• Generic Repository pattern with Create, Read, Update, Delete, Query, and Count operations
• Entity definition with struct tags for schema mapping
• Relationship support: HasOne, HasMany, BelongsTo, ManyToMany
• Eager loading via Preload for related entities
• Join support for HasOne, HasMany, BelongsTo relations
• Row locking with ForUpdate() inside transactions
• Transaction-aware repository variant

sqlh - SQL HTTP Handlers

The sqlh package provides typed, transaction-aware CRUD handlers for SQLR entities. It integrates with the gosoline HTTP server.

When to use sqlh:

• Building REST APIs for entity management
• Need CRUD endpoints without repetitive transaction and repository code
• Need JSON Merge Patch, server-owned filters, or custom identity and delete behavior

Key capabilities:

• Typed CRUD definitions with create, update, patch, list, and delete operations
• Automatic transactions with commit and rollback behavior
• JSON Merge Patch with selective association synchronization
• Typed list results with shared query and count scopes
• Force filters for authorization and tenant restrictions
• Integration with gosoline HTTP server routing

Choosing the Right Layer

Need	Recommended Layer
Raw SQL execution, complex queries	sqlc
Entity CRUD with relationships	sqlr
REST API endpoints for entities	sqlh
Performance-critical operations	sqlc
Rapid development of CRUD APIs	sqlh + sqlr
Custom business logic	sqlr or sqlc

You can use multiple layers together within the same application—using sqlh for standard CRUD endpoints while leveraging sqlc directly for complex reporting queries.