Flutter SQLite CRUD Example

Flutter SQLite CRUD Example

    Here we will learn how to use SQLite in Flutter to create, read, update and delete or CRUD data operation. With this sqflite we would be able to storage data in the mobile local storage.

    First make sure you installed the latest version of SQLite.

    flutter pub add sqflite

    If you run the above command in your editor terminal, it will install the latest version. 

    We will create a file called sql_helper.dart

    Data Table

    Inside this class, we will create a class name SQLHelper and then put a method name createTables().

    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
          )
          """);
      }
    
    }

    Here mention the data table name items and the fields in the table. We have four fields

    1. id,
    2. title,
    3. description
    4. createdAt

    database.execute does the magic of creating table in app local storage. As we create the table we also mention the data type.

    Create a DB

    Then we will create a db() method, this db() would call createTables() method.

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

    db() method would get called in every CRUD operations of SQLITE

    Inside openDatabase() you should mention your database name.

    Data Insert

    After that we create a method for insert data operation in the storage.

      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;
      }

    We pass title and description from onPressed of floatingActionButton. Before that we will create TextController.

    We need to insert data in Map format. So we save the data in a data variable.

    We used conflictAlgorithm to avoid any kind duplication data entry.

    Get All Data

    Now we will see how to get data from the storage of SQLFLITE. We will get all the data. We will use db.query() method to get data, this method is used to get lots of data or a single data from sqlite.

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

    Get Specific Data

    Here we will see how to get a specific item based on ID. Same as the above example we will use db.query(). In this case, we will send the ID to query() function and return data that matches with the ID.

      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);
      }

    Data Update

    Let's see how to udate an item. If you want to update an item you would call db.update() method(). Just like other db.query() it takes a Map of items. Items are string or object. In this case are passing strings in a form of Map.

      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()
        };

    Complete SQLHelper class

    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(
          'dbtech.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");
        }
      }
    }

    Then in the main.dart we will have

    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: 'SQLITE',
            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('SQL'),
          ),
          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),
          ),
        );
      }
    }

    Courses


    Recent posts