SQLite with Go
You are building a CLI tool that tracks expenses. You want the data to survive between runs. You could dump everything to a JSON file, but querying "show me all expenses over $50 in March" turns into a manual parsing nightmare. You could spin up PostgreSQL, but that requires a running server, credentials, and network configuration for a tool that should just work. SQLite sits in the middle. It is a database engine that lives inside your process. The database is a single file. It speaks SQL. It requires zero configuration. Go makes it straightforward to embed.
How database/sql and drivers work
Go provides the database/sql package, which defines a standard interface for database operations. It does not contain drivers. Drivers register themselves with database/sql at startup. You pick a driver, import it, and database/sql handles the rest. This design decouples your query logic from the database implementation. You can write code once and swap databases later by changing the driver and connection string.
The most common SQLite driver is github.com/mattn/go-sqlite3. It registers the driver name sqlite3. You import the driver using a blank import. The underscore tells the compiler you want the side effects of the import, specifically the init() function that registers the driver, but you don't need to reference any symbols from the package directly.
import (
"database/sql"
_ "github.com/mattn/go-sqlite3" // registers the sqlite3 driver with database/sql
)
SQLite uses ? for query placeholders. database/sql normalizes placeholders for some drivers, but SQLite expects ?. If you pass $1 or %s, the driver may not translate them correctly. Stick to ? for SQLite.
database/sql is the standard. Drivers are plugins. Register the driver, use ?, and let the package manage the rest.
Minimal example
Here is the simplest way to open a SQLite database, create a table, insert a row, and read it back.
package main
import (
"database/sql"
"fmt"
_ "github.com/mattn/go-sqlite3" // registers driver
)
func main() {
// Open initializes the database handle. It does not verify the connection immediately.
db, err := sql.Open("sqlite3", "test.db")
if err != nil {
panic(err)
}
defer db.Close() // closes the connection pool when main exits
// Exec runs a write operation. SQLite uses ? for placeholders.
_, err = db.Exec("CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT)")
if err != nil {
panic(err)
}
_, err = db.Exec("INSERT INTO users (name) VALUES (?)", "Alice")
if err != nil {
panic(err)
}
}
The sql.Open function returns a *sql.DB. This is not a single connection. It is a connection pool. The pool manages opening and closing actual connections to the SQLite file. When you call Exec or Query, the pool hands you a connection. When you are done, the connection goes back. defer db.Close() closes the pool. If you forget this, the file might stay locked or resources leak.
Here is how you query rows and scan them into variables.
// Query returns a Rows object. You must close it to release resources.
rows, err := db.Query("SELECT id, name FROM users")
if err != nil {
panic(err)
}
defer rows.Close()
for rows.Next() {
var id int
var name string
// Scan maps columns to variables by position.
if err := rows.Scan(&id, &name); err != nil {
panic(err)
}
fmt.Println(id, name)
}
Query returns a *sql.Rows. This holds a cursor over the result set. You must close it. The compiler will not force you to close rows like it forces you to close files in some languages, but the runtime will leak if you don't. The for rows.Next() loop advances the cursor. Scan copies column values into variables. The order of variables must match the order of columns in the query.
Open the pool, defer the close, scan the rows.
Walkthrough: what happens at runtime
When you call sql.Open, Go creates the pool structure. It does not connect to the database yet. This is lazy initialization. The first call to Exec, Query, or Ping triggers the actual connection. If the file does not exist, SQLite creates it. If the file exists, SQLite opens it.
The pool tracks open connections. By default, it allows unlimited open connections. For SQLite, this can cause issues. SQLite writes lock the file. If multiple goroutines try to write simultaneously, you will get "database is locked" errors. The pool helps by reusing connections, but SQLite has concurrency limits. For write-heavy workloads, you should configure the pool.
// Limit to one open connection to serialize writes and avoid locking.
db.SetMaxOpenConns(1)
When you call Exec, the pool grabs a connection, sends the SQL, and returns the result. The connection goes back to the pool. When you call Query, the pool grabs a connection, sends the SQL, and returns a *sql.Rows. The connection stays borrowed until you close the rows. defer rows.Close() ensures the connection returns to the pool even if you return early.
If you pass the wrong type to a placeholder, the compiler catches it. The compiler rejects the program with cannot use "value" (untyped string constant) as int value in argument to db.Exec if you try to insert a string where an integer is expected.
Lazy connections save startup time. Configure the pool for SQLite constraints. Close rows to return connections.
Realistic example: wrapping the database
Real code usually wraps the database in a struct. This keeps queries organized and makes testing easier. You can swap the database implementation or mock it for tests.
type UserStore struct {
db *sql.DB
}
// FindByID retrieves a user by their ID.
func (s *UserStore) FindByID(id int) (string, error) {
var name string
// QueryRow is a shortcut for Query when you expect exactly one row.
err := s.db.QueryRow("SELECT name FROM users WHERE id = ?", id).Scan(&name)
if err != nil {
return "", err
}
return name, nil
}
The receiver name is usually one or two letters matching the type. (s *UserStore) is standard. FindByID returns (string, error). Errors always go last in Go return values. QueryRow is a convenience method. It runs the query and returns a *sql.Row, which is a wrapper around *sql.Rows for single results. You call Scan directly on the row. If the query returns no rows, Scan returns sql.ErrNoRows. You must check for this error.
Wrap the DB in a struct. Keep queries close to the data they touch.
Handling NULL values
SQLite allows NULL. Go variables do not. You cannot assign NULL to a string or int. You need a wrapper type. database/sql provides sql.NullString, sql.NullInt64, and sql.NullBool. These types have two fields: the value and a Valid boolean. Valid is true if the value is not NULL.
var name sql.NullString
err := s.db.QueryRow("SELECT name FROM users WHERE id = ?", 1).Scan(&name)
if err != nil {
return err
}
if name.Valid {
fmt.Println(name.String)
} else {
fmt.Println("Name is NULL")
}
If you scan a NULL into a plain string, Scan returns an error. The error message varies by driver, but it indicates a type mismatch. Always use sql.Null* types for columns that can be NULL. Check Valid before accessing the value. This prevents panics and silent data loss.
Go has no null. Use sql.Null* types. Check Valid before use.
Context and cancellation
Long queries can hang. You need a way to cancel them. database/sql supports context.Context. Use QueryContext and ExecContext instead of Query and Exec. Pass a context with a timeout or cancellation.
import "context"
ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
defer cancel()
rows, err := db.QueryContext(ctx, "SELECT * FROM heavy_table")
if err != nil {
return err
}
defer rows.Close()
context.Context always goes as the first parameter in functions that accept it. The convention is to name the variable ctx. Functions that take a context should respect cancellation and deadlines. If the context is cancelled, the query stops and returns an error. This prevents goroutine leaks and resource exhaustion.
Context is plumbing. Run it through every long-lived call site.
The CGO dependency
The go-sqlite3 driver uses CGO. It wraps the SQLite C library. This means you need a C compiler to build your program. If you are on macOS or Windows, you might need to install gcc or a compatible toolchain. Cross-compilation is harder. If you build on macOS and deploy to Linux, you need a Linux C compiler or a cross-compilation setup.
If you want to avoid CGO, use modernc.org/sqlite. This is a pure Go implementation of SQLite. It has no C dependencies. Builds are simpler. Cross-compilation works out of the box. Performance is comparable for most workloads.
import (
"database/sql"
_ "modernc.org/sqlite" // pure Go SQLite driver
)
The driver name is sqlite. The API is the same. You can swap drivers by changing the import and driver name.
CGO adds build complexity. Use pure Go drivers if you need simple builds or cross-compilation.
Pitfalls and errors
The most common runtime surprise is sql.ErrNoRows. If you use QueryRow and the query matches nothing, Scan returns this error. You must check for it. The compiler will not help here. Another trap is concurrency. SQLite writes lock the file. If you have multiple goroutines writing simultaneously, you will get "database is locked" errors. The *sql.DB pool helps, but SQLite has limits. For write-heavy workloads, set db.SetMaxOpenConns(1) to serialize writes, or enable WAL mode in the connection string.
// Enable Write-Ahead Logging for better concurrency.
db, err := sql.Open("sqlite3", "test.db?_journal_mode=WAL")
WAL mode allows readers and writers to proceed concurrently. It improves performance for mixed workloads.
If you forget to import a package, the compiler rejects the program with undefined: sql. If you import a package but don't use it, you get imported and not used. The blank import _ suppresses the unused error because you are importing for side effects.
Check ErrNoRows. Serialize writes if you hit locks. Use WAL for concurrency.
Decision matrix
Use SQLite when you need SQL queries, ACID transactions, and zero configuration for a single-process application.
Use a client-server database like PostgreSQL when multiple processes or machines need to share the same data simultaneously.
Use a key-value store like BoltDB when you only need simple get/set operations and want to avoid the overhead of SQL parsing.
Use an in-memory map when the data is transient and performance is the only concern.
Use database/sql with a different driver when you want to write code that can swap databases later without changing query logic.
Pick the tool that matches your concurrency needs.