What is SQFlite

SQFlite is a popular package in the Flutter framework used for local database storage. Flutter is a UI toolkit developed by Google for building natively compiled applications for mobile, web, and desktop from a single codebase. SQFlite specifically focuses on providing a local database solution for Flutter apps, allowing developers to store and retrieve data on the device itself.

SQFlite is built on top of SQLite, which is a widely used embedded relational database engine. SQLite provides a lightweight, serverless, and self-contained database system that can be integrated directly into applications. SQFlite simplifies the usage of SQLite within Flutter apps, abstracting away the complexities and providing a more Flutter-friendly API.

Key features and concepts of SQFlite include:

1. Local Storage: SQFlite enables you to create and manage a local database within your Flutter app. This is particularly useful for storing data that needs to be available even when the app is offline.

2. Tables and Records: Like traditional databases, SQFlite supports creating tables to organize and structure your data. You can insert, update, delete, and query records within these tables.

3. Queries: SQFlite allows you to perform various types of queries on your database, including selecting specific data, filtering, sorting, and joining tables.

4. Asynchronous Operations: Since database operations can be time-consuming, SQFlite provides asynchronous methods to interact with the database without blocking the main UI thread of your app.

5. Transactions: SQFlite supports transactions, which ensure data consistency and integrity during complex database operations.

6. ORM Integration: While not a built-in feature of SQFlite, many developers use Object-Relational Mapping (ORM) libraries like Moor or floor to work with SQFlite more intuitively by representing database tables as Dart classes.

7. Cross-Platform: SQFlite works across different platforms supported by Flutter, including iOS, Android, and desktop (Windows, macOS, Linux).

Working with SQFlite on Windows

Step -1 Adding Package

The commandflutter pub add sqflite_common_ffi is used to add thesqflite_common_ffi package to your Flutter project. This package is an alternative implementation of the SQFlite package that uses FFI (Foreign Function Interface) to communicate with the native SQLite library.

Usingsqflite_common_ffi can be beneficial in situations where you need better performance or compatibility, as it aims to offer improved performance by using native code interactions.

flutterpubaddsqflite_common_ffi

Step - 2 Downloading SQLite DLL File

Remember to Download Precompiled Binaries for Windows

SQLite DLL LINK

Then add the DLL file into the window path inside your app

windows

Let's Start with Our App

Defining User Data Model

Let's Define Out data model that we want to add inside the SQLite using SQFlite
It can be anything Like if you are building todo app or user login app or anything in my case I am working with Users

import'package:flutter/foundation.dart'showimmutable;@immutableclassUser{finalintid;finalStringname;finalStringemail;finalintpassword;finalintphoneNumber;constUser({requiredthis.id,requiredthis.name,requiredthis.email,requiredthis.password,requiredthis.phoneNumber,});Map<String,dynamic>toMap(){return{"id":id,"name":name,"email":email,"password":password,"phoneNumber":phoneNumber,};}}

Database Initialization - Specifically for Windows

Using asynchronous programming to initialize and retrieve a database instance.

In this code, you have an asynchronous getter named database which returns aFuture<Database>. This getter is designed to return an existing database instance if it's already initialized, or initialize it using the initWinDB() function if it's not yet initialized

Database?_database;Future<Database>getdatabaseasync{if(_database!=null){return_database!;}_database=awaitinitWinDB();return_database!;}

This function prepares and sets up an in-memory SQLite database using FFI. It ensures that the FFI integration is initialized, sets up the database factory, and then creates a database in memory. If this is the first time the app is running, it will call a function to set up the initial structure of the database. The version number is important for possible future changes to the database.

Future<Database>initWinDB()async{sqfliteFfiInit();finaldatabaseFactory=databaseFactoryFfi;returnawaitdatabaseFactory.openDatabase(inMemoryDatabasePath,options:OpenDatabaseOptions(onCreate:_onCreate,version:1,),);}

Platform Specific

The above function is generally for the Windows but if Your applicaton is multiplatform than you have to make write platform specific code just as written below.

Future<Database>initDB()async{if(Platform.isWindows||Platform.isLinux){sqfliteFfiInit();finaldatabaseFactory=databaseFactoryFfi;finalappDocumentsDir=awaitgetApplicationDocumentsDirectory();finaldbPath=join(appDocumentsDir.path,"databases","data.db");finalwinLinuxDB=awaitdatabaseFactory.openDatabase(dbPath,options:OpenDatabaseOptions(version:1,onCreate:_onCreate,),);returnwinLinuxDB;}elseif(Platform.isAndroid||Platform.isIOS){finaldocumentsDirectory=awaitgetApplicationDocumentsDirectory();finalpath=join(documentsDirectory.path,"data.db");finaliOSAndroidDB=awaitopenDatabase(path,version:1,onCreate:_onCreate,);returniOSAndroidDB;}throwException("Unsupported platform");}

1. Future<void> _onCreate(Database database, int version) async: This function takes two arguments: thedatabase instance and theversion of the database. It's marked as asynchronous (async) because executing SQL commands may take some time.

2. final db = database;: This line creates a final reference nameddb that points to the provideddatabase instance. This reference will be used to execute SQL commands.

3. await db.execute(""" ... """);: This line is using theexecute method on thedb reference to run a SQL command. The triple quotes (""") allow you to write a multi-line string for the SQL query.

4. CREATE TABLE IF NOT EXISTS users(...) is the SQL command to create a table namedusers if it doesn't already exist. The table has columns:

   • id: An integer primary key.
   • name: A text field for the user's name.
   • email: A text field for the user's email.
   • password: An integer field for the password (Note: Storing passwords as plain integers is not secure; you should use appropriate encryption techniques).
   • phoneNumber: An integer field for the phone number.

5. TheIF NOT EXISTS ensures that the table is only created if it doesn't exist already.

In simpler terms: this function is called when the database is being created for the first time (as indicated by theversion). It sets up a table namedusers with specific columns for user information. If theusers table doesn't exist, it will be created. If it already exists, this command won't have any effect.

Remember, this function only sets up the initial structure of the database. Actual data insertion, updates, and queries will be handled elsewhere in your code.

As You can see all the fields are similar to our data model e.gid,name,email etc

Future<void>_onCreate(Databasedatabase,intversion)async{finaldb=database;awaitdb.execute(""" CREATE TABLE IF NOT EXISTS users(            id INTEGER PRIMARY KEY,            name TEXT,            email TEXT,            password INTEGER,            phoneNumber INTEGER          ) """);}

Writing Quires

At this stage everything is similar to like when we work withAndroid | iOS

Single Addition of data

Future<User>insertUSer(Useruser)async{finaldb=awaitdatabase;db.insert("users",user.toMap(),conflictAlgorithm:ConflictAlgorithm.replace,);returnuser;}

Batch Insertion

I wanted to add 1000 User at one for my app to work so I wrote this to add data at once by default
This function generates 1000 random users, adds them to a batch for insertion, and then commits the batch to the database. It returns the list of generated users. It's like preparing a tray of cookies (users) and baking them all at once (batch insertion) in the oven (database).

Future<List<User>>batchInsert()async{finaldb=awaitdatabase;finalbatch=db.batch();finalRandomrandom=Random();finalList<User>userList=List.generate(1000,(index)=>User(id:index+1,name:'User$index',email:'user$index@example.com',password:random.nextInt(9999),phoneNumber:random.nextInt(10000),),);for(finalUseruserinuserList){batch.insert('users',user.toMap(),conflictAlgorithm:ConflictAlgorithm.replace,);}awaitbatch.commit();returnuserList;}

Getting Data

This function fetches all the stored users from the database, converts the database rows into a list of User objects, and returns that list. It's like gathering all your friends' contact information from a phoneBook (database) and making a list (list of User objects) with their names and numbers.

Future<List<User>>getAllUsers()async{finaldb=awaitdatabase;finalList<Map<String,dynamic>>maps=awaitdb.query('users');returnList.generate(maps.length,(index){returnUser(id:maps[index]['id'],name:maps[index]['name'],email:maps[index]['email'],password:maps[index]['password'],phoneNumber:maps[index]['phoneNumber'],);});}

Getting Single Data by ID

This function looks up a user's information based on their ID in the database. If a user with the given ID is found, it constructs and returns a User object. If no user is found, it returns null. It's like searching for a specific person's details in a phoneBook (database) using their ID and then giving you their contact information (a User object).

Future<User?>getUserById(intuserId)async{finaldb=awaitdatabase;finalList<Map<String,dynamic>>maps=awaitdb.query('users',where:'id = ?',whereArgs:[userId],);if(maps.isNotEmpty){returnUser(id:maps[0]['id'],name:maps[0]['name'],email:maps[0]['email'],password:maps[0]['password'],phoneNumber:maps[0]['phoneNumber'],);}returnnull;}

Delete All Data

This function removes all users from the database using a single batch operation. It's like emptying a bag of marbles (users) into a box (database table) and then shaking it to make all the marbles disappear (deleting all users).

Future<void>deleteAllUsers()async{finaldb=awaitdatabase;finalBatchbatch=db.batch();batch.delete('users');awaitbatch.commit();}

Full Code for Database

import'dart:math';import'package:sqflite_common_ffi/sqflite_ffi.dart';classSQLiteHelper{Database?_database;Future<Database>getdatabaseasync{if(_database!=null){return_database!;}_database=awaitinitWinDB();return_database!;}Future<Database>initWinDB()async{sqfliteFfiInit();finaldatabaseFactory=databaseFactoryFfi;returnawaitdatabaseFactory.openDatabase(inMemoryDatabasePath,options:OpenDatabaseOptions(onCreate:_onCreate,version:1,),);}Future<void>_onCreate(Databasedatabase,intversion)async{finaldb=database;awaitdb.execute(""" CREATE TABLE IF NOT EXISTS users(            id INTEGER PRIMARY KEY,            name TEXT,            email TEXT,            password INTEGER,            phoneNumber INTEGER          ) """);}Future<User>insertUSer(Useruser)async{finaldb=awaitdatabase;db.insert("users",user.toMap(),conflictAlgorithm:ConflictAlgorithm.replace,);returnuser;}Future<List<User>>batchInsert()async{finaldb=awaitdatabase;finalbatch=db.batch();finalRandomrandom=Random();finalList<User>userList=List.generate(1000,(index)=>User(id:index+1,name:'User$index',email:'user$index@example.com',password:random.nextInt(9999),phoneNumber:random.nextInt(10000),),);for(finalUseruserinuserList){batch.insert('users',user.toMap(),conflictAlgorithm:ConflictAlgorithm.replace,);}awaitbatch.commit();returnuserList;}Future<List<User>>getAllUsers()async{finaldb=awaitdatabase;finalList<Map<String,dynamic>>maps=awaitdb.query('users');returnList.generate(maps.length,(index){returnUser(id:maps[index]['id'],name:maps[index]['name'],email:maps[index]['email'],password:maps[index]['password'],phoneNumber:maps[index]['phoneNumber'],);});}Future<User?>getUserById(intuserId)async{finaldb=awaitdatabase;finalList<Map<String,dynamic>>maps=awaitdb.query('users',where:'id = ?',whereArgs:[userId],);if(maps.isNotEmpty){returnUser(id:maps[0]['id'],name:maps[0]['name'],email:maps[0]['email'],password:maps[0]['password'],phoneNumber:maps[0]['phoneNumber'],);}returnnull;}Future<void>deleteAllUsers()async{finaldb=awaitdatabase;finalBatchbatch=db.batch();batch.delete('users');awaitbatch.commit();}}

Declaimer

You can also wrote other functionality like deleting by Id or Search filed to find data and much more which all these Queries are similar toAndroid | iOS so Do You own practice to learn more

Flutter App - Loading Data to Screen

voidmain(){runApp(constApp());}classAppextendsStatelessWidget{constApp({super.key});@overrideWidgetbuild(BuildContextcontext){returnconstMaterialApp(debugShowCheckedModeBanner:false,home:Home(),);}}

import'package:flutter/material.dart';classHomeextendsStatefulWidget{constHome({super.key});@overrideState<Home>createState()=>_HomeState();}class_HomeStateextendsState<Home>{finalSQLiteHelperhelper=SQLiteHelper();@overridevoidinitState(){super.initState();WidgetsFlutterBinding.ensureInitialized();helper.initWinDB();}@overrideWidgetbuild(BuildContextcontext){returnScaffold(appBar:AppBar(leading:TextButton(onPressed:()async{awaithelper.batchInsert();setState((){});},child:constText("ADD"),),actions:[TextButton(onPressed:()async{awaithelper.deleteAllUsers();setState((){});},child:constText("DEL"),),]),body:FutureBuilder<List<User>>(future:helper.getAllUsers(),builder:(context,snapshot){if(snapshot.connectionState==ConnectionState.waiting){returnconstCenter(child:CircularProgressIndicator());}elseif(snapshot.hasError){returnCenter(child:Text('Error:${snapshot.error}'));}elseif(!snapshot.hasData||snapshot.data!.isEmpty){returnconstCenter(child:Text('No users found.'));}else{finalusers=snapshot.data!;returnListView.builder(itemCount:users.length,itemBuilder:(context,index){finaluser=users[index];return_card(user,context);},);}},),);}}Widget_card(Useruser,BuildContextcontext){returnPadding(padding:constEdgeInsets.all(8.0),child:Card(child:Padding(padding:constEdgeInsets.all(20),child:Column(mainAxisAlignment:MainAxisAlignment.start,crossAxisAlignment:CrossAxisAlignment.start,children:[Text("ID:${user.id}",style:constTextStyle(fontSize:20,fontWeight:FontWeight.bold,),),Text("Name:${user.name}",style:constTextStyle(fontSize:20,fontWeight:FontWeight.bold,),),Text("Email:${user.email}",style:constTextStyle(fontSize:15,fontWeight:FontWeight.bold,),),Text("Phone Number:${user.phoneNumber}",style:constTextStyle(fontSize:15,fontWeight:FontWeight.bold,),),Text("Password:${user.password}",style:constTextStyle(fontSize:15,fontWeight:FontWeight.bold,),),],),),),);}

Video Demo

Github Code

Further Reading