repo

repo module for database CRUD

repo provides a set of convenience methods for carrying out CRUD operations on a single sql-database table.

Install

go get gitlab.com/msts-public/general/gomods/repo

Description

repo is designed for use with a zerolog logger, and uses sqlx database connection and transaction types.

While repo has been set up to support some pgx types, and the integration test uses pgconn, it should still work if not using these. Limitations in type support mainly affect the mocking, and not the core functionality. The core functionality should support any type that supports sql.Value.

TODO: add support from more types to repomock, eg decimal

When initializing a repo, a record struct is provided, and repo extracts column names based on db tags. A basic set of methods for CRUD statements, Create, Update, Delete (logical delete), Remove (database delete), GetById, and GetByParam are then available for interacting with the database table. Also provided are Count and CountByParam methods.

Limitations:

• builder does not support OR clauses
• some operators, eg array operators and between, only support certain basic types of variables - string and ints for array IN and string, ints and time.Time for BETWEEN
  • work around for between is to use two clauses with <= and >= operators
• comparison function in mock defaults to comparing values as strings, and only supports a limited set of types that are not easily/correctly compared as strings

Example - setup

import "gitlab.com/msts-public/general/gomods/repo"

type Record struct {
	ID      string            `db:"id"`
	Col1    string            `db:"col_1"`
	Col2    int               `db:"col_2"`
	Created time.Time         `db:"created"`
	Updated time.Time         `db:"updated"`
	Deleted sql.NullTimeStamp `db:"deleted"`
}

// filters - defines which column are excluded from the Create, Update and Return column lists
var filters = repo.ColumnFilters{
	Create: map[string]*string{"deleted": nil},
	// Note: for updates, this is the set of column that not will be updated. Updated record is based on the ID.
	Update: map[string]*string{"id": nil, "created": nil, "deleted": nil},
	Return: map[string]*string{"deleted": nil},
}
type RecordRepo struct {
	repo.Base
}

func NewRecordRepo(l zerolog.Logger, tx *sqlx.Tx) {*RecordRepo, error} {
	rr := &RecordRepo{
		repo.Base{
			Tx:                  tx,
			Logger:              l,
			Filters:             filters,
			LogicalDeleteColumn: "deleted",
		},
	}

	// initialize the column lists and statements
	err := rr.Init(&Record{})
	if err != nil {
		return err
	}

	// pass the transaction to the repo
	err := rr.InitTx(tx)
	if err != nil {
		return err
	}

	return rr, nil
}

// GetByID -
func (rr *RecordRepo) GetByID(id string, forUpdate bool) (*Record, error) {
	rec := &Record{}
	if err := rr.GetRecByID(rr.GetByIDSQL(), id, rec, forUpdate); err != nil {
		return nil, err
	}
	return rec, nil
}

// GetByParam -
func (rr *RecordRepo) GetByParam(params []repo.Restriction, options *repo.Options) ([]*Record, error) {
	// records
	recs := []*Record{}

	rows, err := rr.GetRowsByParam(rr.GetByParamSQL(), params, options)
	if err != nil {
		rr.Logger.Warn().Err(err).Msg("Failed querying row")
		return nil, err
	}
	defer rows.Close()

	for rows.Next() {
		rec := &Record{}
		err := rows.StructScan(rec)
		if err != nil {
			rr.Logger.Warn().Err(err).Msg("Failed executing struct scan")
			return nil, err
		}
		recs = append(recs, rec)
	}

	return recs, nil
}

// Create -
func (rr *RecordRepo) Create(rec *Record) error {

	rec.Created = time.Now().UTC()

	err := rr.CreateRec(rr.GetCreateSQL(), rec)
	if err != nil {
		rr.Logger.Warn().Err(err).Msg("Failed creating record")
		return err
	}

	return nil
}

// Update -
func (rr *RecordRepo) Update(rec *Record) error {
	prevUpd := rec.Updated
	rec.Updated = time.Now().UTC()

	err := rr.UpdateRec(rr.GetUpdateSQL(), rec)
	if err != nil {
		rec.ResetUpdated(prevUpd)
		rr.Logger.Warn().Err(err).Msg("Failed updating record")
		return err
	}

	return nil
}

// Delete -
func (rr *RecordRepo) Delete(id string) error {
	return rr.DeleteRec(rr.GetDeleteSQL(), id)
}

// Remove -
func (rr *RecordRepo) Remove(id string) error {
	return rr.RemoveRec(rr.GetRemoveSQL(), id)
}

Building a query

A query can be built up to select base on additional criteria (other than ID) using the GetByParams function. This takes two parameters:

• params - the set of column restrictions to apply to the query, ie the WHERE clause of the query. This is an array of structs containing
  • Column - the name of the column the restriction applies to (required)
  • Value - the value the column will be compared to (required for non-unary operators)
  • Operator - the operator used to make the comparison (defaults to '=' or 'IN' as appropriate based on the type of Value)
• options - the set of additional clauses to apply to the query. These are:
  • OrderBy - array of structs defining sort order. Order in the array determines order in the ORDER BY clause. Consists of
    • Column - name of column to sort by
    • Direction - sort direction, either ASC or DESC (case insensitive), (defaults to ASC)
  • ForUpdate - FOR UPDATE clause, can be either skip or nowait
  • Limit - LIMIT clause to restrict the maximum number of records to return
  • Offset - OFFSET clause, number of records to skip before returning records. Should only be used in conjunction with an ORDER BY clause or the results could be unpredictable.

Example:

  params: = []repo.Restriction{
  	{Column: 'created_at', Value: time.Now().Add(0,0,-1), Operator: repo.OperatorGreaterThan}, // records created in the last 24 hours
  	{Column: 'status', Value: []string{'pending','open'},                                      // with a status of 'pending' or 'open' (uses IN operator)
  	{Column: 'assigned_to', Operator: OperatorIsNull},                                         // and not assigned (unary operator)
  }
  options := &repo.Options{
  	OrderBy: []repo.Ordering{{Column: 'priority'},{Column: 'impact', Direction: 'DESC'},{'created_at', 'asc'}},
  	Limit: 10,
  	Offset: 0,
  }

  for {
	  recs, err := r.GetByParams(params, options)
	  break if recs == nil
	  ...
	  options.Offset += options.Limit
  }