xorm

Xorm is a simple and powerful ORM (Object Relational Mapping) library for Go. It provides a rich set of features for database operations, including query building, schema synchronization, and support for multiple database engines.

Pros

Supports multiple database engines (MySQL, PostgreSQL, SQLite, etc.)
Provides both raw SQL and ORM-style operations
Offers caching mechanisms for improved performance
Includes tools for database migration and schema synchronization

Cons

Learning curve can be steep for beginners
Performance may be slower compared to raw SQL in some complex scenarios
Documentation could be more comprehensive and up-to-date
Some advanced features may require additional configuration

Code Examples

Connecting to a database:

engine, err := xorm.NewEngine("mysql", "root:password@/dbname?charset=utf8")
if err != nil {
    log.Fatal(err)
}

Inserting a record:

type User struct {
    Id   int64
    Name string
    Age  int
}

user := User{Name: "John Doe", Age: 30}
affected, err := engine.Insert(&user)

Querying records:

var users []User
err := engine.Where("age > ?", 18).Limit(10, 0).Find(&users)

Updating a record:

user := User{Name: "Jane Doe"}
affected, err := engine.ID(1).Update(&user)

Getting Started

Install Xorm:
```
go get xorm.io/xorm
```
Import Xorm in your Go file:
```
import "xorm.io/xorm"
```

Create an engine and connect to your database:

engine, err := xorm.NewEngine("mysql", "user:password@/dbname?charset=utf8")
if err != nil {
    log.Fatal(err)
}

Define your struct and start using Xorm:

type User struct {
    Id   int64
    Name string
    Age  int
}

err = engine.Sync2(new(User))
if err != nil {
    log.Fatal(err)
}

// Now you can perform database operations using the engine

Pros of GORM

More active development and larger community support
Cleaner API and more intuitive method chaining
Better support for advanced features like associations and callbacks

Cons of GORM

Steeper learning curve for beginners
Can be slower for simple queries compared to XORM
More complex configuration for certain database-specific features

Code Comparison

GORM:

db.Where("name = ?", "jinzhu").First(&user)
db.Create(&User{Name: "jinzhu", Age: 18})
db.Model(&user).Update("name", "hello")

XORM:

has, err := engine.Where("name = ?", "jinzhu").Get(&user)
_, err := engine.Insert(&User{Name: "jinzhu", Age: 18})
_, err := engine.ID(user.Id).Update(&User{Name: "hello"})

Both GORM and XORM are popular ORM libraries for Go, offering similar core functionalities. GORM provides a more expressive API with method chaining, making it easier to construct complex queries. It also has better support for advanced features like associations and callbacks.

XORM, on the other hand, has a simpler API that may be easier for beginners to grasp. It can be faster for simple queries and offers some unique features like reverse engineering of existing databases.

The choice between the two often comes down to personal preference, project requirements, and the level of complexity needed in database operations.

Pros of sqlx

Lightweight and close to raw SQL, offering more control and flexibility
Supports both database-specific and generic interfaces
Excellent performance due to minimal abstraction

Cons of sqlx

Requires more manual work for complex queries and relationships
Less feature-rich compared to full-fledged ORMs
Steeper learning curve for developers new to SQL

Code Comparison

sqlx:

var users []User
err := db.Select(&users, "SELECT * FROM users WHERE status = ?", "active")

xorm:

var users []User
err := engine.Where("status = ?", "active").Find(&users)

Key Differences

sqlx stays closer to raw SQL, while xorm provides a more abstracted, ORM-like interface
xorm offers more built-in features like migrations and schema generation
sqlx requires explicit SQL queries, whereas xorm can generate queries based on struct tags and method calls

Use Cases

sqlx: Projects requiring fine-grained control over SQL queries and performance optimization
xorm: Rapid development of database-driven applications with complex relationships and migrations

Community and Ecosystem

sqlx: Smaller community but focused on SQL proficiency
xorm: Larger ecosystem with more extensions and plugins available

Pros of sqlboiler

Generates type-safe, performant Go code from database schemas
Supports complex queries and relationships out of the box
Provides excellent test coverage and database-specific optimizations

Cons of sqlboiler

Steeper learning curve due to its code generation approach
Less flexibility for ad-hoc queries compared to ORM-style libraries
Requires regeneration of code when database schema changes

Code Comparison

sqlboiler:

users, err := models.Users().All(ctx, db)
if err != nil {
    return err
}

xorm:

var users []User
err := engine.Find(&users)
if err != nil {
    return err

Pros of upper/db

More flexible and database-agnostic approach
Supports a wider range of databases, including NoSQL options
Simpler API with a more intuitive query builder

Cons of upper/db

Less feature-rich compared to xorm
Smaller community and fewer resources available
May require more manual work for complex queries

Code Comparison

upper/db:

res := db.Collection("users").Find(db.Cond{"age >": 18})
var users []User
err := res.All(&users)

xorm:

var users []User
err := engine.Where("age > ?", 18).Find(&users)

Key Differences

upper/db uses a more fluent interface for building queries
xorm provides more ORM-like features out of the box
upper/db's approach is more flexible but may require more code for complex operations
xorm offers better performance for simple CRUD operations

Use Cases

Choose upper/db for projects requiring database flexibility or NoSQL support
Opt for xorm when working primarily with SQL databases and need extensive ORM features

Community and Ecosystem

xorm has a larger user base and more third-party extensions
upper/db has a smaller but growing community with active development

Pros of Pop

Integrated with Buffalo framework, providing a seamless development experience
Supports multiple databases (PostgreSQL, MySQL, SQLite) with easy switching
Includes migration tools and generators for rapid development

Cons of Pop

Less flexible for complex queries compared to XORM
Smaller community and fewer third-party extensions
Steeper learning curve for developers not using the Buffalo framework

Code Comparison

Pop:

db, err := pop.Connect("development")
if err != nil {
    log.Fatal(err)
}
users := []User{}
err = db.All(&users)

XORM:

engine, err := xorm.NewEngine("postgres", "postgres://user:password@localhost:5432/database?sslmode=disable")
if err != nil {
    log.Fatal(err)
}
users := []User{}
err = engine.Find(&users)

Key Differences

Pop is more opinionated and integrated with Buffalo, while XORM is a standalone ORM
XORM provides more low-level control and flexibility for complex queries
Pop offers built-in migration tools, whereas XORM relies on third-party solutions
XORM has a larger community and more extensive documentation
Pop's API is designed to be more intuitive for common CRUD operations

Both ORMs have their strengths, with Pop excelling in rapid development within the Buffalo ecosystem and XORM offering more flexibility and control for standalone Go applications.

Pros of sqlc

Generates type-safe Go code from SQL queries, reducing runtime errors
Supports complex queries and joins, offering more flexibility for advanced SQL operations
Provides better performance by generating optimized code tailored to specific queries

Cons of sqlc

Steeper learning curve, especially for developers more familiar with ORM-style abstractions
Requires writing raw SQL queries, which can be more time-consuming for simple CRUD operations
Less abstraction from the database, potentially making it harder to switch between different database systems

Code Comparison

sqlc example:

-- name: GetAuthor :one
SELECT * FROM authors
WHERE id = $1 LIMIT 1;

xorm example:

author := &Author{Id: 1}
has, err := engine.Get(author)

Summary

sqlc focuses on generating type-safe Go code from SQL queries, offering better performance and flexibility for complex operations. xorm, on the other hand, provides a higher-level ORM abstraction, making it easier to work with databases using Go structs. sqlc requires more manual SQL writing but offers greater control, while xorm simplifies database interactions at the cost of some performance and flexibility.

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.

Features

Struct <-> Table Mapping Support
Chainable APIs
Transaction Support
Both ORM and raw SQL operation Support
Sync database schema Support
Query Cache speed up
Database Reverse support, See Xorm Tool README
Simple cascade loading support
Optimistic Locking support
SQL Builder support via xorm.io/builder
Automatical Read/Write seperatelly
Postgres schema support
Context Cache support

Drivers Support

Drivers for Go's sql package which currently support database/sql includes:

Mysql: github.com/go-sql-driver/mysql
MyMysql: github.com/ziutek/mymysql/godrv
Postgres: github.com/lib/pq
Tidb: github.com/pingcap/tidb
SQLite: github.com/mattn/go-sqlite3
MsSql: github.com/denisenkom/go-mssqldb
Oracle: github.com/mattn/go-oci8 (experiment)

Installation

go get github.com/go-xorm/xorm

Documents

Manual
GoDoc

Quick Start

Create Engine

engine, err := xorm.NewEngine(driverName, dataSourceName)

Define a struct and Sync2 table struct to database

type User struct {
    Id int64
    Name string
    Salt string
    Age int
    Passwd string `xorm:"varchar(200)"`
    Created time.Time `xorm:"created"`
    Updated time.Time `xorm:"updated"`
}

err := engine.Sync2(new(User))

Create Engine Group

dataSourceNameSlice := []string{masterDataSourceName, slave1DataSourceName, slave2DataSourceName}
engineGroup, err := xorm.NewEngineGroup(driverName, dataSourceNameSlice)

masterEngine, err := xorm.NewEngine(driverName, masterDataSourceName)
slave1Engine, err := xorm.NewEngine(driverName, slave1DataSourceName)
slave2Engine, err := xorm.NewEngine(driverName, slave2DataSourceName)
engineGroup, err := xorm.NewEngineGroup(masterEngine, []*Engine{slave1Engine, slave2Engine})

Then all place where engine you can just use engineGroup.

Query runs a SQL string, the returned results is []map[string][]byte, QueryString returns []map[string]string, QueryInterface returns []map[string]interface{}.

results, err := engine.Query("select * from user")
results, err := engine.Where("a = 1").Query()

results, err := engine.QueryString("select * from user")
results, err := engine.Where("a = 1").QueryString()

results, err := engine.QueryInterface("select * from user")
results, err := engine.Where("a = 1").QueryInterface()

Exec runs a SQL string, it returns affected and error

affected, err := engine.Exec("update user set age = ? where name = ?", age, name)

Insert one or multiple records to database

affected, err := engine.Insert(&user)
// INSERT INTO struct () values ()

affected, err := engine.Insert(&user1, &user2)
// INSERT INTO struct1 () values ()
// INSERT INTO struct2 () values ()

affected, err := engine.Insert(&users)
// INSERT INTO struct () values (),(),()

affected, err := engine.Insert(&user1, &users)
// INSERT INTO struct1 () values ()
// INSERT INTO struct2 () values (),(),()

Get query one record from database

has, err := engine.Get(&user)
// SELECT * FROM user LIMIT 1

has, err := engine.Where("name = ?", name).Desc("id").Get(&user)
// SELECT * FROM user WHERE name = ? ORDER BY id DESC LIMIT 1

var name string
has, err := engine.Table(&user).Where("id = ?", id).Cols("name").Get(&name)
// SELECT name FROM user WHERE id = ?

var id int64
has, err := engine.Table(&user).Where("name = ?", name).Cols("id").Get(&id)
has, err := engine.SQL("select id from user").Get(&id)
// SELECT id FROM user WHERE name = ?

var valuesMap = make(map[string]string)
has, err := engine.Table(&user).Where("id = ?", id).Get(&valuesMap)
// SELECT * FROM user WHERE id = ?

var valuesSlice = make([]interface{}, len(cols))
has, err := engine.Table(&user).Where("id = ?", id).Cols(cols...).Get(&valuesSlice)
// SELECT col1, col2, col3 FROM user WHERE id = ?

Exist check if one record exist on table

has, err := testEngine.Exist(new(RecordExist))
// SELECT * FROM record_exist LIMIT 1

has, err = testEngine.Exist(&RecordExist{
		Name: "test1",
	})
// SELECT * FROM record_exist WHERE name = ? LIMIT 1

has, err = testEngine.Where("name = ?", "test1").Exist(&RecordExist{})
// SELECT * FROM record_exist WHERE name = ? LIMIT 1

has, err = testEngine.SQL("select * from record_exist where name = ?", "test1").Exist()
// select * from record_exist where name = ?

has, err = testEngine.Table("record_exist").Exist()
// SELECT * FROM record_exist LIMIT 1

has, err = testEngine.Table("record_exist").Where("name = ?", "test1").Exist()
// SELECT * FROM record_exist WHERE name = ? LIMIT 1

Find query multiple records from database, also you can use join and extends

var users []User
err := engine.Where("name = ?", name).And("age > 10").Limit(10, 0).Find(&users)
// SELECT * FROM user WHERE name = ? AND age > 10 limit 10 offset 0

type Detail struct {
    Id int64
    UserId int64 `xorm:"index"`
}

type UserDetail struct {
    User `xorm:"extends"`
    Detail `xorm:"extends"`
}

var users []UserDetail
err := engine.Table("user").Select("user.*, detail.*").
    Join("INNER", "detail", "detail.user_id = user.id").
    Where("user.name = ?", name).Limit(10, 0).
    Find(&users)
// SELECT user.*, detail.* FROM user INNER JOIN detail WHERE user.name = ? limit 10 offset 0

Iterate and Rows query multiple records and record by record handle, there are two methods Iterate and Rows

err := engine.Iterate(&User{Name:name}, func(idx int, bean interface{}) error {
    user := bean.(*User)
    return nil
})
// SELECT * FROM user

err := engine.BufferSize(100).Iterate(&User{Name:name}, func(idx int, bean interface{}) error {
    user := bean.(*User)
    return nil
})
// SELECT * FROM user Limit 0, 100
// SELECT * FROM user Limit 101, 100

rows, err := engine.Rows(&User{Name:name})
// SELECT * FROM user
defer rows.Close()
bean := new(Struct)
for rows.Next() {
    err = rows.Scan(bean)
}

Update update one or more records, default will update non-empty and non-zero fields except when you use Cols, AllCols and so on.

affected, err := engine.ID(1).Update(&user)
// UPDATE user SET ... Where id = ?

affected, err := engine.Update(&user, &User{Name:name})
// UPDATE user SET ... Where name = ?

var ids = []int64{1, 2, 3}
affected, err := engine.In("id", ids).Update(&user)
// UPDATE user SET ... Where id IN (?, ?, ?)

// force update indicated columns by Cols
affected, err := engine.ID(1).Cols("age").Update(&User{Name:name, Age: 12})
// UPDATE user SET age = ?, updated=? Where id = ?

// force NOT update indicated columns by Omit
affected, err := engine.ID(1).Omit("name").Update(&User{Name:name, Age: 12})
// UPDATE user SET age = ?, updated=? Where id = ?

affected, err := engine.ID(1).AllCols().Update(&user)
// UPDATE user SET name=?,age=?,salt=?,passwd=?,updated=? Where id = ?

Delete delete one or more records, Delete MUST have condition

affected, err := engine.Where(...).Delete(&user)
// DELETE FROM user Where ...

affected, err := engine.ID(2).Delete(&user)
// DELETE FROM user Where id = ?

Count count records

counts, err := engine.Count(&user)
// SELECT count(*) AS total FROM user

FindAndCount combines function Find with Count which is usually used in query by page

var users []User
counts, err := engine.FindAndCount(&users)

Sum sum functions

agesFloat64, err := engine.Sum(&user, "age")
// SELECT sum(age) AS total FROM user

agesInt64, err := engine.SumInt(&user, "age")
// SELECT sum(age) AS total FROM user

sumFloat64Slice, err := engine.Sums(&user, "age", "score")
// SELECT sum(age), sum(score) FROM user

sumInt64Slice, err := engine.SumsInt(&user, "age", "score")
// SELECT sum(age), sum(score) FROM user

Query conditions builder

err := engine.Where(builder.NotIn("a", 1, 2).And(builder.In("b", "c", "d", "e"))).Find(&users)
// SELECT id, name ... FROM user WHERE a NOT IN (?, ?) AND b IN (?, ?, ?)

Multiple operations in one go routine, no transation here but resue session memory

session := engine.NewSession()
defer session.Close()

user1 := Userinfo{Username: "xiaoxiao", Departname: "dev", Alias: "lunny", Created: time.Now()}
if _, err := session.Insert(&user1); err != nil {
    return err
}

user2 := Userinfo{Username: "yyy"}
if _, err := session.Where("id = ?", 2).Update(&user2); err != nil {
    return err
}

if _, err := session.Exec("delete from userinfo where username = ?", user2.Username); err != nil {
    return err
}

return nil

Transation should on one go routine. There is transaction and resue session memory

session := engine.NewSession()
defer session.Close()

// add Begin() before any action
if err := session.Begin(); err != nil {
    // if returned then will rollback automatically
    return err
}

user1 := Userinfo{Username: "xiaoxiao", Departname: "dev", Alias: "lunny", Created: time.Now()}
if _, err := session.Insert(&user1); err != nil {
    return err
}

user2 := Userinfo{Username: "yyy"}
if _, err := session.Where("id = ?", 2).Update(&user2); err != nil {
    return err
}

if _, err := session.Exec("delete from userinfo where username = ?", user2.Username); err != nil {
    return err
}

// add Commit() after all actions
return session.Commit()

Or you can use Transaction to replace above codes.

res, err := engine.Transaction(func(session *xorm.Session) (interface{}, error) {
    user1 := Userinfo{Username: "xiaoxiao", Departname: "dev", Alias: "lunny", Created: time.Now()}
    if _, err := session.Insert(&user1); err != nil {
        return nil, err
    }

    user2 := Userinfo{Username: "yyy"}
    if _, err := session.Where("id = ?", 2).Update(&user2); err != nil {
        return nil, err
    }

    if _, err := session.Exec("delete from userinfo where username = ?", user2.Username); err != nil {
        return nil, err
    }
    return nil, nil
})

Context Cache, if enabled, current query result will be cached on session and be used by next same statement on the same session.

	sess := engine.NewSession()
	defer sess.Close()

	var context = xorm.NewMemoryContextCache()

	var c2 ContextGetStruct
	has, err := sess.ID(1).ContextCache(context).Get(&c2)
	assert.NoError(t, err)
	assert.True(t, has)
	assert.EqualValues(t, 1, c2.Id)
	assert.EqualValues(t, "1", c2.Name)
	sql, args := sess.LastSQL()
	assert.True(t, len(sql) > 0)
	assert.True(t, len(args) > 0)

	var c3 ContextGetStruct
	has, err = sess.ID(1).ContextCache(context).Get(&c3)
	assert.NoError(t, err)
	assert.True(t, has)
	assert.EqualValues(t, 1, c3.Id)
	assert.EqualValues(t, "1", c3.Name)
	sql, args = sess.LastSQL()
	assert.True(t, len(sql) == 0)
	assert.True(t, len(args) == 0)

Contributing

If you want to pull request, please see CONTRIBUTING. And we also provide Xorm on Google Groups to discuss.

Credits

Contributors

This project exists thanks to all the people who contribute. [Contribute].

Backers

Thank you to all our backers! ð [Become a backer]

Changelog

v0.7.0
- Some bugs fixed
v0.6.6
- Some bugs fixed
v0.6.5
- Postgres schema support
- vgo support
- Add FindAndCount
- Database special params support via NewEngineWithParams
- Some bugs fixed
v0.6.4
- Automatical Read/Write seperatelly
- Query/QueryString/QueryInterface and action with Where/And
- Get support non-struct variables
- BufferSize on Iterate
- fix some other bugs.

More changes ...

Cases

LICENSE

BSD License http://creativecommons.org/licenses/BSD/

Top Related Projects

Quick Overview

Pros

Cons

Code Examples

Getting Started

Competitor Comparisons

Pros of GORM

Cons of GORM

Code Comparison

Pros of sqlx

Cons of sqlx

Code Comparison

Key Differences

Use Cases

Community and Ecosystem

Pros of sqlboiler

Cons of sqlboiler

Code Comparison

Pros of upper/db

Cons of upper/db

Code Comparison

Key Differences

Use Cases

Community and Ecosystem

Pros of Pop

Cons of Pop

Code Comparison

Key Differences

Pros of sqlc

Cons of sqlc

Code Comparison

Summary

Convert designs to code with AI

README

xorm HAS BEEN MOVED TO https://gitea.com/xorm/xorm . THIS REPOSITORY WILL NOT BE UPDATED ANY MORE.

Features

Drivers Support

Installation

Documents

Quick Start

Contributing

Credits

Contributors

Backers

Sponsors

Changelog

Cases

LICENSE

Top Related Projects

Convert designs to code with AI