# How CasaOS Uses SQLite with GORM for Data Persistence

> Discover how CasaOS leverages SQLite and GORM for efficient data persistence with auto-migration and connection pooling, ensuring a robust user experience.

- Repository: [IceWhale/CasaOS](https://github.com/IceWhaleTech/CasaOS)
- Tags: internals
- Published: 2026-06-28

---

**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`](https://github.com/IceWhaleTech/CasaOS/blob/main/pkg/sqlite/db.go)

The `GetDb` function in [`pkg/sqlite/db.go`](https://github.com/IceWhaleTech/CasaOS/blob/main/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`](https://github.com/IceWhaleTech/CasaOS/blob/main/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`](https://github.com/IceWhaleTech/CasaOS/blob/main/service/model/o_shares.go) manages shared folder configurations.

### Additional Data Models

CasaOS maintains several other entities: `ConnectionsDBModel` in [`service/model/o_connections.go`](https://github.com/IceWhaleTech/CasaOS/blob/main/service/model/o_connections.go) for connection records, `PeerDriveDBModel` in [`service/model/o_drive.go`](https://github.com/IceWhaleTech/CasaOS/blob/main/service/model/o_drive.go) for peer drive information, and `RelyDBModel` in [`service/model/o_rely.go`](https://github.com/IceWhaleTech/CasaOS/blob/main/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:

```go
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:

```go
// 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:

```go
// 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`](https://github.com/IceWhaleTech/CasaOS/blob/main/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`](https://github.com/IceWhaleTech/CasaOS/blob/main/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.