これはGo Wednesdayという社内勉強で共有した資料です。
- Review the purpose of package reading.
- Basically how to use
- How did I study?
- What's goal of the database/sql pkg?
- impressions
Review the purpose of package reading.
Code Reading Recommendations for Go's Standard Library
- Know how to use the standard packages
- Read GoDoc to know the specifications
- Read the test code to know the specifications
- Know how to write Go-like code
- Read the code to know how to write code for Go's standard library
- Know how to write Go tests
- Read the test code to know how to write Go tests
Basically how to use
I prepare simple way. I used docker-compose.
version: '3.1' services: db: image: mysql:8.0 command: --default-authentication-plugin=mysql_native_password restart: always environment: MYSQL_ROOT_PASSWORD: passw0rd ports: - 3306:3306
- create album table. And you have to create database "recordings" before execute this sql.
DROP TABLE IF EXISTS album; CREATE TABLE album ( id INT AUTO_INCREMENT NOT NULL, title VARCHAR(128) NOT NULL, artist VARCHAR(255) NOT NULL, price DECIMAL(5,2) NOT NULL, PRIMARY KEY (`id`) ); INSERT INTO album (title, artist, price) VALUES ('Blue Train', 'John Coltrane', 56.99), ('Giant Steps', 'John Coltrane', 63.99), ('Jeru', 'Gerry Mulligan', 17.99), ('Sarah Vaughan', 'Sarah Vaughan', 34.98);
- main.go
package main import ( "database/sql" "fmt" "github.com/go-sql-driver/mysql" "log") type Album struct { ID int64 Title string Artist string Price float32 } var db *sql.DB func main() { // Capture connection properties. cfg := mysql.Config{ User: "root", Passwd: "passw0rd", Net: "tcp", Addr: "127.0.0.1:3306", DBName: "recordings", AllowNativePasswords: true, } // Get a database handle. var err error db, err = sql.Open("mysql", cfg.FormatDSN()) if err != nil { log.Fatal(err) } pingErr := db.Ping() if pingErr != nil { log.Fatal(pingErr) } fmt.Println("Connected!") albums, err := albumsByArtist("John Coltrane") if err != nil { log.Fatal(err) } fmt.Printf("Albums found: %v\n", albums) } // albumsByArtist queries for albums that have the specified artist name.func albumsByArtist(name string) ([]Album, error) { // An albums slice to hold data from returned rows. var albums []Album rows, err := db.Query("SELECT * FROM album WHERE artist = ?", name) if err != nil { return nil, fmt.Errorf("albumsByArtist %q: %v", name, err) } defer rows.Close() // Loop through rows, using Scan to assign column data to struct fields. for rows.Next() { var alb Album if err := rows.Scan(&alb.ID, &alb.Title, &alb.Artist, &alb.Price); err != nil { return nil, fmt.Errorf("albumsByArtist %q: %v", name, err) } albums = append(albums, alb) } if err := rows.Err(); err != nil { return nil, fmt.Errorf("albumsByArtist %q: %v", name, err) } return albums, nil }
- result
$ go run main.go
Connected!
Albums found: [{1 Blue Train John Coltrane 56.99} {2 Giant Steps John Coltrane 63.99}]
How did I study?
My own approach to this package is to read the design policy and see how the code is written.
What's goal of the database/sql pkg?
Goals of the sql and sql/driver packages:
- Provide a generic database API for a variety of SQL or SQL-like databases. There currently exist Go libraries for SQLite, MySQL, and Postgres, but all with a very different feel, and often a non-Go-like feel.
// omission
- Separate out the basic implementation of a database driver (implementing the sql/driver interfaces) vs the implementation of all the user-level types and convenience methods. In a nutshell:
User Code ---> sql package (concrete types) ---> sql/driver (interfaces) Database Driver -> sql (to register) + sql/driver (implement interfaces)
The figure is based on the document. This time we will focus on this structure and read the package.
Provide general database API - Like Driver function
The sql package must be used in conjunction with a database driver.
- Provide a generic database API for a variety of SQL or SQL-like databases.
We can get a list of imported drivers. And then, we can open specific driver by sql.Open().
- main.go
package main import ( "database/sql" "fmt" "github.com/go-sql-driver/mysql" "log") var db *sql.DB func main() { // return a list of all registered database drivers. drivers := sql.Drivers() fmt.Println(drivers) // Capture connection properties. cfg := mysql.Config{ User: "root", Passwd: "passw0rd", Net: "tcp", Addr: "127.0.0.1:3306", DBName: "mysql", AllowNativePasswords: true, } // Get a database handle. var err error db, err = sql.Open("mysql", cfg.FormatDSN()) if err != nil { log.Fatal(err) } pingErr := db.Ping() if pingErr != nil { log.Fatal(pingErr) } fmt.Println("Connected!") }
- result
$ go run main.go [mysql] Connected!
Just in case, I tried to register custom driver. I was able to register it.
- main.go
package main import ( "database/sql" "database/sql/driver" "fmt" "github.com/go-sql-driver/mysql" "log") var db *sql.DB // MyCustomDriver must implement the database/sql/driver.Driver interface.type MyCustomDriver struct{} // Open is a method to open a connection to a database, but is not implemented in this sample. func (d MyCustomDriver) Open(name string) (driver.Conn, error) { return nil, fmt.Errorf("not implemented") } func main() { // Register the custom driver. sql.Register("mycustomdriver", &MyCustomDriver{}) // return a list of all registered database drivers. drivers := sql.Drivers() fmt.Println(drivers) // Capture connection properties. cfg := mysql.Config{ User: "root", Passwd: "passw0rd", Net: "tcp", Addr: "127.0.0.1:3306", DBName: "mysql", AllowNativePasswords: true, } // Get a database handle. var err error db, err = sql.Open("mysql", cfg.FormatDSN()) if err != nil { log.Fatal(err) } pingErr := db.Ping() if pingErr != nil { log.Fatal(pingErr) } fmt.Println("Connected!") }
- result
$ go run main.go [mycustomdriver mysql] Connected!
The reason we can use sql.Open() is because driver implements Open in this way.
For example: MySQL
// Open new Connection.// See https://github.com/go-sql-driver/mysql#dsn-data-source-name for how // the DSN string is formatted func (d MySQLDriver) Open(dsn string) (driver.Conn, error) { cfg, err := ParseDSN(dsn) if err != nil { return nil, err } c := newConnector(cfg) return c.Connect(context.Background()) }
As you can see, we are benefiting from the interface.
Type conversion consistency - Like sql/convert.go
The design document states the following:
There currently exist Go libraries for SQLite, MySQL, and Postgres, but all with a very different feel, and often a non-Go-like feel.
This package converts the non-Go-like parts to be handled in a Go-like manner. Let's convert the query results to Go types.
Look at the following code:
- users table.
DROP TABLE IF EXISTS users; CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL, birthday DATE NOT NULL ); INSERT INTO users (name, birthday) VALUES ('John Coltrane', '1926-09-23'), ('Gerry Mulligan', '1927-04-06'), ('Sarah Vaughan', '1924-03-27'), ('Miles Davis', '1926-05-26'), ('Thelonious Monk', '1917-10-10');
- main.go
package main import ( "database/sql" "fmt" "github.com/go-sql-driver/mysql" "log" "time") var db *sql.DB func main() { cfg := mysql.Config{ User: "root", Passwd: "passw0rd", Net: "tcp", Addr: "127.0.0.1:3306", DBName: "playground", AllowNativePasswords: true, ParseTime: true, } db, err := sql.Open("mysql", cfg.FormatDSN()) if err != nil { log.Fatal(err) } defer db.Close() rows, err := db.Query("SELECT id, name, birthday FROM users") if err != nil { log.Fatal(err) } defer rows.Close() for rows.Next() { // type declaration var ( id int name string birthday time.Time ) err = rows.Scan(&id, &name, &birthday) if err != nil { log.Fatal(err) } fmt.Printf("id: %d, name: %s, birthday: %v\n", id, name, birthday) } if err = rows.Err(); err != nil { log.Fatal(err) } }
- result
$ go run main.go id: 1, name: John Coltrane, birthday: 1926-09-23 00:00:00 +0000 UTC id: 2, name: Gerry Mulligan, birthday: 1927-04-06 00:00:00 +0000 UTC id: 3, name: Sarah Vaughan, birthday: 1924-03-27 00:00:00 +0000 UTC id: 4, name: Miles Davis, birthday: 1926-05-26 00:00:00 +0000 UTC id: 5, name: Thelonious Monk, birthday: 1917-10-10 00:00:00 +0000 UTC
The sql.Scan() function appears to perform type conversion in the process of execution. That is the convertAssignRows function.
func convertAssignRows(dest, src any, rows *Rows) error { // Common cases, without reflect. switch s := src.(type) { case string: switch d := dest.(type) { case *string: if d == nil { return errNilPtr } *d = s return nil case *[]byte: if d == nil { return errNilPtr } *d = []byte(s) return nil case *RawBytes: if d == nil { return errNilPtr } *d = rows.setrawbuf(append(rows.rawbuf(), s...)) return nil } case []byte: switch d := dest.(type) { case *string: if d == nil { return errNilPtr } *d = string(s) return nil case *any: if d == nil { return errNilPtr } *d = bytes.Clone(s) return nil case *[]byte: if d == nil { return errNilPtr } *d = bytes.Clone(s) return nil case *RawBytes: if d == nil { return errNilPtr } *d = s return nil } case time.Time: switch d := dest.(type) { case *time.Time: *d = s return nil case *string: *d = s.Format(time.RFC3339Nano) return nil case *[]byte: if d == nil { return errNilPtr } *d = []byte(s.Format(time.RFC3339Nano)) return nil case *RawBytes: if d == nil { return errNilPtr } *d = rows.setrawbuf(s.AppendFormat(rows.rawbuf(), time.RFC3339Nano)) return nil } // continue codes...
Thanks to this process, we can handle values in a Go-like manner without being aware of it.
Concurrency handling - Like DB struct
The design document states the following:
Handle concurrency well. Users shouldn't need to care about the database's per-connection thread safety issues (or lack thereof), and shouldn't have to maintain their own free pools of connections. The 'sql' package should deal with that bookkeeping as needed. Given an *sql.DB, it should be possible to share that instance between multiple goroutines, without any extra synchronization.
Where it manages connection pool? Hints are here.
DB struct has mu, freeConn and etc...
type DB struct { // Total time waited for new connections. waitDuration atomic.Int64 connector driver.Connector // numClosed is an atomic counter which represents a total number of // closed connections. Stmt.openStmt checks it before cleaning closed // connections in Stmt.css. numClosed atomic.Uint64 mu sync.Mutex // protects following fields freeConn []*driverConn // free connections ordered by returnedAt oldest to newest connRequests connRequestSet numOpen int // number of opened and pending open connections // continue codes...
freeConn checks for connections that can be reused.
For example, when we conduct Query.
- main.go
// continue code... rows, err := db.Query("SELECT id, name, birthday FROM users") //
- sql.go
In sql.go, Query() calls QueryContext().
func (db *DB) QueryContext(ctx context.Context, query string, args ...any) (*Rows, error) { var rows *Rows var err error err = db.retry(func(strategy connReuseStrategy) error { rows, err = db.query(ctx, query, args, strategy) return err }) return rows, err } // Query executes a query that returns rows, typically a SELECT. // The args are for any placeholder parameters in the query. // // Query uses [context.Background] internally; to specify the context, use // [DB.QueryContext]. func (db *DB) Query(query string, args ...any) (*Rows, error) { return db.QueryContext(context.Background(), query, args...) }
And then QueryContext calls db.query(). This local method calls db.conn().
func (db *DB) conn(ctx context.Context, strategy connReuseStrategy) (*driverConn, error) { db.mu.Lock() if db.closed { db.mu.Unlock() return nil, errDBClosed } // Check if the context is expired. select { default: case <-ctx.Done(): db.mu.Unlock() return nil, ctx.Err() } lifetime := db.maxLifetime // Prefer a free connection, if possible. last := len(db.freeConn) - 1 if strategy == cachedOrNewConn && last >= 0 { // Reuse the lowest idle time connection so we can close // connections which remain idle as soon as possible. conn := db.freeConn[last] db.freeConn = db.freeConn[:last] conn.inUse = true if conn.expired(lifetime) { db.maxLifetimeClosed++ db.mu.Unlock() conn.Close() return nil, driver.ErrBadConn } db.mu.Unlock() // Reset the session if required. if err := conn.resetSession(ctx); errors.Is(err, driver.ErrBadConn) { conn.Close() return nil, err } return conn, nil }
Use mutexes to lock DB structures during method execution to prevent other goroutines from modifying the same DB instance at the same time.
db.mu.Lock()
It looks for available connections from the freeConn slice and, if found, removes them from the pool and marks them as in use.
last := len(db.freeConn) - 1 if strategy == cachedOrNewConn && last >= 0 { // Reuse the lowest idle time connection so we can close // connections which remain idle as soon as possible. conn := db.freeConn[last] db.freeConn = db.freeConn[:last] conn.inUse = true
Checks up to session reset and returns this connection if there are no problems. Users do not need to be aware of these. How wonderful!
The interface for ResetSession is defined to be implemented in the driver layer.
// SessionResetter may be implemented by [Conn] to allow drivers to reset the // session state associated with the connection and to signal a bad connection. type SessionResetter interface { // ResetSession is called prior to executing a query on the connection // if the connection has been used before. If the driver returns ErrBadConn // the connection is discarded. ResetSession(ctx context.Context) error }
impressions
I like the design philosophy of golang and the way it is written. An opportunity to better understand that it would be this package reading. Great opportunity!
Based on the design policy of the database/sql package, I have tried to dig deeper.
I hope this helps!
