Go Web programming database

An overview of the

Databases are used to store data. Every project that is not a toy project needs a database. MySQL is still the most popular, and PostgreSQL is growing fast. A database is also a must in Web development. This article will show you how to operate a database in the Go language, based on MySQL. This article assumes that you already know the basics of databases and MySQL. There is a very detailed free tutorial on MySQL that I have included in the reference.

Database/SQL, the Go language standard library, provides only a set of interfaces for querying and manipulating databases, without any implementation. You can only use third-party libraries to manipulate databases in Go. There are third-party libraries for each type of database. The most common mysql-enabled driver in Go is go-SQL-driver/MySQL. The library supports database/ SQL, all using GO implementation.

Database operations

The preparatory work

Create a database department that represents a department in the company. Create two tables employees and Teams in the library. Employees Records employee information. Teams Records team information. Each employee belongs to a team, and each team has several employees.

SET NAMES utf8mb4; CREATE DATABASE IF NOT EXISTS `department` CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; USE `department`; CREATE TABLE IF NOT EXISTS `employees` ( `id` INT(11) AUTO_INCREMENT PRIMARY KEY, `name` VARCHAR(255) NOT NULL DEFAULT '', `age` INT(11) NOT NULL DEFAULT 0, `salary` INT(11) NOT NULL DEFAULT 0, `team_id` INT(11) NOT NULL DEFAULT 0 ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS `teams` ( `id` INT(11) AUTO_INCREMENT PRIMARY KEY, `name` VARCHAR(255) NOT NULL DEFAULT '' ) ENGINE=InnoDB; INSERT INTO ` teams ` (` name `) VALUES (' planning '), (' development '), (' operating '), (' operations'); INSERT INTO ` employees ` (` name `, ` age `, ` salary `, ` team_id `) VALUES (' zhang SAN, 28, 1200, 1), (' li si, 38, 4000, 1), (' Cathy ', 36, 3500, 1), (' six zhao, 31, 3100, 2), (' notoginseng, 29, 2900, 2), (' eight wu, 27, 1500, 3), (' zhu ', 26, 1600, 3), (' money 10, 27, 1800, 3), Eleven (' tao ', 28, 1900, 4), (' wang 12, 25, 2000, 4), (' sword 13, 24, 30000, 4);Copy the code

Insert some test data. Save the department.sql file to a directory and open the command line there:

$ mysql -u root -p

Copy the code

Enter a password to connect to the database, then enter the following command:

mysql> sourceDepartment. SQL Query OK, 0 rows affected (0.00 SEC) Query OK, 2 rows affected (0.02 SEC) Query OK, 1 row affected (0.00 SEC) Database changed Query OK, 0 rows affected, 4 warnings (0.02 SEC) Query OK, 0 rows affected, 1 Warning (0.02 SEC) Query OK, 4 rows Affected (0.01 SEC) Records: 4 Duplicates: 0 Warnings: 0 Query OK, 11 rows affected (0.00 SEC) Records: 11 Duplicates: 0 Warnings: 0 mysql>Copy the code

The database and tables are now created.

Connecting to a Database

Go-sql-driver /mysql is a third-party library that needs to be installed:

$ go get github.com/go-sql-driver/mysql
Copy the code

Use:

package main

import (
  "database/sql"
  "log"

  _ "github.com/go-sql-driver/mysql"
)

func main(a) {
  db, err := sql.Open("mysql"."Root: 12345 @ TCP (127.0.0.1:3306)/department")
  iferr ! =nil {
    log.Fatal("connect database failed: ", err)
  }
  defer db.Close()
}
Copy the code

We do not operate the database directly using the mysql library, but through the Database/SQL interface.

import _ "github.com/go-sql-driver/mysql"
Copy the code

Mysql driver registered with database/ SQL; mysql driver registered with database/ SQL;

// go-sql-driver/mysql/driver.go
func init(a) {
  sql.Register("mysql", &MySQLDriver{})
}
Copy the code

SQL > create a SQL.DB structure with sql.Open. Parameter 1 is the name of the mysql library, and parameter 2 is the database connection information. Each database accepts different connection information. For MySQL, connection information is actually a DSN (Data Source Name). The general format of DSN is:

[username[:password]@][protocol[(address)]]/dbname[?param1=value1&...&paramN=valueN]
Copy the code

This example uses a DSN, specifying user name root, password 12345, and TCP to connect to MySQL’s Department database with IP 127.0.0.1 and port 3306.

After use, you need to call db.close to Close sqL.db.

** It is important to note that SQL.open does not establish a connection to the database, nor does it detect driver connection parameters. It simply creates a database abstraction layer for later use. Connections to the database are actually lazily created as needed. ** So, we use an invalid username or password to connect to a library that does not exist on the host, and sql.Open will not report an error. Change the DSN above to user:password@tcp(127.0.0.1:6666)/not_exist_department, run the program without error.

If you want to check whether the database is accessible, you can use the db.ping () function:

err = db.Ping()
iferr ! =nil {
  log.Fatal("ping failed: ", err)
}
Copy the code

The not_exist_department connection will fail:

2020/01/20 22:16:12 ping failed: Error 1049: Unknown database 'not_exist_department'
exit status 1
Copy the code

Sql. DB objects generally live as global variables of some sort. Do not open and close the object frequently. This can have a significant impact on performance.

The query

Let’s start with a simple example:

package main

import (
  "database/sql"
  "log"

  _ "github.com/go-sql-driver/mysql"
)

func main(a) {
  db, err := sql.Open("mysql"."Root: 12345 @ TCP (127.0.0.1:3306)/department")
  iferr ! =nil {
    log.Fatal("open database failed: ", err)
  }
  defer db.Close()

  var id int
  var name string
  var age int
  var salary int
  var teamId int

  rows, err := db.Query("select id, name, age, salary, team_id from employees where id = ?".1)
  iferr ! =nil {
    log.Fatal("query failed: ", err)
  }
  defer rows.Close()

  for rows.Next() {
    err := rows.Scan(&id, &name, &age, &salary, &teamId)
    iferr ! =nil {
      log.Fatal("scan failed: ", err)
    }
    log.Printf("id: %d name:%s age:%d salary:%d teamId:%d\n", id, name, age, salary, teamId)
  }

  err = rows.Err()
  iferr ! =nil {
    log.Fatal(err)
  }
}
Copy the code

Run the program, output:

2020/01/20 22:27:21 ID :1 Name: Zhang SAN Age :28 Salary :1200 teamId:1Copy the code

From the above program, we can see the basic flow of a query operation:

• usedb.Query()Query database;
• Iterating through the returned rows in the loop,rows.Scan()Read the values of each column,rows.Next()Moves pointer to the next line;
• When I’ve gone through all the rows,rows.Next()Will return false and the loop exits.

Database operations can encounter various errors, so error handling is important. For example, calling rows.scan in a loop might produce an error.

Be sure to close Rows when you finish traversing. Because it holds a pointer to the connection, leaving it open can cause resource leakage. Rows.next () returns an EOF error when it encounters the last line and closes the connection. Also, if rows.next () returns false due to an error, rows will be closed automatically. In other cases, if you exit the loop early, you might forget to close rows. Instead, defer rows.close () to ensure normal closure.

Tips:

When the Scan method is called, the corresponding data type conversion is performed internally based on the parameter type passed in. Take advantage of this feature to simplify your code. For example, a column in MySQL is a VARCHAR/CHAR or similar text type, but we know that it holds an integer. We can then pass in a variable of type int, which inside Scan will help us convert the string to int. Saves us the trouble of manually calling strconv-related methods.

Database/SQL functions are specifically named:

• Query*This kind of toQueryA function at the beginning, which must return several (possibly zero) rows of data;
• Cannot be used if a statement does not return row dataQuery*Function, should be usedExec.

Prepare

When we need to execute the same statement more than once, it is best to create a PreparedStatement first. The PreparedStatement can contain parameter placeholders that will be supplied later in the execution.

Each database has its own parameter placeholder, MySQL uses? . Using parameter placeholders has one obvious benefit: you can avoid SQL injection attacks.

To execute SQL, call the Query method in PreparedStatement with the passed parameter:

func main(a) {
  db, err := sql.Open("mysql"."Root: 12345 @ TCP (127.0.0.1:3306)/department")
  iferr ! =nil {
    log.Fatal("open failed: ", err)
  }
  defer db.Close()

  stmt, err := db.Prepare("select id, name, age, salary from employees where id = ?")
  iferr ! =nil {
    log.Fatal("prepare failed: ", err)
  }
  defer stmt.Close()

  rows, err := stmt.Query(2)
  iferr ! =nil {
    log.Fatal("query failed: ", err)
  }
  defer rows.Close()

  var (
    id int
    name string
    age int
    salary int
  )
  for rows.Next() {
    err := rows.Scan(&id, &name, &age, &salary)
    iferr ! =nil {
      log.Fatal("scan failed: ", err)
    }
    log.Printf("id:%d name:%s age:%d salary:%d\n", id, name, age, salary)
  }

  err = rows.Err()
  iferr ! =nil {
    log.Fatal(err)
  }
}
Copy the code

In fact, inside the db.query () function, a PreparedStatement is created, executed, and then closed. This generates three communications with the database. Try to create a PreparedStatement before using it.

A single query

Instead of writing a loop if the query returns at most one row, using QueryRow can simplify code writing.

Call db.QueryRow directly:

var name string
err = db.QueryRow("select name from employees where id = ?".1).Scan(&name)
iferr ! =nil {
  log.Fatal(err)
}
fmt.Println(name)
Copy the code

QueryRow can also be called from a PreparedStatement:

stmt, err := db.Prepare("select name from employees where id = ?").Scan(&name)
iferr ! =nil {
  log.Fatal(err)
}
defer stmt.Close()
var name string
err = stmt.QueryRow(1).Scan(&name)
iferr ! =nil {
  log.Fatal(err)
}
fmt.Println(name)
Copy the code

Pay attention to,QueryRowErrors encountered are deferred until calledScanWill return.

Insert/modify/delete

INSERT/UPDATE/DELETE operations, since they do not return rows, should use the Exec function. You are advised to create a PreparedStatement before executing it.

Now the “planning team” has a new employee:

func main(a) {
  db, err := sql.Open("mysql"."Root: 12345 @ TCP (127.0.0.1:3306)/department")
  iferr ! =nil {
    log.Fatal("open failed: ", err)
  }
  defer db.Close()

  stmt, err := db.Prepare("INSERT INTO employees(name, age, salary, team_id) VALUES(? ,? ,? ,?) ")
  iferr ! =nil {
    log.Fatal("prepare failed: ", err)
  }
  defer stmt.Close()

  res, err := stmt.Exec("Liu Shi".32.5000.1)
  iferr ! =nil {
    log.Fatal("exec failed: ", err)
  }
  lastId, err := res.LastInsertId()
  iferr ! =nil {
    log.Fatal("fetch last insert id failed: ", err)
  }
  rowCnt, err := res.RowsAffected()
  iferr ! =nil {
    log.Fatal("fetch rows affected failed: ", err)
  }
  log.Printf("ID = %d, affected = %d\n", lastId, rowCnt)
}
Copy the code

The Exec method returns a value of the sqL. Result interface type:

// src/database/sql/sql.go
type Result interface {
  LastInsertId() (int64, error)
  RowsAffected() (int64, error)
}
Copy the code

Some tables have a self-increasing ID, so you do not need to set the ID when inserting, and the database automatically generates a return. LastInsertId() returns the ID generated when it was inserted. RowsAffected() returns the number of RowsAffected.

Run the program, output:

2020/01/21 07:20:26 ID = 12, affected = 1
Copy the code

The transaction

In Go, a transaction is essentially an object that holds a connection to a database. This same connection is used when executing the methods we described above through this object. Call db.begin () to create a transaction object, execute the above methods on that object, and finally call Commit() on success and Rollback() on failure to close the transaction.

func main(a) {
  db, err := sql.Open("mysql"."Root: 12345 @ TCP (127.0.0.1:3306)/department")
  iferr ! =nil {
    log.Fatal("open failed: ", err)
  }
  defer db.Close()

  tx, err := db.Begin()
  iferr ! =nil {
    log.Fatal("begin failed: ", err)
  }
  defer tx.Rollback()


  stmt, err := tx.Prepare("UPDATE employees SET team_id=? WHERE id=?")
  iferr ! =nil {
    log.Fatal("prepare failed: ", err)
  }
  defer stmt.Close()

  _, err = stmt.Exec(2.1)
  iferr ! =nil {
    log.Fatal("exec failed: ", err)
  }

  tx.Commit()
}
Copy the code

Note that db’s methods can no longer be called directly from within a transaction, because DB uses a different connection from the transaction, which may result in inconsistent execution results.

Error handling

Almost all operations in database/ SQL return a type error at the end. There are all kinds of errors in the database, and we should always check for errors. The following are some of the special cases of errors.

Iterate over the result set

for rows.Next() {
  // ...
}

iferr = rows.Err(); err ! =nil{}Copy the code

The error returned by ‘rows.err ()’ could be a variety of errors in the rows.next () loop. The loop may exit prematurely for some reason. We should check to see if the loop exits properly. Database/SQL automatically calls rows.close () on an abnormal exit. To exit early, we need to manually call rows.close (). ** Can call rows.close () ‘** multiple times.

Closing the result set

In fact, rows.close () also returns an error. However, there is a limit to what we can do about this error. This is usually just journaling. If logging is not required, this error is usually ignored.

QueryRow

Consider the following code:

var name string
err = db.QueryRow("SELECT name FROM employees WHERE id = ?".1).Scan(&name)
iferr ! =nil {
  log.Fatal(err)
}
fmt.Println(name)
Copy the code

If there is no employee with id = 1, what happens to Scan()?

Go defines a special error constant, sqL.errnorows. QueryRow returns this error if there are no rows that match the request. This error requires special handling in most cases, because the absence of a result is not usually considered an error at the application layer.

var name string
err = db.QueryRow("SELECT name FROM employees WHERE id = ?".1).Scan(&name)
iferr ! =nil {
  if err == sql.ErrNoRows {
  } else {
	log.Fatal(err)
  }
}
fmt.Println(name)
Copy the code

So why does QueryRow return an error if there are no rows that match the request?

If an empty result set is returned, since Scan() does not take any time, we cannot distinguish between name reading an empty string and an initial value.

Specific database error

One way to tell what kind of error has occurred is to check for specific text in the error description:

rows, err := db.Query("SELECT someval FROM sometable")
iferr ! =nil {
  if strings.Contains(err.Error(), "Access denied") {}}Copy the code

However, this is not recommended because these descriptions may not be consistent across database versions.

A good practice is to convert the error to a database-specific error and compare the error code:

if driverErr, ok := err.(*mysql.MySQLError); ok {
  if driverErr.Number == 1045{}}Copy the code

The judgment method may differ between different drivers. Also, it’s not a good idea to write the number 1045 directly. VividCortex compiles MySQL error codes, GitHub repository is mysqlerr. Use the library for subsequent modifications:

if driverErr, ok := err.(*mysql.MySQLError); ok {
  if driverErr.Number == mysqlerr.ER_ACCESS_DENIED_ERROR {
  }
}
Copy the code

Working with unknown columns

Sometimes, we may not be sure how many columns a query returns. But Scan() requires that the correct number of arguments be passed in. To do this, we can use rows.columns () to return all the column names and then create a slice of the string pointer of the same size to pass to Scan() :

func main(a) {
  db, err := sql.Open("mysql"."Root: 12345 @ TCP (127.0.0.1:3306)/department")
  iferr ! =nil {
    log.Fatal("open failed: ", err)
  }
  defer db.Close()

  stmt, err := db.Prepare("SELECT * FROM employees")
  iferr ! =nil {
    log.Fatal("prepare failed: ", err)
  }
  defer stmt.Close()

  rows, err := stmt.Query()
  iferr ! =nil {
    log.Fatal("exec failed: ", err)
  }
  defer rows.Close()

  cols, err := rows.Columns()
  iferr ! =nil {
    log.Fatal("columns failed: ", err)
  }

  data := make([]interface{}, len(cols), len(cols))
  for i := range data {
    data[i] = new(string)}for rows.Next() {
    err = rows.Scan(data...)
    iferr ! =nil {
      log.Fatal("scan failed: ", err)
    }

    for i := 0; i < len(cols); i++ {
      fmt.Printf("%s: %s ", cols[i], *(data[i].(*string)))
    }
    fmt.Println()
  }

  iferr = rows.Err(); err ! =nil {
    log.Fatal(err)
  }
}
Copy the code

Run the program:

Id: 1 name: Zhang SAN Age: 28 salary: 1200 team_id: 2 ID: 2 name: Li Siage: 38 salary: 4000 team_id: 1 ID: 3 name: Wang Wuage: 36 Salary: 3500 team_id: 1 ID: 4 Name: Zhao 6 Age: 31 Salary: 3100 team_id: 2 ID: 5 Name: Tian 7 Age: 29 salary: 2900 team_id: 2 ID: 6 Name: Wu Ba Age: 27 salary: 1500 team_id: 3 ID: 7 Name: Zhu Jiu age: 26 salary: 1600 team_id: 3 ID: 8 Name: Qian Shi age: 27 Salary: 1800 team_id: 3 ID: 9 Name: Tao Eleven Age: 28 Salary: 1900 team_id: 4 ID: 10 Name: Wang twelve age: 25 salary: 2000 team_id: 4 ID: 11 Name: Jian Shisan age: 24 Salary: 30000 team_id: 4 ID: 12 Name: Liu Shisan age: 32 salary: 5000 team_id: 1Copy the code

The connection pool

Database/SQL implements a basic connection pool. Connection pooling has some interesting features to learn about to avoid potholes:

• Execute two consecutive statements against the same database, which may be executed on different database connections. The results can be misleading. For example, firstLOCK TABLESAnd then executeINSERTMay clog up;
• If a new connection is needed and there are no free connections in the pool, create a new connection.
• By default, there is no limit on the number of connections. If you perform many operations at once, you may create many connections at the same time. The database may appeartoo many connectionsError;
• calldb.SetMaxIdleConns(N)Limit the maximum number of free connections in the pooldb.SetMaxOpenConns(N)Limit the number of open connections
• A connection that has not been used for a long time may cause problems. If a connection times out, try setting the maximum number of idle connections to 0.
• Reusing long-lived connections may cause network problems and can be calleddb.SetConnMaxLifeTime(duration)Set the maximum connection lifetime.

conclusion

This article introduces how to query and modify databases in Go, mainly the use of database/ SQL and go-SQL-driver /mysql libraries. The Database/SQL interface is not complicated, but many details need attention. You can leak resources if you’re not careful.

reference

1. MySQL tutorial, very detailed tutorial
2. The Go database/SQL tutorial
3. Build Web Application with Golang

I

My blog

Welcome to follow my wechat public account [GoUpUp], learn together, progress together ~

This article is published by OpenWrite!