structdbpostgres

README

struct-db-postgres

Package structdbpostgres is meant to map structs to PostgreSQL tables (like ORM).

Example usage

TL;DR

Go through *_test.go files, starting with main_test.go to get working examples. The tests should be covering all the use cases.

Defining structs (models)

Models are defined with structs as follows (take a closer look at the tags):

type User struct {
	ID                 int    `json:"user_id"`
	Flags              int    `json:"flags"`
	Name               string `json:"name" 2db:"req lenmin:2 lenmax:50"`
	Email              string `json:"email" 2db:"req"`
	Password           string `json:"password"`
	EmailActivationKey string `json:"email_activation_key" 2db:""`
	CreatedAt          int    `json:"created_at"`
	CreatedByUserID    int    `json:"created_by_user_id"`
}

type Session struct {
	ID                 int    `json:"session_id"`
	Flags              int    `json:"flags"`
	Key                string `json:"key" 2db:"uniq lenmin:32 lenmax:50"`
	ExpiresAt          int    `json:"expires_at"`
	UserID             int    `json:"user_id" 2db:"req"`
}

type Something struct {
	ID                 int    `json:"something_id"`
	Flags              int    `json:"flags"`
	Email              string `json:"email" 2db:"req"`
	Age                int    `json:"age" 2db:"req valmin:18 valmax:130 val:18"`
	Price              int    `json:"price" 2db:"req valmin:0 valmax:9900 val:100"`
	CurrencyRate       int    `json:"currency_rate" 2db:"req valmin:40000 valmax:61234 val:10000"`
	PostCode           string `json:"post_code" 2db:"req val:32-600"`
}

Field tags

Struct tags define ORM behaviour. structdbpostgres parses tags such as 2db and various tags starting with 2db_. Apart from the last one, a tag define many properties which are separated with space char, and if they contain a value other than bool (true, false), it is added after semicolon char. See below list of all the tags with examples.

Tag	Example	Explanation
2db	2db:"req valmin:0 valmax:130 val:18"	Struct field properties defining its valid value for model. See Field Properties for more info
2db_regexp	validation_regexp:"^[0-9]{2}\\-[0-9]{3}$"	Regular expression that struct field must match

Field properties

Possible properties in the 2db tag are as follows.

Property	Explanation
req	Field is required
uniq	Field has to be unique (like UNIQUE on the database column)
valmin	If field is numeric, this is minimal value for the field
valmax	If field is numeric, this is maximal value for the field
lenmin	If field is string, this is a minimal length of the field value
lenmax	If field is string, this is a maximal length of the field value

Overwritting table column type

Fields that are of string type are represented by VARCHAR(255) database column by default. This can be overwritten with a data_type field. Check README of structsqlpostgres module to view all supported values.

Creating controller

To perform model database actions, a Controller object must be created. See below example that modify object(s) in the database.

import (
	stdb "github.com/mikolajgs/prototyping/pkg/struct-db-postgres"
)

// Create connection with sql
conn, _ := sql.Open("postgres", fmt.Sprintf("host=%s port=%s user=%s password=%s dbname=%s sslmode=disable", dbHost, dbPort, dbUser, dbPass, dbName))
defer conn.Close()

// Create a database controller and an instance of a struct
c := stdb.NewController(conn, "app1_", nil)
user := &User{}

err = c.CreateTable(user) // Run 'CREATE TABLE'

user.Email = "test@example.com"
user.Name = "Jane Doe"
user.CreatedAt = time.Now().Unix()
err = c.Save(user) // Insert object to database table

user.Email = "newemail@example.com"
err = c.Save(user) // Update object in the database table

err = c.Delete(user) // Delete object from the database table

err = c.DropTable(user) // Run 'DROP TABLE'

Changing tag name

A different than 2db tag can be used. See example below.

c := stdb.NewController(conn, "app1_", &stdb.ControllerConfig{
	TagName: "mytag",
})

Documentation

Index

• Constants
• type Controller
  • func NewController(dbConn *sql.DB, tblPrefix string, cfg *ControllerConfig) *Controller
  • func (c *Controller) AddSQLGenerator(obj interface{}, parentObj interface{}, overwrite bool, forceName string, ...) error
  • func (c Controller) CreateTable(obj interface{}) error
  • func (c Controller) CreateTables(xobj ...interface{}) error
  • func (c Controller) Delete(obj interface{}, options DeleteOptions) error
  • func (c Controller) DeleteMultiple(obj interface{}, options DeleteMultipleOptions) error
  • func (c Controller) DropTable(obj interface{}) error
  • func (c Controller) DropTables(xobj ...interface{}) error
  • func (c Controller) Get(newObjFunc func() interface{}, options GetOptions) ([]interface{}, error)
  • func (c Controller) GetCount(newObjFunc func() interface{}, options GetCountOptions) (int64, error)
  • func (c *Controller) GetFieldNameFromDBCol(obj interface{}, dbCol string) (string, error)
  • func (c Controller) GetFiltersInterfaces(mf map[string]interface{}) []interface{}
  • func (c Controller) GetObjFieldInterfaces(obj interface{}, includeID bool) []interface{}
  • func (c *Controller) GetObjIDInterface(obj interface{}) interface{}
  • func (c *Controller) GetObjIDValue(obj interface{}) int64
  • func (c Controller) Load(obj interface{}, id string, options LoadOptions) error
  • func (c Controller) ResetFields(obj interface{})
  • func (c Controller) Save(obj interface{}, options SaveOptions) error
  • func (c *Controller) SetObjCreated(obj interface{}, at int64, by int64)
  • func (c *Controller) SetObjLastModified(obj interface{}, at int64, by int64)
  • func (c *Controller) StringToFieldValues(obj interface{}, values map[string]interface{}) map[string]interface{}
  • func (c Controller) UpdateMultiple(obj interface{}, values map[string]interface{}, options UpdateMultipleOptions) error
  • func (c Controller) Validate(obj interface{}, filters map[string]interface{}) (bool, map[string]int, error)
• type ControllerConfig
• type DeleteMultipleOptions
• type DeleteOptions
• type ErrController
  • func (e ErrController) Error() string
  • func (e ErrController) Unwrap() error
• type ErrValidation
  • func (e ErrValidation) Error() string
  • func (e ErrValidation) Unwrap() error
• type GetCountOptions
• type GetOptions
• type LoadOptions
• type SaveOptions
• type UpdateMultipleOptions

Constants

const RawConjuctionAND = 2

const RawConjuctionOR = 1

const VERSION = "0.7.0"

Types

type Controller

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

Controller is the main component that gets and saves objects in the database.

func NewController

func NewController(dbConn *sql.DB, tblPrefix string, cfg *ControllerConfig) *Controller

NewController returns new Controller object

func (*Controller) AddSQLGenerator

func (c *Controller) AddSQLGenerator(obj interface{}, parentObj interface{}, overwrite bool, forceName string, parentOnlyRoot bool) error

AddSQLGenerator adds StructSQL object to sqlGenerators

func (Controller) CreateTable

func (c Controller) CreateTable(obj interface{}) error

CreateTable creates database table to store specified type of objects. It takes struct name and its fields, converts them into table and columns names (all lowercase with underscore), assigns column type based on the field type, and then executes "CREATE TABLE" query on attached DB connection

func (Controller) CreateTables

func (c Controller) CreateTables(xobj ...interface{}) error

CreateTables creates tables in the database for specified objects (see CreateTable for a single struct)

func (Controller) Delete

func (c Controller) Delete(obj interface{}, options DeleteOptions) error

Delete removes object from the database table and it does that only when ID field is set (greater than 0). Once deleted from the DB, all field values are zeroed TODO: Error handling probably needs re-designing

func (Controller) DeleteMultiple

func (c Controller) DeleteMultiple(obj interface{}, options DeleteMultipleOptions) error

DeleteMultiple removes objects from the database based on specified filters

func (Controller) DropTable

func (c Controller) DropTable(obj interface{}) error

DropTable drops database table used to store specified type of objects. It just takes struct name, converts it to lowercase-with-underscore table name and executes "DROP TABLE" query using attached DB connection

func (Controller) DropTables

func (c Controller) DropTables(xobj ...interface{}) error

DropTables drop tables in the database for specified objects (see DropTable for a single struct)

func (Controller) Get

func (c Controller) Get(newObjFunc func() interface{}, options GetOptions) ([]interface{}, error)

Get runs a select query on the database with specified filters, order, limit and offset and returns a list of objects

func (Controller) GetCount

func (c Controller) GetCount(newObjFunc func() interface{}, options GetCountOptions) (int64, error)

GetCount runs a 'SELECT COUNT(*)' query on the database with specified filters, order, limit and offset and returns count of rows

func (*Controller) GetFieldNameFromDBCol

func (c *Controller) GetFieldNameFromDBCol(obj interface{}, dbCol string) (string, error)

GetDBCol returns column name used in the database

func (Controller) GetFiltersInterfaces

func (c Controller) GetFiltersInterfaces(mf map[string]interface{}) []interface{}

GetFiltersInterfaces returns list of interfaces from filters map (used in querying)

func (Controller) GetObjFieldInterfaces

func (c Controller) GetObjFieldInterfaces(obj interface{}, includeID bool) []interface{}

GetObjFieldInterfaces return list of interfaces to object's fields Argument includeID tells it to include or omit the ID field

func (*Controller) GetObjIDInterface

func (c *Controller) GetObjIDInterface(obj interface{}) interface{}

GetObjIDInterface returns an interface{} to ID field of an object

func (*Controller) GetObjIDValue

func (c *Controller) GetObjIDValue(obj interface{}) int64

GetObjIDValue returns value of ID field (int64) of an object

func (Controller) Load

func (c Controller) Load(obj interface{}, id string, options LoadOptions) error

Load sets object's fields with values from the database table with a specific id. If record does not exist in the database, all field values in the struct are zeroed TODO: Should it return an ErrNotExist?

func (Controller) ResetFields

func (c Controller) ResetFields(obj interface{})

ResetFields zeroes object's field values

func (Controller) Save

func (c Controller) Save(obj interface{}, options SaveOptions) error

Save takes object, validates its field values and saves it in the database. If ID is not present then an INSERT will be performed If ID is set then an "upsert" is performed

func (*Controller) SetObjCreated

func (c *Controller) SetObjCreated(obj interface{}, at int64, by int64)

SetObjCreated sets object's CreatedAt and CreatedBy fields

func (*Controller) SetObjLastModified

func (c *Controller) SetObjLastModified(obj interface{}, at int64, by int64)

SetObjLastModified sets object's LastModifiedAt and LastModifiedBy fields

func (*Controller) StringToFieldValues

func (c *Controller) StringToFieldValues(obj interface{}, values map[string]interface{}) map[string]interface{}

StringToFieldValues converts map of field values which are in string to values of the same kind of fields are

func (Controller) UpdateMultiple

func (c Controller) UpdateMultiple(obj interface{}, values map[string]interface{}, options UpdateMultipleOptions) error

UpdateMultiple updates specific fields in objects from the database based on specified filters

func (Controller) Validate

func (c Controller) Validate(obj interface{}, filters map[string]interface{}) (bool, map[string]int, error)

Validate checks object's fields. It returns result of validation as a bool and list of fields with invalid value

type ControllerConfig

type ControllerConfig struct {
	TagName string
}

type DeleteMultipleOptions

type DeleteMultipleOptions struct {
	Filters            map[string]interface{}
	CascadeDeleteDepth int
	Constructors       map[string]func() interface{}
}

type DeleteOptions

type DeleteOptions struct {
	Constructors map[string]func() interface{}
}

type ErrController

type ErrController struct {
	Op  string
	Err error
}

ErrController wraps original error that occurred in Err with name of the operation/step that failed, which is in Op field

func (ErrController) Error

func (e ErrController) Error() string

func (ErrController) Unwrap

func (e ErrController) Unwrap() error

type ErrValidation

type ErrValidation struct {
	Fields map[string]int
	Err    error
}

ErrValidation wraps error occurring during object validation

func (ErrValidation) Error

func (e ErrValidation) Error() string

func (ErrValidation) Unwrap

func (e ErrValidation) Unwrap() error

type GetCountOptions

type GetCountOptions struct {
	Filters map[string]interface{}
}

type GetOptions

type GetOptions struct {
	Order               []string
	Limit               int
	Offset              int
	Filters             map[string]interface{}
	RowObjTransformFunc func(interface{}) interface{}
}

type LoadOptions

type LoadOptions struct {
	Unused bool
}

type SaveOptions

type SaveOptions struct {
	NoInsert   bool
	ModifiedBy int64
	ModifiedAt int64
}

type UpdateMultipleOptions

type UpdateMultipleOptions struct {
	Filters                 map[string]interface{}
	CascadeDeleteDepth      int
	ConvertValuesFromString bool
}