A type safe SQL like ORM for Go
GOE logo by Luanexs
go get github.com/go-goe/goe
As any database/sql support in go, you have to get a specific driver for your database, check Available Drivers
go get github.com/go-goe/postgres
go get github.com/go-goe/sqlite
package main
import (
"fmt"
"github.com/go-goe/goe"
"github.com/go-goe/sqlite"
)
type Animal struct {
ID int
Name string
Emoji string
}
type Database struct {
Animal *Animal
*goe.DB
}
func main() {
db, err := goe.Open[Database](sqlite.Open("goe.db", sqlite.Config{}))
if err != nil {
panic(err)
}
defer goe.Close(db)
err = goe.AutoMigrate(db)
if err != nil {
panic(err)
}
err = goe.Delete(db.Animal).All()
if err != nil {
panic(err)
}
animals := []Animal{
{Name: "Cat", Emoji: "🐈"},
{Name: "Dog", Emoji: "🐕"},
{Name: "Rat", Emoji: "🐀"},
{Name: "Pig", Emoji: "🐖"},
{Name: "Whale", Emoji: "🐋"},
{Name: "Fish", Emoji: "🐟"},
{Name: "Bird", Emoji: "🐦"},
}
err = goe.Insert(db.Animal).All(animals)
if err != nil {
panic(err)
}
animals, err = goe.List(db.Animal).AsSlice()
if err != nil {
panic(err)
}
fmt.Println(animals)
}type Database struct {
User *User
Role *Role
UserLog *UserLog
*goe.DB
}In goe, it's necessary to define a Database struct, this struct implements *goe.DB and a pointer to all the structs that's it's to be mappend.
It's through the Database struct that you will interact with your database.
type User struct {
Id uint //this is primary key
Login string
Password string
}By default the field "Id" is primary key and all ids of integers are auto increment
type User struct {
Identifier uint `goe:"pk"`
Login string
Password string
}In case you want to specify a primary key use the tag value "pk".
type User struct {
Id string `goe:"pk;type:uuid"`
Login string `goe:"type:varchar(10)"`
Name string `goe:"type:varchar(150)"`
Password string `goe:"type:varchar(60)"`
}You can specify a type using the tag value "type"
type User struct {
Id int
Name string
Email *string // this will be a null column
}A pointer is considered a null column in Database.
Default values will be added in future features.
In goe relational fields are created using the pattern TargetTable+TargetTableId, so if you want to have a foreign key to User, you will have to write a field like "UserId" or "IdUser".
type User struct {
Id uint
Login string
Name string
Password string
}
type UserDetails struct {
Id uint
Email string
Birthdate time.Time
UserId uint // one to one with User
}For simplifications all relational slices should be the last fields on struct.
type User struct {
Id uint
Name string
Password string
UserLogs []UserLog // one User has many UserLogs
}
type UserLog struct {
Id uint
Action string
DateTime time.Time
UserId uint // if remove the slice from user, will became a one to one
}The difference from one to one and many to one it's the add of a slice field on the "many" struct
For simplifications all relational slices should be the last fields on struct.
type User struct {
Id uint
Name string
Password string
UserRoles []UserRole
}
type UserRole struct {
UserId uint `goe:"pk"`
RoleId uint `goe:"pk"`
}
type Role struct {
Id uint
Name string
UserRoles []UserRole
}Is used a combination of two many to one to generate a many to many. In this example, User has many UserRole and Role has many UserRole.
It's used the tags "pk" for ensure that the foreign keys will be both primary key.
One to Many
type Page struct {
Id int
Number int
PageId *int
Pages []Page
}One to One
type Page struct {
Id int
Number int
PageId *int
}type User struct {
Id uint
Name string
Email string `goe:"unique"`
}To create a unique index you need the "unique" goe tag
type User struct {
Id uint
Name string
Email string `goe:"index"`
}To create a common index you need the "index" goe tag
type User struct {
Id uint
Name string `goe:"index(n:idx_name_status)"`
Email string `goe:"index(n:idx_name_status);unique"`
}Using the goe tag "index()", you can pass the index infos as a function call. "n:" is a parameter for name, to have a two column index just need two indexes with same name. You can use the semicolon ";" to create another single index for the field.
type User struct {
Id uint
Name string `goe:"index(unique n:idx_name_status)"`
Email string `goe:"index(unique n:idx_name_status);unique"`
}Just as creating a Two Column Index but added the "unique" value inside the index function.
Function indexes will be added in future features.
GOE supports any logger that implements the Logger interface
type Logger interface {
InfoContext(ctx context.Context, msg string, kv ...any)
WarnContext(ctx context.Context, msg string, kv ...any)
ErrorContext(ctx context.Context, msg string, kv ...any)
}The logger is defined on database opening
db, err := goe.Open[Database](sqlite.Open("goe.db", sqlite.Config{
DatabaseConfig: goe.DatabaseConfig{
Logger: slog.New(slog.NewTextHandler(os.Stdout, &slog.HandlerOptions{Level: slog.LevelInfo})),
IncludeArguments: true,
QueryThreshold: time.Second},
}))You can use slog as your standard logger or make a adapt over the Logger interface
To open a database use goe.Open function, it's require a valid driver. Most of the drives will require a dns/path connection and a config setup. On goe.Open needs to specify the struct database.
To migrate the structs, use the goe.AutoMigrate passing the database returned by goe.Open.
If you don't need the database connection anymore, call goe.Close to ensure that all the database resources will be removed from memory.
type Database struct {
Animal *Animal
AnimalFood *AnimalFood
Food *Food
*goe.DB
}
dns := "user=postgres password=postgres host=localhost port=5432 database=postgres"
db, err := goe.Open[Database](postgres.Open(dns, postgres.Config{}))
if err != nil {
// handler error
}
defer goe.Close(db)
// migrate all database structs
err = goe.AutoMigrate(db)
if err != nil {
// handler error
}You can use the postgres.Config{} to active a log that will print all the queries. Also db.Log() it's a alternativly way of active or deactive the logs at any time.
Find is used when you want to return a single result.
// one primary key
animal, err = goe.Find(db.Animal).ById(Animal{Id: 2})
// two primary keys
animalFood, err = goe.Find(db.AnimalFood).ById(AnimalFood{IdAnimal: 3, IdFood: 2})
// find record by value, if have more than one it will returns the first
cat, err = goe.Find(db.Animal).ByValue(Animal{Name: "Cat"})Use goe.FindContext for specify a context
Use OnErrNotFound to replace ErrNotFound with a new error
List has support for OrderBy, Pagination and Joins.
// list all animals
animals, err = goe.List(db.Animal).AsSlice()
// list the animals with name "Cat", Id "3" and IdHabitat "4"
animals, err = goe.List(db.Animal).Filter(Animal{Name: "Cat", Id: 3, IdHabitat: 4}).AsSlice()
// when using % on filter, goe makes a like operation
animals, err = goe.List(db.Animal).Filter(Animal{Name: "%Cat%"}).AsSlice()Use goe.ListContext for specify a context
Return all animals as a slice
// select * from animals
animals, err = goe.Select(db.Animal).From(db.Animal).AsSlice()
if err != nil {
// handler error
}Use goe.SelectContext for specify a context
Iterate over the rows
for row, err := range goe.Select(db.Animal).From(db.Animal).Rows() {
// iterator rows
}// return a slice of this struct
animals, err = goe.Select(&struct {
User *string
Role *string
EndTime **time.Time
}{
User: &db.User.Name,
Role: &db.Role.Name,
EndTime: &db.UserRole.EndDate,
}).From(db.User).
Joins(
join.LeftJoin[int](&db.User.Id, &db.UserRole.UserId),
join.LeftJoin[int](&db.UserRole.RoleId, &db.Role.Id),
).AsSlice()
if err != nil {
// handler error
}Can use Rows() to itereate over the result and map the values to another struct
// iterate over the rows
for row, err := range goe.Select(&struct {
User *string
Role *string
EndTime **time.Time
}{
User: &db.User.Name,
Role: &db.Role.Name,
EndTime: &db.UserRole.EndDate,
}).From(db.User).
Joins(
join.LeftJoin[int](&db.User.Id, &db.UserRole.UserId),
join.LeftJoin[int](&db.UserRole.RoleId, &db.Role.Id),
).Rows() {
if err != nil {
// handler error
}
anotherStruct := struct {
User string
Role string
EndTime *time.Time
}{
User: query.Get(row.User),
Role: query.Get(row.Role),
EndTime: query.Get(row.EndTime),
}
}You can use query.Get for remove the pointer stack, so if was needed a **time.Time for query the field, you can use query.Get to get *time.Time. In cases of *string and wanted string it's returned a empty string if the pointer is nil (database returns null).
For specific field is used a new struct, each new field guards the reference for the database attribute.
For where, goe uses a sub-package where, on where package you have all the goe available where operations.
animals, err = goe.Select(db.Animal).From(db.Animal).Where(where.Equals(&db.Animal.Id, 2)).AsSlice()
if err != nil {
//handler error
}It's possible to group a list of where operations inside Where()
animals, err = goe.Select(db.Animal).From(db.Animal).Where(
where.And(
where.LessEquals(&db.Animal.Id, 2),
where.In(&db.Animal.Name, []string{"Cat", "Dog"}),
),
).AsSlice()
if err != nil {
//handler error
}You can use a if to call a where operation only if it's match
selectQuery := goe.Select(db.Animal).From(db.Animal).Where(where.LessEquals(&db.Animal.Id, 30))
if filter.In {
selectQuery.Where(
where.And(
where.LessEquals(&db.Animal.Id, 30),
where.In(&db.Animal.Name, []string{"Cat", "Dog"}),
),
)
}
animals, err = selectQuery.AsSlice()
if err != nil {
//handler error
}On join, goe uses a sub-package join, on join package you have all the goe available join operations.
For the join operations, you need to specify the type, this make the joins operations more safe. So if you change a type from a field, the compiler will throw a error.
animals, err = goe.Select(db.Animal).From(db.Animal).
Joins(
join.Join[int](&db.Animal.Id, &db.AnimalFood.IdAnimal),
join.Join[uuid.UUID](&db.Food.Id, &db.AnimalFood.IdFood),
).AsSlice()
if err != nil {
//handler error
}Same as where, you can use a if to only make a join if the condition match.
For OrderBy you need to pass a reference to a mapped database field.
It's possible to OrderBy desc and asc. List and Select has support for OrderBy queries.
animals, err = goe.List(db.Animal).OrderByDesc(&db.Animal.Id).AsSlice()
if err != nil {
//handler error
}animals, err = goe.Select(db.Animal).From(db.Animal).OrderByAsc(&db.Animal.Id).AsSlice()
if err != nil {
//handler error
}For pagination, it's possible to run on Select and List functions
// page 1 of size 10
page, err = goe.Select(db.Animal).From(db.Animal).AsPagination(1, 10)
if err != nil {
//handler error
}AsPagination default values for page and size are 1 and 10 respectively
// page 1 of size 10
page, err = goe.List(db.Animal).AsPagination(1, 10)
if err != nil {
//handler error
}AsPagination default values for page and size are 1 and 10 respectively
For aggregates goe uses a sub-package aggregate, on aggregate package you have all the goe available aggregates.
On select fields, goe uses query sub-package for declaring a aggregate field on struct.
result, err := goe.Select(&struct{ *query.Count }{aggregate.Count(&db.Animal.Id)}).From(db.Animal).AsSlice()
if err != nil {
// handler error
}
// count value as int64
result[0].ValueFor functions goe uses a sub-package function, on function package you have all the goe available functions.
On select fields, goe uses query sub-package for declaring a function result field on struct.
for row, err := range goe.Select(&struct {
UpperName *query.Function[string]
}{
UpperName: function.ToUpper(&db.Animal.Name),
}).From(db.Animal).Rows() {
if err != nil {
//handler error
}
//function result value
row.UpperName.Value
}Functions can be used inside where.
animals, err = goe.Select(db.Animal).From(db.Animal).
Where(
where.Like(function.ToUpper(&db.Animal.Name), "%CAT%")
).AsSlice()
if err != nil {
//handler error
}where like expected a second argument always as string
animals, err = goe.Select(db.Animal).From(db.Animal).
Where(
where.Equals(function.ToUpper(&db.Animal.Name), function.Argument("CAT")),
).AsSlice()
if err != nil {
//handler error
}to by pass the compiler type warning, use function.Argument. This way the compiler will check the argument value
On Insert if the primary key value is auto-increment, the new Id will be stored on the object after the insert.
Use create when you want to insert a record on database and return it.
myPage, err := goe.Create(db.Page).ByValue(Page{Number: 1})
if err != nil {
//handler error
}Use goe.CreateContext for specify a context
a := Animal{Name: "Cat", Emoji: "🐘"}
err = goe.Insert(db.Animal).One(&a)
if err != nil {
//handler error
}
// new generated id
a.IdUse goe.InsertContext for specify a context
foods := []Food{
{Name: "Meat", Emoji: "🥩"},
{Name: "Hotdog", Emoji: "🌭"},
{Name: "Cookie", Emoji: "🍪"},
}
err = goe.Insert(db.Food).All(foods)
if err != nil {
//handler error
}Use goe.InsertContext for specify a context
Save is the basic function for updates a single record; only updates the non-zero values.
a := Animal{Id: 2}
a.Name = "Update Cat"
// update animal of id 2
err = goe.Save(db.Animal).Value(a)
if err != nil {
//handler error
}
// save will try to update the record, if the record don't exist it will be created
createdAnimal, err = goe.Save(db.Animal).OrCreateByValue(Animal{Name: "Create Cat"})
if err != nil {
//handler error
}
// save will update the record and return it from database
updateAnimal, err := goe.Save(db.Animal).AndFindByValue(Animal{Id: 2, Name: "Little Cat"})Use goe.SaveContext for specify a context
Use OnErrNotFound to replace ErrNotFound with a new error
Update with set uses update sub-package. This is used for more complex updates, like updating a field with zero/nil values or make a batch update.
a := Animal{Id: 2}
// a.IdHabitat is nil, so is ignored by Save
err = goe.Update(db.Animal).
Sets(update.Set(&db.Animal.IdHabitat, a.IdHabitat)).
Where(where.Equals(&db.Animal.Id, a.Id))
if err != nil {
//handler error
}Check out the Where section for more information about where operations.
The where call ensures that only the matched rows will be updated.
Use goe.UpdateContext for specify a context
Remove is used for remove only one record by primary key
// remove animal of id 2
err = goe.Remove(db.Animal).ById(Animal{Id: 2})
if err != nil {
//handler error
}Use goe.RemoveContext for specify a context
Use OnErrNotFound to replace ErrNotFound with a new error
Delete all records from Animal
err = goe.Delete(db.Animal).All()
if err != nil {
//handler error
}Delete all matched records
err = goe.Delete(db.Animal).Where(where.Like(&db.Animal.Name, "%Cat%"))
if err != nil {
//handler error
}Check out the Where section for more information about where operations.
Use goe.DeleteContext for specify a context
Setup the transaction with the database function db.NewTransaction()
tx, err = db.NewTransaction()
if err != nil {
// handler error
}
defer tx.Rollback()You can use the OnTransaction() function to setup a transaction for Select, Insert, Update and Delete.
Ensure to call
defer tx.Rollback(); this will make the Rollback happens if something go wrong
Use goe.NewTransactionContext for specify a context
To Commit a Transaction just call tx.Commit()
err = tx.Commit()
if err != nil {
// handler the error
}To Rollback a Transaction just call tx.Rollback()
err = tx.Rollback()
if err != nil {
// handler the error
}The isolation is used for control the flow and security of multiple transactions. On goe you can use the sql.IsolationLevel.
By default if you call db.NewTransaction() it's use the Serializable isolation.