List below contains some useful information and can be very beneficial while working with database driven Go application so it's up to you to test and apply to your own code. The most of the information below is coming from Go database/sql tutorial and The Ultimate Guide To Building Database - Driven Apps with Go documents.


The most important thing you will learn here and must take care of is the usage of db.Prepare function. It can really hit you very very hard if you don't follow the suggestions, don't understand what actually it does and how it does! On top of the information written below, I highly suggest you to read second link above because there are more in-depth explanations on why.


1) Prepared vs non-prepared statements


If you are 100% sure that the data you are about the use as part of your SQL queries (select, insert, update so on.) is safe (SQL injection free), you should always consider using db.Exec instead of relying on db.Prepare statement. The reason is because, prepared statements will waste resources by issuing three queries (Prepare, Execute and Close) just to do one job whereas directly executing statements will issue only one query (Query). The example below is meant to insert a single row into a table. The prepared option will triple the number of network round-trips. However, the direct execution option will cause one network round-trip. Note: If you wish to prepare parameters explicitly, fmt.Sprintf() with the %q placeholder could be used.


db.Prepare


func Insert() (int64, error) {
stmt, err := db.Prepare("INSERT INTO users (name) VALUES (?)")
if err != nil {
return 0, err
}
defer stmt.Close()

res, err := stmt.Exec("inanzzz")
if err != nil {
return 0, err
}

id, err := res.LastInsertId()
if err != nil {
return 0, err
}

return id, nil
}

2019-12-28T21:12:53.517599Z	    3 Prepare	INSERT INTO user (name) VALUES (?)
2019-12-28T21:12:53.518932Z 3 Execute INSERT INTO user (name) VALUES ('inanzzz')
2019-12-28T21:12:53.527352Z 3 Close stmt

db.Exec


func Insert() (int64, error) {
res, err := db.Exec("INSERT INTO user (name) VALUES ('inanzzz')")
if err != nil {
return 0, err
}

id, err := res.LastInsertId()
if err != nil {
return 0, err
}

return id, nil
}

2019-12-28T21:13:11.337554Z	    3 Query	INSERT INTO user (name) VALUES ('inanzzz')

Benchmark


100 users concurrently sending requests for 10 seconds without a break. The findings below are just rough calculations.


// db.Prepare
Average total requests: 4500
Average request duration: 210ms

// db.Exec
Average total requests: 8500
Average request duration: 120ms

2) Prepare inside vs outside of a loop


If you are going to run multiple identical queries for different set of data in a loop, keep the prepare db.Prepare outside of the loop and execute stmt.Exec inside of it. The reason is because preparing and closing the same query repeatedly is just going to waste resources for all executions which is obviously useless. Moreover, calling defer stmt.Close() inside the loop is like waiting for disaster to happen because it will potentially cause resource leak. As a result, preparing inside the loop will double the amount of queries. See examples below.


Inside


func Insert() error {
for i := 1; i < 4; i++ {
stmt, err := r.database.Prepare(`INSERT INTO users (name) VALUES (?)`)
if err != nil {
return err
}
defer stmt.Close()

_, err := stmt.Exec(fmt.Sprintf("inanzzz-%d", i))
if err != nil {
return err
}
}

return nil
}

As you can see we have total of 9 queries run.


2019-12-28T21:12:53.517599Z	    3 Prepare	INSERT INTO user (name) VALUES (?)
2019-12-28T21:12:53.518932Z 3 Execute INSERT INTO user (name) VALUES ('inanzzz-1')
2019-12-28T21:12:54.517599Z 3 Prepare INSERT INTO user (name) VALUES (?)
2019-12-28T21:12:54.518932Z 3 Execute INSERT INTO user (name) VALUES ('inanzzz-2')
2019-12-28T21:12:55.517599Z 3 Prepare INSERT INTO user (name) VALUES (?)
2019-12-28T21:12:55.518932Z 3 Execute INSERT INTO user (name) VALUES ('inanzzz-3')
2019-12-28T21:12:56.527352Z 3 Close stmt
2019-12-28T21:12:56.527352Z 3 Close stmt
2019-12-28T21:12:56.527352Z 3 Close stmt

Outside


func Insert() error {
stmt, err := db.Prepare(`INSERT INTO users (name) VALUES (?)`)
if err != nil {
return err
}
defer stmt.Close()

for i := 1; i < 4; i++ {
_, err := stmt.Exec(fmt.Sprintf("inanzzz-%d", i))
if err != nil {
return err
}
}

return nil
}

As you can see we have total of 5 queries run.


2019-12-28T21:12:53.517599Z	    3 Prepare	INSERT INTO user (name) VALUES (?)
2019-12-28T21:12:53.518932Z 3 Execute INSERT INTO user (name) VALUES ('inanzzz-1')
2019-12-28T21:12:54.518932Z 3 Execute INSERT INTO user (name) VALUES ('inanzzz-2')
2019-12-28T21:12:55.518932Z 3 Execute INSERT INTO user (name) VALUES ('inanzzz-3')
2019-12-28T21:12:55.527352Z 3 Close stmt

3) LastInsertId and RowsAffected


Use result.LastInsertId() and result.RowsAffected() functions only if you explicitly need to know what last inserted id was or how many rows were affected. Otherwise do not use them by default because although these functions don’t access the database, they may become "blocking" operations if their connections are busy. Also, not all database drivers support these features either.


4) Query/QueryRow vs Exec


You should prefer using db.Exec if the statement doesn’t return rows as opposed to db.Query and db.QueryRow. The main reason is because db.Exec straight away releases its connection back to the pool whereas db.Query keeps its connection out of the pool until rows.Close() is called. If you avoid this suggestion, you might cause connection "leak" and server run out of available connections.


5) db.SetMaxIdleConns


It defines the amount of connections which should remain idle in the pool after being released. The default value is 2. This default value would cause a lot of connections being closed and opened one after another which is something you want to avoid. It leads to extra work and latency as well as delay while waiting for a new connection. You can safely set it to 5 or above such as 25 instead.


Benchmark


100 users concurrently sending requests for 10 seconds without a break. The findings below are just rough calculations.


// db.SetMaxIdleConns = 2 (default)
Average total requests: 3800
Average request duration: 250ms

// db.SetMaxIdleConns = 5
Average total requests: 4700
Average request duration: 200ms

// db.SetMaxIdleConns = 25
Average total requests: 5500
Average request duration: 180ms

Additional notes


Deferring inside a loop


Don't call defer rows.Close() inside a loop because it would potentially cause memory or connection "leak".


Opening many db objects


Create the sql.DB instance only once at the application boot and let all the requests use it. Otherwise you will be opening and closing many TCP connections to the database and potentially crash the server.


Forgetting to close the rows


Run defer rows.Close() as soon as you can otherwise it will potentially cause connection "leak".


Avoid prepared statements


If you can, do your best to avoid db.Prepare in order to increase performance by running less queries and unnecessary network round-trips for duplicated queries. Try to utilise fmt.Sprintf() with the %q placeholder to explicitly prepare parameters.


Using query for non-select queries


Don't use db.Query and db.QueryRow if you are not issuing a Select statement. Use db.Exec instead.


Working with null fields


If the field in a table is returning a nullable, your struct or variable type must be set as nullable too. e.g. sql.NullString, mysql.NullTime etc.


Working with uint64 parameters


The db.Query, db.QueryRow and db.Exec functions are not really good at working with parameters of type uint64 so convert them to strings with fmt.Sprint() to avoid surprises.