Flutter & SQLite: CRUD Example using sqflite package

Create a Project

flutter create -e --org com.example sqlite

Packages

SQLite:- https://pub.dev/packages/sqflite

flutter pub add sqlite

config/database.dart

class Database {
  static String database = "database.db";
  // Tables
  static String items = "items";
}

core/classes/sqlite.dart

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

import '../../config/database.dart';
import '../../models/item_model.dart';

class Sqlite {
  // ,createdAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP

  // 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(
      Database.database,
      version: 1,
      onCreate: (sql.Database database, int version) async {
        await database.execute(ItemModel().createTableQuery);
      },
    );
  }
}

models/base_sql_model.dart

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

import '../core/classes/sqlite.dart';

abstract class BaseSQLModel {
  String get table;
  String get orderBy;

  Future<List<Map<String, dynamic>>> getAll() async {
    final db = await Sqlite.db();
    return db.query(table, orderBy: orderBy);
  }

  Future<Map<String, dynamic>?> getById(int id) async {
    final db = await Sqlite.db();
    final List<Map<String, dynamic>> maps = await db.query(
      table,
      where: "id = ?",
      whereArgs: [id],
    );
    return maps.isNotEmpty ? maps.first : null;
  }

  Future<Map<String, dynamic>?> getBySlug(String slug) async {
    final db = await Sqlite.db();
    final List<Map<String, dynamic>> maps = await db.query(
      table,
      where: "slug = ?",
      whereArgs: [slug],
    );
    return maps.isNotEmpty ? maps.first : null;
  }

  static Future<Map<String, dynamic>?> getBy(
      Map<String, dynamic> criteria) async {
    final db = await Sqlite.db();
    String whereClause = criteria.keys.map((key) => '$key = ?').join(' AND ');
    List<dynamic> whereArgs = criteria.values.toList();

    final result = await db.query('your_table_name',
        where: whereClause, whereArgs: whereArgs);
    return result.isNotEmpty ? result.first : null;
  }

  Future<int> store(obj) async {
    try {
      final db = await Sqlite.db();
      final int id = await db.transaction<int>((txn) async {
        // Perform the insert operation inside the transaction
        return await txn.insert(table, obj,
            conflictAlgorithm: sql.ConflictAlgorithm.replace);
      });

      return id;
    } catch (e) {
      debugPrint("MySql Error storing data: $e");
      return -1; // Return a specific value to indicate the failure.
    }
  }

  Future<int> update(int id, dynamic obj) async {
    try {
      final db = await Sqlite.db();
      final int result = await db.transaction<int>((txn) async {
        // Perform the update operation inside the transaction
        return await txn.update(table, obj, where: "id = ?", whereArgs: [id]);
      });
      return result;
    } catch (e) {
      debugPrint("MySql Error updating data: $e");
      return -1; // Return a specific value to indicate the failure.
    }
  }

  Future<void> delete(int id) async {
    final db = await Sqlite.db();
    try {
      await db.delete(table, where: "id = ?", whereArgs: [id]);
    } catch (err) {
      debugPrint("MySql Error deleting an item: $err");
    }
  }

  Future<void> truncate() async {
    final db = await Sqlite.db();
    try {
      await db.transaction((txn) async {
        await txn.rawDelete('DELETE FROM $table');
        await txn.delete('sqlite_sequence',
            where: "name = ?",
            whereArgs: [table]); // This resets the autoincrement counter.
      });
    } catch (err) {
      debugPrint("MySql Error truncate an item: $err");
    }
  }

  Future<int> updateOrCreate(
      Map<String, dynamic> criteria, Map<String, dynamic> record) async {
    try {
      var dbRecord = await getBy(criteria);
      if (dbRecord != null) {
        return update(dbRecord['id'], record);
      } else {
        return store(record);
      }
    } catch (e) {
      debugPrint("MySql Error updateOrCreate data: $e");
      return -1; // Return a specific value to indicate the failure.
    }
  }

  // create and update function using slug as unique key
  Future updateOrCreateBySlug(String slug, dynamic record) async {
    try {
      var dbRecord = await getBySlug(slug);
      if (dbRecord != null) {
        return update(dbRecord['id'], record);
      } else {
        return store(record);
      }
    } catch (e) {
      debugPrint("MySql Error storing data: $e");
      return -1; // Return a specific value to indicate the failure.
    }
  }
}

models/item_model.dart

import '../config/database.dart';
import 'base_sql_model.dart';

class ItemModel extends BaseSQLModel {
  @override
  String get table => Database.items;

  @override
  String get orderBy => 'title';

  String get createTableQuery => """
    CREATE TABLE $table (
      id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
      title TEXT,
      description TEXT,
      createdAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
    )
  """;
}

views/sql_view.dart

import 'package:flutter/material.dart';

import '../models/item_model.dart';
import '../widgets/widgets.dart';

class SqlView extends StatefulWidget {
  const SqlView({super.key});
  @override
  State<SqlView> createState() => _SqlViewState();
}

class _SqlViewState extends State<SqlView> {
  final ItemModel _recordModel = ItemModel();

  List<Map<String, dynamic>> _records = [];
  bool _isLoading = true;
  // Form
  final TextEditingController _titleCtr = TextEditingController();
  final TextEditingController _descriptionCtr = TextEditingController();

  @override
  void initState() {
    super.initState();
    // _controller.orderBy = "title";
    _refreshRecords();
  }

  _refreshRecords() async {
    final data = await _recordModel.getAll();
    setState(() {
      _records = data;
      _isLoading = false;
    });
  }

  void _showForm(int? id) async {
    if (id != null) {
      final existingRecord =
          _records.firstWhere((element) => element['id'] == id);
      _titleCtr.text = existingRecord['title'];
      _descriptionCtr.text = existingRecord['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: [
                  MLTextField(labelText: "Title", controller: _titleCtr),
                  const SizedBox(
                    height: 10,
                  ),
                  MLTextField(
                      labelText: "Description", controller: _descriptionCtr),
                  const SizedBox(
                    height: 10,
                  ),
                  ElevatedButton(
                    onPressed: () async {
                      if (id != null) {
                        await _updateItem(id);
                      } else {
                        await _addItem();
                      }
                      // Clear the text fields
                      _titleCtr.text = '';
                      _descriptionCtr.text = '';
                      // Close the bottom sheet
                      Navigator.pop(context);
                    },
                    child: Text(id == null ? 'Create New' : 'Update'),
                  )
                ],
              ),
            ));
  }

  // Insert a new record to the database
  Future<void> _addItem() async {
    await _recordModel.store({
      'title': _titleCtr.text,
      'description': _descriptionCtr.text,
    });
    showMessage(context, "Successfully created a record!");
    _refreshRecords();
  }

  // Update an existing record
  Future<void> _updateItem(int id) async {
    await _recordModel.update(id, {
      'title': _titleCtr.text,
      'description': _descriptionCtr.text,
    });
    showMessage(context, "Successfully update a record!");
    _refreshRecords();
  }

// Delete an item
  void _deleteItem(int id) async {
    await _recordModel.delete(id);
    showMessage(context, "Successfully deleted a record!");
    _refreshRecords();
  }

  @override
  Widget build(BuildContext context) {
    return Scaffold(
      appBar: AppBar(
        title: const Text("Sql View"),
        actions: [
          IconButton(
            icon: const Icon(Icons.refresh),
            onPressed: _refreshRecords,
          )
        ],
      ),
      floatingActionButton: FloatingActionButton(
        child: const Icon(Icons.add),
        onPressed: () => _showForm(null),
      ),
      body: _isLoading
          ? fullScreenLoader()
          : (_records.isEmpty)
              ? noDataFound()
              : ListView.builder(
                  itemCount: _records.length,
                  itemBuilder: (context, index) => Card(
                    margin: const EdgeInsets.all(15),
                    child: ListTile(
                        title: Text(_records[index]['title']),
                        subtitle: Text(_records[index]['description']),
                        trailing: SizedBox(
                          width: 100,
                          child: Row(
                            children: [
                              IconButton(
                                icon: const Icon(Icons.edit),
                                onPressed: () =>
                                    _showForm(_records[index]['id']),
                              ),
                              IconButton(
                                icon: const Icon(Icons.delete),
                                onPressed: () =>
                                    _deleteItem(_records[index]['id']),
                              ),
                            ],
                          ),
                        )),
                  ),
                ),
    );
  }
}

widgets/input.dart

import 'package:flutter/material.dart';

InputDecoration inputDecoration(String labelText, IconData iconData,
    {String? prefix, String? helperText, String? hintText}) {
  return InputDecoration(
    // contentPadding: const EdgeInsets.symmetric(vertical: 12, horizontal: 12),
    helperText: helperText,
    labelText: labelText,
    // labelStyle: TextStyle(color: Colors.grey),
    // fillColor: Colors.grey.shade200,
    // filled: true,
    prefixText: prefix,
    // prefixIcon: Icon(
    //   iconData,
    //   size: 20,
    // ),
    // prefixIconConstraints: const BoxConstraints(minWidth: 60),
    // enabledBorder: OutlineInputBorder(
    //     borderRadius: BorderRadius.circular(30),
    //     borderSide: const BorderSide(color: Colors.black)),
    // focusedBorder: OutlineInputBorder(
    //     borderRadius: BorderRadius.circular(30),
    //     borderSide: const BorderSide(color: Colors.black)),
    // errorBorder: OutlineInputBorder(
    //     borderRadius: BorderRadius.circular(30),
    //     borderSide: const BorderSide(color: Colors.black)),
    // border: OutlineInputBorder(
    //     borderRadius: BorderRadius.circular(30),
    //     borderSide: const BorderSide(color: Colors.black)),
  );
}

class MLTextField extends StatelessWidget {
  String labelText;
  String? hintText;
  TextEditingController controller = TextEditingController();
  MLTextField(
      {required this.labelText,
      this.hintText,
      required this.controller,
      super.key});

  @override
  Widget build(BuildContext context) {
    return TextFormField(
      // validator: (value) {
      //   return customTextValidator(value);
      // },
      controller: controller,
      // decoration: inputDecoration(labelText, Icons.abc),
      decoration: InputDecoration(hintText: labelText),
    );
  }
}

widgets/widgets.dart

import 'package:flutter/material.dart';

// Fields
export 'input.dart';

showMessage(BuildContext context, String massage) {
  return ScaffoldMessenger.of(context).showSnackBar(
    SnackBar(
      content: Text(massage),
    ),
  );
}

Widget onScreenLoader() {
  return const Padding(
    padding: EdgeInsets.only(top: 10, bottom: 40),
    child: Center(
      child: CircularProgressIndicator(),
    ),
  );
}

Widget noMoreContent() {
  return Container(
    padding: const EdgeInsets.only(top: 15, bottom: 15),
    // color: Colors.amber,
    child: const Center(
      child: Text('You have fetched all of the content'),
    ),
  );
}

Widget noDataFound() {
  return const Center(
    child: Text('No Data Found'),
  );
}

Widget fullScreenLoader() {
  return const Scaffold(
    body: Center(
      child: CircularProgressIndicator(),
    ),
  );
}

main.dart

import 'package:flutter/material.dart';

import 'views/sql_view.dart';

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

class MainApp extends StatelessWidget {
  const MainApp({super.key});

  @override
  Widget build(BuildContext context) {
    return const MaterialApp(
      home: SqlView(),
    );
  }
}

Learning Flutter more Example

GitHub - RajTechnologiesPvtLtd/flutter-moonlight: MoonLight - Flutter Starter Kit

Sponsor @bhargavraviya on GitHub Sponsors

Get more from Raviya Technical on Patreon