sqlh

package module
v0.4.0 Latest Latest
Warning

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

 Go to latest Published: Feb 20, 2026 License: BSD-3-Clause Imports: 9 Imported by: 0

README

sqlh: A Go SQL Helper Package

Go Report Card GoDoc

sqlh is a lightweight helper package for Go that simplifies interactions with SQL databases. It leverages generics to provide a set of intuitive functions (Insert, Update, Get, List, Delete) that work directly with your Go structs, reducing boilerplate code.

The package automatically generates SQL queries from your struct definitions, using struct tags for customization.

Features

  • Generic Functions: Work with any of your custom structs without needing to write specific SQL for each.
  • Automatic Query Generation: Automatically creates CREATE TABLE, INSERT, UPDATE, SELECT, and DELETE statements.
  • Struct Tag-Based Mapping: Use db, db_type, and db_key tags to control table and column definitions.
  • Autoincrement Support: Automatically excludes fields marked with autoincrement from INSERT and UPDATE statements.
  • Built-in Transactions: All write operations (Insert, Update, Delete, Set) are wrapped in transactions for data integrity.
  • Standardized Error Handling: Returns standard errors like sql.ErrNoRows and exported package errors for easy checking with errors.Is.

Installation

go get github.com/kirill-scherba/sqlh

Quick Start

Here's a quick example of how to use sqlh with an in-memory SQLite database.

1. Define Your Struct

Define a Go struct that represents your database table. Use struct tags to define column names, types, and keys.

package main

import (
    "database/sql"
    "errors"
    "fmt"
    "log"

    "github.com/kirill-scherba/sqlh"
    "github.com/kirill-scherba/sqlh/query"
    _ "github.com/mattn/go-sqlite3"
)

// User represents the users table.
type User struct {
    ID    int64  `db:"id" db_key:"not null primary key autoincrement"`
    Name  string `db:"name" db_key:"unique"`
    Email string `db:"email"`
}
2. Connect and Create Table

Use the query.Table function to generate a CREATE TABLE statement from your struct.

func main() {
    // Open in-memory SQLite database for this example
    db, err := sql.Open("sqlite3", "file::memory:?cache=shared")
    if err != nil {
        log.Fatalf("failed to open database: %v", err)
    }
    defer db.Close()

    // Generate and execute CREATE TABLE statement
    createStmt, err := query.Table[User]()
    if err != nil {
        log.Fatalf("failed to create table query: %v", err)
    }
    if _, err := db.Exec(createStmt); err != nil {
        log.Fatalf("failed to execute create table statement: %v", err)
    }
    fmt.Println("Table 'user' created successfully.")

    // Insert a new user
    alice := User{Name: "Alice", Email: "[email protected]"}
    if err := sqlh.Insert(db, alice); err != nil {
        log.Fatalf("failed to insert user: %v", err)
    }
    fmt.Println("Inserted Alice.")

    // Get the user we just inserted
    retrievedUser, err := sqlh.Get[User](db, sqlh.Where{Field: "name=", Value: "Alice"})
    if err != nil {
        // Check for a specific "not found" error
        if errors.Is(err, sql.ErrNoRows) {
            log.Println("User not found.")
        } else {
            log.Fatalf("failed to get user: %v", err)
        }
        return
    }
    fmt.Printf("Retrieved User: ID=%d, Name=%s, Email=%s\n", retrievedUser.ID, retrievedUser.Name, retrievedUser.Email)

    // Update Alice's email
    retrievedUser.Email = "[email protected]"
    updateAttr := sqlh.UpdateAttr[User]{
        Row:    *retrievedUser,
        Wheres: []sqlh.Where{{Field: "id=", Value: retrievedUser.ID}},
    }
    if err := sqlh.Update(db, updateAttr); err != nil {
        log.Fatalf("failed to update user: %v", err)
    }
    fmt.Println("Updated Alice's email.")
}

Changelog

For a detailed list of changes, please see the CHANGELOG.md file.

Licence

BSD

Documentation

Overview

Sqlh is a SQL Helper package contains helper functions to execute SQL requests. It provides such functions as Execute, Select, Insert, Update and Delete.

Index

Constants

This section is empty.

Variables

View Source 
var (
	ErrWhereClauseRequired = errors.New("sqlh: the where clause is required")
	ErrMultipleRowsFound   = errors.New("sqlh: multiple rows found")

	// Re-exported errors from the query package
	ErrTypeIsNotStruct              = query.ErrTypeIsNotStruct
	ErrWhereClauseRequiredForUpdate = query.ErrWhereClauseRequiredForUpdate
)

Exported errors

Functions

func Count 

func Count[T any](db querier, wheres ...Where) (count int, err error)

Count returns the number of rows from the selected T table in the database.

The function accepts a variadic list of Where conditions to filter the rows. It constructs a SQL COUNT statement and executes it using the provided database connection. The count of rows is returned along with any error encountered during the execution.

func Create added in v0.3.7 

func Create[T any]() (db *sql.DB, err error)

Create creates the SQL table for the T type.

It takes a database connection as a parameter and returns an error if the table could not be created.

The function does not start a transaction, so it is up to the caller to manage transactions if needed.

func Delete 

func Delete[T any](db *sql.DB, wheres ...Where) (err error)

Delete deletes rows from the T database table.

The function takes a variadic list of Where conditions to specify which rows to delete. It constructs a DELETE SQL statement with the given conditions, starts a database transaction, prepares the DELETE statement, and executes it. If any error occurs during the process, the transaction is rolled back. Otherwise, the transaction is committed.

func Get 

func Get[T any](db *sql.DB, wheres ...Where) (row *T, err error)

Get returns a row from T database table.

The function takes a list of Where condition as input parameter. The function executes SELECT statement with the given where conditions. If the row is found, the function returns the row and nil as error. If the row is not found, the function returns a default value for row and an error with message "not found". If multiple rows are found, the function returns a default value for row and an error with message "multiple rows found". It returns a pointer to the row.

func GetNumRows added in v0.3.1 

func GetNumRows() int

GetNumRows returns default number of rows. It may be set by SetNumRows. By default, it is 10.

func Insert 

func Insert[T any](db *sql.DB, rows ...T) (err error)

Insert inserts rows into the T database table.

It accepts a variadic number of rows of type T and inserts them into the corresponding database table. The function starts a transaction and prepares an insert statement. Each row is then inserted in a loop. If any error occurs, the transaction is rolled back. Otherwise, the transaction is committed.

func InsertId added in v0.3.3 

func InsertId[T any](db *sql.DB, rows ...T) (id int64, err error)

InsertId inserts rows into the T database table and returns the last inserted row ID.

It accepts a variadic number of rows of type T and inserts them into the database table. The function starts a transaction and prepares an insert statement. Each row is then inserted in a loop. If any error occurs, the transaction is rolled back. Otherwise, the transaction is committed. The last inserted row ID is returned as a result.

func InsertWithCallback added in v0.3.3 

func InsertWithCallback[T any](

	db *sql.DB,

	callback func(db *sql.DB, tx *sql.Tx) error,

	rows ...T,
) (err error)

InsertWithCallback inserts rows into the T database table and calls the callback function after the rows are successfully inserted.

The function accepts a database connection, a callback function, and a variadic number of rows of type T. The callback function is called after the rows are successfully inserted. If any error occurs, the transaction is rolled back. Otherwise, the transaction is committed.

The callback function is called with the database connection and the transaction object as parameters instead of transaction. If the callback function returns an error, the transaction is rolled back. Otherwise, the transaction is committed.

The function returns an error if any error occurs.

func List 

func List[T any](db querier, previous int, groupBy, orderBy string, listAttrs ...any) (
	rows []T, pagination int, err error)

List returns rows from T database table.

The function takes a list of Where condition as input parameter. The function executes SELECT statement with the given where conditions. If the rows are found, the function returns the rows and nil as error. If the rows are not found, the function returns a default value for rows and an error with message "not found". It returns number of rows limited to numRows. The default value for numRows is 10. The numRows may be set by SetNumRows and get by GetNumRows functions.

func ListRange added in v0.2.2 

func ListRange[T any](db querier, offset int, groupBy, orderBy string, limit int,
	listAttrs ...any) iter.Seq2[int, T]

ListRange returns an iterator over the rows in the database. It takes a querier, a previous number of rows, order by string, number of rows to retrieve, and a variadic list of where conditions to filter the rows. The returned iterator yields each row in the database, and will stop yielding when all the rows have been retrieved or when the yield function returns false. The yielded value is a pointer to a struct of type T, and a new instance of the struct is created for each yielded value. To check for errors, add a function of type func(error) to the query arguments (listAttrs parameter of this function). The range will stop on any error returned by the function.

To use Joins, add a Join type to the listAttrs parameter and set T to func generic type with mine table and joined tables structs. To add Joins use the query.MakeJoin function.

Example: 

users, _, err := ListRange[struct {
	*TestTable  // Main table
	*TestTable2 // Other joined table
}](db, 0, "", "name ASC", 100,
	SetAlias("t"), // Set main table alias to use in Joins
	query.MakeJoin[TestTable2](query.Join{On: "t.id = o.id", Alias: "o"}),
)

func ListRows 

func ListRows[T any](db querier, previous int, groupBy, orderBy string, numRows int,
	listAttrs ...any) (rows []T, pagination int, err error)

ListRows returns rows from T database table.

The function takes a list of Where condition as input parameter. The function executes SELECT statement with the given where conditions. If the rows are found, the function returns the rows and nil as error. If the rows are not found, the function returns a default value for rows and an error with message "not found". It returns number of rows limited to numRows.

The listAttrs is a variadic list of Where conditions to filter the rows.

func QueryRange added in v0.3.0 

func QueryRange[T any](db querier, selectQuery string, queryArgs ...any) iter.Seq[T]

QueryRange returns an iterator over the rows in the database. It takes a querier, a select query string and a variadic list of query arguments. The returned iterator yields each row in the database, and will stop yielding when all the rows have been retrieved or when the yield function returns false. The yielded value is a pointer to a struct of type T, and a new instance of the struct is created for each yielded value.

To check for errors, add a function of type func(error) to the query arguments (queryArgs parameter of this function). The range will stop on any error returned by the function.

func Set added in v0.1.1 

func Set[T any](db *sql.DB, row T, wheres ...Where) (err error)

Set sets a row in T database table.

The function is atomic and uses a transaction. The function takes a list of Where condition as input parameter. The function checks if the row is found in the database. If the row is not found, the function inserts a new row. If the row is found, the function updates the row. If multiple rows are found, the function returns an error with message "multiple rows found".

func SetNumRows 

func SetNumRows(n int)

SetNumRows sets numer of rows in List function. It may be get by GetNumRows. By default, it is 10.

func Update 

func Update[T any](db *sql.DB, attrs ...UpdateAttr[T]) (err error)

Update updates rows in T database table.

The function takes a list of UpdateAttr as input parameter. UpdateAttr contains row and where condition. The function executes UPDATE statement for each UpdateAttr in the list.

The function returns error if something failed during the update process.

Types

type Alias added in v0.3.2 

type Alias string

Alias is a type for query.Args function to set table alias

func SetAlias added in v0.3.2 

func SetAlias(alias string) Alias

SetAlias returns a Alias type with the given alias. It's used to set table alias in the select statement.

type Distinct added in v0.3.2 

type Distinct bool

Distinct is a type for query.Args function to add DISTINCT clause

func SetDistinct added in v0.3.2 

func SetDistinct() Distinct

SetDistinct returns a Distinct type set to true. It's used to add DISTINCT clause to the select statement.

type Name added in v0.3.2 

type Name *string

Name is a type for query.Args function to set table name

func SetName added in v0.3.2 

func SetName(name string) Name

SetName returns a Name type with the given name. It's used to set table name in the select statement.

type UpdateAttr 

type UpdateAttr[T any] struct {

	// Row value to be updated
	Row T

	// Where condition
	Wheres []Where
}

UpdateAttr struct contains row and where condition and used in Update function as attrs parameter.

type Where 

type Where struct {

	// Database table field Name and Condition Operator, f.e. "id="
	// 	=	Equal
	// 	>	Greater than
	// 	<	Less than
	// 	>=	Greater than or equal
	// 	<=	Less than or equal
	// 	<>	Not equal. In some versions of SQL it may be written as !=
	// 	BETWEEN	Between a certain range
	// 	LIKE	Search for a pattern
	// 	IN	To specify multiple possible values for a column
	Field string

	// Field value
	Value any
}

Where struct contains where condition as field and value.

type WheresJoinOr added in v0.3.1 

type WheresJoinOr bool

WheresJoinOr is a type for query.Args function to join wheres with OR

func SetWheresJoinAnd added in v0.3.1 

func SetWheresJoinAnd() WheresJoinOr

SetWheresJoinAnd returns a WheresJoinOr type set to false. It's used to join wheres conditions with AND instead of OR. It's used in the List function. The join wheres conditions with AND is the default behavior.

func SetWheresJoinOr added in v0.3.1 

func SetWheresJoinOr() WheresJoinOr

SetWheresJoinOr returns a WheresJoinOr type set to true. It's used to join wheres conditions with OR instead of AND. It's used in the List function.

Source Files

View all Source files

Directories

Path Synopsis
Query is SQL Helper Query package contains helper functions to generate SQL statements query.
 Query is SQL Helper Query package contains helper functions to generate SQL statements query.

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.