Floor — what is it, ORM over SQLite in Flutter

What is Floor?

Floor is an ORM library for Flutter and Dart built on top of SQLite. It uses annotations to describe entities (Entity), Data Access Objects (DAO) and the database (Database). Code generation is performed via build_runner and floor_generator — the compiler creates DAO implementations and a managing database class. Unlike raw sqflite, Floor completely eliminates the need for manual ResultSet-to-Dart object conversion by automatically mapping columns to Entity fields through type reflection.

Floor follows the Repository + DAO pattern familiar to Android developers from Room. Each table is represented by a Dart class with the @Entity annotation, SQL queries are grouped in interfaces with the @dao annotation, and the database is assembled in an abstract class with @Database. This approach strictly separates the data model from query logic.

According to Flutter Pulse (2023), Floor is chosen in 28% of Flutter projects that require a local database. The main reasons for choosing it are familiarity with SQL (no need to learn a new query language) and compile-time query checking. Additionally, Floor generates readable code that is easy to debug compared to more abstract ORMs with custom DSLs, which lowers the entry barrier for new developers on the team.

Floor Architecture

Floor consists of three layers: Entity (table model), DAO (query interface) and Database (entry point). The generator creates _$_Entity implementations for field mapping and _$_Dao implementations for executing SQL. When Entity or DAO changes, simply restart build_runner — the code updates automatically. For migration between schema versions, Floor uses sequential version numbers, ensuring data integrity when updating the app on user devices.

How does Floor work in Flutter?

Floor uses SQLite through the sqflite package for platform builds and sqlite3 for desktop and web. When the app starts, Floor creates or opens the SQLite file, applies migrations and prepares DAO methods for executing queries. All operations are performed asynchronously via Future and Stream.

Code generation in Floor works as follows: the parser reads annotations from the source files, creates an AST (Abstract Syntax Tree) of models and queries, then generates Dart files with the _$ prefix. The generated code includes ResultSet → Entity mappers and vice versa.

Thread safety

Floor works with SQLite in a single isolate. All queries are executed asynchronously, but concurrent writes are locked at the SQLite level. For transactions, the @transaction annotation is used, which guarantees atomicity of a group of queries and rollback on error.

Floor vs Drift: ORM comparison for Flutter

Both Floor and Drift are ORMs over SQLite, but they differ in philosophy. Floor is closer to Room from Android, Drift is more reactive with built-in Stream API and query compilation via SQL files. The choice between them depends on team experience and required reactivity.

When to choose Floor

Floor is the choice for teams already familiar with SQL and Android Room. If developers are used to writing SQL queries manually and want a minimal wrapper over SQLite — Floor provides typing without learning a new DSL. It is also easier to debug since the generated code is readable and predictable.

When Drift is better

Drift offers more powerful reactivity and supports more platforms. If the app actively uses Stream for UI updates, requires complex queries with JOIN and subqueries or targets web — Drift is preferable. However, its entry barrier is higher due to the need to learn its own DSL.

Code examples with Floor

Floor is built around annotations. Below is a complete example of Entity, DAO and Database for a task list app. After running build_runner, the generated classes are ready to use.

Defining Entity and DAO

The TaskEntity class with @Entity annotation maps to the task table. A field with @primaryKey becomes the primary key. The TaskDao interface contains methods for table operations — each method is annotated with @Query, @Insert, @Update or @Delete.

@entity
class TaskEntity {
    @PrimaryKey(autoGenerate: true)
    final int id;
    final String title;
    final bool isCompleted;
    final int priority;

    TaskEntity({this.id, required this.title,
        this.isCompleted = false, this.priority = 0});
}

@dao
abstract class TaskDao {
    @Query('SELECT * FROM TaskEntity ORDER BY priority DESC')
    Future<List<TaskEntity>> getAllTasks();

    @Insert
    Future<int> insertTask(TaskEntity task);

    @Update
    Future<void> updateTask(TaskEntity task);

    @Query('SELECT * FROM TaskEntity WHERE isCompleted = :status')
    Stream<List<TaskEntity>> watchTasks(bool status);
}

Database initialization

An abstract class with @Database annotation links Entity and DAO. The databaseBuilder method creates a database instance. After calling build, the database is ready: Floor opens the SQLite file, applies migrations and returns the DAO for work.

@Database(version: 1, entities: [TaskEntity])
abstract class AppDatabase extends FloorDatabase {
    TaskDao get taskDao;
}

// Usage
final database = await $FloorAppDatabase.databaseBuilder('app.db').build();
final taskDao = database.taskDao;
final tasks = await taskDao.getAllTasks();

Reactive queries via Stream

Floor supports returning Stream from DAO methods. On any table changes, the Stream emits a new list. This integrates with StreamBuilder in Flutter — the UI automatically updates when records are added, modified or deleted.

@Query('SELECT * FROM TaskEntity ORDER BY priority DESC')
Stream<List<TaskEntity>> watchAllTasks();

// In Flutter widget
StreamBuilder<List<TaskEntity>>(
    stream: taskDao.watchAllTasks(),
    builder: (context, snapshot) {
        final tasks = snapshot.data ?? [];
        return ListView.builder(
            itemCount: tasks.length,
            itemBuilder: (_, i) => TaskTile(tasks[i]),
        );
    },
)

Migrations and versioning in Floor

Floor supports database versioning through the version parameter in @Database annotation. When Entity changes (adding or removing fields), you need to increment the version and add a migration. A migration is a Dart function that receives a transaction and executes ALTER TABLE SQL queries.

Migration example

Suppose in version 2 we added a dueDate field to TaskEntity. The migration is performed via ALTER TABLE SQL query. If a migration is not specified, Floor calls MigrationStrategy where you can set a fallback (e.g., recreating the table with data loss).

Testing Floor queries

Floor does not provide a built-in mock framework, but the database can be easily replaced in tests. Create an inMemoryDatabaseBuilder — it creates an in-memory SQLite database identical in schema to production. After each test, clear data via deleteDatabase to isolate test scenarios.

Transactions and batch operations in Floor

Floor supports transactions via the @transaction annotation on DAO methods. Inside a transaction, multiple queries are executed sequentially with a rollback guarantee on error. Batch insertion via @Insert with a List parameter optimizes inserting multiple records in a single call — this is several times faster than inserting one record at a time in a loop. For bulk operations, use batch inserts of 100–200 records: this offers the optimal balance between execution speed and RAM consumption on mobile devices with limited resources.

final migration1to2 = Migration(1, 2, (database) async {
    await database.execute(
        'ALTER TABLE TaskEntity ADD COLUMN dueDate TEXT'
    );
});

final database = await $FloorAppDatabase.databaseBuilder('app.db')
    .addMigrations([migration1to2])
    .build();