CRUD operation using Flutter and SQLite in Flutter

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 and SQLite.
A computer with Flutter with Android Studio installed or VS Code.

Why SQLite and Sqflite?

SQLite is an open source relational database, it is used to create a database, perform different operation like add, delete, and remove data and one of the most popular ways to store data locally. SQLite does not require a server or backend code. All the data is saved to a text file in the device. For this article, we will be using the package Sqflite to connect with SQLite. Sqflite is packages for connecting to SQLite databases in Flutter.

sqflite

Sqflite is a Flutter plugin for SQLite, a self-contained, high-reliability, embedded, SQL database engine. SQLite plugin for Flutter. Supports iOS, Android and MacOS. While it’s not a first-party plugin, it’s recommended in the official Flutter cookbook.

Support transactions and batches.
Automatic version management during open.
Helpers for insert/query/update/delete queries.
DB operation executed in a background thread on iOS and Android.

path_provider

A Flutter plugin for finding commonly used locations on the filesystem. Supports Android, iOS, Linux, macOS and Windows. Not all methods are supported on all platforms.

path vs path_provider

path is a package to manipulate paths: join them, convert to absolute, add prefixes, get path info and so on.

path_provider is dedicated to more specific OS operations, e.g. downloads, temporary files, application documents are stored in different places based on the OS (obviously, file structure in Android is different than in iOS, Windows, Linux, etc.).

To compare these two, packages have different purpose - path to actually "work" with paths while path_provider, well, provide you with the exact path to resources based on OS. Hence, you cannot replace one with the other, if you need to use both, that's it, just use both of them.

Project Dependencies

In your project go to pubspec.yaml and look for dependencies. Under dependencies, add the latest version of sqflite and path_provider. You can find the latest version of the dependencies in https://pub.dev/. While writing this article the dependencies and its version below.

sqflite: ^2.0.2
path_provider: ^2.0.9

after adding the dependencies then click pub get link to update the dependencies in our flutter project its returns Process finished with exit code 0.

Database Structure

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

Column	Type	Description
Id	INTEGER	id is Primary Key
Name	TEXT	name is for User Name
Contact	TEXT	contact is for user Contact Number
Description	TEXT	description for some sample description

Project Structure

We should create the directories and file as per the below project structure.

	lib

		db_helper
			database_connection.dart
			repository.dart
		model
			User.dart
		screens
			AddUser.dart
			EditUser.dart
			ViewUsers.dart
		services
			UserService.dart
	main.dart

Creating the Model Class

Before creating the CRUD operations, we need to add a model. We can use this class type to transfer data between UI (main.dart) and other Database Class then we have to create a class called User which will contain different fields related to a notes source code below

User.dart

class User{
	int? id;
	String? name;
	String? contact;
	String? description;
	userMap() {
		var mapping = Map<String, dynamic>();
		mapping['id'] = id ?? null;
		mapping['name'] = name!;
		mapping['contact'] = contact!;
		mapping['description'] = description!;
		return mapping;
	  }
}

In this User Class we are going to use four fields as per the table design and userMap() method is use to convert field to map for data operation.

SQLite Database & Connection

Now, under the lib folder, create another folder called db_helper and inside of it create a class called Database Connection. This class will take care to create SQLite database db_crud, table users and setDatabase() method is to return database connection.

database_connection.dart

import 'package:sqflite/sqflite.dart';
import 'package:path/path.dart';
import 'package:path_provider/path_provider.dart';
class DatabaseConnection{
	Future<Database>setDatabase() async {
		var directory = await getApplicationDocumentsDirectory();
		var path = join(directory.path, 'db_crud');
		var database =
		await openDatabase(path, version: 1, onCreate: _createDatabase);
		return database;
	  }
	Future<void>_createDatabase(Database database, int version) async {
		String sql=
		"CREATE TABLE users (id INTEGER PRIMARY KEY,nameTEXT,contactText,description TEXT);";
		await database.execute(sql);
	  }
}

While _createDatabase(database,version) method is use to create users table in db_crud.

Saving Data in SQLite

Create a new file repository.dart in db_helper Now inside the class Repository , we can create a method insertData to insert data into the database.

for example:

insertData(table, data) async {
	var connection = await database;
	return await connection?.insert(table, data);
}

Here insertData() will take a list of details, then we loop inside the collection and insert each details to the table notes. The insertData() method takes the following parameters String table, Map<String, Object?> values, and that’s why we create a userMap() method in the model class.

Retrieve Data from SQLite

Then to retrieve data, we can create another method in the Repository class, for example:

The readData() method to get all the records in the table.The readData() method will take one parameter String table which is table name.

readData(table) async {
	var connection = await database;
	return await connection?.query(table);
}

While readDataByID() method is to get a particular record by ID.The readDataByID() method will take two parameters String table,int id which is table name and primary key of a record to read.

readDataById(table, itemId) async {
	var connection = await database;
	return await connection?.query(table, where: 'id=?', whereArgs: [itemId]);
}

So here we use the query () method and give it the string users which is the table name. So, this will select all columns from the table users.

Update Data from SQLite

Then we can update data we can create another method in the Repository class, for example The updateData() method is use to update record in the table.

updateData(table, data) async {
	var connection = await database;
	return await connection
	?.update(table, data, where: 'id=?', whereArgs: [data['id']]);
}

The updateData() method will take two parameters String table, int id which is table name and primary key of a record to update.

Delete Data from SQLite

Then we can delete data we can create another method in the Repository class, for example The deleteData() method is use to delete a record in the table.

deleteDataById(table, itemId) async {
	var connection = await database;
	return await connection?.rawDelete("delete from $table where id=$itemId");
}

The deleteData() method will take two parameters String table, int id which is table name and primary key of a record to delete.

repository.dart

import 'package:crud_sqflite_demo/db_helper/database_connection.dart';
import 'package:sqflite/sqflite.dart';

	class Repository {
		late DatabaseConnection_databaseConnection;
		Repository() {
			_databaseConnection= DatabaseConnection();
		  }
		static Database? _database;
		Future<Database?>get database async {
			if (_database != null) {
				return _database;
			} else {
				_database = await _databaseConnection.setDatabase();
				return _database;
			}
		}
		insertData(table, data) async {
			var connection = await database;
			return await connection?.insert(table, data);
		}
		readData(table) async {
			var connection = await database;
			return await connection?.query(table);
		}
		readDataById(table, itemId) async {
			var connection = await database;
			return await connection?.query(table, where: 'id=?', whereArgs: [itemId]);
		}
		updateData(table, data) async {
			var connection = await database;
			return await connection
			?.update(table, data, where: 'id=?', whereArgs: [data['id']]);
		}
		deleteDataById(table, itemId) async {
			var connection = await database;
			return await connection?.rawDelete("delete from $table where id=$itemId");
		}
	}

AddUser.dart

import 'package:crud_sqlite_joes/model/User.dart';
import 'package:crud_sqlite_joes/services/userService.dart';
import 'package:flutter/material.dart';

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

  @override
  State<AddUser> createState() => _AddUserState();
}

class _AddUserState extends State<AddUser> {
  var _userNameController = TextEditingController();
  var _userContactController = TextEditingController();
  var _userDescriptionController = TextEditingController();
  bool _validateName = false;
  bool _validateContact = false;
  bool _validateDescription = false;
  var _userService=UserService();
  @override
  Widget build(BuildContext context) {
    return Scaffold(
      appBar: AppBar(
        title: const Text("SQLite CRUD"),
      ),
      body: SingleChildScrollView(
        child: Container(
          padding: const EdgeInsets.all(16.0),
          child: Column(
            crossAxisAlignment: CrossAxisAlignment.start,
            children: [
              const Text(
                'Add New User',
                style: TextStyle(
                    fontSize: 20,
                    color: Colors.teal,
                    fontWeight: FontWeight.w500),
              ),
              const SizedBox(
                height: 20.0,
              ),
              TextField(
                  controller: _userNameController,
                  decoration: InputDecoration(
                    border: const OutlineInputBorder(),
                    hintText: 'Enter Name',
                    labelText: 'Name',
                    errorText:
                        _validateName ? 'Name Value Can\'t Be Empty' : null,
                  )),
              const SizedBox(
                height: 20.0,
              ),
              TextField(
                  controller: _userContactController,
                  decoration: InputDecoration(
                    border: const OutlineInputBorder(),
                    hintText: 'Enter Contact',
                    labelText: 'Contact',
                    errorText: _validateContact
                        ? 'Contact Value Can\'t Be Empty'
                        : null,
                  )),
              const SizedBox(
                height: 20.0,
              ),
              TextField(
                  controller: _userDescriptionController,
                  decoration: InputDecoration(
                    border: const OutlineInputBorder(),
                    hintText: 'Enter Description',
                    labelText: 'Description',
                    errorText: _validateDescription
                        ? 'Description Value Can\'t Be Empty'
                        : null,
                  )),
              const SizedBox(
                height: 20.0,
              ),
              Row(
                children: [
                  TextButton(
                      style: TextButton.styleFrom(
                          primary: Colors.white,
                          backgroundColor: Colors.teal,
                          textStyle: const TextStyle(fontSize: 15)),
                      onPressed: () async {
                        setState(() {
                          _userNameController.text.isEmpty
                              ? _validateName = true
                              : _validateName = false;
                          _userContactController.text.isEmpty
                              ? _validateContact = true
                              : _validateContact = false;
                          _userDescriptionController.text.isEmpty
                              ? _validateDescription = true
                              : _validateDescription = false;

                        });
                        if (_validateName == false &&
                            _validateContact == false &&
                            _validateDescription == false) {
                         // print("Good Data Can Save");
                          var _user = User();
                          _user.name = _userNameController.text;
                          _user.contact = _userContactController.text;
                          _user.description = _userDescriptionController.text;
                          var result=await _userService.SaveUser(_user);
                         Navigator.pop(context,result);
                        }
                      },
                      child: const Text('Save Details')),
                  const SizedBox(
                    width: 10.0,
                  ),
                  TextButton(
                      style: TextButton.styleFrom(
                          primary: Colors.white,
                          backgroundColor: Colors.red,
                          textStyle: const TextStyle(fontSize: 15)),
                      onPressed: () {
                        _userNameController.text = '';
                        _userContactController.text = '';
                        _userDescriptionController.text = '';
                      },
                      child: const Text('Clear Details'))
                ],
              )
            ],
          ),
        ),
      ),
    );
  }
}

EditUser.dart

import 'package:crud_sqlite_joes/model/User.dart';
import 'package:crud_sqlite_joes/services/userService.dart';
import 'package:flutter/material.dart';
class EditUser extends StatefulWidget {
  final User user;
  const EditUser({Key? key,required this.user}) : super(key: key);

  @override
  State<EditUser> createState() => _EditUserState();
}

class _EditUserState extends State<EditUser> {
  var _userNameController = TextEditingController();
  var _userContactController = TextEditingController();
  var _userDescriptionController = TextEditingController();
  bool _validateName = false;
  bool _validateContact = false;
  bool _validateDescription = false;
  var _userService=UserService();

  @override
  void initState() {
    setState(() {
      _userNameController.text=widget.user.name??'';
      _userContactController.text=widget.user.contact??'';
      _userDescriptionController.text=widget.user.description??'';
    });
    super.initState();
  }
  @override
  Widget build(BuildContext context) {
    return Scaffold(
      appBar: AppBar(
        title: const Text("SQLite CRUD"),
      ),
      body: SingleChildScrollView(
        child: Container(
          padding: const EdgeInsets.all(16.0),
          child: Column(
            crossAxisAlignment: CrossAxisAlignment.start,
            children: [
              const Text(
                'Edit New User',
                style: TextStyle(
                    fontSize: 20,
                    color: Colors.teal,
                    fontWeight: FontWeight.w500),
              ),
              const SizedBox(
                height: 20.0,
              ),
              TextField(
                  controller: _userNameController,
                  decoration: InputDecoration(
                    border: const OutlineInputBorder(),
                    hintText: 'Enter Name',
                    labelText: 'Name',
                    errorText:
                    _validateName ? 'Name Value Can\'t Be Empty' : null,
                  )),
              const SizedBox(
                height: 20.0,
              ),
              TextField(
                  controller: _userContactController,
                  decoration: InputDecoration(
                    border: const OutlineInputBorder(),
                    hintText: 'Enter Contact',
                    labelText: 'Contact',
                    errorText: _validateContact
                        ? 'Contact Value Can\'t Be Empty'
                        : null,
                  )),
              const SizedBox(
                height: 20.0,
              ),
              TextField(
                  controller: _userDescriptionController,
                  decoration: InputDecoration(
                    border: const OutlineInputBorder(),
                    hintText: 'Enter Description',
                    labelText: 'Description',
                    errorText: _validateDescription
                        ? 'Description Value Can\'t Be Empty'
                        : null,
                  )),
              const SizedBox(
                height: 20.0,
              ),
              Row(
                children: [
                  TextButton(
                      style: TextButton.styleFrom(
                          primary: Colors.white,
                          backgroundColor: Colors.teal,
                          textStyle: const TextStyle(fontSize: 15)),
                      onPressed: () async {
                        setState(() {
                          _userNameController.text.isEmpty
                              ? _validateName = true
                              : _validateName = false;
                          _userContactController.text.isEmpty
                              ? _validateContact = true
                              : _validateContact = false;
                          _userDescriptionController.text.isEmpty
                              ? _validateDescription = true
                              : _validateDescription = false;

                        });
                        if (_validateName == false &&
                            _validateContact == false &&
                            _validateDescription == false) {
                          // print("Good Data Can Save");
                          var _user = User();
                          _user.id=widget.user.id;
                          _user.name = _userNameController.text;
                          _user.contact = _userContactController.text;
                          _user.description = _userDescriptionController.text;
                          var result=await _userService.UpdateUser(_user);
                          Navigator.pop(context,result);
                        }
                      },
                      child: const Text('Update Details')),
                  const SizedBox(
                    width: 10.0,
                  ),
                  TextButton(
                      style: TextButton.styleFrom(
                          primary: Colors.white,
                          backgroundColor: Colors.red,
                          textStyle: const TextStyle(fontSize: 15)),
                      onPressed: () {
                        _userNameController.text = '';
                        _userContactController.text = '';
                        _userDescriptionController.text = '';
                      },
                      child: const Text('Clear Details'))
                ],
              )
            ],
          ),
        ),
      ),
    );
  }
}

ViewUsers.dart

import 'package:crud_sqlite_joes/model/User.dart';
import 'package:flutter/material.dart';

class ViewUser extends StatefulWidget {
  final User user;

  const ViewUser({Key? key, required this.user}) : super(key: key);

  @override
  State<ViewUser> createState() => _ViewUserState();
}

class _ViewUserState extends State<ViewUser> {
  @override
  Widget build(BuildContext context) {
    return Scaffold(
        appBar: AppBar(
          title: const Text("SQLite CRUD"),
        ),
        body: Container(
          padding: EdgeInsets.all(16.0),
          child: Column(
            crossAxisAlignment: CrossAxisAlignment.start,
            children: [
              const Text(
                "Full Details",
                style: TextStyle(
                    fontWeight: FontWeight.w600,
                    color: Colors.blueGrey,
                    fontSize: 20),
              ),
              const SizedBox(
                height: 20,
              ),
              Row(
                children: [
                  const Text('Name',
                      style: TextStyle(
                          color: Colors.teal,
                          fontSize: 16,
                          fontWeight: FontWeight.w600)),
                  Padding(
                    padding: const EdgeInsets.only(left: 30),
                    child: Text(widget.user.name ?? '', style: TextStyle(fontSize: 16)),
                  ),
                ],
              ),
              const SizedBox(
                height: 20,
              ),
              Row(
                children: [
                  const Text('Contact',
                      style: TextStyle(
                          color: Colors.teal,
                          fontSize: 16,
                          fontWeight: FontWeight.w600)),
                  Padding(
                    padding: const EdgeInsets.only(left: 25),
                    child: Text(widget.user.contact ?? '', style: TextStyle(fontSize: 16)),
                  ),
                ],
              ),
              const SizedBox(
                height: 20,
              ),
              Column(
                crossAxisAlignment: CrossAxisAlignment.start,
                children: [
                  const Text('Description',
                      style: TextStyle(
                          color: Colors.teal,
                          fontSize: 16,
                          fontWeight: FontWeight.w600)),
                  const SizedBox(
                    height: 20,
                  ),
                  Text(widget.user.description ?? '', style: const TextStyle(fontSize: 16)),
                ],
              )
            ],
          ),
        ));
  }
}

UserService.dart

import 'dart:async';

import 'package:crud_sqlite_joes/db_helper/repository.dart';
import 'package:crud_sqlite_joes/model/User.dart';

class UserService
{
  late Repository _repository;
  UserService(){
    _repository = Repository();
  }
  //Save User
  SaveUser(User user) async{
    return await _repository.insertData('users', user.userMap());
  }
  //Read All Users
  readAllUsers() async{
    return await _repository.readData('users');
  }
  //Edit User
  UpdateUser(User user) async{
    return await _repository.updateData('users', user.userMap());
  }

  deleteUser(userId) async {
    return await _repository.deleteDataById('users', userId);
  }

}

main.dart

import 'package:crud_sqlite_joes/model/User.dart';
import 'package:crud_sqlite_joes/screens/EditUser.dart';
import 'package:crud_sqlite_joes/screens/addUser.dart';
import 'package:crud_sqlite_joes/screens/viewUser.dart';
import 'package:crud_sqlite_joes/services/userService.dart';
import 'package:flutter/material.dart';

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

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

  @override
  Widget build(BuildContext context) {
    return MaterialApp(
      title: 'Flutter Demo',
      debugShowCheckedModeBanner: false,
      theme: ThemeData(
        primarySwatch: Colors.teal,
      ),
      home: const MyHomePage(),
    );
  }
}

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

  @override
  State<MyHomePage> createState() => _MyHomePageState();
}

class _MyHomePageState extends State<MyHomePage> {
  late List<User> _userList = <User>[];
  final _userService = UserService();

  getAllUserDetails() async {
    var users = await _userService.readAllUsers();
    _userList = <User>[];
    users.forEach((user) {
      setState(() {
        var userModel = User();
        userModel.id = user['id'];
        userModel.name = user['name'];
        userModel.contact = user['contact'];
        userModel.description = user['description'];
        _userList.add(userModel);
      });
    });
  }

  @override
  void initState() {
    getAllUserDetails();
    super.initState();
  }

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

  _deleteFormDialog(BuildContext context, userId) {
    return showDialog(
        context: context,
        builder: (param) {
          return AlertDialog(
            title: const Text(
              'Are You Sure to Delete',
              style: TextStyle(color: Colors.teal, fontSize: 20),
            ),
            actions: [
              TextButton(
                  style: TextButton.styleFrom(
                      primary: Colors.white, // foreground
                      backgroundColor: Colors.red),
                  onPressed: ()  async{
                     var result=await _userService.deleteUser(userId);
                     if (result != null) {
                       Navigator.pop(context);
                       getAllUserDetails();
                       _showSuccessSnackBar(
                           'User Detail Deleted Success');
                     }
                  },
                  child: const Text('Delete')),
              TextButton(
                  style: TextButton.styleFrom(
                      primary: Colors.white, // foreground
                      backgroundColor: Colors.teal),
                  onPressed: () {
                    Navigator.pop(context);
                  },
                  child: const Text('Close'))
            ],
          );
        });
  }

  @override
  Widget build(BuildContext context) {
    return Scaffold(
      appBar: AppBar(
        title: const Text("SQLite CRUD"),
      ),
      body: ListView.builder(
          itemCount: _userList.length,
          itemBuilder: (context, index) {
            return Card(
              child: ListTile(
                onTap: () {
                  Navigator.push(
                      context,
                      MaterialPageRoute(
                          builder: (context) => ViewUser(
                                user: _userList[index],
                              )));
                },
                leading: const Icon(Icons.person),
                title: Text(_userList[index].name ?? ''),
                subtitle: Text(_userList[index].contact ?? ''),
                trailing: Row(
                  mainAxisSize: MainAxisSize.min,
                  children: [
                    IconButton(
                        onPressed: () {
                          Navigator.push(
                              context,
                              MaterialPageRoute(
                                  builder: (context) => EditUser(
                                        user: _userList[index],
                                      ))).then((data) {
                            if (data != null) {
                              getAllUserDetails();
                              _showSuccessSnackBar(
                                  'User Detail Updated Success');
                            }
                          });
                          ;
                        },
                        icon: const Icon(
                          Icons.edit,
                          color: Colors.teal,
                        )),
                    IconButton(
                        onPressed: () {
                          _deleteFormDialog(context, _userList[index].id);
                        },
                        icon: const Icon(
                          Icons.delete,
                          color: Colors.red,
                        ))
                  ],
                ),
              ),
            );
          }),
      floatingActionButton: FloatingActionButton(
        onPressed: () {
          Navigator.push(context,
                  MaterialPageRoute(builder: (context) => const AddUser()))
              .then((data) {
            if (data != null) {
              getAllUserDetails();
              _showSuccessSnackBar('User Detail Added Success');
            }
          });
        },
        child: const Icon(Icons.add),
      ),
    );
  }
}

Output

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.

To download raw file Click Here