database.md

SQLite Database

AppFlowy use SQLite as database and Diesel as ORM.

The flowy-sqlite

The crate, flowy-sqlite, contains the logic for creating the SQLite schema and providing a shared kv storage. It is located in frontend/rust-lib/flowy-sqlite.

The following section will guide you through how to create or update a schema. Before starting, I recommend checking out the Diesel Getting Started if you don't know about diesel before. Make sure you install the diesel CLI tool. You can install it by running:

cargo install diesel_cli --no-default-features --features sqlite

Create schema

Create a new schema.

/// Go to the working directory
cd frontend/rust-lib/flowy-sqlite/

/// Generate a new migration named user
diesel migration generate user

Output

• Creating migrations/2022-08-07-140433_user/up.sql
• Creating migrations/2022-08-07-140433_user/down.sql

Create a table named user_table. Open the up.sql

CREATE TABLE user_table (
    id TEXT NOT NULL PRIMARY KEY,
    name TEXT NOT NULL DEFAULT '',
    token TEXT NOT NULL DEFAULT '',
    email TEXT NOT NULL DEFAULT ''
);

When doing revert operation, the down.sql will be applied. We drop the user_table here.

DROP TABLE user_table;

Run the migration

diesel migration run

Migrations allow us to evolve the database schema over time. Each migration can be applied (up.sql) or reverted (down.sql). Applying and immediately reverting a migration should leave your database schema unchanged. It’s a good idea to make sure that down.sql is correct. You can quickly confirm that your down.sql rolls back your migration correctly by redoing the migration:

diesel migration redo

Output:

• Rolling back migration 2022-08-07-140433_user
• Running migration 2022-08-07-140433_user

Ok, here we go. Everything is fine. After running the migration, the schema is automatically added to the schema.rs

// flowy-sqlite/src/schema.rs
table! {
    user_table (id) {
        id -> Text,
        name -> Text,
        token -> Text,
        email -> Text,
    }
}

Writing Rust

#[derive(Clone, Default, Queryable, Identifiable, Insertable)]
#[table_name = "user_table"]
pub struct UserTable {
    pub(crate) id: String,
    pub(crate) name: String,
    pub(crate) token: String,
    pub(crate) email: String,
}

Update schema

Update an existing schema.

cd frontend/rust-lib/flowy-sqlite/
diesel migration generate user-add-icon

Output

• Creating migrations/2022-08-07-140433_user-add-icon/up.sql
• Creating migrations/2022-08-07-140433_user-add-icon/down.sql

up.sql

ALTER TABLE user_table ADD COLUMN icon_url TEXT NOT NULL DEFAULT '';

down.sql

ALTER TABLE user_table DROP COLUMN icon_url;

diesel migration run
diesel migration redo

After running the migration, the icon_url is added to the user_table schema automatically.

// flowy-sqlite/src/schema.rs
table! {
    user_table (id) {
        id -> Text,
        name -> Text,
        token -> Text,
        email -> Text,
        icon_url -> Text,
    }
}

Write Rust

Let's write some Rust to read the database data. We're not going to explain how to use the diesel macros here, you can check this out for that.

We create a struct named UserTable to read the record of the user_table. The name of the properties should be the same as the user_table. We can use UserTable to insert a new record or read the existing record from the database.

#[derive(Clone, Default, Queryable, Identifiable, Insertable)]
#[table_name = "user_table"]
pub struct UserTable {
    pub(crate) id: String,
    pub(crate) name: String,
    pub(crate) token: String,
    pub(crate) email: String,
    pub(crate) icon_url: String,
}

Diesel provides lots of handy functions for reading and updating a record.

Read

// conn: the connection to the database
let user: UserTable = dsl::user_table
    .filter(user_table::id.eq(&user_id))
    .first::<UserTable>(conn)?;

Insert

Check out this for more information about inserting a record.

// user: instance of the UserTable
let _ = diesel::insert_into(user_table::table)
            .values(user)
            .execute(conn)?;

Update

We useAsChangeset macro that diesel provides to implement the AsChangeset trait. Check out this for more information about updating a record.

#[derive(AsChangeset, Identifiable, Default, Debug)]
#[table_name = "user_table"]
pub struct UserTableChangeset {
    pub id: String,
    pub name: Option<String>,
    pub email: Option<String>,
    pub icon_url: Option<String>
}

Apply the changeset to the database

// changeset: instance of the UserTableChangeset
diesel::update(user_table::table).set(&changeset);

Architecture

We use dependency injection to forbid the other crates directly dependencies on the flowy-sqlite crate. Each crate defines their database traits to meet their need.

Traits are a name given to a group of functions that a data structure can implement. I think using traits to isolate dependencies is a very good practice.

The flowy-user dependencies on the flowy-sqlite crate directly. It initializes the database connection when the Application launch or when the user switches account. The flowy-grid defines the GridDatabase trait and the flowy-folder defines the WorkspaceDatabase trait, these two traits are implemented in the flowy-sdk crate.

flowy-sdk is a crate that aggregates all the crates and resolves each crate's dependencies. flowy-sqlite is a crate that handles all the grid operations

flowy-folder is a crate that handles all the folder operations. The folder represents the concepts that include the workspace, app, and view.