Flutter Functions


Using Flutter with SQLite: Offline Data Storage in Apps

In the ever-evolving landscape of mobile app development, one thing remains constant – the need for efficient data storage. Whether your app requires storing user profiles, notes, or any other kind of data, having a reliable and performant storage solution is crucial. One such solution that has gained popularity among Flutter developers is SQLite.

Using Flutter with SQLite: Offline Data Storage in Apps

SQLite is a self-contained, serverless, and zero-configuration SQL database engine. It’s a perfect fit for mobile app development, particularly in the Flutter ecosystem. In this comprehensive guide, we’ll explore how to use Flutter with SQLite for offline data storage in your apps. We’ll cover everything from setting up your project to performing CRUD (Create, Read, Update, Delete) operations with SQLite. Let’s dive in!

1. Setting up Your Flutter Project

Before diving into SQLite, you’ll need to set up a Flutter project if you haven’t already. You can do this using the Flutter CLI or your preferred IDE. Once you have your project ready, you can start integrating SQLite.

1.1. Adding Dependencies

The first step is to add the necessary dependencies to your pubspec.yaml file. To use SQLite in Flutter, you’ll need the sqflite package for SQLite support and path_provider for accessing the device’s file system. Here’s how you can add them:

    sdk: flutter
  sqflite: ^2.0.0
  path_provider: ^2.0.0

After adding these dependencies, run ‘flutter pub get’ to fetch and install them in your project.

2. Creating a Database Helper

To work with SQLite efficiently in your Flutter app, it’s a good practice to create a database helper class. This class will encapsulate the database operations and make your code more organized and maintainable.

2.1. Opening and Closing the Database

Let’s start by creating a class named DatabaseHelper to manage the database. Here’s a snippet of how you can open and close the database using sqflite:

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

class DatabaseHelper {
  static final DatabaseHelper _instance = DatabaseHelper._internal();
  factory DatabaseHelper() => _instance;

  static Database? _database;


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

    _database = await initDatabase();
    return _database;

  Future<Database> initDatabase() async {
    final documentsDirectory = await getDatabasesPath();
    final path = join(documentsDirectory, 'my_app_database.db');

    return await openDatabase(
      version: 1,
      onCreate: _createDatabase,

  Future<void> _createDatabase(Database db, int version) async {
    // Create your database tables here
    // Example: await db.execute('CREATE TABLE your_table (id INTEGER PRIMARY KEY, name TEXT)');

  Future<void> closeDatabase() async {
    final db = await database;

In this code:

  • We define a singleton DatabaseHelper class to ensure there’s only one instance of the database helper throughout the app.
  • The initDatabase function initializes the SQLite database and specifies its version. You can also create tables in the onCreate callback.
  • The closeDatabase function allows you to close the database when you’re done with it.

2.2. Creating Tables

To work with data in SQLite, you’ll need to create tables to store your data. You can define these tables in the _createDatabase method, as shown above. Here’s an example of creating a table to store user profiles:

await db.execute('''
  CREATE TABLE users (
    name TEXT,
    email TEXT

This code creates a table named users with columns for id, name, and email. You can customize the table structure to match your app’s data requirements.

3. CRUD Operations with SQLite

Now that you have your database set up, let’s explore how to perform CRUD operations with SQLite.

3.1. Inserting Data

To insert data into a SQLite table, you can use the insert method provided by sqflite. Here’s an example of how to insert a user into the users table:

Future<int> insertUser(User user) async {
  final db = await database;
  return await db.insert('users', user.toMap());

In this code:

  • We assume there’s a User class with a toMap method that converts a User object into a map of column names to values.

3.2. Retrieving Data

To retrieve data from a SQLite table, you can use the query method. Here’s an example of how to retrieve all users from the users table:

Future<List<User>> getUsers() async {
  final db = await database;
  final List<Map<String, dynamic>> maps = await db.query('users');

  return List.generate(maps.length, (i) {
    return User.fromMap(maps[i]);

In this code:

  • We use the query method to retrieve all rows from the users table.
  • We then use the List.generate method to convert the result into a list of User objects.

3.3. Updating Data

To update data in a SQLite table, you can use the update method. Here’s an example of how to update a user’s information:

Future<int> updateUser(User user) async {
  final db = await database;
  return await db.update(
    where: 'id = ?',
    whereArgs: [user.id],

In this code:

  • We use the update method, specifying the table name, the new values, and a WHERE clause to identify the row to update.

3.4. Deleting Data

To delete data from a SQLite table, you can use the delete method. Here’s an example of how to delete a user:

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

In this code:

  • We use the delete method, specifying the table name and a WHERE clause to identify the row to delete.

4. Working with Models

In the previous examples, we assumed the existence of a User class with toMap and fromMap methods for converting between User objects and database rows. Here’s an example of what the User class might look like:

class User {
  final int id;
  final String name;
  final String email;

    required this.id,
    required this.name,
    required this.email,

  Map<String, dynamic> toMap() {
    return {
      'id': id,
      'name': name,
      'email': email,

  factory User.fromMap(Map<String, dynamic> map) {
    return User(
      id: map['id'],
      name: map['name'],
      email: map['email'],

Creating model classes like User makes it easier to work with SQLite and ensures type safety in your code.

5. Best Practices

As you continue to develop your Flutter app with SQLite, here are some best practices to keep in mind:

  • Use Transactions: When performing multiple database operations together, wrap them in a transaction. Transactions ensure that all changes are either fully applied or fully rolled back, maintaining the integrity of your data.
  • Database Versioning: If you need to make changes to your database structure, increment the database version and implement the necessary migrations to handle schema changes.
  • Error Handling: Always handle database errors gracefully. Use try-catch blocks to capture exceptions and provide appropriate feedback to users.
  • Optimize Queries: Be mindful of query performance, especially when dealing with large datasets. Indexing columns that are frequently used in WHERE clauses can significantly improve query speed.
  • Testing: Write unit tests for your database operations. Flutter provides tools like flutter_test and mockito to help you test your database code.


In this guide, we’ve explored how to use Flutter with SQLite for efficient offline data storage in your mobile apps. We covered setting up your Flutter project, creating a database helper, performing CRUD operations, and working with models. By following best practices and leveraging SQLite’s power, you can build robust and performant Flutter apps that excel in data storage and retrieval.

SQLite offers a reliable and efficient solution for local data storage in your Flutter applications. It empowers you to create feature-rich apps that can operate offline seamlessly. So, go ahead, implement SQLite in your Flutter project, and take your app’s data management to the next level. Happy coding!

Previously at
Flag Argentina
time icon
Full Stack Systems Analyst with a strong focus on Flutter development. Over 5 years of expertise in Flutter, creating mobile applications with a user-centric approach.