DEV Community

Cover image for How to use SQFlite on Windows Flutter
Ayoub Ali
Ayoub Ali

Posted on • Updated on

How to use SQFlite on Windows Flutter

#flutter #dart #sql #programming

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 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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Path

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,
    };
  }
}
Enter fullscreen mode Exit fullscreen mode

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!;
  }
Enter fullscreen mode Exit fullscreen mode

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,
      ),
    );
  }
Enter fullscreen mode Exit fullscreen mode

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");
  }
Enter fullscreen mode Exit fullscreen mode

  1. Future<void> _onCreate(Database database, int version) async: This function takes two arguments: the database instance and the version 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 named db that points to the provided database instance. This reference will be used to execute SQL commands.

  3. await db.execute(""" ... """);: This line is using the execute method on the db 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 named users 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. 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
          )
 """);
  }
Enter fullscreen mode Exit fullscreen mode

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;
  }
Enter fullscreen mode Exit fullscreen mode

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;
  }
Enter fullscreen mode Exit fullscreen mode

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

    return List.generate(maps.length, (index) {
      return User(
        id: maps[index]['id'],
        name: maps[index]['name'],
        email: maps[index]['email'],
        password: maps[index]['password'],
        phoneNumber: maps[index]['phoneNumber'],
      );
    });
  }
Enter fullscreen mode Exit fullscreen mode

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],
    );

    if (maps.isNotEmpty) {
      return User(
        id: maps[0]['id'],
        name: maps[0]['name'],
        email: maps[0]['email'],
        password: maps[0]['password'],
        phoneNumber: maps[0]['phoneNumber'],
      );
    }

    return null;
  }
Enter fullscreen mode Exit fullscreen mode

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

    batch.delete('users');

    await batch.commit();
  }
Enter fullscreen mode Exit fullscreen mode

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

    return List.generate(maps.length, (index) {
      return User(
        id: maps[index]['id'],
        name: maps[index]['name'],
        email: maps[index]['email'],
        password: maps[index]['password'],
        phoneNumber: maps[index]['phoneNumber'],
      );
    });
  }

  Future<User?> getUserById(int userId) async {
    final db = await database;
    final List<Map<String, dynamic>> maps = await db.query(
      'users',
      where: 'id = ?',
      whereArgs: [userId],
    );

    if (maps.isNotEmpty) {
      return User(
        id: maps[0]['id'],
        name: maps[0]['name'],
        email: maps[0]['email'],
        password: maps[0]['password'],
        phoneNumber: maps[0]['phoneNumber'],
      );
    }

    return null;
  }


  Future<void> deleteAllUsers() async {
    final db = await database;
    final Batch batch = db.batch();

    batch.delete('users');

    await batch.commit();
  }
}
Enter fullscreen mode Exit fullscreen mode

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(),
    );
  }
}
Enter fullscreen mode Exit fullscreen mode 
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];

                return _card(user, context);
              },
            );
          }
        },
      ),
    );
  }
}

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,
              ),
            ),
          ],
        ),
      ),
    ),
  );
}
Enter fullscreen mode Exit fullscreen mode

Video Demo

Video Demo

Github Code

Further Reading

Top comments (2)

Collapse
 
nigel447 profile image
nigel447 • • Edited

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

Collapse
 
mahmudt profile image
mahmud •

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؟

Read next

shariqahmed525 profile image

How I Would Learn To Code (If I Could Start Over)

Shariq Ahmed -

imashwani profile image

JavaScript Non-Primitive Data Types

Ashwani Singh -

yuricodesbot profile image

Supabase Auth now supports Anonymous Sign-ins

Yuri -

aneeqakhan profile image

How to Add Markers to Google Maps in React.js

Aneeqa Khan -