Avoid falling into these 11 pitfalls when using database/sql! (Go database)

                                                                                    

The following column will introduce you to these 11 pitfalls that you should avoid falling into when using database/sql. I hope it will be helpful to friends in need!

We are big fans of the Avoid falling into these 11 pitfalls when using database/sql! (Go database) language and its database access library

database/sql. As you can probably see with your own eyes,

database/sql

is very small in size, but you can do a lot with it. This includes substantial risks of error and deceptive error. This blog post is dedicated to some of the mistakes we’ve made in the past, in the hope that you won’t make the same mistakes again. Common Pitfalls

Delay within a loop.

Long-lived functions have queries within the loop, and delaying
• rows.Close()

  within the loop will cause memory and connection usage to grow without limit.

  Open many
db
• objects.

  Please create a global sql.DB and do not open a new one for every incoming HTTP request your API server should respond to. Otherwise, you will be opening and closing a lot of TCP connections to the database. TIME_WAIT State latency, load and TCP connections are heavy.

  Do not do
rows.Close()
• after the operation is completed.

  Forgetting to close the rows variable means a connection leak. Combined with the increasing load on the server, this may mean encountering max_connections errors or similar. Please run rows.Close() as soon as possible, even if it will be used again later (which is also harmless). Chain db.QueryRow() and .Scan() for the same reason.

  Prepared statement bloat.
If your code is running with high concurrency, consider whether prepared statements are the right solution, as they may be re-prepared multiple times on different connections when the connection is busy.

• strconv or casts clutter the code.
It is recommended to scan the results into a variable of the type you want and let
• .Scan()

  do the conversion for you behind the scenes.

  Error handling and retries lead to confusing code.
Let
• database/sql

  handle the connection pooling, reconnection and retry logic for you.

  Forgot to check for errors after
rows.Next()
• .

  Don't forget, rows.Next() The loop may exit abnormally.

  Use
db.Query()
• for non-SELECT queries.

  If there is no result set, do not tell Avoid falling into these 11 pitfalls when using database/sql! (Go database) that you wish to iterate over the result set, otherwise the connection will be leaked.

  Assume that subsequent statements use the same connection.
If two statements are run consecutively, they are likely to be run on two different connections. Run
• LOCK TABLES tbl1 WRITE

  , then run SELECT * FROM tbl1, and you will most likely block and wait. If you need to ensure that a single statement is used, you need to use the parameter sql.Tx.

  Access the database while using TX.
• sql.Tx

  is bound to a transaction, but the database is not bound, so accessing it will not participate in a transaction.

  Surprised at a
NULL
• .

  You cannot scan a NULL type into a variable unless it is one of the NullXXX types provided by the database/sql package (or you make it yourself , or provided by the driver), otherwise. Check your schema carefully, because if a column can be NULL, then one day it will become NULL, and what works in test may break in production.

  Original address: https://orangematter.solarwinds.com/2017/03/23/common-pitfalls-when-using-database-sql-in-go/

Translation address: https://learnku.com/go/t/50966

The above is the detailed content of Avoid falling into these 11 pitfalls when using database/sql! (Go database). For more information, please follow other related articles on the PHP Chinese website!