SQLite

Provides an API for accessing SQLite databases

Technology

SQLite
Golang
Project Forge

The sqlite module provides SQLite database integration for Project Forge applications. This module extends the base database module with SQLite-specific functionality, offering an embedded database solution perfect for development, testing, and lightweight production deployments.

Overview

This module adds SQLite embedded database support to Project Forge applications and requires the database module. It provides:

Key Features

Embedded Database

Performance

Portability

Development Benefits

Configuration

Environment Variables

The module reads configuration from environment variables (with optional prefix):

File Path Examples

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
# Relative to project root
db_file=data/app.db

# Absolute path
db_file=/var/lib/myapp/database.db

# Memory database (for testing)
db_file=:memory:

# Temporary database
db_file=""  # Uses temporary file

Usage

Basic Setup

1
2
3
4
5
6
7
8
9
// Load configuration from environment
params := SQLiteParamsFromEnv("")

// Open database connection
db, err := database.OpenSQLiteDatabase(params)
if err != nil {
    return err
}
defer db.Close()

Custom Configuration

1
2
3
4
5
6
7
8
// Manual configuration
params := &SQLiteParams{
    File:   "data/myapp.db",
    Schema: "main",
    Debug:  false,
}

db, err := database.OpenSQLiteDatabase(params)

Memory Database (Testing)

1
2
3
4
5
6
7
// In-memory database for testing
params := &SQLiteParams{
    File:  ":memory:",
    Debug: true,
}

db, err := database.OpenSQLiteDatabase(params)

With Environment Prefix

1
2
3
// Use custom environment variable prefix
// Reads myapp_db_file, myapp_db_schema, etc.
params := SQLiteParamsFromEnv("myapp_")

SQLite-Specific Features

WAL Mode

SQLite databases automatically use WAL mode for better concurrency:

JSON Support

Modern SQLite includes JSON1 extension:

1
2
3
4
5
6
7
8
// Insert JSON data
_, err := db.Exec(`insert into users (data) values (?)`, `{"name": "John", "preferences": {"theme": "dark"}}`)

// Query with JSON functions
rows, err := db.Query(`select json_extract(data, '$.name') from users`)

// JSON array operations
rows, err := db.Query(`select * from users where json_extract(data, '$.active') = 'true'`)

Full-Text Search

SQLite’s FTS5 extension provides powerful text search:

1
2
3
4
5
// Create FTS table
_, err := db.Exec(`create virtual table posts_fts using fts5(title, content)`)

// Full-text search
rows, err := db.Query(`select * from posts_fts where posts_fts match 'database and sqlite'`)

File Operations

1
2
3
4
5
6
7
8
// Backup database
_, err := db.Exec(`vacuum into 'backup.db'`)

// Attach additional database
_, err := db.Exec(`attach database 'other.db' as other`)

// Database integrity check
rows, err := db.Query(`pragma integrity_check`)

Development Workflow

Database Creation

Schema Migrations

Testing

1
2
3
4
5
6
// Use in-memory database for tests
func setupTestDB() *sql.DB {
    params := &SQLiteParams{File: ":memory:"}
    db, _ := database.OpenSQLiteDatabase(params)
    return db
}

Dependencies

This module requires:

Production Considerations

File Management

Performance Optimization

Limitations

Source Code

See Also