sqlc

package module
v0.3.0 Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: Feb 24, 2026 License: MIT Imports: 19 Imported by: 0

README

sqlc - High Performance Golang ORM Framework

A modern ORM framework based on Go Generics + Code Generation + Squirrel SQL Builder.

Core Features

  • ✅ Generics Driven - Type-safe API using Go 1.18+ generics, zero runtime reflection overhead.
  • ✅ Code Generation - Auto-generate model metadata, only ~50 lines of code per model.
  • ✅ SQL Builder - Integrated Squirrel for SQL construction, auto-adapts to MySQL/PostgreSQL/SQLite.
  • ✅ Fluent Query API - Chainable query builder.
  • ✅ Transaction Support - Auto-commit/rollback transaction management.
  • ✅ Lifecycle Hooks - Model hooks like BeforeCreate/AfterCreate.
  • ✅ JSON Support - Rich JSON operations including querying, updating, and deep merging (RFC 7396).
  • ✅ Observability - Built-in support for slog logging and OpenTelemetry tracing.
  • ✅ High Performance - High-performance ORM designed for speed and efficiency.

Quick Start

Installation
go get github.com/arllen133/sqlc
Define Model
package models

import "time"

type User struct {
    ID        int64     `db:"id,primaryKey,autoIncrement"`
    Username  string    `db:"username,size:100,unique"`
    Email     string    `db:"email,size:255"`
    CreatedAt time.Time `db:"created_at"`
}
Generate Code
go install github.com/arllen133/sqlc/cmd/sqlcli@latest
sqlcli -i ./models

This generates models/generated/user_gen.go, containing:

  • generated.User - Schema instance with type-safe field definitions.
  • generated.UserMetadata - JSON path accessors (if JSON fields exist).
Declarative Configuration (Optional)

Create a config.go file in your model directory to customize code generation:

// models/config.go
package models

import "github.com/arllen133/sqlc/gen"

var _ = gen.Config{
    OutPath:        "../generated",              // Output directory (relative to model dir)
    IncludeStructs: []any{"User", Post{}},       // Supports strings and type literals
    ExcludeStructs: []any{BaseModel{}, "Draft"}, // Skip these structs
}

When sqlcli runs, it automatically detects and applies this configuration.

Usage
package main

import (
    "context"
    "database/sql"
    "log/slog"
    "time"

    _ "github.com/mattn/go-sqlite3"
    "github.com/arllen133/sqlc"
    "yourapp/models"
    "yourapp/models/generated" // Import generated code
)

func main() {
    // 1. Connect to Database with Observability
    db, _ := sql.Open("sqlite3", "app.db")
    session := sqlc.NewSession(db, &sqlc.SQLiteDialect{},
        sqlc.WithLogger(slog.Default()), // Enable logging
        sqlc.WithDefaultTracer(),        // Enable OpenTelemetry tracing
    )

    // 2. Create Repository
    userRepo := sqlc.NewRepository[models.User](session)
    ctx := context.Background()

    // 3. Create Record
    user := &models.User{
        Username: "alice",
        Email:    "alice@example.com",
    }
    userRepo.Create(ctx, user)
    // user.ID is auto-filled

    // 4. Type-Safe Query
    users, _ := userRepo.Query().
        Where(generated.User.Username.Eq("alice")).
        OrderBy(generated.User.CreatedAt.Asc()).
        Limit(10).
        Find(ctx)

    // 5. Update
    user.Email = "new@example.com"
    userRepo.Update(ctx, user)

    // 6. Delete
    userRepo.Delete(ctx, user.ID)
}

Advanced Features

Transactions
err := session.Transaction(ctx, func(txSession *sqlc.Session) error {
    txRepo := sqlc.NewRepository[models.User](txSession)

    user1 := &models.User{Username: "user1"}
    if err := txRepo.Create(ctx, user1); err != nil {
        return err // Auto-rollback
    }

    return nil // Auto-commit
})
JSON Operations

Rich support for JSON columns with dialect-specific optimizations (MySQL, PostgreSQL, SQLite).

Define JSON Field
type Metadata struct {
    Tags []string `json:"tags"`
    Info struct {
        Age int `json:"age"`
    } `json:"info"`
}

type Post struct {
    ID   int64                 `db:"id"`
    Meta sqlc.JSON[Metadata]   `db:"meta,type:json"`
}
JSON Querying
import "yourapp/models/generated"

// Query by JSON path
repo.Query().
    Where(generated.Metadata.Age.Gt(18)). // Metadata is generated from Metadata struct name
    Where(generated.Metadata.Tags.Contains("golang")).
    Find(ctx)
JSON Updates
// Partial update (JSON_SET)
repo.UpdateColumns(ctx, id,
    generated.Metadata.Age.Set(25),
)

// Remove field (JSON_REMOVE)
repo.UpdateColumns(ctx, id,
    generated.Metadata.Info.Remove(), // Assuming Info itself can be removed or a field inside it
    // Or for a custom path:
    field.JSONRemove(generated.Post.Meta, "$.deprecated_field"),
)
JSON Merge (RFC 7396)

Efficiently merge JSON objects using DB-native functions (JSON_MERGE_PATCH in MySQL, || in Postgres, json_patch in SQLite).

// Merge Patch
repo.UpdateColumns(ctx, id,
    generated.Post.Meta.MergePatch(map[string]any{
        "view_count": 100,
        "tags": []string{"updated"},
    }),
)
Relations & Eager Loading

Support for defining and eagerly loading relationships (HasOne, HasMany, BelongsTo) to avoid N+1 query problems.

Define Relations

Use the relation tag on your struct fields. These fields are typically not stored in the database column matching the field name, so use db:"-".

type User struct {
    ID    int64   `db:"id,primaryKey,autoIncrement"`
    Posts []*Post `db:"-" relation:"hasMany,foreignKey:user_id"`
}

type Post struct {
    ID     int64 `db:"id,primaryKey"`
    UserID int64 `db:"user_id"`
    // BelongsTo: FK (user_id) is on the Post struct
    Author *User `db:"-" relation:"belongsTo,foreignKey:user_id"`
}
Generate Code

Running sqlcli will automatically generate relation metadata in your *_gen.go files, e.g., generated.User_Posts.

sqlcli -i ./models
Eager Loading (Preload)

Use WithPreload to load relationships efficiently (usually via logical IN queries).

// Load Users with their Posts
users, _ := userRepo.Query().
    WithPreload(sqlc.Preload(generated.User_Posts)).
    Find(ctx)

for _, u := range users {
    fmt.Printf("User %d has %d posts\n", u.ID, len(u.Posts))
}

// Load Posts with their Author
posts, _ := postRepo.Query().
    WithPreload(sqlc.Preload(generated.Post_Author)).
    Find(ctx)
Observability
Logging

Built-in support for log/slog.

sess := sqlc.NewSession(db, dialect,
    sqlc.WithLogger(slog.Default()),
    sqlc.WithSlowQueryThreshold(200*time.Millisecond), // Alert on slow queries
    sqlc.WithQueryLogging(true),                       // Log all queries (debug)
)
Tracing

Built-in integration with OpenTelemetry.

import "go.opentelemetry.io/otel"

sess := sqlc.NewSession(db, dialect,
    sqlc.WithTracer(otel.Tracer("my-service")),
)

Spans include attributes like db.statement, db.system, and db.table.

Fluent Expressions
models.UserFields.Username.Eq("alice")      // username = 'alice'
models.UserFields.Age.Ne(18)                // age != 18
models.UserFields.Age.Gt(18)                // age > 18
models.UserFields.Age.Gte(18)               // age >= 18
models.UserFields.Age.Lt(30)                // age < 30
models.UserFields.Status.In("a", "b")       // status IN ('a', 'b')
models.UserFields.Username.Like("%alice%")  // username LIKE '%alice%'
models.UserFields.Email.IsNull()            // email IS NULL
Joins and Aggregations
// JOIN
repo.Query().
    Join("departments", clause.Expr{SQL: "users.dept_id = departments.id"}).
    Find(ctx)

// Aggregation
count, _ := repo.Query().
    Where(models.UserFields.Status.Eq("active")).
    Count(ctx)
Upsert

Support INSERT ... ON CONFLICT/DUPLICATE KEY UPDATE across databases.

// Default: Update non-PK columns on conflict
repo.Upsert(ctx, user)

// Custom: Specific conflict target and update columns
repo.Upsert(ctx, user,
    sqlc.OnConflict(models.UserFields.Email),
    sqlc.DoUpdate(models.UserFields.Username),
)

Database Support

  • ✅ SQLite (Modern JSON support)
  • ✅ MySQL (5.7+, 8.0+)
  • ✅ PostgreSQL (JSONB support)

License

MIT License

Documentation

Overview

Package sqlc provides a type-safe ORM library using generics and code generation. This file implements database dialect abstraction to handle SQL differences between databases.

Dialect is the key abstraction for sqlc to support multiple databases, responsible for:

  • Database identification (MySQL, PostgreSQL, SQLite)
  • Placeholder format (? vs $1, $2)
  • Upsert syntax (ON DUPLICATE KEY vs ON CONFLICT)

Currently supported databases:

  • MySQL 5.7+
  • PostgreSQL 12+
  • SQLite 3.24+ (with UPSERT support)

Usage example: 

// MySQL
session := sqlc.NewSession(db, sqlc.MySQLDialect{})

// PostgreSQL
session := sqlc.NewSession(db, sqlc.PostgreSQLDialect{})

// SQLite
session := sqlc.NewSession(db, sqlc.SQLiteDialect{})

Package sqlc provides a type-safe ORM library using generics and code generation. This file implements the model lifecycle hooks mechanism.

Lifecycle hooks allow executing custom logic at critical moments of database operations, such as:

  • Data validation: Validate data integrity before saving
  • Auto fields: Automatically set timestamps like created_at, updated_at
  • Data transformation: Transform data format before saving or after loading
  • Audit logging: Record data change history
  • Cache management: Clear related caches after changes
  • Cascade operations: Automatically handle related data

Hook execution order:

  • Create: BeforeCreate → INSERT → AfterCreate
  • Update: BeforeUpdate → UPDATE → AfterUpdate
  • Delete: BeforeDelete → DELETE → AfterDelete

Usage example: 

type User struct {
    ID        int64     `db:"id,primaryKey"`
    Email     string    `db:"email"`
    Name      string    `db:"name"`
    CreatedAt time.Time `db:"created_at"`
    UpdatedAt time.Time `db:"updated_at"`
}

// Implement BeforeCreate hook
func (u *User) BeforeCreate(ctx context.Context) error {
    // Validate email format
    if !strings.Contains(u.Email, "@") {
        return errors.New("invalid email format")
    }
    // Set creation time
    u.CreatedAt = time.Now()
    u.UpdatedAt = time.Now()
    return nil
}

// Implement BeforeUpdate hook
func (u *User) BeforeUpdate(ctx context.Context) error {
    // Auto-update modification time
    u.UpdatedAt = time.Now()
    return nil
}

Package sqlc provides a type-safe ORM library using generics and code generation. This file implements JOIN query helper functions and subquery expressions.

JOIN is a core feature of relational databases for associating data across multiple tables. sqlc provides type-safe JOIN support, including:

  • INNER JOIN: Inner join, returns only matching records
  • LEFT JOIN: Left join, returns all records from left table and matching records from right table
  • RIGHT JOIN: Right join, returns all records from right table and matching records from left table

Usage example: 

// INNER JOIN
users, err := userRepo.Query().
    Join(generated.OrderSchema{},
        sqlc.On(generated.User.ID, generated.Order.UserID),
    ).
    Find(ctx)

// LEFT JOIN with alias
users, err := userRepo.Query().
    LeftJoinAs(generated.ProfileSchema{}, "p",
        sqlc.On(generated.User.ID, clause.Column{Name: "user_id", Table: "p"}),
    ).
    Find(ctx)

// EXISTS subquery
activeUsers, err := userRepo.Query().
    Where(sqlc.Exists(
        orderRepo.Query().
            Select(generated.Order.ID).
            Where(clause.Eq{
                Column: generated.Order.UserID,
                Value:  generated.User.ID.Column(),
            }),
    )).
    Find(ctx)

Package sqlc provides a type-safe ORM library using generics and code generation. This file implements observability functionality, including logging, tracing, and metrics.

Observability is a critical component of database operations in production environments. sqlc provides comprehensive observability support:

  • Structured logging: Records all SQL queries, execution time, error messages
  • Distributed tracing: Traces database operations via OpenTelemetry
  • Performance metrics: Collects query count, latency distribution, error rate, etc.
  • Slow query detection: Automatically identifies and logs slow queries

Usage example: 

// Basic logging configuration
session := sqlc.NewSession(db, sqlc.MySQL{},
    sqlc.WithLogger(slog.Default()),
    sqlc.WithQueryLogging(true),
)

// Complete observability configuration
session := sqlc.NewSession(db, sqlc.PostgreSQL{},
    sqlc.WithLogger(slog.Default()),
    sqlc.WithDefaultTracer(),
    sqlc.WithDefaultMeter(),
    sqlc.WithSlowQueryThreshold(100*time.Millisecond),
    sqlc.WithQueryLogging(true),
)

Observability data usage:

  • Performance analysis: Identify slow queries and performance bottlenecks
  • Troubleshooting: Locate issues through trace chains
  • Capacity planning: Analyze database load through metrics
  • Security audit: Record all database operations

Package sqlc provides a type-safe ORM library using generics and code generation. This file implements the QueryBuilder type, providing fluent SQL query building functionality.

QueryBuilder is the query core of sqlc ORM, providing type-safe chainable query API. It supports all common SQL query operations, including:

  • Conditional filtering (WHERE)
  • Sorting (ORDER BY)
  • Pagination (LIMIT/OFFSET)
  • Column selection (SELECT)
  • Deduplication (DISTINCT)
  • Grouping (GROUP BY/HAVING)
  • Joining (JOIN)
  • Aggregation (COUNT)
  • Preloading (Preload)
  • Subqueries (Subquery)

Usage examples: 

// Basic query
users, err := userRepo.Query().Find(ctx)

// Conditional query
activeUsers, err := userRepo.Query().
    Where(generated.User.Status.Eq("active")).
    Where(generated.User.Age.Gt(18)).
    Find(ctx)

// Sorting and pagination
users, err := userRepo.Query().
    OrderBy(generated.User.CreatedAt.Desc()).
    Limit(10).
    Offset(20).
    Find(ctx)

// Join query
users, err := userRepo.Query().
    Join(generated.OrderSchema{},
        sqlc.On(generated.User.ID, generated.Order.UserID),
    ).
    Find(ctx)

// Aggregation query
count, err := userRepo.Query().
    Where(generated.User.Status.Eq("active")).
    Count(ctx)

Design principles:

  • Immutability: Most methods return new QueryBuilder instances
  • Type safety: Leverages generics for compile-time type checking
  • Composability: Build complex queries through method chaining
  • Automation: Automatically handles soft delete, column selection, etc.

Package sqlc provides aggregate function support for QueryBuilder. This file implements common SQL aggregate functions like SUM, AVG, MIN, MAX.

Aggregate functions operate on a set of values and return a single summary value. They are commonly used with GROUP BY clauses for data analysis and reporting.

Usage examples: 

// Calculate total revenue
total, err := orderRepo.Query().Sum(ctx, generated.Order.Total)

// Calculate average age of active users
avgAge, err := userRepo.Query().
    Where(generated.User.Status.Eq("active")).
    Avg(ctx, generated.User.Age)

// Find minimum and maximum prices
minPrice, err := productRepo.Query().Min(ctx, generated.Product.Price)
maxPrice, err := productRepo.Query().Max(ctx, generated.Product.Price)

Design considerations:

  • All aggregate functions respect WHERE conditions and soft delete filters
  • LIMIT and OFFSET are ignored in aggregate calculations
  • NULL values are excluded from calculations (standard SQL behavior)
  • Functions return appropriate Go types based on the aggregate operation

Package sqlc provides a type-safe ORM library using generics and code generation. This file implements model relationship definitions and eager loading functionality.

Relationships are one of the core features of ORM, allowing automatic loading of associated models when querying the main model. sqlc supports two main types:

  • HasOne: One-to-one relationship (e.g., user has one profile)
  • HasMany: One-to-many relationship (e.g., user has many posts)

Relationship implementation:

  1. Define Relation struct describing foreign key and local key mappings
  2. Use Preload() function to create preload executor
  3. Automatically execute associated queries after main query
  4. Populate associated data into main model

Usage example: 

// Define User and Post models
type User struct {
    ID    int64   `db:"id,primaryKey"`
    Name  string  `db:"name"`
    Posts []*Post `db:"-"` // Relationship field, not mapped to database column
}

type Post struct {
    ID     int64  `db:"id,primaryKey"`
    UserID int64  `db:"user_id"` // Foreign key
    Title  string `db:"title"`
}

// Define relationship (usually generated by code generator)
userHasManyPosts := sqlc.HasMany[User, Post](
    clause.Column{Name: "user_id"},           // Foreign key (in posts table)
    clause.Column{Name: "id"},                // Local key (in users table)
    func(u *User, posts []*Post) { u.Posts = posts },
    func(u *User) any { return u.ID },
)

// Query with preload
users, err := userRepo.Query().
    WithPreload(sqlc.Preload(userHasManyPosts)).
    Find(ctx)
// users[0].Posts is automatically loaded

Performance optimization:

  • Uses IN query to batch load associated data, avoiding N+1 problem
  • Supports nested preloading (preloaded associations can preload their associations)
  • Automatically initializes empty slices to avoid nil pointers

Package sqlc provides a type-safe ORM library using generics and code generation. This file implements the Repository type, providing complete CRUD operation support.

Repository is the core component of sqlc ORM, providing type-safe database operations for model T. It encapsulates all common database operations, including:

  • Create (Create, BatchCreate, Upsert)
  • Read (FindOne, Query)
  • Update (Update, UpdateColumns)
  • Delete (Delete, DeleteModel, SoftDelete, ForceDelete)
  • Soft delete support (SoftDelete, Restore)
  • Conditional scoping (Where)

Package sqlc provides a type-safe ORM library using generics and code generation. This file implements the model-to-database table mapping mechanism, which is the core abstraction of sqlc ORM.

The Schema interface defines how to map Go structs to database tables, including:

  • Table name mapping
  • Column selection
  • Insert data extraction
  • Update data mapping
  • Primary key management
  • Soft delete support

Package sqlc provides a type-safe ORM library using generics and code generation. This file implements database session management, including connection management, transaction handling, and observability integration.

Package sqlc provides a type-safe ORM library using generics and code generation. This file provides utility functions to support the core functionality of the ORM.

Utility functions include:

  • ResolveColumnNames: Extract column names from Columnar interface slice

These functions are infrastructure for internal ORM implementation and are typically not called directly by external code.

Index

Constants

This section is empty.

Variables

View Source 
var (
	SQLite     = SQLiteDialect{}
	MySQL      = MySQLDialect{}
	PostgreSQL = PostgreSQLDialect{}
)
View Source 
var ErrNotFound = errors.New("sqlc: record not found")

ErrNotFound indicates that no record was found. Returned when Take(), First(), Last(), FindOne() and other methods find no matching record.

Usage example: 

user, err := userRepo.FindOne(ctx, 123)
if err != nil {
    if errors.Is(err, sqlc.ErrNotFound) {
        // User not found
        return nil
    }
    // Other error
    return err
}

Functions

func Exists added in v0.2.0 

func Exists(expr clause.Expression) clause.Expression

Exists creates an EXISTS subquery expression. Used to check if a subquery returns any records.

EXISTS syntax: 

WHERE EXISTS (SELECT ... FROM ... WHERE ...)

Parameters:

  • expr: Subquery expression (usually QueryBuilder)

Returns:

  • clause.Expression: EXISTS expression

Use cases:

  • Check if related records exist
  • Filter main records with/without related records
  • Implement complex business rules

Usage example: 

// Query users who have orders
usersWithOrders, err := userRepo.Query().
    Where(sqlc.Exists(
        orderRepo.Query().
            Select(generated.Order.ID).
            Where(clause.Eq{
                Column: generated.Order.UserID,
                Value:  generated.User.ID.Column(),
            }),
    )).
    Find(ctx)

// Query users who have pending orders
usersWithPendingOrders, err := userRepo.Query().
    Where(sqlc.Exists(
        orderRepo.Query().
            Select(generated.Order.ID).
            Where(clause.And{
                clause.Eq{Column: generated.Order.UserID, Value: generated.User.ID.Column()},
                clause.Eq{Column: generated.Order.Status, Value: "pending"},
            }),
    )).
    Find(ctx)

Performance notes:

  • EXISTS stops scanning after finding the first matching record
  • Usually more efficient than IN or JOIN (for existence checks)
  • Database optimizer automatically chooses the best execution plan

func NotExists added in v0.2.0 

func NotExists(expr clause.Expression) clause.Expression

NotExists creates a NOT EXISTS subquery expression. Used to check if a subquery returns no records.

NOT EXISTS syntax: 

WHERE NOT EXISTS (SELECT ... FROM ... WHERE ...)

Parameters:

  • expr: Subquery expression (usually QueryBuilder)

Returns:

  • clause.Expression: NOT EXISTS expression

Use cases:

  • Check if related records don't exist
  • Filter main records without related records
  • Implement exclusive business rules

Usage example: 

// Query users who have no orders
usersWithoutOrders, err := userRepo.Query().
    Where(sqlc.NotExists(
        orderRepo.Query().
            Select(generated.Order.ID).
            Where(clause.Eq{
                Column: generated.Order.UserID,
                Value:  generated.User.ID.Column(),
            }),
    )).
    Find(ctx)

// Query users who have no unread messages
usersWithNoUnread, err := userRepo.Query().
    Where(sqlc.NotExists(
        messageRepo.Query().
            Select(generated.Message.ID).
            Where(clause.And{
                clause.Eq{Column: generated.Message.UserID, Value: generated.User.ID.Column()},
                clause.Eq{Column: generated.Message.Read, Value: false},
            }),
    )).
    Find(ctx)

Performance notes:

  • NOT EXISTS must scan entire subquery result
  • For large tables, may be slower than LEFT JOIN + IS NULL
  • Ensure appropriate indexes exist on subquery

func Preload added in v0.2.0 

func Preload[P, C any](rel Relation[P, C]) preloadExecutor[P]

Preload creates a preload executor for given relationship. This is a standalone function returning a function to be added to QueryBuilder.

Type parameters:

  • P: Parent model type
  • C: Child model type

Parameters:

  • rel: Relationship definition

Returns:

  • preloadExecutor[P]: Preload executor function

Preload flow:

  1. Collect local key values from all parent models
  2. Build IN query to batch load all associated child models
  3. Group child models by foreign key value
  4. Iterate parent models and set corresponding child models
  5. For HasMany, initialize empty slices

Performance advantages:

  • Avoids N+1 query problem
  • Uses single IN query to load all associated data
  • Performs data matching in memory, highly efficient

Usage example: 

// Single preload
users, err := userRepo.Query().
    WithPreload(sqlc.Preload(userHasManyPosts)).
    Find(ctx)

// Multiple preloads
users, err := userRepo.Query().
    WithPreload(sqlc.Preload(userHasManyPosts)).
    WithPreload(sqlc.Preload(userHasOneProfile)).
    Find(ctx)

// Nested preload (posts' comments)
users, err := userRepo.Query().
    WithPreload(sqlc.Preload(userHasManyPosts)).
    WithPreload(sqlc.Preload(postHasManyComments)).
    Find(ctx)

func RegisterSchema 

func RegisterSchema[T any](schema Schema[T])

RegisterSchema registers a Schema implementation for a model. Usually called during program initialization (e.g., in init() functions).

Parameters:

  • schema: Schema implementation for the model

Type parameter:

  • T: Model type

Note:

  • Each type can only be registered once, duplicate registrations will overwrite
  • All registrations should be completed at program startup
  • After registration, can be retrieved via LoadSchema[T]()

Example: 

func init() {
    sqlc.RegisterSchema[models.User](generated.UserSchema{})
    sqlc.RegisterSchema[models.Order](generated.OrderSchema{})
}

func ResolveColumnNames 

func ResolveColumnNames(args []clause.Columnar) []string

ResolveColumnNames extracts column names from a slice of types implementing clause.Columnar. This is a general-purpose column name resolution function for converting type-safe field references to string column names.

Parameters:

  • args: Slice of objects implementing clause.Columnar interface

Returns:

  • []string: Slice of column names, returns nil if input is empty

Usage scenarios:

  • QueryBuilder.Select(): Resolve selected columns
  • QueryBuilder.GroupBy(): Resolve group-by columns
  • Repository.Upsert(): Resolve conflict columns and update columns

clause.Columnar interface:

  • Any type implementing ColumnName() string method can be used as parameter
  • Common implementations: field.String, field.Number[T], clause.Column

Example: 

// Extract column names from field references
columns := ResolveColumnNames([]clause.Columnar{
    generated.User.ID,       // field.Number[int64]
    generated.User.Email,    // field.String
    generated.User.Name,     // field.String
})
// columns = ["id", "email", "name"]

// Use in Select
query.Select(generated.User.ID, generated.User.Email)
// Internally calls: ResolveColumnNames([]clause.Columnar{...})

// Use in GroupBy
query.GroupBy(generated.User.Status)
// Internally calls: ResolveColumnNames([]clause.Columnar{...})

Performance considerations:

  • Pre-allocates result slice to avoid multiple expansions
  • Returns nil quickly for empty input, avoiding empty slice creation

Types

type AfterCreateInterface 

type AfterCreateInterface interface {
	AfterCreate(context.Context) error
}

AfterCreateInterface defines the hook interface for after creation. If a model implements this interface, Create() and BatchCreate() methods will call AfterCreate() after successful insertion.

Use cases:

  • Audit logging: Record creation operations to log system
  • Cache management: Warm up cache, send cache invalidation notifications
  • Notifications: Send welcome emails, notify administrators
  • Cascade creation: Create related records (use with caution)
  • Search indexing: Update search engine index

Notes:

  • Executes within transaction, if it fails the transaction will rollback
  • If error is returned, the entire creation operation will rollback
  • Should not execute database operations that might cause recursion
  • For BatchCreate, called for each model

Example: 

type Order struct {
    ID      int64 `db:"id,primaryKey"`
    UserID  int64 `db:"user_id"`
    Amount  int64 `db:"amount"`
    Status  string `db:"status"`
}

func (o *Order) AfterCreate(ctx context.Context) error {
    // Send order creation notification
    notification := &Notification{
        UserID:  o.UserID,
        Type:    "order_created",
        Message: fmt.Sprintf("Order #%d has been created", o.ID),
    }
    // Note: Need to access Repository via context or use global service
    return notificationService.Send(ctx, notification)
}

type AfterDeleteInterface 

type AfterDeleteInterface interface {
	AfterDelete(context.Context) error
}

AfterDeleteInterface defines the hook interface for after delete. If a model implements this interface, DeleteModel() method will call AfterDelete() after successful deletion.

Use cases:

  • Audit logging: Record deletion operations
  • Cache management: Clear related caches
  • Notifications: Notify relevant parties of data deletion
  • Search indexing: Remove index from search engine
  • Statistics update: Update counters

Notes:

  • Executes within transaction, if it fails the transaction will rollback
  • If error is returned, the entire deletion operation will rollback
  • Not triggered for Delete() (no model instance)
  • For SoftDelete(), use SoftDeleteModel
  • Database record is already deleted at this point, cannot query anymore

Example: 

type Document struct {
    ID      int64  `db:"id,primaryKey"`
    Title   string `db:"title"`
    OwnerID int64  `db:"owner_id"`
}

func (d *Document) AfterDelete(ctx context.Context) error {
    // Remove index from search engine
    searchService.DeleteDocument(ctx, d.ID)

    // Clear related caches
    cache.Delete(fmt.Sprintf("document:%d", d.ID))
    cache.Delete(fmt.Sprintf("user:%d:documents", d.OwnerID))

    // Record audit log
    auditLog := &AuditLog{
        Action:    "delete",
        Entity:    "document",
        EntityID:  d.ID,
        Timestamp: time.Now(),
    }
    return auditLogRepo.Create(ctx, auditLog)
}

type AfterUpdateInterface 

type AfterUpdateInterface interface {
	AfterUpdate(context.Context) error
}

AfterUpdateInterface defines the hook interface for after update. If a model implements this interface, Update() method will call AfterUpdate() after successful update.

Use cases:

  • Audit logging: Record data changes
  • Cache management: Clear related caches
  • Notifications: Notify relevant parties of data changes
  • Search indexing: Update search engine index
  • Status change handling: Handle state machine transition side effects

Notes:

  • Executes within transaction, if it fails the transaction will rollback
  • If error is returned, the entire update operation will rollback
  • Should not execute database operations that might cause recursion
  • Not triggered for UpdateColumns() (no complete model instance)

Example: 

type Product struct {
    ID          int64   `db:"id,primaryKey"`
    Name        string  `db:"name"`
    Price       float64 `db:"price"`
    Stock       int     `db:"stock"`
}

func (p *Product) AfterUpdate(ctx context.Context) error {
    // Clear product cache
    cache.Delete(fmt.Sprintf("product:%d", p.ID))

    // If price changed, record price history
    // Note: Need to get original price from context or other means
    priceHistory := &PriceHistory{
        ProductID: p.ID,
        Price:     p.Price,
        ChangedAt: time.Now(),
    }
    return priceHistoryRepo.Create(ctx, priceHistory)
}

type BeforeCreateInterface 

type BeforeCreateInterface interface {
	BeforeCreate(context.Context) error
}

BeforeCreateInterface defines the hook interface for before creation. If a model implements this interface, Create() and BatchCreate() methods will call BeforeCreate() before insertion.

Use cases:

  • Data validation: Validate required fields, format validation, business rule checks
  • Auto fields: Set created_at, generate UUIDs, calculate default values
  • Data transformation: Encrypt passwords, normalize data formats
  • Auditing: Record creation operations

Notes:

  • If error is returned, creation operation will be aborted
  • Should not execute potentially failing network operations (e.g., API calls)
  • Should not execute database operations that might cause recursion
  • Should execute quickly to avoid blocking

Example: 

type User struct {
    ID           int64      `db:"id,primaryKey"`
    Email        string     `db:"email"`
    PasswordHash string     `db:"password_hash"`
    CreatedAt    time.Time  `db:"created_at"`
}

func (u *User) BeforeCreate(ctx context.Context) error {
    // Validate email
    if u.Email == "" {
        return errors.New("email is required")
    }

    // Encrypt password (should use bcrypt in production)
    if u.PasswordHash != "" {
        hashed, err := bcrypt.GenerateFromPassword([]byte(u.PasswordHash), bcrypt.DefaultCost)
        if err != nil {
            return err
        }
        u.PasswordHash = string(hashed)
    }

    // Set creation time
    u.CreatedAt = time.Now()

    return nil
}

type BeforeDeleteInterface 

type BeforeDeleteInterface interface {
	BeforeDelete(context.Context) error
}

BeforeDeleteInterface defines the hook interface for before delete. If a model implements this interface, DeleteModel() method will call BeforeDelete() before deletion.

Use cases:

  • Data validation: Check if deletion is allowed (e.g., if order is completed)
  • Cascade deletion: Delete related data
  • Archiving: Archive data before deletion
  • Auditing: Record deletion operations
  • Cleanup: Clean up related resources (files, caches, etc.)

Notes:

  • If error is returned, deletion operation will be aborted
  • Not triggered for Delete() (no model instance)
  • Not triggered for SoftDelete() (no model instance)
  • Should not execute database operations that might cause recursion

Example: 

type User struct {
    ID       int64  `db:"id,primaryKey"`
    Email    string `db:"email"`
    AvatarURL string `db:"avatar_url"`
}

func (u *User) BeforeDelete(ctx context.Context) error {
    // Check if user has pending orders
    count, err := orderRepo.Query().
        Where(generated.Order.UserID.Eq(u.ID)).
        Where(generated.Order.Status.Ne("completed")).
        Count(ctx)
    if err != nil {
        return err
    }
    if count > 0 {
        return errors.New("cannot delete user with pending orders")
    }

    // Delete user avatar file
    if u.AvatarURL != "" {
        storage.Delete(u.AvatarURL)
    }

    return nil
}

Cascade deletion example: 

type Order struct {
    ID int64 `db:"id,primaryKey"`
}

func (o *Order) BeforeDelete(ctx context.Context) error {
    // Delete all order items
    orderItems, err := orderItemRepo.Query().
        Where(generated.OrderItem.OrderID.Eq(o.ID)).
        Find(ctx)
    if err != nil {
        return err
    }

    for _, item := range orderItems {
        if err := orderItemRepo.DeleteModel(ctx, item); err != nil {
            return err
        }
    }

    return nil
}

type BeforeUpdateInterface 

type BeforeUpdateInterface interface {
	BeforeUpdate(context.Context) error
}

BeforeUpdateInterface defines the hook interface for before update. If a model implements this interface, Update() method will call BeforeUpdate() before updating.

Use cases:

  • Data validation: Validate update data legality
  • Auto fields: Update updated_at, record modifier
  • Change tracking: Record which fields were modified
  • Condition checking: Ensure business rules (e.g., order status transitions)
  • Data transformation: Encrypt sensitive fields

Notes:

  • If error is returned, update operation will be aborted
  • Should not execute database operations that might cause recursion
  • Not triggered for UpdateColumns() (no complete model instance)

Example: 

type User struct {
    ID        int64     `db:"id,primaryKey"`
    Email     string    `db:"email"`
    Name      string    `db:"name"`
    UpdatedAt time.Time `db:"updated_at"`
}

func (u *User) BeforeUpdate(ctx context.Context) error {
    // Auto-update modification time
    u.UpdatedAt = time.Now()

    // Validate email format
    if u.Email != "" && !strings.Contains(u.Email, "@") {
        return errors.New("invalid email format")
    }

    return nil
}

Order status transition example: 

type Order struct {
    ID     int64 `db:"id,primaryKey"`
    Status string `db:"status"`
}

func (o *Order) BeforeUpdate(ctx context.Context) error {
    // Load original order from database
    original, err := orderRepo.FindOne(ctx, o.ID)
    if err != nil {
        return err
    }

    // Validate status transition
    validTransitions := map[string][]string{
        "pending":   {"paid", "cancelled"},
        "paid":      {"shipped", "refunded"},
        "shipped":   {"delivered", "returned"},
        "delivered": {"returned"},
    }

    allowed, ok := validTransitions[original.Status]
    if !ok {
        return fmt.Errorf("invalid current status: %s", original.Status)
    }

    for _, s := range allowed {
        if s == o.Status {
            return nil // Valid status transition
        }
    }

    return fmt.Errorf("cannot transition from %s to %s", original.Status, o.Status)
}

type Dialect 

type Dialect interface {
	// Name returns the database type name.
	// Used for logging, metrics collection, and driver selection.
	//
	// Returns:
	//   - "mysql" for MySQL
	//   - "postgres" for PostgreSQL
	//   - "sqlite3" for SQLite
	Name() string

	// PlaceholderFormat returns the placeholder format used by the database.
	// Squirrel uses this format to generate parameterized queries.
	//
	// Common formats:
	//   - sq.Question: ? placeholder (MySQL, SQLite)
	//   - sq.Dollar: $1, $2 placeholders (PostgreSQL)
	PlaceholderFormat() sq.PlaceholderFormat

	// UpsertClause generates the SQL clause for insert or update (Upsert).
	// This handles database-specific syntax for "update if exists, insert if not".
	//
	// Parameters:
	//   - tableName: Table name
	//   - conflictCols: Conflict detection columns (unique constraint or primary key)
	//   - updateCols: Columns to update when conflict occurs
	//
	// Returns:
	//   - string: Complete Upsert clause (e.g., "ON CONFLICT ... DO UPDATE SET ...")
	//
	// Example output:
	//   MySQL: "ON DUPLICATE KEY UPDATE name=VALUES(name), email=VALUES(email)"
	//   PostgreSQL: "ON CONFLICT (email) DO UPDATE SET name=EXCLUDED.name"
	//   SQLite: "ON CONFLICT (email) DO UPDATE SET name=excluded.name"
	UpsertClause(tableName string, conflictCols []string, updateCols []string) string
}

Dialect abstracts database-specific SQL features. Different databases have SQL syntax differences, and the Dialect interface provides a unified abstraction layer.

Main differences handled:

  • Placeholder format: MySQL/SQLite use ?, PostgreSQL uses $1, $2
  • Upsert syntax: MySQL uses ON DUPLICATE KEY UPDATE, PostgreSQL/SQLite use ON CONFLICT
  • Other database-specific features (future expansion)

Implementations:

  • MySQLDialect: MySQL dialect
  • PostgreSQLDialect: PostgreSQL dialect
  • SQLiteDialect: SQLite dialect

type Executor 

type Executor interface {
	// QueryContext executes a query and returns multiple rows
	QueryContext(ctx context.Context, query string, args ...any) (*sql.Rows, error)
	// ExecContext executes a write operation (INSERT/UPDATE/DELETE) and returns affected rows
	ExecContext(ctx context.Context, query string, args ...any) (sql.Result, error)
	// QueryRowContext executes a query expecting a single row result
	QueryRowContext(ctx context.Context, query string, args ...any) *sql.Row
	// SelectContext executes a query and scans results into a struct slice
	SelectContext(ctx context.Context, dest any, query string, args ...any) error
	// GetContext executes a query and scans a single row result into a struct
	GetContext(ctx context.Context, dest any, query string, args ...any) error
}

Executor defines the common database operations for both DB and Tx. This interface is implemented by both *sqlx.DB and *sqlx.Tx, allowing Session to seamlessly switch between regular queries and transactions without modifying calling code.

Implementations:

  • *sqlx.DB: for regular database operations
  • *sqlx.Tx: for transactional database operations

type JSON 

type JSON[T any] struct {
	Data T
}

JSON is a generic wrapper for handling JSON fields in database. It implements sql.Scanner and driver.Valuer.

Usage: 

type User struct {
    Metadata sqlc.JSON[UserMetadata] `db:"metadata"`
}

// Access data
user.Metadata.Data.Initial = "V"

func NewJSON 

func NewJSON[T any](v T) JSON[T]

NewJSON creates a new JSON wrapper for the given value.

func (JSON[T]) MarshalJSON 

func (j JSON[T]) MarshalJSON() ([]byte, error)

MarshalJSON implements json.Marshaler.

func (*JSON[T]) Scan 

func (j *JSON[T]) Scan(value any) error

Scan implements the sql.Scanner interface.

func (*JSON[T]) UnmarshalJSON 

func (j *JSON[T]) UnmarshalJSON(data []byte) error

UnmarshalJSON implements json.Unmarshaler.

func (JSON[T]) Value 

func (j JSON[T]) Value() (driver.Value, error)

Value implements the driver.Valuer interface.

type JoinOn added in v0.2.0 

type JoinOn struct {
	// Left is the column on the left side of JOIN condition
	Left clause.Column

	// Right is the column on the right side of JOIN condition
	Right clause.Column
}

JoinOn defines the column correspondence in JOIN conditions. Used to specify the ON clause of JOIN, connecting columns from left and right tables.

Field descriptions:

  • Left: Column from left table (usually main table or already joined table)
  • Right: Column from right table (table to be joined)

Usage example: 

// users.id = orders.user_id
joinOn := JoinOn{
    Left:  clause.Column{Name: "id", Table: "users"},
    Right: clause.Column{Name: "user_id", Table: "orders"},
}

Note:

  • Usually created using On() function instead of direct construction
  • Supports multiple JoinOn conditions (connected with AND)

func On added in v0.2.0 

func On(left, right interface{ Column() clause.Column }) JoinOn

On creates a JOIN condition. This is the recommended way to build JOIN ON clauses.

Parameters:

  • left: Column from left table (must implement Column() method, e.g., field.Field)
  • right: Column from right table (must implement Column() method)

Returns:

  • JoinOn: JOIN condition

Usage example: 

// Basic usage
joinOn := sqlc.On(generated.User.ID, generated.Order.UserID)

// Use in JOIN
users, err := userRepo.Query().
    Join(generated.OrderSchema{},
        sqlc.On(generated.User.ID, generated.Order.UserID),
    ).
    Find(ctx)

// Multiple conditions (connected with AND)
users, err := userRepo.Query().
    Join(generated.OrderSchema{},
        sqlc.On(generated.User.ID, generated.Order.UserID),
        sqlc.On(generated.User.TenantID, generated.Order.TenantID),
    ).
    Find(ctx)

Type constraints:

  • left and right must implement Column() clause.Column method
  • Usually generated field.Field types

type Metrics 

type Metrics struct {
	// QueryCount records the total number of SQL queries executed.
	// Grouped by operation type (select, insert, update, delete) and database type.
	//
	// Metric attributes:
	//   - db.operation: Operation type (select, exec, query, etc.)
	//   - db.system: Database type (mysql, postgres, sqlite3)
	//
	// Usage:
	//   - Monitor query throughput
	//   - Analyze operation type distribution
	//   - Capacity planning and performance optimization
	QueryCount metric.Int64Counter

	// QueryDuration records the distribution of query execution time.
	// Using histogram allows analyzing latency percentiles (P50, P95, P99).
	//
	// Metric attributes:
	//   - db.operation: Operation type
	//   - db.system: Database type
	//
	// Unit: milliseconds (ms)
	//
	// Predefined bucket boundaries: 1, 5, 10, 25, 50, 100, 250, 500, 1000, 2500, 5000
	//
	// Usage:
	//   - Identify slow queries
	//   - Analyze performance trends
	//   - Set SLAs and alert thresholds
	QueryDuration metric.Float64Histogram

	// QueryErrors records the total number of query errors.
	// Grouped by operation type and database type.
	//
	// Metric attributes:
	//   - db.operation: Operation type
	//   - db.system: Database type
	//
	// Usage:
	//   - Monitor error rates
	//   - Identify anomaly patterns
	//   - Set up error alerts
	QueryErrors metric.Int64Counter
}

Metrics contains all OpenTelemetry metric instruments. These metrics are used to monitor the performance and health of database operations.

Metric types:

  • QueryCount: Counter, records total number of queries
  • QueryDuration: Histogram, records query latency distribution
  • QueryErrors: Counter, records total number of query errors

Usage scenarios:

  • Monitor database load and throughput
  • Analyze query performance and latency distribution
  • Track error rates and anomalies
  • Set up alerts and automated responses

type MySQLDialect 

type MySQLDialect struct{}

MySQLDialect implements MySQL database dialect.

MySQL features:

  • Uses ? as placeholder
  • Uses ON DUPLICATE KEY UPDATE syntax for Upsert
  • Automatically detects conflict target (primary key or unique key)

Usage example: 

session := sqlc.NewSession(db, sqlc.MySQLDialect{})

Note:

  • Upsert doesn't need to specify conflict columns, MySQL automatically detects by primary key or unique key
  • VALUES() function references proposed insert values

func (*MySQLDialect) Name 

func (d *MySQLDialect) Name() string

Name returns the MySQL dialect name.

func (*MySQLDialect) PlaceholderFormat 

func (d *MySQLDialect) PlaceholderFormat() sq.PlaceholderFormat

PlaceholderFormat returns MySQL's placeholder format (?).

func (*MySQLDialect) UpsertClause 

func (d *MySQLDialect) UpsertClause(tableName string, conflictCols []string, updateCols []string) string

UpsertClause generates MySQL's Upsert clause. MySQL uses ON DUPLICATE KEY UPDATE syntax.

Syntax format: 

ON DUPLICATE KEY UPDATE col1=VALUES(col1), col2=VALUES(col2)

MySQL features:

  • Doesn't need to specify conflict columns (auto-detects primary key or unique key)
  • VALUES(col) function references proposed insert values
  • If updateCols is empty, returns empty string (cannot implement DO NOTHING)

Parameters:

  • tableName: Table name (not used by MySQL, but kept for interface compatibility)
  • conflictCols: Conflict columns (not used by MySQL, auto-detects)
  • updateCols: Columns to update

Returns:

  • string: ON DUPLICATE KEY UPDATE clause

Example: 

dialect.UpsertClause("users", []string{"email"}, []string{"name", "updated_at"})
// Returns: "ON DUPLICATE KEY UPDATE name=VALUES(name),updated_at=VALUES(updated_at)"

type ObservabilityConfig 

type ObservabilityConfig struct {
	// Logger is the structured logger for recording query logs.
	// If nil, no logs are recorded.
	//
	// Log levels:
	//   - Debug: All queries (requires LogQueries = true)
	//   - Warn: Slow queries
	//   - Error: Failed queries
	//
	// Log fields:
	//   - operation: Operation type
	//   - duration: Execution duration
	//   - query: SQL statement (requires LogQueries = true)
	//   - error: Error message (if failed)
	Logger *slog.Logger

	// Tracer is the OpenTelemetry tracer for creating distributed trace spans.
	// If nil, no trace data is created.
	//
	// Span attributes:
	//   - db.statement: SQL statement
	//   - db.operation: Operation type
	//   - db.system: Database type
	//
	// Usage:
	//   - Trace request flow through the system
	//   - Analyze database operation proportion in overall requests
	//   - Identify performance bottlenecks
	Tracer trace.Tracer

	// Meter is the OpenTelemetry metrics collector.
	// If nil, no metric data is collected.
	//
	// Usage:
	//   - Create counters and histograms
	//   - Collect performance metrics
	Meter metric.Meter

	// Metrics contains initialized metric instruments.
	// Automatically initialized via WithMeter() or WithDefaultMeter().
	Metrics *Metrics

	// SlowQueryThreshold defines the threshold for slow queries.
	// Queries exceeding this threshold are logged at warning level.
	//
	// Default: 200 milliseconds
	//
	// Usage:
	//   - Identify queries needing optimization
	//   - Set performance baselines
	SlowQueryThreshold time.Duration

	// LogQueries controls whether to log all queries.
	// If true, all queries are logged at Debug level.
	//
	// Default: false
	//
	// Note:
	//   - Enabling this generates a large volume of logs, use only for debugging
	//   - For production, recommend disabling or using sampling
	//   - Slow queries and error queries are always logged
	LogQueries bool
}

ObservabilityConfig holds configuration for logging, tracing, and metrics. This configuration controls the observability behavior of Session.

Configuration items:

  • Logger: Structured logger (slog.Logger)
  • Tracer: OpenTelemetry tracer
  • Meter: OpenTelemetry metrics collector
  • Metrics: Initialized metric instruments
  • SlowQueryThreshold: Slow query threshold
  • LogQueries: Whether to log all queries

Usage example: 

// Custom configuration
config := &ObservabilityConfig{
    Logger:             slog.Default(),
    Tracer:             otel.Tracer("my-app"),
    SlowQueryThreshold: 200 * time.Millisecond,
    LogQueries:         true,
}

type PK 

type PK = clause.Eq

PK is a type alias for primary key equality expression. Used to simplify primary key query writing.

Example: 

pk := schema.PK(model) // Get primary key condition
query.Where(pk)        // Use primary key as query condition

type PostgreSQLDialect 

type PostgreSQLDialect struct{}

PostgreSQLDialect implements PostgreSQL database dialect.

PostgreSQL features:

  • Uses $1, $2, $3 as placeholders
  • Uses ON CONFLICT ... DO UPDATE syntax for Upsert
  • Requires explicitly specifying conflict columns
  • Uses EXCLUDED table to reference proposed insert values

Usage example: 

session := sqlc.NewSession(db, sqlc.PostgreSQLDialect{})

Note:

  • Upsert needs to specify conflict columns (ON CONFLICT (col))
  • EXCLUDED is a special table name, must be uppercase
  • Supports DO NOTHING (no update)

func (*PostgreSQLDialect) Name 

func (d *PostgreSQLDialect) Name() string

Name returns the PostgreSQL dialect name.

func (*PostgreSQLDialect) PlaceholderFormat 

func (d *PostgreSQLDialect) PlaceholderFormat() sq.PlaceholderFormat

PlaceholderFormat returns PostgreSQL's placeholder format ($1, $2, ...).

func (*PostgreSQLDialect) UpsertClause 

func (d *PostgreSQLDialect) UpsertClause(tableName string, conflictCols []string, updateCols []string) string

UpsertClause generates PostgreSQL's Upsert clause. PostgreSQL uses ON CONFLICT ... DO UPDATE syntax.

Syntax format: 

ON CONFLICT (conflict_columns) DO UPDATE SET col1=EXCLUDED.col1, col2=EXCLUDED.col2
ON CONFLICT (conflict_columns) DO NOTHING

PostgreSQL features:

  • Requires explicitly specifying conflict columns
  • EXCLUDED table references proposed insert values (must be uppercase)
  • Supports DO NOTHING

Parameters:

  • tableName: Table name (not used by PostgreSQL)
  • conflictCols: Conflict detection columns
  • updateCols: Columns to update

Returns:

  • string: ON CONFLICT clause

Example: 

dialect.UpsertClause("users", []string{"email"}, []string{"name", "updated_at"})
// Returns: "ON CONFLICT (email) DO UPDATE SET name=EXCLUDED.name,updated_at=EXCLUDED.updated_at"

type QueryBuilder 

type QueryBuilder[T any] struct {
	// contains filtered or unexported fields
}

QueryBuilder is a generic SQL query builder for model T. It provides a fluent chainable API to build complex SQL queries.

Type parameter:

  • T: Model type, must be registered via RegisterSchema[T]()

Core features:

  • Automatically applies soft delete filter (if model supports it)
  • Supports all common SQL query operations
  • Supports relation preloading
  • Supports being used as subquery

Usage example: 

// Create query builder
query := userRepo.Query()

// Build query through chaining
users, err := query.
    Where(generated.User.Status.Eq("active")).
    OrderBy(generated.User.CreatedAt.Desc()).
    Limit(10).
    Find(ctx)

Notes:

  • QueryBuilder is not completely immutable, some methods modify internal state
  • If you need to reuse query, use WithBuilder() to create a copy
  • Soft delete filter is automatically applied on creation

func Query 

func Query[T any](session *Session) *QueryBuilder[T]

Query creates a new QueryBuilder instance. This is the starting point for building queries, usually called via Repository.Query().

Type parameter:

  • T: Model type, must be registered via RegisterSchema[T]()

Parameters:

  • session: Database session

Returns:

  • *QueryBuilder[T]: Initialized query builder

Automatic behavior:

  • If model supports soft delete, automatically adds deleted_at IS NULL filter
  • Sets correct placeholder format (based on database dialect)
  • Initializes table name and schema

Usage example: 

// Create via Repository (recommended)
query := userRepo.Query()

// Create directly (advanced usage)
query := sqlc.Query[models.User](session)

Note:

  • Model T must be registered, otherwise will panic
  • Returned QueryBuilder already contains soft delete filter (if applicable)

func (*QueryBuilder[T]) Avg 

func (q *QueryBuilder[T]) Avg(ctx context.Context, column clause.Columnar) (float64, error)

Avg calculates the average (mean) of values in a numeric column. Returns the arithmetic mean of all non-NULL values in the specified column.

Parameters:

  • ctx: Context for cancellation and tracing
  • column: The numeric column to average (must implement clause.Columnar)

Returns:

  • float64: Average of all values (0 if no rows or all NULL)
  • error: Query execution error

Usage example: 

// Average product price
avgPrice, err := productRepo.Query().Avg(ctx, generated.Product.Price)
if err != nil {
    return err
}
fmt.Printf("Average price: $%.2f\n", avgPrice)

// Average with conditions
avgActivePrice, err := productRepo.Query().
    Where(generated.Product.Status.Eq("active")).
    Avg(ctx, generated.Product.Price)

// Average with grouping (manual approach)
// For grouped averages, use Select with custom SQL
query.
    Select(generated.User.Country, clause.Expr{SQL: "AVG(age)"}).
    GroupBy(generated.User.Country)

Note:

  • Returns 0 if no matching records found
  • NULL values are excluded from the calculation
  • Column should be numeric type
  • Result is always float64
  • For grouped averages, use Select() with raw SQL instead
  • Respects soft delete filter (unless WithTrashed() called)

func (*QueryBuilder[T]) Build added in v0.2.0 

func (q *QueryBuilder[T]) Build() (string, []any, error)

Build implements clause.Expression, enabling QueryBuilder to be used as a subquery. This allows nesting queries in WHERE clauses like: WHERE id IN (SELECT ...)

func (*QueryBuilder[T]) Chunk added in v0.2.0 

func (q *QueryBuilder[T]) Chunk(ctx context.Context, size int, fn func([]*T) error) error

Chunk processes query results in batches of the specified size. This is useful for processing large datasets without loading everything into memory. The callback function receives each batch of records; if it returns an error, chunking stops and the error is returned.

Example: 

err := userRepo.Query().Where(generated.User.Active.Eq(true)).Chunk(ctx, 100, func(users []*models.User) error {
    for _, u := range users {
        processUser(u)
    }
    return nil
})

func (*QueryBuilder[T]) Count 

func (q *QueryBuilder[T]) Count(ctx context.Context) (int64, error)

Count returns the number of records matching the query conditions. Ignores any Limit/Offset settings to count all matching records.

Parameters:

  • ctx: Context for cancellation and tracing

Returns:

  • int64: Number of matching records
  • error: Query execution error

Usage example: 

// Count all users
count, err := userRepo.Query().Count(ctx)

// Count active users
count, err := userRepo.Query().
    Where(generated.User.Status.Eq("active")).
    Count(ctx)

// Count with join
count, err := userRepo.Query().
    Join(generated.OrderSchema{},
        sqlc.On(generated.User.ID, generated.Order.UserID),
    ).
    Count(ctx)

Note:

  • Generates SELECT COUNT(*) FROM ...
  • Removes LIMIT and OFFSET from count query
  • Respects soft delete filter (unless WithTrashed() called)
  • Does not execute preloads

func (*QueryBuilder[T]) Distinct added in v0.2.0 

func (q *QueryBuilder[T]) Distinct() *QueryBuilder[T]

Distinct adds DISTINCT to the SELECT clause, removing duplicate rows from results. Example: repo.Query().Distinct().Select(UserFields.Email).Find(ctx)

func (*QueryBuilder[T]) Find 

func (q *QueryBuilder[T]) Find(ctx context.Context) ([]*T, error)

Find executes the query and returns all matching records. This is the primary method for retrieving multiple records from the database.

Parameters:

  • ctx: Context for cancellation and tracing

Returns:

  • []*T: Slice of model pointers (empty slice if no results)
  • error: Query execution error

Automatic behavior:

  • Executes all registered preloads after main query
  • Applies soft delete filter (unless WithTrashed() called)
  • Resolves column names from schema if not explicitly set

Usage example: 

// Get all users
users, err := userRepo.Query().Find(ctx)

// Get active users
activeUsers, err := userRepo.Query().
    Where(generated.User.Status.Eq("active")).
    Find(ctx)

// With preloading
users, err := userRepo.Query().
    WithPreload(sqlc.Preload(generated.User.Posts)).
    Find(ctx)

Note:

  • Returns empty slice (not nil) if no records found
  • Preloads are executed in the order they were added
  • Context is propagated to all database operations

func (*QueryBuilder[T]) First 

func (q *QueryBuilder[T]) First(ctx context.Context) (*T, error)

First executes the query and returns the first record ordered by primary key ascending. Returns ErrNotFound if no record matches the query conditions.

Parameters:

  • ctx: Context for cancellation and tracing

Returns:

  • *T: First model instance (by primary key)
  • error: ErrNotFound if no record, or other query error

Usage example: 

// Get first user by ID
user, err := userRepo.Query().First(ctx)

// Get first active user
user, err := userRepo.Query().
    Where(generated.User.Status.Eq("active")).
    First(ctx)

Note:

  • Orders by primary key ascending
  • Adds LIMIT 1 to the query
  • For custom ordering, use OrderBy().Take()

func (*QueryBuilder[T]) FirstOr added in v0.2.0 

func (q *QueryBuilder[T]) FirstOr(ctx context.Context, fallback func() *T) (*T, error)

FirstOr returns the first matching record, or executes the fallback function if no record is found (ErrNotFound).

Example: 

user, err := userRepo.Query().Where(generated.User.Email.Eq("test@example.com")).FirstOr(ctx, func() *models.User {
    return &models.User{Email: "test@example.com", Name: "Default"}
})

func (*QueryBuilder[T]) GroupBy 

func (q *QueryBuilder[T]) GroupBy(columns ...clause.Columnar) *QueryBuilder[T]

GroupBy adds GROUP BY clause to the query for aggregation. Used with aggregate functions like COUNT, SUM, AVG, MAX, MIN.

Parameters:

  • columns: Columns to group by (must implement clause.Columnar)

Returns:

  • *QueryBuilder[T]: Returns itself to support chaining

Usage example: 

// Group by single column
query.GroupBy(generated.User.Status)

// Group by multiple columns
query.GroupBy(
    generated.User.Status,
    generated.User.Country,
)

// With aggregation
query.
    Select(generated.User.Status, clause.Count("*")).
    GroupBy(generated.User.Status)

Note:

  • Usually combined with Select() to choose aggregate columns
  • Use Having() to filter grouped results
  • Arguments must implement clause.Columnar (e.g., field.Field, clause.Column)

func (*QueryBuilder[T]) Having 

func (q *QueryBuilder[T]) Having(expr clause.Expression) *QueryBuilder[T]

Having adds HAVING clause to filter grouped results. Similar to WHERE but operates on groups after aggregation.

Parameters:

  • expr: Filter condition expression

Returns:

  • *QueryBuilder[T]: Returns itself to support chaining

Usage example: 

// Filter groups with count > 5
query.
    Select(generated.User.Country, clause.Count("*")).
    GroupBy(generated.User.Country).
    Having(clause.Gt{clause.Count("*"), 5})

// Multiple conditions
query.
    GroupBy(generated.User.Status).
    Having(clause.And{
        clause.Gt{clause.Count("*"), 10},
        clause.Eq{generated.User.Active, true},
    })

Note:

  • Must be used after GroupBy()
  • Can reference aggregate functions in conditions
  • Conditions are applied after grouping, not before

func (*QueryBuilder[T]) Join 

func (q *QueryBuilder[T]) Join(target tableNamer, ons ...JoinOn) *QueryBuilder[T]

Join adds an INNER JOIN clause to the query using schema-based table reference. This is the type-safe way to join tables using generated schema types.

Parameters:

  • target: The schema of the table to join (e.g., generated.OrderSchema{})
  • ons: Join conditions created with On() function

Returns:

  • *QueryBuilder[T]: Returns itself to support chaining

Usage example: 

query.Join(generated.OrderSchema{},
    sqlc.On(generated.User.ID, generated.Order.UserID),
)

Note:

  • Multiple On() conditions are combined with AND
  • Automatically handles table name prefixes for columns
  • For custom table names, use JoinAs()

func (*QueryBuilder[T]) JoinAs added in v0.2.0 

func (q *QueryBuilder[T]) JoinAs(target tableNamer, alias string, ons ...JoinOn) *QueryBuilder[T]

JoinAs adds an INNER JOIN clause with a custom table alias. Use this when joining the same table multiple times or when you need a custom alias.

Parameters:

  • target: The schema of the table to join
  • alias: Custom alias for the joined table
  • ons: Join conditions created with On() function

Returns:

  • *QueryBuilder[T]: Returns itself to support chaining

Usage example: 

// Join orders table with alias 'o'
query.JoinAs(generated.OrderSchema{}, "o",
    sqlc.On(generated.User.ID, clause.Column{Name: "user_id", Table: "o"}),
)

Note:

  • Column references in On() should use the alias when referring to the joined table

func (*QueryBuilder[T]) JoinTable added in v0.2.0 

func (q *QueryBuilder[T]) JoinTable(table string, on clause.Expression) *QueryBuilder[T]

JoinTable adds an INNER JOIN clause using raw table name and expression. This provides maximum flexibility for complex join conditions.

Parameters:

  • table: Raw table name (can include alias, e.g., "orders o")
  • on: Join condition as a clause.Expression

Returns:

  • *QueryBuilder[T]: Returns itself to support chaining

Usage example: 

// Simple join
query.JoinTable("orders", clause.Expr{
    SQL:  "users.id = orders.user_id",
    Vars: nil,
})

// Join with parameters
query.JoinTable("orders", clause.Expr{
    SQL:  "users.id = orders.user_id AND orders.total > ?",
    Vars: []any{100},
})

Note:

  • Use this for complex join conditions not supported by On()
  • Prefer Join() with On() for type safety when possible

func (*QueryBuilder[T]) Last added in v0.2.0 

func (q *QueryBuilder[T]) Last(ctx context.Context) (*T, error)

Last executes the query and returns the last record ordered by primary key descending. Returns ErrNotFound if no record matches the query conditions.

Parameters:

  • ctx: Context for cancellation and tracing

Returns:

  • *T: Last model instance (by primary key)
  • error: ErrNotFound if no record, or other query error

Usage example: 

// Get last user by ID
user, err := userRepo.Query().Last(ctx)

// Get last active user
user, err := userRepo.Query().
    Where(generated.User.Status.Eq("active")).
    Last(ctx)

Note:

  • Orders by primary key descending
  • Adds LIMIT 1 to the query
  • For custom ordering, use OrderBy().Take()

func (*QueryBuilder[T]) LeftJoin 

func (q *QueryBuilder[T]) LeftJoin(target tableNamer, ons ...JoinOn) *QueryBuilder[T]

LeftJoin adds a LEFT JOIN clause to the query. Returns all records from the left table and matching records from the right table. Unmatched records will have NULL values for right table columns.

Parameters:

  • target: The schema of the table to join
  • ons: Join conditions created with On() function

Returns:

  • *QueryBuilder[T]: Returns itself to support chaining

Usage example: 

// Get all users and their orders (if any)
query.LeftJoin(generated.OrderSchema{},
    sqlc.On(generated.User.ID, generated.Order.UserID),
)

Note:

  • Users without orders will be included with NULL order fields
  • For custom alias, use LeftJoinAs()

func (*QueryBuilder[T]) LeftJoinAs added in v0.2.0 

func (q *QueryBuilder[T]) LeftJoinAs(target tableNamer, alias string, ons ...JoinOn) *QueryBuilder[T]

LeftJoinAs adds a LEFT JOIN clause with a custom table alias. Combines LEFT JOIN behavior with custom aliasing.

Parameters:

  • target: The schema of the table to join
  • alias: Custom alias for the joined table
  • ons: Join conditions created with On() function

Returns:

  • *QueryBuilder[T]: Returns itself to support chaining

Usage example: 

query.LeftJoinAs(generated.OrderSchema{}, "recent_orders",
    sqlc.On(generated.User.ID, clause.Column{Name: "user_id", Table: "recent_orders"}),
)

func (*QueryBuilder[T]) LeftJoinTable added in v0.2.0 

func (q *QueryBuilder[T]) LeftJoinTable(table string, on clause.Expression) *QueryBuilder[T]

LeftJoinTable adds a LEFT JOIN clause using raw table name and expression. Combines LEFT JOIN behavior with maximum flexibility.

Parameters:

  • table: Raw table name (can include alias)
  • on: Join condition as a clause.Expression

Returns:

  • *QueryBuilder[T]: Returns itself to support chaining

Usage example: 

query.LeftJoinTable("orders o", clause.Expr{
    SQL:  "users.id = o.user_id",
    Vars: nil,
})

func (*QueryBuilder[T]) Limit 

func (q *QueryBuilder[T]) Limit(n uint64) *QueryBuilder[T]

Limit limits the number of records returned by the query. Used to implement pagination or limit result set size.

Parameters:

  • n: Maximum number of records to return

Returns:

  • *QueryBuilder[T]: Returns itself to support chaining

Usage example: 

// Limit to 10 records
query.Limit(10)

// Pagination: page 3, 20 records per page
query.Limit(20).Offset(40)

Note:

  • 0 means no limit (some databases may not support this)
  • Usually used with Offset() for pagination

func (*QueryBuilder[T]) Max 

func (q *QueryBuilder[T]) Max(ctx context.Context, column clause.Columnar) (any, error)

Max finds the maximum value in a column. Returns the largest non-NULL value from the specified column.

Parameters:

  • ctx: Context for cancellation and tracing
  • column: The column to find maximum value (must implement clause.Columnar)

Returns:

  • any: Maximum value (nil if no rows or all NULL)
  • error: Query execution error

Usage example: 

// Find highest price
maxPrice, err := productRepo.Query().Max(ctx, generated.Product.Price)
if err != nil {
    return err
}
if maxPrice != nil {
    fmt.Printf("Highest price: %v\n", maxPrice)
}

// Max with conditions
maxActivePrice, err := productRepo.Query().
    Where(generated.Product.Status.Eq("active")).
    Max(ctx, generated.Product.Price)

// Max date
latestOrder, err := orderRepo.Query().
    Max(ctx, generated.Order.CreatedAt)

Note:

  • Returns nil if no matching records found
  • NULL values are excluded from the calculation
  • Works with numeric, string, and date/time columns
  • Return type is any; type assertion may be needed
  • Actual type depends on database driver and column type
  • Respects soft delete filter (unless WithTrashed() called)

func (*QueryBuilder[T]) Min 

func (q *QueryBuilder[T]) Min(ctx context.Context, column clause.Columnar) (any, error)

Min finds the minimum value in a column. Returns the smallest non-NULL value from the specified column.

Parameters:

  • ctx: Context for cancellation and tracing
  • column: The column to find minimum value (must implement clause.Columnar)

Returns:

  • any: Minimum value (nil if no rows or all NULL)
  • error: Query execution error

Usage example: 

// Find lowest price
minPrice, err := productRepo.Query().Min(ctx, generated.Product.Price)
if err != nil {
    return err
}
if minPrice != nil {
    fmt.Printf("Lowest price: %v\n", minPrice)
}

// Min with conditions
minActivePrice, err := productRepo.Query().
    Where(generated.Product.Status.Eq("active")).
    Min(ctx, generated.Product.Price)

// Min date
earliestOrder, err := orderRepo.Query().
    Min(ctx, generated.Order.CreatedAt)

Note:

  • Returns nil if no matching records found
  • NULL values are excluded from the calculation
  • Works with numeric, string, and date/time columns
  • Return type is any; type assertion may be needed
  • Actual type depends on database driver and column type
  • Respects soft delete filter (unless WithTrashed() called)

func (*QueryBuilder[T]) Offset 

func (q *QueryBuilder[T]) Offset(n uint64) *QueryBuilder[T]

Offset sets the offset for query results. Used to implement pagination, skipping the first N records.

Parameters:

  • n: Number of records to skip

Returns:

  • *QueryBuilder[T]: Returns itself to support chaining

Usage example: 

// Skip first 20 records
query.Offset(20)

// Pagination: page 3, 10 records per page
// OFFSET = (page - 1) * pageSize
query.Limit(10).Offset(20)

Note:

  • Usually used with Limit()
  • Large offsets may impact performance
  • Consider using cursor pagination instead of large offsets

func (*QueryBuilder[T]) OnlyTrashed added in v0.2.0 

func (q *QueryBuilder[T]) OnlyTrashed() *QueryBuilder[T]

OnlyTrashed returns only soft-deleted records.

Example: 

repo.Query().OnlyTrashed().Find(ctx)

func (*QueryBuilder[T]) OrderBy 

func (q *QueryBuilder[T]) OrderBy(orders ...clause.OrderByColumn) *QueryBuilder[T]

OrderBy adds ORDER BY clause to the query. Supports ascending (ASC) and descending (DESC) sorting.

Parameters:

  • orders: Sort columns (variadic)

Returns:

  • *QueryBuilder[T]: Returns itself to support chaining

Usage example: 

// Single column sorting
query.OrderBy(generated.User.CreatedAt.Desc())

// Multiple column sorting
query.OrderBy(
    generated.User.Status.Asc(),
    generated.User.CreatedAt.Desc(),
)

// Using clause.OrderByColumn
query.OrderBy(clause.OrderByColumn{
    Column: generated.User.Name.Column(),
    Desc:   false, // Ascending
})

Note:

  • Multiple calls will append sort columns
  • Asc() means ascending, Desc() means descending

func (*QueryBuilder[T]) Pluck added in v0.2.0 

func (q *QueryBuilder[T]) Pluck(ctx context.Context, column clause.Columnar, dest any) error

Pluck queries a single column and returns the values as a slice. dest must be a pointer to a slice of the appropriate type (e.g., *[]string, *[]int64). This is useful for extracting single column values without loading full models.

Example: 

var emails []string
userRepo.Query().Where(generated.User.Active.Eq(true)).Pluck(ctx, generated.User.Email, &emails)

func (*QueryBuilder[T]) RightJoin 

func (q *QueryBuilder[T]) RightJoin(target tableNamer, ons ...JoinOn) *QueryBuilder[T]

RightJoin adds a RIGHT JOIN clause to the query. Returns all records from the right table and matching records from the left table. Unmatched records will have NULL values for left table columns.

Parameters:

  • target: The schema of the table to join
  • ons: Join conditions created with On() function

Returns:

  • *QueryBuilder[T]: Returns itself to support chaining

Usage example: 

// Get all orders and their users (if any)
query.RightJoin(generated.OrderSchema{},
    sqlc.On(generated.User.ID, generated.Order.UserID),
)

Note:

  • Orders without users will be included with NULL user fields
  • Not all databases support RIGHT JOIN (e.g., SQLite)

func (*QueryBuilder[T]) RightJoinAs added in v0.2.0 

func (q *QueryBuilder[T]) RightJoinAs(target tableNamer, alias string, ons ...JoinOn) *QueryBuilder[T]

RightJoinAs adds a RIGHT JOIN clause with a custom table alias. Combines RIGHT JOIN behavior with custom aliasing.

Parameters:

  • target: The schema of the table to join
  • alias: Custom alias for the joined table
  • ons: Join conditions created with On() function

Returns:

  • *QueryBuilder[T]: Returns itself to support chaining

Usage example: 

query.RightJoinAs(generated.OrderSchema{}, "o",
    sqlc.On(generated.User.ID, clause.Column{Name: "user_id", Table: "o"}),
)

func (*QueryBuilder[T]) RightJoinTable added in v0.2.0 

func (q *QueryBuilder[T]) RightJoinTable(table string, on clause.Expression) *QueryBuilder[T]

RightJoinTable adds a RIGHT JOIN clause using raw table name and expression. Combines RIGHT JOIN behavior with maximum flexibility.

Parameters:

  • table: Raw table name (can include alias)
  • on: Join condition as a clause.Expression

Returns:

  • *QueryBuilder[T]: Returns itself to support chaining

Usage example: 

query.RightJoinTable("orders o", clause.Expr{
    SQL:  "users.id = o.user_id",
    Vars: nil,
})

func (*QueryBuilder[T]) Scan 

func (q *QueryBuilder[T]) Scan(ctx context.Context, dest any) error

Scan executes the query and scans the results into a custom destination. dest can be a pointer to a struct or a pointer to a slice of structs. This is useful for partial selections or joins mapping to DTOs.

func (*QueryBuilder[T]) Select 

func (q *QueryBuilder[T]) Select(columns ...clause.Columnar) *QueryBuilder[T]

Select replaces the selected columns arguments must implement clause.Columnar (e.g. field.Field, clause.Column)

func (*QueryBuilder[T]) Sum 

func (q *QueryBuilder[T]) Sum(ctx context.Context, column clause.Columnar) (float64, error)

Sum calculates the sum of values in a numeric column. Returns the total sum of all non-NULL values in the specified column.

Parameters:

  • ctx: Context for cancellation and tracing
  • column: The numeric column to sum (must implement clause.Columnar)

Returns:

  • float64: Sum of all values (0 if no rows or all NULL)
  • error: Query execution error

Usage example: 

// Total order amount
total, err := orderRepo.Query().Sum(ctx, generated.Order.Total)
if err != nil {
    return err
}
fmt.Printf("Total revenue: $%.2f\n", total)

// Sum with conditions
activeTotal, err := orderRepo.Query().
    Where(generated.Order.Status.Eq("completed")).
    Sum(ctx, generated.Order.Total)

// Sum with join
userTotal, err := orderRepo.Query().
    Join(generated.UserSchema{},
        sqlc.On(generated.Order.UserID, generated.User.ID),
    ).
    Where(generated.User.ID.Eq(123)).
    Sum(ctx, generated.Order.Total)

Note:

  • Returns 0 if no matching records found
  • NULL values are excluded from the calculation
  • Column should be numeric type (INT, FLOAT, DECIMAL, etc.)
  • Result is always float64 regardless of column type
  • Respects soft delete filter (unless WithTrashed() called)

func (*QueryBuilder[T]) Take added in v0.2.0 

func (q *QueryBuilder[T]) Take(ctx context.Context) (*T, error)

Take executes the query and returns a single record without any ordering. Returns ErrNotFound if no record matches the query conditions.

Parameters:

  • ctx: Context for cancellation and tracing

Returns:

  • *T: Single model instance
  • error: ErrNotFound if no record, or other query error

Usage example: 

// Get any active user
user, err := userRepo.Query().
    Where(generated.User.Status.Eq("active")).
    Take(ctx)
if errors.Is(err, sqlc.ErrNotFound) {
    // No active user found
}

Note:

  • Adds LIMIT 1 to the query
  • Does not guarantee which record is returned if multiple match
  • Use First() or Last() for deterministic ordering

func (*QueryBuilder[T]) ToSQL added in v0.2.0 

func (q *QueryBuilder[T]) ToSQL() (string, []any, error)

ToSQL returns the SQL string and arguments without executing the query. This is useful for testing, debugging, or logging generated SQL.

func (*QueryBuilder[T]) Where 

func (q *QueryBuilder[T]) Where(expr clause.Expression) *QueryBuilder[T]

Where adds WHERE condition to the query. Multiple calls to Where() will connect all conditions with AND.

Parameters:

  • expr: Condition expression (e.g., clause.Eq, clause.And, clause.Or, etc.)

Returns:

  • *QueryBuilder[T]: Returns itself to support chaining

Supported expression types:

  • clause.Eq: Equals (=)
  • clause.Ne: Not equals (!=)
  • clause.Gt: Greater than (>)
  • clause.Gte: Greater than or equal (>=)
  • clause.Lt: Less than (<)
  • clause.Lte: Less than or equal (<=)
  • clause.Like: Pattern matching (LIKE)
  • clause.In: Contains (IN)
  • clause.And: Logical AND
  • clause.Or: Logical OR
  • clause.Not: Logical NOT

Usage example: 

// Single condition
query.Where(generated.User.Status.Eq("active"))

// Multiple conditions (connected with AND)
query.Where(generated.User.Status.Eq("active")).
      Where(generated.User.Age.Gte(18))

// Complex condition
query.Where(clause.Or{
    generated.User.Role.Eq("admin"),
    generated.User.Role.Eq("moderator"),
})

// Combined condition
query.Where(clause.And{
    generated.User.Status.Eq("active"),
    clause.Or{
        generated.User.Age.Gt(18),
        generated.User.ParentConsent.Eq(true),
    },
})

Note:

  • Modifies current QueryBuilder instance and returns it
  • Conditions are connected with AND
  • Use clause.Or for OR conditions

func (*QueryBuilder[T]) WithBuilder 

func (q *QueryBuilder[T]) WithBuilder(fn func(b sq.SelectBuilder) sq.SelectBuilder) *QueryBuilder[T]

WithBuilder allow users to manipulate the underlying squirrel.SelectBuilder. This provides an escape hatch for complex queries (Joins, CTEs, Window functions) that are not directly supported by the simplified ORM API.

func (*QueryBuilder[T]) WithPreload added in v0.2.0 

func (q *QueryBuilder[T]) WithPreload(preload preloadExecutor[T]) *QueryBuilder[T]

WithPreload adds a preload executor to load related data after the main query. Use with Preload() function to create type-safe preload executors.

func (*QueryBuilder[T]) WithTrashed added in v0.2.0 

func (q *QueryBuilder[T]) WithTrashed() *QueryBuilder[T]

WithTrashed includes soft-deleted records in query results. By default, soft-deleted records are filtered out automatically.

Example: 

repo.Query().WithTrashed().Find(ctx)

type Relation added in v0.2.0 

type Relation[P, C any] struct {
	// Type is the relationship type (HasOne or HasMany).
	// Affects data loading and population behavior.
	Type RelationType

	// ForeignKey is the foreign key column in child table.
	// Used to find associated records in child table.
	//
	// Examples:
	//   - For User has many Posts, foreign key is posts.user_id
	//   - For User has one Profile, foreign key is profiles.user_id
	ForeignKey clause.Column

	// LocalKey is the local key column in parent table.
	// Usually the primary key of parent table, used to match with foreign key.
	//
	// Examples:
	//   - For User has many Posts, local key is users.id
	//   - For User has one Profile, local key is users.id
	LocalKey clause.Column

	// Setter sets loaded child models into parent model.
	// This is the data population function.
	//
	// Parameters:
	//   - parent: Parent model instance pointer
	//   - children: Child model slice
	//
	// For HasOne:
	//   - children contains 0 or 1 element
	//   - If empty, indicates no associated record
	//
	// For HasMany:
	//   - children contains all associated records
	//   - If empty slice, indicates no associated record (already initialized)
	//
	// Examples:
	//   // HasOne
	//   func(u *User, children []*Profile) {
	//       if len(children) > 0 {
	//           u.Profile = children[0]
	//       }
	//   }
	//
	//   // HasMany
	//   func(u *User, posts []*Post) {
	//       u.Posts = posts
	//   }
	Setter func(parent *P, children []*C)

	// GetLocalKeyValue extracts local key value from parent model.
	// Used to build IN condition for associated queries.
	//
	// Parameters:
	//   - parent: Parent model instance pointer
	//
	// Returns:
	//   - any: Local key value (e.g., user ID)
	//
	// Example:
	//   func(u *User) any { return u.ID }
	GetLocalKeyValue func(parent *P) any

	// GetForeignKeyValue extracts foreign key value from child model.
	// Used to group child models by foreign key during preload.
	// This replaces reflection-based field extraction for better performance and type safety.
	//
	// Parameters:
	//   - child: Child model instance pointer
	//
	// Returns:
	//   - any: Foreign key value (e.g., post's user_id)
	//
	// Example:
	//   func(p *Post) any { return p.UserID }
	GetForeignKeyValue func(child *C) any
}

Relation defines a relationship between parent model P and child model C. This is the core definition of a relationship, containing all necessary information to load associated data.

Type parameters:

  • P: Parent model type (e.g., User)
  • C: Child model type (e.g., Post)

Key concepts:

  • Foreign Key: Column in child table referencing parent table
  • Local Key: Column in parent table being referenced (usually primary key)
  • Setter: Function to set loaded child models into parent model
  • GetLocalKeyValue: Function to extract local key value from parent model

Usage example: 

// User has many posts
relation := Relation[User, Post]{
    Type:       RelationHasMany,
    ForeignKey: clause.Column{Name: "user_id"},
    LocalKey:   clause.Column{Name: "id"},
    Setter:     func(u *User, posts []*Post) { u.Posts = posts },
    GetLocalKeyValue: func(u *User) any { return u.ID },
}

Note:

  • Usually created using HasOne() or HasMany() functions, not directly constructed
  • Can be automatically generated by code generator

func HasMany added in v0.2.0 

func HasMany[P, C any](
	foreignKey clause.Column,
	localKey clause.Column,
	setter func(*P, []*C),
	getLocalKey func(*P) any,
	getForeignKey func(*C) any,
) Relation[P, C]

HasMany creates a HasMany relationship definition. Used to define one-to-many relationships.

Type parameters:

  • P: Parent model type
  • C: Child model type

Parameters:

  • foreignKey: Foreign key column in child table
  • localKey: Local key column in parent table (usually primary key)
  • setter: Setter function to set child model slice into parent model
  • getLocalKey: Extract function to get local key value from parent model

Returns:

  • Relation[P, C]: Relationship definition

Usage example: 

// User has many posts
userHasManyPosts := sqlc.HasMany[User, Post](
    clause.Column{Name: "user_id"},  // posts.user_id
    clause.Column{Name: "id"},       // users.id
    func(u *User, posts []*Post) {
        u.Posts = posts // Can be empty slice, but not nil
    },
    func(u *User) any { return u.ID },
)

// Use with preload
users, err := userRepo.Query().
    WithPreload(sqlc.Preload(userHasManyPosts)).
    Find(ctx)

Note:

  • Even if no associated records, empty slice is set (not nil)
  • This avoids need for nil pointer checks

func HasOne added in v0.2.0 

func HasOne[P, C any](
	foreignKey clause.Column,
	localKey clause.Column,
	setter func(*P, *C),
	getLocalKey func(*P) any,
	getForeignKey func(*C) any,
) Relation[P, C]

HasOne creates a HasOne relationship definition. Used to define one-to-one relationships.

Type parameters:

  • P: Parent model type
  • C: Child model type

Parameters:

  • foreignKey: Foreign key column in child table
  • localKey: Local key column in parent table (usually primary key)
  • setter: Setter function to set single child model into parent model
  • getLocalKey: Extract function to get local key value from parent model

Returns:

  • Relation[P, C]: Relationship definition

Usage example: 

// User has one profile
userHasOneProfile := sqlc.HasOne[User, Profile](
    clause.Column{Name: "user_id"},  // profiles.user_id
    clause.Column{Name: "id"},       // users.id
    func(u *User, p *Profile) {
        if p != nil {
            u.Profile = p
        }
    },
    func(u *User) any { return u.ID },
)

// Use with preload
users, err := userRepo.Query().
    WithPreload(sqlc.Preload(userHasOneProfile)).
    Find(ctx)

type RelationType added in v0.2.0 

type RelationType int

RelationType defines the type of relationship. Different relationship types differ in data loading and population methods. 

const (
	// RelationHasOne indicates a one-to-one relationship.
	// Parent model has one child model.
	//
	// Characteristics:
	//   - Foreign key is in child table
	//   - Each parent model has at most one associated child model
	//   - Setter receives slice with 0 or 1 element
	//
	// Examples:
	//   - User has one profile
	//   - Order has one shipping address
	//   - Article has one author info
	//
	// Database structure:
	//   users: id, name
	//   profiles: id, user_id (foreign key), bio
	//
	// Usage example:
	//
	//	userHasOneProfile := sqlc.HasOne[User, Profile](
	//	    clause.Column{Name: "user_id"},  // profiles.user_id
	//	    clause.Column{Name: "id"},       // users.id
	//	    func(u *User, p *Profile) { u.Profile = p },
	//	    func(u *User) any { return u.ID },
	//	)
	RelationHasOne RelationType = iota

	// RelationHasMany indicates a one-to-many relationship.
	// Parent model has many child models.
	//
	// Characteristics:
	//   - Foreign key is in child table
	//   - Each parent model can have multiple associated child models
	//   - Setter receives slice with any number of elements
	//   - Automatically initializes empty slice to avoid nil
	//
	// Examples:
	//   - User has many posts
	//   - Category has many products
	//   - Department has many employees
	//
	// Database structure:
	//   users: id, name
	//   posts: id, user_id (foreign key), title
	//
	// Usage example:
	//
	//	userHasManyPosts := sqlc.HasMany[User, Post](
	//	    clause.Column{Name: "user_id"},  // posts.user_id
	//	    clause.Column{Name: "id"},       // users.id
	//	    func(u *User, posts []*Post) { u.Posts = posts },
	//	    func(u *User) any { return u.ID },
	//	)
	RelationHasMany
)

type Repository 

type Repository[T any] struct {
	// contains filtered or unexported fields
}

Repository manages all CRUD operations for model T. It is type-safe, leveraging Go generics for compile-time type checking.

Repository design principles:

  • Immutability: Where() and other methods return new Repository instances, not modifying original instances
  • Composability: Build complex queries through method chaining
  • Lifecycle hooks: Automatically triggers BeforeCreate/AfterCreate and other hooks
  • Soft delete support: Automatically detects and supports soft delete functionality

Usage example: 

// Create Repository
userRepo := sqlc.NewRepository[models.User](session)

// Create record
user := &models.User{Email: "test@example.com", Name: "Test User"}
if err := userRepo.Create(ctx, user); err != nil {
    return err
}
fmt.Println("Created user ID:", user.ID) // Auto-increment ID backfilled

// Query record
user, err := userRepo.FindOne(ctx, 1)

// Update record
user.Name = "New Name"
if err := userRepo.Update(ctx, user); err != nil {
    return err
}

// Delete record
if err := userRepo.Delete(ctx, 1); err != nil {
    return err
}

func NewRepository 

func NewRepository[T any](session *Session) *Repository[T]

NewRepository creates a new Repository instance. This is the entry point for using Repository.

Parameters:

  • session: Database session, can be regular session or transaction session

Type parameter:

  • T: Model type, must be registered via RegisterSchema

Returns:

  • *Repository[T]: Initialized Repository instance

Note:

  • Model T must be registered via RegisterSchema[T]()
  • If not registered, LoadSchema[T]() will panic

Example: 

// Basic usage
userRepo := sqlc.NewRepository[models.User](session)

// Use in transaction
err := session.Transaction(ctx, func(txSession *Session) error {
    txUserRepo := sqlc.NewRepository[models.User](txSession)
    return txUserRepo.Create(ctx, user)
})

func (*Repository[T]) BatchCreate 

func (r *Repository[T]) BatchCreate(ctx context.Context, models []*T) error

BatchCreate inserts multiple records in a single SQL statement. This is more efficient than calling Create() in a loop, suitable for batch import scenarios.

Operation flow:

  1. Trigger BeforeCreate hook for each model
  2. Build batch INSERT statement (single SQL, multiple VALUES)
  3. Execute batch insertion
  4. Trigger AfterCreate hook for each model

Parameters:

  • ctx: Context, supports cancellation and timeout
  • models: Model instance pointer slice

Returns:

  • error: Insertion error or hook error

Note:

  • Empty slice will immediately return nil (no-op)
  • Auto-increment IDs will not be backfilled to models (database limitation)
  • If any hook fails, entire operation aborts
  • Does not support partial rollback within transaction (should be called outside transaction)

Performance suggestions:

  • For large amounts of data (>1000 records), consider calling in batches
  • Database may have single SQL size limits

Example: 

users := []*models.User{
    {Email: "user1@example.com", Name: "User 1"},
    {Email: "user2@example.com", Name: "User 2"},
    {Email: "user3@example.com", Name: "User 3"},
}

if err := userRepo.BatchCreate(ctx, users); err != nil {
    return err
}

// Note: users[i].ID will not be set

func (*Repository[T]) Create 

func (r *Repository[T]) Create(ctx context.Context, model *T) error

Create inserts a new record into the database. This is the recommended way to create a single record.

Operation flow:

  1. Trigger BeforeCreate hook (if model implements BeforeCreateInterface)
  2. Extract insert data from model (via schema.InsertRow)
  3. Execute INSERT statement
  4. If auto-increment primary key, backfill ID to model
  5. Trigger AfterCreate hook (if model implements AfterCreateInterface)

Parameters:

  • ctx: Context, supports cancellation and timeout
  • model: Model instance pointer, may be modified after insertion (auto-increment ID backfilled)

Returns:

  • error: Insertion error or hook error

Hook support:

  • BeforeCreate: Called before insertion, can be used for validation or setting default values
  • AfterCreate: Called after insertion, can be used for logging or cascade operations

Example: 

user := &models.User{
    Email: "test@example.com",
    Name:  "Test User",
}

if err := userRepo.Create(ctx, user); err != nil {
    return err
}

fmt.Println("Created user ID:", user.ID) // Auto-increment ID backfilled

func (*Repository[T]) Delete 

func (r *Repository[T]) Delete(ctx context.Context, id any) error

Delete deletes a record by primary key. Performs hard delete, record will be permanently removed from database.

Parameters:

  • ctx: Context, supports cancellation and timeout
  • id: Record's primary key value

Returns:

  • error: Deletion error

Note:

  • This is hard delete, record will be permanently removed
  • Does not trigger lifecycle hooks (no model instance)
  • For soft delete models, recommend using SoftDelete()
  • Scope conditions will be combined with primary key condition

Example: 

// Basic delete
if err := userRepo.Delete(ctx, userID); err != nil {
    return err
}

// Conditional delete
if err := userRepo.
    Where(generated.User.Status.Eq("inactive")).
    Delete(ctx, userID); err != nil {
    return err
}

func (*Repository[T]) DeleteModel 

func (r *Repository[T]) DeleteModel(ctx context.Context, model *T) error

DeleteModel deletes a record by model instance, triggering lifecycle hooks. Use when you need to execute custom logic before/after deletion.

Operation flow:

  1. Trigger BeforeDelete hook (if model implements BeforeDeleteInterface)
  2. Extract primary key from model
  3. Build DELETE statement with primary key condition
  4. Apply all scope conditions
  5. Execute deletion
  6. Trigger AfterDelete hook (if model implements AfterDeleteInterface)

Parameters:

  • ctx: Context, supports cancellation and timeout
  • model: Model instance pointer, must contain valid primary key value

Returns:

  • error: Deletion error or hook error

Example: 

// Query first then delete (supports hooks)
user, err := userRepo.FindOne(ctx, userID)
if err != nil {
    return err
}

if err := userRepo.DeleteModel(ctx, user); err != nil {
    return err
}

func (*Repository[T]) FindOne 

func (r *Repository[T]) FindOne(ctx context.Context, id any) (*T, error)

FindOne queries a single record by primary key. This is a shortcut for getting a record by ID.

Parameters:

  • ctx: Context, supports cancellation and timeout
  • id: Record's primary key value

Returns:

  • *T: Found model instance
  • error: Query error (ErrNotFound indicates not found)

Note:

  • Automatically applies soft delete filter
  • Scope conditions will be combined with primary key condition
  • If record not found, returns ErrNotFound

Example: 

user, err := userRepo.FindOne(ctx, 123)
if err != nil {
    if errors.Is(err, sqlc.ErrNotFound) {
        // User not found
        return nil
    }
    return err
}
fmt.Println("User:", user.Name)

func (*Repository[T]) FirstOrCreate added in v0.2.0 

func (r *Repository[T]) FirstOrCreate(ctx context.Context, defaults *T) (*T, error)

FirstOrCreate returns the first matching record, or creates one with defaults. This is the recommended way to implement "find or create" pattern.

Operation flow:

  1. Build query with scope conditions
  2. Try to find record
  3. If found, return record
  4. If not found, create record using defaults
  5. Trigger BeforeCreate/AfterCreate hooks

Parameters:

  • ctx: Context, supports cancellation and timeout
  • defaults: Default value model, used to create new record

Returns:

  • *T: Found or created model instance
  • error: Query or creation error

Note:

  • Scope conditions are used to find record
  • defaults should contain all necessary fields
  • Creation will trigger lifecycle hooks
  • Auto-increment ID will be backfilled to defaults

Example: 

// Find or create user
user, err := userRepo.
    Where(generated.User.Email.Eq("test@example.com")).
    FirstOrCreate(ctx, &models.User{
        Email: "test@example.com",
        Name:  "New User",
    })

if err != nil {
    return err
}

// user could be existing user or newly created user
fmt.Println("User ID:", user.ID)

func (*Repository[T]) Query 

func (r *Repository[T]) Query() *QueryBuilder[T]

Query returns a QueryBuilder for building complex queries. This is the starting point for building queries, supports method chaining.

Returns:

  • *QueryBuilder[T]: Query builder

Query features:

  • Automatically applies soft delete filter (if model supports)
  • Supports conditions, sorting, pagination, aggregation
  • Supports relation preloading
  • Supports subqueries

Example: 

// Basic query
users, err := userRepo.Query().Find(ctx)

// Conditional query
activeUsers, err := userRepo.Query().
    Where(generated.User.Status.Eq("active")).
    OrderBy(generated.User.CreatedAt.Desc()).
    Find(ctx)

// Paginated query
users, err := userRepo.Query().
    Limit(10).
    Offset(20).
    Find(ctx)

// Aggregation query
count, err := userRepo.Query().
    Where(generated.User.Status.Eq("active")).
    Count(ctx)

func (*Repository[T]) Restore added in v0.2.0 

func (r *Repository[T]) Restore(ctx context.Context, id any) error

Restore restores a soft-deleted record by clearing the soft delete marker. Returns an error if the model doesn't support soft delete.

Parameters:

  • ctx: Context, supports cancellation and timeout
  • id: Record's primary key value

Returns:

  • error: Restore error, returns error if model doesn't support soft delete

Note:

  • Only effective for models supporting soft delete
  • Scope conditions will be combined with primary key condition
  • If record was not deleted, operation still succeeds (no effect)

Example: 

// Restore deleted user
if err := userRepo.Restore(ctx, userID); err != nil {
    return err
}

// Now user can be queried normally
user, err := userRepo.FindOne(ctx, userID)

func (*Repository[T]) Unscoped added in v0.2.0 

func (r *Repository[T]) Unscoped() *Repository[T]

Unscoped returns a new Repository instance that bypasses soft delete. When unscoped is set to true, Delete() and DeleteModel() will perform hard delete even if the model supports soft delete.

Example: 

err := userRepo.Unscoped().Delete(ctx, userID)

func (*Repository[T]) Update 

func (r *Repository[T]) Update(ctx context.Context, model *T) error

Update updates a record in the database. Locates record by model's primary key, updates all updatable fields.

Operation flow:

  1. Trigger BeforeUpdate hook (if model implements BeforeUpdateInterface)
  2. Extract update data from model (via schema.UpdateMap)
  3. Build UPDATE statement with primary key condition
  4. Apply all scope conditions (set via Where)
  5. Execute update
  6. Trigger AfterUpdate hook (if model implements AfterUpdateInterface)

Parameters:

  • ctx: Context, supports cancellation and timeout
  • model: Model instance pointer, must contain valid primary key value

Returns:

  • error: Update error or hook error

Note:

  • Model must have valid primary key value
  • Scope conditions (Where) will be combined with primary key condition
  • Empty UpdateMap will result in UPDATE with no actual changes

Example: 

// Basic update
user.Name = "New Name"
if err := userRepo.Update(ctx, user); err != nil {
    return err
}

// Update with scope
user.Status = "archived"
if err := userRepo.Where(generated.User.Status.Eq("inactive")).Update(ctx, user); err != nil {
    return err
}

func (*Repository[T]) UpdateColumns 

func (r *Repository[T]) UpdateColumns(ctx context.Context, id any, assignments ...clause.Assignment) error

UpdateColumns updates specific columns for a record identified by id. This allows for partial updates without loading the entire record.

Difference from Update():

  • Update(): Requires complete model instance, updates all fields
  • UpdateColumns(): Only needs ID, updates specified fields

Parameters:

  • ctx: Context, supports cancellation and timeout
  • id: Record's primary key value
  • assignments: Column assignment list (column = value)

Returns:

  • error: Update error

Note:

  • Empty assignments will immediately return nil (no-op)
  • Does not trigger lifecycle hooks (no complete model instance)
  • Scope conditions will be combined with primary key condition

Example: 

// Update single field
err := userRepo.UpdateColumns(ctx, userID,
    clause.Assignment{
        Column: generated.User.Status.Column(),
        Value:  "active",
    },
)

// Update multiple fields
err := userRepo.UpdateColumns(ctx, userID,
    clause.Assignment{Column: generated.User.Name.Column(), Value: "New Name"},
    clause.Assignment{Column: generated.User.UpdatedAt.Column(), Value: time.Now()},
)

// Conditional update with scope
err := userRepo.
    Where(generated.User.Status.Eq("pending")).
    UpdateColumns(ctx, userID,
        clause.Assignment{Column: generated.User.Status.Column(), Value: "processed"},
    )

func (*Repository[T]) Upsert 

func (r *Repository[T]) Upsert(ctx context.Context, model *T, opts ...UpsertOption) error

Upsert inserts or updates a record. By default, it uses the Primary Key as the conflict target and updates all other columns. You can customize this utilizing OnConflict() and DoUpdate() options.

Database dialect differences:

  • MySQL: ON DUPLICATE KEY UPDATE
  • PostgreSQL: ON CONFLICT (...) DO UPDATE SET
  • SQLite: ON CONFLICT (...) DO UPDATE SET

Operation flow:

  1. Trigger BeforeCreate hook
  2. Determine conflict columns (default is primary key)
  3. Determine update columns (default is all non-conflict columns)
  4. Build INSERT ... ON CONFLICT statement
  5. Execute statement
  6. Trigger AfterCreate hook

Parameters:

  • ctx: Context, supports cancellation and timeout
  • model: Model instance pointer
  • opts: Optional configuration (OnConflict, DoUpdate)

Returns:

  • error: Insert/update error or hook error

Example: 

// Basic usage (use primary key to detect conflict)
err := userRepo.Upsert(ctx, user)

// Use unique constraint to detect conflict
err := userRepo.Upsert(ctx, user,
    sqlc.OnConflict(generated.User.Email),
)

// Specify columns to update
err := userRepo.Upsert(ctx, user,
    sqlc.OnConflict(generated.User.Email),
    sqlc.DoUpdate(generated.User.Name, generated.User.LastLoginAt),
)

func (*Repository[T]) Where 

func (r *Repository[T]) Where(conds ...clause.Expression) *Repository[T]

Where returns a new Repository instance with appended conditions. This allows method chaining, e.g., repo.Where(cond).Update(...)

Important: This method returns a new Repository instance, not modifying the original instance. This ensures Repository immutability and thread safety.

Parameters:

  • conds: Query condition expressions (variadic)

Returns:

  • *Repository[T]: New Repository instance with appended conditions

Use cases:

  • Conditional update: repo.Where(active.Eq(true)).Update(ctx, user)
  • Conditional delete: repo.Where(old.Eq(true)).Delete(ctx, id)
  • Batch operations: repo.Where(status.Eq("pending")).UpdateColumns(ctx, nil, ...)

Example: 

// Conditional update
affected, err := userRepo.
    Where(generated.User.Status.Eq("inactive")).
    Where(generated.User.LastLoginAt.Lt(time.Now().AddDate(0, -6, 0))).
    UpdateColumns(ctx, nil,
        clause.Assignment{Column: generated.User.Status.Column(), Value: "archived"},
    )

// Conditional delete
err := orderRepo.
    Where(generated.Order.Status.Eq("cancelled")).
    Delete(ctx, orderID)

type SQLiteDialect 

type SQLiteDialect struct{}

SQLiteDialect implements SQLite database dialect.

SQLite features:

  • Uses ? as placeholder
  • Uses ON CONFLICT ... DO UPDATE syntax for Upsert (version 3.24+)
  • Requires explicitly specifying conflict columns
  • Uses excluded table to reference proposed insert values (lowercase)

Usage example: 

session := sqlc.NewSession(db, sqlc.SQLiteDialect{})

Note:

  • Upsert requires SQLite 3.24+ version
  • excluded table name is lowercase (different from PostgreSQL)
  • Supports DO NOTHING
  • Commonly used in testing and development environments

func (*SQLiteDialect) Name 

func (d *SQLiteDialect) Name() string

Name returns the SQLite dialect name.

func (*SQLiteDialect) PlaceholderFormat 

func (d *SQLiteDialect) PlaceholderFormat() sq.PlaceholderFormat

PlaceholderFormat returns SQLite's placeholder format (?).

func (*SQLiteDialect) UpsertClause 

func (d *SQLiteDialect) UpsertClause(tableName string, conflictCols []string, updateCols []string) string

UpsertClause generates SQLite's Upsert clause. SQLite uses ON CONFLICT ... DO UPDATE syntax (version 3.24+).

Syntax format: 

ON CONFLICT (conflict_columns) DO UPDATE SET col1=excluded.col1, col2=excluded.col2
ON CONFLICT (conflict_columns) DO NOTHING

SQLite features:

  • Requires explicitly specifying conflict columns
  • excluded table references proposed insert values (lowercase)
  • Supports DO NOTHING
  • Requires SQLite 3.24+ version

Parameters:

  • tableName: Table name (not used by SQLite)
  • conflictCols: Conflict detection columns
  • updateCols: Columns to update

Returns:

  • string: ON CONFLICT clause

Example: 

dialect.UpsertClause("users", []string{"email"}, []string{"name", "updated_at"})
// Returns: "ON CONFLICT (email) DO UPDATE SET name=excluded.name,updated_at=excluded.updated_at"

type Schema 

type Schema[T any] interface {
	// TableName returns the database table name for the model.
	//
	// Returns:
	//   - string: Table name (e.g., "users", "orders")
	//
	// Example:
	//   func (s UserSchema) TableName() string { return "users" }
	TableName() string

	// SelectColumns returns the list of column names to select in queries.
	// These columns will be used in SELECT statements and result scanning.
	//
	// Returns:
	//   - []string: List of column names (e.g., ["id", "email", "name"])
	//
	// Note:
	//   - Column names should match database column names
	//   - Column names should match model's db tags
	//   - Usually doesn't include auto-timestamp columns like created_at, updated_at
	//
	// Example:
	//   func (s UserSchema) SelectColumns() []string {
	//       return []string{"id", "email", "name", "age"}
	//   }
	SelectColumns() []string

	// InsertRow extracts insert data from a model instance.
	// Used in Create() and BatchCreate() operations.
	//
	// Parameters:
	//   - *T: Model instance pointer
	//
	// Returns:
	//   - []string: List of column names to insert
	//   - []any: List of corresponding column values
	//
	// Note:
	//   - Usually doesn't include auto-increment primary key column
	//   - Can include default values or computed fields
	//   - Column and value order must match
	//
	// Example:
	//   func (s UserSchema) InsertRow(user *User) ([]string, []any) {
	//       return []string{"email", "name", "age"},
	//              []any{user.Email, user.Name, user.Age}
	//   }
	InsertRow(*T) ([]string, []any)

	// UpdateMap generates update mapping from a model instance.
	// Used in Update() operations.
	//
	// Parameters:
	//   - *T: Model instance pointer
	//
	// Returns:
	//   - map[string]any: Mapping from column names to new values
	//
	// Note:
	//   - Usually doesn't include primary key column
	//   - Can include auto-update fields like updated_at
	//   - Empty map results in UPDATE with no actual changes
	//
	// Example:
	//   func (s UserSchema) UpdateMap(user *User) map[string]any {
	//       return map[string]any{
	//           "email": user.Email,
	//           "name":  user.Name,
	//           "age":   user.Age,
	//       }
	//   }
	UpdateMap(*T) map[string]any

	// PK extracts primary key information from a model instance.
	// Used to build primary key query conditions.
	//
	// Parameters:
	//   - *T: Model instance pointer (can be nil, only used to get column info)
	//
	// Returns:
	//   - PK: Primary key equality expression (containing column name and value)
	//
	// Note:
	//   - When parameter is nil, returned PK should contain correct column info, but value can be zero
	//   - Used for operations like First(), Last() that need primary key column info
	//
	// Example:
	//   func (s UserSchema) PK(user *User) PK {
	//       if user == nil {
	//           return clause.Eq{Column: clause.Column{Name: "id"}}
	//       }
	//       return clause.Eq{
	//           Column: clause.Column{Name: "id"},
	//           Value:  user.ID,
	//       }
	//   }
	PK(*T) PK

	// SetPK sets the primary key value on a model instance.
	// Used to backfill ID after insert for auto-increment primary keys.
	//
	// Parameters:
	//   - m: Model instance pointer
	//   - val: Primary key value (auto-increment ID returned from database)
	//
	// Example:
	//   func (s UserSchema) SetPK(user *User, val int64) {
	//       user.ID = val
	//   }
	SetPK(m *T, val int64)

	// AutoIncrement indicates whether the table uses auto-increment primary key.
	//
	// Returns:
	//   - bool: true means auto-increment primary key, false means manual assignment
	//
	// Behavior:
	//   - true: Create() will call SetPK() to backfill ID
	//   - false: Create() won't attempt to get and set ID
	//
	// Example:
	//   func (s UserSchema) AutoIncrement() bool { return true }
	AutoIncrement() bool

	// SoftDeleteColumn returns the soft delete column name.
	// Returns "" if the model does not support soft delete.
	SoftDeleteColumn() string

	// SoftDeleteValue returns the value to set on the soft delete column when deleting.
	// For *time.Time -> time.Now(), for int64 -> time.Now().Unix(), etc.
	// Returns nil if soft delete is not supported.
	SoftDeleteValue() any

	// SetDeletedAt sets the deletion marker on the model instance.
	// No-op if soft delete is not supported.
	SetDeletedAt(m *T)
}

Schema defines how to map model T to a database table and back. This is the core interface of sqlc ORM, all models need to implement this interface.

This interface is usually automatically implemented by the code generator, developers don't need to write it manually. The code generator parses model struct tags (e.g., `db:"column_name"`) and generates the implementation.

Types implementing this interface must provide the following functionality:

  • Table metadata (table name)
  • Read operations (which columns to select)
  • Write operations (how to insert and update)
  • Primary key management (how to get and set primary key)

Usage example (generated by code generator): 

type UserSchema struct{}

func (s UserSchema) TableName() string {
    return "users"
}

func (s UserSchema) SelectColumns() []string {
    return []string{"id", "email", "name", "created_at"}
}

func (s UserSchema) InsertRow(user *User) ([]string, []any) {
    return []string{"email", "name"},
           []any{user.Email, user.Name}
}

func (s UserSchema) UpdateMap(user *User) map[string]any {
    return map[string]any{
        "email": user.Email,
        "name":  user.Name,
    }
}

func (s UserSchema) PK(user *User) PK {
    return clause.Eq{
        Column: clause.Column{Name: "id"},
        Value:  user.ID,
    }
}

func (s UserSchema) SetPK(user *User, val int64) {
    user.ID = val
}

func (s UserSchema) AutoIncrement() bool {
    return true
}

func LoadSchema 

func LoadSchema[T any]() Schema[T]

LoadSchema loads the registered Schema for a model. Panics if the type is not registered.

Type parameter:

  • T: Model type

Returns:

  • Schema[T]: Schema implementation for the model

Panics:

  • panic: If type is not registered

Usage scenarios:

  • Repository initialization
  • Query building
  • Relation loading

Example: 

schema := sqlc.LoadSchema[models.User]()
tableName := schema.TableName()

type Session 

type Session struct {
	// contains filtered or unexported fields
}

Session manages the database connection and current transaction state. It is the core component of sqlc ORM, responsible for:

  • Database connection management
  • Transaction lifecycle management
  • SQL dialect adaptation
  • Observability integration (logging, tracing, metrics)

Session supports two modes:

  • Regular mode: executor points to *sqlx.DB
  • Transaction mode: executor points to *sqlx.Tx

Usage example: 

// Create session
session := sqlc.NewSession(db, sqlc.MySQL{})

// Regular query
users, err := userRepo.Query().Find(ctx)

// Transaction operation
err := session.Transaction(ctx, func(txSession *Session) error {
    if err := userRepo.WithSession(txSession).Create(ctx, user); err != nil {
        return err // Auto rollback
    }
    return nil // Auto commit
})

func NewSession 

func NewSession(db *sql.DB, dialect Dialect, opts ...SessionOption) *Session

NewSession creates a new database session. This is the entry point for using sqlc ORM.

Parameters:

  • db: Standard library *sql.DB connection pool
  • dialect: Database dialect (MySQLDialect/PostgreSQLDialect/SQLiteDialect)
  • opts: Optional session configuration options (logging, tracing, metrics, etc.)

Returns:

  • *Session: Initialized session instance

Example: 

// Basic usage
session := sqlc.NewSession(db, sqlc.MySQL{})

// With logging
session := sqlc.NewSession(db, sqlc.MySQL{},
    sqlc.WithLogger(slog.Default()),
    sqlc.WithQueryLogging(true),
)

// With tracing and metrics
session := sqlc.NewSession(db, sqlc.PostgreSQLDialect{},
    sqlc.WithDefaultTracer(),
    sqlc.WithDefaultMeter(),
)

func (*Session) Begin 

func (s *Session) Begin(ctx context.Context) (*Session, error)

Begin starts a new transaction. Returns a new Session instance with the executor being the transaction object.

Note: You need to manually call Commit() or Rollback() after use. For automatic transaction management, use the Transaction() method instead.

Parameters:

  • ctx: Context supporting cancellation and timeout

Returns:

  • *Session: New session instance bound to the transaction
  • error: Error starting transaction

Example: 

txSession, err := session.Begin(ctx)
if err != nil {
    return err
}
defer txSession.Rollback() // Safety measure, rollback on committed transaction is no-op

// Execute transaction operations...

if err := txSession.Commit(); err != nil {
    return err
}

func (*Session) Commit 

func (s *Session) Commit() error

Commit commits the current transaction. Only effective in transaction mode (after calling Begin()).

Returns:

  • error: Commit error, returns sql.ErrTxDone if not in a transaction

Example: 

txSession, _ := session.Begin(ctx)
// ... execute operations
if err := txSession.Commit(); err != nil {
    log.Error("commit failed", "error", err)
}

func (*Session) Exec 

func (s *Session) Exec(ctx context.Context, query string, args ...any) (sql.Result, error)

Exec executes a SQL statement that doesn't return rows (INSERT/UPDATE/DELETE). Suitable for data modification operations.

Parameters:

  • ctx: Context supporting cancellation and timeout
  • query: SQL statement (using placeholders)
  • args: Statement parameters

Returns:

  • sql.Result: Result containing affected rows and last insert ID
  • error: Execution error

Example: 

result, err := session.Exec(ctx,
    "UPDATE users SET name = ? WHERE id = ?",
    "New Name", 1,
)
if err != nil {
    return err
}
rowsAffected, _ := result.RowsAffected()

func (*Session) Get 

func (s *Session) Get(ctx context.Context, dest any, query string, args ...any) error

Get executes a query and scans a single row result into a struct. This is the recommended way to query a single record and map to a model.

Parameters:

  • ctx: Context supporting cancellation and timeout
  • dest: Pointer to target struct (e.g., *User)
  • query: SQL query statement (using placeholders)
  • args: Query parameters

Returns:

  • error: Query or scan error (sql.ErrNoRows indicates not found)

Example: 

var user User
err := session.Get(ctx, &user,
    "SELECT * FROM users WHERE id = ?",
    1,
)
if err == sql.ErrNoRows {
    // User not found
}

func (*Session) Query 

func (s *Session) Query(ctx context.Context, query string, args ...any) (*sql.Rows, error)

Query executes a SQL query that returns multiple rows. Suitable for scenarios requiring iteration over large result sets.

Parameters:

  • ctx: Context supporting cancellation and timeout
  • query: SQL query statement (using placeholders)
  • args: Query parameters

Returns:

  • *sql.Rows: Query result set, caller must call Close()
  • error: Query error

Example: 

rows, err := session.Query(ctx, "SELECT * FROM users WHERE age > ?", 18)
if err != nil {
    return err
}
defer rows.Close()

for rows.Next() {
    var user User
    if err := rows.Scan(&user.ID, &user.Name); err != nil {
        return err
    }
    // Process user
}

func (*Session) QueryRow 

func (s *Session) QueryRow(ctx context.Context, query string, args ...any) *sql.Row

QueryRow executes a SQL query expecting at most one row. The returned *sql.Row needs to call Scan() method to retrieve data.

Note: Since actual execution happens when Scan() is called, this method cannot provide complete observability (execution duration, error statistics). For complete observability, use the Get() method instead.

Parameters:

  • ctx: Context supporting cancellation and timeout
  • query: SQL query statement (using placeholders)
  • args: Query parameters

Returns:

  • *sql.Row: Single row result

Example: 

var name string
err := session.QueryRow(ctx, "SELECT name FROM users WHERE id = ?", 1).Scan(&name)
if err == sql.ErrNoRows {
    // Record not found
}

func (*Session) Rollback 

func (s *Session) Rollback() error

Rollback rolls back the current transaction. Only effective in transaction mode (after calling Begin()).

Returns:

  • error: Rollback error, returns sql.ErrTxDone if not in a transaction

Example: 

txSession, _ := session.Begin(ctx)
// ... execute operations
if err := txSession.Rollback(); err != nil {
    log.Error("rollback failed", "error", err)
}

func (*Session) Select 

func (s *Session) Select(ctx context.Context, dest any, query string, args ...any) error

Select executes a query and scans results into a struct slice. This is the recommended way to query multiple rows and map to models.

Parameters:

  • ctx: Context supporting cancellation and timeout
  • dest: Pointer to target slice (e.g., *[]*User)
  • query: SQL query statement (using placeholders)
  • args: Query parameters

Returns:

  • error: Query or scan error

Example: 

var users []*User
err := session.Select(ctx, &users,
    "SELECT * FROM users WHERE age > ? ORDER BY name",
    18,
)

func (*Session) Transaction 

func (s *Session) Transaction(ctx context.Context, fn func(txSession *Session) error) (err error)

Transaction executes a function within a transaction with automatic commit and rollback. This is the recommended way to execute transactions, providing:

  • Automatic commit: Commits automatically when function returns successfully
  • Automatic rollback: Rolls back automatically when function returns error or panics
  • Nesting support: If already in a transaction, executes function directly (no nested transaction)

Parameters:

  • ctx: Context supporting cancellation and timeout
  • fn: Transaction function, receives transaction session and returns error

Returns:

  • error: Function error or commit error

Example: 

err := session.Transaction(ctx, func(txSession *Session) error {
    // Create user in transaction
    userRepo := sqlc.NewRepository[models.User](txSession)
    if err := userRepo.Create(ctx, user); err != nil {
        return err // Auto rollback
    }

    // Create order in transaction
    orderRepo := sqlc.NewRepository[models.Order](txSession)
    if err := orderRepo.Create(ctx, order); err != nil {
        return err // Auto rollback
    }

    return nil // Auto commit
})

if err != nil {
    log.Error("transaction failed", "error", err)
}

type SessionOption 

type SessionOption func(*Session)

SessionOption defines a function for configuring Session. Uses functional options pattern to provide flexible configuration.

Advantages:

  • Optional configuration: Unneeded configurations can be omitted
  • Composable: Multiple options can be combined
  • Extensible: Easy to add new configuration options
  • Readable: Configuration intent is clear

Example: 

session := sqlc.NewSession(db, sqlc.MySQL{},
    sqlc.WithLogger(logger),
    sqlc.WithQueryLogging(true),
    sqlc.WithSlowQueryThreshold(100*time.Millisecond),
)

func WithDefaultMeter 

func WithDefaultMeter() SessionOption

WithDefaultMeter creates a metrics collector using the global OpenTelemetry MeterProvider. This is the simplest way to enable metrics collection.

Usage example: 

// Initialize OpenTelemetry (must be done at program startup)
mp := metricsdk.NewMeterProvider(...)
otel.SetMeterProvider(mp)

// Enable metrics collection when creating Session
session := sqlc.NewSession(db, sqlc.MySQL{},
    sqlc.WithDefaultMeter(),
)

Collected metrics:

  • sqlc.query.count: Total query count

    Unit: {query}

    Attributes: db.operation, db.system

  • sqlc.query.duration: Query latency distribution

    Unit: ms

    Attributes: db.operation, db.system

    Bucket boundaries: 1, 5, 10, 25, 50, 100, 250, 500, 1000, 2500, 5000

  • sqlc.query.errors: Query error count

    Unit: {error}

    Attributes: db.operation, db.system

Note:

  • Requires global MeterProvider to be configured first
  • Metric names are prefixed with "sqlc."

func WithDefaultTracer 

func WithDefaultTracer() SessionOption

WithDefaultTracer creates a tracer using the global OpenTelemetry TracerProvider. This is the simplest way to enable tracing.

Usage example: 

// Initialize OpenTelemetry (must be done at program startup)
tp := tracesdk.NewTracerProvider(...)
otel.SetTracerProvider(tp)

// Enable tracing when creating Session
session := sqlc.NewSession(db, sqlc.MySQL{},
    sqlc.WithDefaultTracer(),
)

Note:

  • Requires global TracerProvider to be configured first
  • Tracer name is "github.com/arllen133/sqlc"

func WithLogger 

func WithLogger(logger *slog.Logger) SessionOption

WithLogger sets the logger for the session. When enabled, query execution status, slow queries, and errors are logged.

Parameter:

  • logger: slog.Logger instance, cannot be nil

Usage example: 

// Use default logger
session := sqlc.NewSession(db, sqlc.MySQL{},
    sqlc.WithLogger(slog.Default()),
)

// Use custom logger
logger := slog.New(slog.NewJSONHandler(os.Stdout, &slog.HandlerOptions{
    Level: slog.LevelDebug,
}))
session := sqlc.NewSession(db, sqlc.MySQL{},
    sqlc.WithLogger(logger),
)

Note:

  • Only sets the logger, doesn't automatically log all queries
  • Use with WithQueryLogging(true) to log all queries
  • Slow queries and error queries are always logged

func WithMeter 

func WithMeter(meter metric.Meter) SessionOption

WithMeter sets the OpenTelemetry metrics collector. When enabled, collects query count, latency, errors, and other metrics.

Parameter:

  • meter: metric.Meter instance

Usage example: 

// Use custom meter
meter := otel.Meter("my-service")
session := sqlc.NewSession(db, sqlc.MySQL{},
    sqlc.WithMeter(meter),
)

Collected metrics:

  • sqlc.query.count: Total query count (grouped by operation type)
  • sqlc.query.duration: Query latency distribution (milliseconds)
  • sqlc.query.errors: Query error count (grouped by operation type)

Note:

  • Requires OpenTelemetry SDK configuration to take effect
  • Recommend using WithDefaultMeter() for simpler configuration

func WithQueryLogging 

func WithQueryLogging(enabled bool) SessionOption

WithQueryLogging controls whether to log all queries. When enabled, all queries are logged at Debug level.

Parameter:

  • enabled: true to enable, false to disable

Usage example: 

// Enable query logging (debug mode)
session := sqlc.NewSession(db, sqlc.MySQL{},
    sqlc.WithLogger(slog.Default()),
    sqlc.WithQueryLogging(true),
)

// Disable query logging (production mode)
session := sqlc.NewSession(db, sqlc.MySQL{},
    sqlc.WithLogger(slog.Default()),
    sqlc.WithQueryLogging(false),
)

Log levels:

  • Debug: All queries (enabled = true)
  • Warn: Slow queries (always logged)
  • Error: Failed queries (always logged)

Note:

  • Enabling this generates a large volume of logs, may impact performance
  • For production, recommend disabling or using sampling
  • Requires Logger configuration to take effect

func WithSlowQueryThreshold 

func WithSlowQueryThreshold(d time.Duration) SessionOption

WithSlowQueryThreshold sets the slow query threshold. Queries exceeding this threshold are logged at warning level.

Parameter:

  • d: Slow query threshold (time.Duration)

Usage example: 

// Set 100ms as slow query threshold
session := sqlc.NewSession(db, sqlc.MySQL{},
    sqlc.WithLogger(slog.Default()),
    sqlc.WithSlowQueryThreshold(100*time.Millisecond),
)

// Set 1s as slow query threshold
session := sqlc.NewSession(db, sqlc.PostgreSQL{},
    sqlc.WithLogger(slog.Default()),
    sqlc.WithSlowQueryThreshold(time.Second),
)

Note:

  • Requires Logger configuration to log slow queries
  • Slow queries are logged at Warn level
  • Default threshold is 200ms

func WithTracer 

func WithTracer(tracer trace.Tracer) SessionOption

WithTracer sets the OpenTelemetry tracer. When enabled, all database operations create trace spans.

Parameter:

  • tracer: trace.Tracer instance

Usage example: 

// Use custom tracer
tracer := otel.Tracer("my-service")
session := sqlc.NewSession(db, sqlc.MySQL{},
    sqlc.WithTracer(tracer),
)

Trace data includes:

  • Span name: Operation type (e.g., "sqlc.Query", "sqlc.Exec")
  • Span attributes: SQL statement, operation type, database type
  • Span status: Success or failure
  • Span events: Error information (if failed)

Note:

  • Requires OpenTelemetry SDK configuration to take effect
  • Recommend using WithDefaultTracer() for simpler configuration

type UpsertOption 

type UpsertOption func(*upsertConfig)

UpsertOption defines configuration function for Upsert operation. Uses functional options pattern to provide flexible configuration.

func DoUpdate 

func DoUpdate(columns ...clause.Columnar) UpsertOption

DoUpdate specifies which columns to update when a conflict occurs. If not specified, all model columns (except conflict columns) are updated.

Parameters:

  • columns: Columns to update (implements clause.Columnar interface)

Returns:

  • UpsertOption: Configuration function

Default behavior:

  • If this option is not called, all non-conflict columns are updated

Example: 

// Only update name and updated_at when conflict occurs
err := userRepo.Upsert(ctx, user,
    sqlc.OnConflict(generated.User.Email),
    sqlc.DoUpdate(generated.User.Name, generated.User.UpdatedAt),
)

// Don't update any columns when conflict occurs (DO NOTHING)
err := userRepo.Upsert(ctx, user,
    sqlc.OnConflict(generated.User.Email),
    sqlc.DoUpdate(), // Empty parameters
)

func OnConflict 

func OnConflict(columns ...clause.Columnar) UpsertOption

OnConflict specifies columns to check for conflict (e.g., PRIMARY KEY or UNIQUE constraints)

Parameters:

  • columns: Conflict detection columns (implements clause.Columnar interface)

Returns:

  • UpsertOption: Configuration function

Default behavior:

  • If this option is not called, primary key column is used by default

Example: 

// Use email unique constraint to detect conflict
err := userRepo.Upsert(ctx, user,
    sqlc.OnConflict(generated.User.Email),
)

// Use composite unique constraint
err := orderRepo.Upsert(ctx, order,
    sqlc.OnConflict(generated.Order.UserID, generated.Order.ProductID),
)

Source Files

View all Source files

Directories

Path Synopsis
cmd
sqlcli command
sqlcli/generator
examples
01_basic_crud command
01_basic_crud/models
01_basic_crud/models/generated
02_relationships command
02_relationships/models
02_relationships/models/generated
03_soft_delete command
03_soft_delete/models
03_soft_delete/models/generated
04_transactions command
04_transactions/models
04_transactions/models/generated
05_json_type command
05_json_type/models
05_json_type/models/generated
06_hooks command
06_hooks/models
06_hooks/models/generated
json

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.