Just a few weeks until the 2021 JavaScript Full-Stack Bootcamp opens.
Signup to the waiting list!
In this article I list how to do common SQL database operations with Go.
- Introducing
database/sql
- Open the database connection
- Close the database connection
- Extract data from the database
Introducing database/sql
Go offers a clean SQL database API in its standard library database/sql
package, but the specific database drivers must be installed separately.
It’s a smart approach because it provides a common interface that nearly every DB driver implements.
If you want to use MySQL, you can use https://github.com/go-sql-driver/mysql.
If you’re using PostgreSQL, use https://github.com/lib/pq.
You just need to include the lib using import _
, and the database/sql
API will be configured to enable that driver:
import "database/sql"
import _ "github.com/go-sql-driver/mysql"
Open the database connection
Although the goal is to have it abstracted, there are still differences in some things, for example in how you connect to a database:
import "database/sql"
import _ "github.com/go-sql-driver/mysql"
//...
db, err := sql.Open("mysql", "theUser:thePassword@/theDbName")
if err != nil {
panic(err)
}
import "database/sql"
import _ "github.com/lib/pq"
//...
db, err := sql.Open("postgres", "user=theUser dbname=theDbName sslmode=verify-full")
if err != nil {
panic(err)
}
but much of the actual API is db-independent, and can be interchanged quite easily (not talking about SQL here, just referring to the database API).
Close the database connection
Where it makes sense, you should always close the database connection.
You can as usual use defer
to close it when the function that opens the db connection ends:
db, err := sql.Open("postgres", psqlInfo)
defer db.Close()
Extract data from the database
Select a single row
Querying a table is done in 2 steps. First you call db.QueryRow()
, then you call Scan()
on the result.
Example:
id := 1
var col string
sqlStatement := `SELECT col FROM my_table WHERE id=$1`
row := db.QueryRow(sqlStatement, id)
err := row.Scan(&col)
if err != nil {
if err == sql.ErrNoRows {
fmt.Println("Zero rows found")
} else {
panic(err)
}
}
db.QueryRow()
is used to query a single value from a table.
Signature:
func (db *DB) QueryRow(query string, args ...interface{}) *Row
It returns a pointer to a db.Row
value.
(*Row) Scan
scans the row, copying the column values to the parameter passed into it.
Signature:
func (r *Row) Scan(dest ...interface{}) error
If more than one row was returned, it only scans the first one and ignore the rest.
If no row was returned, it returns a ErrNoRows
error.
var ErrNoRows = errors.New("sql: no rows in result set")
Select multiple rows
To query a single row we used db.QueryRow()
. To query multiple rows we use db.Query()
, which returns a *Rows
value.
From the docs:
//Rows is the result of a query. Its cursor starts before the first row of the result set. Use Next to advance through the rows:
rows, err := db.Query("SELECT ...")
...
defer rows.Close()
for rows.Next() {
var id int
var name string
err = rows.Scan(&id, &name)
...
}
err = rows.Err() // get any error encountered ing iteration
...
// Err returns the error, if any, that was encountered during iteration. // Err may be called after an explicit or implicit Close.
We need to iterate on rows.Next()
, which allows us to call rows.Scan()
into the loop.
If any error arises when preparing the next row, the loop ends and we can get the error by calling rows.Err()
:
type Timeline struct {
Id int
Content string
}
rows, err := db.Query(`SELECT id, content FROM timeline`)
if err != nil {
panic(err)
}
defer rows.Close()
for rows.Next() {
timeline := Timeline{}
err = rows.Scan(&timeline.Id, &timeline.Content)
if err != nil {
panic(err)
}
fmt.Println(timeline)
}
err = rows.Err()
if err != nil {
panic(err)
}
The 2021 JavaScript Full-Stack Bootcamp will start at the end of March 2021. Don't miss this opportunity, signup to the waiting list!
More go tutorials:
- Using NGINX Reverse Proxy to serve Go services
- Making a copy of a struct in Go
- The basics of a Go Web Server
- Sorting a map type in Go
- Go pointers in a nutshell
- Go Tags explained
- Go Date and Time Formatting
- JSON processing with Go
- Go Variadic Functions
- Go Strings Cheat Sheet
- The Go Empty Interface Explained
- Debugging Go with VS Code and Delve
- Named Go returns parameters
- Generating random numbers and strings in Go
- Filesystem Structure of a Go project
- Binary Search Algorithm Implemented in Go
- Using Command Line Flags in Go
- GOPATH Explained
- Build a Command Line app with Go: lolcat
- Building a CLI command with Go: cowsay
- Using Shell Pipes with Go
- Go CLI tutorial: fortune clone
- List the files in a folder with Go
- Use Go to get a list of repositories from GitHub
- Go, append a slice of strings to a file
- Go, convert a string to a bytes slice
- Visualize your local Git contributions with Go
- Getting started with Go CPU and memory profiling
- Solving the "does not support indexing" error in a Go program
- Measuring execution time in a Go program
- Building a Web Crawler with Go to detect duplicate titles
- Go Best Practices: Pointer or value receivers?
- Go Best Practices: Should you use a method or a function?
- Go Data Structures: Set
- Go Maps Cheat Sheet
- Generate implementations for generic types in Go
- Go Data Structures: Dictionary
- Go Data Structures: Hash Table
- Implement Events Listeners in Go through Channels
- Go Data Structures: Stack
- Go Data Structures: Queue
- Go Data Structures: Binary Search Tree
- Go Data Structures: Graph
- Go Data Structures: Linked List
- The complete guide to Go Data Structures
- Comparing Go Values
- Is Go object oriented?
- Working with a SQL Database in Go
- Using environment variables in Go
- Go tutorial: REST API backed by PostgreSQL
- Enabling CORS on a Go Web Server
- Deploying a Go Application in a Docker Container
- Why Go is a powerful language to learn as a PHP developer
- Go, remove the io.Reader.ReadString newline char
- Go, how to watch changes and rebuild your program
- Go, count the months since a date
- Accessing HTTP POST parameters in Go