Database IO v0.1.3

Import driver

Database interactions are defined by package database/sql. It inturn relies on you importing a driver package for the database you will be working with.

import (
    "database/sql"
    _ "github.com/mattn/go-sqlite3"
)

Note that the driver is imported into '_' since we won't be using it directly. Should you forget to import a driver, your application will panic.

$ go run main_sql.go
panic: sql: unknown driver "sqlite3" (forgotten import?)

Construct DB handle

Having a loaded driver we are set to open a database handle *sql.DB. The handle is a pool of connections so you don't have to worry about reusing database connections.

var db *sql.DB
if db, err = sql.Open("sqlite3", "/tmp/test.db"); err != nil {
    panic(err)
}

Reuse the db handle throughout your application, only close it when you exit. It's meant to live long and prosper.

Define your queries

In this example we'll be working with account data. Making it slightly more readable lets define them in a struct.

var accounts = struct {
    CREATE, INSERT, SELECT string
}{
    `CREATE TABLE accounts (
        username VARCHAR(64) NULL,
        email VARCHAR(256) NULL
    )`,
    "INSERT INTO accounts(username, email) values(?,?)",
    "SELECT * FROM accounts",
}

Create table

Creating tables involves calling the Exec method on our connection. We'll do just a tiny bit of error checking and accept errors telling us the table already exists.


if _, err = db.Exec(accounts.CREATE); err != nil {
    if !strings.Contains(err.Error(), "already exists") {
        panic(err)
    }
}

Since we haven't written our query in a way that works even if the table already exists the err is checked for acceptable content. Note that error messages could differ between drivers so it's not bullet proof. You would probably want to rewrite your query to contain a IF NOT EXISTS.

Insert row

We'll secure our query by using a prepared statement, minimizing SQL injection problems. The placeholders are driver specific, and in our case of sqlite3 we're using the '?' character.

var stmt *sql.Stmt
if stmt, err = db.Prepare(accounts.INSERT); err != nil {
    panic(err)
}
defer stmt.Close()
if _, err = stmt.Exec("Ben kenobi", "ben@council.com"); err != nil {
    panic(err)
}

Make sure you close the prepared statement After you inserted all the rows. Do not prepare it for each row you want to insert.

Select rows

var rows *sql.Rows
if rows, err = db.Query(accounts.SELECT); err != nil {
    panic(err)
}
defer rows.Close()

Once you have rows, scan column values into their corresponding types, the number of values must be the same as the number of columns in your SELECT query.

var username, email string
for rows.Next() {
    if err = rows.Scan(&username, &email); err != nil {
	panic(err)
    }
}

Note how we use pointers to the variables in the call to rows.Scan.

And those are the basics of database input and output operations in Go.