Skip to content

derkan/sqlorm

 
 

Repository files navigation

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

Changes

  • PR #66 Add support for sql null types
type Test struct {
    Id       hood.Id
    Number   sql.NullInt64
}

// Connect and Start Transaction

a = Test{}
tx.Save(&a) //Number column is set to NULL in database (defaults to NULL)

b = Test{}
b.Number = sql.NullInt64{128, true} //Number column is set to 128 in database
tx.Save(&b)

c = Test{}
c.Number = sql.NullInt64{128, false} //Number column is set to NULL in database since valid bool is set to false
tx.Save(&c)
  • PR #67 Add ability to define foreign keys in models
type User struct {
    Id   hood.Id
    Name string
}

type Post struct {
    Id      hood.Id
    Content string
    UserId  int64
}

func (table *Post) ForeignKeys(foreignKeys *hood.ForeignKeys) {
    foreignKeys.Add("test_fk", "user_id", "user", "id", hood.Cascade, hood.Cascade)
}
  • PR # 70 Adds FindOne
type findOneModel struct {
	Id Id
	A  string
	B  int
}
// Test with struct
var out findOneModel
err = hd.Where("a", "=", "string!").And("b", "=", 2).FindOne(&out)
if err != nil {
	t.Fatal("error not nil", err)
}
if out.Id != 0 || out.A != "" || out.B != 0 {
	t.Fatal("struct should be zeroed", out)
}

// Test with pointer to struct
var out2 *findOneModel
err = hd.Where("a", "=", "string!").And("b", "=", 2).FindOne(&out)
if err != nil {
	t.Fatal("error not nil", err)
}
  • PR #73 Fixing README so that the "Basic Example" correctly creates a table

  • Added support for CockroachDB CockroachDB uses postresql driver, connect like:

    db, err := hood.OpenWithDialect("postgres", "cockroachdb", "user=USER dbname=DB port=26257 sslmode=disable")
	//db, err := hood.Open(("postgres", "user=USER dbname=DB sslmode=disable")
	if err != nil {
		panic(err)
	}

or:

session, err := sql.Open("postgres", "user=USER dbname=DB port=26257 sslmode=disable")
if err != nil {
    panic(err)
}
db := hood.New(session, NewCockroachDB())
  • Added support for UTC only timestamps and related types: TimeUTC, CreatedUTC, UpdatedUTC CreatedUTC, UpdatedUTC is filled with time.UTC()
type MyTable struct {
	Id            hood.Id    `sql:"notnull"`
	Name          string     `sql:"size(128),notnull"`
	ExpireOn      hood.TimeUTC
	DefinedOn     hood.CreatedUTC
	UpdatedOn     hood.UpdatedUTC
}

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)
	}

	// Define a table's structure and data
	type Fruit struct {
		Id    hood.Id
		Name  string `validate:"presence"`
		Color string
	}

	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()
	
	// Create the Fruit Table
	err = tx.CreateTable(&Fruit{})
	if err != nil {
		panic(err)
	}
	
	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{})
}

Contributors

Releases

No releases published

Packages

No packages published

Languages

  • Go 99.9%
  • Shell 0.1%