Go database/sql

67 %
33 %
Information about Go database/sql

Published on April 27, 2014

Author: ArtemKovardin

Source: slideshare.net

Description

I will show how to use Go's database/sql package, with MySQL as an example. Although the documentation is good, it's dense. I'll discuss idiomatic database/sql code, and cover some topics that can save you time and frustration, and perhaps even prevent serious mistakes.

DATABASE/SQL

• github.com/VividCortex • @xaprb • baron@vividcortex.com • linkedin.com/in/xaprb Optimization, Backups, Replication, and more Baron Schwartz, Peter Zaitsev & Vadim Tkachenko High Performance MySQL 3rd Edition CoversVersion 5.5 ME

• Docs: golang.org/pkg/database/sql/ • Tutorial: go-database-sql.org/ • MySQL Driver: github.com/go-sql-driver/mysql/ RESOURCES

THINGS

DB • Not a connection. • Open() Close() • Query() QueryRow() Exec()

ROWS • Next() • Scan()

PATTERNS

package main import ( ! _ "github.com/go-sql-driver/mysql" ! "database/sql" ! "log" ) func main() { ! db, err := sql.Open("mysql", ! ! "user:password@tcp(127.0.0.1:3306)/test") ! if err != nil { ! ! log.Println(err) ! } ! // What do we have here? ! defer db.Close() }

var str string err = db.QueryRow( "select hello from world where id = 1"). Scan(&str) if err != nil && err != sql.ErrNoRows { log.Println(err) } log.Println(str)

q := "select id, hello from world where id = ?" rows, err := db.Query(q, 1) if err != nil { ! log.Fatal(err) } defer rows.Close() var id int var str string for rows.Next() { ! err = rows.Scan(&id, &str) ! if err != nil { ! ! log.Fatal(err) ! } ! // Use the variables scanned from the row }

for rows.Next() { ! // scan } if err = rows.Err(); err != nil { rows.Close() log.Fatal(err) }

stmt, err := db.Prepare( ! "select id, hello from world where id = ?") if err != nil { ! log.Fatal(err) } defer stmt.Close() rows, err := stmt.Query(1) if err != nil { ! log.Fatal(err) } defer rows.Close() for rows.Next() { ! // ... }

stmt, err := db.Prepare( ! "insert into world(hello) values(?)") if err != nil { ! log.Fatal(err) } defer stmt.Close() res, err := stmt.Exec("hello, Dolly") if err != nil { ! log.Fatal(err) }

THINGSTO KNOW

THINGSTO KNOW • There is a connection pool. • You can’t use uint64 with high bit set in parameters.

DON’T BE LAZY

// Don’t do this! for i := 0; i < 50; i++ { ! _, err := db.Query("DELETE FROM hello.world") } // Use this instead! for i := 0; i < 50; i++ { ! _, err := db.Exec("DELETE FROM hello.world") }

MORE HELPFUL ADVICE • Don’t defer() in long-running functions. • Don’t use cxn state. UseTx to bind to a connection. • Don’t use BEGIN and COMMIT via SQL.

SURPRISES

RETRY. RETRY. RETRY. RETRY. RETRY. RETRY. RETRY. RETRY. RETRY. RETRY.

// Exec executes a query without returning any rows. // The args are for any placeholder parameters in the query. func (db *DB) Exec(query string, args ...interface{}) (Result, error) { ! var res Result ! var err error ! for i := 0; i < 10; i++ { ! ! res, err = db.exec(query, args) ! ! if err != driver.ErrBadConn { ! ! ! break ! ! } ! } ! return res, err }

PREPARED STATEMENTS

NULL This page intentionally left blank.

var s sql.NullString err := db.QueryRow( "SELECT name FROM foo WHERE id=?", id).Scan(&s) if s.Valid { // use s.String } else { // NULL value }

CUSTOMTYPES

CUSTOMTYPES • You can implement theValuer and Scanner interfaces. • Why?Transparently transform data in <=> out of the DB. • Compress and uncompress. • Marshall/unmarshall JSON or another type. • Encrypt and decrypt. • See e.g. http://jmoiron.net/blog/built-in-interfaces/

RESOURCES • http://golang.org/pkg/database/sql/ • http://go-database-sql.org/ • https://github.com/go-sql-driver/mysql • http://jmoiron.net/blog/ • @VividCortex * not biased, not biased, not biased

IMAGE CREDITS • http://www.flickr.com/photos/simens/6306917636/ • http://www.flickr.com/photos/dexxus/5794905716/ • http://www.flickr.com/photos/sebastian_bergmann/202396633/ • http://www.flickr.com/photos/doug88888/4794114114/ • http://www.flickr.com/photos/oatsy40/6443878013/ • http://www.sxc.hu/photo/1160562/ • Google Maps (screenshot) • http://www.flickr.com/photos/estherase/13553883/ • http://www.flickr.com/photos/paperpariah/4150220583/ • http://www.flickr.com/photos/zooboing/4743616313/ • http://www.flickr.com/photos/dseneste/5912382808/ • http://www.flickr.com/photos/clickykbd/66165381/sizes/l/ • http://www.flickr.com/photos/mamnaimie/5576980406/ • https://www.flickr.com/photos/zachstern/87431231

Add a comment

Related pages

Go database/sql tutorial

The idiomatic way to use a SQL, or SQL-like, database in Go is through the database/sql package. It provides a lightweight interface to a row-oriented ...
Read more

database/sql Open - The Go Programming Language

Overview Package sql provides a generic interface around SQL (or SQL-like) databases. The sql package must be used in conjunction with a database driver.
Read more

Overview - Go database/sql

To access databases in Go, you use a sql.DB. You use this type to create statements and transactions, execute queries, and fetch results. The first thing ...
Read more

LEAD Database Guide | Gene Ontology Consortium

The GO database server and mirrors are a shared resource and thus we require data mining to be performed in a manner ... and the GO database SQL source, ...
Read more

GO (Transact-SQL) - msdn.microsoft.com

GO is not a Transact-SQL statement; it is a command recognized by the sqlcmd and osql utilities and SQL Server Management Studio Code editor.
Read more

GitHub - VividCortex/go-database-sql-tutorial: A tutorial ...

go-database-sql-tutorial - A tutorial for Go's database/sql package. ... VividCortex / go-database-sql-tutorial. Code. Issues 3. Pull requests 0. Pulse Graphs
Read more

How to: Create a Database (SQL Server ... - msdn.microsoft.com

To create a database, using: ... USE master ; GO CREATE DATABASE Sales ON ... see CREATE DATABASE (SQL Server Transact-SQL).
Read more

GitHub - go-sql-driver/mysql: Go MySQL Driver is a ...

mysql - Go MySQL Driver is a lightweight and fast MySQL driver for Go's (golang) database/sql package
Read more

What's the recommended way to connect to MySQL from Go?

What's the recommended way to connect to MySQL from Go? ... I love that Go provides the database/sql package that libraries can implement.
Read more

docs.oracle.com

docs.oracle.com
Read more