SQLite is a lightweight, serverless database that pairs perfectly with Go's simplicity and performance. Whether you're building a CLI tool, desktop application, or need an embedded database solution, the combination of Go and SQLite offers remarkable flexibility and ease of deployment.
In this comprehensive guide, we'll explore everything you need to know about integrating SQLite with Go, from basic operations to advanced patterns and best practices.
Why Choose SQLite with Go?
SQLite brings several advantages to Go applications:
- Zero Configuration: No server setup or administration required
- Single File Database: Perfect for portable applications
- ACID Compliance: Full transaction support with reliability
- Cross-Platform: Works seamlessly across different operating systems
- Embedded Nature: Ideal for desktop apps, CLIs, and edge computing
- Performance: Excellent read performance for most use cases
Getting Started
Installing the SQLite Driver
Go doesn't include SQLite support in its standard library, so we need a third-party driver. The most popular and mature option is go-sqlite3:
go mod init sqlite-example
go get github.com/mattn/go-sqlite3Basic Connection Example
package main
import (
"database/sql"
"fmt"
"log"
_ "github.com/mattn/go-sqlite3"
)
func main() {
// Open database connection
db, err := sql.Open("sqlite3", "./example.db")
if err != nil {
log.Fatal(err)
}
defer db.Close()
// Test the connection
err = db.Ping()
if err != nil {
log.Fatal(err)
}
fmt.Println("Successfully connected to SQLite database!")
}Core Database Operations (CRUD)
Creating Tables and Schema Management
Create a users table with unique constraints and timestamp columns so you can audit changes over time. Use IF NOT EXISTS to make the migration idempotent.
func createUserTable(db *sql.DB) error {
query := `
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
username TEXT UNIQUE NOT NULL,
email TEXT UNIQUE NOT NULL,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);`
_, err := db.Exec(query)
return err
}Inserting Data with Prepared Statements
Insert users safely using placeholders to prevent SQL injection. Return the new row’s ID for downstream use.
type User struct {
ID int64 `json:"id"`
Username string `json:"username"`
Email string `json:"email"`
}
func insertUser(db *sql.DB, user User) (int64, error) {
query := `INSERT INTO users (username, email) VALUES (?, ?)`
result, err := db.Exec(query, user.Username, user.Email)
if err != nil {
return 0, err
}
return result.LastInsertId()
}Querying Data Effectively
Provide focused read paths: fetch one record by ID with clear “not found” handling, or list all users in recent-first order.
func getUserByID(db *sql.DB, id int64) (*User, error) {
query := `SELECT id, username, email FROM users WHERE id = ?`
var user User
row := db.QueryRow(query, id)
err := row.Scan(&user.ID, &user.Username, &user.Email)
if err != nil {
if err == sql.ErrNoRows {
return nil, fmt.Errorf("user with ID %d not found", id)
}
return nil, err
}
return &user, nil
}
func getAllUsers(db *sql.DB) ([]User, error) {
query := `SELECT id, username, email FROM users ORDER BY created_at DESC`
rows, err := db.Query(query)
if err != nil {
return nil, err
}
defer rows.Close()
var users []User
for rows.Next() {
var user User
err := rows.Scan(&user.ID, &user.Username, &user.Email)
if err != nil {
return nil, err
}
users = append(users, user)
}
return users, rows.Err()
}Updating and Deleting Records
Update and delete with row-count checks so you know the change actually happened. Update the updated_at timestamp automatically.
func updateUser(db *sql.DB, id int64, username, email string) error {
query := `
UPDATE users
SET username = ?, email = ?, updated_at = CURRENT_TIMESTAMP
WHERE id = ?`
result, err := db.Exec(query, username, email, id)
if err != nil {
return err
}
rowsAffected, err := result.RowsAffected()
if err != nil {
return err
}
if rowsAffected == 0 {
return fmt.Errorf("user with ID %d not found", id)
}
return nil
}
func deleteUser(db *sql.DB, id int64) error {
query := `DELETE FROM users WHERE id = ?`
result, err := db.Exec(query, id)
if err != nil {
return err
}
rowsAffected, err := result.RowsAffected()
if err != nil {
return err
}
if rowsAffected == 0 {
return fmt.Errorf("user with ID %d not found", id)
}
return nil
}Transaction Management
SQLite transactions are crucial for data integrity, especially when performing multiple related operations:
func transferUserData(db *sql.DB, fromUserID, toUserID int64) error {
// Begin transaction
tx, err := db.Begin()
if err != nil {
return err
}
// Ensure transaction is rolled back on error
defer func() {
if err != nil {
tx.Rollback()
return
}
err = tx.Commit()
}()
// Perform multiple operations within transaction
_, err = tx.Exec(`UPDATE user_stats SET points = points - 100 WHERE user_id = ?`, fromUserID)
if err != nil {
return err
}
_, err = tx.Exec(`UPDATE user_stats SET points = points + 100 WHERE user_id = ?`, toUserID)
if err != nil {
return err
}
_, err = tx.Exec(`INSERT INTO transactions (from_user, to_user, amount, type) VALUES (?, ?, 100, 'transfer')`, fromUserID, toUserID)
if err != nil {
return err
}
return nil
}Advanced Patterns and Best Practices
Database Connection Management
Wrap the driver in a small Database type that initializes one SQLite connection, applies conservative pooling, and runs idempotent migrations at boot.
type Database struct {
db *sql.DB
}
func NewDatabase(dbPath string) (*Database, error) {
db, err := sql.Open("sqlite3", dbPath)
if err != nil {
return nil, err
}
// Configure connection pool
db.SetMaxOpenConns(1) // SQLite works best with single connection
db.SetMaxIdleConns(1)
db.SetConnMaxLifetime(0)
if err = db.Ping(); err != nil {
return nil, err
}
return &Database{db: db}, nil
}
func (d *Database) Close() error {
return d.db.Close()
}
func (d *Database) Migrate() error {
migrations := []string{
`CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
username TEXT UNIQUE NOT NULL,
email TEXT UNIQUE NOT NULL,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
)`,
`CREATE INDEX IF NOT EXISTS idx_users_email ON users(email)`,
`CREATE INDEX IF NOT EXISTS idx_users_username ON users(username)`,
}
for _, migration := range migrations {
if _, err := d.db.Exec(migration); err != nil {
return fmt.Errorf("migration failed: %v", err)
}
}
return nil
}Repository Pattern Implementation
Hide SQL behind a repository so business logic doesn’t depend on queries; this makes testing and refactors safer.
type UserRepository struct {
db *sql.DB
}
func NewUserRepository(db *sql.DB) *UserRepository {
return &UserRepository{db: db}
}
func (r *UserRepository) Create(ctx context.Context, user User) (*User, error) {
query := `INSERT INTO users (username, email) VALUES (?, ?) RETURNING id, created_at`
err := r.db.QueryRowContext(ctx, query, user.Username, user.Email).Scan(&user.ID, &user.CreatedAt)
if err != nil {
return nil, err
}
return &user, nil
}
func (r *UserRepository) FindByID(ctx context.Context, id int64) (*User, error) {
query := `SELECT id, username, email, created_at FROM users WHERE id = ?`
var user User
err := r.db.QueryRowContext(ctx, query, id).Scan(&user.ID, &user.Username, &user.Email, &user.CreatedAt)
if err != nil {
if err == sql.ErrNoRows {
return nil, nil // Not found
}
return nil, err
}
return &user, nil
}Performance Optimization Tips
Enable WAL Mode for Better Concurrency
WAL lets readers proceed while a writer appends to the log, improving Golang concurrency for desktop/CLI apps with mixed workloads.
func enableWALMode(db *sql.DB) error {
_, err := db.Exec("PRAGMA journal_mode=WAL;")
return err
}Optimize with Proper Indexing
-- Add indexes for frequently queried columns
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_created_at ON users(created_at);
-- Composite indexes for multi-column queries
CREATE INDEX idx_users_status_created ON users(status, created_at);Use Connection Pragmas for Performance
Apply safe, practical PRAGMAs: moderate sync level, larger cache, in-memory temp storage, WAL mode, and automatic checkpoints.
func configurePerformance(db *sql.DB) error {
pragmas := []string{
"PRAGMA synchronous = NORMAL;",
"PRAGMA cache_size = 1000000;",
"PRAGMA temp_store = MEMORY;",
"PRAGMA journal_mode = WAL;",
"PRAGMA wal_autocheckpoint = 1000;",
}
for _, pragma := range pragmas {
if _, err := db.Exec(pragma); err != nil {
return err
}
}
return nil
}Error Handling and Debugging
Custom Error Types
Wrap underlying SQLite errors with the operation name and allow unwrapping, so callers can check root causes while keeping messages readable.
type DatabaseError struct {
Operation string
Err error
}
func (e DatabaseError) Error() string {
return fmt.Sprintf("database %s failed: %v", e.Operation, e.Err)
}
func (e DatabaseError) Unwrap() error {
return e.Err
}
func handleSQLiteError(operation string, err error) error {
if err == nil {
return nil
}
if err == sql.ErrNoRows {
return DatabaseError{Operation: operation, Err: errors.New("record not found")}
}
return DatabaseError{Operation: operation, Err: err}
}Connection Monitoring
Periodically log pool stats to spot leaks and contention; keep the ticker scoped so it stops when your app shuts down.
func monitorDatabase(db *sql.DB) {
ticker := time.NewTicker(30 * time.Second)
defer ticker.Stop()
for range ticker.C {
stats := db.Stats()
log.Printf("DB Stats - Open: %d, InUse: %d, Idle: %d",
stats.OpenConnections,
stats.InUse,
stats.Idle,
)
}
}Testing Strategies
In-Memory Database for Tests
Spin up :memory: with a minimal schema per test and tear it down after—fast, deterministic, and perfect for repository/unit tests.
func setupTestDB(t *testing.T) *sql.DB {
db, err := sql.Open("sqlite3", ":memory:")
if err != nil {
t.Fatalf("Failed to create test database: %v", err)
}
// Create test schema
_, err = db.Exec(`
CREATE TABLE users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
username TEXT UNIQUE NOT NULL,
email TEXT UNIQUE NOT NULL,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
)
`)
if err != nil {
t.Fatalf("Failed to create test table: %v", err)
}
return db
}
func TestUserRepository_Create(t *testing.T) {
db := setupTestDB(t)
defer db.Close()
repo := NewUserRepository(db)
user := User{
Username: "testuser",
Email: "test@example.com",
}
created, err := repo.Create(context.Background(), user)
if err != nil {
t.Fatalf("Failed to create user: %v", err)
}
if created.ID == 0 {
t.Error("Expected user ID to be set")
}
if created.Username != user.Username {
t.Errorf("Expected username %s, got %s", user.Username, created.Username)
}
}Database File Management
Prepare the filesystem before opening the DB and provide a simple offline backup path by copying the database file.
func ensureDBDirectory(dbPath string) error {
dir := filepath.Dir(dbPath)
return os.MkdirAll(dir, 0755)
}
func backupDatabase(srcPath, destPath string) error {
src, err := os.Open(srcPath)
if err != nil {
return err
}
defer src.Close()
dst, err := os.Create(destPath)
if err != nil {
return err
}
defer dst.Close()
_, err = io.Copy(dst, src)
return err
}Conclusion
SQLite and Go form a powerful combination for applications requiring embedded database functionality. The simplicity of SQLite aligns perfectly with Go's philosophy, while providing robust data persistence without the complexity of client-server databases.
Key takeaways:
- Use prepared statements to prevent SQL injection and improve performance
- Implement proper transaction management for data integrity
- Configure SQLite pragmas for optimal performance
- Structure your code with repository patterns for maintainability
- Test with in-memory databases for fast, isolated tests
- Monitor database performance and connection usage
By following these patterns and best practices, you'll build reliable, performant applications that leverage the best of both Go and SQLite.
>>> Follow and Contact Relia Software for more information!
- golang
- coding