Golang Accessing SQL like Database (i)--thought, driver need to implement interface

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

Golang officially does not provide a database driver, but has defined some standard interfaces for developing database drivers, and developers can develop corresponding database drivers according to the official standard interface. The advantage of this is that, as long as the code developed in accordance with the standard interface, no modifications are required to migrate the database. Golang defines this series of common interfaces for SQL (or sql-like) databases through the package Database/sql. The SQL package must be used in conjunction with a database driver. The SQL database Drivers list can refer to SQL Drivers.

Design goals for SQL and Sql/driver

The SQL package consists mainly of two parts, Database/sql and Database/sql/driver. The design goals of SQL and Sql/driver are:

• A generic database API that is provided for SQL or Sql-like databases. The existing go libraries include sqlite,mysql and Postgres, but all have very different sensations and feel less like go style.
• feels like go
• more about common situations. Normally SQL should be portable. The boundary condition of SQL or the extension of a particular database can be checked by the application and conditionally used. A very special database extension or strange use method should not be put into purpose.

• Separates the basic implementation of database driver (the implementation of Sql/driver interfaces) from the implementation of all user layer types and methods.

  In short, it is:
  User code-> SQL package (specific type), Sql/driver (interface), Database driver-> SQL (registered database) + SQL /driver (Implement Interface)

• let type conversion (casting/conversions) be consistent across all drivers. To achieve this, most conversions are done in the SQL package, rather than in individual driver, drivers only a small subset of the data types.
• Flexible type conversions, but requires silent truncation and other precision loss.
• handles concurrency very well. The user does not need to care about the security of each connection thread in the database, nor does it need to maintain the free pool for the connection. The ' DB ' package will handle bookkeeping as needed. Given a *sql. DB, it should be able to be shared between multiple co-processes, without any additional synchronization required.
• for push complexity, it is necessary to devolve to sql+driver packages, rather than exposing the user directly. Otherwise, the SQL package needs to expose an ideal database that is not overly fastidious about how to access it, although this is not the case. The
• provides some optional interfaces for drivers implementations in Sql/driver for some special cases or fastpaths. But only SQL package knows this. For user code There is something that might start or start a little bit faster.

Interfaces defined by Database/sql/driver

Driver. Driver

Driver. Driver is a interface type, driver defines only one method Open,open returns the Conn interface of a database.

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

The returned conn can only be used by one goroutine at a time, because if a conn is used by more than one goroutine at the same time, go cannot tell which Goroutine originated an operation, which could cause data confusion.

The database driver will implement the Open function, which is used to parse the parameter name to obtain database-related information, use this information to initialize a conn after parsing is complete, and return.

Driver. Conn

Conn is a database connection interface. Conn is considered stateful and cannot be used by multiple goroutines at the same time.

Conn defines three methods: Prepare,close and begin.

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

* Prepare returns a readiness state stmt bound to the current connection;
* Close stops the current connection's transtraction and closes the current connection and marks it as no longer in use. Also because a connected free pool is maintained in the SQL package, and only close is called when there is an excess idle connection. So there is no need for third-party drivers to implement their own connection cache (connection caching).
* Begin to start and return a new transaction

Driver. Stmt

Stmt is a ready-to-state interface, and conn binding, and cannot be used by multiple goroutines simultaneously.

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

* Close closes the current state. After Go1.1, stmt does not close a state that is being used by queries.
* Numinput returns the number of reserved parameters. If the return value >=0,sql the package checks the caller's arguments, an error is returned to the caller before the exec and query methods calling stmt are called. If the driver does not know the number of reserved parameters, Numinput returns-1, in which case the SQL package does not check the number of parameters for exec and query.
* EXEC performs no rows of database operations, such as insert,update, etc.;
* Query executes a database operation that returns row, such as SELECT.

Driver. Execer

Execer is an optional implementation of the interface, which can also be implemented through Conn. If Conn does not implement Execer, the SQL package db.exec will first need to prepare a query, execute statement, and then close statement.

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

Driver. Queryer

Queryer is also an optional implementation of the interface, also can be achieved through Conn. If Conn does not implement Queryer, the SQL package Db.query first needs to prepare a query, execute statement, and then close statement.

type Queryer interface {    Query(query string，args []Value) (Rows, error)}

Driver. Result

Result is the results of query execution.

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

• Lastinsertid returns the automatically generated ID of the database, such as the primary key for the insert.
• Rowsaffected returns the number of rows involved in the query operation.

Driver. Columnconverter

Columnconverter is also an optional implementation of the interface. If statement knows the type of its column and can convert any type to a dirver value, Columnconverter can be implemented by stmt.

type ColumnConverter interface {    ColumnConverter(index int) ValueConverter}

Columnconverter returns a valueconverter of the corresponding column based on the passed column index, if the type of column is not known, or if the type cannot be processed, A defaultvalueconverter is returned.

Driver. Rows

Rows is an iterator that executes the results of a query.

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

• Columns returns the name of the columns. The number of columns can be obtained by the length of the slice. If a particular column name cannot be obtained, an empty string is returned.
• Close will stop the iteration of rows.
• Next is called from row in rows that return results from query to the supplied slice. The space occupied by the slice will be the same width as the columns (). Dest Slice can only hold driver value types, but not string types. All strings must be converted to []byte. If there are no more rows, the IO is returned. Eof

Driver. Value

Value is a type of drivers that can be handle. In fact, it is an empty interface and can contain any type. The types that drivers can handle are nil or the following types:

• Int64
• Float64
• bool
• []byte
• String
• Time.time

Reference links

Https://golang.org/src/database/sql/doc.txt
Https://godoc.org/database/sql
https://golang.org/src/database/sql/
Https://godoc.org/github.com/lib/pq
Https://github.com/lib/pq
Https://godoc.org/github.com/lib/pq
http://jmoiron.github.io/sqlx/
Https://github.com/jmoiron/sqlx/blob/master/sqlx.go
Https://github.com/golang/go/wiki/SQLInterface
Https://github.com/golang/go/wiki/SQLDrivers