ASOasis - All about Tech ASOasis
Contact
Mobile Development

Mastering Complex SQLite Queries in Flutter with sqflite: A Practical Tutorial

Learn how to write and optimize complex SQLite queries in Flutter using sqflite: JOINs, subqueries, CTEs, pagination, indexes, and transactions.

ASOasis
9 min read
Mastering Complex SQLite Queries in Flutter with sqflite: A Practical Tutorial

Image used for representation purposes only.

Overview

Flutter’s sqflite plugin gives you full access to SQLite—fast, reliable, and offline-first. Beyond simple CRUD, you can (and should) leverage real SQL: multi-table JOINs, subqueries, aggregations, CTEs, indexes, and transactions. This tutorial walks you through building and optimizing complex queries in a realistic Flutter app, with copy‑pasteable Dart and SQL snippets.

Prerequisites

  • Flutter 3.x+
  • sqflite and path packages
  • Basic SQL familiarity (SELECT, WHERE, JOIN)

Add dependencies:

dependencies:
  sqflite: ^2.3.0
  path: ^1.9.0

Project setup and database opening

Create a database helper that opens the DB, creates tables, and handles migrations.

import 'package:path/path.dart' as p;
import 'package:sqflite/sqflite.dart';

class AppDatabase {
  static const _dbName = 'library.db';
  static const _dbVersion = 2; // bump when schema changes

  static Future<Database> open() async {
    final dbPath = await getDatabasesPath();
    final path = p.join(dbPath, _dbName);

    return openDatabase(
      path,
      version: _dbVersion,
      onCreate: (db, version) async {
        await _createSchema(db);
        await _seed(db);
      },
      onUpgrade: (db, oldVersion, newVersion) async {
        if (oldVersion < 2) {
          // Example migration: add index
          await db.execute('CREATE INDEX IF NOT EXISTS idx_books_title ON books(title);');
        }
      },
    );
  }

  static Future<void> _createSchema(Database db) async {
    await db.execute('''
      CREATE TABLE authors (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL
      );
    ''');
    await db.execute('''
      CREATE TABLE books (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        title TEXT NOT NULL,
        published_at INTEGER,  -- unix epoch millis
        price_cents INTEGER NOT NULL DEFAULT 0,
        category TEXT
      );
    ''');
    await db.execute('''
      CREATE TABLE book_authors (
        book_id INTEGER NOT NULL,
        author_id INTEGER NOT NULL,
        PRIMARY KEY(book_id, author_id),
        FOREIGN KEY(book_id) REFERENCES books(id) ON DELETE CASCADE,
        FOREIGN KEY(author_id) REFERENCES authors(id) ON DELETE CASCADE
      );
    ''');

    await db.execute('CREATE INDEX IF NOT EXISTS idx_books_category ON books(category);');
    await db.execute('CREATE INDEX IF NOT EXISTS idx_books_published ON books(published_at);');
  }

  static Future<void> _seed(Database db) async {
    final authorIds = <String, int>{};
    for (final name in ['Ada Lovelace', 'Grace Hopper', 'Donald Knuth', 'Edsger Dijkstra']) {
      final id = await db.insert('authors', {'name': name});
      authorIds[name] = id;
    }

    Future<int> addBook(String title, DateTime? published, int priceCents, String category, List<String> authors) async {
      final bookId = await db.insert('books', {
        'title': title,
        'published_at': published?.millisecondsSinceEpoch,
        'price_cents': priceCents,
        'category': category,
      });
      for (final a in authors) {
        await db.insert('book_authors', {
          'book_id': bookId,
          'author_id': authorIds[a]!,
        });
      }
      return bookId;
    }

    await addBook('Algorithms Unlocked', DateTime(2013, 1, 1), 2599, 'CS', ['Donald Knuth']);
    await addBook('Programming Pearls', DateTime(1999, 5, 1), 1999, 'CS', ['Edsger Dijkstra']);
    await addBook('Compilers 101', DateTime(2010, 2, 1), 2999, 'Systems', ['Grace Hopper']);
    await addBook('Analytical Engines', DateTime(1843, 8, 1), 1599, 'History', ['Ada Lovelace']);
  }
}

Checking SQLite feature support at runtime

Some advanced features (CTEs, window functions, JSON) depend on the SQLite version the device ships with. Detect it once and cache:

Future<String> sqliteVersion(Database db) async {
  final row = (await db.rawQuery('SELECT sqlite_version() AS v')).first;
  return row['v'] as String; // e.g., 3.39.4
}
  • Common Table Expressions (WITH): widely available on modern devices.
  • Window functions (OVER …): require SQLite 3.25.0+.
  • JSON1/FTS extensions: may or may not be compiled in; check before relying on them.

Data access helpers

A small mapper keeps your UI clean.

class BookWithAuthors {
  final int id;
  final String title;
  final DateTime? publishedAt;
  final int priceCents;
  final String? category;
  final List<String> authors;

  BookWithAuthors({
    required this.id,
    required this.title,
    required this.publishedAt,
    required this.priceCents,
    required this.category,
    required this.authors,
  });
}

Complex JOIN: one-to-many and many-to-many

Retrieve books with their authors, sorted by newest first. Use GROUP_CONCAT to flatten authors per book.

Future<List<BookWithAuthors>> fetchBooksWithAuthors(Database db, {String? category}) async {
  final where = StringBuffer('1=1');
  final args = <Object?>[];
  if (category != null) {
    where.write(' AND b.category = ?');
    args.add(category);
  }

  final sql = '''
    SELECT
      b.id,
      b.title,
      b.published_at,
      b.price_cents,
      b.category,
      GROUP_CONCAT(a.name, ', ') AS authors
    FROM books b
    JOIN book_authors ba ON ba.book_id = b.id
    JOIN authors a ON a.id = ba.author_id
    WHERE $where
    GROUP BY b.id
    ORDER BY b.published_at DESC NULLS LAST, b.id DESC
  ''';

  final rows = await db.rawQuery(sql, args);
  return rows.map((r) => BookWithAuthors(
    id: r['id'] as int,
    title: r['title'] as String,
    publishedAt: r['published_at'] == null ? null : DateTime.fromMillisecondsSinceEpoch(r['published_at'] as int),
    priceCents: r['price_cents'] as int,
    category: r['category'] as String?,
    authors: (r['authors'] as String?)?.split(', ').toList() ?? const [],
  )).toList();
}

Notes:

  • Always use parameter placeholders (?) instead of string interpolation to prevent SQL injection and to let SQLite cache query plans.
  • GROUP_CONCAT’s default separator is comma; specify it explicitly for clarity.

Aggregations and HAVING: top categories by revenue

Future<List<Map<String, Object?>>> topCategoriesByRevenue(Database db, {int minBooks = 1, int limit = 5}) async {
  final sql = '''
    SELECT category,
           COUNT(*) AS book_count,
           SUM(price_cents) AS revenue_cents,
           AVG(price_cents) AS avg_price_cents
    FROM books
    GROUP BY category
    HAVING COUNT(*) >= ?
    ORDER BY revenue_cents DESC NULLS LAST
    LIMIT ?
  ''';
  return db.rawQuery(sql, [minBooks, limit]);
}

Subqueries: filter by author and recent year

Find books by a given author published after a dynamic cut‑off.

Future<List<Map<String, Object?>>> recentBooksByAuthor(Database db, String authorName, int year) async {
  final cutoff = DateTime(year, 1, 1).millisecondsSinceEpoch;
  final sql = '''
    SELECT * FROM books
    WHERE id IN (
      SELECT ba.book_id
      FROM book_authors ba
      JOIN authors a ON a.id = ba.author_id
      WHERE a.name = ?
    )
    AND (published_at IS NOT NULL AND published_at >= ?)
    ORDER BY published_at DESC
  ''';
  return db.rawQuery(sql, [authorName, cutoff]);
}

Dynamic IN clauses safely

Build placeholders based on list length.

Future<List<Map<String, Object?>>> booksInCategories(Database db, List<String> categories) async {
  if (categories.isEmpty) return [];
  final qMarks = List.filled(categories.length, '?').join(',');
  final sql = 'SELECT * FROM books WHERE category IN ($qMarks)';
  return db.rawQuery(sql, categories);
}

CTEs (WITH): readable, composable queries

Common Table Expressions improve clarity for staged logic. Example: monthly revenue for the last N months.

Future<List<Map<String, Object?>>> monthlyRevenue(Database db, {int months = 6}) async {
  final now = DateTime.now();
  final start = DateTime(now.year, now.month - (months - 1), 1);
  final startMs = start.millisecondsSinceEpoch;

  final sql = '''
    WITH filtered AS (
      SELECT price_cents, published_at
      FROM books
      WHERE published_at >= ?
    ), buckets AS (
      SELECT
        strftime('%Y-%m', datetime(published_at/1000, 'unixepoch')) AS ym,
        price_cents
      FROM filtered
    )
    SELECT ym,
           SUM(price_cents) AS revenue_cents,
           COUNT(*) AS book_count
    FROM buckets
    GROUP BY ym
    ORDER BY ym ASC
  ''';
  return db.rawQuery(sql, [startMs]);
}

Window functions (if available): running totals and ranks

If SQLite ≥ 3.25.0 on the device, you can compute running totals without self‑joins.

Future<List<Map<String, Object?>>> runningRevenue(Database db) async {
  final version = await sqliteVersion(db);
  bool supportsWindows = _atLeast(version, [3, 25, 0]);
  if (!supportsWindows) {
    // Fallback: compute in Dart after fetching monthly aggregates
    final monthly = await monthlyRevenue(db, months: 12);
    int acc = 0;
    return monthly.map((row) {
      acc += (row['revenue_cents'] as int? ?? 0);
      return {...row, 'running_revenue_cents': acc};
    }).toList();
  }

  final sql = '''
    WITH monthly AS (
      SELECT strftime('%Y-%m', datetime(published_at/1000, 'unixepoch')) AS ym,
             SUM(price_cents) AS revenue_cents
      FROM books
      GROUP BY ym
      ORDER BY ym
    )
    SELECT ym,
           revenue_cents,
           SUM(revenue_cents) OVER (ORDER BY ym ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_revenue_cents
    FROM monthly
  ''';
  return db.rawQuery(sql);
}

bool _atLeast(String v, List<int> min) {
  final parts = v.split('.').map(int.parse).toList();
  for (var i = 0; i < min.length; i++) {
    final a = i < parts.length ? parts[i] : 0;
    if (a != min[i]) return a > min[i];
  }
  return true;
}

Text search: LIKE vs FTS

  • LIKE/ILIKE pattern matching works everywhere but can be slow. Add an index on title for prefix searches (e.g., title LIKE 'Pro%').
  • FTS3/4/5 offer full‑text capabilities if compiled into the platform SQLite. Check via a quick probe; if not available, fall back to LIKE.
Future<List<Map<String, Object?>>> searchTitles(Database db, String q) async {
  // Prefix search using indexed LIKE
  return db.rawQuery('SELECT * FROM books WHERE title LIKE ? ORDER BY title', ['${q.replaceAll('%', '\\%')}%']);
}

Optional FTS virtual table (verify support first):

CREATE VIRTUAL TABLE IF NOT EXISTS books_fts USING fts4(title);
INSERT INTO books_fts(rowid, title) SELECT id, title FROM books;
-- Query: SELECT id FROM books_fts WHERE books_fts MATCH 'algorithm*';

Keyset pagination for large lists

OFFSET/LIMIT becomes slow on big tables. Use keyset (a.k.a. seek) pagination with the last seen sort key.

Future<List<Map<String, Object?>>> pageBooksByPublished(Database db, {int? beforeMs, int limit = 20}) async {
  final args = <Object?>[];
  final where = StringBuffer('1=1');
  if (beforeMs != null) {
    where.write(' AND (published_at < ? OR (published_at IS NULL AND id < ?))');
    args..add(beforeMs)..add(1 << 62); // large id fallback for nulls
  }
  final sql = '''
    SELECT * FROM books
    WHERE $where
    ORDER BY published_at DESC NULLS LAST, id DESC
    LIMIT ?
  ''';
  args.add(limit);
  return db.rawQuery(sql, args);
}

Performance: indexes, analyze, and EXPLAIN

  • Create focused indexes on columns used in WHERE/JOIN/ORDER BY.
  • Avoid leading wildcards in LIKE.
  • Use covering indexes when possible (add projected columns to the index in other DBs; in SQLite, keep rowid lookups minimal by selective predicates).
  • Inspect plans:
Future<List<Map<String, Object?>>> explainPlan(Database db, String sql, [List<Object?> args = const []]) async {
  return db.rawQuery('EXPLAIN QUERY PLAN $sql', args);
}

Example:

final plan = await explainPlan(db, 'SELECT * FROM books WHERE category = ? AND published_at >= ?', ['CS', DateTime(2010).millisecondsSinceEpoch]);
print(plan); // Look for "USING INDEX" or scans

Transactions and batch writes

Group multiple statements atomically and for speed.

Future<void> bulkInsertBooks(Database db, List<Map<String, Object?>> books, Map<String, int> authorIds) async {
  await db.transaction((txn) async {
    final batch = txn.batch();
    for (final b in books) {
      batch.insert('books', b);
    }
    final results = await batch.commit(noResult: false, continueOnError: false);

    // Link authors in another batch, using returned rowids
    final linkBatch = txn.batch();
    for (var i = 0; i < books.length; i++) {
      final bookId = results[i] as int;
      final authors = (books[i]['_authors'] as List<String>? ?? const []);
      for (final name in authors) {
        linkBatch.insert('book_authors', {'book_id': bookId, 'author_id': authorIds[name]!});
      }
    }
    await linkBatch.commit();
  });
}

Migrations without drama

  • Keep a schema version in code (see _dbVersion) and write incremental migrations.
  • Never drop columns on user devices; prefer new tables + copy or add nullable columns.
  • Write idempotent statements: CREATE INDEX IF NOT EXISTS ....

Error handling and time values

  • Catch DatabaseException to identify constraint violations or syntax errors.
  • Store times as INTEGER epoch millis for consistent sorting and math; convert to DateTime in Dart.
try {
  await db.insert('authors', {'name': 'Ada Lovelace'});
} on DatabaseException catch (e) {
  // Log and surface a friendly message
  if (e.isUniqueConstraintError()) {
    // Handle duplicates
  }
}

Helper for constraint detection:

extension on DatabaseException {
  bool isUniqueConstraintError() => toString().contains('UNIQUE');
}

Testing complex queries

  • Use a temporary in‑memory DB for fast unit tests: openDatabase(inMemoryDatabasePath, version: ..., onCreate: ...).
  • Seed minimal fixtures per test.
  • Assert both result shape and ordering.
import 'package:flutter_test/flutter_test.dart';

void main() {
  test('topCategoriesByRevenue returns sorted categories', () async {
    final db = await openDatabase(inMemoryDatabasePath, version: 1, onCreate: (db, _) async {
      await AppDatabase._createSchema(db);
      await AppDatabase._seed(db);
    });
    final rows = await topCategoriesByRevenue(db);
    expect(rows.isNotEmpty, true);
  });
}

Troubleshooting cheat‑sheet

  • Query returns duplicates: missing GROUP BY or DISTINCT.
  • Slow query: missing index, leading wildcard in LIKE, large OFFSET.
  • Syntax error: feature not supported on device SQLite; check SELECT sqlite_version().
  • JOIN returns fewer rows: JOIN condition too restrictive; consider LEFT JOIN to keep unmatched rows.
  • Date filters off by timezone: epoch millis are UTC; convert consistently.

Putting it together

Complex queries in sqflite are just SQL—plan them, index thoughtfully, and verify with EXPLAIN. Start with clean schema design, use parameterized queries, and add CTEs or window functions when the device SQLite supports them. With the patterns above—JOINs, subqueries, aggregations, pagination, and transactions—you can keep your Flutter app fast and robust even with demanding offline data needs.

Tags

Flutter SQLite sqflite Mobile Database Dart Query Optimization Tutorial

Related Posts