SQLite with sqflite package in Flutter - Deep Dive

Overview - SQLite with sqflite package

What is it?

SQLite is a small, fast database engine that stores data in a file on your device. The sqflite package lets Flutter apps use SQLite easily to save and read data locally. It helps apps keep information even when closed or offline, like saving notes or user settings. You can create tables, add, update, and delete data using simple commands.

Why it matters

Without a local database like SQLite, apps would lose data every time they close or need an internet connection to work. This would make apps less reliable and slower. Using sqflite means your app can store data safely on the device, work offline, and load information quickly, improving user experience and trust.

Where it fits

Before learning sqflite, you should know basic Flutter app structure and Dart programming. After mastering sqflite, you can learn about syncing local data with online servers or using more advanced databases like Firebase or ObjectBox.

Mental Model

Core Idea

SQLite with sqflite is like a personal notebook inside your app where you write, read, and update information quickly and safely on the device.

Think of it like...

Imagine your app has a small filing cabinet (SQLite) where it stores papers (data). The sqflite package is the set of tools that lets you open the cabinet, find the right drawer (table), and add or remove papers easily.

┌─────────────┐
│ Flutter App │
└──────┬──────┘
       │ uses
┌──────▼──────┐
│  sqflite    │
│  package   │
└──────┬──────┘
       │ talks to
┌──────▼──────┐
│  SQLite DB  │
│ (file on    │
│  device)    │
└─────────────┘

Build-Up - 7 Steps

1

Foundation

What is SQLite and sqflite

Concept: Introduce SQLite as a local database and sqflite as the Flutter package to use it.

SQLite is a lightweight database stored as a file on your device. sqflite is a Flutter plugin that lets your app create and manage this database. You can store data in tables, just like a spreadsheet, and retrieve it anytime.

Result

You understand that sqflite connects your Flutter app to a local database stored on the device.

Knowing that sqflite is a bridge between Flutter and SQLite helps you see how data stays saved locally without internet.

2

Foundation

Setting up sqflite in Flutter

Concept: Learn how to add sqflite to your Flutter project and open a database.

Add sqflite and path packages to pubspec.yaml. Import them in your Dart file. Use openDatabase() to create or open a database file. You can specify a path and version number. This prepares your app to store data.

Result

Your app can now open a database file and is ready to create tables and store data.

Understanding setup is key because without opening the database, you cannot save or read any data.

3

Intermediate

Creating tables and inserting data

🤔Before reading on: do you think you can insert data before creating tables? Commit to your answer.

Concept: Learn how to create tables and add data rows using SQL commands in sqflite.

Use the onCreate callback in openDatabase to run SQL commands like CREATE TABLE. Then use db.insert() to add rows. Each row is a map of column names to values. This organizes your data into structured tables.

Result

Your database has tables with data rows you can query later.

Knowing that tables must exist before inserting data prevents common errors and helps organize data logically.

4

Intermediate

Querying and updating data

🤔Before reading on: do you think queries return data as objects or raw strings? Commit to your answer.

Concept: Learn how to read and update data using sqflite query and update methods.

Use db.query() to get data as a list of maps, each map representing a row. Use db.update() with conditions to change existing rows. This lets your app show saved data and modify it as needed.

Result

You can display stored data in your app and keep it current by updating rows.

Understanding how queries return structured data helps you connect database results to UI components easily.

5

Intermediate

Deleting data and closing database

Concept: Learn how to remove data and properly close the database connection.

Use db.delete() with conditions to remove rows. Always close the database with db.close() when done to free resources. This keeps your app efficient and data clean.

Result

Your app can remove unwanted data and manage resources well.

Knowing to close the database prevents memory leaks and app crashes in real use.

6

Advanced

Handling database version upgrades

🤔Before reading on: do you think changing table structure requires reopening or upgrading the database? Commit to your answer.

Concept: Learn how to manage changes in database structure using version numbers and upgrade callbacks.

When you change tables, increase the database version. Use onUpgrade callback to run SQL commands like ALTER TABLE. This updates the database without losing existing data.

Result

Your app can evolve its data structure safely as it updates.

Understanding version upgrades prevents data loss and keeps user data intact across app updates.

7

Expert

Optimizing sqflite for performance

🤔Before reading on: do you think every database call opens a new connection? Commit to your answer.

Concept: Learn advanced tips like using single database instance, batching commands, and transactions for speed and safety.

Keep one open database instance instead of opening/closing repeatedly. Use batch() to group multiple commands in one call. Use transactions to ensure all-or-nothing changes. These reduce delays and prevent data corruption.

Result

Your app runs faster and handles data safely even under heavy use.

Knowing how to optimize database access is key for smooth, professional apps that scale well.

Under the Hood

sqflite uses platform channels to communicate between Flutter (Dart) and native SQLite libraries on Android and iOS. When you call sqflite methods, it sends messages to native code that executes SQL commands on the device's SQLite engine. The results are sent back as Dart objects. The database is a single file stored in the app's private storage.

Why designed this way?

SQLite was designed as a lightweight, serverless database to embed in apps easily. sqflite was built to bridge Flutter's cross-platform Dart code with native SQLite, using platform channels for performance and compatibility. This avoids reinventing database engines and leverages proven native code.

Flutter App (Dart)
   │
   ▼ platform channels
sqflite plugin (Dart)
   │
   ▼ platform channels
Native SQLite (Android/iOS)
   │
   ▼
Database file on device storage

Myth Busters - 4 Common Misconceptions

Quick: Do you think sqflite automatically syncs data to the cloud? Commit yes or no.

Common Belief: sqflite syncs data automatically to online servers.

Reality: sqflite only manages local data storage on the device; syncing to cloud requires extra code or services.

Why it matters: Assuming automatic sync can cause data loss or confusion when offline changes don't appear online.

Quick: Can you use sqflite without opening the database first? Commit yes or no.

Common Belief: You can run queries without explicitly opening the database.

Reality: You must open the database with openDatabase() before running any commands.

Why it matters: Trying to query before opening causes runtime errors and app crashes.

Quick: Does closing the database mean you cannot reopen it later? Commit yes or no.

Common Belief: Once closed, the database cannot be reopened without restarting the app.

Reality: You can reopen the database anytime by calling openDatabase() again.

Why it matters: Misunderstanding this can lead to keeping the database open unnecessarily, wasting resources.

Quick: Is it safe to run multiple database operations at the same time without transactions? Commit yes or no.

Common Belief: Running multiple operations simultaneously without transactions is safe and consistent.

Reality: Without transactions, partial updates can happen, causing inconsistent data.

Why it matters: Ignoring transactions can lead to corrupted or incomplete data in real apps.

Expert Zone

1

Keeping a single database instance open across the app lifecycle avoids costly open/close operations and improves performance.

2

Using batch operations reduces the overhead of multiple database calls by grouping them into one atomic operation.

3

Transactions not only ensure data integrity but also improve speed by reducing disk writes.

When NOT to use

sqflite is not ideal for very large datasets or complex queries requiring advanced indexing or full-text search. For those, consider server-based databases or specialized local databases like ObjectBox or Hive.

Production Patterns

In production, apps often use a singleton pattern to manage the database instance, apply migrations carefully with versioning, and wrap database calls in repositories or services to separate concerns and enable testing.

Connections

REST API

Builds-on

Understanding local data storage with sqflite helps when syncing data with remote servers via REST APIs, enabling offline-first app design.

File System Storage

Alternative approach

Knowing sqflite contrasts with storing raw files helps choose the right method for structured versus unstructured data.

Transactional Systems in Banking

Same pattern

Database transactions in sqflite follow the same all-or-nothing principle used in banking to keep data consistent and reliable.

Common Pitfalls

#1 Trying to insert data before creating the table.

Wrong approach:

await db.insert('notes', {'title': 'Hello'});

Correct approach:

await db.execute('CREATE TABLE notes(id INTEGER PRIMARY KEY, title TEXT)');
await db.insert('notes', {'title': 'Hello'});

Root cause:

Not understanding that tables must exist before inserting data causes runtime errors.

#2 Opening and closing the database for every query.

Wrong approach:

var db = await openDatabase(path);
await db.query('notes');
await db.close();
var db2 = await openDatabase(path);

Correct approach:

var db = await openDatabase(path);
await db.query('notes');
// keep db open for reuse
// close db only when app closes

Root cause:

Misunderstanding database lifecycle leads to performance issues.

#3 Not using transactions for multiple related operations.

Wrong approach:

await db.insert('notes', note1);
await db.insert('notes', note2);

Correct approach:

await db.transaction((txn) async {
await txn.insert('notes', note1);
await txn.insert('notes', note2);
});

Root cause:

Ignoring transactions risks partial updates and data inconsistency.

Key Takeaways

sqflite is a Flutter plugin that lets you use SQLite, a local database stored as a file on the device.

You must open the database before running commands and create tables before inserting data.

Queries return data as maps, which you can use to display or update information in your app.

Managing database versions and using transactions are essential for safe and reliable data handling.

Optimizing database access with single instances and batch operations improves app performance.