xorm

https://my.oschina.net/goskyblue/blog/206799

http://blog.xorm.io/2014/1/1/1-7-weapons.html

 

 

摘要: for golang package github.com/lunny/xorm, the quick reference card.

xorm is a very famous golang lib, for operating relation database.

Install

go get -v github.com/go-xorm/xorm

Documentation

• godoc-xorm
• gowalker-xorm
• Quickstart

Get started

Import packages

package main

import (
    "log"

    "github.com/go-xorm/xorm"
    _ "github.com/mattn/go-sqlite3" ) 

Define models

type User struct { Id int64 Name string `xorm:"unique"` Email string } 

Create database engine

var xe *xorm.Engine

func init() { var err error xe, err = xorm.NewEngine("sqlite3", "./test.db") if err != nil { log.Fatal(err) } if err = xe.Sync(&User{}); err != nil { log.Fatal(err) } } 

Insert and Query

func main() { xe.Insert(&User{Name: "UU", Email: "uu@example.com"}) var user = &User{Name: "UU"} exists, err := xe.Get(user) if err != nil { log.Fatal(err) } log.Printf("Exists: %v, User: %#v", exists, user) } 

Driver and source

Example

x, err := xorm.NewEngine("mysql", "root:123@/test?charset=utf8")
defer x.Close()

other driver

mysql github.com/go-sql-driver/mysql

[username[:password]@][protocol[(address)]]/dbname[?param1=value1&...&paramN=valueN]

mymysql github.com/ziutek/mymysql/godrv

usage todo.

postgresql github.com/lib/pq

1. postgres://username:password@address/dbname?sslmode=verify-full
2. user=pqgotest dbname=pqgotest sslmode=verify-full

sqlite3 github.com/mattn/go-sqlite3

1. ./dbfile

Define models

// default matches
// - string => varchar(255)
type Model struct { Id int64 SmallInt int64 `xorm:"smallint"` Int int64 `xorm:"int"` Text string `xorm:"text"` DefaultVal int64 `xorm:"default 3"` Bool bool `xorm:"bool"` Unique string `xorm:"unique"` // unique([Unique]) UniqA1 string `xorm:"unique(a)"` // unique([UniqA1, UniqA2]) UniqA2 string `xorm:"unique(a)"` Version int64 `xorm:"version"` // will be filled 1 on insert and autoincr on update CreatedAt time.Time `xorm:"created"` // will be filled in current time on insert UpdatedAt time.Time `xorm:"updated"` // will be filled in current time on insert or update } 

column types

ref: https://github.com/lunny/xorm/blob/master/docs/COLUMNTYPE.md

• pk # primary key
• autoincr
• [not ]null
• unique or unique(uniquename)
• index or index(indexname)
• extends
• `- # ignore this field
• created # This field will be filled in current time on insert
• version # This field will be filled 1 on insert and autoincrement on update
• default 0 # default value, need quoted if it is a string

CRUD funcs

Create, Retrive, Update, Delete

INSERT (Create) more

• Insert one record

    user := &User{
        Name: "test",
    }
    affected, err := engine.Insert(user)
  

• Insert multi records

    users := make([]User, 0)
    users = append(users, User{Name: "test"})
    affected, err := engine.Insert(&users)
  

Query (Retrieve) more

• Get one record

    var user = User{Id: 1}
    has, err := engine.Get(&user)
  

• Fetch multi records in to slice or map

    var everyone []User
    err := engine.Find(&everyone)
  
    users := make(map[int64]User)
    err := engine.Find(&users)
  

• Use where and limit

    var users []User
    err := engine.Where("id > ?", 3).Limit(10, 5).Find(&users) // limit 10 offset 5 

• Query use struct

    var users []User
    err := engine.Find(&users, &User{School: "hangzhou"})
  

• Use in function

    var users []User
    err := engine.In("id", 1, 3, 5).Find(&users) 

• Default will query all cols, use Cols to restrict that

    var users []User
    err := engine.Cols("id", "name").Find(&users)
  

• Count

    total, err := engine.Where("id > ?", 1).Count(&User{})
  

  If user is not empty, it will become the select rule.

    total, err := engine.Count(&User{School: "hangzhou"})
  

• Complex example

    var users []User
    err = x.Cols("age", "name")
        .Where("`age` > ?", 10) .And("`age` < 30") .Limit(10, 5) // limit 10 offset 5 .Desc("age") .In("school", "hangzhou", "beijing"}) .Find(&users) 

Update (Update) more