How to use SQFlite on Windows Flutter
Tags: flutter, dart, sql, programming
Published: 2023-08-23 09:51:06


## 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.

```dart
flutter pub add  sqflite_common_ffi
```



### Step - 2 Downloading SQLite DLL File

Remember to Download Precompiled Binaries for Windows

[SQLite DLL LINK](https://www.sqlite.org/download.html)

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

```html
windows
```
![Path](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vrwfq7nudfv6oo8ngcwq.PNG)

## 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

```dart
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

```dart
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.

```dart
  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.

```dart
  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");
  }
```

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

```dart
  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

```dart
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).

```dart

  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.

```dart
  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'],
      );
    });
  }
```

#### 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).

```dart

  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;
  }
```

#### 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).

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

    batch.delete('users');

    await batch.commit();
  }
```

## Full Code for Database

```dart
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();
  }
}
```

### 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

```dart

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

```

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

## Video Demo
[![Video Demo](https://img.youtube.com/vi/1GA02DzV-LY/0.jpg)](https://www.youtube.com/watch?v=1GA02DzV-LY)

## [Github Code](https://gist.github.com/ayoubzulfiqar/ea0c22461075ce708a2abbca433a9744)

### [Further Reading](https://pub.dev/packages/sqflite_common_ffi)