5.1 database/sql interface

5.1 database/sql interface

Go doesn’t provide any official database drivers, unlike other languages like PHP which do. However, it does have some database driver interface standards for developers to develop database drivers with. The advantage is that if your code is developed according to these interface standards, you will not need to change any code if your database changes. Let’s see what these database interface standards are.

sql.Register

This function is in the database/sql package for registering database drivers when you use third-party database drivers. All of these should call the Register(name string, driver driver.Driver) function in init() in order to register themselves.

Let’s take a look at the corresponding mymysql and sqlite3 driver code:

    //https://github.com/mattn/go-sqlite3 driver
    func init() {
        sql.Register("sqlite3", &SQLiteDriver{})
    }
    //https://github.com/mikespook/mymysql driver
    // Driver automatically registered in database/sql
    var d = Driver{proto: "tcp", raddr: "127.0.0.1:3306"}
    func init() {
        Register("SET NAMES utf8")
        sql.Register("mymysql", &d)
    }

We see that all third-party database drivers implement this function to register themselves, and Go uses a map to save user drivers inside of database/sql.

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

Therefore, this registration function can register as many drivers as you may require, each with different names.

We always see the following code when we use third-party drivers:

    import (
        "database/sql"
        _ "github.com/mattn/go-sqlite3"
    )

Here, the underscore (also known as a ‘blank’) _ can be quite confusing for many beginners, but this is a great feature in Go. We already know that this underscore identifier is used for discarding values from function returns, and also that you must use all packages that you’ve imported in your code in Go. So when the blank is used with import, it means that you need to execute the init() function of that package without directly using it, which is a perfect fit for the use-case of registering database drivers.

driver.Driver

Driver is an interface containing an Open(name string) method that returns a Conn interface.

    type Driver interface {
        Open(name string) (Conn, error)
    }

This is a one-time Conn, which means it can only be used once per goroutine. The following code will cause errors to occur:

    ...
    go goroutineA (Conn)  // query
    go goroutineB (Conn)  // insert
    ...

Because Go has no idea which goroutine does which operation, the query operation may get the result of the insert operation, and vice-versa.

All third-party drivers should have this function to parse the name of Conn and return the correct results.

driver.Conn

This is a database connection interface with some methods, and as i’ve said above, the same Conn can only be used once per goroutine.

    type Conn interface {
        Prepare(query string) (Stmt, error)
        Close() error
        Begin() (Tx, error)
    }

Prepare returns the prepare status of corresponding SQL commands for querying and deleting, etc.
Close closes the current connection and cleans resources. Most third-party drivers implement some kind of connection pool, so you don’t need to cache connections which can cause unexpected errors.
Begin returns a Tx that represents a transaction handle. You can use it for querying, updating, rolling back transactions, etc.

driver.Stmt

This is a ready status that corresponds with Conn, so it can only be used once per goroutine (as is the case with Conn).

    type Stmt interface {
        Close() error
        NumInput() int
        Exec(args []Value) (Result, error)
        Query(args []Value) (Rows, error)
    }

Close closes the current connection but still returns row data if it is executing a query operation.
NumInput returns the number of obligate arguments. Database drivers should check their caller’s arguments when the result is greater than 0, and it returns -1 when database drivers don’t know any obligate argument.
Exec executes the update/insert SQL commands prepared in Prepare, returns Result.
Query executes the select SQL command prepared in Prepare, returns row data.

driver.Tx

Generally, transaction handles only have submit or rollback methods, and database drivers only need to implement these two methods.

    type Tx interface {
        Commit() error
        Rollback() error
    }

driver.Execer

This is an optional interface.

    type Execer interface {
        Exec(query string, args []Value) (Result, error)
    }

If the driver doesn’t implement this interface, when you call DB.Exec, it will automatically call Prepare, then return Stmt. After that it executes the Exec method of Stmt, then closes Stmt.

driver.Result

This is the interface for results of update/insert operations.

    type Result interface {
        LastInsertId() (int64, error)
        RowsAffected() (int64, error)
    }

LastInsertId returns auto-increment Id number after a database insert operation.
RowsAffected returns rows that were affected by query operations.

driver.Rows

This is the interface for the result of a query operation.

    type Rows interface {
        Columns() []string
        Close() error
        Next(dest []Value) error
    }

Columns returns field information of database tables. The slice has a one-to-one correspondence with SQL query fields only, and does not return all fields of that database table.
Close closes Rows iterator.
Next returns next data and assigns to dest, converting all strings into byte arrays, and gets io.EOF error if no more data is available.

driver.RowsAffected

This is an alias of int64, but it implements the Result interface.

    type RowsAffected int64
    func (RowsAffected) LastInsertId() (int64, error)
    func (v RowsAffected) RowsAffected() (int64, error)

driver.Value

This is an empty interface that can contain any kind of data.

    type Value interface{}

The Value must be something that drivers can operate on or nil, so it should be one of the following types:

    int64
    float64
    bool
    []byte
    string   [*] Except Rows.Next which cannot return string
    time.Time

driver.ValueConverter

This defines an interface for converting normal values to driver.Value.

    type ValueConverter interface {
        ConvertValue(v interface{}) (Value, error)
    }

This interface is commonly used in database drivers and has many useful features:

Converts driver.Value to a corresponding database field type, for example converts int64 to uint16.
Converts database query results to driver.Value.
Converts driver.Value to a user defined value in the scan function.

driver.Valuer

This defines an interface for returning driver.Value.

    type Valuer interface {
        Value() (Value, error)
    }

Many types implement this interface for conversion between driver.Value and itself.

At this point, you should know a bit about developing database drivers in Go. Once you can implement interfaces for operations like add, delete, update, etc., there are only a few problems left related to communicating with specific databases.

database/sql

database/sql defines even more high-level methods on top of database/sql/driver for more convenient database operations, and it suggests that you implement a connection pool.

    type DB struct {
        driver   driver.Driver
        dsn      string
        mu       sync.Mutex // protects freeConn and closed
        freeConn []driver.Conn
        closed   bool
    }

As you can see, the Open function returns a DB that has a freeConn, and this is a simple connection pool. Its implementation is very simple and ugly. It uses defer db.putConn(ci, err) in the Db.prepare function to put a connection into the connection pool. Everytime you call the Conn function, it checks the length of freeConn. If it’s greater than 0, that means there is a reusable connection and it directly returns to you. Otherwise it creates a new connection and returns.