How CasaOS Uses SQLite with GORM for Data Persistence

CasaOS persists runtime state to a local SQLite file using GORM, implementing a lightweight persistence layer with auto-migration and connection pooling.

CasaOS leverages SQLite as its embedded database engine, accessed through the GORM ORM to store notifications, shares, connections, and peer drive information. This architecture eliminates the need for external database servers while providing structured data management. The implementation resides primarily in the pkg/sqlite package and service/model directory, offering a file-based persistence strategy that automatically handles schema creation and updates.

Database Initialization and Configuration

The persistence layer centers around a single database file that is initialized at startup with specific optimizations for SQLite's concurrency characteristics.

The Entry Point in pkg/sqlite/db.go

The GetDb function in pkg/sqlite/db.go serves as the central initialization routine. It first ensures the target directory exists using file.IsNotExistMkDir(dbPath), then opens the SQLite file using the github.com/glebarez/sqlite driver—a pure-Go implementation that integrates seamlessly with GORM without requiring CGO bindings.

Connection Pool Optimization for SQLite

Since SQLite does not support high concurrency for write operations, CasaOS configures the connection pool conservatively. The code sets SetMaxOpenConns(1) to prevent locking conflicts while allowing SetMaxIdleConns(10) to maintain a modest pool of idle connections for read operations.

Auto-Migration and Schema Management

Upon initialization, GORM executes auto-migration for all model structs including AppNotify, SharesDBModel, ConnectionsDBModel, and PeerDriveDBModel. According to the source code at lines 44-48, this process inspects struct tags and creates or updates tables as needed. The system also cleans up legacy tables such as o_application and o_friend (lines 49-52) that persist from earlier CasaOS versions.

Model Definitions and Struct Tags

The domain models reside in service/model/ and define table structures using GORM tags that map Go structs to SQLite schema.

Notification and Shares Models

The AppNotify struct in service/model/o_notify.go defines the notifications table with fields for state, message, timestamps, and metadata. Similarly, SharesDBModel in service/model/o_shares.go manages shared folder configurations.

Additional Data Models

CasaOS maintains several other entities: ConnectionsDBModel in service/model/o_connections.go for connection records, PeerDriveDBModel in service/model/o_drive.go for peer drive information, and RelyDBModel in service/model/o_rely.go for dependency tracking.

Working with the Database

Developers interact with the database by obtaining the singleton *gorm.DB instance and using standard GORM methods such as Create, Find, Where, and Delete.

Initialize the database once at startup:

import (
    "github.com/IceWhaleTech/CasaOS/pkg/sqlite"
    "github.com/IceWhaleTech/CasaOS/service/model"
)

func initDB() *gorm.DB {
    // The directory where casaOS.db will be created
    dbPath := "/var/lib/casaos"
    return sqlite.GetDb(dbPath)
}

Create new records using struct instances:

// Creating a new notification record
func addNotify(db *gorm.DB, msg string) error {
    n := model.AppNotify{
        State:   1,               // unread
        Message: msg,
        CreatedAt: time.Now().Format(time.RFC3339),
        UpdatedAt: time.Now().Format(time.RFC3339),
        Id:       uuid.New().String(),
        Type:     0,
        Icon:     "info",
        Name:     "CasaOS",
        Class:    0,
    }
    return db.Create(&n).Error
}

Query existing data with GORM's fluent API:

// Querying all shared folders
func listShares(db *gorm.DB) ([]model.SharesDBModel, error) {
    var shares []model.SharesDBModel
    err := db.Find(&shares).Error
    return shares, err
}

Summary

• CasaOS stores runtime data in a single SQLite file (casaOS.db) accessed via GORM, eliminating external database dependencies
• The GetDb function in pkg/sqlite/db.go handles initialization, connection pooling, and directory creation using file.IsNotExistMkDir
• Connection pooling is limited to one open connection (SetMaxOpenConns(1)) to accommodate SQLite's concurrency constraints
• Auto-migration creates and updates tables for AppNotify, SharesDBModel, and other entities defined in service/model/
• Legacy table cleanup removes obsolete schemas (o_application, o_friend) from previous versions
• The pure-Go github.com/glebarez/sqlite driver eliminates CGO dependencies while maintaining full GORM compatibility

Frequently Asked Questions

Where is the CasaOS database file stored?

CasaOS creates the database file named casaOS.db in the directory specified when calling GetDb, typically /var/lib/casaos. The initialization code ensures the directory exists before attempting to open the file, preventing startup errors due to missing paths.

Why does CasaOS limit SQLite connections to one?

SQLite handles concurrency poorly when multiple writers access the database simultaneously. By setting SetMaxOpenConns(1) in pkg/sqlite/db.go, CasaOS prevents database locking errors while maintaining data integrity through serialized access to the single casaOS.db file.

How does CasaOS handle database schema changes?

CasaOS uses GORM's auto-migration feature to automatically create tables and update schemas based on model struct definitions. When the application starts, it inspects structs like AppNotify and SharesDBModel in the service/model package and applies necessary changes to the SQLite schema without requiring manual migration scripts.

Which driver does CasaOS use for SQLite?

CasaOS uses the github.com/glebarez/sqlite driver, a pure-Go implementation that works without CGO. This choice simplifies cross-compilation and deployment across different architectures while maintaining full compatibility with GORM's feature set for the CasaOS SQLite GORM implementation.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}