sqlx/index.html at gh-pages · oscgithub/sqlx

494 lines (434 loc) · 35.5 KB
<!DOCTYPE html>
<html lang="en">
        <meta charset="utf-8">
        <title>Illustrated Guide to SQLX</title>
        <link href='https://fonts.googleapis.com/css?family=PT+Sans:400,700' rel='stylesheet' type='text/css'>
        <link href="assets/css/prettify.css" rel="stylesheet">
        <script src="assets/js/prettify.js"></script>
        <style>
            .container { width: 660px; margin: 0 auto; margin-top: 2em; color: #333; font-family: "PT Sans", sans-serif; }
            .prettyprint.linenums { box-shadow: none; -webkit-box-shadow: none; }
            .prettyprint { border: 0;  background-color: #f2f2f2; padding: .5em 1em; }
            ol.linenums { padding: 0; margin: 0; list-style-type: none; }
            ol.linenums li { padding: 0; margin: 0; }
            strong { color: #000; }
            h1 { margin-bottom: 12px; }
            h2 { margin-bottom: 6px; }
            td.code { width: 100%; }
            a.permalink { visibility: hidden; text-decoration: none; }
            h1:hover a.permalink { visibility: visible; color: #eee; font-size: 85%; }
            h2:hover a.permalink { visibility: visible; color: #eee; font-size: 85%; }
            h3:hover a.permalink { visibility: visible; color: #eee; font-size: 85%; }
            h4:hover a.permalink { visibility: visible; color: #eee; font-size: 85%; }
            h1 a.permalink:hover { color: #555; }
            h2 a.permalink:hover { color: #555; }
            h3 a.permalink:hover { color: #555; }
            h4 a.permalink:hover { color: #555; }
            pre {
                font-family: consolas,monospace;
                font-size: 80%;
                line-height: 1.5em;
                padding: .5em 1em; 
                border-radius: 5px;
                background-color: #f2f2f2;
                text-shadow: 0 1px 0 white;
                overflow-x: auto;
            code {
                font-family: consolas,monospace;
                font-size: 85%;
                color: #000;           
                font-weight: bold;
        </style>
    </head>
    <body onload="prettyPrint();">
    <div class="container">
    <h1>Illustrated guide to SQLX</h1>
<p><a href="https://github.com/jmoiron/sqlx/"><img style="position: absolute; top: 0; right: 0; border: 0;" src="https://s3.amazonaws.com/github/ribbons/forkme_right_darkblue_121621.png" alt="Fork me on GitHub"></a></p>
<p><code>sqlx</code> is a package for Go which provides a set of extensions on top of the excellent built-in <code>database/sql</code> package.</p>
<p>Examining <em>Go</em> idioms is the focus of this document, so there is no presumption being made that any <em>SQL</em> herein is actually a recommended way to use a database.  It will not cover setting up a Go development environment, basic Go information about syntax or semantics, or SQL itself.</p>
<p>Finally, the standard <code>err</code> variable will be used to indicate that errors are being returned, but for brevity they will be ignored.  You should make sure to check all errors in an actual program.</p>
<h2>Resources <a href="#resources" class="permalink" id="resources">&para;</a></h2>
<p>There are other resources of excellent information about using SQL in Go:</p>
<li><a href="https://golang.org/pkg/database/sql/">database/sql documentation</a></li>
<li><a href="http://go-database-sql.org/">go-database-sql tutorial</a></li>
<p>If you need help getting started with Go itself, I recommend these resources:</p>
<li><a href="https://tour.golang.org">The Go tour</a></li>
<li><a href="https://golang.org/doc/code.html">How to write Go code</a></li>
<li><a href="https://golang.org/doc/effective_go.html">Effective Go</a></li>
<p>Because the <code>database/sql</code> interface is a subset of sqlx, all of the advice in these documents about <code>database/sql</code> usage also apply to usage of sqlx.</p>
<h2>Getting Started <a href="#gettingStarted" class="permalink" id="gettingStarted">&para;</a></h2>
<p>You will want to install <code>sqlx</code> and a database driver.  Since it's infrastructureless, I recommend mattn's sqlite3 driver to start out with:</p>
<pre class="prettyprint linenums">
$ go get github.com/jmoiron/sqlx
$ go get github.com/mattn/go-sqlite3
<h2>Handle Types <a href="#handles" class="permalink" id="handles">&para;</a></h2>
<p><code>sqlx</code> is intended to have the same <em>feel</em> as <code>database/sql</code>.  There are 4 main <em>handle</em> types:</p>
<li><code>sqlx.DB</code> - analagous to <code>sql.DB</code>, a representation of a database</li>
<li><code>sqlx.Tx</code> - analagous to <code>sql.Tx</code>, a representation of a transaction</li>
<li><code>sqlx.Stmt</code> - analagous to <code>sql.Stmt</code>, a representation of a prepared statement</li>
<li><code>sqlx.NamedStmt</code> - a representation of a prepared statement with support for <a href="#namedParams">named
  parameters</a></li>
<p>Handle types all <a href="https://golang.org/doc/effective_go.html#embedding">embed</a> their <code>database/sql</code> equivalents, meaning that when you call <code>sqlx.DB.Query</code>, you are calling the <em>same</em> code as <code>sql.DB.Query</code>.  This makes it easy to introduce into an existing codebase.</p>
<p>In addition to these, there are 2 <em>cursor</em> types:</p>
<li><code>sqlx.Rows</code> - analagous to <code>sql.Rows</code>, a cursor returned from <code>Queryx</code></li>
<li><code>sqlx.Row</code> - analagous to <code>sql.Row</code>, a result returned from <code>QueryRowx</code></li>
<p>As with the handle types, <code>sqlx.Rows</code> embeds <code>sql.Rows</code>.  Because the underlying implementation was inaccessible, <code>sqlx.Row</code> is a partial re-implementation of <code>sql.Row</code> that retains the standard interface.</p>
<h3>Connecting to Your Database <a href="#connecting" class="permalink" id="connecting">&para;</a></h3>
<p>A <code>DB</code> instance is <em>not</em> a connection, but an abstraction representing a Database.  This is why creating a DB does not return an error and will not panic.  It maintains a <a href="#connectionPool">connection pool</a> internally, and will attempt to connect when a connection is first needed.  You can create an sqlx.DB via <code>Open</code> or by creating a new sqlx DB handle from an existing sql.DB via <code>NewDb</code>:</p>
<pre class="prettyprint linenums">
var db *sqlx.DB
// exactly the same as the built-in
db = sqlx.Open("sqlite3", ":memory:")
// from a pre-existing sql.DB; note the required driverName
db = sqlx.NewDb(sql.Open("sqlite3", ":memory:"), "sqlite3")
// force a connection and test that it worked
err = db.Ping()
<p>In some situations, you might want to open a DB and connect at the same time; for instance, in order to catch configuration issues during your initialization phase.  You can do this in one go with <code>Connect</code>, which Opens a new DB and attempts a <code>Ping</code>.  The <code>MustConnect</code> variant will panic when encountering an error, suitable for use at the module level of your package.</p>
<pre class="prettyprint linenums">
var err error
// open and connect at the same time:
db, err = sqlx.Connect("sqlite3", ":memory:")
// open and connect at the same time, panicing on error
db = sqlx.MustConnect("sqlite3", ":memory:")
<h2>Querying 101 <a href="#querying101" class="permalink" id="querying101">&para;</a></h2>
<p>The handle types in sqlx implement the same basic verbs for querying your database:</p>
<li><code>Exec(...) (sql.Result, error)</code> - unchanged from database/sql</li>
<li><code>Query(...) (*sql.Rows, error)</code> - unchanged from database/sql</li>
<li><code>QueryRow(...) *sql.Row</code> - unchanged from database/sql</li>
<p>These extensions to the built-in verbs:</p>
<li><code>MustExec() sql.Result</code> -- Exec, but panic on error</li>
<li><code>Queryx(...) (*sqlx.Rows, error)</code> - Query, but return an sqlx.Rows</li>
<li><code>QueryRowx(...) *sqlx.Row</code> -- QueryRow, but return an sqlx.Row</li>
<p>And these new semantics:</p>
<li><code>Get(dest interface{}, ...) error</code></li>
<li><code>Select(dest interface{}, ...) error</code></li>
<p>Let's go from the unchanged interface through the new semantics, explaining their use.</p>
<h3>Exec <a href="#exec" class="permalink" id="exec">&para;</a></h3>
<p>Exec and MustExec get a connection from the connection pool and executes the provided query on the server.  For drivers that do not support ad-hoc query execution, a prepared statement <em>may</em> be created behind the scenes to be executed.  The connection is returned to the pool before the result is returned.</p>
<pre class="prettyprint linenums">
schema := `CREATE TABLE place (
    country text,
    city text NULL,
    telcode integer);`
// execute a query on the server
result, err := db.Exec(schema)
// or, you can use MustExec, which panics on error
cityState := `INSERT INTO place (country, telcode) VALUES (?, ?)`
countryCity := `INSERT INTO place (country, city, telcode) VALUES (?, ?, ?)`
db.MustExec(cityState, "Hong Kong", 852)
db.MustExec(cityState, "Singapore", 65)
db.MustExec(countryCity, "South Africa", "Johannesburg", 27)
<p>The <a href="https://golang.org/pkg/database/sql/#Result">result</a> has two possible pieces of data: <code>LastInsertId()</code> or <code>RowsAffected()</code>, the availability of which is driver dependent.  In MySQL, for instance, <code>LastInsertId()</code> will be available on inserts with an auto-increment key, but in PostgreSQL, this information can only be retrieved from a normal row cursor by using the <code>RETURNING</code> clause.</p>
<h4>bindvars <a href="#bindvars" class="permalink" id="bindvars">&para;</a></h4>
<p>The <code>?</code> query placeholders, called <code>bindvars</code> internally, are important;  you should <em>always</em> use these to send values to the database, as they will prevent <a href="https://en.wikipedia.org/wiki/SQL_injection">SQL injection</a> attacks.  database/sql does not attempt <em>any</em> validation on the query text;  it is sent to the server as is, along with the encoded parameters.  Unless drivers implement a special interface, the query is prepared on the server first before execution.  Bindvars are therefore database specific:</p>
<li>MySQL uses the <code>?</code> variant shown above</li>
<li>PostgreSQL uses an enumerated <code>$1</code>, <code>$2</code>, etc bindvar syntax</li>
<li>SQLite accepts both <code>?</code> and <code>$1</code> syntax</li>
<li>Oracle uses a <code>:name</code> syntax</li>
<p>Other databases may vary.  You can use the <code>sqlx.DB.Rebind(string) string</code> function with the <code>?</code> bindvar syntax to get a query which is suitable for execution on your current database type.</p>
<p>A common misconception with bindvars is that they are used for interpolation.  They are only for <em>parameterization</em>, and are not allowed to <a href="https://use-the-index-luke.com/sql/where-clause/bind-parameters">change the structure of an SQL statement</a>.  For instance, using bindvars to try and parameterize column or table names will not work:</p>
<pre class="prettyprint linenums">
// doesn't work
db.Query("SELECT * FROM ?", "mytable")
// also doesn't work
db.Query("SELECT ?, ? FROM people", "name", "location")
<h3>Query <a href="#query" class="permalink" id="query">&para;</a></h3>
<p>Query is the primary way to run queries with database/sql that return row results.  Query returns an <code>sql.Rows</code> object and an error:</p>
<pre class="prettyprint linenums">
// fetch all places from the db
rows, err := db.Query("SELECT country, city, telcode FROM place")
// iterate over each row
for rows.Next() {
    var country string
    // note that city can be NULL, so we use the NullString type
    var city    sql.NullString
    var telcode int
    err = rows.Scan(&country, &city, &telcode)
// check the error from rows
err = rows.Err()
<p>You should treat the Rows like a database cursor rather than a materialized list of results.  Although driver buffering behavior can vary, iterating via <code>Next()</code> is a good way to bound the memory usage of large result sets, as you're only scanning a single row at a time.  <code>Scan()</code> uses <a href="https://golang.org/pkg/reflect">reflect</a> to map sql column return types to Go types like <code>string</code>, <code>[]byte</code>, et al.  If you do not iterate over a whole rows result, be sure to call <code>rows.Close()</code> to return the connection back to the pool!</p>
<p>The error returned by Query is any error that might have happened while preparing or executing on the server.  This can include grabbing a bad connection from the pool, although database/sql will <a href="https://golang.org/src/pkg/database/sql/sql.go?s=23888:23957#L885">retry 10 times</a> to attempt to find or create a working connection.  Generally, the error will be due to bad SQL syntax, type mismatches, or incorrect field and table names.</p>
<p>In most cases, Rows.Scan will copy the data it gets from the driver, as it is not aware of how the driver may reuse its buffers.  The special type <code>sql.RawBytes</code> can be used to get a <em>zero-copy</em> slice of bytes from the actual data returned by the driver.  After the next call to Next(), such a value is no longer valid, as that memory might have been overwritten by the driver.</p>
<p>The connection used by the Query remains active until <em>either</em> all rows are exhausted by the iteration via Next, or <code>rows.Close()</code> is called, at which point it is released.  For more information, see the section on <a href="#connectionPool">the connection pool</a>.</p>
<p>The sqlx extension <code>Queryx</code> behaves exactly as Query does, but returns an <code>sqlx.Rows</code>, which has extended scanning behaviors:</p>
<pre class="prettyprint linenums">
type Place struct {
    Country       string
    City          sql.NullString
    TelephoneCode int `db:"telcode"`
rows, err := db.Queryx("SELECT * FROM place")
for rows.Next() {
    var p Place
    err = rows.<a href="#advancedScanning">StructScan</a>(&p)
<p>The primary extension on sqlx.Rows is <code>StructScan()</code>, which automatically scans results into struct fields.  Note that the fields must be <a href="https://golang.org/doc/effective_go.html#names">exported</a> (capitalized) in order for sqlx to be able to write into them, something true of <em>all</em> marshallers in Go.  You can use the <code>db</code> struct tag to specify which column name maps to each struct field, or set a new default mapping with <a href="#mapping">db.MapperFunc()</a>.  The default behavior is to use <code>strings.Lower</code> on the field name to match against the column names.  For more information about <code>StructScan</code>, <code>SliceScan</code>, and <code>MapScan</code>, see the <a href="#advancedScanning">section on advanced scanning</a>.</p>
<h3>QueryRow <a href="#queryrow" class="permalink" id="queryrow">&para;</a></h3>
<p>QueryRow fetches one row from the server.  It takes a connection from the connection pool and executes the query using Query, returning a <code>Row</code> object which has its own internal Rows object:</p>
<pre class="prettyprint linenums">
row := db.QueryRow("SELECT * FROM place WHERE telcode=?", 852)
var telcode int
err = row.Scan(&telcode)
<p>Unlike Query, QueryRow returns a Row type result with no error, making it safe to chain the Scan off of the return.  If there was an error executing the query, that error is returned by Scan.  If there are no rows, Scan returns <code>sql.ErrNoRows</code>.  If the scan itself fails (eg. due to type mismatch), that error is also returned.</p>
<p>The Rows struct internal to the Row result is Closed upon Scan, meaning that the connection used by QueryRow is kept open until the result is scanned.  It also means that <code>sql.RawBytes</code> is not usable here, since the referenced memory belongs to the driver and may already be invalid by the time control is returned to the caller.</p>
<p>The sqlx extension <code>QueryRowx</code> will return an sqlx.Row instead of an sql.Row, and it implements the same scanning extensions as Rows, outlined above and in the <a href="#advancedScanning">advanced scanning section</a>:</p>
<pre class="prettyprint linenums">
var p Place
err := db.QueryRowx("SELECT city, telcode FROM place LIMIT 1").StructScan(&p)
<h3>Get and Select <a class="permalink" href="#getAndSelect" id="getAndSelect">&para;</a></h3>
<p><code>Get</code> and <code>Select</code> are time saving extensions to the handle types.  They combine the execution of a query with flexible scanning semantics.  To explain them clearly, we have to talk about what it means to be <code>scannable</code>:</p>
<li>a value is scannable if it is not a struct, eg <code>string</code>, <code>int</code></li>
<li>a value is scannable if it implements <code>sql.Scanner</code></li>
<li>a value is scannable if it is a struct with no exported fields (eg. <code>time.Time</code>)</li>
<p><code>Get</code> and <code>Select</code> use <code>rows.Scan</code> on scannable types and <code>rows.StructScan</code> on non-scannable types.  They are roughly analagous to <code>QueryRow</code> and <code>Query</code>, where Get is useful for fetching a single result and scanning it, and Select is useful for fetching a slice of results:</p>
<pre class="prettyprint linenums">
p := Place{}
pp := []Place{}
// this will pull the first place directly into p
err = db.Get(&p, "SELECT * FROM place LIMIT 1")
// this will pull places with telcode > 50 into the slice pp
err = db.Select(&pp, "SELECT * FROM place WHERE telcode > ?", 50)
// they work with regular types as well
err = db.Get(&id, "SELECT count(*) FROM place")
// fetch at most 10 place names
var names []string
err = db.Select(&names, "SELECT name FROM place LIMIT 10")
<p>Get and Select both will close the Rows they create during query execution, and will return any error encountered at any step of the process.  Since they use StructScan internally, the details in the <a href="#advancedScanning">advanced scanning section</a> also apply to Get and Select.</p>
<p>Select can save you a lot of typing, but beware!  It's semantically different from <code>Queryx</code>, since it will load the entire result set into memory at once.  If that set is not bounded by your query to some reasonable size, it might be best to use the classic Queryx/StructScan iteration instead.</p>
<h2>Transactions <a href="#transactions" class="permalink" id="transactions">&para;</a></h2>
<p>To use transactions, you must create a transaction handle with <code>DB.Begin()</code>.  Code like this <strong>will not work</strong>:</p>
<pre class="prettyprint linenums">
// this will not work if connection pool > 1
db.MustExec("BEGIN;")
db.MustExec(...)
db.MustExec("COMMIT;")
<p>Remember, Exec and all other query verbs will ask the DB for a connection and then return it to the pool each time.  There's no guarantee that you will receive the same connection that the BEGIN statement was executed on.  To use transactions, you must therefore use <code>DB.Begin()</code></p>
<pre class="prettyprint linenums">
tx, err := db.Begin()
err = tx.Exec(...)
err = tx.Commit()
<p>The DB handle also has the extensions <code>Beginx()</code> and <code>MustBegin()</code>, which return an <code>sqlx.Tx</code> instead of an <code>sql.Tx</code>:</p>
<pre class="prettyprint linenums">
tx := db.MustBegin()
tx.MustExec(...)
err = tx.Commit()
<p><code>sqlx.Tx</code> has all of the handle extensions that <code>sqlx.DB</code> has.</p>
<p>Since transactions are connection state, the Tx object must bind and control a single connection from the pool.  A Tx will maintain that single connection for its entire life cycle, releasing it only when <code>Commit()</code> or <code>Rollback()</code> is called.  You should take care to call at least one of these, or else the connection will be held until garbage collection.</p>
<p>Because you only have one connection to use in a transaction, you can only execute one statement at a time;  the cursor types Row and Rows must be Scanned or Closed, respectively, before executing another query.  If you attempt to send the server data while it is sending you a result, it can potentially corrupt the connection.</p>
<p>Finally, Tx objects do not actually imply any behavior on the server;  they merely execute a BEGIN statement and bind a single connection.  The actual behavior of the transaction, including things like locking and <a href="https://en.wikipedia.org/wiki/Isolation_(database_systems)">isolation</a>, is completely unspecified and database dependent.</p>
<h2>Prepared Statements <a href="#preparedStmts" class="permalink" id="preparedStmts">&para;</a></h2>
<p>On most databases, statements will actually be prepared behind the scenes whenever a query is executed.  However, you may also explicitly prepare statements for reuse elsewhere with <code>sqlx.DB.Prepare()</code>:</p>
<pre class="prettyprint linenums">
stmt, err := db.Prepare(`SELECT * FROM place WHERE telcode=?`)
row = stmt.QueryRow(65)
tx, err := db.Begin()
txStmt, err := tx.Prepare(`SELECT * FROM place WHERE telcode=?`)
row = txStmt.QueryRow(852)
<p>Prepare actually runs the preparation on the database, so it requires a connection and its connection state.  database/sql abstracts this from you, allowing you to execute from a single Stmt object concurrently on many connections by creating the statements on new connections automatically.  <code>Preparex()</code>, which returns an <code>sqlx.Stmt</code> which has all of the handle extensions that sqlx.DB and sqlx.Tx do:</p>
<pre class="prettyprint linenums">
stmt, err := db.Preparex(`SELECT * FROM place WHERE telcode=?`)
var p Place
err = stmt.Get(&p, 852)
<p>The standard sql.Tx object also has a <code>Stmt()</code> method which returns a transaction-specific statement from a pre-existing one.  sqlx.Tx has a <code>Stmtx</code> version which will create a new transaction specific <code>sqlx.Stmt</code> from an existing sql.Stmt <em>or</em> sqlx.Stmt.</p>
<h2>Query Helpers</h2>
<p>The <code>database/sql</code> package does not do anything with your actual query text.  This makes it trivial to use backend-specific features in your code;  you can write queries just as you would write them in your database prompt.  While this is very flexible, it makes writing certain kinds of queries difficult.</p>
<h3>"In" Queries <a href="#inQueries" class="permalink" id="inQueries">&para;</a></h3>
<p>Because <code>database/sql</code> does not inspect your query and it passes your arguments directly to the driver, it makes dealing with queries with IN clauses difficult:</p>
<pre class="prettyprint linenums">
SELECT * FROM users WHERE level IN (?);
<p>When this gets prepared as a statement on the backend, the bindvar <code>?</code> will only correspond to a <em>single</em> argument, but what is often desired is for that to be a variable number of arguments depending on the length of some slice, eg:</p>
<pre class="prettyprint linenums">
var levels = []int{4, 6, 7}
rows, err := db.Query("SELECT * FROM users WHERE level IN (?);", levels)
<p>This pattern is possible by first processing the query with <code>sqlx.In</code>:</p>
<pre class="prettyprint linenums">
var levels = []int{4, 6, 7}
query, args, err := sqlx.In("SELECT * FROM users WHERE level IN (?);", levels)
// sqlx.In returns queries with the `?` bindvar, we can rebind it for our backend
query = db.Rebind(query)
rows, err := db.Query(query, args...)
<p>What <code>sqlx.In</code> does is expand any bindvars in the query passed to it that correspond to a slice in the arguments to the length of that slice, and then append those slice elements to a new arglist.  It does this with the <code>?</code> bindvar only;  you can use <code>db.Rebind</code> to get a query suitable for your backend.</p>
<h3>Named Queries <a href="#namedParams" class="permalink" id="namedParams">&para;</a></h3>
<p>Named queries are common to many other database packages.  They allow you to use a bindvar syntax which refers to the names of struct fields or map keys to bind variables a query, rather than having to refer to everything positionally.  The struct field naming conventions follow that of <code>StructScan</code>, using the <code>NameMapper</code> and the <code>db</code> struct tag.  There are two extra query verbs related to named queries:</p>
<li><code>NamedQuery(...) (*sqlx.Rows, error)</code> - like Queryx, but with named bindvars</li>
<li><code>NamedExec(...) (sql.Result, error)</code> - like Exec, but with named bindvars</li>
<p>And one extra handle type:</p>
<li><code>NamedStmt</code> - an sqlx.Stmt which can be prepared with named bindvars</li>
<pre class="prettyprint linenums">
// named query with a struct
p := Place{Country: "South Africa"}
rows, err := db.NamedQuery(`SELECT * FROM place WHERE country=:country`, p)
// named query with a map
m := map[string]interface{}{"city": "Johannesburg"}
result, err := db.NamedExec(`SELECT * FROM place WHERE city=:city`, m)
<p>Named query execution and preparation works off both structs and maps.  If you desire the full set of query verbs, prepare a named statement and use that instead:</p>
<pre class="prettyprint linenums">
p := Place{TelephoneCode: 50}
pp := []Place{}
// select all telcodes > 50
nstmt, err := db.PrepareNamed(`SELECT * FROM place WHERE telcode > :telcode`)
err = nstmt.Select(&pp, p)
<p>Named query support is implemented by parsing the query for the <code>:param</code> syntax and replacing it with the bindvar supported by the underlying database, then performing the mapping at execution, so it is usable on any database that sqlx supports.  You can also use <code>sqlx.Named</code>, which uses the <code>?</code> bindvar, and can be composed with <code>sqlx.In</code>:</p>
<pre class="prettyprint linenums">
arg := map[string]interface{}{
    "published": true,
    "authors": []{8, 19, 32, 44},
query, args, err := sqlx.Named("SELECT * FROM articles WHERE published=:published AND author_id IN (:authors)", arg)
query, args, err := sqlx.In(query, args...)
query = db.Rebind(query)
db.Query(query, args...)
<h2>Advanced Scanning <a href="#advancedScanning" class="permalink" id="advancedScanning">&para;</a></h2>
<p><code>StructScan</code> is deceptively sophisticated.  It supports embedded structs, and assigns to fields using the same precedence rules that Go uses for embedded attribute and method access.  A common use of this is sharing common parts of a table model among many tables, eg:</p>
<pre class="prettyprint linenums">
type AutoIncr struct {
    ID       uint64
    Created  time.Time
type Place struct {
    Address string
    AutoIncr
type Person struct {
    Name string
    AutoIncr
<p>With the structs above, Person and Place will both be able to receive <code>id</code> and <code>created</code> columns from a StructScan, because they embed the <code>AutoIncr</code> struct which defines them.  This feature can enable you to quickly create an ad-hoc table for joins.  It works recursively as well;  the following will have the Person's Name and its AutoIncr ID and Created fields accessible, both via the Go dot operator and via StructScan:</p>
<pre class="prettyprint linenums">
type Employee struct {
    BossID uint64
    EmployeeID uint64
<p>Note that sqlx historically supported this feature for non-embedded structs, this ended up being confusing because users were using this feature to define relationships and embedding the same structs twice:</p>
<pre class="prettyprint linenums">
type Child struct {
    Father Person
    Mother Person
<p>This causes some problems.  In Go, it's legal to shadow descendent fields;  if Employee from the embedded example defined a <code>Name</code>, it would take precedence over the Person's Name.  But <em>ambiguous</em> selectors are illegal and cause <a href="https://play.golang.org/p/MGRxdjLaUc">a runtime error</a>.  If we wanted to create a quick JOIN type for Person and Place, where would we put the <code>id</code> column, which is defined in both via their embedded AutoIncr?  Would there be an error?</p>
<p>Because of the way that sqlx builds the mapping of field name to field address, by the time you Scan into a struct, it no longer knows whether or not a name was encountered twice during its traversal of the struct tree.  So unlike Go, StructScan will choose the "first" field encountered which has that name.  Since Go struct fields are ordered from top to bottom, and sqlx does a breadth-first traversal in order to maintain precedence rules, it would happen in the shallowest, top-most definition.  For example, in the type:</p>
<pre class="prettyprint linenums">
type PersonPlace struct {
<p>A StructScan will set an <code>id</code> column result in <code>Person.AutoIncr.ID</code>, also accessible as <code>Person.ID</code>.  To avoid confusion, it's suggested that you use <code>AS</code> to create column aliases in your SQL instead.</p>
<h3>Scan Destination Safety <a href="#safety" class="permalink" id="safety">&para;</a></h3>
<p>By default, StructScan will return an error if a column does not map to a field in the destination.  This mimics the treatment for things like unused variables in Go, but does <em>not</em> match the way that standard library marshallers like <code>encoding/json</code> behave.  Because SQL is generally executed in a more controlled fashion than parsing JSON, and these errors are generally coding errors, a decision was made to return errors by default.</p>
<p>Like unused variables, columns which you ignore are a waste of network and database resources, and detecting things like an incompatible mapping or a typo in a struct tag early can be difficult without the mapper letting you know something wasn't found.</p>
<p>Despite this, there are some cases where ignoring columns with no destination might be desired.  For this, there is the <code>Unsafe</code> method on each <a href="#handles">Handle type</a> which returns a new copy of that handle with this safety turned off:</p>
<pre class="prettyprint linenums">
var p Person
// err here is not nil because there are no field destinations for columns in `place`
err = db.Get(&p, "SELECT * FROM person, place LIMIT 1;")
// this will NOT return an error, even though place columns have no destination
udb := db.Unsafe()
err = udb.Get(&p, "SELECT * FROM person, place LIMIT 1;")
<h3>Controlling Name Mapping <a href="#mapping" class="permalink" id="mapping">&para;</a></h3>
<p>Struct fields used as targets for StructScans <em>must</em> be capitalized in order to be accessible by sqlx. Because of this, sqlx uses a <em>NameMapper</em> which applies <code>strings.ToLower</code> to field names to map them to columns in your rows result.  This isn't always desirable, depending on your schema, so sqlx allows the mapping to be customized a number of ways.</p>
<p>The simplest of these ways is to set it for a db handle by using <code>sqlx.DB.MapperFunc</code>, which receives an argument of type <code>func(string) string</code>.  If your library requires a particular mapper, and you don't want to poison the <code>sqlx.DB</code> you receive, you can create a copy for use in the library to ensure a particular default mapping:</p>
<pre class="prettyprint linenums">
// if our db schema uses ALLCAPS columns, we can use normal fields
db.MapperFunc(strings.ToUpper)
// suppose a library uses lowercase columns, we can create a copy
copy := sqlx.NewDb(db.DB, db.DriverName())
copy.MapperFunc(strings.ToLower)
<p>Each <code>sqlx.DB</code> uses the <code>sqlx/reflectx</code> package's <a href="https://godoc.org/github.com/jmoiron/sqlx/reflectx#Mapper">Mapper</a> to achieve this mapping underneath, and exposes the active mapper as <code>sqlx.DB.Mapper</code>.  You can further customize the mapping on a DB by setting it directly:</p>
<pre class="prettyprint linenums">
import "github.com/jmoiron/sqlx/reflectx"
// Create a new mapper which will use the struct field tag "json" instead of "db"
db.Mapper = reflectx.NewMapperFunc("json", strings.ToLower)
<h3>Alternate Scan Types <a href="#altScanning" class="permalink" id="altScanning">&para;</a></h3>
<p>In addition to using Scan and StructScan, an sqlx Row or Rows can be used to automatically return a slice or a map of results:</p>
<pre class="prettyprint linenums">
rows, err := db.Queryx("SELECT * FROM place")
for rows.Next() {
    // cols is an []interface{} of all of the column results
    cols, err := rows.SliceScan()
rows, err := db.Queryx("SELECT * FROM place")
for rows.Next() {
    results := make(map[string]interface{})
    err = rows.MapScan(results)
<p>SliceScan returns an <code>[]interface{}</code> of all columns, which can be useful in <a href="http://wts.jmoiron.net">situations</a> where you are executing queries on behalf of a third party and have no way of knowing what columns may be returned.  MapScan behaves the same way, but maps the column names to interface{} values.  An important caveat here is that the results returned by <code>rows.Columns()</code> does not include fully qualified names, such that <code>SELECT a.id, b.id FROM a NATURAL JOIN b</code> will result in a Columns result of <code>[]string{"id", "id"}</code>, clobbering one of the results in your map.</p>
<h2>Custom Types <a href="#customTypes" class="permalink" id="customTypes">&para;</a></h2>
<p>The examples above all used the built-in types to both scan and query with, but database/sql provides interfaces to allow you to use any custom types:</p>
<li><code>sql.Scanner</code> allows you to use custom types in a Scan()</li>
<li><code>driver.Valuer</code> allows you to use custom types in a Query/QueryRow/Exec</li>
<p>These are the standard interfaces, and using them will ensure portability to any library that might be providing services on top of database/sql.  For a detailed look at how to use them, <a href="http://jmoiron.net/blog/built-in-interfaces">read this blog post</a> or check out the <a href="https://github.com/jmoiron/sqlx/blob/master/types/types.go">sqlx/types</a> package, which implements a few standard useful types.</p>
<h2>The Connection Pool <a href="#connectionPool" class="permalink" id="connectionPool">&para;</a></h2>
<p>Statement preparation and query execution require a connection, and the DB object will manage a pool of them so that it can be safely used for concurrent querying.  There are two ways to control the size of the connection pool as of Go 1.2:</p>
<li><code>DB.SetMaxIdleConns(n int)</code></li>
<li><code>DB.SetMaxOpenConns(n int)</code></li>
<p>By default, the pool grows unbounded, and connections will be created whenever there isn't a free connection available in the pool.  You can use <code>DB.SetMaxOpenConns</code> to set the maximum size of the pool.  Connections that are not being used are marked idle and then closed if they aren't required.  To avoid making and closing lots of connections, set the maximum idle size with <code>DB.SetMaxIdleConns</code> to a size that is sensible for your query loads.</p>
<p>It is easy to get into trouble by accidentally holding on to connections.  To prevent this:</p>
<li>Ensure you <code>Scan()</code> every Row object</li>
<li>Ensure you either <code>Close()</code> or fully-iterate via <code>Next()</code> every Rows object</li>
<li>Ensure every transaction returns its connection via <code>Commit()</code> or <code>Rollback()</code></li>
<p>If you neglect to do one of these things, the connections they use may be held until garbage collection, and your db will end up creating far more connections at once in order to compensate for the ones its using.  Note that <code>Rows.Close()</code> can be called multiple times safely, so do not fear calling it where it might not be necessary.</p>
    </body>
Provide feedback

Saved searches

Use saved searches to filter your results more quickly

FilesExpand file tree

index.html

Latest commit

History

index.html

File metadata and controls
