Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to do a database query with SQFlite in Flutter

Tags:

How do you query data from SQLite database in Flutter using the SQFlite plugin?

I have been working on learning this recently, so I am adding my answer below as a means to help me learn and also as a quick reference for others in the future.

like image 622
Suragch Avatar asked Jan 16 '19 19:01

Suragch


People also ask

What is sqflite database in Flutter?

SQFlite is a plugin in flutter which is used to store the data. In SQFlite we perform many operations like create, delete, update, etc. This operation is called CRUD Operations. Using this we can easily store the data in a local database.

Is SQLite and sqflite same?

SQLite is a relational database management system contained in a C library. SQLite is not a client-server database engine. sqflite the package provides us to implement SQLite into our flutter app. This package provides various methods to insert, update, edit, and fetch queries from the database.


1 Answers

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

I'm keeping 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 query) in 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 in 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. 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 whereArguments list get substituted in place of the ?s in the whereString. In this case there was only one ? so the whereArguments only had one item. If there were two ?s (for example an integer and a string), then you would have two items in the list.

Raw query

If you prefer the familiarity or flexibility of SQL code itself, you can do a raw query. 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

enter image description here

For your copy-and-paste convenience, here is the layout code for main.dart:

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

Going on

  • This post is a development from my previous post: Simple SQFlite database example in Flutter. See that post for other SQL operations and advice.
like image 104
Suragch Avatar answered Sep 23 '22 17:09

Suragch