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:
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.
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.
Queries: SQFlite allows you to perform various types of queries on your database, including selecting specific data, filtering, sorting, and joining tables.
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.
Transactions: SQFlite supports transactions, which ensure data consistency and integrity during complex database operations.
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.
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 command flutter pub add sqflite_common_ffi
is used to add the sqflite_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.
Using sqflite_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.
flutter pub add sqflite_common_ffi
Step - 2 Downloading SQLite DLL File
Remember to Download Precompiled Binaries for Windows
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' show immutable;
@immutable
class User {
final int id;
final String name;
final String email;
final int password;
final int phoneNumber;
const User({
required this.id,
required this.name,
required this.email,
required this.password,
required this.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 a Future<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> get database async {
if (_database != null) {
return _database!;
}
_database = await initWinDB();
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();
final databaseFactory = databaseFactoryFfi;
return await databaseFactory.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();
final databaseFactory = databaseFactoryFfi;
final appDocumentsDir = await getApplicationDocumentsDirectory();
final dbPath = join(appDocumentsDir.path, "databases", "data.db");
final winLinuxDB = await databaseFactory.openDatabase(
dbPath,
options: OpenDatabaseOptions(
version: 1,
onCreate: _onCreate,
),
);
return winLinuxDB;
} else if (Platform.isAndroid || Platform.isIOS) {
final documentsDirectory = await getApplicationDocumentsDirectory();
final path = join(documentsDirectory.path, "data.db");
final iOSAndroidDB = await openDatabase(
path,
version: 1,
onCreate: _onCreate,
);
return iOSAndroidDB;
}
throw Exception("Unsupported platform");
}
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.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.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.-
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.
-
The
IF 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 the version
). It sets up a table named users
with specific columns for user information. If the users
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.g id
, name
, email
etc
Future<void> _onCreate(Database database, int version) async {
final db = database;
await db.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 with Android | iOS
Single Addition of data
Future<User> insertUSer(User user) async {
final db = await database;
db.insert(
"users",
user.toMap(),
conflictAlgorithm: ConflictAlgorithm.replace,
);
return user;
}
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 {
final db = await database;
final batch = db.batch();
final Random random = Random();
final List<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 (final User user in userList) {
batch.insert(
'users',
user.toMap(),
conflictAlgorithm: ConflictAlgorithm.replace,
);
}
await batch.commit();
return userList;
}
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 {
final db = await database;
final List<Map<String, dynamic>> maps = await db.query('users');
<span class="k">return</span> <span class="kt">List</span><span class="o">.</span><span class="na">generate</span><span class="p">(</span><span class="n">maps</span><span class="o">.</span><span class="na">length</span><span class="p">,</span> <span class="p">(</span><span class="n">index</span><span class="p">)</span> <span class="p">{</span>
<span class="k">return</span> <span class="n">User</span><span class="p">(</span>
<span class="nl">id:</span> <span class="n">maps</span><span class="p">[</span><span class="n">index</span><span class="p">][</span><span class="s">'id'</span><span class="p">],</span>
<span class="nl">name:</span> <span class="n">maps</span><span class="p">[</span><span class="n">index</span><span class="p">][</span><span class="s">'name'</span><span class="p">],</span>
<span class="nl">email:</span> <span class="n">maps</span><span class="p">[</span><span class="n">index</span><span class="p">][</span><span class="s">'email'</span><span class="p">],</span>
<span class="nl">password:</span> <span class="n">maps</span><span class="p">[</span><span class="n">index</span><span class="p">][</span><span class="s">'password'</span><span class="p">],</span>
<span class="nl">phoneNumber:</span> <span class="n">maps</span><span class="p">[</span><span class="n">index</span><span class="p">][</span><span class="s">'phoneNumber'</span><span class="p">],</span>
<span class="p">);</span>
<span class="p">});</span>
}
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(int userId) async {
final db = await database;
final List<Map<String, dynamic>> maps = await db.query(
'users',
where: 'id = ?',
whereArgs: [userId],
);
<span class="k">if</span> <span class="p">(</span><span class="n">maps</span><span class="o">.</span><span class="na">isNotEmpty</span><span class="p">)</span> <span class="p">{</span>
<span class="k">return</span> <span class="n">User</span><span class="p">(</span>
<span class="nl">id:</span> <span class="n">maps</span><span class="p">[</span><span class="mi">0</span><span class="p">][</span><span class="s">'id'</span><span class="p">],</span>
<span class="nl">name:</span> <span class="n">maps</span><span class="p">[</span><span class="mi">0</span><span class="p">][</span><span class="s">'name'</span><span class="p">],</span>
<span class="nl">email:</span> <span class="n">maps</span><span class="p">[</span><span class="mi">0</span><span class="p">][</span><span class="s">'email'</span><span class="p">],</span>
<span class="nl">password:</span> <span class="n">maps</span><span class="p">[</span><span class="mi">0</span><span class="p">][</span><span class="s">'password'</span><span class="p">],</span>
<span class="nl">phoneNumber:</span> <span class="n">maps</span><span class="p">[</span><span class="mi">0</span><span class="p">][</span><span class="s">'phoneNumber'</span><span class="p">],</span>
<span class="p">);</span>
<span class="p">}</span>
<span class="k">return</span> <span class="kc">null</span><span class="p">;</span>
}
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 {
final db = await database;
final Batch batch = db.batch();
<span class="n">batch</span><span class="o">.</span><span class="na">delete</span><span class="p">(</span><span class="s">'users'</span><span class="p">);</span>
<span class="k">await</span> <span class="n">batch</span><span class="o">.</span><span class="na">commit</span><span class="p">();</span>
}
Full Code for Database
import 'dart:math';
import 'package:sqflite_common_ffi/sqflite_ffi.dart';
class SQLiteHelper {
Database? _database;
Future<Database> get database async {
if (_database != null) {
return _database!;
}
_database = await initWinDB();
return _database!;
}
Future<Database> initWinDB() async {
sqfliteFfiInit();
final databaseFactory = databaseFactoryFfi;
return await databaseFactory.openDatabase(
inMemoryDatabasePath,
options: OpenDatabaseOptions(
onCreate: _onCreate,
version: 1,
),
);
}
Future<void> _onCreate(Database database, int version) async {
final db = database;
await db.execute(""" CREATE TABLE IF NOT EXISTS users(
id INTEGER PRIMARY KEY,
name TEXT,
email TEXT,
password INTEGER,
phoneNumber INTEGER
)
""");
}
Future<User> insertUSer(User user) async {
final db = await database;
db.insert(
"users",
user.toMap(),
conflictAlgorithm: ConflictAlgorithm.replace,
);
return user;
}
Future<List<User>> batchInsert() async {
final db = await database;
final batch = db.batch();
final Random random = Random();
final List<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 (final User user in userList) {
batch.insert(
'users',
user.toMap(),
conflictAlgorithm: ConflictAlgorithm.replace,
);
}
await batch.commit();
return userList;
}
Future<List<User>> getAllUsers() async {
final db = await database;
final List<Map<String, dynamic>> maps = await db.query('users');
<span class="k">return</span> <span class="kt">List</span><span class="o">.</span><span class="na">generate</span><span class="p">(</span><span class="n">maps</span><span class="o">.</span><span class="na">length</span><span class="p">,</span> <span class="p">(</span><span class="n">index</span><span class="p">)</span> <span class="p">{</span>
<span class="k">return</span> <span class="n">User</span><span class="p">(</span>
<span class="nl">id:</span> <span class="n">maps</span><span class="p">[</span><span class="n">index</span><span class="p">][</span><span class="s">'id'</span><span class="p">],</span>
<span class="nl">name:</span> <span class="n">maps</span><span class="p">[</span><span class="n">index</span><span class="p">][</span><span class="s">'name'</span><span class="p">],</span>
<span class="nl">email:</span> <span class="n">maps</span><span class="p">[</span><span class="n">index</span><span class="p">][</span><span class="s">'email'</span><span class="p">],</span>
<span class="nl">password:</span> <span class="n">maps</span><span class="p">[</span><span class="n">index</span><span class="p">][</span><span class="s">'password'</span><span class="p">],</span>
<span class="nl">phoneNumber:</span> <span class="n">maps</span><span class="p">[</span><span class="n">index</span><span class="p">][</span><span class="s">'phoneNumber'</span><span class="p">],</span>
<span class="p">);</span>
<span class="p">});</span>
}
Future<User?> getUserById(int userId) async {
final db = await database;
final List<Map<String, dynamic>> maps = await db.query(
'users',
where: 'id = ?',
whereArgs: [userId],
);
<span class="k">if</span> <span class="p">(</span><span class="n">maps</span><span class="o">.</span><span class="na">isNotEmpty</span><span class="p">)</span> <span class="p">{</span>
<span class="k">return</span> <span class="n">User</span><span class="p">(</span>
<span class="nl">id:</span> <span class="n">maps</span><span class="p">[</span><span class="mi">0</span><span class="p">][</span><span class="s">'id'</span><span class="p">],</span>
<span class="nl">name:</span> <span class="n">maps</span><span class="p">[</span><span class="mi">0</span><span class="p">][</span><span class="s">'name'</span><span class="p">],</span>
<span class="nl">email:</span> <span class="n">maps</span><span class="p">[</span><span class="mi">0</span><span class="p">][</span><span class="s">'email'</span><span class="p">],</span>
<span class="nl">password:</span> <span class="n">maps</span><span class="p">[</span><span class="mi">0</span><span class="p">][</span><span class="s">'password'</span><span class="p">],</span>
<span class="nl">phoneNumber:</span> <span class="n">maps</span><span class="p">[</span><span class="mi">0</span><span class="p">][</span><span class="s">'phoneNumber'</span><span class="p">],</span>
<span class="p">);</span>
<span class="p">}</span>
<span class="k">return</span> <span class="kc">null</span><span class="p">;</span>
}
Future<void> deleteAllUsers() async {
final db = await database;
final Batch batch = db.batch();
<span class="n">batch</span><span class="o">.</span><span class="na">delete</span><span class="p">(</span><span class="s">'users'</span><span class="p">);</span>
<span class="k">await</span> <span class="n">batch</span><span class="o">.</span><span class="na">commit</span><span class="p">();</span>
}
}
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 to Android | iOS
so Do You own practice to learn more
Flutter App - Loading Data to Screen
void main() {
runApp(const App());
}
class App extends StatelessWidget {
const App({super.key});
@override
Widget build(BuildContext context) {
return const MaterialApp(
debugShowCheckedModeBanner: false,
home: Home(),
);
}
}
import 'package:flutter/material.dart';
class Home extends StatefulWidget {
const Home({super.key});
@override
State<Home> createState() => _HomeState();
}
class _HomeState extends State<Home> {
final SQLiteHelper helper = SQLiteHelper();
@override
void initState() {
super.initState();
WidgetsFlutterBinding.ensureInitialized();
helper.initWinDB();
}
@override
Widget build(BuildContext context) {
return Scaffold(
appBar: AppBar(
leading: TextButton(
onPressed: () async {
await helper.batchInsert();
setState(() {});
},
child: const Text("ADD"),
),
actions: [
TextButton(
onPressed: () async {
await helper.deleteAllUsers();
setState(() {});
},
child: const Text("DEL"),
),
]),
body: FutureBuilder<List<User>>(
future: helper.getAllUsers(),
builder: (context, snapshot) {
if (snapshot.connectionState == ConnectionState.waiting) {
return const Center(child: CircularProgressIndicator());
} else if (snapshot.hasError) {
return Center(child: Text('Error: ${snapshot.error}'));
} else if (!snapshot.hasData || snapshot.data!.isEmpty) {
return const Center(child: Text('No users found.'));
} else {
final users = snapshot.data!;
return ListView.builder(
itemCount: users.length,
itemBuilder: (context, index) {
final user = users[index];
<span class="k">return</span> <span class="n">_card</span><span class="p">(</span><span class="n">user</span><span class="p">,</span> <span class="n">context</span><span class="p">);</span>
<span class="p">},</span>
<span class="p">);</span>
<span class="p">}</span>
<span class="p">},</span>
<span class="p">),</span>
<span class="p">);</span>
}
}
Widget _card(User user, BuildContext context) {
return Padding(
padding: const EdgeInsets.all(8.0),
child: Card(
child: Padding(
padding: const EdgeInsets.all(20),
child: Column(
mainAxisAlignment: MainAxisAlignment.start,
crossAxisAlignment: CrossAxisAlignment.start,
children: [
Text(
"ID: ${user.id}",
style: const TextStyle(
fontSize: 20,
fontWeight: FontWeight.bold,
),
),
Text(
"Name: ${user.name}",
style: const TextStyle(
fontSize: 20,
fontWeight: FontWeight.bold,
),
),
Text(
"Email: ${user.email}",
style: const TextStyle(
fontSize: 15,
fontWeight: FontWeight.bold,
),
),
Text(
"Phone Number: ${user.phoneNumber}",
style: const TextStyle(
fontSize: 15,
fontWeight: FontWeight.bold,
),
),
Text(
"Password: ${user.password}",
style: const TextStyle(
fontSize: 15,
fontWeight: FontWeight.bold,
),
),
],
),
),
),
);
}
Top comments (2)
I have found this really useful thanks a bunch, 2 things,
I spent some time reading the sqflite src and its seems safe re SQL injection particularly if u use the DatabaseFactory 'insert', and avoid running dynamic queries with 'rawQuery'
its using isolates so should avoid table locks at least on the dart side
the next issue would be on memory leaks w.r.t db connections and this would be a heavily system dependent issue
thanks again for sharing your work here
Hello, thank you for your explanation
There is a problem after installing the application on Windows. I enter the data, and when I close the application, I do not find the previous data. How can this problem be solved؟