Sqlx: Best way to update a row?

[MichaelHipp]

In database/sql I generally use db.Exec("UPDATE ... WHERE id=$1;", ...) to update a single row. Is there a better way to do this this in Sqlx?

[asiplas]

NamedExec?

db.NamedExec(`UPDATE user SET first_name=:first, last_name=:last WHERE :id = 42`, user)

See https://github.com/joncrlsn/go-examples/blob/99176d656cb949d3ab83b01403b507d6680dc4fa/sqlx-sqlite.go#L98

[mcandre]

Hmm, I thought the point of using an object relational mapper is to not have to explicitly write out raw SQL statements.

Could we get Update(), Upsert() methods similar to GORM's Save() method?

[cbndr]

@jmoiron happy to code this extension - what's the contribution policy?

I would suggest Update/Insert/Upsert methods on db and tx. Need a way to identify PK for updates though.

[oxodao]

sqlx is not an ORM, if that's what you're looking for, you should take a look at gorm or other libs like that.

[cayter]

I'm currently building an ORM on top of sqlx that will support:

• [x] multi-database
• [x] specify masters/replicas via struct tag
• [x] randomly select 1 of the masters/replicas to execute the SQL query
• [x] query execution with context support
• [x] query logging
• [ ] provide hooks for before/after CRUD
• [ ] provide interface mocks for easier unit testing

If you would like to understand how the implementation looks like and what the tradeoff is between using pure sqlx and ORM, you can follow up here.

Disclaimer

The ORM is tightly coupled with the web framework as the ORM uses app.DB("primary") to get a connection from the primary DB connection pool (which is defined as DB_URI_PRIMARY=postgres://... in the encrypted dotenv config file), but it should give you a rough idea of how challenging it can be to build a ORM and when you would want to avoid using ORM along with the tradeoff you're getting.

Benchmark (as of 6 May 2020)

go test -run=NONE -bench . -benchmem -benchtime 5s -failfast ./record
goos: darwin
goarch: amd64
pkg: github.com/appist/appy/record
BenchmarkRawInsert-4                 639          13037602 ns/op              88 B/op          5 allocs/op
BenchmarkDBInsert-4                  544          18675449 ns/op            1431 B/op         19 allocs/op
BenchmarkOrmInsert-4                 375          16094181 ns/op           13168 B/op        228 allocs/op
BenchmarkRawInsertMulti-4            182          34590632 ns/op          107896 B/op        415 allocs/op
BenchmarkDBInsertMulti-4             169          32759844 ns/op          163915 B/op        443 allocs/op
BenchmarkOrmInsertMulti-4            202          42330407 ns/op         1619811 B/op       1274 allocs/op
BenchmarkRawRead-4                  1363           4904510 ns/op            1811 B/op         47 allocs/op
BenchmarkDBRead-4                   1022           5231108 ns/op            3287 B/op         69 allocs/op
BenchmarkOrmRead-4                  1094           5087548 ns/op            6327 B/op        223 allocs/op
BenchmarkRawReadSlice-4              885           6727643 ns/op           23088 B/op       1331 allocs/op
BenchmarkDBReadSlice-4               985           6081792 ns/op           24781 B/op       1353 allocs/op
BenchmarkOrmReadSlice-4              932           7352789 ns/op            6324 B/op        223 allocs/op

Note:

• Raw refers to database/sql
• DB refers to sqlx
• Orm refers to the ORM layer I'm currently building