usql

README

Database

This package provides four main types:

• DBConfig: contains the different parameters to be configured.
• DBHolder: creates the connection to the database and runs SQL migrations.
• TestDBHolder: is a wrapper of DBHolder that provides a Reset() method that cleans the database and runs again all migrations.
• DBrepository: is built on top of sqlx to provide easier transaction management as well as methods like Save or Find.

Usage

DBConfig

// Returns a *DBConfig initialized by env variables
// Host                 string `env:"POSTGRES_HOST" envDefault:"localhost"`
// Port                 string `env:"POSTGRES_PORT" envDefault:"5432"`
// User                 string `env:"POSTGRES_USER" envDefault:"postgres"`
// Password             string `env:"POSTGRES_PASSWORD" envDefault:"postgres"`
// DatabaseName         string `env:"POSTGRES_DATABASE" envDefault:"postgres"`
// SchemaName           string `env:"POSTGRES_SCHEMA" envDefault:"public"`
// MigrationsDir        string `env:"POSTGRES_MIGRATIONS_DIR" envDefault:"./migrations"`
// RunMigrationsOnReset bool   `env:"POSTGRES_RUN_MIGRATIONS" envDefault:"false"`
dbConfig := NewDBConfigFromEnv()

DBHolder

// Returns a *DBHolder initialized with the provided config.
// In case the *DBConfig object has zero values, those will
// be filled with default values.
dbHolder := NewDBHolder(dbConfig)
// Run SQL migrations found in the folder specified by DBConfig.MigrationsDir
dbHolder.RunMigrations()

DBrepository

type Resource struct {
    ID     string
    Name   string
    Random int
}
// This map will be used in the method Find(context.Context, url.values) to use the filters
// and sorters provided in the url.values parameter. In case the url.values contains a filter
// that it is not in the filters map, it will return an error.
filtersMap := map[string]usqlFilters.Filter{
    "id":            usqlFilters.TextField("id"),
    "name":          usqlFilters.TextField("name"),
    "random":        usqlFilters.NumField("random"),
}

sortersMap := map[string]usqlFilters.Sorter{
    "sort": usqlFilters.Sort("name", "random"),
}

r := NewDBRepository[*Resource](dbHolder, filtersMap, sortersMap)

Transactions

func DoSomething(ctx context.Context) (rErr error) {}
    // ctx must be of type context.Context
    // repository must implement the Transactional interface
    // type Transactional interface {
    //     Begin(ctx context.Context) (context.Context, error)
    //     Commit(ctx context.Context) error
    //     Rollback(ctx context.Context)
    // }
    // DBrepository already implements it.
    // The returned ctx has the transaction within it.
    ctx, err := BeginTx(ctx, repository)
    // If there is an error, the transaction could not be created.

    // Expects an *error as last parameter since it will
    // automatically perform a rollback if the function
    // finishes with an error or a commit in case there is not error.
    defer EndTx(ctx, repository, &rErr)
    // do stuff
	
    res := Resource{}
    // It's important to use the GetTransaction method instead of GetDBInstance if we want the following operations to be part of the transaction.
    tx := repository.GetTransaction(ctx)
    err = tx.NamedExecContext(ctx,
        "INSERT INTO resources (id, name, random) VALUES (:id, :name, :random);",
        &res,
    )
    // Check err

    // do more stuff
    return err // or nil
}

Search

var obj Resource
var list []*Resource

// It will return the resource found in the variable obj.
// Notice the &.
dbInstance := repository.GetDBInstance()
query := "SELECT id, name, random FROM resources"
err := repository.GetContext(ctx, dbInstance, &obj, query, url.Values{})

// Filtering by id
v := url.values{}
v.Add("id", "an_ID")
// type ResourcePage[T any] struct {
//     Total  int64 `json:"total"`
//     Limit  int64 `json:"limit"`
//     Offset int64 `json:"offset"`
//
//     In this example, *[]*Resource.
//     Resources []T `json:"resources"`
// }
// In this example, repository.SelectContext return type is ResourePage[*Resource].
resourcePage, err := repository.SelectContext(ctx, dbInstance, query, url.Values{})

// Filtering by name
v = url.values{}
v.Add("name", "the name to filter")
resourcePage, err = repository.SelectContext(ctx, dbInstance, query, v)

// Filtering by a number
v = url.values{}
v.Add("random", "4")
resourcePage, err = repository.SelectContext(ctx, dbInstance, query, v)

// Sorting by name field in ascending order
v = url.values{}
v.Add("sort", "name")
resourcePage, err = repository.SelectContext(ctx, dbInstance, query, v)

// Sorting by name field in descending order
v = url.values{}
v.Add("sort", "-name")
resourcePage, err = repository.SelectContext(ctx, dbInstance, query, v)

Build SQL

v := url.values{}
v.Add("id", "an_ID")
v.Add("limit", "10")
v.Add("sort", "name")
v.Add("sort", "-random")
// output:
//     query: "SELECT id, name, random FROM resources WHERE id = ? ORDER BY name, random desc LIMIT 10"
//     args: []interface{}{"an_ID"}
//     limit: 10
//     offset: 0
//     err: nil
query, args, limit, offset, err := repository.ApplyFilters(dbInstance, query, v)

Documentation

Index

• type DBHolder
  • func NewDBHolder(config *udatabase.DBConfig) *DBHolder
  • func (d *DBHolder) GetDBInstance() *sqlx.DB
  • func (d *DBHolder) MapperFunc(mf func(string) string)
  • func (d *DBHolder) RunMigrations() error
• type DBrepository
  • func NewDBRepository[T any](dbHolder *DBHolder, filtersMap map[string]usqlFilters.Filter, ...) *DBrepository[T]
  • func (r *DBrepository[T]) ApplyFilters(db Querier, query string, v url.Values) (queryResult string, args []any, limit, offset int64, err error)
  • func (r *DBrepository[T]) Begin(ctx context.Context) (context.Context, error)
  • func (r *DBrepository[T]) Commit(ctx context.Context) error
  • func (r *DBrepository[T]) GetContext(ctx context.Context, db Querier, dst T, query string, v url.Values) (T, error)
  • func (r *DBrepository[T]) GetDBInstance() *sqlx.DB
  • func (r *DBrepository[T]) GetTransaction(ctx context.Context) *sqlx.Tx
  • func (r *DBrepository[T]) HandleSaveOrUpdateError(res sql.Result, err error) error
  • func (r *DBrepository[T]) HandleSearchError(err error) error
  • func (r *DBrepository[T]) Rollback(ctx context.Context)
  • func (r *DBrepository[T]) SelectContext(ctx context.Context, db Querier, query string, v url.Values) (rp *udatabase.ResourcePage[T], err error)
• type Querier
• type TestDBHolder
  • func NewTestDBHolder(schemaName string) *TestDBHolder
  • func (d *TestDBHolder) Reset()

Types

type DBHolder

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

func NewDBHolder

func NewDBHolder(config *udatabase.DBConfig) *DBHolder

Returns a *DBHolder initialized with the provided config. In case the *DBConfig object has zero values, those will be filled with default values.

func (*DBHolder) GetDBInstance

func (d *DBHolder) GetDBInstance() *sqlx.DB

GetDBInstance returns the inner database object *sqlx.DB provided by sqlx. More on sqlx here: https://github.com/jmoiron/sqlx

func (*DBHolder) MapperFunc

func (d *DBHolder) MapperFunc(mf func(string) string)

func (*DBHolder) RunMigrations

func (d *DBHolder) RunMigrations() error

RunMigrations runs SQL migrations found in the folder specified by DBConfig.MigrationsDir

type DBrepository

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

DBrepository is built on top of sqlx to provide easier transaction management as well as methods for error handling.

func NewDBRepository

func NewDBRepository[T any](dbHolder *DBHolder, filtersMap map[string]usqlFilters.Filter,
	sorters map[string]usqlFilters.Sorter) *DBrepository[T]

NewDBRepository returns a DBrepository. requires a that map will be used in the method Find(context.Context, url.values) to use the filters and sorters provided in the url.values{} parameter. In case the url.values contains a filter that it is not in the filters map, it will return an error.

func (*DBrepository[T]) ApplyFilters

func (r *DBrepository[T]) ApplyFilters(db Querier, query string, v url.Values) (queryResult string, args []any,
	limit, offset int64, err error)

func (*DBrepository[T]) Begin

func (r *DBrepository[T]) Begin(ctx context.Context) (context.Context, error)

Begin opens a new transaction. NOTE: Nested transactions not supported.

func (*DBrepository[T]) Commit

func (r *DBrepository[T]) Commit(ctx context.Context) error

Commit closes and confirms the current transaction.

func (*DBrepository[T]) GetContext

func (r *DBrepository[T]) GetContext(ctx context.Context, db Querier, dst T, query string, v url.Values) (T, error)

func (*DBrepository[T]) GetDBInstance

func (r *DBrepository[T]) GetDBInstance() *sqlx.DB

func (*DBrepository[T]) GetTransaction

func (r *DBrepository[T]) GetTransaction(ctx context.Context) *sqlx.Tx

func (*DBrepository[T]) HandleSaveOrUpdateError

func (r *DBrepository[T]) HandleSaveOrUpdateError(res sql.Result, err error) error

HandleSaveOrUpdateError in case of running an INSERT/UPDATE query, this method provides an easy way of checking if the returned error is nil or if it violates a PRIMARY KEY/UNIQUE constraint.

func (*DBrepository[T]) HandleSearchError

func (r *DBrepository[T]) HandleSearchError(err error) error

IsResourceNotFound in case of running SELECT queries using *sqlx.DB/*sqlx.Tx, this method provides an easy way of checking if the error returned is a NotFound or other type.

func (*DBrepository[T]) Rollback

func (r *DBrepository[T]) Rollback(ctx context.Context)

Rollback cancels the current transaction.

func (*DBrepository[T]) SelectContext

func (r *DBrepository[T]) SelectContext(ctx context.Context, db Querier, query string,
	v url.Values) (rp *udatabase.ResourcePage[T], err error)

type Querier

type Querier interface {
	Rebind(query string) string
	GetContext(ctx context.Context, dest any, query string, args ...any) error
	SelectContext(ctx context.Context, dest any, query string, args ...any) error
}

type TestDBHolder

type TestDBHolder struct {
	*DBHolder
}

func NewTestDBHolder

func NewTestDBHolder(schemaName string) *TestDBHolder

func (*TestDBHolder) Reset

func (d *TestDBHolder) Reset()