Understanding database/sql

Question

I am playing around with the database/sql package trying to see how it works and understand what would happen if you don't call rows.Close() etc.

So I wrote the following piece of code for inserting a model to database:

func (db Database) Insert(m model.Model) (int32, error) {
    var id int32

    quotedTableName := m.TableName(true)

    // Get insert query
    q, values := model.InsertQuery(m)

    rows, err := db.Conn.Query(q, values...)
    if err != nil {
        return id, err
    }

    for rows.Next() {
        err = rows.Scan(&id)

        if err != nil {
            return id, err
        }

    }

    return id, nil
}

I don't call rows.Close() on purpose to see the consequences. When setting up the database connection I set some properties such as:

conn.SetMaxOpenConns(50)
conn.SetMaxIdleConns(2)
conn.SetConnMaxLifetime(time.Second*60)

Then I attempt to insert 10000 records:

for i := 0; i < 10000; i++ {
    lander := models.Lander{
        // ...struct fields with random data on each iteration
    }

    go func() {
        Insert(&lander)
    }()
}

(It lacks error checking, context timeouts etc. but for the purpose of playing around it gets the job done). When I execute piece of code from above I expect to see at least some errors regarding database connections however the data gets inserted without problems (all 10000 records). When I check the Stats() I see the following:

{MaxOpenConnections:50 OpenConnections:1 InUse:0 Idle:1 WaitCount:9951 WaitDuration:3h9m33.896466243s MaxIdleClosed:48 MaxLifetimeClosed:2}

Since I didn't call rows.Close() I expected to see more OpenConnections or more InUse connections because I am never releasing the connection (maybe I might be wrong, but this is the purpose of Close() to release a Connection and return it to the pool).

So my question is simply what do these Stats() mean and why are there no errors whatsoever when doing the insertion. Also why aren't there more OpenConnections or InUse ones and what are the real consequences of not calling Close()?

Burak Serdar · Accepted Answer · 2020-12-20 17:56:54Z

1

According to the docs for Rows:

If Next is called and returns false and there are no further result sets, the Rows are closed automatically and it will suffice to check the result of Err.

Since you iterate all the results, the result set is closed.

answered Dec 20, 2020 at 17:56

Burak Serdar

52.5k4 gold badges54 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

David Prifti Over a year ago

I followed the examples from golang.org/src/database/sql/example_test.go and when they iterate using Next() they still do a defer rows.Close()

Burak Serdar Over a year ago

Read the docs for Rows. If you iterate all the results, there is no need to close the result set. Also, Close is idempotent, so it is good practice to defer close in case you don't end up iterating the whole resultset.

Collectives™ on Stack Overflow

Understanding database/sql

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related