Dive into Golang database/sql (1)

This is a creation in Article, where the information may have evolved or changed.

Database operations are an essential part of an application, but it is not enough that we often only use SQL packages for Golang. The execution of each statement, what happened behind it. A variety of packages for the SQL package, is not necessary, have not done a hard work?

This is go to database package the first article in a series. This series expands according to the order in which the SQL packages are used in the program

Let's look at a short code:

package mainimport (    "database/sql"    _ "github.com/go-sql-driver/mysql"    "fmt")func main() {    db, err := sql.Open("mysql", "user:password@/dbname")    if nil != err {        panic(err)    }    age := 18    rows,err := db.Query(`SELECT name,age FROM person where age > ?`, age)    if nil != err {        panic(err)    }    defer rows.Close()    for rows.Next() {        var name string        var age int        err := rows.Scan(&name, &age)        if nil != err {            panic(err)        }        fmt.Println(name, age)    }}

This should be the simplest use scenario. This article will also follow the above code, step-by-step.

import _ "somedriver"What are you doing?

Let's take a look at Golang official documentation:

To import a package solely for its side-effects (initialization) with the blank identifier as explicit package name:

import _ "lib/math"

That import _ "somedriver" is, just the way to invoke somedriver the package init . Then we can look at go-sql-driver/mysql the init method together. It's very simple:

func init() {    sql.Register("mysql", &MySQLDriver{})}

Just 1 lines, it's really simple. The method that invokes SQL Register registers a mysql database driver named, and the driver itself is &MySQLDriver{} .

Then let's look at sql the methods in the package Register :

// Register makes a database driver available by the provided name.// If Register is called twice with the same name or if driver is nil,// it panics.func Register(name string, driver driver.Driver) {    driversMu.Lock()    defer driversMu.Unlock()    if driver == nil {        panic("sql: Register driver is nil")    }    if _, dup := drivers[name]; dup {        panic("sql: Register called twice for driver " + name)    }    drivers[name] = driver}

RegisterThe second parameter receives a driver.Driver interface, so the go-sql-driver/mysql package &MySQLDriver must implement a driver.Driver set of methods (which of course it certainly does).

Registerfunction if a driver named name is found to be registered, the panic will be triggered, otherwise it will be registered. Registration is actually very simple, drivers[name] = driver .

driversis a map

drivers   = make(map[string]driver.Driver)

So simply put, it import _ "somedriver" 's actually called sql.Register registering an instance that implements the driver.Driver interface.

The driver provides the most basic support for the SQL package, and the SQL package ultimately works with the database through driver. Actually should not say the SQL package, but should say is the DB instance.

At the beginning of the main function of the program above, execution sql.Open gets an DB instance, then what is an instance and what does it DB sql.Open do?

sql.OpenWhat are you doing?

Check out the official documentation:

func Open(driverName, dataSourceName string) (*DB, error)//Open opens a database specified by its database driver name and a driver-specific data source name, usually consisting of at least a database name and connection information.//Most users will open a database via a driver-specific connection helper function that returns a *DB. No database drivers are included in the Go standard library. See https://golang.org/s/sqldrivers for a list of third-party drivers.//Open may just validate its arguments without creating a connection to the database. To verify that the data source name is valid, call Ping.//The returned DB is safe for concurrent use by multiple goroutines and maintains its own pool of idle connections. Thus, the Open function should be called just once. It is rarely necessary to close a DB.

In a nutshell, open returns an DB instance that DB实例 references the driverName specified database driver. The DB database connection pool is maintained by itself and is thread-safe.

func Open(driverName, dataSourceName string) (*DB, error) {    driversMu.RLock()    driveri, ok := drivers[driverName]    driversMu.RUnlock()    if !ok {        return nil, fmt.Errorf("sql: unknown driver %q (forgotten import?)", driverName)    }    db := &DB{        driver:   driveri,        dsn:      dataSourceName,        openerCh: make(chan struct{}, connectionRequestQueueSize),        lastPut:  make(map[*driverConn]string),    }    go db.connectionOpener()    return db, nil}

Open method:

• According to driverName the corresponding driver
• Generate a DB instance based on driver and DataSourceName
• Another goroutine to perform some sort of task a

If you are more sensitive to goroutine, you might guess what you're doing go db.connectionOpener() . In most cases of go, a new goroutine is in:

• Listen to a channel
• Send a message to a channel

According to the above code it is not difficult to guess, connectionOpener and opennerCh about. It is easy to see the name, connectionOpener translation is 连接创建者 , is responsible for creating the connection. Let's look at the code:

// Runs in a separate goroutine, opens new connections when requested.func (db *DB) connectionOpener() {    for range db.openerCh {        db.openNewConnection()    }}

Whenever a openerCh message is taken from, connectionOpener a connection is created.

How to create a connection is really simple, is to call the method provided by driver Open , the specific first temporarily do not expand. (This decision does not unfold, and Golang's SQL package is very consistent, because the SQL package to open a connection processing, simply defines an interface, let the driver to implement.) That is, in the logic here to open a new connection, specifically how to do I don't care, driver you provide open interface, return to me I want the line. )

The whole DB can draw a picture to understand.

211460-b738074548aa0ba8.png

There are a lot of other details about DB instances, but the sql.Open above is enough for the method. In summary, sql.Open a DB instance is generated based on drivername and DataSourceName, and another goroutine is responsible for creating a new connection (listening to the new connection request for Openerch).

As you can see here, execution sql.Open returns only the DB instance, but it is not known if the connection to the database is really successful. According to the documentation, if you want to confirm that the database is actually connected, you need to execute the Ping method:

Open may just validate its arguments without creating a connection to the database. To verify that the data source name is valid, call Ping.

After successfully getting the DB object, we can manipulate the database.

In the next article, our theme will be 连接池的维护 andwhat's behind the db.Query command