Golang struct-to-table database mapper

Related tags

ORM go squirrel
Overview

Structable: Struct-Table Mapping for Go

Stability: Sustained

Warning: This is the Structable 4 development branch. For a stable release, use version 3.1.0. Structable development happens very slowly.

This library provides basic struct-to-table mapping for Go.

It is based on the Squirrel library.

What It Does

Structable maps a struct (Record) to a database table via a structable.Recorder. It is intended to be used as a back-end tool for building systems like Active Record mappers.

It is designed to satisfy a CRUD-centered record management system, filling the following contract:

  type Recorder interface {
    Bind(string, Record) Recorder // link struct to table
    Interface() interface{}  // Get the struct that has been linked
    Insert() error // INSERT just one record
    Update() error // UPDATE just one record
    Delete() error // DELETE just one record
    Exists() (bool, error) // Check for just one record
    ExistsWhere(cond interface{}, args ...interface{}) (bool, error)
    Load() error  // SELECT just one record
    LoadWhere(cond interface{}, args ...interface{}) error // Alternate Load()
  }

Squirrel already provides the ability to perform more complicated operations.

How To Install It

The usual way...

$ glide get github.com/Masterminds/structable
$ # or...
$ go get github.com/Masterminds/structable

And import it via:

import "github.com/Masterminds/structable"

How To Use It

GoDoc

Structable works by mapping a struct to columns in a database.

To annotate a struct, you do something like this:

  type Stool struct {
    Id		 int	`stbl:"id, PRIMARY_KEY, AUTO_INCREMENT"`
    Legs	 int    `stbl:"number_of_legs"`
    Material string `stbl:"material"`
    Ignored  string // will not be stored. No tag.
  }

To manage instances of this struct, you do something like this:

  stool := new(Stool)
  stool.Material = "Wood"
  db := getDb() // Get a sql.Db. You're on  the hook to do this part.

  // Create a new structable.Recorder and tell it to
  // bind the given struct as a row in the given table.
  r := structable.New(db, "mysql").Bind("test_table", stool)

  // This will insert the stool into the test_table.
  err := r.Insert()

And of course you have Load(), Update(), Delete() and so on.

The target use case for Structable is to use it as a backend for an Active Record pattern. An example of this can be found in the structable_test.go file

Most of Structable focuses on individual objects, but there are helpers for listing objects:

// Get a list of things that have the same type as object.
stool := new(Stool)
items, err := structable.List(stool, offset, limit)

// Customize a list of things that have the same type as object.
fn = func(object structable.Describer, sql squirrel.SelectBuilder) (squirrel.SelectBuilder, error) {
  return sql.Limit(10), nil
}
items, err := structable.ListWhere(stool, fn)

For example, here is a function that uses ListWhere to get collection of definitions from a table described in a struct named Table:

func (s *SchemaInfo) Tables() ([]*Table, error) {

  // Bind a new recorder. We use an empty object just to get the field
  // data for that struct.
	t := &Table{}
	st := structable.New(s.Queryer, s.Driver).Bind(t.TableName(), t)

  // We want to return no more than 10 of these.
	fn := func(d structable.Describer, q squirrel.SelectBuilder) (squirrel.SelectBuilder, error) {
		return q.Limit(10), nil
	}

  // Fetch a list of Table structs.
	items, err := structable.ListWhere(st, fn)
	if err != nil {
		return []*Table{}, err
	}

  // Because we get back a []Recorder, we need to get the original data
  // back out. We have to manually convert it back to its real type.
	tables := make([]*Table, len(items))
	for i, item := range items {
		tables[i] = item.Interface().(*Table)
	}
	return tables, nil
}

Tested On

  • MySQL (5.5)
  • PostgreSQL (9.3, 9.4, 9.6)
  • SQLite 3

What It Does Not Do

It does not...

  • Create or manage schemas.
  • Guess or enforce table or column names. (You have to tell it how to map.)
  • Provide relational mapping.
  • Handle bulk operations (use Squirrel for that)

LICENSE

This software is licensed under an MIT-style license. See LICENSE.txt

Issues
  • Sqlite integration test suite

    Sqlite integration test suite

    As proposed in PR #5, here is the integration test suite performed with an sqlite in-memory database.

    It is composed of 2 files:

    • sqlite_pod_test.go: tests performed on plain-old-data fields only
    • sqlite_ptr_test.go: tests performed on a structure with a pointer field. Both cases are tested in each test function (nil and non-nil pointer)

    The sqlite3 driver is provided by github.com/mattn/go-sqlite3 package

    opened by arl 4
  • New Feature: handle pointer fields

    New Feature: handle pointer fields

    I'm proposing a new feature for Structable, the handling of pointer fields.

    The main advantage to have this feature implemented in Structable is that pointer fields play well when you want to hide (or not include) some fields of a structure, before marshall it into JSON, or more generally to let a structure have different representations, depending on wether some pointer fields are nil.

    Let's take this struct as an example:

    type User struct {
        Id       int     `stbl:"id,PRIMARY_KEY" json:"id"`
        Name     string  `stbl:"name"           json:"name"`
        Email    string  `stbl:"email"          json:"email"`
        Password *string `stbl:"email"          json:"password,omitempty"`
    }
    

    This hypothetical User struct can be used as-is for both database mapping (through Structable) and Json marshalling. Json omitempty requires the field to be a pointer, it is an easy way to represent the absence of a field. We can now have different representations of an User, one with the password being included and another with the password hidden. Some other use cases exist. Some basic tests for pointer fields have been added to the existing test suite, they and included in this PR.

    PS: I wrote a test suite, using an in-memory sqlite database backend, that could be considered as integration tests, however I didn't include it so as to keep focused the field of this PR focused on the feature. If you are interested I can push it here or in another PR.

    opened by arl 2
  • Hooks?

    Hooks?

    Do you think hooks fit in structable's design? For example, I have a struct

    type Object struct {
        Id int `stbl:"id, PRIMARY_KEY, AUTO_INCREMENT"`
        PropsJSON string `stbl:"props_json"`
        CreatedAt time.Time `stbl:"create_at"`
        UpdatedAt time.Time `stbl:"updated_at"`
    
        Props map[string]string
    }
    

    I want CreateAt and UpdatedAt updated and PropsJSON <=> Props get transformed automatically. My first thought is that if structable supports Hooks I can write following methods:

    // called before Insert
    func (this *Object) BeforeInsert() {
        this.CreatedAt = time.Now()
    }
    
    // called before Update
    func (this *Object) BeforeUpdate() {
        this.UpdatedAt = time.Now()
    }
    
    // called before Insert and Update
    func (this *Object) BeforeSave() {
        _, this.ProsJSON = json.Marshal(&this.Props)
    }
    
    // called after Load
    func (this *Object) AfterLoad() {
        json.Unmarshal([]byte(this.PropsJSON), &this.Props)
    }
    

    Do you think this is a good idea?

    Thanks!

    opened by fluxxu 1
  • ListWhere() silently fails and returns empty objects

    ListWhere() silently fails and returns empty objects

    It seems that ListWhere() is unable to convert a []Recorder back to its real type and returns an empty base object. Further investigation showed that there is a contradiction within the function on how to handle keys.

    The below snippet shows that the includeKeys parameter of Column() is false hence columns that are marked as keys will be omitted from the returned list.

    https://github.com/Masterminds/structable/blob/a1a302ef78ec3d85606dcf104a9a168542004036/structable.go#L309

    However, later the withKeys parameter of FieldReferences() is true hence fields marked as keys will be included in the returned list.

    https://github.com/Masterminds/structable/blob/a1a302ef78ec3d85606dcf104a9a168542004036/structable.go#L340

    This will currently result in a mismatch between the number of columns and throw an ERROR: sql: expected 19 destination arguments in Scan, not 20 message. Unfortunately, errors returned by rows.Scan(dest...) are ignored hence the function will silently fail and return an empty base object.

    opened by sgabe 1
  • What struct types -> db types does this support?

    What struct types -> db types does this support?

    Is there a listing of type support for this library? I am currently using a struct like this, and was wondering if structable would automatically transform the *timestamp.Timestamp into a postgresql DATE type.

    type PgData struct {
    	TenantID  string 
    	AssetID   string 
    	JobID     string 
    	FormatID  string
    	Status    status
    	StartTime *timestamp.Timestamp
    	EndTime   *timestamp.Timestamp
    }
    
    opened by rustysys-dev 0
SQL mapper ORM framework for Golang

SQL mapper ORM framework for Golang English 中文 Please read the documentation website carefully when using the tutorial. DOC Powerful Features High Per

null 0 Dec 8, 2021
Mybatis for golang - SQL mapper ORM framework

SQL mapper ORM framework for Golang English 中文 Please read the documentation website carefully when using the tutorial. DOC Powerful Features High Per

Tim 2 Jun 17, 2022
A example of a join table using liquibase and gorm

A example of a join table using liquibase and gorm. Additionally the join table's composite key is used as a composite foreign key for another table.

null 1 Feb 4, 2022
Crud - A mysql crud code generate tool from table DDL sql file

crud is a mysql crud code generate tool 中文文档 Getting Started Overview Crud is a

null 18 Jun 17, 2022
Gorm-sharding - High performance table sharding plugin for Gorm

Gorm Sharding This project has moved to Gorm offical organization: https://githu

Longbridge 65 Jun 26, 2022
A pure golang SQL database for learning database theory

Go SQL DB 中文 "Go SQL DB" is a relational database that supports SQL queries for research purposes. The main goal is to show the basic principles and k

auxten 678 Jun 21, 2022
Automatically generate tags for golang struct.

gtag is a command tool that can automatically generate tags for golang struct. Quick start Install gtag into your GOPATH go install github.com/sycki/g

null 2 Feb 12, 2022
QBS stands for Query By Struct. A Go ORM.

Qbs Qbs stands for Query By Struct. A Go ORM. 中文版 README ChangeLog 2013.03.14: index name has changed to {table name}_{column name}. For existing appl

Evan Zhou 551 Jun 23, 2022
Golang Event Scheduling Sample Using Postgresql Database as persisting layer.

Database Based Event Scheduling Example that demonstrates super basic database based event scheduling. To run this example; Copy .env.example to .env

Dipesh Dulal 36 Jun 22, 2022
beedb is a go ORM,support database/sql interface,pq/mysql/sqlite

Beedb ❗ IMPORTANT: Beedb is being deprecated in favor of Beego.orm ❗ Beedb is an ORM for Go. It lets you map Go structs to tables in a database. It's

astaxie 709 Mar 8, 2022
Database agnostic ORM for Go

If you are looking for something more lightweight and flexible, have a look at jet For questions, suggestions and general topics visit the group. Inde

Erik Aigner 694 Jun 16, 2022
Generate a Go ORM tailored to your database schema.

SQLBoiler is a tool to generate a Go ORM tailored to your database schema. It is a "database-first" ORM as opposed to "code-first" (like gorm/gorp). T

Volatile Technologies Inc. 5k Jun 23, 2022
Converts a database into gorm structs and RESTful api

gen The gen tool produces a CRUD (Create, read, update and delete) REST api project template from a given database. The gen tool will connect to the d

smallnest 1.2k Jun 23, 2022
Simple project in Go to play around with some CRUD operations using a PostgreSQL database and pgx

Record Store November 2021 I started learning Go a few weeks ago and this is my first proper project using Go. I wanted to use it to get to grips with

Katie Hawcutt 0 Nov 26, 2021
Examples of using various popular database libraries and ORM in Go.

Introduction Examples of using various popular database libraries and ORM in Go. sqlx sqlc Gorm sqlboiler ent The aim is to demonstrate and compare us

Hafiz Shafruddin 0 Dec 12, 2021
Examples of using various popular database libraries and ORM in Go.

Introduction Examples of using various popular database libraries and ORM in Go. sqlx sqlc Gorm sqlboiler ent The aim is to demonstrate and compare us

Hafiz Shafruddin 3 Mar 18, 2022
100% type-safe ORM for Go (Golang) with code generation and MySQL, PostgreSQL, Sqlite3, SQL Server support. GORM under the hood.

go-queryset 100% type-safe ORM for Go (Golang) with code generation and MySQL, PostgreSQL, Sqlite3, SQL Server support. GORM under the hood. Contents

Denis Isaev 663 Jun 15, 2022
An orm library support nGQL for Golang

norm An ORM library support nGQL for Golang. Overview Build insert nGQL by struct / map (Support vertex, edge). Parse Nebula execute result to struct

Zhihu 69 Jun 22, 2022
golang orm

korm golang orm, 一个简单易用的orm, 支持嵌套事务 安装 go get github.com/wdaglb/korm go get github.com/go-sql-driver/mysql 支持数据库 mysql https://github.com/go-sql-driv

null 6 Oct 11, 2021