...
Run Format

Package sql

import "database/sql"
Overview
Index
Examples
Subdirectories

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. See https://golang.org/s/sqldrivers for a list of drivers.

For more usage examples, see the wiki page at https://golang.org/s/sqlwiki.

Index ▾

Variables
func Drivers() []string
func Register(name string, driver driver.Driver)
type DB
    func Open(driverName, dataSourceName string) (*DB, error)
    func (db *DB) Begin() (*Tx, error)
    func (db *DB) Close() error
    func (db *DB) Driver() driver.Driver
    func (db *DB) Exec(query string, args ...interface{}) (Result, error)
    func (db *DB) Ping() error
    func (db *DB) Prepare(query string) (*Stmt, error)
    func (db *DB) Query(query string, args ...interface{}) (*Rows, error)
    func (db *DB) QueryRow(query string, args ...interface{}) *Row
    func (db *DB) SetConnMaxLifetime(d time.Duration)
    func (db *DB) SetMaxIdleConns(n int)
    func (db *DB) SetMaxOpenConns(n int)
    func (db *DB) Stats() DBStats
type DBStats
type NullBool
    func (n *NullBool) Scan(value interface{}) error
    func (n NullBool) Value() (driver.Value, error)
type NullFloat64
    func (n *NullFloat64) Scan(value interface{}) error
    func (n NullFloat64) Value() (driver.Value, error)
type NullInt64
    func (n *NullInt64) Scan(value interface{}) error
    func (n NullInt64) Value() (driver.Value, error)
type NullString
    func (ns *NullString) Scan(value interface{}) error
    func (ns NullString) Value() (driver.Value, error)
type RawBytes
type Result
type Row
    func (r *Row) Scan(dest ...interface{}) error
type Rows
    func (rs *Rows) Close() error
    func (rs *Rows) Columns() ([]string, error)
    func (rs *Rows) Err() error
    func (rs *Rows) Next() bool
    func (rs *Rows) Scan(dest ...interface{}) error
type Scanner
type Stmt
    func (s *Stmt) Close() error
    func (s *Stmt) Exec(args ...interface{}) (Result, error)
    func (s *Stmt) Query(args ...interface{}) (*Rows, error)
    func (s *Stmt) QueryRow(args ...interface{}) *Row
type Tx
    func (tx *Tx) Commit() error
    func (tx *Tx) Exec(query string, args ...interface{}) (Result, error)
    func (tx *Tx) Prepare(query string) (*Stmt, error)
    func (tx *Tx) Query(query string, args ...interface{}) (*Rows, error)
    func (tx *Tx) QueryRow(query string, args ...interface{}) *Row
    func (tx *Tx) Rollback() error
    func (tx *Tx) Stmt(stmt *Stmt) *Stmt

Package files

convert.go sql.go

Variables

ErrNoRows is returned by Scan when QueryRow doesn't return a row. In such a case, QueryRow returns a placeholder *Row value that defers this error until a Scan.

var ErrNoRows = errors.New("sql: no rows in result set")
var ErrTxDone = errors.New("sql: Transaction has already been committed or rolled back")

func Drivers

Drivers returns a sorted list of the names of the registered drivers.

func Drivers() []string

func 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)

type DB

DB is a database handle representing a pool of zero or more underlying connections. It's safe for concurrent use by multiple goroutines.

The sql package creates and frees connections automatically; it also maintains a free pool of idle connections. If the database has a concept of per-connection state, such state can only be reliably observed within a transaction. Once DB.Begin is called, the returned Tx is bound to a single connection. Once Commit or Rollback is called on the transaction, that transaction's connection is returned to DB's idle connection pool. The pool size can be controlled with SetMaxIdleConns.

type DB struct {
        // contains filtered or unexported fields
}

func Open

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.

func Open(driverName, dataSourceName string) (*DB, error)

func (*DB) Begin

Begin starts a transaction. The isolation level is dependent on the driver.

func (db *DB) Begin() (*Tx, error)

func (*DB) Close

Close closes the database, releasing any open resources.

It is rare to Close a DB, as the DB handle is meant to be long-lived and shared between many goroutines.

func (db *DB) Close() error

func (*DB) Driver

Driver returns the database's underlying driver.

func (db *DB) Driver() driver.Driver

func (*DB) Exec

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)

func (*DB) Ping

Ping verifies a connection to the database is still alive, establishing a connection if necessary.

func (db *DB) Ping() error

func (*DB) Prepare

Prepare creates a prepared statement for later queries or executions. Multiple queries or executions may be run concurrently from the returned statement. The caller must call the statement's Close method when the statement is no longer needed.

func (db *DB) Prepare(query string) (*Stmt, error)

func (*DB) Query

Query executes a query that returns rows, typically a SELECT. The args are for any placeholder parameters in the query.

func (db *DB) Query(query string, args ...interface{}) (*Rows, error)

Example

Code:

    age := 27
    rows, err := db.Query("SELECT name FROM users WHERE age=?", age)
    if err != nil {
            log.Fatal(err)
    }
    defer rows.Close()
    for rows.Next() {
            var name string
            if err := rows.Scan(&name); err != nil {
                    log.Fatal(err)
            }
            fmt.Printf("%s is %d\n", name, age)
    }
    if err := rows.Err(); err != nil {
            log.Fatal(err)
    }

func (*DB) QueryRow

QueryRow executes a query that is expected to return at most one row. QueryRow always returns a non-nil value. Errors are deferred until Row's Scan method is called.

func (db *DB) QueryRow(query string, args ...interface{}) *Row

Example

Code:

    id := 123
    var username string
    err := db.QueryRow("SELECT username FROM users WHERE id=?", id).Scan(&username)
    switch {
    case err == sql.ErrNoRows:
            log.Printf("No user with that ID.")
    case err != nil:
            log.Fatal(err)
    default:
            fmt.Printf("Username is %s\n", username)
    }

func (*DB) SetConnMaxLifetime

SetConnMaxLifetime sets the maximum amount of time a connection may be reused.

Expired connections may be closed lazily before reuse.

If d <= 0, connections are reused forever.

func (db *DB) SetConnMaxLifetime(d time.Duration)

func (*DB) SetMaxIdleConns

SetMaxIdleConns sets the maximum number of connections in the idle connection pool.

If MaxOpenConns is greater than 0 but less than the new MaxIdleConns then the new MaxIdleConns will be reduced to match the MaxOpenConns limit

If n <= 0, no idle connections are retained.

func (db *DB) SetMaxIdleConns(n int)

func (*DB) SetMaxOpenConns

SetMaxOpenConns sets the maximum number of open connections to the database.

If MaxIdleConns is greater than 0 and the new MaxOpenConns is less than MaxIdleConns, then MaxIdleConns will be reduced to match the new MaxOpenConns limit

If n <= 0, then there is no limit on the number of open connections. The default is 0 (unlimited).

func (db *DB) SetMaxOpenConns(n int)

func (*DB) Stats

Stats returns database statistics.

func (db *DB) Stats() DBStats

type DBStats

DBStats contains database statistics.

type DBStats struct {
        // OpenConnections is the number of open connections to the database.
        OpenConnections int
}

type NullBool

NullBool represents a bool that may be null. NullBool implements the Scanner interface so it can be used as a scan destination, similar to NullString.

type NullBool struct {
        Bool  bool
        Valid bool // Valid is true if Bool is not NULL
}

func (*NullBool) Scan

Scan implements the Scanner interface.

func (n *NullBool) Scan(value interface{}) error

func (NullBool) Value

Value implements the driver Valuer interface.

func (n NullBool) Value() (driver.Value, error)

type NullFloat64

NullFloat64 represents a float64 that may be null. NullFloat64 implements the Scanner interface so it can be used as a scan destination, similar to NullString.

type NullFloat64 struct {
        Float64 float64
        Valid   bool // Valid is true if Float64 is not NULL
}

func (*NullFloat64) Scan

Scan implements the Scanner interface.

func (n *NullFloat64) Scan(value interface{}) error

func (NullFloat64) Value

Value implements the driver Valuer interface.

func (n NullFloat64) Value() (driver.Value, error)

type NullInt64

NullInt64 represents an int64 that may be null. NullInt64 implements the Scanner interface so it can be used as a scan destination, similar to NullString.

type NullInt64 struct {
        Int64 int64
        Valid bool // Valid is true if Int64 is not NULL
}

func (*NullInt64) Scan

Scan implements the Scanner interface.

func (n *NullInt64) Scan(value interface{}) error

func (NullInt64) Value

Value implements the driver Valuer interface.

func (n NullInt64) Value() (driver.Value, error)

type NullString

NullString represents a string that may be null. NullString implements the Scanner interface so it can be used as a scan destination:

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

func (*NullString) Scan

Scan implements the Scanner interface.

func (ns *NullString) Scan(value interface{}) error

func (NullString) Value

Value implements the driver Valuer interface.

func (ns NullString) Value() (driver.Value, error)

type RawBytes

RawBytes is a byte slice that holds a reference to memory owned by the database itself. After a Scan into a RawBytes, the slice is only valid until the next call to Next, Scan, or Close.

type RawBytes []byte

type Result

A Result summarizes an executed SQL command.

type Result interface {
        // LastInsertId returns the integer generated by the database
        // in response to a command. Typically this will be from an
        // "auto increment" column when inserting a new row. Not all
        // databases support this feature, and the syntax of such
        // statements varies.
        LastInsertId() (int64, error)

        // RowsAffected returns the number of rows affected by an
        // update, insert, or delete. Not every database or database
        // driver may support this.
        RowsAffected() (int64, error)
}

type Row

Row is the result of calling QueryRow to select a single row.

type Row struct {
        // contains filtered or unexported fields
}

func (*Row) Scan

Scan copies the columns from the matched row into the values pointed at by dest. See the documentation on Rows.Scan for details. If more than one row matches the query, Scan uses the first row and discards the rest. If no row matches the query, Scan returns ErrNoRows.

func (r *Row) Scan(dest ...interface{}) error

type Rows

Rows is the result of a query. Its cursor starts before the first row of the result set. Use Next to advance through the rows:

rows, err := db.Query("SELECT ...")
...
defer rows.Close()
for rows.Next() {
    var id int
    var name string
    err = rows.Scan(&id, &name)
    ...
}
err = rows.Err() // get any error encountered during iteration
...
type Rows struct {
        // contains filtered or unexported fields
}

func (*Rows) Close

Close closes the Rows, preventing further enumeration. If Next returns false, the Rows are closed automatically and it will suffice to check the result of Err. Close is idempotent and does not affect the result of Err.

func (rs *Rows) Close() error

func (*Rows) Columns

Columns returns the column names. Columns returns an error if the rows are closed, or if the rows are from QueryRow and there was a deferred error.

func (rs *Rows) Columns() ([]string, error)

func (*Rows) Err

Err returns the error, if any, that was encountered during iteration. Err may be called after an explicit or implicit Close.

func (rs *Rows) Err() error

func (*Rows) Next

Next prepares the next result row for reading with the Scan method. It returns true on success, or false if there is no next result row or an error happened while preparing it. Err should be consulted to distinguish between the two cases.

Every call to Scan, even the first one, must be preceded by a call to Next.

func (rs *Rows) Next() bool

func (*Rows) Scan

Scan copies the columns in the current row into the values pointed at by dest. The number of values in dest must be the same as the number of columns in Rows.

Scan converts columns read from the database into the following common Go types and special types provided by the sql package:

*string
*[]byte
*int, *int8, *int16, *int32, *int64
*uint, *uint8, *uint16, *uint32, *uint64
*bool
*float32, *float64
*interface{}
*RawBytes
any type implementing Scanner (see Scanner docs)

In the most simple case, if the type of the value from the source column is an integer, bool or string type T and dest is of type *T, Scan simply assigns the value through the pointer.

Scan also converts between string and numeric types, as long as no information would be lost. While Scan stringifies all numbers scanned from numeric database columns into *string, scans into numeric types are checked for overflow. For example, a float64 with value 300 or a string with value "300" can scan into a uint16, but not into a uint8, though float64(255) or "255" can scan into a uint8. One exception is that scans of some float64 numbers to strings may lose information when stringifying. In general, scan floating point columns into *float64.

If a dest argument has type *[]byte, Scan saves in that argument a copy of the corresponding data. The copy is owned by the caller and can be modified and held indefinitely. The copy can be avoided by using an argument of type *RawBytes instead; see the documentation for RawBytes for restrictions on its use.

If an argument has type *interface{}, Scan copies the value provided by the underlying driver without conversion. When scanning from a source value of type []byte to *interface{}, a copy of the slice is made and the caller owns the result.

Source values of type time.Time may be scanned into values of type *time.Time, *interface{}, *string, or *[]byte. When converting to the latter two, time.Format3339Nano is used.

Source values of type bool may be scanned into types *bool, *interface{}, *string, *[]byte, or *RawBytes.

For scanning into *bool, the source may be true, false, 1, 0, or string inputs parseable by strconv.ParseBool.

func (rs *Rows) Scan(dest ...interface{}) error

type Scanner

Scanner is an interface used by Scan.

type Scanner interface {
        // Scan assigns a value from a database driver.
        //
        // The src value will be of one of the following types:
        //
        //    int64
        //    float64
        //    bool
        //    []byte
        //    string
        //    time.Time
        //    nil - for NULL values
        //
        // An error should be returned if the value cannot be stored
        // without loss of information.
        Scan(src interface{}) error
}

type Stmt

Stmt is a prepared statement. A Stmt is safe for concurrent use by multiple goroutines.

type Stmt struct {
        // contains filtered or unexported fields
}

func (*Stmt) Close

Close closes the statement.

func (s *Stmt) Close() error

func (*Stmt) Exec

Exec executes a prepared statement with the given arguments and returns a Result summarizing the effect of the statement.

func (s *Stmt) Exec(args ...interface{}) (Result, error)

func (*Stmt) Query

Query executes a prepared query statement with the given arguments and returns the query results as a *Rows.

func (s *Stmt) Query(args ...interface{}) (*Rows, error)

func (*Stmt) QueryRow

QueryRow executes a prepared query statement with the given arguments. If an error occurs during the execution of the statement, that error will be returned by a call to Scan on the returned *Row, which is always non-nil. If the query selects no rows, the *Row's Scan will return ErrNoRows. Otherwise, the *Row's Scan scans the first selected row and discards the rest.

Example usage:

var name string
err := nameByUseridStmt.QueryRow(id).Scan(&name)
func (s *Stmt) QueryRow(args ...interface{}) *Row

type Tx

Tx is an in-progress database transaction.

A transaction must end with a call to Commit or Rollback.

After a call to Commit or Rollback, all operations on the transaction fail with ErrTxDone.

The statements prepared for a transaction by calling the transaction's Prepare or Stmt methods are closed by the call to Commit or Rollback.

type Tx struct {
        // contains filtered or unexported fields
}

func (*Tx) Commit

Commit commits the transaction.

func (tx *Tx) Commit() error

func (*Tx) Exec

Exec executes a query that doesn't return rows. For example: an INSERT and UPDATE.

func (tx *Tx) Exec(query string, args ...interface{}) (Result, error)

func (*Tx) Prepare

Prepare creates a prepared statement for use within a transaction.

The returned statement operates within the transaction and can no longer be used once the transaction has been committed or rolled back.

To use an existing prepared statement on this transaction, see Tx.Stmt.

func (tx *Tx) Prepare(query string) (*Stmt, error)

func (*Tx) Query

Query executes a query that returns rows, typically a SELECT.

func (tx *Tx) Query(query string, args ...interface{}) (*Rows, error)

func (*Tx) QueryRow

QueryRow executes a query that is expected to return at most one row. QueryRow always returns a non-nil value. Errors are deferred until Row's Scan method is called.

func (tx *Tx) QueryRow(query string, args ...interface{}) *Row

func (*Tx) Rollback

Rollback aborts the transaction.

func (tx *Tx) Rollback() error

func (*Tx) Stmt

Stmt returns a transaction-specific prepared statement from an existing statement.

Example:

updateMoney, err := db.Prepare("UPDATE balance SET money=money+? WHERE id=?")
...
tx, err := db.Begin()
...
res, err := tx.Stmt(updateMoney).Exec(123.45, 98293203)

The returned statement operates within the transaction and can no longer be used once the transaction has been committed or rolled back.

func (tx *Tx) Stmt(stmt *Stmt) *Stmt

Subdirectories

Name Synopsis
..
driver Package driver defines interfaces to be implemented by database drivers as used by package sql.