Minimalistic Idiomatic Database "ORM" Functionality for Go

Features

• Structs have SQL table information associated with them - tmeta knows that your WidgetFactory struct corresponds to the "widget_factory" table (names configurable, of course).
• Useful struct tags to express things like primary keys and relations. Sensible defaults but easily configurable.
• Relation support (belongs to, has many, has one, belongs to many, belongs to many IDs)
• Builds queries using dbr, simple and flexible, avoids the cruft of more complex solutions. Supports common operations like CRUD, loading relations, and maintaining join tables.
• Operations are succinct and explicit, not too magical, we're not trying to be Hiberate (or GORM for that matter); this is Go.
• Does not require or expect you to embed a special "Model" type, your structs remain simple, no additional dependencies in your model. (E.g. should not interfere with existing lightweight database packages like sqlx)
• Optimistic locking (version column)
• Date Created/Updated functionality
• Normal underlying DB features like transactions and context support are not hidden from you and easily usable.
• Primary keys can be string/UUID (recommended) or auto-incremented integer.
• We don't do DDL, this makes things simpler. (DDL is necessary but should be separate.)
• Supports SQLite3, MySQL, Postgres

GoDoc

• tmeta (table/type information) https://godoc.org/github.com/gocaveman/tmeta
• tmetadbr (query building) https://godoc.org/github.com/gocaveman/tmeta/tmetadbr

Basic CRUD

The tmeta package understands your types and tmetadbr provides query building using dbr.

To get set up and concisely run your queries:

type Widget struct {
	WidgetID string `db:"widget_id" tmeta:"pk"`
	Name     string `db:"name"`
}

// register things
meta := tmeta.NewMeta()
err := meta.Parse(Widget{})

// database connection with dbr
conn, err := dbr.Open(...)

// initialize a session
sess := conn.NewSession(nil)

// use a Builder to construct queries
b := tmetadbr.New(sess, meta)

// don't get scared by the "Must" in front of these functions, it applies to the
// query building steps, not to the queries themselves

// create
widgetID := gouuidv6.NewB64().String() // use what you want for an ID, my personal favorite: github.com/bradleypeabody/gouuidv6
_, err = b.MustInsert(&Widget{WidgetID: widgetID,Name: "Widget A"}).Exec()

// read multiple
var widgetList []Widget
// notice you can modify the queries before they are executed - where, limit, order by, etc.
_, err = b.MustSelect(&widgetList).Where("name LIKE ?", `Widget%`).Load(&widgetList)

// read one
var widget Widget
err = b.MustSelectByID(&widget, widgetID).LoadOne(&widget)

// update
widget.Name = "Widget A1"
_, err = b.MustUpdateByID(&widget).Exec()

// delete
_, err = b.MustDeleteByID(Widget{}, widgetID).Exec()

Variations of these methods without Must are available if you want type errors to be returned instead of panicing.

Table Info

tmeta understands basic properties about your tables such as SQL table name, the primary key(s), and the list of fields. These things can be easily set up once and then don't have to be repated in your code, resulting in code that is safer, more resilient to change, still simple and generally just easier to maintain.

type Widget struct {
	WidgetID string `db:"widget_id" tmeta:"pk"`
	Name     string `db:"name"`
}

// a Meta instance holds info for a group of tables (usually all your tables)
meta := tmeta.NewMeta()

// parse the struct tags and add the table to your Meta instance
// note that pointers are automatically dereferenced throughout, so for the purpose
// of table metadata a Widget and *Widget are treated the same
err := meta.Parse(Widget{})

// fetch the table name
tableName := meta.For(Widget{}).SQLName()

// you can also set the SQL table name, useful for prefixing tables
meta.For(Widget{}).SetSQLName("demoapp_widget")

// code can fetch table info either by struct type
widgetTI := meta.For(Widget{})
// or by logical name
widgetTI = meta.ForName("widget")

// get a slice of primary key SQL field names (tagged with `tmeta:"pk"`)
pkFieldSlice := widgetTI.SQLPKFields()

// get a slice of all of the SQL fields, including the primary keys
fieldSlice := widgetTI.SQLFields(true)

// or without the pks
fieldSlice = widgetTI.SQLFields(false)

Have a look at the TableInfo godoc for a full list of what's available.

This functionality from tmeta is what is used by tmetadbr to implement higher level query building.

Relations

With tmetadbr you can also easily generate the SQL to load related records.

Has Many

A "has many" relation is used by one table where a second one has an ID that points back to it.

type Author struct {
	AuthorID   string `db:"author_id" tmeta:"pk"`
	NomDePlume string `db:"nom_de_plume"`
	BookList   []Book `db:"-" tmeta:"has_many"`
}

type Book struct {
	BookID   string `db:"book_id" tmeta:"pk"`
	AuthorID string `db:"author_id"`
	Title    string `db:"title"`
}


authorT := b.For(Author{})

_, err = b.MustSelectRelation(&author, "book_list").
	Load(authorT.RelationTargetPtr(&author, "book_list"))

// or you can load directly into the field, less dynamic but shorter and more clear
_, err = b.MustSelectRelation(&author, "book_list").Load(&author.BookList)

Has One

A "has_one" relation is like a "has_many" but is used for a one-to-one relation.

type Category struct {
	CategoryID   string        `db:"category_id" tmeta:"pk"`
	Name         string        `db:"name"`
	CategoryInfo *CategoryInfo `db:"-" tmeta:"has_one"`
}

type CategoryInfo struct {
	CategoryInfoID string `db:"category_info_id" tmeta:"pk"`
	CategoryID     string `db:"category_id"`
}

categoryT := b.For(Category{})
err = b.MustSelectRelation(&category, "category_info").
	LoadOne(categoryT.RelationTargetPtr(&category, "category_info")))

Belongs To

A "belongs_to" relation is the inverse of a "has_many", it is used when the ID is on the same table as the field being loaded.

type Author struct {
	AuthorID   string `db:"author_id" tmeta:"pk"`
	NomDePlume string `db:"nom_de_plume"`
}

type Book struct {
	BookID   string  `db:"book_id" tmeta:"pk"`
	AuthorID string  `db:"author_id"`
	Author   *Author `db:"-" tmeta:"belongs_to"`
	Title    string  `db:"title"`
}

bookT := b.For(Book{})
assert.NoError(b.MustSelectRelation(&book, "author").
	LoadOne(bookT.RelationTargetPtr(&book, "author")))

Belongs To Many

A "belongs_to_many" relation is used for a many-to-many relation, using a join table.

type Book struct {
	BookID string           `db:"book_id" tmeta:"pk"`
	Title string            `db:"title"`
	CategoryList []Category `db:"-" tmeta:"belongs_to_many,join_name=book_category"`
}

// BookCategory is the join table
type BookCategory struct {
	BookID     string `db:"book_id" tmeta:"pk"`
	CategoryID string `db:"category_id" tmeta:"pk"`
}

type Category struct {
	CategoryID string `db:"category_id" tmeta:"pk"`
	Name       string `db:"name"`
}

_, err = b.MustSelectRelation(&book, "category_list").
	Load(bookT.RelationTargetPtr(&book, "category_list"))

Belongs To Many IDs

A "belongs_to_many_ids" relation is similar to a "belongs_to_many" but instead of the field being a slice of structs, it is a slice of IDs, and methods are provided to easily synchronize the join table.

type Book struct {
	BookID string           `db:"book_id" tmeta:"pk"`
	Title string            `db:"title"`
	CategoryIDList []string `db:"-" tmeta:"belongs_to_many_ids,join_name=book_category"`
}

// BookCategory is the join table
type BookCategory struct {
	BookID     string `db:"book_id" tmeta:"pk"`
	CategoryID string `db:"category_id" tmeta:"pk"`
}

type Category struct {
	CategoryID string `db:"category_id" tmeta:"pk"`
	Name       string `db:"name"`
}

// set the list of IDs in the join
book.CategoryIDList = []string{"category_0001","category_0002"}
// to synchronize we first delete any not in the new set
err = b.ExecOK(b.MustDeleteRelationNotIn(&book, "category_id_list"))
// and then insert (with ignore) the set
err = b.ExecOK(b.MustInsertRelationIgnore(&book, "category_id_list"))

// and you load it like any other relation
book.CategoryIDList = nil
_, err = b.MustSelectRelation(&book, "category_id_list").
	Load(bookT.RelationTargetPtr(&book, "category_id_list"))

Relation Targets

When selecting relations, the query builder needs the object to inspect and the name of the relation. However, the call to actually execute the select statement also needs to know the "target" field to load into.

The examples above use SelectRelation and RelationTargetPtr pointer to do this. This allows you to dynamically load a relation by only knowing it's name. If you are hard coding for a specific relation you can pass a pointer to the field itself. SelectRelationPtr is also available and returns the pointer when the query is built. Pick your poison.

Create and Update Timestamps, and Dates in General

Create and update timestamps will be updated at the appropriate time with a simple call to the corresponding method on your struct, if it exists:

// called on insert
func (w *Widget) CreateTimeTouch() { ... }

// called on update
func (w *Widget) UpdateTimeTouch() { ... }

That part is easy.

To get date-time information to work correctly on multiple databases/drivers and make use of Go's time.Time, marshal to/from JSON correctly, have subsecond precision, is human readable, and avoid time zone confusion, it is useful to make a custom type that deals with these concerns.

In SQLite3 use TEXT as the column type, in Postgres use DATETIME and in MySQL use DATETIME(6) (requires 5.6.4 or above for subsecond precision).

Here