Here’s an overview of what I’ve done, but no quality claim is made.
In package main there’s a global:
type DB struct {
*sql.DB
}
var database DB
In func main() I call an initialization function that reads a JSON
configuration file and does sql.Open with dbname, user, password, host,
port, and sslmode from the file, result assigned to database.DB. There’s a
database.Ping() then database.SetMaxIdleConns and database.SetMaxOpenConns.
Any error causes a panic.
The rest of package main has many methods on DB. An example is all of my
handlers call database.validSession(r). The method approach is so that I
can configure a test database when writing unit tests for package main as
suggested by Google search results awhile back. In some cases database
corruption can happen in my app by concurrent DB.Query calls followed by
DB.Exec. I don’t change the postgres transaction isolation, but some
methods are on TX instead of DB because of these race conditions.
type TX struct {
*sql.Tx
}
// error handling for sql.DB.Begin and sql.Tx.Commit is always a panic
func (db DB) Begin() TX {
func (tx TX) Commit() {
One of my TX methods has a forWrite bool to add a FOR UPDATE to the query
because SELECT won’t lock the row for a transaction without it in postgres
at the default transaction isolation level.
Here’s an example query:
err = db.QueryRow("SELECT "+session_name+" FROM "+session_table+" WHERE "+
session_key+"=$1;", keyCookie.Value).Scan(&name)
I have an open issue for figuring out how to make this better/cleaner, but
at least all of my queries rely on consts for key names. Error handling for
database interaction is quite variable depending on the use of the
surrounding function. Any unhandleable error results in a panic, and
sql.ErrNoRows is useful.
I haven’t used the context package, or a middleware pattern besides
copy-paste of function calls in handlers.
Some of my code is messy with these patterns. Commit can’t always be
deferred which brings us back to writing it in at every return point. The
choices of where transactions are opened in relation to the method uses
seems arbitrary because of layering fixes over time without a rewrite. I
haven’t written package main unit tests yet so I can’t comment on how much
value methods on DB and TX add. But problems are mostly straightforward to
fix for now.
My current function structure causes transactions to overlap within one
path of execution sometimes (a TX is open during a DB query or exec), but
this seems to not cause problems since they are guaranteed to be accessing
different tables in my case.
Here’s the app source, all database interaction is in package main:
https://github.com/pciet/wichess
Concurrent request load testing helped me find 5+ problems with my database
interactions. Transactions aren't completely straightforward to get right.
Matt
On Friday, January 5, 2018 at 9:16:34 AM UTC-6, Manlio Perillo wrote:
>
> Recently I have developed a few web applications in Go, using a PostgreSQL
> database, but I have yet understand the best way to use the database/sql
> package.
>
> The simplest method seems to start a transaction in the HTTP handler and
> pass a *sql.Tx value to all the functions that need to access the database.
> However I'm afraid that this method is "too" simple.
>
> One possible problem is an HTTP handler acting as a middleware, and both
> the "middleware" an the "normal" HTTP handler needs a transaction.
> Using the "simplest method", the middleware and the handler will end up
> using two distinct transaction, and this may not be what one expects.
>
>
> The other solution is to do what frameworks like Django do, storing
> something like
>
> type Interface interface {
> func Exec(query string, ...) ...
> func Query(query string, ...) ...
> func InTransaction) bool
> }
>
> as a per request data, including transaction state, (ab)using
> Context.WithValue. But I suspect that this is not a good idea.
>
>
> Thanks Manlio
>
--
You received this message because you are subscribed to the Google Groups
"golang-nuts" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to golang-nuts+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
