Flutter Mobile Development · Lesson

SQLite Database Integration

Integrate SQLite databases into your Flutter app using the `sqflite` package for structured and relational data storage.

SQLite Database Integration is a free Flutter Mobile Development lesson on CoddyKit — lesson 2 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the Flutter Mobile Development learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Storing Data with SQLite

Welcome! In this lesson, we'll learn how to integrate a local database into your Flutter app using SQLite.

SQLite is a lightweight, serverless, self-contained, and transactional relational database engine. It's perfect for storing structured data directly on the user's device without needing a separate server process.

Getting `sqflite` Ready

To use SQLite in Flutter, we rely on the popular sqflite package. It provides a robust interface to SQLite databases.

First, add it (along with path_provider and path for database file management) to your pubspec.yaml file under dependencies:

dependencies:
flutter:
sdk: flutter
sqflite: ^2.3.0
path_provider: ^2.1.1
path: ^1.8.3

Run flutter pub get in your terminal after adding these.

Database Helper Class

It's a best practice to create a dedicated "helper" class to manage your database operations. This centralizes logic for opening, creating, and executing queries.

This class will typically handle:

Initializing and opening the database connection.
Creating tables when the database is first opened.
Providing a single instance (singleton) of the database for the entire application.
Abstracting direct SQL queries.

Initializing the Database

Let's start building our DatabaseHelper. The openDatabase function is key. It takes the database file path and an onCreate callback for initial setup.

Here's how to open or create your database:

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

// NOTE: getDatabasesPath() requires Flutter context.
// This main method is for demonstration.
// In a real Flutter app, _initDatabase would be called
// from a StatefulWidget or similar.

class DatabaseHelper {
  static final DatabaseHelper _instance = DatabaseHelper._internal();
  static Database? _database;

  factory DatabaseHelper() {
    return _instance;
  }

  DatabaseHelper._internal();

  Future<Database> get database async {
    if (_database != null) return _database!;
    _database = await _initDatabase();
    return _database!;
  }

  Future<Database> _initDatabase() async {
    // Get the default databases location for the platform
    String path = await getDatabasesPath();
    String dbPath = join(path, 'item_database.db');

    return await openDatabase(
      dbPath,
      version: 1,
      onCreate: (db, version) {
        // Table creation logic will go here
        print('Database created or opened.');
      },
    );
  }
}

void main() async {
  // This main is illustrative. sqflite operations need a
  // running Flutter app context to fully function (e.g. for getDatabasesPath).
  // When run in a Flutter app, DatabaseHelper().database
  // would ensure the DB is initialized.
  print('DatabaseHelper class defined.');
}

Creating Your First Table

Inside the onCreate callback of _initDatabase, we define our database schema using standard SQL. Let's create a simple items table.

Each item will have an id (primary key), a name (text), and a quantity (integer).

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

class DatabaseHelper {
  static final DatabaseHelper _instance = DatabaseHelper._internal();
  static Database? _database;
  factory DatabaseHelper() { return _instance; }
  DatabaseHelper._internal();
  Future<Database> get database async { if (_database != null) return _database!; _database = await _initDatabase(); return _database!; }
  Future<Database> _initDatabase() async {
    String path = await getDatabasesPath();
    String dbPath = join(path, 'item_database.db');
    return await openDatabase(dbPath, version: 1,
      onCreate: (db, version) async {
        // SQL to create the items table
        await db.execute(
          '''
          CREATE TABLE items(
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT,
            quantity INTEGER
          )
          '''
        );
        print('Table "items" created successfully.');
      },
    );
  }
}

void main() async {
  // This main is illustrative. In a Flutter app,
  // calling DatabaseHelper().database would trigger
  // _initDatabase and the onCreate callback.
  print('Database helper configured for table creation.');
}

Creating a Data Model

To work easily with database records, define a Dart class that mirrors your table's structure. This helps map data between Dart objects and database rows (which are typically represented as Map).

Here's our Item model with toMap() and fromMap() methods:

class Item {
  final int? id;
  final String name;
  final int quantity;

  Item({this.id, required this.name, required this.quantity});

  // Convert an Item into a Map. Keys must match DB column names.
  Map<String, dynamic> toMap() {
    return {
      'id': id,
      'name': name,
      'quantity': quantity,
    };
  }

  // Create an Item object from a Map (database row).
  static Item fromMap(Map<String, dynamic> map) {
    return Item(
      id: map['id'],
      name: map['name'],
      quantity: map['quantity'],
    );
  }

  @override
  String toString() {
    return 'Item(id: $id, name: $name, quantity: $quantity)';
  }
}

void main() {
  final myItem = Item(id: 1, name: 'Apple', quantity: 5);
  print('Created Item: ${myItem.toString()}');
  print('Converted to Map: ${myItem.toMap()}');

  final itemMap = {'id': 2, 'name': 'Banana', 'quantity': 3};
  final itemFromMap = Item.fromMap(itemMap);
  print('Item from Map: ${itemFromMap.toString()}');
}

Inserting Data

Now let's add a method to our DatabaseHelper to insert new items. The insert method takes the table name and a Map (from our Item.toMap()).

It returns the id of the newly inserted row.

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

// Item class (from previous scene, for self-containment)
class Item {
  final int? id; final String name; final int quantity;
  Item({this.id, required this.name, required this.quantity});
  Map<String, dynamic> toMap() { return {'id': id, 'name': name, 'quantity': quantity}; }
  static Item fromMap(Map<String, dynamic> map) { return Item(id: map['id'], name: map['name'], quantity: map['quantity']); }
  @override
  String toString() { return 'Item(id: $id, name: $name, quantity: $quantity)'; }
}

class DatabaseHelper {
  static final DatabaseHelper _instance = DatabaseHelper._internal();
  static Database? _database;
  factory DatabaseHelper() { return _instance; }
  DatabaseHelper._internal();
  Future<Database> get database async { if (_database != null) return _database!; _database = await _initDatabase(); return _database!; }
  Future<Database> _initDatabase() async {
    String path = await getDatabasesPath();
    String dbPath = join(path, 'item_database.db');
    return await openDatabase(dbPath, version: 1,
      onCreate: (db, version) async {
        await db.execute('''
          CREATE TABLE items(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, quantity INTEGER)
          ''');
      },
    );
  }

  Future<int> insertItem(Item item) async {
    final db = await database;
    return await db.insert(
      'items',
      item.toMap(),
      conflictAlgorithm: ConflictAlgorithm.replace, // Replace if ID exists
    );
  }
}

void main() async {
  // Illustrative main. In a Flutter app, you'd call:
  // final dbHelper = DatabaseHelper();
  // final newItem = Item(name: 'Milk', quantity: 2);
  // int id = await dbHelper.insertItem(newItem);
  // print('Inserted item with id: $id');
  print('Item insertion logic defined in DatabaseHelper.');
}

Querying Data

To read data, use the query method. It returns a List>, which you then convert back into your Dart Item objects using Item.fromMap().

You can query all items or specific ones using where clauses.

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

// Item class (for self-containment)
class Item {
  final int? id; final String name; final int quantity;
  Item({this.id, required this.name, required this.quantity});
  Map<String, dynamic> toMap() { return {'id': id, 'name': name, 'quantity': quantity}; }
  static Item fromMap(Map<String, dynamic> map) { return Item(id: map['id'], name: map['name'], quantity: map['quantity']); }
  @override
  String toString() { return 'Item(id: $id, name: $name, quantity: $quantity)'; }
}

class DatabaseHelper {
  static final DatabaseHelper _instance = DatabaseHelper._internal();
  static Database? _database;
  factory DatabaseHelper() { return _instance; }
  DatabaseHelper._internal();
  Future<Database> get database async { if (_database != null) return _database!; _database = await _initDatabase(); return _database!; }
  Future<Database> _initDatabase() async {
    String path = await getDatabasesPath();
    String dbPath = join(path, 'item_database.db');
    return await openDatabase(dbPath, version: 1,
      onCreate: (db, version) async {
        await db.execute('''
          CREATE TABLE items(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, quantity INTEGER)
          ''');
      },
    );
  }

  Future<List<Item>> getItems() async {
    final db = await database;
    // Query all the items from the table
    final List<Map<String, dynamic>> maps = await db.query('items');

    // Convert the List<Map<String, dynamic>> into a List<Item>.
    return List.generate(maps.length, (i) {
      return Item.fromMap(maps[i]);
    });
  }
}

void main() async {
  // Illustrative main. In a Flutter app, you'd call:
  // final dbHelper = DatabaseHelper();
  // await dbHelper.insertItem(Item(name: 'Bread', quantity: 1));
  // final items = await dbHelper.getItems();
  // print('Retrieved items: $items');
  print('Item query logic defined in DatabaseHelper.');
}

Updating Data

To change an existing record, use the update method. You provide the table name, the new data as a Map, and a where clause to specify which record(s) to update.

Always use whereArgs with placeholders (?) to pass values safely and prevent SQL injection.

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

// Item class (for self-containment)
class Item {
  final int? id; final String name; final int quantity;
  Item({this.id, required this.name, required this.quantity});
  Map<String, dynamic> toMap() { return {'id': id, 'name': name, 'quantity': quantity}; }
  static Item fromMap(Map<String, dynamic> map) { return Item(id: map['id'], name: map['name'], quantity: map['quantity']); }
  @override
  String toString() { return 'Item(id: $id, name: $name, quantity: $quantity)'; }
}

class DatabaseHelper {
  static final DatabaseHelper _instance = DatabaseHelper._internal();
  static Database? _database;
  factory DatabaseHelper() { return _instance; }
  DatabaseHelper._internal();
  Future<Database> get database async { if (_database != null) return _database!; _database = await _initDatabase(); return _database!; }
  Future<Database> _initDatabase() async {
    String path = await getDatabasesPath();
    String dbPath = join(path, 'item_database.db');
    return await openDatabase(dbPath, version: 1,
      onCreate: (db, version) async {
        await db.execute('''
          CREATE TABLE items(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, quantity INTEGER)
          ''');
      },
    );
  }

  Future<int> updateItem(Item item) async {
    final db = await database;
    return await db.update(
      'items',
      item.toMap(),
      where: 'id = ?', // Specify which item to update
      whereArgs: [item.id],
    );
  }
}

void main() async {
  // Illustrative main. In a Flutter app, you'd call:
  // final dbHelper = DatabaseHelper();
  // // Assume item with id 1 exists
  // var updatedItem = Item(id: 1, name: 'Milk', quantity: 3);
  // int count = await dbHelper.updateItem(updatedItem);
  // print('Updated $count item(s).');
  print('Item update logic defined in DatabaseHelper.');
}

Deleting Data

To remove records from your database, use the delete method. Similar to update, you provide the table name and a where clause with whereArgs to specify which records to remove.

This method returns the number of rows deleted.

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

// Item class (for self-containment)
class Item {
  final int? id; final String name; final int quantity;
  Item({this.id, required this.name, required this.quantity});
  Map<String, dynamic> toMap() { return {'id': id, 'name': name, 'quantity': quantity}; }
  static Item fromMap(Map<String, dynamic> map) { return Item(id: map['id'], name: map['name'], quantity: map['quantity']); }
  @override
  String toString() { return 'Item(id: $id, name: $name, quantity: $quantity)'; }
}

class DatabaseHelper {
  static final DatabaseHelper _instance = DatabaseHelper._internal();
  static Database? _database;
  factory DatabaseHelper() { return _instance; }
  DatabaseHelper._internal();
  Future<Database> get database async { if (_database != null) return _database!; _database = await _initDatabase(); return _database!; }
  Future<Database> _initDatabase() async {
    String path = await getDatabasesPath();
    String dbPath = join(path, 'item_database.db');
    return await openDatabase(dbPath, version: 1,
      onCreate: (db, version) async {
        await db.execute('''
          CREATE TABLE items(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, quantity INTEGER)
          ''');
      },
    );
  }

  Future<int> deleteItem(int id) async {
    final db = await database;
    return await db.delete(
      'items',
      where: 'id = ?',
      whereArgs: [id],
    );
  }
}

void main() async {
  // Illustrative main. In a Flutter app, you'd call:
  // final dbHelper = DatabaseHelper();
  // // Assume item with id 1 exists
  // int count = await dbHelper.deleteItem(1);
  // print('Deleted $count item(s).');
  print('Item deletion logic defined in DatabaseHelper.');
}

SQLite Operations Quiz

Consider an items table with columns id (INTEGER), name (TEXT), and quantity (INTEGER).

Which sqflite method would you use to retrieve all items where the quantity is greater than 10?

Recap: SQLite & `sqflite`

Great job! You've learned the fundamentals of integrating SQLite into your Flutter app using the sqflite package.

Key takeaways:

SQLite is a lightweight, local, relational database.
The sqflite package provides Dart APIs for SQLite.
A DatabaseHelper class centralizes database operations like creating, opening, and managing tables.
You can perform standard CRUD (Create, Read, Update, Delete) operations using insert, query, update, and delete methods.
Using a data model class (like Item) simplifies mapping data between Dart objects and database rows.

This knowledge is crucial for building robust Flutter apps with local data persistence!

Frequently asked questions

Is the “SQLite Database Integration” lesson free?

Yes — the full text of “SQLite Database Integration” is free to read here on the web, and the Flutter Mobile Development course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the Flutter Mobile Development course, upgrade to CoddyKit PRO.

What will I learn in “SQLite Database Integration”?

Integrate SQLite databases into your Flutter app using the `sqflite` package for structured and relational data storage. You practise Flutter Mobile Development with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start Flutter Mobile Development?

No prior experience is required. Flutter Mobile Development on CoddyKit is structured for beginners through advanced learners; this is — lesson 2 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “SQLite Database Integration” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this Flutter Mobile Development lesson?

Yes. Every Flutter Mobile Development lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to Flutter Mobile Development