SQLite Without the Overhead: FFI vs Platform Channels in Flutter

The architecture difference

Here's what happens when sqflite executes a query:

Dart code
  → MethodChannel.invokeMethod('query', {sql, args})
    → Platform thread (Java/Kotlin on Android, ObjC/Swift on iOS)
      → SQLite C API (sqlite3_prepare, sqlite3_step, ...)
    ← Result serialized to platform map
  ← Result deserialized to Dart Map

And here's what happens with sqlite3 via FFI:

Dart code
  → FFI function call (sqlite3_prepare_v2, sqlite3_step, ...)
  ← Result read directly from native memory

The FFI path skips the entire platform channel layer. The function call is a direct CPU instruction — the same as calling any C function from C. There's still a cost to cross the managed/unmanaged boundary (converting Dart strings to C strings, reading column values from native memory), but it's orders of magnitude less than the serialization roundtrip.

Simon Binder's benchmarks show the difference clearly: for bulk inserts and batch reads, the FFI path is 2-5x faster than platform channels. For single queries, the difference is negligible. The gap widens with volume.

Setting it up

Option 1: The sqlite3_flutter_libs package (recommended)

The easiest path. This package bundles a recent version of SQLite as a precompiled native library for all platforms. You don't compile anything yourself.

dependencies:
  sqlite3: ^2.4.0
  sqlite3_flutter_libs: ^0.5.0

That's it. sqlite3_flutter_libs handles:

The version of SQLite bundled is typically newer than the one shipped with the OS — which matters because Android's system SQLite varies wildly by device and OS version. Some Android 8 devices ship SQLite 3.18; sqlite3_flutter_libs gives you 3.45+.

Option 2: Use the system SQLite

If you don't want to bundle SQLite (to keep APK size down), you can use the OS-provided version:

import 'dart:ffi';
import 'dart:io';
import 'package:sqlite3/open.dart';

void setupSqlite() {
  open.overrideFor(OperatingSystem.android, () {
    return DynamicLibrary.open('libsqlite3.so'); // System SQLite
  });
}

The tradeoff: you lose control over the SQLite version and compile-time flags. Features like JSON support or FTS5 may not be available on all devices. For production apps, bundling is almost always the right call.

Opening a database

import 'package:sqlite3/sqlite3.dart';
import 'package:path_provider/path_provider.dart';
import 'package:path/path.dart' as p;

Future<Database> openAppDatabase() async {
  final dir = await getApplicationDocumentsDirectory();
  final dbPath = p.join(dir.path, 'app.db');

  final db = sqlite3.openV2(
    dbPath,
    // READWRITE | CREATE — create if it doesn't exist
    OpenMode.readWriteCreate,
  );

  // Enable WAL mode for better concurrent read performance
  db.execute('PRAGMA journal_mode=WAL');
  // Enable foreign key enforcement (off by default in SQLite)
  db.execute('PRAGMA foreign_keys=ON');

  return db;
}

The Database object from the sqlite3 package is a thin wrapper around the native sqlite3* handle. When you call db.execute(), it calls sqlite3_exec via FFI. When you call db.select(), it calls sqlite3_prepare_v2, sqlite3_step, reads column values, and returns a ResultSet.

The database handle is not thread-safe. SQLite itself supports concurrent access from multiple connections, but a single connection can only be used from one thread (one Dart isolate) at a time. This matters — more on this below.

Queries

// Create a table
db.execute('''
  CREATE TABLE IF NOT EXISTS products (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    price REAL NOT NULL,
    category TEXT,
    created_at TEXT DEFAULT (datetime('now'))
  )
''');

// Insert
final stmt = db.prepare('INSERT INTO products (name, price, category) VALUES (?, ?, ?)');
stmt.execute(['Flutter Widget Kit', 29.99, 'tools']);
stmt.execute(['Dart Cheat Sheet', 4.99, 'books']);
stmt.dispose(); // Always dispose prepared statements

// Query
final results = db.select(
  'SELECT * FROM products WHERE category = ? ORDER BY price DESC',
  ['tools'],
);

for (final row in results) {
  print('${row['name']}: \$${row['price']}');
}

Notice: db.select() returns a ResultSet — a list of Row objects that you can index by column name or position. The result is a Dart object; the underlying native memory is already freed. You don't need to manage native memory for queries — the sqlite3 package handles that internally.

Prepared statements (db.prepare()) are the exception: they hold native resources and must be disposed. If you don't call stmt.dispose(), you leak the native sqlite3_stmt* handle.

Batch operations

This is where the FFI advantage really shows. Inserting 10,000 records:

Future<void> syncProducts(Database db, List<Product> products) async {
  // Wrap in a transaction — this alone makes batch inserts 50-100x faster
  db.execute('BEGIN TRANSACTION');

  final stmt = db.prepare(
    'INSERT OR REPLACE INTO products (id, name, price, category) VALUES (?, ?, ?, ?)',
  );

  try {
    for (final product in products) {
      stmt.execute([product.id, product.name, product.price, product.category]);
    }
    db.execute('COMMIT');
  } catch (e) {
    db.execute('ROLLBACK');
    rethrow;
  } finally {
    stmt.dispose();
  }
}

The BEGIN TRANSACTION / COMMIT wrapper is critical. Without it, SQLite creates an implicit transaction for each INSERT — which means each insert does a disk sync. With explicit transactions, the disk sync happens once at the end. For 10,000 inserts, this is the difference between 30 seconds and 200 milliseconds.

Combined with the FFI path (no platform channel overhead per statement execution), a sync of 10,000 records typically completes in 100-300ms on a modern phone.

Isolates: the concurrency story

SQLite connections aren't thread-safe. A single Database object must only be used from one isolate. But heavy database work blocks the UI if it runs on the main isolate.

The solution: open a separate connection on a background isolate.

import 'dart:isolate';

Future<List<Product>> queryProductsInBackground(String dbPath) async {
  return Isolate.run(() {
    // Open a NEW connection in the background isolate
    final db = sqlite3.openV2(dbPath, OpenMode.readOnly);
    db.execute('PRAGMA journal_mode=WAL'); // WAL allows concurrent readers

    try {
      final results = db.select('SELECT * FROM products ORDER BY name');
      return results.map((row) => Product(
        id: row['id'] as int,
        name: row['name'] as String,
        price: row['price'] as double,
        category: row['category'] as String?,
      )).toList();
    } finally {
      db.dispose(); // Close the connection when done
    }
  });
}

WAL (Write-Ahead Logging) mode is key here. In WAL mode, readers don't block writers and writers don't block readers. The main isolate can write while a background isolate reads. Without WAL, SQLite uses a file lock that blocks concurrent access.

For persistent background work (not one-off queries), keep a long-lived worker isolate with its own database connection. The Async Dart series on Isolates covers the worker pattern.

Drift: the type-safe layer

Raw SQL works but gets tedious. drift (also by Simon Binder) is a type-safe query builder and ORM built on top of the sqlite3 FFI package:

// Define your schema
class Products extends Table {
  IntColumn get id => integer().autoIncrement()();
  TextColumn get name => text().withLength(min: 1, max: 200)();
  RealColumn get price => real()();
  TextColumn get category => text().nullable()();
  DateTimeColumn get createdAt => dateTime().withDefault(currentDateAndTime)();
}

// The database
@DriftDatabase(tables: [Products])
class AppDatabase extends _$AppDatabase {
  AppDatabase(super.e);

  @override
  int get schemaVersion => 1;

  // Type-safe queries
  Future<List<Product>> getProductsByCategory(String category) {
    return (select(products)
      ..where((p) => p.category.equals(category))
      ..orderBy([(p) => OrderingTerm.desc(p.price)])
    ).get();
  }

  Future<int> insertProduct(ProductsCompanion product) {
    return into(products).insert(product);
  }
}

Drift generates the SQL at compile time, gives you type-safe results (no row['name'] as String casting), handles migrations, and supports both the FFI backend and the sqflite backend — so you can switch between them.

To use Drift with the FFI backend:

import 'package:drift/native.dart';

AppDatabase openDatabase() {
  return AppDatabase(NativeDatabase.createInBackground(
    File(p.join(documentsDir.path, 'app.db')),
  ));
}

NativeDatabase.createInBackground automatically opens the database on a background isolate and proxies queries. You get background execution without managing isolates yourself.

Common errors

"DatabaseException: database is locked"

Cause: Two isolates (or two Database objects on the same isolate) are trying to write simultaneously, and WAL mode isn't enabled.

Fix: Enable WAL mode immediately after opening the connection:

db.execute('PRAGMA journal_mode=WAL');

And never share a single Database object across isolates. Each isolate opens its own connection.

Prepared statement not disposed — silent memory leak

Cause: You called db.prepare() but didn't call stmt.dispose(). The native sqlite3_stmt* handle leaks.

Fix: Always dispose in a finally block:

final stmt = db.prepare('INSERT INTO ...');
try {
  stmt.execute([...]);
} finally {
  stmt.dispose();
}

Or use db.execute() / db.select() for one-off queries — these handle disposal internally.

"no such table" on Android release builds

Cause: ProGuard/R8 stripped something it shouldn't have. Rare with the FFI path (more common with sqflite), but can happen if your proguard rules are aggressive.

Fix: Verify the database file path is correct. Check that sqlite3_flutter_libs is in your dependencies (not just sqlite3). On Android, the native .so must be bundled — without sqlite3_flutter_libs, there's no SQLite binary to load.

"SqliteException(1): near 'RETURNING': syntax error"

Cause: You're using a SQLite feature that the bundled version doesn't support. RETURNING was added in SQLite 3.35. If you're using the system SQLite on an older Android device, you won't have it.

Fix: Use sqlite3_flutter_libs to bundle a recent version. Don't rely on the system SQLite for feature availability.

Batch inserts are incredibly slow

Cause: No explicit transaction. Each insert creates its own implicit transaction with a disk sync.

Fix: Wrap in BEGIN TRANSACTION / COMMIT:

db.execute('BEGIN TRANSACTION');
try {
  // ... all inserts ...
  db.execute('COMMIT');
} catch (e) {
  db.execute('ROLLBACK');
  rethrow;
}

Database file corrupt after crash

Cause: Rare, but possible if the app is killed during a write without WAL mode. WAL mode is significantly more crash-resistant than the default rollback journal.

Fix: Always use WAL mode. Consider PRAGMA wal_checkpoint(TRUNCATE) periodically to keep the WAL file from growing unboundedly.

When to use this vs sqflite

Use `sqlite3` / Drift with FFI when:

Stick with `sqflite` when:

For new projects, Drift + FFI backend is the default recommendation. The setup cost is minimal, and you get better performance, better APIs, and consistent SQLite behavior across devices.

This is Post 12 of the FFI series. Next: Video Processing With FFmpeg. For FFI fundamentals, start with the Dart FFI series.