sqlr - SQL Repository

The sqlr package provides a generic, type-safe repository layer built on top of sqlc. It offers CRUD operations, relationship management, eager loading via joins and preloads, and transaction support — all using Go generics for compile-time type safety.

Getting Started

Add the dependency to your Go module:

go get github.com/gosoline-project/sqlr@v0.2.0

Then import the package in your Go code:

import "github.com/gosoline-project/sqlr"

Configuration

The sqlr package uses sqlc under the hood for database connections. Configure your database using the same sqlc configuration key described in the sqlc documentation:

config.dist.yml
sqlc:
  default:
    driver: mysql
    uri:
      host: 127.0.0.1
      port: 3306
      user: root
      password: gosoline
      database: blog
    migrations:
      enabled: true
      path: migrations

Defining Entities

Entities are Go structs that map to database tables. Embed sqlr.Entity[K] to get a primary key (Id), and automatic timestamp fields (CreatedAt, UpdatedAt):

main.go
type Author struct {
  sqlr.Entity[int64]
  Name  string
  Email string
  Posts []Post // HasMany
}

type Post struct {
  sqlr.Entity[int64]
  AuthorID int64
  Title    string
  Body     string
  Status   string
  Author   Author `db:"-,belongsTo:author_id"` // BelongsTo
  Tags     []Tag  `db:"-,many2many:"`          // ManyToMany
}

type Tag struct {
  sqlr.Entity[int64]
  Name string
}

Entity Base Struct

The sqlr.Entity[K] base struct provides:

Field	Tag	Description
`Id`	`db:"id,primaryKey"`	Primary key, auto-increment for integer types
`CreatedAt`	`db:"created_at,autoCreateTime"`	Set automatically on insert
`UpdatedAt`	`db:"updated_at,autoUpdateTime"`	Set automatically on insert and update

All entities must implement the Entitier[K] interface (satisfied automatically by embedding Entity[K]):

type Entitier[K KeyTypes] interface {
    GetId() K
    GetUpdatedAt() time.Time
    GetCreatedAt() time.Time
}

Struct Tags

Column mappings and behavior are controlled via the db struct tag. The db tag is optional for public fields — when omitted, the field name is converted to a column name using SchemaNameTransformer (default: snake_case). Public struct and slice-of-struct fields without a db tag are auto-detected as relationships (see Auto-detected Relationships). To explicitly exclude a public field from all mapping, use db:"-" (without any options after the comma).

Tag	Description
`db:"column_name"`	Maps the field to a database column
`db:"column_name,primaryKey"`	Marks the field as the primary key
`db:"column_name,autoCreateTime"`	Auto-sets the field to `time.Now()` on insert
`db:"column_name,autoUpdateTime"`	Auto-sets the field to `time.Now()` on insert and update
`db:"-,foreignKey:col"`	Defines a HasOne or HasMany relationship (see Relationships)
`db:"-,belongsTo:col"`	Defines a BelongsTo relationship
`db:"-,many2many:table"`	Defines a ManyToMany relationship
`db:"-,many2many:table,parentKey:col"`	Overrides the join table column that references the parent entity's PK
`db:"-,many2many:table,relatedKey:col"`	Overrides the join table column that references the related entity's PK
`db:"-"`	Explicitly excludes a public field from column and relationship mapping

Excluding Fields

To exclude a public field from all mapping (no column, no relationship), use db:"-":

Table Name Derivation

Table names are automatically derived from the struct type name by applying SchemaNameTransformer (default: snake_case conversion) and then pluralizing:

Struct Name	Table Name
`Author`	`authors`
`Post`	`posts`
`PostTag`	`posts_tags`

To override the default table name, implement the TableNamer interface:

type TableNamer interface {
    TableName() string
}

func (a Author) TableName() string {
    return "my_authors"
}

SchemaNameTransformer

SchemaNameTransformer is a package-level variable that controls how Go field names and type names are converted to database identifiers. It is used for:

Deriving column names for untagged public fields
Deriving table names from struct type names (before pluralization)
Deriving foreign key column names for auto-detected relationships
Deriving join table column names for ManyToMany relationships when no parentKey:/relatedKey: override is set

The default transformer is toSnakeCase (PascalCase/camelCase → snake_case). You can replace it at program startup before any repository is created:

import (
    "strings"
    "github.com/gosoline-project/sqlr"
)

func init() {
    // Use lowercase field names instead of snake_case
    sqlr.SchemaNameTransformer = strings.ToLower
}

The transformer must be set before any repository or schema parsing occurs, as schemas are cached after first use.

Supported Key Types

Primary keys can be any of these types (or their pointer variants):

bool | string | int | int64 | uint | uint64 | float32 | float64

Integer primary key types (int, int64, uint, uint64) are automatically treated as auto-increment — they are excluded from INSERT statements and their value is set from LastInsertId() after creation.

Relationships

Relationships can be declared in two ways: via auto-detection of untagged public fields (convention over configuration) or via explicit db struct tags (full control over every name).

HasOne

The foreign key lives on the related table and the field type is a single struct. HasOne cannot be auto-detected — a non-slice struct field with no db tag is always treated as BelongsTo instead. An explicit foreignKey: tag is always required.

Explicit Tag

type Author struct {
    sqlr.Entity[int64]
    Name    string  `db:"name"`
    Profile Profile `db:"-,foreignKey:author_id"`
}

type Profile struct {
    sqlr.Entity[int64]
    AuthorID int64  `db:"author_id"`
    Bio      string `db:"bio"`
}

Table schema

CREATE TABLE authors (
    id         BIGINT AUTO_INCREMENT PRIMARY KEY,
    name       VARCHAR(255) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE profiles (
    id         BIGINT AUTO_INCREMENT PRIMARY KEY,
    author_id  BIGINT NOT NULL,
    bio        TEXT NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (author_id) REFERENCES authors(id)
);

HasMany

The foreign key lives on the related table and the field type is a slice.

Auto-detected

A public slice-of-struct field with no db tag is automatically treated as HasMany. The foreign key column name on the related table is derived as SchemaNameTransformer(parentTypeName) + "_id".

type Author struct {
    sqlr.Entity[int64]
    Name  string
    Posts []Post // FK "author_id" derived on posts table
}

type Post struct {
    sqlr.Entity[int64]
    AuthorID int64
    Title    string
}

Explicit Tag

type Author struct {
    sqlr.Entity[int64]
    Name  string `db:"name"`
    Posts []Post `db:"-,foreignKey:author_id"`
}

type Post struct {
    sqlr.Entity[int64]
    AuthorID int64  `db:"author_id"`
    Title    string `db:"title"`
}

Table schema

CREATE TABLE authors (
    id         BIGINT AUTO_INCREMENT PRIMARY KEY,
    name       VARCHAR(255) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE posts (
    id         BIGINT AUTO_INCREMENT PRIMARY KEY,
    author_id  BIGINT NOT NULL,
    title      VARCHAR(255) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (author_id) REFERENCES authors(id)
);

BelongsTo

The foreign key lives on the current entity's table.

Auto-detected

A public non-slice struct field with no db tag (whose type is not a known value type such as time.Time) is automatically treated as BelongsTo. The foreign key column name on the current table is derived as SchemaNameTransformer(fieldName) + "_id".

type Post struct {
    sqlr.Entity[int64]
    AuthorID int64
    Title    string
    Author   Author // FK "author_id" derived on this table
}

type Author struct {
    sqlr.Entity[int64]
    Name string
}

Explicit Tag

type Post struct {
    sqlr.Entity[int64]
    AuthorID int64  `db:"author_id"`
    Title    string `db:"title"`
    Author   Author `db:"-,belongsTo:author_id"`
}

type Author struct {
    sqlr.Entity[int64]
    Name string `db:"name"`
}

Table schema

The database schema is the same as HasMany — BelongsTo is the inverse perspective of the same foreign key column:

CREATE TABLE authors (
    id         BIGINT AUTO_INCREMENT PRIMARY KEY,
    name       VARCHAR(255) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE posts (
    id         BIGINT AUTO_INCREMENT PRIMARY KEY,
    author_id  BIGINT NOT NULL,
    title      VARCHAR(255) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (author_id) REFERENCES authors(id)
);

ManyToMany

A join table is required and many2many: always needs an explicit db tag — ManyToMany relationships cannot be auto-detected.

The join table must have columns referencing the primary key of each side. By default these column names are derived as SchemaNameTransformer(EntityType) + "_id".

Auto-derived Join Table Name

Leave the many2many: value empty to have the join table name derived automatically from both entity table names, sorted alphabetically and joined with an underscore:

type Post struct {
    sqlr.Entity[int64]
    Title string
    Tags  []Tag `db:"-,many2many:"` // join table "posts_tags" auto-derived
}

type Tag struct {
    sqlr.Entity[int64]
    Name string
}

Explicit Tag

type Post struct {
    sqlr.Entity[int64]
    Title string `db:"title"`
    Tags  []Tag  `db:"-,many2many:posts_tags"`
}

type Tag struct {
    sqlr.Entity[int64]
    Name string `db:"name"`
}

Table schema

Both the auto-derived and explicit variants map to the same schema. The join table column names are derived as SchemaNameTransformer(EntityType) + "_id":

CREATE TABLE posts (
    id         BIGINT AUTO_INCREMENT PRIMARY KEY,
    title      VARCHAR(255) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE tags (
    id         BIGINT AUTO_INCREMENT PRIMARY KEY,
    name       VARCHAR(255) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE posts_tags (
    post_id BIGINT NOT NULL,
    tag_id  BIGINT NOT NULL,
    PRIMARY KEY (post_id, tag_id),
    FOREIGN KEY (post_id) REFERENCES posts(id),
    FOREIGN KEY (tag_id)  REFERENCES tags(id)
);

Overriding Join Table Column Names

Use parentKey: and relatedKey: to override the join table column names when they differ from the default convention:

type Article struct {
    sqlr.Entity[int64]
    Title  string  `db:"title"`
    Labels []Label `db:"-,many2many:article_labels,parentKey:art_id,relatedKey:lbl_id"`
}

type Label struct {
    sqlr.Entity[int64]
    Name string `db:"name"`
}

Table schema

The corresponding join table uses abbreviated column names that differ from the default SchemaNameTransformer convention (article_id / label_id):

CREATE TABLE article_labels (
    art_id BIGINT NOT NULL,
    lbl_id BIGINT NOT NULL,
    PRIMARY KEY (art_id, lbl_id),
    FOREIGN KEY (art_id) REFERENCES articles(id),
    FOREIGN KEY (lbl_id) REFERENCES labels(id)
);

Option	Description
`parentKey:col`	Join table column referencing the parent entity's primary key
`relatedKey:col`	Join table column referencing the related entity's primary key

type Author struct {
    sqlr.Entity[int64]
    Name     string `db:"name"`
    Internal string `db:"-"` // not mapped to any column
}

Creating the Repository

Create a repository using sqlr.NewRepository[K, E]() within a gosoline application. The type parameters specify the primary key type and the entity type:

main.go
authorRepo, err := sqlr.NewRepository[int64, Author](ctx, config, logger, "default")
if err != nil {
  return nil, fmt.Errorf("failed to create author repository: %w", err)
}

The last argument ("default" above) is the sqlc client name. It must match a key under the sqlc block in your configuration file — the repository uses that named client for all database operations. You can have multiple repositories pointing to different clients (e.g., "default", "analytics", "readonly") by passing different names.

The Repository interface provides:

Method	Description
`Create(ctx, entity)`	Inserts the entity and any populated association fields; sets auto-increment IDs and timestamps
`Read(ctx, id, opts...)`	Loads one entity by primary key, with optional joins/preloads
`Query(ctx, opts...)`	Loads entities matching query conditions
`Update(ctx, entity)`	Updates all fields of the entity
`Delete(ctx, id)`	Deletes the entity by primary key
`Close()`	Releases resources (prepared statements, etc.)

CRUD Operations

Create

Pass a pointer to an entity. Auto-increment IDs and timestamp fields are set automatically:

main.go
func (s *BlogService) createAuthor(ctx context.Context, name, email string) (*Author, error) {
  author := &Author{
    Name:  name,
    Email: email,
  }

  if err := s.authorRepo.Create(ctx, author); err != nil {
    return nil, fmt.Errorf("failed to create author: %w", err)
  }

  // author.Id is automatically set for auto-increment primary keys
  // author.CreatedAt and author.UpdatedAt are set via autoCreateTime/autoUpdateTime

  return author, nil
}

Create with Associations

Populate relationship fields on the entity before calling Create() and sqlr will automatically persist them in the correct order within a single transaction:

main.go
func (s *BlogService) createPost(ctx context.Context, authorId int64, title, body, status string, tags ...Tag) (*Post, error) {
  post := &Post{
    AuthorID: authorId,
    Title:    title,
    Body:     body,
    Status:   status,
    Tags:     tags,
  }

  // When Tags is populated, sqlr automatically inserts the tag rows and
  // the posts_tags join table entries within a single transaction.
  if err := s.postRepo.Create(ctx, post); err != nil {
    return nil, fmt.Errorf("failed to create post: %w", err)
  }

  return post, nil
}

The association save order is:

BelongsTo — related entity inserted first so the parent's FK column is set before the parent row is written.
Parent entity — the base row is inserted.
HasOne / HasMany — related entities are inserted with their FK pointing to the parent PK.
ManyToMany — related entities with zero PKs are inserted, then join table rows are created for all of them.

Entities with a non-zero primary key are treated as already-persisted: they are skipped for insertion but the FK or join table row is still created. Update and Delete only affect the base entity row and are not association-aware.

Read

Load a single entity by its primary key:

main.go
func (s *BlogService) readAuthor(ctx context.Context, id int64) (*Author, error) {
  author, err := s.authorRepo.Read(ctx, id)
  if err != nil {
    return nil, fmt.Errorf("failed to read author: %w", err)
  }

  return author, nil
}

Update

Modify the entity and pass it to Update(). The UpdatedAt timestamp is refreshed automatically:

main.go
func (s *BlogService) updatePostStatus(ctx context.Context, post *Post, status string) (*Post, error) {
  post.Status = status

  updated, err := s.postRepo.Update(ctx, post)
  if err != nil {
    return nil, fmt.Errorf("failed to update post: %w", err)
  }

  // updated.UpdatedAt is automatically refreshed via autoUpdateTime

  return updated, nil
}

Delete

Remove an entity by its primary key. Returns ErrNotFound if the entity doesn't exist:

main.go
func (s *BlogService) deleteTag(ctx context.Context, id int64) error {
  if err := s.tagRepo.Delete(ctx, id); err != nil {
    return fmt.Errorf("failed to delete tag: %w", err)
  }

  return nil
}

Query Operations

Use Query() with a QueryBuilderSelect to filter, sort, and paginate results:

main.go
func (s *BlogService) queryPublishedPosts(ctx context.Context) ([]Post, error) {
  posts, err := s.postRepo.Query(ctx, func(qb *sqlr.QueryBuilderSelect) {
    qb.Where(sqlc.Col("status").Eq("published")).
      OrderBy("created_at DESC").
      Limit(10).
      Offset(0)
  })
  if err != nil {
    return nil, fmt.Errorf("failed to query posts: %w", err)
  }

  return posts, nil
}

The QueryBuilderSelect supports:

Method	Description
`Where(condition, params...)`	Adds a WHERE condition (multiple calls are ANDed)
`OrderBy(cols...)`	Sets the ORDER BY clause
`Limit(n)`	Limits the number of results
`Offset(n)`	Skips the first n results
`GroupBy(cols...)`	Sets the GROUP BY clause
`Having(condition, params...)`	Adds a HAVING condition

WHERE conditions use the same sqlc.Col() expression API from the sqlc package:

qb.Where(sqlc.Col("status").Eq("published"))
qb.Where(sqlc.Col("age").Gt(18))
qb.Where(sqlc.And(sqlc.Col("a").Eq(1), sqlc.Col("b").Eq(2)))

Eager Loading with Preload

Use Preload() to load related entities in separate queries. Preloads support all relationship types: HasOne, HasMany, BelongsTo, and ManyToMany.

main.go
func (s *BlogService) queryPostsWithAuthor(ctx context.Context) ([]Post, error) {
  posts, err := s.postRepo.Query(ctx, func(qb *sqlr.QueryBuilderSelect) {
    qb.Where(sqlc.Col("status").Eq("published")).
      Preload("Author").
      Preload("Tags")
  })
  if err != nil {
    return nil, fmt.Errorf("failed to query posts: %w", err)
  }

  // Each post now has post.Author and post.Tags populated

  return posts, nil
}

Nested Preloads

Load nested relationships using dot-separated paths. Conditions on nested paths apply to the leaf relation only:

qb.Preload("Posts.Comments")
qb.Preload("Posts.Comments", sqlr.Condition("body != ?", ""))

Auto-Preload

Add the preload tag option to a relationship to automatically load it on every Read and Query — without requiring an explicit Preload() call:

type PostWithPreloads struct {
    sqlr.Entity[int64]
    AuthorID int64  `db:"author_id"`
    Title    string `db:"title"`
    Author   Author `db:"-,belongsTo:author_id,preload"`
    Tags     []Tag  `db:"-,many2many:posts_tags,preload"`
}

Auto-preloads are recursively discovered across nested relationships and merged with any explicit preloads (explicit preloads take precedence when both are present).

Preload Conditions

Pass conditions to Preload() to filter which related entities are loaded:

main.go
func (s *BlogService) queryPostsPreloadWithCondition(ctx context.Context) ([]Post, error) {
  posts, err := s.postRepo.Query(ctx, func(qb *sqlr.QueryBuilderSelect) {
    qb.Preload("Author").Preload("Tags", sqlr.Condition(sqlc.Col("name").NotEq("")))
  })
  if err != nil {
    return nil, fmt.Errorf("failed to query posts: %w", err)
  }

  return posts, nil
}

Eager Loading with Joins

Use LeftJoin(), InnerJoin(), RightJoin(), or CrossJoin() to load related entities via SQL JOINs. Joins support HasOne, HasMany, and BelongsTo relationships (ManyToMany and nested paths require Preload).

Joins on Query

main.go
func (s *BlogService) queryPostsWithAuthorJoin(ctx context.Context) ([]Post, error) {
  posts, err := s.postRepo.Query(ctx, func(qb *sqlr.QueryBuilderSelect) {
    qb.Where(sqlc.Col("status").Eq("published")).
      LeftJoin("Author")
  })
  if err != nil {
    return nil, fmt.Errorf("failed to query posts: %w", err)
  }

  // Each post now has post.Author populated via a SQL JOIN

  return posts, nil
}

Joins on Read

Joins can also be used with Read() to load relations alongside a single entity lookup:

main.go
func (s *BlogService) readPostWithAuthor(ctx context.Context, postId int64) (*Post, error) {
  post, err := s.postRepo.Read(ctx, postId, func(qb *sqlr.QueryBuilderRead) {
    qb.LeftJoin("Author")
  })
  if err != nil {
    return nil, fmt.Errorf("failed to read post with author: %w", err)
  }

  return post, nil
}

Join Conditions

Pass conditions to restrict the joined rows:

main.go
func (s *BlogService) queryPostsJoinWithCondition(ctx context.Context) ([]Post, error) {
  posts, err := s.postRepo.Query(ctx, func(qb *sqlr.QueryBuilderSelect) {
    qb.LeftJoin("Author", sqlr.Condition(sqlc.Col("name").Eq("Alice")))
  })
  if err != nil {
    return nil, fmt.Errorf("failed to query posts: %w", err)
  }

  return posts, nil
}

Error Handling

The sqlr.ErrNotFound sentinel error is returned when Read() or Delete() cannot find the requested entity:

author, err := repo.Read(ctx, id)
if errors.Is(err, sqlr.ErrNotFound) {
    // entity does not exist
}

Getting Started​

Configuration​

Defining Entities​

Entity Base Struct​

Struct Tags​

Excluding Fields​

Table Name Derivation​

SchemaNameTransformer​

Supported Key Types​

Relationships​

HasOne​

Explicit Tag​

HasMany​

Auto-detected​

Explicit Tag​

BelongsTo​

Auto-detected​

Explicit Tag​

ManyToMany​

Auto-derived Join Table Name​

Explicit Tag​

Overriding Join Table Column Names​

Creating the Repository​

CRUD Operations​

Create​

Create with Associations​

Read​

Update​

Delete​

Query Operations​

Eager Loading with Preload​

Nested Preloads​

Auto-Preload​

Preload Conditions​

Eager Loading with Joins​

Joins on Query​

Joins on Read​

Join Conditions​

Error Handling​

Getting Started

Configuration

Defining Entities

Entity Base Struct

Struct Tags

Excluding Fields

Table Name Derivation

SchemaNameTransformer

Supported Key Types

Relationships

HasOne

Explicit Tag

HasMany

Auto-detected

Explicit Tag

BelongsTo

Auto-detected

Explicit Tag

ManyToMany

Auto-derived Join Table Name

Explicit Tag

Overriding Join Table Column Names

Creating the Repository

CRUD Operations

Create

Create with Associations

Read

Update

Delete

Query Operations

Eager Loading with Preload

Nested Preloads

Auto-Preload

Preload Conditions

Eager Loading with Joins

Joins on Query

Joins on Read

Join Conditions

Error Handling