sqflite: Local SQL Databases in Flutter

WHY IT EXISTS: Apps often need to store more than simple key-value pairs locally. They need to store structured, relational data that can be queried efficiently without a network connection. sqflite provides this by giving Flutter apps access to the powerful, battle-tested SQLite database engine that's built into iOS and Android.

THE MENTAL MODEL: Think of sqflite as a bridge to a private, file-based SQL database that lives within your app's sandboxed storage. You don't manage a server; you manage a database file. You interact with it using standard SQL strings or type-safe Dart helper methods. All operations are asynchronous and run on a background thread, keeping your UI responsive.

HOW IT WORKS: You start by adding sqflite to your pubspec.yaml. Then, you call openDatabase('my_app.db'). This function finds the appropriate directory on the device, opens the database file if it exists, or creates it if it doesn't. The onCreate callback is your chance to execute CREATE TABLE statements for a new database. You perform operations using db.rawQuery(), db.rawInsert(), or helper methods like db.insert(). These helpers often work well with model classes that have toMap() methods to convert Dart objects into a format suitable for database insertion.

WHEN TO USE IT: Use sqflite for storing significant amounts of structured, relational data locally. It's perfect for apps like note-takers, expense trackers, or any application that needs an offline cache of complex data that can be queried. Its support for transactions ensures data integrity for multi-step operations.

WHEN NOT TO USE IT: For simple key-value storage (like user preferences or a theme setting), the shared_preferences package is a lighter-weight and simpler solution. sqflite is overkill for non-relational or document-style data. It is also a local-only solution; it does not handle data synchronization across devices or with a backend server.

ONE CANONICAL EXAMPLE: A common pattern is to create a table and insert data. First, open the database with a version and an onCreate callback: Database db = await openDatabase('path/to/db', version: 1, onCreate: (db, version) => db.execute('CREATE TABLE Users (id INTEGER PRIMARY KEY, name TEXT)')); Then, to insert a new user, you can use a helper method which takes a map: await db.insert('Users', {'name': 'Alice'}); To query for all users, you get back a list of maps: List<Map<String, dynamic>> users = await db.query('Users');