sqlrud

package module
v0.4.0 Latest Latest
Warning

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

 Go to latest Published: Jul 13, 2026 License: MIT Imports: 9 Imported by: 0

README

sqlrud

Small CRUD helper for database/sql-style applications.

sqlrud keeps query strings out of everyday CRUD code while using database/sql and sqlx underneath for execution, scanning, and placeholder binding.

Requirements

  • Go 1.22 or later

Install

go get github.com/yusukeyama/sqlrud

Model

Use db tags for column names and CRUD metadata. Mark primary keys with primary_key; add auto_increment when the database generates the value.

type User struct {
	ID    int64  `db:"id,auto_increment,primary_key"`
	Name  string `db:"name"`
	Email string `db:"email"`
}

func (User) TableName() string {
	return "users"
}

If TableName is omitted, the default table name is the snake_case struct name with s appended, such as User -> users.

Usage

PostgreSQL:

import _ "github.com/lib/pq"

db := sqlx.MustConnect("postgres", dsn)
client := sqlrud.New(db)

MySQL:

import _ "github.com/go-sql-driver/mysql"

db := sqlx.MustConnect("mysql", "user:pass@tcp(localhost:3306)/app?parseTime=true")
client := sqlrud.New(db)

Then use the client in the same way for either database:

var user User
user.ID = 1
err := client.First(ctx, &user)

var users []User
err = client.Find(
	ctx,
	&users,
	sqlrud.Where("Name", sqlrud.Like("Yu%")),
	sqlrud.OrderBy("ID", sqlrud.Desc),
	sqlrud.Limit(20),
)

err = client.Create(ctx, &User{Name: "Yusuke", Email: "y@example.com"})

user.Name = "New Name"
err = client.Update(ctx, &user)

err = client.CreateOrUpdate(ctx, &user)

err = client.Delete(ctx, &user)

First loads a record by primary key. Find accepts field names (Email) or column names (email) in conditions. Supported predicates are Eq, NotEq, Gt, Gte, Lt, Lte, Like, In, NotIn, IsNull, and IsNotNull.

Exported anonymous structs are treated as embedded model fields. Fields without a db tag use the snake_case field name as their column name, such as CreatedAt -> created_at.

Nullable columns

Use database/sql nullable types or pointer fields for nullable columns.

type User struct {
	ID        int64           `db:"id,auto_increment,primary_key"`
	Name      sql.NullString  `db:"name"`
	Age       sql.NullInt64   `db:"age"`
	Score     sql.NullFloat64 `db:"score"`
	Active    sql.NullBool    `db:"active"`
	BirthDate sql.NullTime    `db:"birth_date"`
}

Go 1.22 or later also supports generic nullable values with sql.Null[T].

type User struct {
	ID       int64            `db:"id,auto_increment,primary_key"`
	Nickname sql.Null[string] `db:"nickname"`
	Age      sql.Null[int64]  `db:"age"`
	Active   sql.Null[bool]   `db:"active"`
}

You can also use pointer fields when that better fits your model.

type User struct {
	ID     int64   `db:"id,auto_increment,primary_key"`
	Email  *string `db:"email"`
	Age    *int64  `db:"age"`
	Active *bool   `db:"active"`
}

Use IsNull or IsNotNull when filtering by NULL.

err := client.Find(ctx, &users, sqlrud.Where("Email", sqlrud.IsNull()))

Eq(nil) generates = ?, so use IsNull() for SQL NULL checks.

Transactions

Pass an anonymous function to Transaction. Returning an error rolls back; returning nil commits.

err := client.Transaction(ctx, func(tx *sqlrud.Client) error {
	if err := tx.Create(ctx, &user); err != nil {
		return err
	}

	return tx.Update(ctx, &profile)
})

Tags

db option Meaning
primary_key Primary key used by Update, Delete, and CreateOrUpdate
auto_increment Omit this field on Create when it has the zero value
readonly Never write this field on create or update
createonly Write this field on create, not update
updateonly Write this field on update, not create
omitempty Omit this field when it has the zero value

primary, pk, and auto are also accepted as aliases.

Notes

  • Update and Delete require either a non-zero primary key value or explicit Where conditions.
  • CreateOrUpdate uses a database-native atomic upsert for PostgreSQL and MySQL. If the primary key is zero, it performs Create.
  • CreateOrUpdate requires the database to enforce the relevant primary or unique key constraint. PostgreSQL uses the model primary key as the conflict target; MySQL may update on any primary or unique key conflict.
  • CreateOrUpdate returns ErrUnsupportedDialect for unsupported drivers.
  • MySQL requires Limit when Offset is used.
  • SQL identifiers come from struct metadata and are validated before query execution.

Documentation

Overview

Package sqlrud provides a small CRUD helper for database/sql-style applications.

Index

Constants

This section is empty.

Variables

View Source 
var (
	ErrInvalidModel        = errors.New("sqlrud: model must be a struct or a pointer to struct")
	ErrInvalidDestination  = errors.New("sqlrud: destination must be a pointer to struct or slice")
	ErrInvalidIdentifier   = errors.New("sqlrud: invalid identifier")
	ErrDuplicateColumn     = errors.New("sqlrud: duplicate column")
	ErrAmbiguousField      = errors.New("sqlrud: ambiguous field")
	ErrMissingPrimaryKey   = errors.New("sqlrud: model has no primary key")
	ErrMissingPrimaryValue = errors.New("sqlrud: primary key value is empty")
	ErrMissingWhere        = errors.New("sqlrud: update or delete requires conditions or a primary key value")
	ErrNoColumns           = errors.New("sqlrud: no writable columns")
	ErrUnknownField        = errors.New("sqlrud: unknown field")
	ErrEmptyIn             = errors.New("sqlrud: IN predicate requires at least one value")
	ErrUnsupportedOption   = errors.New("sqlrud: unsupported option")
	ErrUnsupportedDialect  = errors.New("sqlrud: unsupported dialect")
)

Functions

This section is empty.

Types

type Client 

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

Client runs CRUD operations through sqlx.

func New 

func New(db *sqlx.DB) *Client

New creates a Client that executes queries through db.

func (*Client) Create 

func (client *Client) Create(ctx context.Context, model any) error

Create inserts model.

func (*Client) CreateOrUpdate 

func (client *Client) CreateOrUpdate(ctx context.Context, model any) error

CreateOrUpdate inserts model or atomically updates it on primary key conflict.

func (*Client) Delete 

func (client *Client) Delete(ctx context.Context, model any, options ...QueryOption) error

Delete deletes model by primary key or by explicit Where conditions.

func (*Client) Find 

func (client *Client) Find(ctx context.Context, destination any, options ...QueryOption) error

Find loads all matching records into destination.

func (*Client) First 

func (client *Client) First(ctx context.Context, destination any) error

First loads one record by the primary key values set on destination.

func (*Client) Transaction 

func (client *Client) Transaction(ctx context.Context, fn func(*Client) error) error

Transaction runs fn in a transaction. Returning an error from fn rolls back the transaction.

func (*Client) TransactionOptions 

func (client *Client) TransactionOptions(ctx context.Context, options *sql.TxOptions, fn func(*Client) error) (err error)

TransactionOptions runs fn in a transaction using options.

func (*Client) Update 

func (client *Client) Update(ctx context.Context, model any, options ...QueryOption) error

Update updates model by primary key or by explicit Where conditions.

type Direction 

type Direction string

Direction controls ORDER BY direction. 

const (
	// Asc sorts results in ascending order.
	Asc Direction = "ASC"
	// Desc sorts results in descending order.
	Desc Direction = "DESC"
)

type Predicate 

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

Predicate represents a safe field comparison used by Where.

func Eq 

func Eq(value any) Predicate

Eq builds an equality predicate.

func Gt 

func Gt(value any) Predicate

Gt builds a greater-than predicate.

func Gte 

func Gte(value any) Predicate

Gte builds a greater-than-or-equal predicate.

func In 

func In(values ...any) Predicate

In builds an IN predicate.

func IsNotNull 

func IsNotNull() Predicate

IsNotNull builds an IS NOT NULL predicate.

func IsNull 

func IsNull() Predicate

IsNull builds an IS NULL predicate.

func Like 

func Like(value any) Predicate

Like builds a LIKE predicate.

func Lt 

func Lt(value any) Predicate

Lt builds a less-than predicate.

func Lte 

func Lte(value any) Predicate

Lte builds a less-than-or-equal predicate.

func NotEq 

func NotEq(value any) Predicate

NotEq builds an inequality predicate.

func NotIn 

func NotIn(values ...any) Predicate

NotIn builds a NOT IN predicate.

type QueryOption 

type QueryOption interface {
	// contains filtered or unexported methods
}

QueryOption configures CRUD queries.

func Limit 

func Limit(limit int) QueryOption

Limit adds a LIMIT clause.

func Offset 

func Offset(offset int) QueryOption

Offset adds an OFFSET clause. MySQL requires Limit to be specified as well.

func OrderBy 

func OrderBy(field string, direction Direction) QueryOption

OrderBy adds an ORDER BY clause.

func Where 

func Where(field string, predicate Predicate) QueryOption

Where adds an AND condition. field can be either a struct field name or db column name.

type TableNamer 

type TableNamer interface {
	TableName() string
}

TableNamer overrides the table name for a model.

Source Files

View all Source files

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.