Go Database SQL — SQL Database Operations with database/sql and Drivers
In this tutorial, you will learn about Go Database SQL. We cover key concepts, practical examples, and best practices to help you master this topic.
Go database/sql package provides generic SQL database interface with connection pooling, prepared statements, transactions, and row scanning.
What You'll Learn
- Opening database connections
- Querying with Query and QueryRow
- Executing with Exec
- Transactions and prepared statements
Why It Matters
Database access is essential for applications. Docker uses databases for image metadata. Kubernetes uses etcd. DodaZIP uses PostgreSQL for file processing metadata.
Real-World Use
Web application backends, data pipelines, ETL processes, analytics platforms.
flowchart LR
A["database/sql"] --> B["Open/Ping"]
A --> C["Query/QueryRow"]
A --> D["Exec"]
A --> E["Transactions"]
A:::current --> B
style A fill:#2563eb,stroke:#2563eb,color:#fff
style B fill:#dbeafe,stroke:#2563eb,color:#1e40af
style C fill:#dbeafe,stroke:#2563eb,color:#1e40af
style D fill:#dbeafe,stroke:#2563eb,color:#1e40af
style E fill:#f1f5f9,stroke:#94a3b8,color:#64748b
Opening Connection
import (
"database/sql"
_ "github.com/lib/pq"
)
type DB struct {
*sql.DB
}
func NewDB() (*DB, error) {
dsn := "host=localhost port=5432 user=postgres password=pass dbname=app sslmode=disable"
db, err := sql.Open("postgres", dsn)
if err != nil {
return nil, err
}
db.SetMaxOpenConns(25)
db.SetMaxIdleConns(5)
db.SetConnMaxLifetime(5 * time.Minute)
if err := db.Ping(); err != nil {
return nil, err
}
return &DB{db}, nil
}
Querying Data
type User struct {
ID int `json:"id"`
Name string `json:"name"`
Email string `json:"email"`
CreatedAt time.Time `json:"created_at"`
}
func (db *DB) GetUser(id int) (*User, error) {
query := "SELECT id, name, email, created_at FROM users WHERE id = $1"
var u User
err := db.QueryRow(query, id).Scan(&u.ID, &u.Name, &u.Email, &u.CreatedAt)
if err == sql.ErrNoRows {
return nil, nil
}
if err != nil {
return nil, err
}
return &u, nil
}
func (db *DB) ListUsers() ([]User, error) {
query := "SELECT id, name, email FROM users ORDER BY id"
rows, err := db.Query(query)
if err != nil {
return nil, err
}
defer rows.Close()
var users []User
for rows.Next() {
var u User
if err := rows.Scan(&u.ID, &u.Name, &u.Email); err != nil {
return nil, err
}
users = append(users, u)
}
return users, rows.Err()
}
Inserting Data
func (db *DB) CreateUser(name, email string) (*User, error) {
query := "INSERT INTO users (name, email) VALUES ($1, $2) RETURNING id, created_at"
var u User
u.Name = name
u.Email = email
err := db.QueryRow(query, name, email).Scan(&u.ID, &u.CreatedAt)
if err != nil {
return nil, err
}
return &u, nil
}
Updates and Deletes
func (db *DB) UpdateUser(id int, name string) error {
query := "UPDATE users SET name = $1 WHERE id = $2"
result, err := db.Exec(query, name, id)
if err != nil {
return err
}
rows, _ := result.RowsAffected()
if rows == 0 {
return fmt.Errorf("user %d not found", id)
}
return nil
}
func (db *DB) DeleteUser(id int) error {
query := "DELETE FROM users WHERE id = $1"
result, err := db.Exec(query, id)
if err != nil {
return err
}
rows, _ := result.RowsAffected()
if rows == 0 {
return fmt.Errorf("user %d not found", id)
}
return nil
}
Transactions
func (db *DB) TransferFunds(from, to int, amount float64) error {
tx, err := db.Begin()
if err != nil {
return err
}
defer tx.Rollback()
_, err = tx.Exec("UPDATE accounts SET balance = balance - $1 WHERE id = $2", amount, from)
if err != nil { return err }
_, err = tx.Exec("UPDATE accounts SET balance = balance + $1 WHERE id = $2", amount, to)
if err != nil { return err }
return tx.Commit()
}
Common Mistakes
1. Not Closing Rows
rows, _ := db.Query("SELECT * FROM users")
// defer rows.Close() // Missing! Connection leak
2. Ignoring sql.ErrNoRows
err := db.QueryRow("SELECT id FROM users WHERE id = $1", 999).Scan(&id)
// err == sql.ErrNoRows is not an error — handle it
3. SQL Injection
// Bad: fmt.Sprintf("SELECT * FROM users WHERE name = '%s'", name)
// Good: Use parameterized queries ($1, $2, etc.)
4. Not Checking Rows.Err()
for rows.Next() { /* scan */ }
// Missing: if err := rows.Err(); err != nil { /* handle */ }
5. Forgetting Rollback
Always defer tx.Rollback(). If Commit succeeds, Rollback is a no-op.
Practice Questions
1. What drivers does database/sql support? PostgreSQL (lib/pq, pgx), MySQL (go-sql-driver/mysql), SQLite (mattn/go-sqlite3). Each registers itself via init().
2. What is connection pooling? database/sql maintains a pool of connections. SetMaxOpenConns limits concurrent connections. SetMaxIdleConns keeps idle connections ready.
3. How do prepared statements prevent SQL injection? Parameters are sent separately from the SQL template. Database handles escaping. Never concatenate user input into SQL.
4. What is the difference between Query and QueryRow? Query returns multiple rows (Rows Iterator). QueryRow returns at most one row. QueryRow includes ErrNoRows handling.
Challenge: Implement a function that batch inserts users using a Transaction.
Solution
func (db *DB) BulkInsert(users []User) error {
tx, _ := db.Begin()
defer tx.Rollback()
stmt, _ := tx.Prepare("INSERT INTO users (name, email) VALUES ($1, $2)")
defer stmt.Close()
for _, u := range users {
if _, err := stmt.Exec(u.Name, u.Email); err != nil {
return err
}
}
return tx.Commit()
}
FAQ
{{< faq question="Should I use database/sql or an ORM?" >}} database/sql for control and performance. ORMs (GORM, Ent) for productivity. Start with database/sql, add ORM when object mapping complexity grows. {{< /faq >}}
{{< faq question="What is the $1 syntax?" >}} Parameter placeholder. PostgreSQL uses $1, $2. MySQL uses ?. The driver handles the convention. {{< /faq >}}
{{< faq question="How do I handle NULL values?" >}} Use sql.NullString, sql.NullInt64, sql.NullTime for nullable columns. Check Valid field before using Value. {{< /faq >}}
{{< faq question="What is db.Ping for?" >> Tests the connection is alive. Used in startup health checks. Not automatic — connections may fail silently. {{< /faq >}}
{{< faq question="How do I migrate schemas?" >}} Use Migration tools: golang-migrate/migrate, pressly/goose. They version control database schema changes. {{< /faq >}}
Try It Yourself
package main
import (
"database/sql"
"fmt"
"log"
)
func main() {
db, err := sql.Open("sqlite3", ":memory:")
if err != nil { log.Fatal(err) }
defer db.Close()
db.Exec("CREATE TABLE items (id INTEGER PRIMARY KEY, name TEXT)")
db.Exec("INSERT INTO items (name) VALUES ('hello')")
var name string
db.QueryRow("SELECT name FROM items WHERE id = ?", 1).Scan(&name)
fmt.Println(name)
}
Expected output:
hello
What's Next
Now that you understand database operations, explore GORM for ORM-based database access.
| Topic | Description | Link |
|---|---|---|
| Go GORM | ORM for Go | {{< ref "30-gorm" >}} |
| Go HTTP Servers | Building web servers | {{< ref "27-http-server" >}} |
| Go Generics | Type parameters | {{< ref "31-generics" >}} |