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) {
137 return nil, errors.New("sql: database is closed")
139 if n := len(db.freeConn); n > 0 {
140 conn := db.freeConn[n-1]
141 db.freeConn = db.freeConn[:n-1]
146 return db.driver.Open(db.dsn)
149 func (db *DB) connIfFree(wanted driver.Conn) (conn driver.Conn, ok bool) {
152 for n, conn := range db.freeConn {
154 db.freeConn[n] = db.freeConn[len(db.freeConn)-1]
155 db.freeConn = db.freeConn[:len(db.freeConn)-1]
162 func (db *DB) putConn(c driver.Conn) {
165 if n := len(db.freeConn); !db.closed && n < db.maxIdleConns() {
166 db.freeConn = append(db.freeConn, c)
169 db.closeConn(c) // TODO(bradfitz): release lock before calling this?
172 func (db *DB) closeConn(c driver.Conn) {
173 // TODO: check to see if we need this Conn for any prepared statements
178 // Prepare creates a prepared statement for later execution.
179 func (db *DB) Prepare(query string) (*Stmt, error) {
180 // TODO: check if db.driver supports an optional
181 // driver.Preparer interface and call that instead, if so,
182 // otherwise we make a prepared statement that's bound
183 // to a connection, and to execute this prepared statement
184 // we either need to use this connection (if it's free), else
185 // get a new connection + re-prepare + execute on that one.
191 si, err := ci.Prepare(query)
198 css: []connStmt{{ci, si}},
203 // Exec executes a query without returning any rows.
204 func (db *DB) Exec(query string, args ...interface{}) (Result, error) {
205 sargs, err := subsetTypeArgs(args)
216 if execer, ok := ci.(driver.Execer); ok {
217 resi, err := execer.Exec(query, sargs)
218 if err != driver.ErrSkip {
222 return result{resi}, nil
226 sti, err := ci.Prepare(query)
232 resi, err := sti.Exec(sargs)
236 return result{resi}, nil
239 // Query executes a query that returns rows, typically a SELECT.
240 func (db *DB) Query(query string, args ...interface{}) (*Rows, error) {
241 stmt, err := db.Prepare(query)
246 return stmt.Query(args...)
249 // QueryRow executes a query that is expected to return at most one row.
250 // QueryRow always return a non-nil value. Errors are deferred until
251 // Row's Scan method is called.
252 func (db *DB) QueryRow(query string, args ...interface{}) *Row {
253 rows, err := db.Query(query, args...)
254 return &Row{rows: rows, err: err}
257 // Begin starts a transaction. The isolation level is dependent on
259 func (db *DB) Begin() (*Tx, error) {
264 txi, err := ci.Begin()
267 return nil, fmt.Errorf("sql: failed to Begin transaction: %v", err)
276 // DriverDatabase returns the database's underlying driver.
277 func (db *DB) Driver() driver.Driver {
281 // Tx is an in-progress database transaction.
283 // A transaction must end with a call to Commit or Rollback.
285 // After a call to Commit or Rollback, all operations on the
286 // transaction fail with ErrTransactionFinished.
290 // ci is owned exclusively until Commit or Rollback, at which point
291 // it's returned with putConn.
295 // cimu is held while somebody is using ci (between grabConn
299 // done transitions from false to true exactly once, on Commit
300 // or Rollback. once done, all operations fail with
301 // ErrTransactionFinished.
305 var ErrTransactionFinished = errors.New("sql: Transaction has already been committed or rolled back")
307 func (tx *Tx) close() {
309 panic("double close") // internal error
317 func (tx *Tx) grabConn() (driver.Conn, error) {
319 return nil, ErrTransactionFinished
325 func (tx *Tx) releaseConn() {
329 // Commit commits the transaction.
330 func (tx *Tx) Commit() error {
332 return ErrTransactionFinished
335 return tx.txi.Commit()
338 // Rollback aborts the transaction.
339 func (tx *Tx) Rollback() error {
341 return ErrTransactionFinished
344 return tx.txi.Rollback()
347 // Prepare creates a prepared statement for use within a transaction.
349 // The returned statement operates within the transaction and can no longer
350 // be used once the transaction has been committed or rolled back.
352 // To use an existing prepared statement on this transaction, see Tx.Stmt.
353 func (tx *Tx) Prepare(query string) (*Stmt, error) {
354 // TODO(bradfitz): We could be more efficient here and either
355 // provide a method to take an existing Stmt (created on
356 // perhaps a different Conn), and re-create it on this Conn if
357 // necessary. Or, better: keep a map in DB of query string to
358 // Stmts, and have Stmt.Execute do the right thing and
359 // re-prepare if the Conn in use doesn't have that prepared
360 // statement. But we'll want to avoid caching the statement
361 // in the case where we only call conn.Prepare implicitly
362 // (such as in db.Exec or tx.Exec), but the caller package
363 // can't be holding a reference to the returned statement.
364 // Perhaps just looking at the reference count (by noting
365 // Stmt.Close) would be enough. We might also want a finalizer
366 // on Stmt to drop the reference count.
367 ci, err := tx.grabConn()
371 defer tx.releaseConn()
373 si, err := ci.Prepare(query)
387 // Stmt returns a transaction-specific prepared statement from
388 // an existing statement.
391 // updateMoney, err := db.Prepare("UPDATE balance SET money=money+? WHERE id=?")
393 // tx, err := db.Begin()
395 // res, err := tx.Stmt(updateMoney).Exec(123.45, 98293203)
396 func (tx *Tx) Stmt(stmt *Stmt) *Stmt {
397 // TODO(bradfitz): optimize this. Currently this re-prepares
398 // each time. This is fine for now to illustrate the API but
399 // we should really cache already-prepared statements
400 // per-Conn. See also the big comment in Tx.Prepare.
402 if tx.db != stmt.db {
403 return &Stmt{stickyErr: errors.New("sql: Tx.Stmt: statement from different database used")}
405 ci, err := tx.grabConn()
407 return &Stmt{stickyErr: err}
409 defer tx.releaseConn()
410 si, err := ci.Prepare(stmt.query)
420 // Exec executes a query that doesn't return rows.
421 // For example: an INSERT and UPDATE.
422 func (tx *Tx) Exec(query string, args ...interface{}) (Result, error) {
423 ci, err := tx.grabConn()
427 defer tx.releaseConn()
429 if execer, ok := ci.(driver.Execer); ok {
430 resi, err := execer.Exec(query, args)
434 return result{resi}, nil
437 sti, err := ci.Prepare(query)
443 sargs, err := subsetTypeArgs(args)
448 resi, err := sti.Exec(sargs)
452 return result{resi}, nil
455 // Query executes a query that returns rows, typically a SELECT.
456 func (tx *Tx) Query(query string, args ...interface{}) (*Rows, error) {
458 return nil, ErrTransactionFinished
460 stmt, err := tx.Prepare(query)
465 return stmt.Query(args...)
468 // QueryRow executes a query that is expected to return at most one row.
469 // QueryRow always return a non-nil value. Errors are deferred until
470 // Row's Scan method is called.
471 func (tx *Tx) QueryRow(query string, args ...interface{}) *Row {
472 rows, err := tx.Query(query, args...)
473 return &Row{rows: rows, err: err}
476 // connStmt is a prepared statement on a particular connection.
477 type connStmt struct {
482 // Stmt is a prepared statement. Stmt is safe for concurrent use by multiple goroutines.
485 db *DB // where we came from
486 query string // that created the Stmt
487 stickyErr error // if non-nil, this error is returned for all operations
489 // If in a transaction, else both nil:
493 mu sync.Mutex // protects the rest of the fields
496 // css is a list of underlying driver statement interfaces
497 // that are valid on particular connections. This is only
498 // used if tx == nil and one is found that has idle
499 // connections. If tx != nil, txsi is always used.
503 // Exec executes a prepared statement with the given arguments and
504 // returns a Result summarizing the effect of the statement.
505 func (s *Stmt) Exec(args ...interface{}) (Result, error) {
506 _, releaseConn, si, err := s.connStmt()
512 // -1 means the driver doesn't know how to count the number of
513 // placeholders, so we won't sanity check input here and instead let the
514 // driver deal with errors.
515 if want := si.NumInput(); want != -1 && len(args) != want {
516 return nil, fmt.Errorf("db: expected %d arguments, got %d", want, len(args))
519 // Convert args to subset types.
520 if cc, ok := si.(driver.ColumnConverter); ok {
521 for n, arg := range args {
522 args[n], err = cc.ColumnConverter(n).ConvertValue(arg)
524 return nil, fmt.Errorf("db: converting Exec argument #%d's type: %v", n, err)
526 if !driver.IsParameterSubsetType(args[n]) {
527 return nil, fmt.Errorf("db: driver ColumnConverter error converted %T to unsupported type %T",
532 for n, arg := range args {
533 args[n], err = driver.DefaultParameterConverter.ConvertValue(arg)
535 return nil, fmt.Errorf("db: converting Exec argument #%d's type: %v", n, err)
540 resi, err := si.Exec(args)
544 return result{resi}, nil
547 // connStmt returns a free driver connection on which to execute the
548 // statement, a function to call to release the connection, and a
549 // statement bound to that connection.
550 func (s *Stmt) connStmt() (ci driver.Conn, releaseConn func(), si driver.Stmt, err error) {
551 if s.stickyErr != nil {
552 return nil, nil, nil, s.stickyErr
557 err = errors.New("db: statement is closed")
561 // In a transaction, we always use the connection that the
562 // transaction was created on.
565 ci, err = s.tx.grabConn() // blocks, waiting for the connection.
569 releaseConn = func() { s.tx.releaseConn() }
570 return ci, releaseConn, s.txsi, nil
575 for _, v := range s.css {
576 // TODO(bradfitz): lazily clean up entries in this
577 // list with dead conns while enumerating
578 if _, match = s.db.connIfFree(cs.ci); match {
585 // Make a new conn if all are busy.
586 // TODO(bradfitz): or wait for one? make configurable later?
588 ci, err := s.db.conn()
590 return nil, nil, nil, err
592 si, err := ci.Prepare(s.query)
594 return nil, nil, nil, err
597 cs = connStmt{ci, si}
598 s.css = append(s.css, cs)
603 releaseConn = func() { s.db.putConn(conn) }
604 return conn, releaseConn, cs.si, nil
607 // Query executes a prepared query statement with the given arguments
608 // and returns the query results as a *Rows.
609 func (s *Stmt) Query(args ...interface{}) (*Rows, error) {
610 ci, releaseConn, si, err := s.connStmt()
615 // -1 means the driver doesn't know how to count the number of
616 // placeholders, so we won't sanity check input here and instead let the
617 // driver deal with errors.
618 if want := si.NumInput(); want != -1 && len(args) != want {
619 return nil, fmt.Errorf("db: statement expects %d inputs; got %d", si.NumInput(), len(args))
621 sargs, err := subsetTypeArgs(args)
625 rowsi, err := si.Query(sargs)
630 // Note: ownership of ci passes to the *Rows, to be freed
635 releaseConn: releaseConn,
641 // QueryRow executes a prepared query statement with the given arguments.
642 // If an error occurs during the execution of the statement, that error will
643 // be returned by a call to Scan on the returned *Row, which is always non-nil.
644 // If the query selects no rows, the *Row's Scan will return ErrNoRows.
645 // Otherwise, the *Row's Scan scans the first selected row and discards
651 // err := nameByUseridStmt.QueryRow(id).Scan(&s)
652 func (s *Stmt) QueryRow(args ...interface{}) *Row {
653 rows, err := s.Query(args...)
655 return &Row{err: err}
657 return &Row{rows: rows}
660 // Close closes the statement.
661 func (s *Stmt) Close() error {
662 if s.stickyErr != nil {
675 for _, v := range s.css {
676 if ci, match := s.db.connIfFree(v.ci); match {
680 // TODO(bradfitz): care that we can't close
681 // this statement because the statement's
682 // connection is in use?
689 // Rows is the result of a query. Its cursor starts before the first row
690 // of the result set. Use Next to advance through the rows:
692 // rows, err := db.Query("SELECT ...")
697 // err = rows.Scan(&id, &name)
700 // err = rows.Err() // get any error encountered during iteration
704 ci driver.Conn // owned; must call putconn when closed to release
709 lastcols []interface{}
713 // Next prepares the next result row for reading with the Scan method.
714 // It returns true on success, false if there is no next result row.
715 // Every call to Scan, even the first one, must be preceded by a call
717 func (rs *Rows) Next() bool {
721 if rs.lasterr != nil {
724 if rs.lastcols == nil {
725 rs.lastcols = make([]interface{}, len(rs.rowsi.Columns()))
727 rs.lasterr = rs.rowsi.Next(rs.lastcols)
728 return rs.lasterr == nil
731 // Err returns the error, if any, that was encountered during iteration.
732 func (rs *Rows) Err() error {
733 if rs.lasterr == io.EOF {
739 // Scan copies the columns in the current row into the values pointed
740 // at by dest. If dest contains pointers to []byte, the slices should
741 // not be modified and should only be considered valid until the next
742 // call to Next or Scan.
743 func (rs *Rows) Scan(dest ...interface{}) error {
745 return errors.New("db: Rows closed")
747 if rs.lasterr != nil {
750 if rs.lastcols == nil {
751 return errors.New("db: Scan called without calling Next")
753 if len(dest) != len(rs.lastcols) {
754 return fmt.Errorf("db: expected %d destination arguments in Scan, not %d", len(rs.lastcols), len(dest))
756 for i, sv := range rs.lastcols {
757 err := convertAssign(dest[i], sv)
759 return fmt.Errorf("db: Scan error on column index %d: %v", i, err)
765 // Close closes the Rows, preventing further enumeration. If the
766 // end is encountered, the Rows are closed automatically. Close
768 func (rs *Rows) Close() error {
773 err := rs.rowsi.Close()
778 // Row is the result of calling QueryRow to select a single row.
780 // One of these two will be non-nil:
781 err error // deferred error for easy chaining
785 // Scan copies the columns from the matched row into the values
786 // pointed at by dest. If more than one row matches the query,
787 // Scan uses the first row and discards the rest. If no row matches
788 // the query, Scan returns ErrNoRows.
790 // If dest contains pointers to []byte, the slices should not be
791 // modified and should only be considered valid until the next call to
793 func (r *Row) Scan(dest ...interface{}) error {
801 return r.rows.Scan(dest...)
804 // A Result summarizes an executed SQL command.
805 type Result interface {
806 LastInsertId() (int64, error)
807 RowsAffected() (int64, error)