Now that we have both subcommands and the ability to pass in data to our CMS application through the use of the pFlag package or through the use of Cobra flags, in this lesson we're going to start taking a look at how we can save that data into a database.

If you follow along with my content then you know that I'm a big fan of SQL and one of the greatest things about Go or the Go Standard Library is that it provides an interface in order to be able to work with SQL databases.

This is through the database/sql package of the standard library, which provides a generic interface around SQL or SQL-like databases.

Database Drivers

One thing to be aware of however is that this package itself doesn't actually provide any connections to SQL databases, it just provides a generic interface that other SQL drivers can conform to, which actually has a few benefits.

However, this means that in order to be able to connect to a SQL database, we need to use this package in conjunction with a driver.

What is a driver? A driver is basically an implementation of the generic interfaces and types that are provided by the database/sql package.

There are quite a few drivers out there. If we take a look at this link, we can actually see a list of all of the drivers that have been created by third-party providers for a number of different database types, including:

• AWS Athena
• DynamoDB
• ClickHouse
• CockroachDB
• SQLite
• PostgreSQL

Choosing the Right Driver

How do you know which one to choose?

A good rule of thumb is to choose a driver that has an asterisk. Drivers marked with an asterisk are both included in and past the compatibility test suite, which ensures that the driver matches the Go standard library expectations.

For SQLite: github.com/mattn/go-sqlite3 ✅

For PostgreSQL:

• github.com/lib/pq ✅ (more dedicated towards being a driver)
• github.com/jackc/pgx ✅ (more modern implementation, my preference)

Driver Characteristics

PostgreSQL drivers (lib/pq, pgx):

• Implemented using Pure Go
• Very portable and easy to include
• Easy to build for other architectures

SQLite driver (go-sqlite3):

• Uses CGO (which causes some issues)
• There are Pure Go implementations, but these have caveats
• More complex for cross-compilation

Setting Up SQLite

Prerequisites

First, ensure you have SQLite3 installed on your system:

macOS:

brew install sqlite3

Linux (Ubuntu/Debian):

sudo apt-get install sqlite3

Linux (Arch):

sudo pacman -S sqlite

NixOS:

nix-shell -p sqlite

Installing the Go SQLite Driver

go get github.com/mattn/go-sqlite3

As go-sqlite3 is a CGO package, you need to ensure:

• GCC is enabled
• CGO_ENABLED=1 environment variable

CGO Linking Issues (macOS)

On macOS, you might encounter linking issues. To resolve this, set the CGO_LDFLAGS:

export CGO_LDFLAGS="-L$(xcrun --show-sdk-path)/usr/lib -L/usr/lib"
go run .

Basic Database Operations

Importing and Connecting

package main

import (
    "context"
    "database/sql"
    "log"
    "os"
    "os/signal"

    _ "github.com/mattn/go-sqlite3" // Import driver with blank identifier
)

func main() {
    // Create context with signal handling
    ctx, cancel := signal.NotifyContext(context.Background(), os.Interrupt)
    defer cancel()

    // Open database connection
    db, err := sql.Open("sqlite3", "db.sql")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close() // Always Be Closing (ABC)

    fmt.Println("Database connected")
}

Key Points:

• Use blank identifier (_) when importing the driver
• The driver registers itself using the init() function
• Driver name: "sqlite3"
• Data source name: "db.sql" (creates a file in the local directory)

Creating Tables (Exec)

const createQuery = `
CREATE TABLE IF NOT EXISTS posts (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL
)`

_, err = db.ExecContext(ctx, createQuery)
if err != nil {
    log.Fatal(err)
}

ExecContext is used for:

• SQL commands that don't return rows
• CREATE, INSERT, UPDATE, DELETE statements
• Returns sql.Result and error

Inserting Data

const insertPost = `INSERT INTO posts (title) VALUES (?)`

title := "My First Post"
_, err = db.ExecContext(ctx, insertPost, title)
if err != nil {
    log.Fatal(err)
}

Key Points:

• Use ? placeholders for parameters (SQLite style)
• Pass arguments as variadic parameters
• Arguments are filled in the same order as placeholders

Querying Single Row (QueryRow)

const selectPost = `SELECT id, title FROM posts WHERE id = ?`

row := db.QueryRowContext(ctx, selectPost, 1)

// Check for query errors
if row.Err() != nil {
    log.Fatal(row.Err())
}

// Scan into variables
var id int
var title string
err = row.Scan(&id, &title)
if err != nil {
    log.Fatal(err)
}

fmt.Printf("ID: %d\n", id)
fmt.Printf("Title: %s\n", title)

Important:

• Be explicit with column names instead of SELECT *
• Scan order must match the SELECT order
• Pass pointers to Scan method

Querying Multiple Rows (Query)

const selectAllPosts = `SELECT id, title FROM posts`

rows, err := db.QueryContext(ctx, selectAllPosts)
if err != nil {
    log.Fatal(err)
}
defer rows.Close() // ABC - Always Be Closing

// Iterate through rows
for rows.Next() {
    var id int
    var title string
    
    err := rows.Scan(&id, &title)
    if err != nil {
        log.Fatal(err)
    }
    
    fmt.Printf("ID: %d, Title: %s\n", id, title)
}

Key Points:

• Use rows.Next() to iterate (similar to bufio.Scanner)
• Always defer rows.Close()
• Scan each row individually

Three Main Database Functions

1. ExecContext - Execute SQL without expecting rows back (CREATE, INSERT, UPDATE, DELETE)
2. QueryContext - Execute SQL that returns multiple rows
3. QueryRowContext - Execute SQL that returns exactly one row

Best Practice: Always use the Context versions of these functions for proper timeout and cancellation handling.

Switching to PostgreSQL

The beauty of the database/sql package is that switching databases requires minimal code changes:

import (
    _ "github.com/lib/pq" // PostgreSQL driver
)

// Change connection
db, err := sql.Open("postgres", "host=localhost dbname=postgres sslmode=disable")

PostgreSQL Connection String Formats:

1. Key-Value Format:

   host=localhost dbname=postgres user=username password=password sslmode=disable

2. URI Format: (more common)

   postgres://username:password@localhost/dbname?sslmode=disable

Testing Your Database

You can verify your SQLite database using the SQLite CLI:

sqlite3 db.sql

# Inside SQLite CLI:
.tables                    # List all tables
SELECT * FROM posts;       # Query data
.quit                      # Exit

Homework Assignment

Add database support to your CMS application with the following features:

1. Database Setup

• Choose either SQLite or PostgreSQL
• Create your database tables (recommend using CLI initially)
• Add database connection to your application

2. Implement CRUD Operations

Create Command:

./cms posts create --title "My First Post"

Update Command:

./cms posts update --id 3 --title "My New Title"

Delete Command:

./cms posts delete --id 3

Get Command:

./cms posts get 3  # Get post with ID 3

List Command:

./cms posts list   # List all posts

3. Example SQL Queries

Create Table:

CREATE TABLE IF NOT EXISTS posts (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    content TEXT,
    author TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

Insert:

INSERT INTO posts (title, content, author) VALUES (?, ?, ?)

Update:

UPDATE posts SET title = ? WHERE id = ?

Delete:

DELETE FROM posts WHERE id = ?

Select One:

SELECT id, title, content, author, created_at FROM posts WHERE id = ?

Select All:

SELECT id, title, content, author, created_at FROM posts ORDER BY created_at DESC

4. Bonus Features

• Use the tabwriter package for formatted output in list command
• Add proper error handling and validation
• Consider adding flags for different sorting options

Important Notes

⚠️ Don't get too attached to this code! We'll be refactoring it in upcoming lessons when we introduce:

• Database migrations (next lesson)
• SQLC package (makes SQL operations much easier)

Key Takeaways

• database/sql provides a generic interface for SQL databases
• Drivers implement database-specific functionality
• Always use Context methods for proper cancellation
• Use parameterized queries to prevent SQL injection
• SQLite is great for local/portable applications
• PostgreSQL is better for distributed/cloud applications
• The interface abstraction makes switching databases easy

Next Lesson: Database Migrations - Learn how to manage database schema changes properly.