golang 資料庫開發神器 sqlx使用指南

21天精品區塊鏈課程免費學習，深入實戰行家帶路，助力開發人員輕鬆玩轉區塊鏈！>>>   

sqlx使用指南

這邊文章主要基於Illustrated guide to SQLX翻譯而成。
sqlx是一個go語言套件，在內建database／sql包之上增加了很多擴充，簡化資料庫作業碼的書寫。

資源

如果對於go語言的sql用法不熟悉，可以到下面網站學習：
database/sql documentation
go-database-sql tutorial

如果對於golang語言不熟悉，可以到下面網站學習：
The Go tour
How to write Go code
Effective Go
CSDN

由於database/sql介面是sqlx的子集，當前文檔中所有關於database/sql的用法同樣用於sqlx

開始

安裝sqlx 驅動

$ go get github.com/jmoiron/sqlx

•  

本文訪問sqlite資料庫

$ go get github.com/mattn/go-sqlite3

•  

Handle Types

sqlx設計和database/sql使用方法是一樣的。包含有4中主要的handle types：
- sqlx.DB - 和sql.DB相似，表示資料庫。
- sqlx.Tx - 和sql.Tx相似，表示transacion。
- sqlx.Stmt - 和sql.Stmt相似，表示prepared statement。
- sqlx.NamedStmt - 表示prepared statement（支援named parameters）

所有的handler types都提供了對database/sql的相容，意味著當你調用sqlx.DB.Query時，可以直接替換為sql.DB.Query.這就使得sqlx可以很容易的加入到已有的資料庫專案中。

此外，sqlx還有兩個cursor類型：
- sqlx.Rows - 和sql.Rows類似，Queryx返回。
- sqlx.Row - 和sql.Row類似，QueryRowx返回。

連級到資料庫

一個DB執行個體並不是一個連結，但是抽象表示了一個資料庫。這就是為什麼建立一個DB時並不會返回錯誤和panic。它內部維護了一個串連池，當需要進行串連的時候嘗試串連。你可以通過Open建立一個sqlx.DB或通過NewDb從已存在的sql.DB中建立一個新的sqlx.DB

var db *sqlx.DB// exactly the same as the built-indb = sqlx.Open("sqlite3", ":memory:")// from a pre-existing sql.DB; note the required driverNamedb = sqlx.NewDb(sql.Open("sqlite3", ":memory:"), "sqlite3")// force a connection and test that it workederr = db.Ping()

•  

在一些環境下，你可能需要同時開啟一個DB並連結。可以調用connect，這個函數開啟一個新的DB並嘗試Ping。MustConnect函數在連結出錯時會panic。

var err error// open and connect at the same time:db, err = sqlx.Connect("sqlite3", ":memory:")// open and connect at the same time, panicing on errordb = sqlx.MustConnect("sqlite3", ":memory:")

•  

Querying 101

sqlx中的handle types實現了資料庫查詢相同的基本的操作文法。
- Exec(…) (sql.Result, error) - 和 database/sql相比沒有改變
- Query(…) (*sql.Rows, error) - 和 database/sql相比沒有改變
- QueryRow(…) *sql.Row - 和 database/sql相比沒有改變

對內建文法的擴充
- MustExec() sql.Result – Exec, but panic on error
- Queryx(…) (*sqlx.Rows, error) - Query, but return an sqlx.Rows
- QueryRowx(…) *sqlx.Row – QueryRow, but return an sqlx.Row

還有下面新的文法

• Get(dest interface{}, …) error
• Select(dest interface{}, …) error
  下面會詳細介紹這些方法的使用

Exec

Exec和MustExec從串連池中擷取一個串連然後只想對應的query操作。對於不支援ad-hoc query execution的驅動，在操作執行的背後會建立一個prepared statement。在結果返回前這個connection會返回到串連池中。

schema := `CREATE TABLE place (    country text,    city text NULL,    telcode integer);`// execute a query on the serverresult, err := db.Exec(schema)// or, you can use MustExec, which panics on errorcityState := `INSERT INTO place (country, telcode) VALUES (?, ?)`countryCity := `INSERT INTO place (country, city, telcode) VALUES (?, ?, ?)`db.MustExec(cityState, "Hong Kong", 852)db.MustExec(cityState, "Singapore", 65)db.MustExec(countryCity, "South Africa", "Johannesburg", 27)

•  

上面代碼中 result有兩個可能的資料LastInsertId() or RowsAffected(),依賴不同的驅動。
Mysql中，在含有auto-increment key的表中執行插入操作會得到LastInsertId()，在PostgreSQL中這個資訊只有在使用RETURNING語句的row cursor中才會返回。

bindvars

代碼中？預留位置，稱為bindvars，非常重要，你可以總是使用它們來向資料庫發送資料，可以用來組織SQL Injection攻擊。
database/sql並不會對查詢語句進行任何的校正，傳入什麼就發送到server是什麼。
除非driver實現特定的介面，query在資料庫執行之前會準備好。不同的資料庫的bindvars不一樣。
- MySQL 使用？
- PostgreSQL 使用1,1,2等等
- SQLite 使用？或$1
- Oracle 使用:name

其他資料庫可能還不一樣。你可以使用sqlx.DB.Rebind(string) string函數利用？文法來得到一個適合在當前資料庫上執行的query語句。

關於bindvars常見的誤解是他們用於插值。他們只用於參數化，不允許改變sql語句的合法介面。例如，下面的用法是會報錯的

// doesn't workdb.Query("SELECT * FROM ?", "mytable")// also doesn't workdb.Query("SELECT ?, ? FROM people", "name", "location")

•  

Query

Query是database/sql中執行查詢主要使用的方法，該方法返回row結果。Query返回一個sql.Rows對象和一個error對象。

// fetch all places from the dbrows, err := db.Query("SELECT country, city, telcode FROM place")// iterate over each rowfor rows.Next() {    var country string    // note that city can be NULL, so we use the NullString type    var city    sql.NullString    var telcode int    err = rows.Scan(&country, &city, &telcode)}

•  

在使用的時候應該吧Rows當成一個遊標而不是一系列的結果。儘管資料庫驅動緩衝的方法不一樣，通過Next()迭代每次擷取一列結果，對於查詢結果非常巨大的情況下，可以有效限制記憶體的使用，Scan()利用reflect把sql每一列結果映射到go語言的資料類型如string，[]byte等。如果你沒有遍曆完全部的rows結果，一定要記得在把connection返回到串連池之前調用rows.Close()。

Query返回的error有可能是在server準備查詢的時候發生的，也有可能是在執行查詢語句的時候發生的。例如可能從串連池中擷取一個壞的連級（儘管資料庫會嘗試10次去發現或建立一個工作串連）。一般來說，錯誤主要由錯誤的sql語句，錯誤的類似匹配，錯誤的網域名稱或表名等。

在大部分情況下，Rows.Scan()會把從驅動擷取的資料進行拷貝，無論驅動如何使用緩衝。特殊類型sql.RawBytes可以用來從驅動返回的資料總擷取一個zero-copy的slice byte。當下一次調用Next的時候，這個值就不在有效了，因為它指向的記憶體已經被驅動重寫了別的資料。

Query使用的connection在所有的rows通過Next()遍曆完後或者調用rows.Close()後釋放。
Queryx和Query行為很相似，不過返回一個sqlx.Rows對象，支援擴充的scan行為。

type Place struct {    Country       string    City          sql.NullString    TelephoneCode int `db:"telcode"`}rows, err := db.Queryx("SELECT * FROM place")for rows.Next() {    var p Place    err = rows.StructScan(&p)}

•  

sqlx.Rowx的主要擴充就是StructScan，可以自動把查下結果掃描到對應結構體中的域（fileld）中。注意結構體中域（field）必須是可匯出（exported）的，這樣sqlx才能夠寫入值到結構體中。
正如在上面代碼中所示，可以利用db結構體標籤來指定結構體field映射到資料庫中特定的列名，或者用db.MapperFunc()來指定預設的映射。db預設對結構體的filed名執行strings.Lower後，和資料庫的列名進行匹配。關於StructScan,SliceScan,MapScan更詳細的內容請參見後面章節advanced scanning

QueryRow

QueryRow從資料庫server中擷取一列資料。它從串連池中擷取一個連級，然後執行Query，返回一個Row對象，這個對象有一個自己的內部的Rows對象。

row := db.QueryRow("SELECT * FROM place WHERE telcode=?", 852)var telcode interr = row.Scan(&telcode)

•  

不像Query，QueryRow只返回一個Row類型，並不返回error，如果在執行查詢過程中出錯，則錯誤通過Scan返回，如果查詢結果為空白，則返回sql.ErrNoRows。如果Scan本身出錯，error同樣由scan返回。

QueryRow使用的connection當result返回的時候就關閉了，也就意味著使用QueryRow的時候不能夠使用sql.RawByes,因為driver使用sql.RawBytes引用記憶體，在connection回收後可能也會無效。

QueryRowx返回一個sqlx.Row而不是sql.Row，它實現了跟Rows相同的scan方法如上，同時還有進階的scan方法如下：（更進階的scan方法advanced scanning section）

var p Placeerr := db.QueryRowx("SELECT city, telcode FROM place LIMIT 1").StructScan(&p)

•  

Get and Select

Get和Select是一個非常省時的擴充。它們把query和非常靈活的scan文法結合起來。為了更加清晰的介紹它們，我們先討論下什麼是scannalbe：

• a value is scannable if it is not a struct, eg string, int
• a value is scannable if it implements sql.Scanner
• a value is scannable if it is a struct with no exported fields (eg. time.Time)

Get和Select對scannable的類型使用rows.scan，對non-scannable的類型使用rows.StructScan。Get用來擷取單個結果然後Scan，Select用來擷取結果切片。

p := Place{}pp := []Place{}// this will pull the first place directly into perr = db.Get(&p, "SELECT * FROM place LIMIT 1")// this will pull places with telcode > 50 into the slice pperr = db.Select(&pp, "SELECT * FROM place WHERE telcode > ?", 50)// they work with regular types as wellvar id interr = db.Get(&id, "SELECT count(*) FROM place")// fetch at most 10 place namesvar names []stringerr = db.Select(&names, "SELECT name FROM place LIMIT 10")

•  

Get和Select在執行完查詢後就會關閉Rows，並且在執行階段遇到任何問題都會返回錯誤。由於它們內部使用的StructScan，所以 下文中advanced scanning section講的特徵也適用與Get和Select。

Select可以提高編碼小路，但是要注意Select和Queryx是有很大不同的，因為Select會把整個結果一次放入記憶體。如果查詢結果沒有限制特定的大小，那麼最好使用Query/StructScan迭代方法。

Transactions

為了使用transactions，必須使用DB.Begin()來建立，下面的代碼是錯誤的：

db.MustExec("BEGIN;")db.MustExec(...)db.MustExec("COMMIT;")

Exec和其他查詢語句會向DB請求一個connection，執行完後就返回到串連池中，並不能保證每次擷取的connection就是BEGIN執行時使用的那個，所以正確的做法要使用DB.Begin:

tx, err := db.Begin()err = tx.Exec(...)err = tx.Commit()

DB除了Begin之外，還可以使用擴充Beginx()和MustBegin()，返回sqlx.Tx：

tx := db.MustBegin()tx.MustExec(...)err = tx.Commit()

sqlx.Tx擁有sqlx.DB擁有的所有的handle extensions.
由於transaction是一個connection狀態，所以Tx對象必須綁定和控制單個connection。一個Tx會在整個生命週期中儲存一個connection，然後在調用commit或Rollback()的時候釋放掉。你在調用這幾個函數的時候必須十分小心，否則connection會一直被佔用直到被記憶體回收。
由於在一個transaction中只能有一個connection，所以每次只能執行一條語句。在執行另外的query操作之前，cursor對象Row*和Rows必須被Scanned或Closed。如果在資料庫給你返回資料的時候你嘗試向資料庫發送資料，這個操作可能會中斷connection。

最後，Tx對象僅僅執行了一個BEGIN語句和綁定了一個connection，它其實並沒有在server上執行任何操作。而transaction真實的行為包含locking和isolation，在不同資料庫上實現是不同的。

Prepared Statements

對於大部分的資料庫來說，當一個query執行的時候，在資料庫內部statements其實已經準備好了。然後你可以通過sqlx.DB.Prepare()準備statements，便於後面在別的地方使用。

stmt, err := db.Prepare(`SELECT * FROM place WHERE telcode=?`)row = stmt.QueryRow(65)tx, err := db.Begin()txStmt, err := tx.Prepare(`SELECT * FROM place WHERE telcode=?`)row = txStmt.QueryRow(852)

Prepare實際上在資料庫上執行preparation操作，所以它需要一個connection和它的connection state。
database/sql把這部分進行了抽象，自動在新的connection上建立statement，這樣開發人員就能通過stmt對象在多個connection上並發執行操作。
Preparex()返回一個sqlx.Stmt對象，包含sqlx.DB和sqlx.Tx所有的handle 擴充（方法）。

sql.Tx對象含有一個Stmt()方法，從已存在的statement中返回一個特定於改transaction的statement。
sqlx.Tx同樣含有一個Stmtx()方法，從已有的sql.Stmt或sqlx.Stmt中建立一個特定於transaction的sqlx.Stmt。

Query Helpers

“In” Queries

由於database/sql並不會分析你的查詢語句然後直接把參數傳遞給driver，這樣對於IN