Database agnostic ORM for Go

Related tags

ORM hood
Overview

If you are looking for something more lightweight and flexible, have a look at jet

For questions, suggestions and general topics visit the group.

Index

Overview

Hood is a database agnostic ORM for Go developed by @eaignr. It was written with following points in mind:

  • Chainable API
  • Transaction support
  • Migration and schema generation
  • Model validations
  • Model event hooks
  • Database dialect interface
  • No implicit fields
  • Clean and testable codebase

Dialects currently implemented

Adding a dialect is simple. Just create a new file named <dialect_name>.go and the corresponding struct type, and mixin the Base dialect. Then implement the methods that are specific to the new dialect (for an example see postgres.go).

Documentation

You can find the documentation over at GoDoc. To get a sense of the API, it's best to take a quick look at the unit tests, as they are always up to date!

Opening a Database

If the dialect is registered, you can open the database directly using

hd, err := hood.Open("postgres", "user=<username> dbname=<database>")

or you can pass an existing database and dialect to hood.New(*sql.DB, hood.Dialect)

hd := hood.New(db, NewPostgres())

Schemas

Schemas can be declared using the following syntax (only for demonstration purposes, would not produce valid SQL since it has 2 primary keys)

type Person struct {
  // Auto-incrementing int field 'id'
  Id hood.Id

  // Custom primary key field 'first_name', with presence validation
  FirstName string `sql:"pk" validate:"presence"`

  // string field 'last_name' with size 128, NOT NULL
  LastName string `sql:"size(128),notnull"`

  // string field 'tag' with size 255, default value 'customer'
  Tag string `sql:"size(255),default('customer')"`

  // You can also combine tags, default value 'orange'
  CombinedTags string `sql:"size(128),default('orange')"`
  Birthday     time.Time    // timestamp field 'birthday'
  Data         []byte       // data field 'data'
  IsAdmin      bool         // boolean field 'is_admin'
  Notes        string       // text field 'notes'

  // You can alternatively define a var char as a string field by setting a size
  Nick  string  `sql:"size(128)"`

  // Validates number range
  Balance int `validate:"range(10:20)"`

  // These fields are auto updated on save
  Created hood.Created
  Updated hood.Updated

  // ... and other built in types (int, uint, float...)
}

// Indexes are defined via the Indexed interface to avoid
// polluting the table fields.

func (table *Person) Indexes(indexes *hood.Indexes) {
  indexes.Add("tag_index", "tag") // params: indexName, unique, columns...
  indexes.AddUnique("name_index", "first_name", "last_name")
}

Schema creation is completely optional, you can use any other tool you like.

The following built in field properties are defined (via sql: tag):

  • pk the field is a primary key
  • notnull the field must be NOT NULL
  • size(x) the field must have the specified size, e.g. for varchar size(128)
  • default(x) the field has the specified default value, e.g. default(5) or default('orange')
  • - ignores the field

Migrations

To use migrations, you first have to install the hood tool. To do that run the following:

go get github.com/eaigner/hood
cd $GOPATH/src/github.com/eaigner/hood
./install.sh

Assuming you have your $GOPATH/bin directory in your PATH, you can now invoke the hood tool with hood. Before we can use migrations we have to create a database configuration file first. To do this type

hood create:config

This command will create a db/config.json file relative to your current directory. It will look something like this:

{
  "development": {
    "driver": "",
    "source": ""
  },
  "production": {
    "driver": "",
    "source": ""
  },
  "test": {
    "driver": "",
    "source": ""
  }
}

Populate it with your database credentials. The driver and source fields are the strings you would pass to the sql.Open(2) function. Now hood knows about our database, so let's create our first migration with

hood create:migration CreateUserTable

and another one

hood create:migration AddUserNameIndex

This command creates new migrations in db/migrations. Next we have to populate the generated migrations Up (and Down) methods like so:

func (m *M) CreateUserTable_1357605106_Up(hd *hood.Hood) {
  type Users struct {
    Id		hood.Id
    First	string
    Last	string
  }
  hd.CreateTable(&Users{})
}
func (m *M) AddUserNameIndex_1357605116_Up(hd *hood.Hood) {
  hd.CreateIndex("users", "name_index", true, "first", "last")
}

The passed in hood instance is a transaction that will be committed after the method.

Now we can run migrations with

hood db:migrate

and roll back with

hood db:rollback

For a complete list of commands invoke hood -help

A schema.go file is automatically generated in the db directory. This file reflects the current state of the database! In our example, it will look like this:

package db

import (
  "github.com/eaigner/hood"
)

type Users struct {
  Id    hood.Id
  First string
  Last  string
}

func (table *Users) Indexes(indexes *hood.Indexes) {
  indexes.AddUnique("name_index", "first", "last")
}

Validation

Besides the sql: struct tag, you can specify a validate: tag for model validation:

  • presence validates that a field is set
  • len(min:max) validates that a string field’s length lies within the specified range
    • len(min:) validates that it has the specified min length,
    • len(:max) or max length
  • range(min:max) validates that an int value lies in the specific range
    • range(min:) validates that it has the specified min value,
    • range(:max) or max value
  • <regexp>, e.g. ^[a-z]+$, validates that a string matches the regular expression
    • the expression must start with ^
    • backslash and double quote should be escaped
    • does not work with other validation methods on the same field

You can also define multiple validations on one field, e.g. validate:"len(:12),presence"

For more complex validations you can use custom validation methods. The methods are added to the schema and must start with Validate and return an error.

For example:

func (u *User) ValidateUsername() error {
	rx := regexp.MustCompile(`[a-z0-9]+`)
	if !rx.MatchString(u.Name) {
		return NewValidationError(1, "username contains invalid characters")
	}
	return nil
}

Hooks

You can add hooks to a model to run on a specific action like so:

func (u *User) BeforeUpdate() error {
	u.Updated = time.Now()
	return nil
}

If the hook returns an error on a Before- action it is not performed!

The following hooks are defined:

  • Before/AfterSave
  • Before/AfterInsert
  • Before/AfterUpdate
  • Before/AfterDelete

Basic Example

package main

import (
	"hood"
)

func main() {
	// Open a DB connection, use New() alternatively for unregistered dialects
	hd, err := hood.Open("postgres", "user=hood dbname=hood_test sslmode=disable")
	if err != nil {
		panic(err)
	}

	// Create a table
	type Fruit struct {
		Id    hood.Id
		Name  string `validate:"presence"`
		Color string
	}

	err = hd.CreateTable(&Fruit{})
	if err != nil {
		panic(err)
	}

	fruits := []Fruit{
		Fruit{Name: "banana", Color: "yellow"},
		Fruit{Name: "apple", Color: "red"},
		Fruit{Name: "grapefruit", Color: "yellow"},
		Fruit{Name: "grape", Color: "green"},
		Fruit{Name: "pear", Color: "yellow"},
	}

	// Start a transaction
	tx := hd.Begin()

	ids, err := tx.SaveAll(&fruits)
	if err != nil {
		panic(err)
	}

	fmt.Println("inserted ids:", ids) // [1 2 3 4 5]

	// Commit changes
	err = tx.Commit()
	if err != nil {
		panic(err)
	}

	// Ids are automatically updated
	if fruits[0].Id != 1 || fruits[1].Id != 2 || fruits[2].Id != 3 {
		panic("id not set")
	}

	// If an id is already set, a call to save will result in an update
	fruits[0].Color = "green"

	ids, err = hd.SaveAll(&fruits)
	if err != nil {
		panic(err)
	}

	fmt.Println("updated ids:", ids) // [1 2 3 4 5]

	if fruits[0].Id != 1 || fruits[1].Id != 2 || fruits[2].Id != 3 {
		panic("id not set")
	}

	// Let's try to save a row that does not satisfy the required validations
	_, err = hd.Save(&Fruit{})
	if err == nil || err.Error() != "value not set" {
		panic("does not satisfy validations, should not save")
	}

	// Find
	//
	// The markers are db agnostic, so you can always use '?'
	// e.g. in Postgres they are replaced with $1, $2, ...
	var results []Fruit
	err = hd.Where("color", "=", "green").OrderBy("name").Limit(1).Find(&results)
	if err != nil {
		panic(err)
	}

	fmt.Println("results:", results) // [{1 banana green}]

	// Delete
	ids, err = hd.DeleteAll(&results)
	if err != nil {
		panic(err)
	}

	fmt.Println("deleted ids:", ids) // [1]

	results = nil
	err = hd.Find(&results)
	if err != nil {
		panic(err)
	}

	fmt.Println("results:", results) // [{2 apple red} {3 grapefruit yellow} {4 grape green} {5 pear yellow}]

	// Drop
	hd.DropTable(&Fruit{})
}
Comments
  • Removed Index Type, and move the functionality to tag.

    Removed Index Type, and move the functionality to tag.

    The tag's syntax is like sql:"index(.;*/other_column1/other_column2)" sql:"index(*)" where "." or "" is the alias of current field's snake name, "." means normal index, "" means unique index. "/" is used to separate column names. ";" is used to separate indexes.

    opened by coocood 13
  • Another propose about index definition.

    Another propose about index definition.

    How about we define a interface for a table then define the table property in method?

    like

    type Table interface{
        Properties() map[string]interface{}
    } 
    
    enhancement accepted 
    opened by coocood 12
  • Title case field naming

    Title case field naming

    I want to use this with a database that has a TitleCase naming convention -- is there any way to do it? I didn't see any way to override the snake cased naming convention.

    Thanks!

    opened by robfig 7
  • can't call functions on where close

    can't call functions on where close

    I was hoping to use the following code:

    hood.Where("lower(username)", "=", "eaigner").Limit(1).Find(&user)
    

    But it looks like hood is quoting the first argument resulting in a bad query (MySQL).

    opened by mattetti 6
  • Queried results end up camelcased instead of underscored

    Queried results end up camelcased instead of underscored

    All the results with underscore names become camelcased and are not added to the returned struct if it has underscored names. When creating the table, no warning is given about this and the table columns are not converted to camelcase either. No mention about this in the documentation.

    opened by andriussev 5
  • Support for ignoring fields in a struct

    Support for ignoring fields in a struct

    Hi again,

    I hit a snag when I was trying to override the default name of a struct's xml element.

    type Person struct {
        XMLName xml.Name `sql:"-" xml:"person" json:"-"`
        Id hood.Id `xml:"id,attr" json:"id"`
        FirstName string `validate:"presence" xml:"name>first" json:"first_name"`
        LastName hood.VarChar `sql:"size(128),notnull" xml:"name>last" json:"last_name"`
        Updated time.Time `xml:"dateUpdated" json:"date_updated"`
    }
    

    Previously the existence of the XMLName field would cause a panic: invalid sql type.

    Seeing as the only purpose of the XMLName field is to override behaviour specific to xml un/marshalling, I added a flag to the sql tag (based on the convention used in the json tag) allowing hood to ignore the field.

    feedback is appreciated, this is my first go at go.

    opened by StefanKjartansson 5
  • Null values cause a crash

    Null values cause a crash

    Trying to select from a table with a null value will cause a crash. Do something like this in SQL:

    create table test (a integer not null, b varchar); insert into test values (1, null);

    Then with Hood, if you do this, you'll get a panic:

    type Test struct { Id hood.Id Test string }

    vals := []Test{} hd.FindSQL(&vals, "select * from test")

    That crashes consistently.

    opened by iragsdale 4
  • Follow standard convention for struct tags

    Follow standard convention for struct tags

    Hey,

    I haven't glanced at the code yet, but based on the examples I'd like to highlight the established convention for struct tags. hood shouldn't claim the entire struct tag, and should coexist peacefully with other things that might be interested (such as encoding/json).

    There's an API in reflect, StructTag.Get, which will parse the standard format and give you only the requested section back. So, in all the places that you extract the entire StructTag from the struct using reflect, you can instead use StructTag.Get to parse out a "hood" section with the same behavior, which would allow coexistence like so:

    type User struct {
        Name string `json:"user_name,omitempty" hood:"pk"`
    }
    

    Very ideal for declaring options for both encoding/json and hood, which I would foresee as a common occurrence.

    accepted 
    opened by jedsmith 4
  • hood create:config fails db/config.json doesn't already exist

    hood create:config fails db/config.json doesn't already exist

    $ /bin/hood create:config
    panic: open /Users/justin/repos/goHood/db/config.json: no such file or directory
    
    goroutine 1 [running]:
    main.init·1()
        /usr/local/go/src/pkg/github.com/eaigner/hood/cmd/cmd.go:56 +0x54c
    main.init()
        /usr/local/go/src/pkg/github.com/eaigner/hood/cmd/templates.go:5 +0xf9
    
    goroutine 2 [syscall]:
    created by runtime.main
        /usr/local/go/src/pkg/runtime/proc.c:221
    
    critical accepted 
    opened by justinbarry 2
  • Run migration in application.

    Run migration in application.

    The current migration implementation seems not easy to use.

    I want migration to be executed in the application.

    We can provide some APIs to execute a sequence of migration and associate each migration command with a version number.

    the schema after each version of migration can be stored in local file, and will be used to check if new migration is added.

    And I also want database level migration, like create database, rename database.

    enhancement help-wanted 
    opened by coocood 2
  • A suggestion to simplify Indexes Add method.

    A suggestion to simplify Indexes Add method.

    Can we provide two methods for unique and non-unique index, like "Add" and "AddUnique", and remove the bool parameter. because pass bool parameter is harder to read.

    And if no columns parameter is passed in, the index name will be considered as the single column name. this way we only need to pass in one parameter most of the time.

    opened by coocood 2
  • feature request : dialect registered for driver sqlite3

    feature request : dialect registered for driver sqlite3

    package main
    
    import (
    	"github.com/eaigner/hood"
    	_ "github.com/mattn/go-sqlite3"
    	"fmt"
    
    )
    
    func main() {
    	// Open a DB connection, use New() alternatively for unregistered dialects
    	hd, err := hood.Open("sqlite3", "test.db")
    	if err != nil {
    		panic(err)
    	}
    
    	fmt.Println(hd)
    }
    
    

    panic: no dialect registered for driver name

    opened by tablecell 0
  • How use join?

    How use join?

    If there is example?. I didn't understand this =(

    // Join performs a JOIN on tables, for example
    // Join(hood.InnerJoin, &User{}, "user.id", "order.id")
    func (hood *Hood) Join(op Join, table interface{}, a Path, b Path) *Hood {
         hood.joins = append(hood.joins, &join{
              join: op,
              table: tableName(table),
              a: a,
              b: b,
         })
    return hood
    }
    
    opened by weldpua2008 0
Owner
Erik Aigner
Erik Aigner
Using-orm-with-db - Trying to use ORM to work with PostgreSQL

Using ORM with db This repo contains the training (rough) code, and possibly not

Pavel V 5 Jul 31, 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 710 Nov 25, 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. 5.3k Nov 18, 2022
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 5 Nov 18, 2022
A simple wrapper around sql.DB to help with structs. Not quite an ORM.

go-modeldb A simple wrapper around sql.DB to help with structs. Not quite an ORM. Philosophy: Don't make an ORM Example: // Setup require "modeldb" db

Jae Kwon 17 Nov 16, 2019
ORM-ish library for Go

We've moved! gorp is now officially maintained at: https://github.com/go-gorp/gorp This fork was created when the project was moved, and is provided f

James Cooper 52 Aug 23, 2022
Simple Go ORM for Google/Firebase Cloud Firestore

go-firestorm Go ORM (Object-relational mapping) for Google Cloud Firestore. Goals Easy to use Non intrusive Non exclusive Fast Features Basic CRUD ope

Jens Kjær Schødt 38 Sep 28, 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 549 Sep 9, 2022
Simple and Powerful ORM for Go, support mysql,postgres,tidb,sqlite3,mssql,oracle, Moved to https://gitea.com/xorm/xorm

xorm HAS BEEN MOVED TO https://gitea.com/xorm/xorm . THIS REPOSITORY WILL NOT BE UPDATED ANY MORE. 中文 Xorm is a simple and powerful ORM for Go. Featur

null 6.6k Nov 22, 2022
A better ORM for Go, based on non-empty interfaces and code generation.

reform A better ORM for Go and database/sql. It uses non-empty interfaces, code generation (go generate), and initialization-time reflection as oppose

null 1.3k Nov 10, 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 688 Nov 16, 2022
A better ORM for Go, based on non-empty interfaces and code generation.

A better ORM for Go and database/sql. It uses non-empty interfaces, code generation (go generate), and initialization-time reflection as opposed to interface{}, type system sidestepping, and runtime reflection. It will be kept simple.

null 1.4k Nov 27, 2022
Simple and performant ORM for sql.DB

Simple and performant ORM for sql.DB Main features are: Works with PostgreSQL, MySQL, SQLite. Selecting into a map, struct, slice of maps/structs/vars

Uptrace 1.7k Nov 22, 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 75 Nov 16, 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 31, 2022
Golang ORM with focus on PostgreSQL features and performance

go-pg is in a maintenance mode and only critical issues are addressed. New development happens in Bun repo which offers similar functionality but works with PostgreSQL, MySQL, and SQLite.

null 5.3k Nov 27, 2022
GorosePro(Go ORM),这个版本是Gorose的专业版

GorosePro(Go ORM),这个版本是Gorose的专业版

火线兔 5 Oct 26, 2022
The fantastic ORM library for Golang, aims to be developer friendly

GORM The fantastic ORM library for Golang, aims to be developer friendly. Overview Full-Featured ORM Associations (Has One, Has Many, Belongs To, Many

Yadhu Sasidharan 0 Nov 11, 2021