How to Do Database Query With SQFlite In Flutter?

SQLite is used to store and retrieve data to and from into local database. So in today’s article, we will drive through how to do database Query With SQFlite in flutter.

Dive into the world of database querying in Flutter as we explore the powerful SQFlite package. In this comprehensive blog, we’ll equip you with the knowledge and techniques to perform efficient database queries using SQFlite. From basic CRUD operations to complex filtering and sorting, our step-by-step guide will empower you to leverage the full potential of SQFlite to interact with your app’s SQLite database.

Learn how to construct queries, handle results, and optimize performance for seamless data retrieval and manipulation. Whether you’re a beginner or an experienced developer, our comprehensive guide will help you level up your Flutter app’s data management capabilities. Join us on this exciting journey to become a master of database queries with SQFlite in Flutter.

Database Query with SQFlite In Flutter

Add the dependencies

Open pubspec.yaml and in the dependencies section add the following lines:

sqflite: ^1.0.0
path_provider: ^0.4.1

The sqflite is the SQFlite plugin of course and the path_provider will help us get the user directory on Android and iPhone. You can check the most up-to-date version numbers here: sqflite and path_provider.

Make a database helper class

• We need a global reference to the database in a singleton class.
• This will prevent concurrency issues and data leaks.
• You can also add helper methods like queries here for accessing the database.

Create a new file called database_helper.dart and paste in the following code:

import 'dart:io' show Directory;
import 'package:path/path.dart' show join;
import 'package:sqflite/sqflite.dart';
import 'package:path_provider/path_provider.dart' show getApplicationDocumentsDirectory;

class DatabaseHelper {

  static final _databaseName = "MyDatabase.db";
  static final _databaseVersion = 1;

  static final table = 'my_table';

  static final columnId = '_id';
  static final columnName = 'name';
  static final columnAge = 'age';

  // make this a singleton class
  DatabaseHelper._privateConstructor();
  static final DatabaseHelper instance = DatabaseHelper._privateConstructor();

  // only have a single app-wide reference to the database
  static Database _database;
  Future<Database> get database async {
    if (_database != null) return _database;
    // lazily instantiate the db the first time it is accessed
    _database = await _initDatabase();
    return _database;
  }

  // this opens the database (and creates it if it doesn't exist)
  _initDatabase() async {
    Directory documentsDirectory = await getApplicationDocumentsDirectory();
    String path = join(documentsDirectory.path, _databaseName);
    return await openDatabase(path,
        version: _databaseVersion,
        onCreate: _onCreate);
  }

  // SQL code to create the database table
  Future _onCreate(Database db, int version) async {
    await db.execute('''
          CREATE TABLE $table (
            $columnId INTEGER PRIMARY KEY,
            $columnName TEXT NOT NULL,
            $columnAge INTEGER NOT NULL
          )
          ''');

    // prepopulate a few rows (consider using a transaction)
    await db.rawInsert('INSERT INTO $table ($columnName, $columnAge) VALUES("Bob", 23)');
    await db.rawInsert('INSERT INTO $table ($columnName, $columnAge) VALUES("Mary", 32)');
    await db.rawInsert('INSERT INTO $table ($columnName, $columnAge) VALUES("Susan", 12)');
  }
}

Note that when the database is created I pre-populated a few rows. This is so that we have something to work with within the query examples below.

Query data

We’ll use an async method to do our query because database operations can be expensive.

Get all rows

To do a SELECT * and return everything in the table you just pass in the table name.

_query() async {

   // get a reference to the database
   Database db = await DatabaseHelper.instance.database;

   // get all rows
   List<Map> result = await db.query(DatabaseHelper.table);

   // print the results
   result.forEach((row) => print(row));
   // {_id: 1, name: Bob, age: 23}
   // {_id: 2, name: Mary, age: 32}
   // {_id: 3, name: Susan, age: 12}
 }

Get a single row

We can pass an argument in for the where parameter to select specific rows that meet our criteria. So in this example, we will query the row with an ID of 1.

_query() async {

  // get a reference to the database
  Database db = await DatabaseHelper.instance.database;

  // get single row
  List<String> columnsToSelect = [
    DatabaseHelper.columnId,
    DatabaseHelper.columnName,
    DatabaseHelper.columnAge,
  ];
  String whereString = '${DatabaseHelper.columnId} = ?';
  int rowId = 1;
  List<dynamic> whereArguments = [rowId];
  List<Map> result = await db.query(
      DatabaseHelper.table,
      columns: columnsToSelect,
      where: whereString,
      whereArgs: whereArguments);

  // print the results
  result.forEach((row) => print(row));
  // {_id: 1, name: Bob, age: 23}
}

The items in the where argument list get substituted in place of the ?s in the where string. In this case, there was only one? so the where argument only had one item. If there were two ?s then you would have two items on the list.

Raw query

If you prefer the familiarity or flexibility of SQL code itself, you can do a raw query. So in this example, we will select any row whose name column is ‘Mary‘.

_query() async {

  // get a reference to the database
  Database db = await DatabaseHelper.instance.database;

  // raw query
  List<Map> result = await db.rawQuery('SELECT * FROM my_table WHERE name=?', ['Mary']);

  // print the results
  result.forEach((row) => print(row));
  // {_id: 2, name: Mary, age: 32}
}

Be sure to use data binding using? string replacements. This will guard against SQL injection attacks.

Notes

• You will have to import the DatabaseHelper class and sqflite if you are in another file (like main.dart).
• The SQFlite plugin uses a Map<String, dynamic> to map the column names to the data in each row.

Supplemental Code

import 'package:flutter/material.dart';
// I called my project 'flutter_database_operations'. You can update for yours.
import 'package:flutter_database_operations/database_helper.dart';
import 'package:sqflite/sqflite.dart';

void main() => runApp(MyApp());

class MyApp extends StatelessWidget {
  @override
  Widget build(BuildContext context) {
    return MaterialApp(
      title: 'SQFlite Demo',
      theme: ThemeData(
        primarySwatch: Colors.blue,
      ),
      home: MyHomePage(),
    );
  }
}

class MyHomePage extends StatelessWidget {

  @override
  Widget build(BuildContext context) {
    return Scaffold(
      appBar: AppBar(
        title: Text('sqflite'),
      ),
      body: RaisedButton(
        child: Text('query', style: TextStyle(fontSize: 20),),
        onPressed: () {_query();},
      ),
    );
  }

  _query() async {

    // get a reference to the database
    Database db = await DatabaseHelper.instance.database;

    // get all rows
    List<Map> result = await db.query(DatabaseHelper.table);

    // get single row
    //List<Map> result = await db.query(DatabaseHelper.table,
    //    columns: [DatabaseHelper.columnId, DatabaseHelper.columnName, DatabaseHelper.columnAge],
    //    where: '${DatabaseHelper.columnId} = ?',
    //    whereArgs: [1]);


    // raw query
    //List<Map> result = await db.rawQuery('SELECT * FROM my_table WHERE name=?', ['Mary']);

    // get each row in the result list and print it
    result.forEach((row) => print(row));
  }
}

Conclusion:

In this article, we have been through how to do database Query With SQFlite in flutter.

Keep Learning !!! Keep Fluttering !!!

Now, I’d like to hear from you:

Which strategy from today’s post are you trying??

Or maybe I didn’t mention one of the methods that worked for you!!

Either way, Let me know by leaving a comment below right now.

Do let us know in the comments if you are still confused in flutter!! we would love to help 🙂

FlutterAgency.com is our portal Platform dedicated to Flutter Technology and Flutter Developers. The portal is full of cool resources from Flutter like Flutter Widget Guide, Flutter Projects, Code libs and etc.

FlutterAgency.com is one of the most popular online portals dedicated to Flutter Technology and daily thousands of unique visitors come to this portal to enhance their knowledge of Flutter.

Written by Abhishek Dhanani

Abhishek Dhanani, a skilled software developer with 3+ years of experience, masters Dart, JavaScript, TypeScript, and frameworks like Flutter and NodeJS. Proficient in MySQL, Firebase, and cloud platforms AWS and GCP, he delivers innovative digital solutions.