In modern software development, databases play a crucial role in storing and managing data from applications. For different database systems, developers usually need to use specific database drivers to operate the database, which often requires developers to master different driver programming interfaces. In Go, there is a name calleddatabase/sqlThe standard library provides a unified programming interface that enables developers to interact with various relational databases in a common way.
concept
database/sqlPackages implement abstractions of different database drivers by providing a unified programming interface.
Its general principle is as follows:
-
DriverInterface definition:database/sql/driverA defined in the packageDriverInterface, which is used to represent a database driver. Drivers need to implement this interface to provide interactive capabilities with specific databases. -
DriverRegistration: Driver developers need to call in the program initialization stagedatabase/sqlPackage provided()Method to register your own driver withdatabase/sqlmiddle. so,database/sqlThis driver can be recognized and used. - Database connection pool management:
database/sqlA database connection pool is maintained to manage database connections. When passing()When opening a database connection,database/sqlThe registered driver will be called at the appropriate time to create a specific connection and add it to the connection pool. The connection pool is responsible for the reuse, management and maintenance of the connection, and this is concurrently secure. - Unified programming interface:
database/sqlDefine a unified set of programming interfaces for users to use, such asPrepare()、Exec()andQuery()Methods such as preparing SQL statements, executing SQL statements, and executing queries. These methods will receive parameters and call the corresponding methods of the underlying driver to perform the actual database operations. - Implementation of interface method: Drivers need to implement
database/sql/driverSome interface methods defined in this way support the upper layerdatabase/sqlPackage providedPrepare()、Exec()andQuery()etc. to provide specific implementation of the underlying database. whendatabase/sqlWhen these methods are called, the corresponding methods of the registered driver will actually be called to perform specific database operations.
Through the above mechanism,database/sqlPackages can implement unified encapsulation and calls to different database drivers. Users can use the same programming interface to perform database operations without caring about the specific details of the underlying driver. This design makes the code more portable and flexible, making it easier to switch and adapt to different databases.
Features
database/sqlIt has the following characteristics:
- Unified programming interface:
database/sqlThe library provides a unified set of interfaces that enable developers to operate different databases in the same way without learning the APIs of a specific database. - Driver support: by importing third-party database drivers,
database/sqlIt can interact with many common relational database systems, such as MySQL, PostgreSQL, SQLite, etc. - Prevent SQL injection:
database/sqlThe library effectively prevents SQL injection attacks by using technologies such as precompiled statements and parameterized queries. - Support transactions: Transactions are an excellent SQL package must-have feature.
Prepare
For demonstrationdatabase/sqlUsage, I have prepared the following MySQL database table:
DROP TABLE IF EXISTS `user`; CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) DEFAULT NULL COMMENT 'username', `email` varchar(255) NOT NULL DEFAULT '' COMMENT 'Mail', `age` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'age', `birthday` datetime DEFAULT NULL COMMENT 'Birthday', `salary` varchar(128) DEFAULT NULL COMMENT 'salary', `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `u_email` (`email`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='User Table';
You can create this table using the MySQL command line or graphical tools.
Connect to the database
To usedatabase/sqlTo operate a database, you must first establish a connection to the database:
package main
import (
"database/sql"
"log"
_ "/go-sql-driver/mysql"
)
func main() {
db, err := ("mysql",
"user:password@tcp(127.0.0.1:3306)/demo?charset=utf8mb4&parseTime=true&loc=Local")
if err != nil {
(err)
}
defer ()
}Because we want to connect to the MySQL database, we need to import the MySQL database driver/go-sql-driver/mysql。database/sqlDesigned by the official Go language team, while the database driver is maintained by the community, and other relational database driver lists can be found inhereCheck.
The code to establish a connection to the database is very simple, just call it()Just function. It receives two parameters, the driver name and the DSN.
Here the driver name ismysql，database/sqlThe reason why this driver name can be recognized is because it is imported anonymously/go-sql-driver/mysqlWhen this library is called internallyRegister it withdatabase/sql。
func init() {
("mysql", &MySQLDriver{})
}In Go, a packageinitThe method will be automatically called during import, and the driver registration is completed here. This is calling()Only find it whenmysqldrive.
The second parameter DSN full nameData Source Name, the source name of the database, its format is as follows:
username:password@protocol(address)/dbname?param=value
Here are the explanations of the DSN sections we provide:
-
user:password: The user name and password of the database. Depending on the actual situation, you need to use your own username and password. -
tcp(127.0.0.1:3306): The protocol connecting to the database server, the address and port number of the database server. In this example, the local host is used127.0.0.1and MySQL default port number3306. You can modify it to your own database server address and port number according to the actual situation. -
/demo: The name of the database. In this example, the database name isdemo. You can modify it to your own database name according to the actual situation. -
charset=utf8mb4: Specify the character set of the database asUTF-8. The one used here isUTF-8Variations ofUTF-8mb4, supports a wider range of characters. -
parseTime=true: Enable time resolution. This parameter enables the database driver to use the time type field in the database (datetime) parsed into Go languagetype. -
loc=Local: Set the time zone to the local time zone. This parameter specifies the local time zone for the database driver to use.
()After the call, a return*Type, can be used to operate the database.
In addition, we calldefer ()to release the database connection. Actually, this step can be done without it.database/sqlThe underlying connection pool will help us handle it. Once the connection is closed, you can no longer use thisdbThe object is here.
*The design is used as a long connection, so it does not need to be done frequentlyOpenandCloseoperate. If we need to connect to multiple databases, we can create one for each different database*Objects, keep these objects asOpenStatus, no need to use it frequentlyCloseto switch connections.
It is worth noting that, in fact()There is no real database connection established, it just prepares everything for subsequent use, and the connection will be established late when the first time it is used.
Although this design is reasonable, it is also somewhat counterintuitive.()The legality of the DSN parameter will not even be verified. But we can use()Method to actively check whether the connection can be properly established.
if err := (); err != nil {
(err)
}use()A unique database connection is not established, in fact,database/sqlA connection pool will be maintained.
We can control some parameters of the connection pool through the following method:
(25) // Set the maximum number of concurrent connections (in-use + idle)(25) // Set the maximum number of idle connections (idle)(5 * ) // Set the maximum life cycle of the connection
These parameter settings can be modified based on experience. The above parameters can meet the use of some small and medium-sized projects. If it is a large project, the parameters can be adjusted appropriately.
Declaration model
After the connection is established, in theory we can operate the database for CRUD. However, in order to make the code written more maintainable, we often need to defineModelto map database tables.
userThe table mapped model is as follows:
type User struct {
ID int
Name
Email string
Age int
Birthday *
Salary Salary
CreatedAt
UpdatedAt string
}The model is represented by struct in Go, and the structure fields correspond one by one to the fields in the database table.
The Salary type is defined as follows:
type Salary struct {
Month int `json:"month"`
Year int `json:"year"`
}Regarding the particularity of the Name and Salary fields, I will explain in the handling of NULL and Custom Field Types.
create
* Provides an Exec method to execute a SQL command that can be used to create, update, delete table data, etc.
Here, use the Exec method to create a user:
func CreateUser(db *) (int64, error) {
birthday := (2000, 1, 1, 0, 0, 0, 0, )
user := User{
Name: {String: "jianghushinian007", Valid: true},
Email: "jianghushinian007@",
Age: 10,
Birthday: &birthday,
Salary: Salary{
Month: 100000,
Year: 10000000,
},
}
res, err := (`INSERT INTO user(name, email, age, birthday, salary) VALUES(?, ?, ?, ?, ?)`,
, , , , )
if err != nil {
return 0, err
}
return ()
}First, we instantiate a User object user and assign values to the corresponding fields.
Then use the method to execute the SQL statement:
INSERT INTO user(name, email, age, birthday, salary) VALUES(?, ?, ?, ?, ?)
Where ? is a parameter placeholder, the placeholders of different database drivers may be different, so you can refer to the database driver documentation.
We pass these 5 parameters to the method in sequence to complete the creation of the user.
After the method is called, the save result will be returned and an error will be marked.
is an interface that contains two methods:
- LastInsertId() (int64, error): Returns the newly inserted user ID.
- RowsAffected() (int64, error): Returns the number of rows affected by the current operation.
The specific implementation of the interface is completed with a database driver.
Call the CreateUser function to create a new user:
if id, err := CreateUser(db); err != nil {
(err)
} else {
("id:", id)
}In addition, database/sql also provides preprocessing methods * Create a prepared SQL statement that uses preprocessing in a loop, which can reduce the number of interactions with the database.
For example, if we need to create two users, we can first create a * object and then call the * method multiple times to insert the data:
func CreateUsers(db *) ([]int64, error) {
stmt, err := ("INSERT INTO user(name, email, age, birthday, salary) VALUES(?, ?, ?, ?, ?)")
if err != nil {
panic(err)
}
// Note: The preprocessing object needs to be closed defer ()
birthday := (2000, 2, 2, 0, 0, 0, 0, )
users := []User{
{
Name: {String: "", Valid: true},
Email: "jianghushinian007@",
Age: 20,
Birthday: &birthday,
Salary: Salary{
Month: 200000,
Year: 20000000,
},
},
{
Name: {String: "", Valid: false},
Email: "jianghushinian007@",
Age: 30,
},
}
var ids []int64
for _, user := range users {
res, err := (, , , , )
if err != nil {
return nil, err
}
id, err := ()
if err != nil {
return nil, err
}
ids = append(ids, id)
}
return ids, nil
}It is to pre-bind a database connection with a SQL statement and return a * structure. It represents the bound connection object and is concurrently safe.
By using preprocessing, multiple complete SQL statements can be avoided executing in a loop, which significantly reduces the number of database interactions, which can improve application performance and efficiency.
Using preprocessing, a connection will be obtained from the connection pool at a time, and then executed cyclically to finally release the connection.
If you use , you need to get the connection - execute SQL-release connection every time you loop. These steps greatly increase the number of interactions with the database.
Don't forget to call () to close the connection. This method is secret and can be called multiple times.
Query
Now that the data is already in the database, we can query the data.
Because the Exec method only executes SQL and does not return results, it is not suitable for querying data.
* Provides the Query method to perform query operations:
func GetUsers(db *) ([]User, error) {
rows, err := ("SELECT * FROM user;")
if err != nil {
return nil, err
}
defer func() { _ = () }()
var users []User
for () {
var user User
if err := (&, &, &, &,
&, &, &, &); err != nil {
(())
continue
}
users = append(users, user)
}
// Handle errors if err := (); err != nil {
return nil, err
}
return users, nil
}Return the query result set*, this is a structure.
()The method is used to determine whether there is a next result, which can be used to determine theforLoop (off topic: This is a bit like a Python iterator, except that the next value is not returned directly, but throughScanmethod obtain).
If the next result exists,()Will returntrue。
()The method can scan the result to the passed-in pointer image. Because we usedSELECT *to query, so the data of all fields will be returned, and theuserThe corresponding field pointer of the object is passed in.
()A row of records will be filled in the specified variable separately, and the type conversion problem will be automatically handled according to the type of the target variable, such as in the databasevarcharTypes will be mapped tostring, but if the corresponding target variable isint, then the conversion fails and returnserror。
CreatedAtThe field isThe reason why the type can be processed correctly is because it is called()The DSN passed whenparseTime=trueparameter.
when()Return asfalseWhen there is no next record. We will store all the query users tousersSliced.
After the loop is over, remember to call it()to handle the error.
Above, we have checked multiple users.*Also providedQueryRowMethods can query a single record:
func GetUser(db *, id int64) (User, error) {
var user User
row := ("SELECT * FROM user WHERE id = ?", id)
err := (&, &, &, &,
&, &, &, &)
switch {
case err == :
return user, ("no user with id %d", id)
case err != nil:
return user, err
}
// Handle errors if err := (); err != nil {
return user, err
}
return user, nil
}Querying a single record will return*Structure, it is actually right*One layer of packaging:
type Row struct {
// One of these two will be non-nil:
err error // deferred error for easy chaining
rows *Rows
}We no longer need to call()Determine whether there is a next result, call()hour*It will automatically handle it for us and return the first piece of data in the query result set.
if()The error type returned isIt means that no data that meets the criteria was found, which is particularly useful for judging the error type.
butdatabase/sqlObviously, error types for all databases cannot be enumerated. Some of them are specified for different databases, usually defined by the database driver.
A specific MySQL error type can be judged as follows:
if driverErr, ok := err.(*); ok {
if == 1045 {
// Handle rejected errors }
}But like1045It is best not to appear in the code such magic numbers.mysqlerrThe package provides an enumeration of MySQL error types.
The above code can be changed to:
if driverErr, ok := err.(*); ok {
if == mysqlerr.ER_ACCESS_DENIED_ERROR {
// Handle rejected errors }
}
Finally, don't forget to call it too()Handle errors.
renew
Update operations can be used the same as creation*Methods are implemented, but here we will use*Method to implement.
ExecContextMethods andExecThe method is no different in use, except that the first parameter needs to receive one, which allows you to control and cancel the execution of SQL statements. Use the context to set the timeout time, process the request cancellation, and other operations when needed.
func UpdateUserName(db *, id int64, name string) error {
ctx := ()
res, err := (ctx, "UPDATE user SET name = ? WHERE id = ?", name, id)
if err != nil {
return err
}
affected, err := ()
if err != nil {
return err
}
if affected == 0 {
// If the new name is equal to the original name, it will also be executed here return ("no user with id %d", id)
}
return nil
}Used here()Gets the number of rows affected by the current operation.
Note that if the updated field results do not change,()Returns 0.
delete
use*Method to delete users:
func DeleteUser(db *, id int64) error {
ctx := ()
res, err := (ctx, "DELETE FROM user WHERE id = ?", id)
if err != nil {
return err
}
affected, err := ()
if err != nil {
return err
}
if affected == 0 {
return ("no user with id %d", id)
}
return nil
}Transactions
Transactions are basically database functions that are indispensable when developing web projects.database/sqlProvides support for transactions.
The following example uses transactions to update users:
func Transaction(db *, id int64, name string) error {
ctx := ()
tx, err := (ctx, &{Isolation: })
if err != nil {
return err
}
_, execErr := (ctx, "UPDATE user SET name = ? WHERE id = ?", name, id)
if execErr != nil {
if rollbackErr := (); rollbackErr != nil {
("update failed: %v, unable to rollback: %v\n", execErr, rollbackErr)
}
("update failed: %v", execErr)
}
return ()
}*Used to start a transaction, the first parameter is, the second parameter is*Object, used to configure transaction options,IsolationFields are used to set the database isolation level.
SQL statements executed in transactions need to be placed intxThe object'sExecContextExecute in the method, not。
If an error occurs during SQL execution, you can use()Perform transaction rollback.
If there is no error, you can use()Submit transaction.
txAlso supportedPrepareMethod, you can clickhereCheck out the usage example.
Process NULL
CreatingUserWhen we define the modelNameThe field type is, not ordinarystringType, this is to support the databaseNULLtype.
In the databasenameThe fields are defined as follows:
`name` varchar(50) DEFAULT NULL COMMENT 'username'
SonameThere are three cases where possible values in the database:NULL, empty string''and a valued string'n1'。
We know that in GostringThe default value of the type is an empty string'',butstringUnable to expressNULLvalue.
At this time, we have two ways to solve this problem:
- Use pointer type.
- use
type.
Because the pointer type may benil, so you can usenilCome to correspondNULLvalue. This isUserIn the modelBirthdayThe field type is defined as*for the reason.
The type definition is as follows:
type NullString struct {
String string
Valid bool // Valid is true if String is not NULL
}StringUsed to record values,ValidUsed to mark whether it isNULL。
NullStringThe value of the structure and the actual stored values in the database have the following mapping relationship:
| value | value for MySQL |
|---|---|
| {String:n1 Valid:true} | 'n1' |
| {String: Valid:true} | '' |
| {String: Valid:false} | NULL |
also,Types are also implementedandTwo interfaces:
// Scan implements the Scanner interface.
func (ns *NullString) Scan(value any) error {
if value == nil {
, = "", false
return nil
}
= true
return convertAssign(&, value)
}
// Value implements the driver Valuer interface.
func (ns NullString) Value() (, error) {
if ! {
return nil, nil
}
return , nil
}These two interfaces are used separately*Methods and*method.
That is to be used*When the method executes SQL, we may need toNameThe value of the field is stored in MySQL, at this timedatabase/sqlWill callType ofValue()Method, obtain the value it will store in the database.
In use*When we are using the method, we may need to get thenameField values mapped toUserStructure fieldNameUp, at this timedatabase/sqlWill callType ofScan()Method, assign the value queryed from the database toNameField.
If the field you are using is notstringtype,database/sqlAlso provided、sql.NullFloat64and other types for users to use.
However, this does not enumerate all field types supported by MySQL databases, so if you can try to avoid it, it is still not recommended to allow database fields.NULLvalue.
Custom field types
Sometimes, the data we store in the database has a specific format, such assalaryThe value stored in the database is{"month":100000,"year":10000000}。
In the databasesalaryThe fields are defined as follows:
`salary` varchar(128) DEFAULT NULL COMMENT 'salary'
If you just map it tostring, be extra careful when operating, if you forget to write one"or,Then, the program may report an error.
becausesalaryThe value is obviously a JSON format, we can define astructTo map its content:
type Salary struct {
Month int `json:"month"`
Year int `json:"year"`
}This is not enough, custom types cannot be supported*Methods and*method.
However, I think you have guessed it, we can refer to itType letSalaryThe same implementationandTwo interfaces:
// Scan implements
func (s *Salary) Scan(src any) error {
if src == nil {
return nil
}
var buf []byte
switch v := src.(type) {
case []byte:
buf = v
case string:
buf = []byte(v)
default:
return ("invalid type: %T", src)
}
err := (buf, s)
return err
}
// Value implements
func (s Salary) Value() (, error) {
v, err := (s)
return string(v), err
}In this way, the operation of storing and querying data,SalaryAll types can be supported.
Unknown column
In extreme cases, we may not know how to use it*The column name and number of fields in the result set of the method query.
At this point, we can use*The method gets all column names, which will return a slice, which is the number of fields.
The sample code is as follows:
func HandleUnknownColumns(db *, id int64) ([]interface{}, error) {
var res []interface{}
rows, err := ("SELECT * FROM user WHERE id = ?", id)
if err != nil {
return res, err
}
defer func() { _ = () }()
// If you don't know the column name, you can use () to find the column name list cols, err := ()
if err != nil {
return res, err
}
("columns: %v\n", cols) // [id name email age birthday salary created_at updated_at]
("columns length: %d\n", len(cols))
// Get column type information types, err := ()
if err != nil {
return nil, err
}
for _, typ := range types {
// id: &{name:id hasNullable:true hasLength:false hasPrecisionScale:false nullable:false length:0 databaseType:INT precision:0 scale:0 scanType:0x1045d68a0}
("%s: %+v\n", (), typ)
}
res = []interface{}{
new(int), // id
new(), // name
new(string), // email
new(int), // age
new(), // birthday
new(Salary), // salary
new(), // created_at
// If you don't know the column type, you can use it, it is actually an alias for []byte new(), // updated_at
}
for () {
if err := (res...); err != nil {
return res, err
}
}
return res, ()
}In addition to getting column names and fields, we can also use*Method to get eachcolumndetails.
If we don't know the type of a field in the database, we can map it toType, it is actually[]bytealias for .
Summarize
database/sqlThe package unifies the programming interface of Go language operating databases, avoiding the dilemma of learning multiple APIs when operating different databases.
use()Establishing a database connection will not take effect immediately, and the connection will be delayed at the right time.
We can use* / *to execute SQL commands. In fact, exceptExecThere is a method corresponding toExecContextVersion, mentioned in the article*、*、*、*There are also corresponding methodsXxxContextYou can test the version yourself.
If the executed SQL statement contains the MySQL keyword, you need to use backticks (`) to wrap the keyword, otherwise you will getError 1064 (42000): You have an error in your SQL syntax;mistake.
*A transaction can be opened, and the transaction needs to be explicit.CommitorRollbackMySQL driver also supports use*Set the transaction isolation level.
forNULLtype,database/sqlProvidedetc. We can also implement it for custom typesandTwo interfaces to implement specific logic.
For unknown column and field types, we can use*、Let’s solve it. Although this greatly increases flexibility, it is not recommended to use it unless it is absolutely necessary. Using more explicit code can reduce the number of bugs and improve maintainability.
The above is the detailed content of the tutorial guide for Go using database/sql to operate databases. For more information about Go database/sql, please follow my other related articles!