PRAGMA Value Gets Reset

[c9845]

I am trying to use PRAGMA busy_timeout = 10000 after calling sql.Open(). The PRAGMA gets set correctly, confirmed by running PRAGMA busy_timeout and viewing response, however, the value gets reset to 5000 (mattn/go-sqlite3 default it appears) after running a one/a few queries. If I set the PRAGMA via the filename DSN method, it does "stick". However, I would like to use the standard SQLite method of setting PRAGMAs just for ease of portability.

I understand there is connection pooling in Go and (some) PRAGMAs are set per-connection, but, from what I can tell, I am using the same conn used in and returned by Open() to run my Exec()s.

I am currently calling Open and then immediately calling Exec to set the PRAGMA.

conn, err := sqlx.Open(driver, connString)
if err != nil {
    log.Println(err)
}

_, err:= conn.Exec("PRAGMA busy_timeout=10000")
if err!= nil {
    log.Println(err)
}

Is there any way to use PRAGMA queries to set PRAGMAs?

[rittneje]

You need to set pragmas in a ConnectHook for them to properly apply to all connections in the pool.

For busy_timeout in particular (and several other common pragmas) you can just set the appropriate value in the connection uri instead and this library will take care of it. https://github.com/mattn/go-sqlite3#connection-string

[c9845]

Thanks. Yes, I am aware of the ability to set pragmas via the connection string. I just had hoped to use the SQLite PRAGMA commands instead. I had thought maybe there was some workaround, maybe with SetMaxOpenConns(), to do so. I will move to the connection string method.