goloquent

Goloquent

The only sequel ORM that respect google datastore.

Why Goloquent?

Few years back, we required to migrate our database to local due to compliance issue, and there have no way to map back the data to SQL database. For sake, we introduce goloquent for whoever want to migrate their google datastore to SQL database.

Database Support

• MySQL (version 5.7 and above)
• Postgres (version 9.4 and above)

This package is not compactible with native package database/sql, if you want the support of it, you may go for sqlike

Sequel Datastore ORM

Inspired by Laravel Eloquent and Google Cloud Datastore

This repo still under development. We accept any pull request. ^_^

Installation

  // dependency
  $ go get -u github.com/go-sql-driver/mysql // Mysql
  $ go get -u github.com/lib/pq // Postgres
  $ go get -u cloud.google.com/go/datastore
  $ go get -u github.com/RevenueMonster/goloquent

• Import the library

  import "github.com/RevenueMonster/goloquent"

Quick Start

Connect to database

    import "github.com/RevenueMonster/goloquent/db"

    conn, err := db.Open("mysql", db.Config{
        Username: "root",
        Password: "",
        Host: "localhost",
        Port: "3306",
        Database: "test",
        Logger: func(stmt *goloquent.Stmt) {
            log.Println(stmt.TimeElapse()) // elapse time in time.Duration
            log.Println(stmt.String()) // Sql string without any ?
            log.Println(stmt.Raw()) // Sql prepare statement
            log.Println(stmt.Arguments()) // Sql prepare statement's arguments
            log.Println(fmt.Sprintf("[%.3fms] %s", stmt.TimeElapse().Seconds()*1000, stmt.String()))
        },
    })
    defer conn.Close()
    if err != nil {
        panic("Connection error: ", err)
    }

User Table

// Address :
type Address struct {
	Line1    string
	Line2    string
	Country  string
	PostCode uint
	Region   struct {
		TimeZone    time.Time
		Keys        []*datastore.Key   `goloquent:"keys"`
		CountryCode string             `goloquent:"regionCode"`
		Geolocation datastore.GeoPoint `goloquent:"geo"`
	} `goloquent:"region"`
}

// User : User kind parent is Merchant
type User struct {
    Key             *datastore.Key `goloquent:"__key__"` // load table key
    Name            string
    Nicknames       []string
    CountryCode     string
    PhoneNumber     string
    Email           string
    BirthDate       goloquent.Date
    Address         Address
    Age             uint8
    ExtraInfo       json.RawMessage
    CreatedDateTime time.Time
    UpdatedDateTime time.Time
}

// Load : load func will execute after load
func (x *User) Load() error {
	return nil
}

// Save : save func will execute before save
func (x *User) Save() (error) {
	return nil
}

Helper

    // StringPrimaryKey is to get key value in string form
    key := datastore.NameKey("Merchant", "mjfFgYnxBS", nil)
    fmt.Println(goloquent.StringKey(key)) // "mjfFgYnxBS"

    key = datastore.IDKey("User", int64(2305297334603281546), nil)
    fmt.Println(goloquent.StringKey(key)) // "2305297334603281546"

    key = datastore.NameKey("User", int64(2305297334603281546), datastore.NameKey("Merchant", "mjfFgYnxBS", nil))
    fmt.Println(goloquent.StringifyKey(key)) // Merchant,'mjfFgYnxBS'/User,2305297334603281546

Table

    import "github.com/RevenueMonster/goloquent/db"

    // Check table exists
    db.Table("User").Exists() // true

    // Drop table if exists
    if err := db.Table("User").DropIfExists(); err != nil {
        log.Fatal(err)
    }

    // Add index
    if err := db.Table("User").AddIndex("Name", "Email"); err != nil {
        log.Fatal(err)
    }

    // Add unique index
    if err := db.Table("User").AddUniqueIndex("Email"); err != nil {
        log.Fatal(err)
    }

Create Record

    import "github.com/RevenueMonster/goloquent/db"

    // Example
    user := new(User)
    user.Name = "Hello World"
    user.Age = 18

    // OR
    var user *User
    user.Name = "Hello World"
    user.Age = 18

    // Create without parent key
    if err := db.Create(user, nil); err != nil {
        log.Println(err) // fail to create record
    }

    // Create with parent key
    parentKey := datastore.NameKey("Parent", "value", nil)
    if err := db.Create(user, parentKey); err != nil {
        log.Println(err) // fail to create record
    }

    // Create without key, goloquent will auto generate primary key
    if err := db.Create(user); err != nil {
        log.Println(err) // fail to create record
    }

    // Create with self generate key
    user.Key = datastore.NameKey("User", "uniqueID", nil)
    if err := db.Create(user); err != nil {
        log.Println(err) // fail to create record
    }

Upsert Record

    import "github.com/RevenueMonster/goloquent/db"

    // Example
    user := new(User)
    user.Name = "Hello World"
    user.Age = 18

    // Update if key exists, else create the user record
    parentKey := datastore.NameKey("Parent", "value", nil)
    if err := db.Upsert(user, parentKey); err != nil {
        log.Println(err) // fail
    }

    // Upsert with self generate key
    user := new(User)
    user.Key = datastore.NameKey("User", "uniqueID", nil)
    user.Name = "Hello World"
    user.Age = 18
    if err := db.Upsert(user); err != nil {
        log.Println(err) // fail
    }

Retrieve Record

• Get Single Record using Primary Key

    // Example
    primaryKey := datastore.IDKey("User", int64(2305297334603281546), nil)
    user := new(User)
    if err := db.Find(primaryKey, user); err != nil {
        log.Println(err) // error while retrieving record
    }

    if err := db.Where("Status", "=", "ACTIVE").
        Find(primaryKey, user); err != goloquent.ErrNoSuchEntity {
        // if no record found using primary key, error `ErrNoSuchEntity` will throw instead
        log.Println(err) // error while retrieving record
    }

• Get Single Record

    import "github.com/RevenueMonster/goloquent/db"
    // Example 1
    user := new(User)
    if err := db.First(user); err != nil {
        log.Println(err) // error while retrieving record
    }

    if user.Key != nil { // if have record
        fmt.Println("Have record")
    } else { // no record
        fmt.Println("Doesnt't have record")
    }

    // Example 2
    user := new(User)
    if err := db.Where("Email", "=", "[email protected]").
        First(user); err != nil {
        log.Println(err) // error while retrieving record
    }

    // Example 3
    age := 22
    parentKey := datastore.IDKey("Parent", 1093, nil)
    user := new(User)
    if err := db.Ancestor(parentKey).
        WhereEqual("Age", &age).
        OrderBy("-CreatedDateTime").
        First(user); err != nil {
        log.Println(err) // error while retrieving record
    }

• Get Multiple Record

    import "github.com/RevenueMonster/goloquent/db"
    // Example 1
    users := new([]User)
    if err := db.Limit(10).Get(ctx, users); err != nil {
        log.Println(err) // error while retrieving record
    }

    // Example 2
    users := new([]*User)
    if err := db.WhereEqual("Name", "Hello World").
        Get(ctx, users); err != nil {
        log.Println(err) // error while retrieving record
    }

    // Example 3
    users := new([]User)
    if err := db.Ancestor(parentKey).
        WhereEqual("Name", "myz").
        WhereEqual("Age", 22).
        Get(ctx, users); err != nil {
        log.Println(err) // error while retrieving record
    }

• Get Record with OrderBying

    import "github.com/RevenueMonster/goloquent/db"
    // Ascending OrderBy
    users := new([]*User)
    if err := db.OrderBy("CreatedDateTime").
        Get(ctx, users); err != nil {
        log.Println(err) // error while retrieving record
    }

    // Descending OrderBy
    if err := db.Table("User").
        OrderBy("-CreatedDateTime").
        Get(ctx, users); err != nil {
        log.Println(err) // error while retrieving record
    }

• Pagination Record

    import "github.com/RevenueMonster/goloquent/db"

    p := goloquent.Pagination{
        Limit:  10,
        Cursor: "", // pass the cursor that generate by the query so that it will display the next record
    }

    // Example
    users := new([]*User)
    if err := db.Ancestor(parentKey).
        OrderBy("-CreatedDateTime").
        Paginate(&p, users); err != nil {
        log.Println(err) // error while retrieving record
    }

    // ***************** OR ********************
    p := &goloquent.Pagination{
        Limit:  10, // number of records in each page
        Cursor: "EhQKCE1lcmNoYW50EK3bueKni5eNIxIWCgxMb3lhbHR5UG9pbnQaBkZrUUc4eA", // pass the cursor to get next record set
    }

    users := new([]*User)
    if err := db.Ancestor(parentKey).
        OrderBy("-CreatedDateTime").
        Paginate(p, users); err != nil {
        log.Println(err) // error while retrieving record
    }

    log.Println(p.NextCursor()) // next page cursor
    log.Println(p.Count()) // record count

Save Record

    import "github.com/RevenueMonster/goloquent/db"
    // Example
    if err := db.Save(user); err != nil {
        log.Println(err) // fail to delete record
    }

Delete Record

• Delete using Primary Key

    import "github.com/RevenueMonster/goloquent/db"
    // Example
    if err := db.Delete(user); err != nil {
        log.Println(err) // fail to delete record
    }

• Delete using Where statement

    // Delete user table record which account type not equal to "PREMIUM" or "MONTLY"
    if err := db.Table("User").
        WhereNotIn("AccountType", []string{
            "PREMIUM", "MONTLY",
        }).Flush(); err != nil {
        log.Println(err) // fail to delete record
    }

Transaction

    // Example
    if err := db.RunInTransaction(func(txn *goloquent.DB) error {
        user := new(User)
        if err := txn.Create(user, nil); err != nil {
            return err // return any err to rollback the transaction
        }
        return nil // return nil to commit the transaction
    }); err != nil {
        log.Println(err)
    }

• Table Locking (only effective inside RunInTransaction)

    // Example
    merchantKey := datastore.IDKey("Merchant", "mjfFgYnxBS", nil)
    userKey := datastore.IDKey("User", int64(4645436182170916864), nil)
    if err := db.RunInTransaction(func(txn *goloquent.DB) error {
        user := new(User)

        if err := txn.NewQuery().
            WLock(). // Lock record for update
            Find(userKey, user); err != nil {
            return err
        }

        merchant := new(Merchant)
        if err := txn.NewQuery().
            RLock(). // Lock record for read
            Find(merchantKey, merchant); err != nil {
            return err
        }

        user.Age = 30
        if err := txn.Save(user); err != nil {
            return err // return any err to rollback the transaction
        }

        return nil // return nil to commit the transaction
    }); err != nil {
        log.Println(err)
    }

• Database Migration

    import "github.com/RevenueMonster/goloquent/db"
    // Example
    user := new(User)
    if err := db.Migrate(
        new(user),
        Merchant{},
        &Log{},
    ); err != nil {
        log.Println(err)
    }

• Filter Query

    import "github.com/RevenueMonster/goloquent/db"
    // Update single record
    user := new(User)
    if err := db.NewQuery().
        WhereIn("Status", []interface{}{"active", "pending"}).
        First(user); err != nil {
        log.Println(err) // error while retrieving record or record not found
    }

    // Get record with like
    if err := db.NewQuery().
        WhereLike("Name", "%name%").
        First(user); err != nil {
        log.Println(err) // error while retrieving record or record not found
    }

• Update Query

    import "github.com/RevenueMonster/goloquent/db"
    // Update multiple record
    if err := db.Table("User").
        Where("Age", ">", 10).
        Update(map[string]interface{}{
            "Name": "New Name",
            "Email": "[email protected]",
            "UpdatedDateTime": time.Now().UTC(),
        }); err != nil {
        log.Println(err) // error while retrieving record or record not found
    }

    if err := db.Table("User").
        Omit("Name").
        Where("Age", ">", 10).
        Update(User{
            Name: "New Name",
            Email: "[email protected]",
            UpdatedDateTime: time.Now().UTC(),
        }); err != nil {
        log.Println(err) // error while retrieving record or record not found
    }

• JSON Filter

    import "github.com/RevenueMonster/goloquent/db"

    // JSON equal
    users := new([]User)
    postCode := uint32(63000)
	if err := db.NewQuery().
		WhereJSONEqual("Address>PostCode", &postCode).
		Get(ctx, users); err != nil {
		log.Println(err)
    }

    // JSON not equal
    var timeZone *time.Time
	if err := db.NewQuery().
		WhereJSONNotEqual("Address>region.TimeZone", timeZone).
		Get(ctx, users); err != nil {
		log.Println(err)
    }

    // JSON contains any
    if err := db.NewQuery().
		WhereJSONContainAny("Nicknames", []string{
            "Joe", "John", "Robert",
        }).Get(ctx, users); err != nil {
		log.Println(err)
    }

    // JSON check type
    if err := db.NewQuery().
		WhereJSONType("Address>region", "Object").
        Get(ctx, users); err != nil {
		log.Println(err)
    }

    // JSON check is object type
    if err := db.NewQuery().
		WhereJSONIsObject("Address>region").
        Get(ctx, users); err != nil {
		log.Println(err)
    }

    // JSON check is array type
    if err := db.NewQuery().
		WhereJSONIsArray("Address>region.keys").
        Get(ctx, users); err != nil {
		log.Println(err)
    }

Context Resolution Query

    import "github.com/RevenueMonster/goloquent/db"
    
    db.NewQuery().
        WhereLike("Name", "%name%").
        First(context.Background(), user)
    // query: SELECT * FROM `user` WHERE `Name` LIKE "%name%"

    ctx := context.Background()
    ctx = db.Where("MerchantID", "=", 1234).InjectResolution(ctx)

    db.NewQuery().
        WhereLike("Name", "%name%").
        First(ctx, user)
    // query: SELECT * FROM `user` WHERE `MerchantID` = 1234 AND `Name` LIKE "%name%"

Replica Connection

You always need to have a primary connection then only adding replica connection. Primary connection must be read-write allowed, and replica can be secondary ( read-write ), and readonly connection ( read ). Currently will be round-robin strategy by default and specific resolver in Query.

Adding Readonly Replica Connection

    import "github.com/RevenueMonster/goloquent/db"

    dbContext := context.Background()
    conn, err := db.Open(dbContext, "mysql", db.Config{
        Username: "root",
        Password: "",
        Host: "localhost",
        Port: "3306",
        Database: "test",
        Logger: func(stmt *goloquent.Stmt) {
            log.Println(stmt.TimeElapse()) // elapse time in time.Duration
            log.Println(stmt.String()) // Sql string without any ?
            log.Println(stmt.Raw()) // Sql prepare statement
            log.Println(stmt.Arguments()) // Sql prepare statement's arguments
            log.Println(fmt.Sprintf("[%.3fms] %s", stmt.TimeElapse().Seconds()*1000, stmt.String()))
        },
    })
    defer conn.Close()
    if err != nil {
        panic("Connection error: ", err)
    }

    if err := conn.Replica(dbContext, "mysql", goloquent.ReplicaConfig{
        Username: "root",
        Password: "",
        Host: "localhost",
        Port: "3306",
        Database: "test",
	ReadOnly: true,
        Logger: func(stmt *goloquent.Stmt) {
            log.Println(stmt.TimeElapse()) // elapse time in time.Duration
            log.Println(stmt.String()) // Sql string without any ?
            log.Println(stmt.Raw()) // Sql prepare statement
            log.Println(stmt.Arguments()) // Sql prepare statement's arguments
            log.Println(fmt.Sprintf("[%.3fms] %s", stmt.TimeElapse().Seconds()*1000, stmt.String()))
        },
     }); err != nil {
            panic(fmt.Sprintf("Connection errors for mysql replica : %s", err))
     }

Adding Secondary Replica Connection

    import "github.com/RevenueMonster/goloquent/db"

    dbContext := context.Background()
    conn, err := db.Open(dbContext, "mysql", db.Config{
        Username: "root",
        Password: "",
        Host: "localhost",
        Port: "3306",
        Database: "test",
        Logger: func(stmt *goloquent.Stmt) {
            log.Println(stmt.TimeElapse()) // elapse time in time.Duration
            log.Println(stmt.String()) // Sql string without any ?
            log.Println(stmt.Raw()) // Sql prepare statement
            log.Println(stmt.Arguments()) // Sql prepare statement's arguments
            log.Println(fmt.Sprintf("[%.3fms] %s", stmt.TimeElapse().Seconds()*1000, stmt.String()))
        },
    })
    defer conn.Close()
    if err != nil {
        panic("Connection error: ", err)
    }

    if err := conn.Replica(dbContext, "mysql", goloquent.ReplicaConfig{
        Username: "root",
        Password: "",
        Host: "localhost",
        Port: "3306",
        Database: "test",
        Logger: func(stmt *goloquent.Stmt) {
            log.Println(stmt.TimeElapse()) // elapse time in time.Duration
            log.Println(stmt.String()) // Sql string without any ?
            log.Println(stmt.Raw()) // Sql prepare statement
            log.Println(stmt.Arguments()) // Sql prepare statement's arguments
            log.Println(fmt.Sprintf("[%.3fms] %s", stmt.TimeElapse().Seconds()*1000, stmt.String()))
        },
     }); err != nil {
            panic(fmt.Sprintf("Connection errors for mysql replica : %s", err))
     }

Specify replica

    import "github.com/RevenueMonster/goloquent/db"
    import "github.com/RevenueMonster/goloquent"
    
    user := new(User)
    if err := db.Where("Email", "=", "[email protected]").
    	ReplicaResolver(goloquent.ReplicaResolvePrimaryOnly). // resolve primary only
        First(user); err != nil {
        log.Println(err) // error while retrieving record
    }
    
    if err := db.Where("Email", "=", "[email protected]").
    	ReplicaResolver(goloquent.ReplicaResolveSecondaryOnly). // resolve secondary only replicas
        First(user); err != nil {
        log.Println(err) // error while retrieving record
    }
    
    if err := db.Where("Email", "=", "[email protected]").
    	ReplicaResolver(goloquent.ReplicaResolveReadOnly). // resolve read only replicas
        First(user); err != nil {
        log.Println(err) // error while retrieving record
    }

• Data Type Support for Where Filtering

The supported data type are :

- string
- bool
- int, int8, int16, int32 and int64 (signed integers)
- uint, uint8, uint16, uint32 and uint64
- float32 and float64
- []byte
- datastore.GeoPoint
- goloquent.Date
- json.RawMessage
- time.Time
- pointers to any one of the above
- *datastore.Key
- slices of any of the above

• Extra Schema Option

Available shorthand:

• longtext (only applicable for string data type)
• index
• unsigned (only applicable for float32 and float64 data type)
• flatten (only applicable for struct or []struct)

type model struct {
    CreatedDateTime time.Time // `CreatedDateTime`
    UpdatedDateTime time.Time // `UpdatedDateTime`
}

// Fields may have a `goloquent:"name,options"` tag.
type User struct {
    Key         *datastore.Key `goloquent:"__key__"` // Primary Key
    Name        string `goloquent:",longtext"` // Using `TEXT` datatype instead of `VARCHAR(255)` by default
    CreditLimit    float64    `goloquent:",unsigned"` // Unsigned option only applicable for float32 & float64 data type
    PhoneNumber string `goloquent:",charset=utf8,collate=utf8_bin,datatype=char(20)"`
    Email       string
    Skip        string `goloquent:"-"` // Skip this field to store in db
    DefaultAddress struct {
        AddressLine1 string // `DefaultAddress.AddressLine1`
        AddressLine2 string // `DefaultAddress.AddressLine2`
        PostCode     int    // `DefaultAddress.PostCode`
        City         string // `DefaultAddress.City`
        State        string // `DefaultAddress.State`
        Country      string
    } `goloquent:",flatten"` // Flatten the struct field
    Birthdate *goloquent.Date
    ExtraInfo json.RawMessage
    model                    // Embedded struct
    Deleted goloquent.SoftDelete
}

The supported data type are :

- string
- int, int8, int16, int32 and int64 (signed integers)
- uint, uint8, uint16, uint32 and uint64
- bool
- float32 and float64
- []byte
- any type whose underlying type is one of the above predeclared types
- datastore.GeoPoint
- goloquent.Date
- goloquent.SoftDelete
- time.Time
- json.RawMessage
- structs whose fields are all valid value types
- pointers to any one of the above
- *datastore.Key
- slices of any of the above

$Key, $Deleted are reserved words, please avoid to use these words as your column name

MIT License