Golang database Development Artifact SQLX User Guide

21-day Boutique blockchain free learning, in-depth expert led the way, to help developers easily play the blockchain! >>>

SQLX User Guide

This article is mainly based on illustrated guide to SQLX translation.
SQLX is a Go language pack that adds a lot of extensions to the built-in Database/sql package, simplifying the writing of database operation codes.

Resources

If you are unfamiliar with the SQL usage of the Go language, you can learn from the following websites:
Database/sql Documentation
Go-database-sql Tutorial

If you are unfamiliar with Golang language, you can learn from the following websites:
The Go Tour
How to write Go code
Effective Go
CSDN

Since the Database/sql interface is a subset of SQLX, all usage of database/sql in the current document is also used for SQLX

Begin

Installing the SQLX Drive

$ go get github.com/jmoiron/sqlx

This article accesses the SQLite database

$ go get github.com/mattn/go-sqlite3

Handle Types

SQLX design and database/sql use the same way. Contains 4 of the main handle types:
-SQLX. DB-and SQL. DB similar to that of the database.
-SQLX. Tx-and SQL. TX is similar, indicating transacion.
-SQLX. STMT-and SQL. The stmt is similar, indicating prepared statement.
-SQLX. NAMEDSTMT-Represents prepared statement (supports named parameters)

All handler types provide compatibility with Database/sql , meaning when you call SQLX. Db. When you query, you can replace it directly with SQL. Db. Query. This makes it easy for SQLX to be added to existing database projects.

In addition, SQLX has two cursor types:
-SQLX. Rows-and SQL. Rows similar to Queryx returned.
-SQLX. Row-and SQL. Similar to row, QUERYROWX returns.

Cascade to Database

A DB instance is not a link, but an abstraction represents a database. This is why the creation of a DB does not return errors and panic. It maintains a connection pool internally and attempts to connect when a connection is required. You can create a SQLX by using Open . DB or pass newdb from an existing SQL. Create a new sqlx.db in DB

var db *sqlx.DB// exactly the same as the built-indb = sqlx.Open("sqlite3", ":memory:")// from a pre-existing sql.DB; note the required driverNamedb = sqlx.NewDb(sql.Open("sqlite3", ":memory:"), "sqlite3")// force a connection and test that it workederr = db.Ping()

In some environments, you may need to open a DB and link at the same time. You can call connect, this function opens a new DB and tries to Ping. The mustconnect function is panic when a link error occurs.

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 errordb = sqlx.MustConnect("sqlite3", ":memory:")

Querying 101

The handle types in SQLX implements the same basic operational syntax for database queries.
-Exec (...) (SQL. Result, error)-no change compared to Database/sql
-Query (...) (*sql. Rows, error)-no change compared to Database/sql
-Queryrow (...) *sql. Row-no change compared to Database/sql

Extensions to built-in syntax
-Mustexec () SQL. Result–exec, but panic on error
-Queryx (...) (*SQLX. Rows, error)-Query, but return an SQLX. Rows
-Queryrowx (...) *sqlx. Row–queryrow, but return to an SQLX. Row

And here's the new syntax.

• Get (dest interface{}, ...) error
• Select (dest interface{}, ...) error
  The use of these methods is described in more detail below

Exec

EXEC and Mustexec get a connection from the connection pool and then just want the corresponding query action. For drivers that do not support ad-hoc query execution , a prepared statementis created behind the action execution. This connection is returned to the connection pool before the result is returned.

schema := `CREATE TABLE place (    country text,    city text NULL,    telcode integer);`// execute a query on the serverresult, err := db.Exec(schema)// or, you can use MustExec, which panics on errorcityState := `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)

Result in the above code has two possible data Lastinsertid () or rowsaffected (), depending on the driver.
In MySQL, performing an insert operation on a table containing auto-increment key will get Lastinsertid (), and in PostgreSQL This information is only used The returning statement is not returned in the row cursor.

Bindvars

In the code ? placeholders, called Bindvars, are important, and you can always use them to send data to a database that can be used to organize SQL injection attacks.
Database/sql does not perform any validation on the query statement, and what is sent to the server is what is passed in.
Unless driver implements a specific interface, query is ready before the database executes. The bindvars of different databases is not the same.
-MySQL use ?
-PostgreSQL uses 1,1,2 and so on
-SQLite use ? or $
-Oracle Use : Name

Other databases may not be the same. You can use sqlx. Db. Rebind (String) string function is used? syntax to get a query statement that is appropriate for execution on the current database.

The common misconception about bindvars is that they are used to interpolate values. They are only used for parameterization and do not allow changes to the legitimate interface of SQL statements. For example, the following usage will be an error

// doesn't workdb.Query("SELECT * FROM ?", "mytable")// also doesn't workdb.Query("SELECT ?, ? FROM people", "name", "location")

Query

Query is the primary method for executing queries in Database/sql, which returns a row result. Query returns a SQL. Rows object and an error object.

// fetch all places from the dbrows, err := db.Query("SELECT country, city, telcode FROM place")// iterate over each rowfor 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)}

You should use it. Rows is treated as a cursor rather than a series of results. Although the database-driven caching method is different, each time a column of results is obtained through next () iteration, it is possible to effectively limit the use of memory for very large query results,Scan () Use reflect to map the results of each column of SQL to the data type of the go language, such as String,[]byte. If you are not traversing the rows of the complete section, be sure to call rows before returning the connection to the connection pool . Close ().

The error returned by query may occur when the server prepares the query, or it may occur when the query is executed. For example, you might get a bad level of connectivity from the connection pool (although the database tries to discover or create a working connection 10 times). In general, errors are mainly caused by incorrect SQL statements, similar errors, incorrect domain names or table names, and so on.

In most cases,Rows.scan () copies the data obtained from the drive, regardless of how the cache is driven. Special type of SQL. Rawbytes can be used to get a zero-copy slice byte from the data returned from the drive. The next time a call is made, this value is not valid because the memory it points to has been driven to rewrite the other data.

Query uses connection after all rows have been traversed through Next () or rows are called . Released after Close () .
Queryx is similar to Query behavior, but returns a sqlx. Rows object, which supports extended scan behavior.

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.StructScan(&p)}

SQLX. the main extension of ROWX is Structscan, which can automatically scan the results to the domain in the corresponding structure (Fileld). Note that the domain (field) in the struct must be exportable (exported) so that the SQLX can write the value into the struct.
As shown in the preceding code, you can use the db struct tag to specify that the struct field maps to a specific column name in the database, or in db. Mapperfunc () to specify the default mappings. DB defaults to the filed name of the struct to perform the strings. After Lower , match the column names of the database. For more detailed information on Structscan,slicescan,mapscan , see the later section Advanced scanning

Queryrow

queryrow Gets a column of data from the database server. It gets a concatenated level from the connection pool, and then executes query, returning a Row object that has its own internal Rows object.

row := db.QueryRow("SELECT * FROM place WHERE telcode=?", 852)var telcode interr = row.Scan(&telcode)

Unlike query,queryrow returns only a row type, does not return an error, and if an error occurs during the execution of the query, it is returned via Scan , and SQL is returned if the query result is empty . Errnorows. If the scan itself is faulted, the error is also returned by scan.

The connection used by Queryrow When result is returned is closed, which means that SQL cannot be used when using Queryrow . Rawbyes, because driver uses SQL. Rawbytes references memory and may also be invalid after connection is reclaimed.

queryrowx Returns a sqlx. Row instead of SQL. Row, which implements the same scan method as Rows , as well as the advanced scan method as follows: (more advanced scan method scanningsection)

var p Placeerr := db.QueryRowx("SELECT city, telcode FROM place LIMIT 1").StructScan(&p)

Get and Select

Get and Select are a very time-saving extension. They combine query with a very flexible scan syntax. To introduce them more clearly, let's discuss what is scannalbe:

• A value is scannable if it's not a struct, eg string, int
• A value is scannable if it implements SQL. Scanner
• A value is scannable if it's a struct with no exported fields (eg. time). Time)

Get and Select use rows.scanfor the type of scannable, and rows for non-scannable types . Structscan. get is used to get a single result and then scan,Select to get the result slice.

p := Place{}pp := []Place{}// this will pull the first place directly into perr = db.Get(&p, "SELECT * FROM place LIMIT 1")// this will pull places with telcode > 50 into the slice pperr = db.Select(&pp, "SELECT * FROM place WHERE telcode > ?", 50)// they work with regular types as wellvar id interr = db.Get(&id, "SELECT count(*) FROM place")// fetch at most 10 place namesvar names []stringerr = db.Select(&names, "SELECT name FROM place LIMIT 10")

get and Select close rows after executing the query and return an error if any problems are encountered during the execution phase. Because of their internal use of Structscan, the features described in the Advanced scanning section below also apply to get and select.

Select can improve the coding path, but note that select and Queryx are very different, because select puts the entire result into memory at once. If the query results are not limited to a specific size, it is best to use the Query/structscan iterative approach.

Transactions

In order to use transactions, you must use DB. Begin () to create, the following code is wrong :

db.MustExec("BEGIN;")db.MustExec(...)db.MustExec("COMMIT;")

The EXEC and other query statements request a connection to the DB, return to the connection pool after execution, and do not guarantee that the connection is the one used at the Begin execution, so the correct practice is to use Db.begin:

tx, err := db.Begin()err = tx.Exec(...)err = tx.Commit()

In addition to the begin, DB can also return SQLX using Extended BeginX () and mustbegin () . Tx:

tx := db.MustBegin()tx.MustExec(...)err = tx.Commit()

SQLX. Tx has sqlx. DB has all of the handle extensions.
Because transaction is a connection state, the Tx object must bind and control a single connection. A Tx saves a connection throughout its lifecycle and then releases it when it calls commit or Rollback () . You must be very careful when calling these functions, otherwise the connection will always be occupied until garbage collection.
Because there can be only one connection in a transaction , only one statement can be executed at a time. The cursor object Row* and Rows must be scanned or Closedbefore other query operations are performed. If you try to send data to the database when the database returns you data, this operation may interrupt connection.

Finally, the TX object simply executes a BEGIN statement and binds a connection, which does not actually perform any operations on the server. The real behavior of transaction consists of locking and isolation, which are different on different databases.

Prepared statements

For most databases, when a query executes, the statements inside the database is actually ready. Then you can pass the sqlx. Db. Prepare () prepares the statements for later use in other places.

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)

Prepare actually performs the preparation operation on the database, so it needs a connection and its connection state.
Database/sql abstracts This part and automatically creates statement on the new connection, so that developers can perform operations concurrently on multiple connection through stmt objects.
Preparex () returns a SQLX. The stmt object that contains the SQLX. DB and Sqlx.tx all handle extensions (methods).

SQL. The Tx object contains a Stmt () method that returns a statement that is specific to the transaction in the existing statement.
SQLX. Tx also contains a stmtx () method, from an existing SQL. Create a transaction-specific sqlx.stmt in Stmt or sqlx.stmt.

Query Helpers

"In" Queries

Because database/sql does not parse your query statements and then pass parameters directly to driver, for in