1 // Copyright 2011 The Go Authors. All rights reserved.
2 // Use of this source code is governed by a BSD-style
3 // license that can be found in the LICENSE file.
5 // Package sql provides a generic interface around SQL (or SQL-like)
18 var drivers = make(map[string]driver.Driver)
20 // Register makes a database driver available by the provided name.
21 // If Register is called twice with the same name or if driver is nil,
23 func Register(name string, driver driver.Driver) {
25 panic("db: Register driver is nil")
27 if _, dup := drivers[name]; dup {
28 panic("db: Register called twice for driver " + name)
30 drivers[name] = driver
33 // NullableString represents a string that may be null.
34 // NullableString implements the ScannerInto interface so
35 // it can be used as a scan destination:
37 // var s NullableString
38 // err := db.QueryRow("SELECT name FROM foo WHERE id=?", id).Scan(&s)
46 // TODO(bradfitz): add other types.
47 type NullableString struct {
49 Valid bool // Valid is true if String is not NULL
52 // ScanInto implements the ScannerInto interface.
53 func (ms *NullableString) ScanInto(value interface{}) error {
55 ms.String, ms.Valid = "", false
59 return convertAssign(&ms.String, value)
62 // ScannerInto is an interface used by Scan.
63 type ScannerInto interface {
64 // ScanInto assigns a value from a database driver.
66 // The value will be of one of the following restricted
73 // nil - for NULL values
75 // An error should be returned if the value can not be stored
76 // without loss of information.
77 ScanInto(value interface{}) error
80 // ErrNoRows is returned by Scan when QueryRow doesn't return a
81 // row. In such a case, QueryRow returns a placeholder *Row value that
82 // defers this error until a Scan.
83 var ErrNoRows = errors.New("db: no rows in result set")
85 // DB is a database handle. It's safe for concurrent use by multiple
91 mu sync.Mutex // protects freeConn and closed
92 freeConn []driver.Conn
96 // Open opens a database specified by its database driver name and a
97 // driver-specific data source name, usually consisting of at least a
98 // database name and connection information.
100 // Most users will open a database via a driver-specific connection
101 // helper function that returns a *DB.
102 func Open(driverName, dataSourceName string) (*DB, error) {
103 driver, ok := drivers[driverName]
105 return nil, fmt.Errorf("db: unknown driver %q (forgotten import?)", driverName)
107 return &DB{driver: driver, dsn: dataSourceName}, nil
110 // Close closes the database, releasing any open resources.
111 func (db *DB) Close() error {
115 for _, c := range db.freeConn {
126 func (db *DB) maxIdleConns() int {
127 const defaultMaxIdleConns = 2
128 // TODO(bradfitz): ask driver, if supported, for its default preference
129 // TODO(bradfitz): let users override?
130 return defaultMaxIdleConns
133 // conn returns a newly-opened or cached driver.Conn
134 func (db *DB) conn() (driver.Conn, error) {
138 return nil, errors.New("sql: database is closed")
140 if n := len(db.freeConn); n > 0 {
141 conn := db.freeConn[n-1]
142 db.freeConn = db.freeConn[:n-1]
147 return db.driver.Open(db.dsn)
150 func (db *DB) connIfFree(wanted driver.Conn) (conn driver.Conn, ok bool) {
153 for n, conn := range db.freeConn {
155 db.freeConn[n] = db.freeConn[len(db.freeConn)-1]
156 db.freeConn = db.freeConn[:len(db.freeConn)-1]
163 func (db *DB) putConn(c driver.Conn) {
166 if n := len(db.freeConn); !db.closed && n < db.maxIdleConns() {
167 db.freeConn = append(db.freeConn, c)
170 db.closeConn(c) // TODO(bradfitz): release lock before calling this?
173 func (db *DB) closeConn(c driver.Conn) {
174 // TODO: check to see if we need this Conn for any prepared statements
179 // Prepare creates a prepared statement for later execution.
180 func (db *DB) Prepare(query string) (*Stmt, error) {
181 // TODO: check if db.driver supports an optional
182 // driver.Preparer interface and call that instead, if so,
183 // otherwise we make a prepared statement that's bound
184 // to a connection, and to execute this prepared statement
185 // we either need to use this connection (if it's free), else
186 // get a new connection + re-prepare + execute on that one.
192 si, err := ci.Prepare(query)
199 css: []connStmt{{ci, si}},
204 // Exec executes a query without returning any rows.
205 func (db *DB) Exec(query string, args ...interface{}) (Result, error) {
206 sargs, err := subsetTypeArgs(args)
217 if execer, ok := ci.(driver.Execer); ok {
218 resi, err := execer.Exec(query, sargs)
219 if err != driver.ErrSkip {
223 return result{resi}, nil
227 sti, err := ci.Prepare(query)
233 resi, err := sti.Exec(sargs)
237 return result{resi}, nil
240 // Query executes a query that returns rows, typically a SELECT.
241 func (db *DB) Query(query string, args ...interface{}) (*Rows, error) {
242 stmt, err := db.Prepare(query)
247 return stmt.Query(args...)
250 // QueryRow executes a query that is expected to return at most one row.
251 // QueryRow always return a non-nil value. Errors are deferred until
252 // Row's Scan method is called.
253 func (db *DB) QueryRow(query string, args ...interface{}) *Row {
254 rows, err := db.Query(query, args...)
255 return &Row{rows: rows, err: err}
258 // Begin starts a transaction. The isolation level is dependent on
260 func (db *DB) Begin() (*Tx, error) {
265 txi, err := ci.Begin()
268 return nil, fmt.Errorf("sql: failed to Begin transaction: %v", err)
277 // DriverDatabase returns the database's underlying driver.
278 func (db *DB) Driver() driver.Driver {
282 // Tx is an in-progress database transaction.
284 // A transaction must end with a call to Commit or Rollback.
286 // After a call to Commit or Rollback, all operations on the
287 // transaction fail with ErrTransactionFinished.
291 // ci is owned exclusively until Commit or Rollback, at which point
292 // it's returned with putConn.
296 // cimu is held while somebody is using ci (between grabConn
300 // done transitions from false to true exactly once, on Commit
301 // or Rollback. once done, all operations fail with
302 // ErrTransactionFinished.
306 var ErrTransactionFinished = errors.New("sql: Transaction has already been committed or rolled back")
308 func (tx *Tx) close() {
310 panic("double close") // internal error
318 func (tx *Tx) grabConn() (driver.Conn, error) {
320 return nil, ErrTransactionFinished
326 func (tx *Tx) releaseConn() {
330 // Commit commits the transaction.
331 func (tx *Tx) Commit() error {
333 return ErrTransactionFinished
336 return tx.txi.Commit()
339 // Rollback aborts the transaction.
340 func (tx *Tx) Rollback() error {
342 return ErrTransactionFinished
345 return tx.txi.Rollback()
348 // Prepare creates a prepared statement for use within a transaction.
350 // The returned statement operates within the transaction and can no longer
351 // be used once the transaction has been committed or rolled back.
353 // To use an existing prepared statement on this transaction, see Tx.Stmt.
354 func (tx *Tx) Prepare(query string) (*Stmt, error) {
355 // TODO(bradfitz): We could be more efficient here and either
356 // provide a method to take an existing Stmt (created on
357 // perhaps a different Conn), and re-create it on this Conn if
358 // necessary. Or, better: keep a map in DB of query string to
359 // Stmts, and have Stmt.Execute do the right thing and
360 // re-prepare if the Conn in use doesn't have that prepared
361 // statement. But we'll want to avoid caching the statement
362 // in the case where we only call conn.Prepare implicitly
363 // (such as in db.Exec or tx.Exec), but the caller package
364 // can't be holding a reference to the returned statement.
365 // Perhaps just looking at the reference count (by noting
366 // Stmt.Close) would be enough. We might also want a finalizer
367 // on Stmt to drop the reference count.
368 ci, err := tx.grabConn()
372 defer tx.releaseConn()
374 si, err := ci.Prepare(query)
388 // Stmt returns a transaction-specific prepared statement from
389 // an existing statement.
392 // updateMoney, err := db.Prepare("UPDATE balance SET money=money+? WHERE id=?")
394 // tx, err := db.Begin()
396 // res, err := tx.Stmt(updateMoney).Exec(123.45, 98293203)
397 func (tx *Tx) Stmt(stmt *Stmt) *Stmt {
398 // TODO(bradfitz): optimize this. Currently this re-prepares
399 // each time. This is fine for now to illustrate the API but
400 // we should really cache already-prepared statements
401 // per-Conn. See also the big comment in Tx.Prepare.
403 if tx.db != stmt.db {
404 return &Stmt{stickyErr: errors.New("sql: Tx.Stmt: statement from different database used")}
406 ci, err := tx.grabConn()
408 return &Stmt{stickyErr: err}
410 defer tx.releaseConn()
411 si, err := ci.Prepare(stmt.query)
421 // Exec executes a query that doesn't return rows.
422 // For example: an INSERT and UPDATE.
423 func (tx *Tx) Exec(query string, args ...interface{}) (Result, error) {
424 ci, err := tx.grabConn()
428 defer tx.releaseConn()
430 if execer, ok := ci.(driver.Execer); ok {
431 resi, err := execer.Exec(query, args)
435 return result{resi}, nil
438 sti, err := ci.Prepare(query)
444 sargs, err := subsetTypeArgs(args)
449 resi, err := sti.Exec(sargs)
453 return result{resi}, nil
456 // Query executes a query that returns rows, typically a SELECT.
457 func (tx *Tx) Query(query string, args ...interface{}) (*Rows, error) {
459 return nil, ErrTransactionFinished
461 stmt, err := tx.Prepare(query)
466 return stmt.Query(args...)
469 // QueryRow executes a query that is expected to return at most one row.
470 // QueryRow always return a non-nil value. Errors are deferred until
471 // Row's Scan method is called.
472 func (tx *Tx) QueryRow(query string, args ...interface{}) *Row {
473 rows, err := tx.Query(query, args...)
474 return &Row{rows: rows, err: err}
477 // connStmt is a prepared statement on a particular connection.
478 type connStmt struct {
483 // Stmt is a prepared statement. Stmt is safe for concurrent use by multiple goroutines.
486 db *DB // where we came from
487 query string // that created the Stmt
488 stickyErr error // if non-nil, this error is returned for all operations
490 // If in a transaction, else both nil:
494 mu sync.Mutex // protects the rest of the fields
497 // css is a list of underlying driver statement interfaces
498 // that are valid on particular connections. This is only
499 // used if tx == nil and one is found that has idle
500 // connections. If tx != nil, txsi is always used.
504 // Exec executes a prepared statement with the given arguments and
505 // returns a Result summarizing the effect of the statement.
506 func (s *Stmt) Exec(args ...interface{}) (Result, error) {
507 _, releaseConn, si, err := s.connStmt()
513 // -1 means the driver doesn't know how to count the number of
514 // placeholders, so we won't sanity check input here and instead let the
515 // driver deal with errors.
516 if want := si.NumInput(); want != -1 && len(args) != want {
517 return nil, fmt.Errorf("db: expected %d arguments, got %d", want, len(args))
520 // Convert args to subset types.
521 if cc, ok := si.(driver.ColumnConverter); ok {
522 for n, arg := range args {
523 args[n], err = cc.ColumnConverter(n).ConvertValue(arg)
525 return nil, fmt.Errorf("db: converting Exec argument #%d's type: %v", n, err)
527 if !driver.IsParameterSubsetType(args[n]) {
528 return nil, fmt.Errorf("db: driver ColumnConverter error converted %T to unsupported type %T",
533 for n, arg := range args {
534 args[n], err = driver.DefaultParameterConverter.ConvertValue(arg)
536 return nil, fmt.Errorf("db: converting Exec argument #%d's type: %v", n, err)
541 resi, err := si.Exec(args)
545 return result{resi}, nil
548 // connStmt returns a free driver connection on which to execute the
549 // statement, a function to call to release the connection, and a
550 // statement bound to that connection.
551 func (s *Stmt) connStmt() (ci driver.Conn, releaseConn func(), si driver.Stmt, err error) {
552 if s.stickyErr != nil {
553 return nil, nil, nil, s.stickyErr
558 err = errors.New("db: statement is closed")
562 // In a transaction, we always use the connection that the
563 // transaction was created on.
566 ci, err = s.tx.grabConn() // blocks, waiting for the connection.
570 releaseConn = func() { s.tx.releaseConn() }
571 return ci, releaseConn, s.txsi, nil
576 for _, v := range s.css {
577 // TODO(bradfitz): lazily clean up entries in this
578 // list with dead conns while enumerating
579 if _, match = s.db.connIfFree(cs.ci); match {
586 // Make a new conn if all are busy.
587 // TODO(bradfitz): or wait for one? make configurable later?
589 ci, err := s.db.conn()
591 return nil, nil, nil, err
593 si, err := ci.Prepare(s.query)
595 return nil, nil, nil, err
598 cs = connStmt{ci, si}
599 s.css = append(s.css, cs)
604 releaseConn = func() { s.db.putConn(conn) }
605 return conn, releaseConn, cs.si, nil
608 // Query executes a prepared query statement with the given arguments
609 // and returns the query results as a *Rows.
610 func (s *Stmt) Query(args ...interface{}) (*Rows, error) {
611 ci, releaseConn, si, err := s.connStmt()
616 // -1 means the driver doesn't know how to count the number of
617 // placeholders, so we won't sanity check input here and instead let the
618 // driver deal with errors.
619 if want := si.NumInput(); want != -1 && len(args) != want {
620 return nil, fmt.Errorf("db: statement expects %d inputs; got %d", si.NumInput(), len(args))
622 sargs, err := subsetTypeArgs(args)
626 rowsi, err := si.Query(sargs)
631 // Note: ownership of ci passes to the *Rows, to be freed
636 releaseConn: releaseConn,
642 // QueryRow executes a prepared query statement with the given arguments.
643 // If an error occurs during the execution of the statement, that error will
644 // be returned by a call to Scan on the returned *Row, which is always non-nil.
645 // If the query selects no rows, the *Row's Scan will return ErrNoRows.
646 // Otherwise, the *Row's Scan scans the first selected row and discards
652 // err := nameByUseridStmt.QueryRow(id).Scan(&s)
653 func (s *Stmt) QueryRow(args ...interface{}) *Row {
654 rows, err := s.Query(args...)
656 return &Row{err: err}
658 return &Row{rows: rows}
661 // Close closes the statement.
662 func (s *Stmt) Close() error {
663 if s.stickyErr != nil {
676 for _, v := range s.css {
677 if ci, match := s.db.connIfFree(v.ci); match {
681 // TODO(bradfitz): care that we can't close
682 // this statement because the statement's
683 // connection is in use?
690 // Rows is the result of a query. Its cursor starts before the first row
691 // of the result set. Use Next to advance through the rows:
693 // rows, err := db.Query("SELECT ...")
698 // err = rows.Scan(&id, &name)
701 // err = rows.Err() // get any error encountered during iteration
705 ci driver.Conn // owned; must call putconn when closed to release
710 lastcols []interface{}
714 // Next prepares the next result row for reading with the Scan method.
715 // It returns true on success, false if there is no next result row.
716 // Every call to Scan, even the first one, must be preceded by a call
718 func (rs *Rows) Next() bool {
722 if rs.lasterr != nil {
725 if rs.lastcols == nil {
726 rs.lastcols = make([]interface{}, len(rs.rowsi.Columns()))
728 rs.lasterr = rs.rowsi.Next(rs.lastcols)
729 return rs.lasterr == nil
732 // Err returns the error, if any, that was encountered during iteration.
733 func (rs *Rows) Err() error {
734 if rs.lasterr == io.EOF {
740 // Scan copies the columns in the current row into the values pointed
741 // at by dest. If dest contains pointers to []byte, the slices should
742 // not be modified and should only be considered valid until the next
743 // call to Next or Scan.
744 func (rs *Rows) Scan(dest ...interface{}) error {
746 return errors.New("db: Rows closed")
748 if rs.lasterr != nil {
751 if rs.lastcols == nil {
752 return errors.New("db: Scan called without calling Next")
754 if len(dest) != len(rs.lastcols) {
755 return fmt.Errorf("db: expected %d destination arguments in Scan, not %d", len(rs.lastcols), len(dest))
757 for i, sv := range rs.lastcols {
758 err := convertAssign(dest[i], sv)
760 return fmt.Errorf("db: Scan error on column index %d: %v", i, err)
766 // Close closes the Rows, preventing further enumeration. If the
767 // end is encountered, the Rows are closed automatically. Close
769 func (rs *Rows) Close() error {
774 err := rs.rowsi.Close()
779 // Row is the result of calling QueryRow to select a single row.
781 // One of these two will be non-nil:
782 err error // deferred error for easy chaining
786 // Scan copies the columns from the matched row into the values
787 // pointed at by dest. If more than one row matches the query,
788 // Scan uses the first row and discards the rest. If no row matches
789 // the query, Scan returns ErrNoRows.
791 // If dest contains pointers to []byte, the slices should not be
792 // modified and should only be considered valid until the next call to
794 func (r *Row) Scan(dest ...interface{}) error {
802 return r.rows.Scan(dest...)
805 // A Result summarizes an executed SQL command.
806 type Result interface {
807 LastInsertId() (int64, error)
808 RowsAffected() (int64, error)