# How the AppFlowy SQLite Database (flowy-sqlite) Stores and Manages Data > Discover how AppFlowy uses its flowy-sqlite crate, powered by Diesel, to store and manage all application data including user preferences and file metadata through connection pooling and schema migrations. - Repository: [AppFlowy-IO/AppFlowy](https://github.com/AppFlowy-IO/AppFlowy) - Tags: internals - Published: 2026-03-03 --- **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`](https://github.com/AppFlowy-IO/AppFlowy/blob/main/flowy-sqlite/src/lib.rs)** to prepare the storage layer. ```rust pub fn init>(storage_path: P) -> Result { // 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. ```rust pub type DBConnection = PooledConnection; pub struct Database { uri: String, pool: Arc, } ``` The pool configuration is defined in **[`flowy-sqlite/src/sqlite_impl/pool.rs`](https://github.com/AppFlowy-IO/AppFlowy/blob/main/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`](https://github.com/AppFlowy-IO/AppFlowy/blob/main/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`](https://github.com/AppFlowy-IO/AppFlowy/blob/main/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. ```rust pub struct KVStorePreferences { database: Option, } ``` 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. ```rust let mut conn = database.get_connection()?; let count = user_table .count() .get_result::(&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`](https://github.com/AppFlowy-IO/AppFlowy/blob/main/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`](https://github.com/AppFlowy-IO/AppFlowy/blob/main/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`](https://github.com/AppFlowy-IO/AppFlowy/blob/main/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`](https://github.com/AppFlowy-IO/AppFlowy/blob/main/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`](https://github.com/AppFlowy-IO/AppFlowy/blob/main/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`](https://github.com/AppFlowy-IO/AppFlowy/blob/main/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.