Flutter & SQLite: CRUD Example (2022)

Last updated on January 3, 2022 A Goodman Loading... 25 comments

SQLite is a fast relational database that can be used to store data offline for mobile applications. CRUD means create, read, update, and delete, the four essential operations of persistent storage.

In this article, we are going to build a small Flutter app that uses SQLite to persist data.

Prerequisites

In order to fully understand the example, you should have:

  • A basic knowledge about Flutter
  • Know what SQLite is. If you don’t, please see the official docs first.
  • A computer with Flutter installed, a code editor (VS Code is great).

App Preview

The app we are going to make is an offline diary that lets users record the activities they did during the day. For simplicity’s sake, we will call each of these activities a “journal” or an “item”.

In the scope of this tutorial, “activity”, “item” and “journal” are interchangeable.

The app has a floating button that can be used to show a bottom sheet. That bottom sheet contains 2 text fields corresponding to “title” and “description”. These text fields are used to create a new “item” or update an existing “item”.

The saved “items” are fetched from the SQLite database and displayed with a list view. There are an update button and a delete button associated with each “item”.

A demo is worth than a thousand words:

Database Structure

We are going to create an SQLite database called kindacode.db. It has only a single table named items. Below is the structure of the table:

ColumnTypeDescription
idINTEGERThe id of an activity
titleTEXTThe name of an activity
descriptionTEXTThe detail of an activity
createdAtTIMESTAMPThe time that the item was created. It will be automatically added by SQLite

The Code

1. Create a new Flutter project. In the lib folder, add a new file named sql_helper.dart.

The project structure:

.
├── main.dart
└── sql_helper.dart

Install the sqflite plugin (note that the name has an “f”):

flutter pub add sqflite

2. Full code in sql_helper.dart:

import 'package:flutter/foundation.dart';
import 'package:sqflite/sqflite.dart' as sql;

class SQLHelper {
  static Future<void> createTables(sql.Database database) async {
    await database.execute("""CREATE TABLE items(
        id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
        title TEXT,
        description TEXT,
        createdAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
      )
      """);
  }
// id: the id of a item
// title, description: name and description of your activity
// created_at: the time that the item was created. It will be automatically handled by SQLite

  static Future<sql.Database> db() async {
    return sql.openDatabase(
      'kindacode.db',
      version: 1,
      onCreate: (sql.Database database, int version) async {
        await createTables(database);
      },
    );
  }

  // Create new item (journal)
  static Future<int> createItem(String title, String? descrption) async {
    final db = await SQLHelper.db();

    final data = {'title': title, 'description': descrption};
    final id = await db.insert('items', data,
        conflictAlgorithm: sql.ConflictAlgorithm.replace);
    return id;
  }

  // Read all items (journals)
  static Future<List<Map<String, dynamic>>> getItems() async {
    final db = await SQLHelper.db();
    return db.query('items', orderBy: "id");
  }

  // Read a single item by id
  // The app doesn't use this method but I put here in case you want to see it
  static Future<List<Map<String, dynamic>>> getItem(int id) async {
    final db = await SQLHelper.db();
    return db.query('items', where: "id = ?", whereArgs: [id], limit: 1);
  }

  // Update an item by id
  static Future<int> updateItem(
      int id, String title, String? descrption) async {
    final db = await SQLHelper.db();

    final data = {
      'title': title,
      'description': descrption,
      'createdAt': DateTime.now().toString()
    };

    final result =
        await db.update('items', data, where: "id = ?", whereArgs: [id]);
    return result;
  }

  // Delete
  static Future<void> deleteItem(int id) async {
    final db = await SQLHelper.db();
    try {
      await db.delete("items", where: "id = ?", whereArgs: [id]);
    } catch (err) {
      debugPrint("Something went wrong when deleting an item: $err");
    }
  }
}

3. Full code in main.dart (with explanations):

// main.dart
import 'package:flutter/material.dart';

import 'sql_helper.dart';

void main() {
  runApp(const MyApp());
}

class MyApp extends StatelessWidget {
  const MyApp({Key? key}) : super(key: key);

  @override
  Widget build(BuildContext context) {
    return MaterialApp(
        // Remove the debug banner
        debugShowCheckedModeBanner: false,
        title: 'Kindacode.com',
        theme: ThemeData(
          primarySwatch: Colors.orange,
        ),
        home: const HomePage());
  }
}

class HomePage extends StatefulWidget {
  const HomePage({Key? key}) : super(key: key);

  @override
  _HomePageState createState() => _HomePageState();
}

class _HomePageState extends State<HomePage> {
  // All journals
  List<Map<String, dynamic>> _journals = [];

  bool _isLoading = true;
  // This function is used to fetch all data from the database
  void _refreshJournals() async {
    final data = await SQLHelper.getItems();
    setState(() {
      _journals = data;
      _isLoading = false;
    });
  }

  @override
  void initState() {
    super.initState();
    _refreshJournals(); // Loading the diary when the app starts
  }

  final TextEditingController _titleController = TextEditingController();
  final TextEditingController _descriptionController = TextEditingController();

  // This function will be triggered when the floating button is pressed
  // It will also be triggered when you want to update an item
  void _showForm(int? id) async {
    if (id != null) {
      // id == null -> create new item
      // id != null -> update an existing item
      final existingJournal =
          _journals.firstWhere((element) => element['id'] == id);
      _titleController.text = existingJournal['title'];
      _descriptionController.text = existingJournal['description'];
    }

    showModalBottomSheet(
        context: context,
        elevation: 5,
        isScrollControlled: true,
        builder: (_) => Container(
              padding: EdgeInsets.only(
                top: 15,
                left: 15,
                right: 15,
                // this will prevent the soft keyboard from covering the text fields
                bottom: MediaQuery.of(context).viewInsets.bottom + 120,
              ),
              child: Column(
                mainAxisSize: MainAxisSize.min,
                crossAxisAlignment: CrossAxisAlignment.end,
                children: [
                  TextField(
                    controller: _titleController,
                    decoration: const InputDecoration(hintText: 'Title'),
                  ),
                  const SizedBox(
                    height: 10,
                  ),
                  TextField(
                    controller: _descriptionController,
                    decoration: const InputDecoration(hintText: 'Description'),
                  ),
                  const SizedBox(
                    height: 20,
                  ),
                  ElevatedButton(
                    onPressed: () async {
                      // Save new journal
                      if (id == null) {
                        await _addItem();
                      }

                      if (id != null) {
                        await _updateItem(id);
                      }

                      // Clear the text fields
                      _titleController.text = '';
                      _descriptionController.text = '';

                      // Close the bottom sheet
                      Navigator.of(context).pop();
                    },
                    child: Text(id == null ? 'Create New' : 'Update'),
                  )
                ],
              ),
            ));
  }

// Insert a new journal to the database
  Future<void> _addItem() async {
    await SQLHelper.createItem(
        _titleController.text, _descriptionController.text);
    _refreshJournals();
  }

  // Update an existing journal
  Future<void> _updateItem(int id) async {
    await SQLHelper.updateItem(
        id, _titleController.text, _descriptionController.text);
    _refreshJournals();
  }

  // Delete an item
  void _deleteItem(int id) async {
    await SQLHelper.deleteItem(id);
    ScaffoldMessenger.of(context).showSnackBar(const SnackBar(
      content: Text('Successfully deleted a journal!'),
    ));
    _refreshJournals();
  }

  @override
  Widget build(BuildContext context) {
    return Scaffold(
      appBar: AppBar(
        title: const Text('Kindacode.com'),
      ),
      body: _isLoading
          ? const Center(
              child: CircularProgressIndicator(),
            )
          : ListView.builder(
              itemCount: _journals.length,
              itemBuilder: (context, index) => Card(
                color: Colors.orange[200],
                margin: const EdgeInsets.all(15),
                child: ListTile(
                    title: Text(_journals[index]['title']),
                    subtitle: Text(_journals[index]['description']),
                    trailing: SizedBox(
                      width: 100,
                      child: Row(
                        children: [
                          IconButton(
                            icon: const Icon(Icons.edit),
                            onPressed: () => _showForm(_journals[index]['id']),
                          ),
                          IconButton(
                            icon: const Icon(Icons.delete),
                            onPressed: () =>
                                _deleteItem(_journals[index]['id']),
                          ),
                        ],
                      ),
                    )),
              ),
            ),
      floatingActionButton: FloatingActionButton(
        child: const Icon(Icons.add),
        onPressed: () => _showForm(null),
      ),
    );
  }
}

Conclusion

You’ve learned the fundamentals of SQLite and gone through an end-to-end example of using SQLite in a Flutter app. From here, you can build more complex apps that store a lot of data offline. If you’d like to explore more about Flutter and modern mobile development, take a look at the following articles:

You can also check out our Flutter category page or Dart category page for the latest tutorials and examples.

Subscribe
Notify of
guest
25 Comments
Inline Feedbacks
View all comments
Mar
Mar
26 days ago

hi,nice tutorial.
I hsve a problem when I run it, the page is just loading and when I push the create button and want to add it to sceeen the Create New button is not working.

Montzking
Montzking
1 month ago

Thanks you guys

Asad
Asad
2 months ago

Great and very helpful. Thank you!

Sajjad Ahmad
Sajjad Ahmad
4 months ago

Excellent work with good programming approach and you have made it easy with comments.
I recommend this site to all the new commers in Flutter to come and learn hare and make their future bright.
Thanks a lot

Ibrahim
Ibrahim
5 months ago

So happy thanks

Mar
Mar
7 months ago

thank you bro you saved my ***

ASA
ASA
7 months ago

I have spent two weeks now trying to figure out how to perform CRUD with persistent data in mysqflite, this was just under 2hours, thank you very much, I am now a developer!

manoj
manoj
7 months ago

how to add more fields like title and description

Yunis
Yunis
9 months ago

it’s very helpful for me

apps maker
apps maker
10 months ago

thank you

broD
broD
11 months ago

Also we declared a variable for date but not showing.
how can I gather those events in month file. Like each month the events are gather in a table or a file.

broD
broD
11 months ago

hi, very nice tutorial.
i had a pb when a loaded it. the Create New button was not working .

anas
anas
11 months ago

thanks ^_*

Serge
Serge
11 months ago

Thank you very much! Short, understandable and works

Pável
Pável
1 year ago

Thank you very much, this was worth more than 1000 videos of tutorials and works..

Raymond
Raymond
1 year ago

Thank you. How would this work using an existing db file that was included in the flutter assets folder?

robby
robby
1 year ago

Nice … This just works !! super well done!

Related Articles