Go has really great sql support using it's database/sql package. It essentially provides a standard interface between your go application and the world of sql databases. There are of course some quirks and inconsistencies.
When using a specific database you'll need to select a database driver for the
specific database you're implementing:
https://github.com/golang/go/wiki/SQLDrivers
Generally, you just need to blank import the database driver to use it's init
function, but read the documentation to be certain. Most people tend to use
these slightly lower level database connection interfaces. If you're in an
absolute rush and need something like an ORM these two packages seem decent:
https://github.com/jmoiron/sqlx
https://github.com/blockloop/scan
Null values
One tricky bit is that SQL databases can have null
values and go generally
cannot. Say you have a table with a "title" column and one of the rows is set to
null
, parsing it to a string with rows.Scan()
will fail:
sql: Scan error on column index 1: unsupported Scan, storing driver.Value type
<nil> into type *string
One solution is to use sql.NullString
instead of string, but as a rule the
best thing to do is avoid NULL
altogether. Set NOT NULL
constraints on all
your database columns and use sensible DEFAULT
values as necessary.
Transactions
Calls to Exec()
, Query()
, and QueryRow()
can use any connection from the
sql.DB
pool. Even if you have two calls immediately next to each other there
is no guarantee that they will use the same database connection. Sometimes this
isn't acceptable and you need them to be called in a particular order. To
guarantee the same connection is used you can wrap the statements in a
"transation" like so:
type ExampleModel struct {
DB *sql.DB
}
func (m *ExampleModel) ExampleTransaction() error {
tx, err := m.DB.Begin()
if err != nil {
return err
}
defer tx.Rollback()
_, err = tx.Exec("UPDATE ...")
if err != nil {
return err
}
err = tx.Commit()
return err
}
Transactions are super useful if you want to execute multiple SQL statements in
a single atomic action. You must always call either Rollback
or Commit
before your function returns. If you don't the connection will stay open and
count towards your maximum connection limit.