dmpr

package module
v0.2.0 Latest Latest
Warning

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

 Go to latest Published: Aug 30, 2019 License: BlueOak-1.0.0 Imports: 13 Imported by: 0

README

DMPR: Database mapper from scratch

This database mapper is written for sqlx database library, mainly supporting postgres. It aims to be as light weight as possible.

Go Report Card GoDoc Releases

In Scope

  • maintains a database connection
  • provides logrus logging
  • provides health report on the connection
  • provides basic query functionality on top of sqlx for logging purposes
  • provides basic model query functionality (Find, FindBy, All, Create, Update, Delete)
  • provides basic "belongs to", "has one", "has many", and "many to many" relationships (NewSelect)

Out of Scope

  • Transactions (for now)
  • Cascading joins in select: all joins are referencing the original model only.

Map models

Models are structs, and mapper reads their "db" tags for meta-information, just like sqlx. There are a couple of rule of thumbs, which might make your life easier:

  • Database table names are generated by struct names by converting to snake_cased, pluralized form.
  • Empty db:"..." tag names are not handled well. If there is a tag, it must be named.
  • if the tag is "-" (just like in db:"-"), then that field will not be represented in the database.
  • if the tag is missing, sqlx uses a standard mapping: field name converted to lower case, and never snake_case (wrt. table names).
  • mapper accepts the following tag options (optional fields after a comma):
    • omitempty: if the field is empty in the model, it won't be added to Create / Update query
    • relation: it represents "has one" or "has many" relationships (depending on the field type)
    • belongs: represents "belongs_to" relationship. It assumes another field with the same name, but with _id suffix.
    • related maps can and should be added to structs. To avoid circular references, use pointers for related structs.
  • References may accept both values or pointers. However, go doesn't accept circular value references. As a simple rule, I'd suggest you to use values at "belongs to", but use pointers at "has one" or "has many" relationships.
  • Known issue: slice of values don't fill well. Use slice of pointers for "has many" and "many to many" relations.

Relations

Belongs

When a struct "belongs to" another struct, it stores the other struct's ID like this:

type Message struct {
    ID    int
    Title string
    Body  string
}

type Comment struct {
    ID     int
    Title  string
    Body   string
    PostID int     `db:"post_id"`
    Post   Message `db:"post,belongs"`
}

In this case, Comment belongs to Message, and it's referenced internally as "post". It also requires a post_id field, as it will be stored in the table.

Selecting a Comment looks like this:

import "gitlab.com/julian7/dmpr"

comments := &[]Comment{}
query, err := dmpr.NewSelect(comments)
if err != nil {
    panic(err)
}
query.Where(dmpr.Eq("id", 1)).Join("post").All()

This query loads comment with an appropriate comment, with the data of Post, which is a Message object.

Has one / has many

When a struct "has one" another struct, it stores the struct ID at the other struct:

type User struct {
    ID       int
    Name     string
    Password string
    Profile  *Profile `db:"profile,relation=user"`
}

type Profile struct {
    ID      int
    UserID  int  `db:"user_id"`
    Email   string
}

In this case, User "has a" Profile, but Profile doesn't "belong to" User. User requires a reference to a profile, and Profile requieres a user_id field. User's Profile field requires an option "relation" with a value how Profile is referencing it.

Selecting a User with profile looks like this:

import "gitlab.com/julian7/dmpr"

users := &[]User{}
query, err := dmpr.NewSelect(users)
if err != nil {
    panic(err)
}
query.Where(dmpr.Eq("id", 1)).Join("profile").All()

A "has_many" relationship is similar to "has_one", but the referencing struct is in a slice:

type ToDoList struct {
    ID         int
    Name       string
    ToDoItems []*ToDoItem `db:"to_do_items,relation=list"`
}

type UserGroup struct {
    ID      int
    ListID  int     `db:"list_id"`
    Name    string
}

toDoLists := &[]ToDoList{}
query, err := dmpr.NewSelect(users)
if err != nil {
    panic(err)
}
query.Join("to_do_items").All()

Many to many

A "many to many" relation represents an n:m relationship, with an anonymous linking table:

CREATE TABLE users (
    id SERIAL,
    name VARCHAR(32)
);

CREATE TABLE groups (
    id SERIAL,
    name VARCHAR(32)
);

CREATE TABLE user_groups (
    user_id INT,
    group_id INT
);

SELECT t1.id, t1.name, t2.id AS group_id, t2.name AS group_name
FROM users t1
LEFT JOIN user_groups tt2 ON (t1.id=tt2.user_id)
LEFT JOIN groups t2 ON (t2.id=tt2.group_id);

It is more compact in go:

type User struct {
    ID   int
    Name string
    Groups []Group `db:groups,relation=user,reverse=group,through=user_groups"`
}

type Group struct {
    ID int
    Name string
    Users []*User `db:users,relation=group,reverse=user,through=user_groups"`
}

users := &[]User{}
query, err := dmpr.NewSelect(users)
if err != nil {
    panic(err)
}
query.Join("groups").All()

Operators

There are just a couple of operators implemented, but it's very easy to add more. They work in a way query builder can fetch their columns and their relations too.

  • Null operator: dmpr.Null("column", true) provides a "column IS NULL" operator. If the second parameter is false, then it will provide "column IS NOT NULL" instead.
  • Eq operator: dmpr.Eq("column", value) provides an equivalence operator, in the form of column = VALUE or column IN (value...).
  • Lt / Gt / Le / Ge operators: they are simple binary operators, implementing "less than," "greater than," "less than or equal," and "greater than or equal" operators. They are similar to dmpr.Eq(column, value), but they cannot handle slices.
  • Not operator: dmpr.Not(operator) negates an operator. For example, dmpr.Not(dmpr.Null("column", true)) returns colum IS NOT NULL.
  • And operator: dmpr.And(operator...) groups other operators together, to provide a single operator with an AND relationship between them.
  • Or operator: dmpr.Or(operator...) groups other operators together, to provide a single operator with an OR relationship between them.

Documentation

Index

Constants

View Source 
const (
	// OptRelatedTo is an internal struct tag option mapper inserts for all columns, which is a subfield of an embedded table. Its value is the name of the embedded table.
	OptRelatedTo = "_related_to_"
	// OptUnrelated is an internal struct tag option mapper inserts for all columns, which have parents, but they are not a subfield of a known relation.
	OptUnrelated = "_unrelated_"
	// OptBelongs is a struct tag option marking a "belongs-to" relation.
	OptBelongs = "belongs"
	// OptRelation is a struct tag option marking a "has-one", "has-many", or a "many-to-many" relation, containing the other end's ref stub for the struct's ID.
	//
	// Example tag: `db:"posts,relation=author"`: you can reference this join as `posts`, and the other end will have a field called `author_id` to reference this table.
	OptRelation = "relation"
	// OptReverse is a struct tag option marking a "many-to-many" relation,
	// containing the stub how the other end is referenced in the linker table (see OptThrough).
	// OptReverse is taken into consideration only if OptRelation and OptThrough
	// are provided.
	//
	// Example tag: `db:"groups,relation=user,reverse=group,through=user_groups"`:
	// you can reference this join as `groups`, and there must be a `user_groups`
	// table with a `user_id` and a `group_id` field. `user_id` references to this
	// table, `group_id` references to the joined table.
	OptReverse = "reverse"
	// OptThrough is a struct tag option marking a "many-to-many" relation,
	// containing the linker table's name. See OptReverse for an example.
	// OptThrough is taken into consideration only if OptRelation and OptReverse
	// are also provided.
	OptThrough = "through"
)

Variables

View Source 
var ErrInvalidType = errors.New("Invalid Model Type")

ErrInvalidType is an error returning where the model is invalid (currently, when it is a null pointer)

Functions

func Reflect 

func Reflect(model interface{}) (reflect.Type, reflect.Value)

Reflect dissects provided model reference into a type and value for further inspection. It accepts pointer, slice, and pointer of slices indirections.

Types

type AND 

type AND struct {
	*GroupOperator
}

AND is an operator struct, with AND relation between each item

func And 

func And(ops ...Operator) *AND

And returns a new AND operator with initial values

func (*AND) Add 

func (op *AND) Add(ops ...Operator)

Add adds more items into the AND operator

func (*AND) And 

func (op *AND) And(ops ...Operator) *AND

And is an alias to Add, and it returns itself for chaining

func (*AND) Where 

func (op *AND) Where(truthy bool) string

Where returns the where clause of all sub-operators stringed together into an AND clause

type BINARY added in v0.2.0 

type BINARY struct {
	ColumnValue
	TruthyRel string
	FalsyRel  string
}

BINARY implements a 2-parameter operator

func (*BINARY) Where added in v0.2.0 

func (op *BINARY) Where(truthy bool) string

Where implements binary operator's where clause

type ColumnValue 

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

ColumnValue is a standard struct representing a database column and its desierd value. This is the base struct of column-based operators.

func (*ColumnValue) Column 

func (c *ColumnValue) Column() string

Column returns returns the object's column name

func (*ColumnValue) Keys 

func (c *ColumnValue) Keys() []string

Keys returns keys the object returns, in order

func (*ColumnValue) Values 

func (c *ColumnValue) Values() map[string]interface{}

Values returns the object's value

type EQ 

type EQ struct {
	ColumnValue
}

EQ implements equivalence Operator. It is based on Column struct.

func (*EQ) Where 

func (op *EQ) Where(truthy bool) string

Where returns a where clause for the equation. It handles nil, scalar, and slice values.

type FieldList 

type FieldList struct {
	Fields []FieldListItem
	Type   reflect.Type
	Joins  map[string]*FieldList
}

FieldList stores fields of a reflectx.StructMap's Index (from sqlx), with the structure's type

func (*FieldList) BelongsToFieldsFor 

func (fl *FieldList) BelongsToFieldsFor(relation, tableref, tablename string) ([]string, []string, error)

BelongsToFieldsFor converts FieldListItems to JOIN and SELECTs query substrings SQL query buildders can use directly

func (*FieldList) FieldsFor 

func (fl *FieldList) FieldsFor() ([]QueryField, error)

FieldsFor converts FieldListItems to query fields SQL query builders can use. It doesn't include related fields.

func (*FieldList) HasNFieldsFor 

func (fl *FieldList) HasNFieldsFor(relation, tableref string, field FieldListItem, typeMapper func(reflect.Type) *FieldList) ([]string, []string, error)

HasNFieldsFor queries related model to build JOIN and SELECTs query substrings SQL query buildders can use directly. It uses a callback, which can provide a *FieldList from the referenced type.

func (*FieldList) RelatedFieldsFor 

func (fl *FieldList) RelatedFieldsFor(relation, tableref string, cb func(reflect.Type) *FieldList) (joins []string, selects []string, err error)

RelatedFieldsFor converts FieldListItems to JOINs and SELECTs SQL query builders can use directly

func (*FieldList) TraversalsByName 

func (fl *FieldList) TraversalsByName(columns []string) (Traversals, error)

TraversalsByName provides a traversal index for SELECT query results, to map result rows' columns with model's entry positions

type FieldListItem 

type FieldListItem struct {
	reflect.Type
	Field     reflect.StructField
	Index     []int
	Name      string
	Options   map[string]string
	Path      string
	Traversed bool
}

FieldListItem is a line item of a model's field list

func (FieldListItem) QField 

func (fi FieldListItem) QField() *QueryField

QField returns a query field based on a FieldListItem

type GroupOperator 

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

GroupOperator is an iternal data structure for an operator with multiple sub-operators

func NewGroupOp 

func NewGroupOp(op string, items ...Operator) *GroupOperator

NewGroupOp returns a new group of operators

func (*GroupOperator) Add 

func (op *GroupOperator) Add(ops ...Operator)

Add adds more operatorn to an existing GroupOperator

func (*GroupOperator) Keys 

func (op *GroupOperator) Keys() []string

Keys returns all the keys found in its sub-operators in order

func (*GroupOperator) Values 

func (op *GroupOperator) Values() map[string]interface{}

Values returns all the values found in its sub-operators

func (*GroupOperator) Where 

func (op *GroupOperator) Where(truthy bool) string

Where is a helper function for implementer structs to provide all where clauses

type Grouper 

type Grouper interface {
	Add(...Operator)
}

Grouper interface denotes a group operator, where multiple sub-operators can be added into

type Mapper 

type Mapper struct {
	Conn *sqlx.DB
	// contains filtered or unexported fields
}

Mapper is our PGSQL connection struct

func New 

func New(connString string) *Mapper

New sets up a new SQL connection. It sets up a "black hole" logger too.

func (*Mapper) All 

func (m *Mapper) All(models interface{}) error

All returns all elements into an array of models

func (*Mapper) Create 

func (m *Mapper) Create(model interface{}) error

Create inserts an item into the database

func (*Mapper) Delete 

func (m *Mapper) Delete(model interface{}, id int64) error

Delete deletes a row

func (*Mapper) Exec 

func (m *Mapper) Exec(query string, args ...interface{}) (sql.Result, error)

Exec runs sqlx.Exec nicely. It opens database if needed, and logs the query.

func (*Mapper) FieldList added in v0.2.0 

func (m *Mapper) FieldList(t reflect.Type) *FieldList

FieldList returns a map of types in the form of a StructMap, from the original model's type

func (*Mapper) FieldMap 

func (m *Mapper) FieldMap(model interface{}) map[string]reflect.Value

FieldMap returns a map of fields for a model. It handles pointer of model.

func (*Mapper) Find 

func (m *Mapper) Find(model interface{}, id int64) error

Find searches database for a row by ID

func (*Mapper) FindBy 

func (m *Mapper) FindBy(model interface{}, column string, needle string) error

FindBy searches database for a row by a column match

func (*Mapper) Get 

func (m *Mapper) Get(dest interface{}, query string, args ...interface{}) error

Get runs sqlx.Get nicely. It opens database if needed, and logs the query.

func (*Mapper) HealthReport 

func (m *Mapper) HealthReport(ctx context.Context) (healthy bool, errors map[string]string)

HealthReport returns healthy status, or map of issues. Currently, a closed database is reported as an error.

func (*Mapper) Logger 

func (m *Mapper) Logger(logger *logrus.Logger)

Logger sets up internal log method, replacing the discarding logger.

func (*Mapper) Name 

func (m *Mapper) Name() string

Name returns module name. Used for subsystem health checks.

func (*Mapper) NamedExec 

func (m *Mapper) NamedExec(query string, arg interface{}) (sql.Result, error)

NamedExec runs sqlx.NamedExec nicely. It opens database if needed, and logs the query.

func (*Mapper) NamedQuery 

func (m *Mapper) NamedQuery(query string, arg interface{}) (*sqlx.Rows, error)

NamedQuery runs sqlx.NamedQuery nicely. It opens database if needed, and logs the query.

func (*Mapper) NewSelect 

func (m *Mapper) NewSelect(model interface{}) (*SelectQuery, error)

NewSelect returns a new SelectQuery with the provided model attached

func (*Mapper) Open 

func (m *Mapper) Open() error

Open opens connection to the database. It is implicitly called by db calls defined in Mapper, but sometimes it's desirable to make sure the database is available at start time.

func (*Mapper) Queryx 

func (m *Mapper) Queryx(query string, args ...interface{}) (*sqlx.Rows, error)

Queryx runs sqlx.Queryx nicely. It opens database if needed, and logs the query.

func (*Mapper) Select 

func (m *Mapper) Select(dest interface{}, query string, args ...interface{}) error

Select runs sqlx.Select nicely. It opens database if needed, and logs the query.

func (*Mapper) Update 

func (m *Mapper) Update(model interface{}) error

Update inserts an item into the database

type NOT 

type NOT struct {
	Operator
}

NOT is a simple negate operator struct

func Not 

func Not(op Operator) *NOT

Not returns a negating version for an already existing operator.

func (*NOT) Where 

func (op *NOT) Where(truthy bool) string

Where is calls the original operator with flipping truthy flag

type NULL 

type NULL struct {
	ColumnValue
}

NULL implements IS NULL operator. It is based on Column struct.

func Null 

func Null(col string, value bool) *NULL

Null creates a new NULL operator

func (*NULL) Values 

func (op *NULL) Values() map[string]interface{}

Values returns NULL operator's values

func (*NULL) Where 

func (op *NULL) Where(truthy bool) string

Where returns NULL operator's where clause in positive (truthy) or negative (falsy) manner.

type OR 

type OR struct {
	*GroupOperator
}

OR is an operator struct, with OR relation between each item

func Or 

func Or(ops ...Operator) *OR

Or returns a new OR operator with initial values

func (*OR) Add 

func (op *OR) Add(ops ...Operator)

Add adds more items into the OR operator

func (*OR) Or 

func (op *OR) Or(ops ...Operator) *OR

Or is an alias to Add, and it returns itself for chaining

func (*OR) Where 

func (op *OR) Where(truthy bool) string

Where returns the where clause of all sub-operators stringed together into an OR clause

type Operator 

type Operator interface {
	Where(bool) string
	Keys() []string
	Values() map[string]interface{}
}

Operator describes an operator, in which queries can build their WHERE clauses.

func BinaryOp added in v0.2.0 

func BinaryOp(col string, value interface{}, truthy, falsy string) Operator

BinaryOp Returns a 2-parameter operator, with a truthy or falsy operator between column and value

Example: BinaryOp("column", 4, ">=", "<") yields `column >= 4` in normal query, or `column < 4` in negated form.

func Eq 

func Eq(col string, value interface{}) Operator

Eq returns an equivalence operator, requesting a certain column should have a certain value. It handles arrays too.

func Ge added in v0.2.0 

func Ge(col string, value interface{}) Operator

Ge returns a >= orperator

func Gt added in v0.2.0 

func Gt(col string, value interface{}) Operator

Gt returns a > orperator

func Le added in v0.2.0 

func Le(col string, value interface{}) Operator

Le returns a <= operator

func Lt added in v0.2.0 

func Lt(col string, value interface{}) Operator

Lt returns a < operator

type QueryField added in v0.2.0 

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

QueryField is a conversion struct for building INSERT or UPDATE queries

type SelectQuery 

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

SelectQuery represent a new SELECT query builder

func (*SelectQuery) All 

func (q *SelectQuery) All() error

All executes SELECT query, returning all the items selected. This call evaluates provided parameters, builds SQL query, and populates model slice the SelectQuery is created with.

func (*SelectQuery) Join 

func (q *SelectQuery) Join(selectors ...string) *SelectQuery

Join prepares query for joining tables, which will populate referenced submodels. This supports "has one," "has many," and "many to many" relations. Parameters have to reference db field names, which are also some sort of relations. Examples:

```golang 

type Table struct {
    ID           int
    WeBelongToID int             `db:"we_belong_to_id"`
    WeBelongTo   TableWeBelongTo `db:"we_belong_to,belongs"`
    HasOne       OtherTable      `db:"has_one,relation=table"`
    HasMany      []*ManyTable    `db:"has_many,relation=table"`
    ManyToMany   []*M2MTable     `db:"many_to_many,relation=table,reverse=mtm,through=table_m2ms"`
}

```

represents four join possibilities: "we_belong_to," "has_one," and "has_many." In the first example, we expect the other item's ID stored with the name of the relation + "_id" ("we_belong_to_id"), In the other two examples, we expect our ID to be stored at the other end with the name of the value of "relation" optional tag + "_id" ("table_id"). In the last example, we also expect that the relation is set up in "table_m2ms" table, and it also has a has many relationship with M2MTable, referencing its ID as "reverse" optional tag + "_id" ("mtm_id") in the "through" table ("table_m2ms") too.

func (*SelectQuery) Select 

func (q *SelectQuery) Select(selectors ...string) *SelectQuery

Select sets columns to be selected into model. By default, all fields in the model and its joined relations are populated.

func (*SelectQuery) Where 

func (q *SelectQuery) Where(op Operator) *SelectQuery

Where sets where clauses to the SELECT query, using Operator interface. Calling it multiple times will yield an AND relationship among operators.

type Traversal added in v0.2.0 

type Traversal struct {
	Name     string
	Index    []int
	Relation reflect.StructField
}

Traversal stores dial information for each column into the resulting model instance

type Traversals added in v0.2.0 

type Traversals []*Traversal

Traversals is an array of Traversal

func (Traversals) Map added in v0.2.0 

func (t Traversals) Map(v reflect.Value, values []interface{}) error

Map creates new empty model struct instance, and fills values slice with pointers to model elements by traversal indexes. This makes sqlx.Scan load rows directly into model instances.

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.