Flutter Sqlite Tutorial

Flutter SQLite CRUD Tutorial with the sqflite Package

This tutorial demonstrates how to store structured data locally in a Flutter application using SQLite and the sqflite package. The example creates a car database and implements the four basic database operations: create, read, update, and delete.

Each car record contains an automatically generated ID, a car name, and the number of miles driven. The application provides separate tabs for inserting records, viewing all records, searching by name, updating an existing record, and deleting a record by ID.

Compatibility note: The complete project below was written with an older Flutter and Dart API. It is useful for understanding SQLite CRUD flow, but current Flutter projects require null-safe fields, modern button widgets such as ElevatedButton, and ScaffoldMessenger for SnackBars. Modern alternatives are explained after the original example.

How the Flutter SQLite Example Is Organized

The application uses three Dart files with separate responsibilities:

• car.dart defines the model used to represent a row from the SQLite table.
• dbhelper.dart opens the database, creates the table, and performs SQL operations.
• main.dart builds the Flutter interface and connects user actions to the database helper.

The user interface contains a TabBar and TabBarView with Insert, View, Query, Update, and Delete screens.

• Insert: Reads the car name and mileage from TextField widgets, creates a Car object, and inserts it into the table.
• View: Queries every row and displays the results in a ListView.
• Query: Searches for cars whose names contain the text entered by the user.
• Update: Updates the name and mileage of the row identified by its ID.
• Delete: Deletes the row matching the supplied ID.

Add sqflite and path Dependencies

Add the sqflite and path packages under the dependencies section of pubspec.yaml. The sqflite package provides the SQLite API, while path is used to construct a platform-safe database file path.

dependencies:
  flutter:
    sdk: flutter
  sqflite:
  path:

For a new project, the packages can also be added from the terminal. Flutter resolves compatible package versions and updates pubspec.yaml.

flutter pub add sqflite path

Run flutter pub get after editing the dependency file manually. Package versions should normally be recorded in pubspec.yaml so that dependency resolution remains reproducible across development environments.

Create the Car Model for SQLite Rows

Under the lib folder, create car.dart. The model converts a SQLite row represented as a map into a Dart object and converts an object back into a map for database operations.

car.dart

import 'package:flutter_sqlite_tutorial/dbhelper.dart';

class Car {
  int id;
  String name;
  int miles;

  Car(this.id, this.name, this.miles);

  Car.fromMap(Map<String, dynamic> map) {
    id = map['id'];
    name = map['name'];
    miles = map['miles'];
  }

  Map<String, dynamic> toMap() {
    return {
      DatabaseHelper.columnId: id,
      DatabaseHelper.columnName: name,
      DatabaseHelper.columnMiles: miles,
    };
  }
}

The original model predates Dart null safety. In a current Flutter project, the database-generated ID can be nullable until a row has been inserted.

class Car {
  const Car({this.id, required this.name, required this.miles});

  final int? id;
  final String name;
  final int miles;

  factory Car.fromMap(Map<String, Object?> map) {
    return Car(
      id: map['id'] as int?,
      name: map['name'] as String,
      miles: map['miles'] as int,
    );
  }

  Map<String, Object?> toMap() {
    return {
      if (id != null) 'id': id,
      'name': name,
      'miles': miles,
    };
  }
}

Leaving the ID out of an insert map allows SQLite to generate it because the table defines the ID column as INTEGER PRIMARY KEY AUTOINCREMENT.

Create the Flutter SQLite Database Helper

Create dbhelper.dart under lib. This class opens one application-wide database connection, creates the cars table when the database is first opened, and exposes methods for inserting, reading, searching, updating, and deleting rows.

dbhelper.dart

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

class DatabaseHelper {

  static final _databaseName = "cardb.db";
  static final _databaseVersion = 1;

  static final table = 'cars_table';

  static final columnId = 'id';
  static final columnName = 'name';
  static final columnMiles = 'miles';

  // make this a singleton class
  DatabaseHelper._privateConstructor();
  static final DatabaseHelper instance = DatabaseHelper._privateConstructor();

  // only have a single app-wide reference to the database
  static Database _database;
  Future<Database> get database async {
    if (_database != null) return _database;
    // lazily instantiate the db the first time it is accessed
    _database = await _initDatabase();
    return _database;
  }

  // this opens the database (and creates it if it doesn't exist)
  _initDatabase() async {
    String path = join(await getDatabasesPath(), _databaseName);
    return await openDatabase(path,
        version: _databaseVersion,
        onCreate: _onCreate);
  }

  // SQL code to create the database table
  Future _onCreate(Database db, int version) async {
    await db.execute('''
          CREATE TABLE $table (
            $columnId INTEGER PRIMARY KEY AUTOINCREMENT,
            $columnName TEXT NOT NULL,
            $columnMiles INTEGER NOT NULL
          )
          ''');
  }

  // Helper methods

  // Inserts a row in the database where each key in the Map is a column name
  // and the value is the column value. The return value is the id of the
  // inserted row.
  Future<int> insert(Car car) async {
    Database db = await instance.database;
    return await db.insert(table, {'name': car.name, 'miles': car.miles});
  }

  // All of the rows are returned as a list of maps, where each map is
  // a key-value list of columns.
  Future<List<Map<String, dynamic>>> queryAllRows() async {
    Database db = await instance.database;
    return await db.query(table);
  }
  
  // Queries rows based on the argument received
  Future<List<Map<String, dynamic>>> queryRows(name) async {
    Database db = await instance.database;
    return await db.query(table, where: "$columnName LIKE '%$name%'");
  }

  // All of the methods (insert, query, update, delete) can also be done using
  // raw SQL commands. This method uses a raw query to give the row count.
  Future<int> queryRowCount() async {
    Database db = await instance.database;
    return Sqflite.firstIntValue(await db.rawQuery('SELECT COUNT(*) FROM $table'));
  }

  // We are assuming here that the id column in the map is set. The other
  // column values will be used to update the row.
  Future<int> update(Car car) async {
    Database db = await instance.database;
    int id = car.toMap()['id'];
    return await db.update(table, car.toMap(), where: '$columnId = ?', whereArgs: [id]);
  }

  // Deletes the row specified by the id. The number of affected rows is
  // returned. This should be 1 as long as the row exists.
  Future<int> delete(int id) async {
    Database db = await instance.database;
    return await db.delete(table, where: '$columnId = ?', whereArgs: [id]);
  }
}

How the SQLite Database Is Opened

getDatabasesPath() returns the directory intended for application databases. The join() function appends cardb.db using the correct separator for the current platform. openDatabase() opens the file and calls onCreate only when the database does not already exist.

The database version is initially 1. When the schema changes in a released application, increase this number and provide an onUpgrade callback that migrates existing user data.

How the cars_table Schema Stores Records

The table contains the following columns:

Column	SQLite type	Purpose
id	INTEGER	Primary key generated automatically for each row.
name	TEXT	Required car name.
miles	INTEGER	Required whole-number mileage value.

Use whereArgs for Parameterized SQLite Queries

The update and delete methods correctly use a ? placeholder with whereArgs. Apply the same pattern to the name search instead of interpolating input directly into the SQL condition. Parameterized values avoid malformed queries when input contains quotes and keep SQL values separate from the query structure.

Future<List<Map<String, Object?>>> queryRows(String name) async {
  final db = await database;
  return db.query(
    table,
    where: '$columnName LIKE ?',
    whereArgs: ['%$name%'],
    orderBy: '$columnName ASC',
  );
}

Connect the SQLite Operations to the Flutter UI

The following original main.dart file creates the five-tab interface and connects each button or text-change event to a database helper method.

main.dart

import 'package:flutter/material.dart';
import 'package:flutter_sqlite_tutorial/car.dart';
import 'package:flutter_sqlite_tutorial/dbhelper.dart';

void main() => runApp(MyApp());

class MyApp extends StatelessWidget {
  @override
  Widget build(BuildContext context) {
    return MaterialApp(
      title: 'TutorialKart - Flutter',
      theme: ThemeData(
        primarySwatch: Colors.purple,
      ),
      home: MyHomePage(),
    );
  }
}

class MyHomePage extends StatefulWidget {
  @override
  _MyHomePageState createState() => _MyHomePageState();
}

class _MyHomePageState extends State<MyHomePage> {
  final dbHelper = DatabaseHelper.instance;

  List<Car> cars = [];
  List<Car> carsByName = [];

  //controllers used in insert operation UI
  TextEditingController nameController = TextEditingController();
  TextEditingController milesController = TextEditingController();

  //controllers used in update operation UI
  TextEditingController idUpdateController = TextEditingController();
  TextEditingController nameUpdateController = TextEditingController();
  TextEditingController milesUpdateController = TextEditingController();

  //controllers used in delete operation UI
  TextEditingController idDeleteController = TextEditingController();

  //controllers used in query operation UI
  TextEditingController queryController = TextEditingController();

  final GlobalKey<ScaffoldState> _scaffoldKey = new GlobalKey<ScaffoldState>();

  void _showMessageInScaffold(String message){
    _scaffoldKey.currentState.showSnackBar(
        SnackBar(
          content: Text(message),
        )
    );
  }

  @override
  Widget build(BuildContext context) {
    return DefaultTabController(
      length: 5,
      child: Scaffold(
        key: _scaffoldKey,
        appBar: AppBar(
          bottom: TabBar(
            tabs: [
              Tab(
                text: "Insert",
              ),
              Tab(
                text: "View",
              ),
              Tab(
                text: "Query",
              ),
              Tab(
                text: "Update",
              ),
              Tab(
                text: "Delete",
              ),
            ],
          ),
          title: Text('TutorialKart - Flutter SQLite Tutorial'),
        ),
        body: TabBarView(
          children: [
            Center(
              child: Column(
                children: <Widget>[
                  Container(
                    padding: EdgeInsets.all(20),
                    child: TextField(
                      controller: nameController,
                      decoration: InputDecoration(
                        border: OutlineInputBorder(),
                        labelText: 'Car Name',
                      ),
                    ),
                  ),
                  Container(
                    padding: EdgeInsets.all(20),
                    child: TextField(
                      controller: milesController,
                      decoration: InputDecoration(
                        border: OutlineInputBorder(),
                        labelText: 'Car Miles',
                      ),
                    ),
                  ),
                  RaisedButton(
                    child: Text('Insert Car Details'),
                    onPressed: () {
                      String name = nameController.text;
                      int miles = int.parse(milesController.text);
                      _insert(name, miles);
                    },
                  ),
                ],
              ),
            ),
            Container(
              child: ListView.builder(
                padding: const EdgeInsets.all(8),
                itemCount: cars.length + 1,
                itemBuilder: (BuildContext context, int index) {
                  if (index == cars.length) {
                    return RaisedButton(
                      child: Text('Refresh'),
                      onPressed: () {
                        setState(() {
                          _queryAll();
                        });
                      },
                    );
                  }
                  return Container(
                    height: 40,
                    child: Center(
                      child: Text(
                        '[${cars[index].id}] ${cars[index].name} - ${cars[index].miles} miles',
                        style: TextStyle(fontSize: 18),
                      ),
                    ),
                  );
                },
              ),
            ),
            Center(
              child: Column(
                children: <Widget>[
                  Container(
                    padding: EdgeInsets.all(20),
                    child: TextField(
                      controller: queryController,
                      decoration: InputDecoration(
                        border: OutlineInputBorder(),
                        labelText: 'Car Name',
                      ),
                      onChanged: (text) {
                        if (text.length >= 2) {
                          setState(() {
                            _query(text);
                          });
                        } else {
                          setState(() {
                            carsByName.clear();
                          });
                        }
                      },
                    ),
                    height: 100,
                  ),
                  Container(
                    height: 300,
                    child: ListView.builder(
                      padding: const EdgeInsets.all(8),
                      itemCount: carsByName.length,
                      itemBuilder: (BuildContext context, int index) {
                        return Container(
                          height: 50,
                          margin: EdgeInsets.all(2),
                          child: Center(
                            child: Text(
                              '[${carsByName[index].id}] ${carsByName[index].name} - ${carsByName[index].miles} miles',
                              style: TextStyle(fontSize: 18),
                            ),
                          ),
                        );
                      },
                    ),
                  ),
                ],
              ),
            ),
            Center(
              child: Column(
                children: <Widget>[
                  Container(
                    padding: EdgeInsets.all(20),
                    child: TextField(
                      controller: idUpdateController,
                      decoration: InputDecoration(
                        border: OutlineInputBorder(),
                        labelText: 'Car id',
                      ),
                    ),
                  ),
                  Container(
                    padding: EdgeInsets.all(20),
                    child: TextField(
                      controller: nameUpdateController,
                      decoration: InputDecoration(
                        border: OutlineInputBorder(),
                        labelText: 'Car Name',
                      ),
                    ),
                  ),
                  Container(
                    padding: EdgeInsets.all(20),
                    child: TextField(
                      controller: milesUpdateController,
                      decoration: InputDecoration(
                        border: OutlineInputBorder(),
                        labelText: 'Car Miles',
                      ),
                    ),
                  ),
                  RaisedButton(
                    child: Text('Update Car Details'),
                    onPressed: () {
                      int id = int.parse(idUpdateController.text);
                      String name = nameUpdateController.text;
                      int miles = int.parse(milesUpdateController.text);
                      _update(id, name, miles);
                    },
                  ),
                ],
              ),
            ),
            Center(
              child: Column(
                children: <Widget>[
                  Container(
                    padding: EdgeInsets.all(20),
                    child: TextField(
                      controller: idDeleteController,
                      decoration: InputDecoration(
                        border: OutlineInputBorder(),
                        labelText: 'Car id',
                      ),
                    ),
                  ),
                  RaisedButton(
                    child: Text('Delete'),
                    onPressed: () {
                      int id = int.parse(idDeleteController.text);
                      _delete(id);
                    },
                  ),
                ],
              ),
            ),
          ],
        ),
      ),
    );
  }

  void _insert(name, miles) async {
    // row to insert
    Map<String, dynamic> row = {
      DatabaseHelper.columnName: name,
      DatabaseHelper.columnMiles: miles
    };
    Car car = Car.fromMap(row);
    final id = await dbHelper.insert(car);
    _showMessageInScaffold('inserted row id: $id');
  }

  void _queryAll() async {
    final allRows = await dbHelper.queryAllRows();
    cars.clear();
    allRows.forEach((row) => cars.add(Car.fromMap(row)));
    _showMessageInScaffold('Query done.');
    setState(() {});
  }

  void _query(name) async {
    final allRows = await dbHelper.queryRows(name);
    carsByName.clear();
    allRows.forEach((row) => carsByName.add(Car.fromMap(row)));
  }

  void _update(id, name, miles) async {
    // row to update
    Car car = Car(id, name, miles);
    final rowsAffected = await dbHelper.update(car);
    _showMessageInScaffold('updated $rowsAffected row(s)');
  }

  void _delete(id) async {
    // Assuming that the number of rows is the id for the last row.
    final rowsDeleted = await dbHelper.delete(id);
    _showMessageInScaffold('deleted $rowsDeleted row(s): row $id');
  }
}

What Each Flutter SQLite CRUD Method Does

Insert a Car into SQLite

The Insert tab collects a name and mileage value. The helper calls db.insert() with a map containing the table columns. The returned integer is the ID of the newly inserted row.

Read and Display All Car Rows

The View tab calls queryAllRows(). Each returned map is converted into a Car object and added to the list displayed by ListView.builder.

Search SQLite Rows by Car Name

The Query tab starts searching after at least two characters have been entered. The SQL LIKE expression uses percent signs around the search term, so it matches the term anywhere in the car name.

Update a Car Record by ID

The Update tab creates a Car object containing an existing ID and the replacement values. The helper limits the update with where: 'id = ?'. The returned value is the number of rows affected.

Delete a Car Record by ID

The Delete tab passes an integer ID to db.delete(). SQLite deletes only rows matching that ID and returns the number of deleted rows. A result of zero means that no matching record was found.

Modern Flutter Changes Needed for the Original Example

The database concepts in the original application remain applicable, but several Flutter and Dart APIs have changed.

• Declare the cached database as Database? and check it for null before opening a new connection.
• Use null-safe model fields, typically int? id for an ID generated during insertion.
• Replace RaisedButton with ElevatedButton.
• Display SnackBars with ScaffoldMessenger.of(context).showSnackBar().
• Dispose every TextEditingController in the State object’s dispose() method.
• Use int.tryParse() and validate empty fields instead of calling int.parse() directly on user input.

void showMessage(String message) {
  ScaffoldMessenger.of(context).showSnackBar(
    SnackBar(content: Text(message)),
  );
}

void insertFromForm() {
  final name = nameController.text.trim();
  final miles = int.tryParse(milesController.text.trim());

  if (name.isEmpty || miles == null || miles < 0) {
    showMessage('Enter a car name and a valid mileage value.');
    return;
  }

  _insert(name, miles);
}

Controllers should be released when the widget is removed from the tree.

@override
void dispose() {
  nameController.dispose();
  milesController.dispose();
  idUpdateController.dispose();
  nameUpdateController.dispose();
  milesUpdateController.dispose();
  idDeleteController.dispose();
  queryController.dispose();
  super.dispose();
}

Run and Test the Flutter SQLite Application

SQLite access is asynchronous, so test the application on a supported mobile or desktop target rather than expecting the database calls to behave like synchronous in-memory operations.

1. Run flutter pub get.
2. Start an Android emulator, iOS simulator, or supported desktop target.
3. Run the project with flutter run.
4. Insert two or more records and note their generated IDs.
5. Refresh the View tab and confirm that every inserted record appears.
6. Search using part of a car name.
7. Update one record and refresh the list to verify the change.
8. Delete a record and confirm that it no longer appears.

Output

Common Flutter SQLite Problems

The Database Schema Does Not Change During Development

The onCreate callback runs only when the database file is first created. Editing the CREATE TABLE statement does not modify an existing database. During development, uninstall the app or delete its database, or increase the version and implement onUpgrade.

int.parse Throws a FormatException

int.parse() throws an exception for empty or non-numeric input. Validate form fields and use int.tryParse() before calling an insert, update, or delete operation.

The List Does Not Refresh After a Database Operation

Await the database operation, fetch the current rows again, and call setState() only after the new list is ready. Calling an asynchronous method inside setState() does not make that method synchronous.

Database Is Closed While Queries Are Running

Keep one managed database connection for normal application use. Do not close the shared database after each CRUD method. Close it only when the application architecture has a clear lifecycle point for releasing the connection.

Flutter SQLite Tutorial FAQs

What is sqflite in Flutter?

sqflite is a Flutter plugin that provides access to SQLite databases. It supports opening database files, executing SQL, using transactions, and performing insert, query, update, and delete operations.

Where is a Flutter SQLite database stored?

The database is stored in the application’s private database directory on the device. Use getDatabasesPath() and join() to construct the path instead of hard-coding a platform-specific location.

Does SQLite data remain after restarting a Flutter app?

Yes. SQLite writes records to a database file, so the data normally remains after the application is closed or the device is restarted. It is generally removed when the app is uninstalled or when application data is cleared.

How do I update an existing SQLite table in Flutter?

Increase the database version passed to openDatabase() and implement onUpgrade. The migration can execute statements such as ALTER TABLE or create and copy data into a replacement table when more extensive schema changes are required.

Can sqflite be used directly in a Flutter web app?

The standard mobile-oriented sqflite implementation is not a direct browser SQLite solution. A Flutter web project needs a storage package and implementation designed for browser environments.

Flutter SQLite CRUD Review Checklist

• The sqflite and path dependencies are declared and resolved.
• The database path is created with getDatabasesPath() and join().
• The table defines an integer primary key and appropriate required columns.
• User-supplied query values are passed through whereArgs.
• Numeric input is checked with int.tryParse().
• Every asynchronous database call is awaited before the UI is refreshed.
• Schema changes include a version increase and an onUpgrade migration.
• Text editing controllers are disposed when the StatefulWidget is removed.

Summary of SQLite Operations in Flutter

In this Flutter Tutorial, we created a local SQLite database, defined a model for database rows, and connected insert, query, update, and delete methods to a Flutter interface. For a current project, use null-safe Dart, parameterized query values, validated form input, modern Flutter widgets, and explicit database migrations.