How the AppFlowy SQLite Database (flowy-sqlite) Stores and Manages Data

AppFlowy centralizes all persistent storage in the flowy-sqlite crate, a Diesel-powered abstraction over SQLite that manages connection pooling, schema migrations, and high-level data access for everything from user preferences to file upload metadata.

AppFlowy is an open-source alternative to Notion that relies entirely on local SQLite databases for offline-first data persistence. The flowy-sqlite crate serves as the foundational storage layer, wrapping raw SQLite connections with connection pooling, type-safe schema definitions, and convenient helper APIs. This article explores the architecture, implementation details, and usage patterns of the AppFlowy SQLite database based on the actual source code in the AppFlowy-IO/AppFlowy repository.

Database Initialization and File Structure

When AppFlowy launches, it invokes the init function exported from flowy-sqlite/src/lib.rs to prepare the storage layer.

pub fn init<P: AsRef<Path>>(storage_path: P) -> Result<Database, io::Error> {
    // 1️⃣ Create the folder if it does not exist
    // 2️⃣ Build a connection-pool (see `Database::new`)
    // 3️⃣ Run the embedded Diesel migrations
}

This function performs three critical tasks: it ensures the data directory exists, constructs a connection pool, and executes Diesel migrations embedded at compile time via embed_migrations!("../flowy-sqlite/migrations/"). The system uses a fixed database name defined by the DB_NAME constant—flowy-database.db—meaning each workspace receives its own discrete SQLite file located under the application’s data directory.

Connection Pooling and Performance Optimization

To ensure thread-safe access without exhausting file handles, flowy-sqlite implements an r2d2 connection pool wrapped in an Arc for shared ownership.

pub type DBConnection = PooledConnection<ConnectionManager>;

pub struct Database {
    uri: String,
    pool: Arc<ConnectionPool>,
}

The pool configuration is defined in flowy-sqlite/src/sqlite_impl/pool.rs, supplying sensible defaults such as min_idle = 1 and max_size = 10, alongside connection timeouts. Every connection acquired from the pool is automatically configured by the DatabaseCustomizer struct, which executes essential SQLite PRAGMAs (approximately lines 45–55 in the source). These include enabling WAL (Write-Ahead Logging) mode to allow concurrent reads during write transactions, setting busy timeouts, and adjusting synchronous levels for durability versus performance.

Schema Definition and Core Tables

All persistent entities are declared using Diesel’s DSL in flowy-sqlite/src/schema.rs. The schema drives type-safe queries across the application and includes tables for user management, workspaces, and file synchronization.

Key tables managed by the storage layer include:

• upload_file_table – Tracks resumable file uploads with fields for workspace ID, file ID, chunk size, and completion status.
• upload_file_part – Stores metadata for individual upload chunks, including ETag values and part numbers.
• kv_table – Provides a simple key-value interface for application preferences and configuration.
• user_table and user_workspace_table – Maintain user profiles and workspace membership data used by other crates in the Rust codebase.

High-Level Data Access Patterns

The KV Store for Preferences

For lightweight configuration data, AppFlowy exposes a dedicated key-value API through flowy-sqlite/src/kv/kv.rs. The KVStorePreferences struct opens a separate SQLite file named cache.db and executes a minimal schema creation query (KV_SQL) on initialization.

pub struct KVStorePreferences {
    database: Option<Database>,
}

This interface offers strongly-typed helpers like set_str and get_str, allowing modules to persist UI themes, recent documents, and feature flags without writing raw SQL.

Direct CRUD Operations

Modules requiring relational data access obtain a pooled connection through Database::get_connection(), which returns a DBConnection type alias. This connection is then passed to Diesel-generated query DSL for type-safe operations.

let mut conn = database.get_connection()?;
let count = user_table
    .count()
    .get_result::<i64>(&mut conn)?;

Real-World Usage: File Upload Metadata

The flowy-storage crate demonstrates how higher-level services leverage flowy-sqlite for complex workflows. When initiating a file upload, the storage manager creates a record via create_upload_record in flowy-storage/src/manager.rs (lines 93–104), which constructs an UploadFileTable struct and persists it using insert_upload_file.

As the upload progresses, each successfully transmitted chunk is tracked in upload_file_part through the insert_upload_part helper defined in flowy-storage/src/sqlite_sql.rs. Upon completion, the update_upload_file_completed function updates the is_finish flag to true. This architecture enables AppFlowy to resume interrupted uploads across application restarts by querying the SQLite metadata tables.

Thread Safety and Async Integration

Although SQLite itself is file-based, AppFlowy enables concurrent access through the WAL journaling mode configured in the connection pool. The r2d2 pool guarantees that each thread receives its own SqliteConnection, preventing race conditions.

Storage APIs are designed to be async-friendly: code acquires a connection synchronously via database.get_connection(), then moves the connection into a tokio::spawn task for non-blocking execution. This pattern appears throughout the storage layer when processing large file uploads or syncing workspace data.

Summary

• Initialization – The init function in flowy-sqlite/src/lib.rs creates the flowy-database.db file and executes Diesel migrations automatically.
• Pooling – An r2d2 pool with custom PRAGMAs (WAL mode, busy timeouts) ensures safe, concurrent access from multiple threads.
• Schema – Diesel-driven table definitions in schema.rs govern user data, workspaces, and upload metadata.
• KV Store – A separate cache.db file managed by KVStorePreferences provides simple key-value storage for settings.
• Integration – The storage layer uses these primitives to implement resumable uploads by persisting chunk metadata in upload_file_table and upload_file_part.

Frequently Asked Questions

What database engine does AppFlowy use for local storage?

AppFlowy uses SQLite exclusively for local data persistence. The flowy-sqlite crate provides a Rust wrapper built on top of Diesel and the r2d2 connection pool, exposing a single SQLite file named flowy-database.db per workspace.

How does AppFlowy handle concurrent database access?

Concurrency is managed through a combination of connection pooling and WAL (Write-Ahead Logging) mode. The pool in flowy-sqlite/src/sqlite_impl/pool.rs maintains up to 10 connections (max_size = 10) with min_idle = 1, while the DatabaseCustomizer enables WAL journaling on every connection to allow readers to proceed while writers hold locks.

Where are AppFlowy user settings stored?

User preferences and configuration data are stored in a dedicated key-value SQLite database called cache.db, separate from the main application database. The KVStorePreferences struct in flowy-sqlite/src/kv/kv.rs manages this file, offering methods like set_str and get_str for persistent settings storage.

Can I access the AppFlowy SQLite database directly?

Yes, the SQLite files are standard SQLite databases stored in the application’s data directory. Both flowy-database.db (containing user and workspace tables) and cache.db (containing key-value preferences) can be opened with standard SQLite clients, though modifying them while AppFlowy is running may trigger busy-timeout errors due to the connection pool’s locking mechanisms.

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