CasaOS Data Persistence with SQLite and GORM: A Complete Technical Guide

CasaOS persists runtime state—including notifications, shares, and peer connections—in a local SQLite file using GORM for object-relational mapping, implementing single-connection pooling and automatic schema migrations to maintain a lightweight, serverless database architecture.

CasaOS employs a file-based persistence strategy that eliminates external database dependencies by storing all runtime data in a local SQLite database accessed through the GORM ORM. This architecture centers on a single database file (casaOS.db) that lives on the host filesystem, with the GetDb function in pkg/sqlite/db.go handling initialization, connection management, and schema evolution. By leveraging GORM's auto-migration capabilities and SQLite's zero-configuration nature, CasaOS achieves reliable data persistence without requiring a separate database server deployment.

Database Initialization in pkg/sqlite/db.go

The GetDb function serves as the central entry point for CasaOS data persistence, orchestrating the SQLite engine setup and GORM configuration.

File System Preparation and Driver Selection

Before opening the database, CasaOS ensures the target directory exists using file.IsNotExistMkDir(dbPath). The system then opens the SQLite file using the pure-Go driver github.com/glebarez/sqlite, which provides seamless GORM integration without CGO dependencies.

Connection Pool Optimization for SQLite

Given SQLite's single-writer architecture, CasaOS configures conservative connection limits in pkg/sqlite/db.go: SetMaxOpenConns(1) restricts the pool to one open connection, while SetMaxIdleConns(10) maintains a modest idle pool for connection reuse. This prevents concurrency conflicts while maintaining performance for read-heavy operations.

Automatic Schema Migration and Legacy Cleanup

Upon initialization, GORM executes AutoMigrate for all model structs including AppNotify, SharesDBModel, ConnectionsDBModel, and PeerDriveDBModel, creating or updating tables based on struct tags. The system also removes legacy tables (o_application, o_friend) that persist from earlier CasaOS versions, ensuring schema consistency.

GORM Model Architecture in service/model

CasaOS defines its database schema through Go structs in the service/model package, utilizing GORM tags for column mapping and primary key configuration.

Notification and Share Storage Models

The notification table defined in service/model/o_notify.go uses the AppNotify struct to store user alerts with fields for state, message content, and timestamps. Similarly, service/model/o_shares.go defines SharesDBModel for managing shared folder metadata and access permissions.

Connection and Peer Drive Models

Network connections and peer drive mappings utilize ConnectionsDBModel (from service/model/o_connections.go) and PeerDriveDBModel (from service/model/o_drive.go) respectively, enabling CasaOS to persist distributed storage configurations across application restarts.

Practical Implementation Examples

The following patterns demonstrate how CasaOS interacts with the SQLite database through GORM's API.

Initialize the database connection:

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

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

Create a notification record:

import (
    "github.com/IceWhaleTech/CasaOS/service/model"
    "github.com/google/uuid"
    "time"
)

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 shared folders:

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

Summary

• CasaOS uses a single SQLite file (casaOS.db) managed through the GetDb function in pkg/sqlite/db.go
• Connection pooling limits open connections to one (SetMaxOpenConns(1)) to accommodate SQLite's single-writer constraint
• GORM auto-migration handles schema creation for models including AppNotify, SharesDBModel, and ConnectionsDBModel
• Legacy table cleanup removes obsolete structures (o_application, o_friend) from previous versions automatically
• Model definitions reside in service/model/ with structs tagged for GORM mapping

Frequently Asked Questions

Where does CasaOS store its SQLite database file?

CasaOS creates the casaOS.db file in the directory specified during GetDb initialization, typically within /var/lib/casaos or user-defined host paths. The application ensures the directory exists before attempting to open the database connection using file.IsNotExistMkDir.

Why does CasaOS limit SQLite connections to one?

The SetMaxOpenConns(1) configuration prevents write conflicts since SQLite uses file-level locking that allows only one writer at a time. This constraint ensures data integrity while the SetMaxIdleConns(10) setting maintains read performance through connection reuse for concurrent read operations.

How does CasaOS handle database schema updates?

CasaOS leverages GORM's AutoMigrate function in pkg/sqlite/db.go to automatically create or update tables based on the current model struct definitions. This eliminates manual migration scripts and ensures the schema evolves with the application code when new fields are added to AppNotify, SharesDBModel, or other structs.

Which driver does CasaOS use for SQLite integration?

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

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