astaxie / beedb

beedb is a go ORM,support database/sql interface，pq/mysql/sqlite

Hi there 👋. You're probably interested in my open source work, so here is a quick summary.

📫 Please open a GitHub issue if you want to discuss something related to an open source project of mine.

Otherwise you can reach me at [email protected]

Last Update: 2022-04-22

Open Source

• beego The oldest Web framework in the Go area. I am the Owner & Originator.
• beedb The First ORM in Go.
• Swagger UI Generator for Go The first tool to generate API docs with Swagger.
• build-web-application-with-golang The First open source Go book.

beedb's Issues

Title case field naming

I want to use this with a database that has a TitleCase naming convention -- is there any way to do it? I didn't see any way to override the snake cased naming convention.

Thanks for the library!

mysql Date type cannot be parsed

If I use date type in mysql db, beedb cannot load the data, err message is "unsupported time format:"
I have to use datetime instead.

x, err := time.Parse("2006-01-02 15:04:05", string(data))

this line has problem.

Got error in example

error:
"unsupported time format: 2012-06-13"

result:
{3 person2 IT 0001-01-01 00:00:00 +0000 UTC}

i think it should be able to set via beedb:formatdate,2006-01-02

Primary key data type

Can the primary key be established as anything other than int? I'd like to be able to store an md5 string as the primary key for one of my tables. I have a struct that amounts to something like this:

type MyThing struct {
        Md5         string `PK`
        Description string
}

I'm storing it thusly:

func storething() {
        db, err := sql.Open("postgres", "user=someuser password=somepass dbname=thingdb")
        if err != nil {
                panic(err)
        }

        var thing MyThing
        thing.Md5 = "5c19ed618b82f97de0fd349d00431fb2"
        thing.Description = "my hovercraft is full of eels"

        orm := beedb.New(db, "pg")
        beedb.OnDebug = true
        orm.Save(&thing)
}

Calling storething() results in the following error:

2012/08/02 22:41:53 panic: reflect: call of reflect.Value.Int on string Value
/home/hobbsc/go/src/pkg/github.com/astaxie/beedb/beedb.go:360 (0x42909b)
        com/astaxie/beedb.(*Model).Save: if reflect.ValueOf(id).Int() == 0 {

----  further stack trace goes here ----

When I set the primary key as type int (be it id or some other field), this works just fine.

beedb在sqlite3上不工作

执行example中的sqlite例子。

insert例子不能正确工作。数据不能保存到数据文件中

Can not enter null values

It's possible I'm missing something, but how do you enter null values in for columns?

For example, giving the code:

type Userinfo struct {
    Uid     int `PK` 
    Age    int
    Departname  string
    Created     time.Time
}

var saveone Userinfo
saveone.Username = "Test Add User"
saveone.Departname = "Dept"
saveone.Created = time.Now()
orm.Save(&saveone)

How would I enter a NULL value for Age? This code will insert a "0" for Age.

save issue

hi astaxie,
Our team used your beedb. But there is a problem in save func. Our model like this

type User struct {
Id int 'sql:"id"'
UserId string 'sql:"user_id"'
User_Pwd 'sql:"user_pwd"'
}

When we save it into mysql, it return a error say Unable_ to save because primary key Id was not found in struct. But Id is a PK in this table already.
When we change struct like this

type User struct {
Id int
UserId string 'sql:"user_id"'
User_Pwd 'sql:"user_pwd"'
}

It will work. Please tell us what's problem in it, thx very much

Delete()删除实体时的问题

Struct定义在同包名的其他文件，主键为首字母大写时,如：Id，在进行id := results[strings.ToLower(orm.PrimaryKey)]操作时获取到的id为nil,导致无法删除的问题。

Snaked Case on orm.PrimaryKey causes PrimaryKey to not match actual PK

If the Primary Key on a struct begins with a capital (e.g. "Uid"), the snakedCaseName on the Primary Key (in ScanPK()) causes the code on lines 366-368 to not work properly:

if reflect.ValueOf(id).Int() == 0 {
    structPtr := reflect.ValueOf(output)
    structVal := structPtr.Elem()
    structField := structVal.FieldByName(orm.PrimaryKey)

Because orm.Primary key would be "uid", FieldByName can't find the correct PrimaryKey "Uid" This would then cause a panic at line 379:

structField.Set(reflect.ValueOf(v))

beedb Save遇到的一个问题

比如我拿到一个beedb.Model的orm
写出类似以下代码
orm := beedb.New(i.DB)
for _, v := range Logs {
value := v
orm.Save(&value)//做插入操作Id自增
}
这样的代码居然只能存循环的第一个
发现是orm.PrimaryKey变了
orm := beedb.New(i.DB)
for _, v := range Logs {
value := v
orm.SetPK("Id").Save(&value)//做插入操作Id自增
}
这样做就对了
或者：
for _, v := range Logs {
orm := beedb.New(i.DB)
value := v
orm.Save(&value)//做插入操作Id自增
}
这样做也行

这个是不是一个bug？

mssql 用不了,连接不报错但是执行没有效果.建议用go-mssqldb(朋友请听进去!鬼知道我经历了什么)

mssql 用不了,连接不报错但是执行没有效果.建议用go-mssqldb(朋友请听进去!鬼知道我经历了什么)

snakeCasedName for primary key

By convention Model struct field names maps to db names via snakeCasedName method but primary key doesn't follow that pattern in Save and Insert .

I have just started with Golang so mostly it could be my mistake but...

In func (orm *Model) Save(output interface{}) error

    id := results[strings.ToLower(orm.PrimaryKey)]
    delete(results, strings.ToLower(orm.PrimaryKey))

it should be using snakeCasedName instead directly using toLower

In func (orm *Model) Insert(properties map[string]interface{}) (int64, error)

    if orm.ParamIdentifier == "pg" {
        statement = fmt.Sprintf("%v RETURNING %v", statement, orm.PrimaryKey)
        var id int64
        orm.Db.QueryRow(statement, args...).Scan(&id)
        return id, nil

here no conversion applied to PrimaryKey so it does not insert without setting PrimaryKey via SetPK

Query syntax error with Postges

Using the /example/pg.go file with a stock Windows Postgres installation results in the following error (and SQL output):

panic: reflect: call of reflect.Value.Set on zero Value

goroutine 1 [running]:
reflect.flag.mustBeAssignable(0x0, 0x575a60)
        C:/Go/src/pkg/reflect/value.go:261 +0x87
reflect.Value.Set(0x0, 0x0, 0x0, 0x4f1290, 0x0, ...)
        C:/Go/src/pkg/reflect/value.go:1191 +0x2b
github.com/astaxie/beedb.(*Model).Save(0x663b88, 0x4eb4e0, 0xf840049700, 0xf840049700, 0x408bdf, ...)
        C:/Users/Alec/Google Drive/Programming/goext/src/github.com/astaxie/beedb/beedb.go:380 +0x4f0
main.insert()
        C:/Users/Alec/Google Drive/Programming/Go/beedb pgtest/beedbtest.go:68 +0xa3
main.main()
        C:/Users/Alec/Google Drive/Programming/Go/beedb pgtest/beedbtest.go:46 +0x186

goroutine 2 [syscall]:
created by runtime.main
        C:/Go/src/pkg/runtime/proc.c:221

goroutine 3 [syscall]:
syscall.Syscall6(0x7fd47042180, 0x5, 0x1fc, 0xf840088c00, 0xf840057308, ...)
        C:/Go/src/pkg/runtime/zsyscall_windows_amd64.c:97 +0x55
syscall.GetQueuedCompletionStatus(0x1fc, 0xf840088c00, 0xf840057308, 0xf840057300, 0xffffffff, ...)
        C:/Go/src/pkg/syscall/zsyscall_windows_amd64.go:490 +0x9e
net.(*resultSrv).Run(0xf840057290, 0x0)
        C:/Go/src/pkg/net/fd_windows.go:107 +0x97
created by net.startServer
        C:/Go/src/pkg/net/fd_windows.go:211 +0x12b

goroutine 4 [select]:
net.(*ioSrv).ProcessRemoteIO(0xf84004f870, 0x0)
        C:/Go/src/pkg/net/fd_windows.go:138 +0x1b5
created by net.startServer
        C:/Go/src/pkg/net/fd_windows.go:216 +0x1ab

PG Admin shows the following log:

STATEMENT: INSERT INTO "userinfo" ("created","departname","username") VALUES ($1, $2, $3) RETURNING uid [2011-12-12 Test_Add_Departname Test_Add_User]
ERROR: syntax error at or near "Test_Add_Departname" at character 106   

And running the generated query manually through the PGAdmin Query editor returns:

ERROR:  syntax error at or near "Test_Add_Departname"
LINE 2: [2011-12-12 Test_Add_Departname Test_Add_User]
                    ^

********** Error **********

ERROR: syntax error at or near "Test_Add_Departname"
SQL state: 42601
Character: 106

I'm not familiar with the SQL query syntax using '$'. What's the rational behind generating statements like that (just wondering, because like as I alluded to, I'm pretty new to database integrated apps)?

Also, I tried doing a very similar test using a struct with a string field that did not have a single word string like "Test_Add_User", and am not sure how the Postgres would interpret something like INSERT INTO "userinfo" ("created","departname","username") VALUES ($1, $2, $3) RETURNING uid [2011-12-12 Sales and Service Test_Add_User] because the strings aren't contained within''`

sqlite delete

请教下 beego sqlite 删除的时候
主表 被子表引用
但是主表的数据依然可以删除
外键约束无效 也不给错误提示 怎么解决啊。
不想每张表都查询验证。。
好麻烦

支持CSV文件的增删改查吗

看demo文件夹，没有csv文件的例子，请问支持CSV文件的增删改查吗

can not work with postgresql

I can insert data to postgresql with go postgres driver api, but not work with beedb.
I give a wrong user account, the beedb complains nothing

Find override previous SetTable

Hi!

Nice work!
I tried to use beedb with PostgreSQL but as I'm using case sensitive table names, that in PG are specified in queries inside "" like this:

SELECT id, name FROM "ENTITY_NAME" WHERE ....

I tried to do (working with already existing tables):

type EntityName struct {
Id int PK
Tipo_loja string
}
var qr EntityName
orm.SetTable(""ENTITY_NAME"").Where("id=?", id).Find(&qr)

But this does not work because Find overrides the SetTable call on the line:

orm.TableName = snakeCasedName(StructName(output))

I think Find should only set the table name if it was not previously done by a call to SetTable
(some kind of similar work would have to be done for the Joins to work)

Thanks

Not support Oracle with go-oci8

When I saving (inserting) data by beedb with go-oci8, always a error sql: statement expects 0 inputs; got 7.

This error throws in database/sql/sql.go, line:671 , I debugged found that is caused by the generated sql using ? as parameter placeholder but go-oci8 (based on oci) only supports which likes :p as placeholder.

Corruption of results in psql with null table columns

When using psql, if a datebase includes null column values, I see random corruption of results, which at first I thought was caused by something I was doing, but on investigation seems to be related to null columns.

To reproduce, start with dbtest/pg.go, insert a few records, then add a column say:

  Rank int,

to the struct, and add it to the database with:

 alter table userinfo add column rank integer;

Then try selectAll and selecting all rows (inserting quite a few and setting a limit of 100 rather than 10 might make it easier to see). The results below have two ints added, one which was filled in, and one which was left blank (null).

Results are returned with seemingly random columns blanked out, e.g. for this:

uid | username | departname | created | rating | rank
-----+---------------+-----------------------------+------------+--------+------
1 | Test_Add_User | Test_Add_Departname | 2011-12-12 | 12 |
2 | Test_Add_User | Test_Add_Departname | 2011-12-12 | 12 |
3 | Test_Add_User | Test_Add_Departname | 2011-12-12 | 12 |
4 | Test_Add_User | Test_Add_Departname | 2011-12-12 | 12 |
5 | Test_Add_User | Test_Add_Departname | 2011-12-12 | 12 |
6 | Test_Add_User | Test_Add_Departname | 2011-12-12 | 12 |
7 | Test_Add_User | Test_Add_Departname | 2011-12-12 | 12 |
8 | Test_Add_User | Test_Add_Departname | 2011-12-12 | 12 |

You'd see this result in the Structs returned, and the result changes in a seemingly random way with every request (even if requests are from sequential runs of the app, not from the same process) - N is username, and D is departname, as you can see random fields are blanked out instead of the null column :

N:Test_Add_UserD:
N:Test_Add_UserD:Test_Add_Departname
N:Test_Add_UserD:
N:Test_Add_UserD:
N:Test_Add_UserD:
N:Test_Add_UserD:Test_Add_Departname
N:D:
N:D:
N:D:
N:D:

Then try setting the rank columns in psql so that nothing is null:

update userinfo set rank=14;

And try requesting the records again, and you get the full records.

I think this might be the cause of #11 - null fields in the db - beedb seems to have a problem at present handling any table with a null value in it.

Field and table names quoting and Bool type

1. There is no field and table names quoting in Find and FindAll methods. I'm using quick fix:

   ...
   if orm.TableName == "" {
       orm.TableName = orm.QuoteIdentifier + snakeCasedName(StructName(output)) + orm.QuoteIdentifier
   }
   for key, _ := range results {
       keys = append(keys, orm.QuoteIdentifier + key + orm.QuoteIdentifier)
   }
   

2. There is no reflect.Bool case in FindMap method. I'm using quick fix:

       ...
       case reflect.Bool:
           if vv.Bool() {
               result[key] = []byte("1")
           } else {
               result[key] = []byte("0")
           }
       }
   

Support Custom Error types

You should user specified error types, instead of errors.New(...). It would make for much better error handling.

ie. instead of errors.New("No record found")
use:
type NoRecordError string

func (e NoRecordError) Error() string {
return "No record found"
}

Syntax errors with UPDATE/Relation does not exist

I've got the following function:

func storebin(b SampleBin) {
        db, err := sql.Open("postgres", "user=testuser password=testpassword dbname=testdb2")
        if err != nil {
                panic(err)
        }

        orm := beedb.New(db)
        beedb.OnDebug = true

        orm.Save(&b)
}

I'm passing it a struct as defined here:

type SampleBin struct {
        Id                   int
        FileName    string
        Description string
        Md5              string
}

When I pass a SampleBin to that function, I get the following in stdout:

UPDATE `sample_bin` SET `description` = ?, `file_name` = ?, `md5` = ? WHERE `Id`=?                                                                                                                                                     &{0xf8400d8580 sample_bin 0 0 `Id`=? [1]  * Id    ` ? 4}

And the following in my postgres logs:

2012-07-27 15:20:10 CDT ERROR:  syntax error at or near "`" at character 820                                                                                                                                                           
12-07-27 15:20:10 CDT STATEMENT:  UPDATE `sample_bin` SET `description` = ?, `file_name` = ?, `md5` = ? WHERE `Id`=?   

Any idea what might be going wrong?

I'm definitely not 100% sure that this is an issue with beedb, I may just be using it wrong. I'm very new to Go and just started using beedb a couple of nights ago, so please pardon my ignorance.

Thanks for your time!

EDIT -

This may or may not related (and may prove it to be my environment), but while trying to run the pg.go example (after changing the database authentication information), my postgres logs show the following:

2012-07-27 15:28:43 CDT ERROR:  relation "userinfo" does not exist at character 13
2012-07-27 15:28:43 CDT STATEMENT:  INSERT INTO "userinfo" ("departname","created","username") VALUES ($1, $2, $3) RETURNING Uid
2012-07-27 15:28:43 CDT LOG:  could not receive data from client: Connection reset by peer
2012-07-27 15:28:43 CDT LOG:  unexpected EOF on client connection

While stdout shows {0 Test_Add_User Test_Add_Departname 2011-12-12}

我更改了代码，使让框架支持了事物，你要不要

support optimistic locking

It would be nice to have this feature.

http://api.rubyonrails.org/classes/ActiveRecord/Locking/Optimistic.html

delete 实体无法取得Id

你好，作者，我在使用beedb删除一个用户时，发现无法取得实体的Id值。通过调试发现在util.go里的方法scanStructIntoMap里存的key与后面delete取PrimaryKey值时，标准不一致。

scanStructIntoMap存的key没有调用strings.ToLower(util.go, 223各225行), 而beedb.go的Delete方法调用了strings.toLower, 这样会造成无法取得primarykey的值(beedb.go 562行, id := results[strings.ToLower(orm.PrimaryKey)])。

谢谢。

我想把表主键设为字符串,但是保存时报错

panic: reflect: call of reflect.Value.Int on string Value
C:/Users/ADMINI~1/AppData/Local/Temp/2/bindist721200707/go/src/pkg/refle
ct/value.go:796 +0xe3
github.com/astaxie/beedb.(*Model).Save(0x11a79a80, 0x577c40, 0x11ae4180, 0x11ae4
180, 0x0, ...)
G:/gopath/src/github.com/astaxie/beedb/beedb.go:364 +0x18b

取出数据不完整

我按照你给的example定义了一个表的结构，但是 Where().Find(&) 之后只有部分字段拿到了值我之前用了goorm然后换到这个也是同样问题，取不出来的字段都是相同的。
求帮助，谢谢~
数据库是mysql

源代码是这样的

package main

import (
    "fmt"
    "github.com/astaxie/beedb"
    _ "github.com/mikespook/mymysql/godrv"
    "database/sql"
)

type Category struct {
    Id int
    Parent_id int
    Order_id int
    Nav_display int
    Name string
    Display_name string
    Status int
}

func main() {
    db, err := sql.Open("mymysql", "golog/root/rabbit")
    if err != nil {
        panic(err)
    }
    orm := beedb.New(db)
    var cat Category
    err = orm.Where("id=?", 1).Find(&cat)
    if err != nil {
        fmt.Printf("%v", err)
    }
    fmt.Printf("%v", cat)
}

打印出来的结果是

{1 0 0 0 cccccccccccccccccc  1}

实际上应该是

Curious intermittent bug SQLite3 and beedb

This is becoming more prevalent. I'm having some trouble with orm.FindAll. The test data in the database is:

1|1|458|1|45000|Debtor has other outstanding obligations|1344643200|1351036800|5|13000000|4380000|1260000|890000|

I print the object after the select and get quite a different result:

&{0 0 0 0 0 Debtor has other outstanding obligations 0 0 5 0 0 0 0 0}

beedb's debug output from that resembles: &{0xf840137300 judgements 0 0 [] attorneyfeesbalance, account, duedayofmonth, interestbalance, bank, statutedate, id, monthlypayment, judgementdate, notation, bankexpensesbalance, principalbalance, apr, lastinterestdate Id ` ? 1}

The fields that get zeroed out are random every time. About 25% of the time, the entire record is returned intact. I had this problem before and I thought it had gone away with your latest fix. If I use Mattn's drivers directly, I don't have intermittent results like this. I can help in any way possible, the drivers are otherwise excellent.

SQLite 3.7.13
Linux Gothmaug 3.4.4-gentoo #1 SMP PREEMPT Tue Jul 17 17:53:20 EDT 2012 x86_64 AMD Phenom(tm) 9950 Quad-Core Processor AuthenticAMD GNU/Linux

Support Transactions

I didn't see a way to get beedb to use a transaction. I'm not sure what the right interface would be, but is it something you are thinking of?

Saving in postgres fails

When initializing an ORM and saving an object like this, I get an error "reflect: call of reflect.Value.Set on zero Value"

orm := beedb.New(rawdb, "pg")
var service models.Service
orm.Save(&service)

This happens because the Primary Key field it tries to update on the struct is "id" instead of the expected "Id".

beedb 在pg上无法持久化数据

如题，手工写sql的可以持久化，但是用beedb无法持久化。用的是example里的例子。

环境:mac osx 10.9 pg9.2 go1.1.2

Consider Using StructTag for Identifying Primary Key Columns

It appears that the way looking for tags in structs is implemented conflicts with the standards.

If we could prepend the tag with a key, i.e:

type Userinfo struct {
    Uid     int `beedb:"PK"` \\ Use this colum as the primary key.
    Username    string
    Departname  string
    Created     time.Time
}

That way I could do something like this:

Uid     int `beedb:",PK" json:"uid,omitempty"`

It looks like that ScanPK would need to change:

func (orm *Model) ScanPK(output interface{}) *Model {
    if reflect.TypeOf(reflect.Indirect(reflect.ValueOf(output)).Interface()).Kind() == reflect.Slice {
        sliceValue := reflect.Indirect(reflect.ValueOf(output))
        sliceElementType := sliceValue.Type().Elem()
        for i := 0; i < sliceElementType.NumField(); i++ {
            bb := reflect.ValueOf(sliceElementType.Field(i).Tag)
            if bb.String() == "PK" {
                orm.PrimaryKey = sliceElementType.Field(i).Name
            }
        }
    } else {
        tt := reflect.TypeOf(reflect.Indirect(reflect.ValueOf(output)).Interface())
        for i := 0; i < tt.NumField(); i++ {
            bb := reflect.ValueOf(tt.Field(i).Tag)
            if bb.String() == "PK" {
                orm.PrimaryKey = tt.Field(i).Name
            }
        }
    }
    return orm

}

More on StructTag in the docs.

sql: statement expects 2 inputs; got 0

sql: statement expects 2 inputs; got 0

Select() 方法，里面传递的表字段竟然有区分大小写，Why?

table : userinfo(Uid, Username)

err = orm.Where("uid > ?", 4).Select("uid,username").FindAll(&allusers)
fmt.Println(allusers) // [{0 } {0 }]

err = orm.Where("uid > ?", 4).Select("Uid,Username").FindAll(&allusers)
fmt.Println(allusers) // [{5 jiemoon} {6 Tom}]

beedb 根据主键无法删除数据问题

Struct结构体，主键为首字母大写时,如：Id，在执行删除操作无法删除
beedb.go 文件 562行 id := results[strings.ToLower(orm.PrimaryKey)] 进行操作时获取到的id为nil,导致无法删除。
将562行修改文 id := results[orm.PrimaryKey] 即可执行

Offset and Order

orm.Limit(10).OrderBy("uid").FindAll(&alluser)

我没查到任何东西，加上offset 之后也不能查询！

Cannot get data when find by uid

As the Sqlite.go , I can't get data at selectone() function.

Support auto create table by model

Support auto create table by model should be a exciting feature.