Genmai

Simple, better and easy-to-use ORM library for Golang.

Note.

This product is naoina/genmai original.
I'm adding some parts.

Overview

flexibility with SQL-like API
Transaction support
Database dialect interface
Query logging
Update/Insert/Delete hooks
Embedded struct
Execute query manually

Database dialect currently supported are:

MySQL
PostgreSQL
SQLite3

Installation

go get -u github.com/WhiteRaven777/genmai

Schema

Schema of the table will be defined as a struct.

// The struct "User" is the table name "user".
// The field name will be converted to lowercase/snakecase, and used as a column name in table.
// e.g. If field name is "CreatedAt", column name is "created_at".
type User struct {
    // PRIMARY KEY. and column name will use "tbl_id" instead of "id".
    Id int64 `db:"pk" column:"tbl_id"`

    // NOT NULL and Default is "me".
    Name string `default:"me"`

    // Nullable column must use a pointer type, or sql.Null* types.
    CreatedAt *time.Time

    // UNIQUE column if specify the db:"unique" tag.
    ScreenName string `db:"unique"`

    // Ignore column if specify the db:"-" tag.
    Active bool `db:"-"`
}

Query API

Create table

package main

import (
    "time"

    _ "github.com/mattn/go-sqlite3"
    // _ "github.com/go-sql-driver/mysql"
    // _ "github.com/lib/pq"
    "github.com/WhiteRaven777/genmai"
)

// define a table schema.
type TestTable struct {
    Id        int64      `db:"pk" column:"tbl_id"`
    Name      string     `default:"me"`
    CreatedAt *time.Time
    UserName  string     `db:"unique" size:"255"`
    Active    bool       `db:"-"`
}

func main() {
    db, err := genmai.New(&genmai.SQLite3Dialect{}, ":memory:")
    // db, err := genmai.New(&genmai.MySQLDialect{}, "dsn")
    // db, err := genmai.New(&genmai.PostgresDialect{}, "dsn")
    if err != nil {
        panic(err)
    }
    defer db.Close()
    if err := db.CreateTable(&TestTable{}); err != nil {
        panic(err)
    }
}

Insert

A single insert:

obj := &TestTable{
    Name: "alice",
    Active: true,
}
n, err := db.Insert(obj)
if err != nil {
    panic(err)
}
fmt.Printf("inserted rows: %d\n", n)

Or bulk-insert:

objs := []TestTable{
    {Name: "alice", Active: true},
    {Name: "bob", Active: true},
}
n, err := db.Insert(objs)
if err != nil {
    panic(err)
}
fmt.Printf("inserted rows: %d\n", n)

Select

var results []TestTable
if err := db.Select(&results); err != nil {
    panic(err)
}
fmt.Printf("%v\n", results)

Where

var results []TestTable
if err := db.Select(&results, db.Where("tbl_id", "=", 1)); err != nil {
    panic(err)
}
fmt.Printf("%v\n", results)

And/Or

var results []TestTable
if err := db.Select(&results, db.Where("tbl_id", "=", 1).And(db.Where("name", "=", "alice").Or("name", "=", "bob"))); err != nil {
    panic(err)
}
fmt.Printf("%v\n", results)

In

var results []TestTable
if err := db.Select(&results, db.Where("tbl_id").In(1, 2, 4)); err != nil {
    panic(err)
}
fmt.Printf("%v\n", results)

Like

var results []TestTable
if err := db.Select(&results, db.Where("name").Like("%li%")); err != nil {
    panic(err)
}
fmt.Printf("%v\n", results)

Between

var results []TestTable
if err := db.Select(&results, db.Where("name").Between(1, 3)); err != nil {
    panic(err)
}
fmt.Printf("%v\n", results)

Is Null/Is Not Null

var results []TestTable
if err := db.Select(&results, db.Where("created_at").IsNull()); err != nil {
    panic(err)
}
fmt.Printf("%v\n", results)
results = []TestTable{}
if err := db.Select(&results, db.Where("created_at").IsNotNull()); err != nil {
    panic(err)
}
fmt.Printf("%v\n", results)

Order by/Offset/Limit

var results []TestTable
if err := db.Select(&results, db.OrderBy("id", genmai.ASC).Offset(2).Limit(10)); err != nil {
    panic(err)
}
fmt.Printf("%v\n", results)

Group by

var results []TestTable
if err := db.Select(&results, db.GroupBy("id")); err != nil {
    panic(err)
}
fmt.Printf("%v\n", results)

Distinct

var results []TestTable
if err := db.Select(&results, db.Distinct("name"), db.Where("name").Like("%")); err != nil {
    panic(err)
}
fmt.Printf("%v\n", results)

Count

var n int64
if err := db.Select(&n, db.Count(), db.From(&TestTable{})); err != nil {
    panic(err)
}
fmt.Printf("%v\n", n)

With condition:

var n int64
if err := db.Select(&n, db.Count(), db.From(&TestTable{}), db.Where("id", ">", 100)); err != nil {
    panic(err)
}
fmt.Printf("%v\n", n)

Join

Inner Join:

package main

import (
    "database/sql"
    "fmt"
    "time"

    _ "github.com/mattn/go-sqlite3"
    // _ "github.com/go-sql-driver/mysql"
    // _ "github.com/lib/pq"
    "github.com/WhiteRaven777/genmai"
)

type TestTable struct {
    Id        int64  `db:"pk" column:"tbl_id"`
    Name      string `default:"me"`
    CreatedAt *time.Time
    Active    bool `db:"-"` // column to ignore.
}

type Table2 struct {
    Id   int64 `db:"pk" column:"tbl_id"`
    Body sql.NullString
}

func main() {
    db, err := genmai.New(&genmai.SQLite3Dialect{}, ":memory:")
    // db, err := genmai.New(&genmai.MySQLDialect{}, "dsn")
    // db, err := genmai.New(&genmai.PostgresDialect{}, "dsn")
    if err != nil {
        panic(err)
    }
    defer db.Close()
    if err := db.CreateTable(&TestTable{}); err != nil {
        panic(err)
    }
    if err := db.CreateTable(&Table2{}); err != nil {
        panic(err)
    }
    objs1 := []TestTable{
        {Name: "alice", Active: true},
        {Name: "bob", Active: true},
    }
    objs2 := []Table2{
        {Body: sql.NullString{String: "something"}},
    }
    if _, err = db.Insert(objs1); err != nil {
        panic(err)
    }
    if _, err := db.Insert(objs2); err != nil {
        panic(err)
    }
    // fmt.Printf("inserted rows: %d\n", n)
    var results []TestTable
    if err := db.Select(&results, db.Join(&Table2{}).On("tbl_id")); err != nil {
        panic(err)
    }
    fmt.Printf("%v\n", results)
}

Left Join:

var results []TestTable
if err := db.Select(&results, db.LeftJoin(&Table2{}).On("name", "=", "body")); err != nil {
    panic(err)
}
fmt.Printf("%v\n", results)

RIGHT OUTER JOIN and FULL OUTER JOIN are still unsupported.

Update

var results []TestTable
if err := db.Select(&results); err != nil {
    panic(err)
}
obj := results[0]
obj.Name = "nico"
if _, err := db.Update(&obj); err != nil {
    panic(err)
}

Delete

A single delete:

obj := TestTable{Id: 1}
if _, err := db.Delete(&obj); err != nil {
    panic(err)
}

Or bulk-delete:

objs := []TestTable{
    {Id: 1}, {Id: 3},
}
if _, err := db.Delete(objs); err != nil {
    panic(err)
}

Transaction

defer func() {
    if err := recover(); err != nil {
        db.Rollback()
    } else {
        db.Commit()
    }
}()
if err := db.Begin(); err != nil {
    panic(err)
}
// do something.

Using any table name

You can implement TableNamer interface to use any table name.

type UserTable struct {
    Id int64 `db:"pk"`
}

func (u *UserTable) TableName() string {
    return "user"
}

In the above example, the table name user is used instead of user_table.

Using raw database/sql interface

rawDB := db.DB()
// do something with using raw database/sql interface...

Query logging

By default, query logging is disabled. You can enable Query logging as follows.

db.SetLogOutput(os.Stdout) // Or any io.Writer can be passed.

Also you can change the format of output as follows.

db.SetLogFormat("format string")

Format syntax uses Go's template. And you can use the following data object in that template.

- .time        time.Time object in current time.
- .duration    Processing time of SQL. It will format to "%.2fms".
- .query       string of SQL query. If it using placeholder,
               placeholder parameters will append to the end of query.

The default format is:

[{{.time.Format "2006-01-02 15:04:05"}}] [{{.duration}}] {{.query}}

In production, it is recommended to disable this feature in order to somewhat affect performance.

db.SetLogOutput(nil) // To disable logging by nil.

Update/Insert/Delete hooks

Genmai calls Before/After hook method if defined in model struct.

func (t *TestTable) BeforeInsert() error {
    t.CreatedAt = time.Now()
    return nil
}

If Before prefixed hook returns an error, it query won't run.

All hooks are:

BeforeInsert/AfterInsert
BeforeUpdate/AfterUpdate
BeforeDelete/AfterDelete

If use bulk-insert or bulk-delete, hooks method run for each object.

Embedded struct

Common fields can be defined on struct and embed that.

package main

import "time"

type TimeStamp struct {
    CreatedAt time.Time
    UpdatedAt time.Time
}

type User struct {
    Id   int64
    Name string

    TimeStamp
}

Also Genmai has defined TimeStamp struct for commonly used fields.

type User struct {
    Id int64

    genmai.TimeStamp
}

See the Godoc of TimeStamp for more information.

If you'll override hook method defined in embedded struct, you'll should call the that hook in overridden method. For example in above struct case:

func (u *User) BeforeInsert() error {
	if err := u.TimeStamp.BeforeInsert(); err != nil {
		return err
	}
	// do something.
	return nil
}

Execute query manually

db, err := genmai.New(&genmai.SQLite3Dialect{}, ":memory:")
if err != nil {
    panic(err)
}
defer db.Close()

defer func() {
	if err := recover(); err != nil {
		cc.DB.Rollback()
	} else {
		cc.DB.Commit()
		ret = true
	}
}()

if err := cc.DB.Begin(); err != nil {
	panic(err)
}

// define a table schema.
type TestTable struct {
    Id        int64      `db:"pk" column:"tbl_id"`
    Name      string     `default:"me"`
    CreatedAt *time.Time
    UserName  string     `db:"unique" size:"255"`
    Active    bool       `db:"-"`
}

var query string

query = `
    INSERT INTO test_table (
        id,
        name,
        created_at,
        user_name,
        active
    ) VALUE (
        ?,
        ?,
        ?,
        ?,
        ?
    )`

if _, err := db.Exec(
    query,
    1,
    "alice",
    *time.Now(),
    "alice",
    true,
    ); err != nil {
	panic(err)
}

// ---

query = `
    SELECT
        id,
        name,
        created_at,
        user_name,
        active
    FROM
        test_table
    WHERE
        name = ?`

var tast_table_list []TestTable
if _, err := db.Query(query, "alice"); err == nil {
    var tast_table TestTable
	for rows.Next() {
		if err = rows.Scan(
			&tast_table.Id,
			&tast_table.Name,
			&tast_table.CreatedAt,
			&tast_table.UserName,
			&tast_table.Active,
		); err != nil {
			continue
		}
		tast_table_list = append(tast_table_list, tast_table)
	}
}

// ---

query = `
    SELECT
        count(id)
    FROM
        test_table
    WHERE
        name = ?`

var cnt int
if err := db.QueryRow(query, "alice").Scan(cnt); err != nil {
    panic(err)
}

Documentation

API document and more examples are available here:

http://godoc.org/github.com/WhiteRaven777/genmai

TODO

Benchmark
More SQL support
Migration

License

Genmai is licensed under the MIT

Name		Name	Last commit message	Last commit date
Latest commit History 97 Commits
.gitignore		.gitignore
.travis.yml		.travis.yml
LICENSE		LICENSE
README.md		README.md
dialect.go		dialect.go
dialect_test.go		dialect_test.go
example_select_test.go		example_select_test.go
example_test.go		example_test.go
field.go		field.go
field_test.go		field_test.go
genmai.go		genmai.go
genmai_test.go		genmai_test.go
log.go		log.go
type.go		type.go
type_test.go		type_test.go
util.go		util.go
util_test.go		util_test.go

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Repository files navigation

Genmai

Note.

Overview

Installation

Schema

Query API

Create table

Insert

Select

Where

And/Or

In

Like

Between

Is Null/Is Not Null

Order by/Offset/Limit

Group by

Distinct

Count

Join

Update

Delete

Transaction

Using any table name

Using raw database/sql interface

Query logging

Update/Insert/Delete hooks

Embedded struct

Execute query manually

Documentation

TODO

License

About

Releases

Packages

Languages

License

WhiteRaven777/genmai

Folders and files

Latest commit

History

Repository files navigation

Genmai

Note.

Overview

Installation

Schema

Query API

Create table

Insert

Select

Where

And/Or

In

Like

Between

Is Null/Is Not Null

Order by/Offset/Limit

Group by

Distinct

Count

Join

Update

Delete

Transaction

Using any table name

Using raw database/sql interface

Query logging

Update/Insert/Delete hooks

Embedded struct

Execute query manually

Documentation

TODO

License

About

Topics

Resources

License

Stars

Watchers

Forks

Releases

Packages 0

Languages

Packages