In this blog, we will learn basic SQLite procedures by constructing our own Flutter application. But first one might ask: What is SQLite? Let’s get into it!
Foundationally, SQLite is an open-source database engine built in C. It is not a separate app, but rather a library that software developers incorporate into their applications. As such, it is a member of the embedded database family.
Additionally, SQLite supports bindings for a wide range of computer languages. It follows PostgreSQL syntax in general, but does not impose type checking. For instance, A string can be inserted into a column specified as an integer.
Furthermore, we’re going to create a Flutter project that utilizes SQLite by inserting queries, viewing, deleting, and updating. Let’s get started!
SQLite In Flutter Project:
To demonstrate, we will make a simple car database in which each car has an id, a name, and the number of miles driven. Based on the required fields, our Flutter app should be able to insert a record into the database, query rows, update rows, and delete rows.
Additionally, The following Flutter project also includes a TabBar with a TabBarView for their respective operations.
Dependency
Critically, we must add the sqflite and path packages to the dependencies area, underneath the flutter property and if your using Visual Studio Code, in the top right corner of the editor, click the Packages get button.
dev_dependencies:
flutter_test:
sdk: flutter
sqflite:
path:Car Class
First, create a file named car.dart in the lib folder with the class shown below. In fact, we will be using this class to define our Car object and transport data between UI and Database.
import 'package:mysqlite/dbhelper.dart';
class Car {
int? id;
String? name;
int? miles;
Car(this.id, this.name, this.miles);
Car.fromMap(Map<String, dynamic> map) {
id = map['id'];
name = map['name'];
miles = map['miles'];
}
Map<String, dynamic> toMap() {
return {
DatabaseHelper.columnId: id,
DatabaseHelper.columnName: name,
DatabaseHelper.columnMiles: miles,
};
}
}SQLite Database Helper Class
Secondly, we must create the Database helper class as it includes methods for creating a database if one is not already present, connecting to the database, and performing SQLite operations such as searching rows from a table, updating, deleting, inserting, and so on.
import 'package:mysqlite/car.dart';
import 'package:path/path.dart';
import 'package:sqflite/sqflite.dart';
class DatabaseHelper {
static const _databaseName = "cardb.db";
static const _databaseVersion = 1;
static const table = 'cars_table';
static const columnId = 'id';
static const columnName = 'name';
static const columnMiles = 'miles';
// 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 =>
_database ??= await _initDatabase();
// this opens the database (and creates it if it doesn't exist)
_initDatabase() async {
String path = join(await getDatabasesPath(), _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 AUTOINCREMENT,
$columnName TEXT NOT NULL,
$columnMiles INTEGER NOT NULL
)
''');
}
// Helper methods
// Inserts a row in the database where each key in the Map is a column name
// and the value is the column value. The return value is the id of the
// inserted row.
Future<int> insert(Car car) async {
Database db = await instance.database;
return await db.insert(table, {'name': car.name, 'miles': car.miles});
}
// All of the rows are returned as a list of maps, where each map is
// a key-value list of columns.
Future<List<Map<String, dynamic>>> queryAllRows() async {
Database db = await instance.database;
return await db.query(table);
}
// Queries rows based on the argument received
Future<List<Map<String, dynamic>>> queryRows(name) async {
Database db = await instance.database;
return await db.query(table, where: "$columnName LIKE '%$name%'");
}
// All of the methods (insert, query, update, delete) can also be done using
// raw SQL commands. This method uses a raw query to give the row count.
Future<int?> queryRowCount() async {
Database db = await instance.database;
return Sqflite.firstIntValue(await db.rawQuery('SELECT COUNT(*) FROM $table'));
}
// We are assuming here that the id column in the map is set. The other
// column values will be used to update the row.
Future<int> update(Car car) async {
Database db = await instance.database;
int id = car.toMap()['id'];
return await db.update(table, car.toMap(), where: '$columnId = ?', whereArgs: [id]);
}
// Deletes the row specified by the id. The number of affected rows is
// returned. This should be 1 as long as the row exists.
Future<int> delete(int id) async {
Database db = await instance.database;
return await db.delete(table, where: '$columnId = ?', whereArgs: [id]);
}
}Main.dart (UI)
Finally, the UI code is shown below; as you can see, the app is divided into different screens, which are displayed on their respective tabs via the TabBar widget.
import 'package:flutter/material.dart';
import 'package:mysqlite/car.dart';
import 'package:mysqlite/dbhelper.dart';
void main() => runApp(MyApp());
class MyApp extends StatelessWidget {
@override
Widget build(BuildContext context) {
return MaterialApp(
title: 'NucleioTechnologies - SQLite',
theme: ThemeData(
primarySwatch: Colors.orange,
elevatedButtonTheme: ElevatedButtonThemeData(
style: ButtonStyle(foregroundColor: MaterialStateProperty.all<Color>(Colors.white),),
),
),
home: MyHomePage(),
);
}
}
class MyHomePage extends StatefulWidget {
@override
_MyHomePageState createState() => _MyHomePageState();
}
class _MyHomePageState extends State<MyHomePage> {
final dbHelper = DatabaseHelper.instance;
List<Car> cars = [];
List<Car> carsByName = [];
//controllers used in insert operation UI
TextEditingController nameController = TextEditingController();
TextEditingController milesController = TextEditingController();
//controllers used in update operation UI
TextEditingController idUpdateController = TextEditingController();
TextEditingController nameUpdateController = TextEditingController();
TextEditingController milesUpdateController = TextEditingController();
//controllers used in delete operation UI
TextEditingController idDeleteController = TextEditingController();
//controllers used in query operation UI
TextEditingController queryController = TextEditingController();
final GlobalKey<ScaffoldState> _scaffoldKey = new GlobalKey<ScaffoldState>();
void _showMessageInScaffold(String message) {
ScaffoldMessenger.of(context).showSnackBar(
SnackBar(
content: Text(message),
duration: const Duration(milliseconds: 300),
),
);
}
@override
Widget build(BuildContext context) {
return DefaultTabController(
length: 5,
child: Scaffold(
resizeToAvoidBottomInset: false,
key: _scaffoldKey,
appBar: AppBar(
bottom: const TabBar(
labelColor: Colors.white,
tabs: [
Tab(
text: "Insert",
icon: Icon(Icons.add),
),
Tab(
text: "View",
icon: Icon(Icons.remove_red_eye_outlined),
),
Tab(
text: "Search",
icon: Icon(Icons.search),
),
Tab(
text: "Update",
icon: Icon(Icons.update),
),
Tab(
text: "Delete",
icon: Icon(Icons.delete_forever),
),
],
),
title: const Text('NucleioTechnologies - SQLite', style: TextStyle(color: Colors.white),),
),
body: TabBarView(
children: [
Center(
child: Column(
children: <Widget>[
Container(
padding: const EdgeInsets.all(20),
child: TextField(
controller: nameController,
decoration: const InputDecoration(
border: OutlineInputBorder(),
labelText: 'Car Name',
),
),
),
Container(
padding: const EdgeInsets.all(20),
child: TextField(
controller: milesController,
decoration: const InputDecoration(
border: OutlineInputBorder(),
labelText: 'Car Miles',
),
),
),
ElevatedButton(
child: const Text('Insert Car Details'),
onPressed: () {
String name = nameController.text;
int miles = int.parse(milesController.text);
_insert(name, miles);
},
),
],
),
),
ListView.builder(
padding: const EdgeInsets.all(8),
itemCount: cars.length + 1,
itemBuilder: (BuildContext context, int index) {
if (index == cars.length) {
return ElevatedButton(
child: const Text('Refresh'),
onPressed: () {
setState(() {
_queryAll();
});
},
);
}
return SizedBox(
height: 40,
child: Center(
child: Text(
'[${cars[index].id}] ${cars[index].name} - ${cars[index].miles} miles',
style: const TextStyle(fontSize: 18),
),
),
);
},
),
Center(
child: Column(
children: <Widget>[
Container(
padding: const EdgeInsets.all(20),
height: 100,
child: TextField(
controller: queryController,
decoration: const InputDecoration(
border: OutlineInputBorder(),
labelText: 'Car Name',
),
onChanged: (text) {
if (text.length >= 2) {
setState(() {
_query(text);
});
} else {
setState(() {
carsByName.clear();
});
}
},
),
),
SizedBox(
height: 300,
child: ListView.builder(
padding: const EdgeInsets.all(8),
itemCount: carsByName.length,
itemBuilder: (BuildContext context, int index) {
return Container(
height: 50,
margin: const EdgeInsets.all(2),
child: Center(
child: Text(
'[${carsByName[index].id}] ${carsByName[index].name} - ${carsByName[index].miles} miles',
style: const TextStyle(fontSize: 18),
),
),
);
},
),
),
],
),
),
Center(
child: Column(
children: <Widget>[
Container(
padding: const EdgeInsets.all(20),
child: TextField(
controller: idUpdateController,
decoration: const InputDecoration(
border: OutlineInputBorder(),
labelText: 'Car id',
),
),
),
Container(
padding: const EdgeInsets.all(20),
child: TextField(
controller: nameUpdateController,
decoration: const InputDecoration(
border: OutlineInputBorder(),
labelText: 'Car Name',
),
),
),
Container(
padding: const EdgeInsets.all(20),
child: TextField(
controller: milesUpdateController,
decoration: const InputDecoration(
border: OutlineInputBorder(),
labelText: 'Car Miles',
),
),
),
ElevatedButton(
child: const Text('Update Car Details'),
onPressed: () {
int id = int.parse(idUpdateController.text);
String name = nameUpdateController.text;
int miles = int.parse(milesUpdateController.text);
_update(id, name, miles);
},
),
],
),
),
Center(
child: Column(
children: <Widget>[
Container(
padding: const EdgeInsets.all(20),
child: TextField(
controller: idDeleteController,
decoration: const InputDecoration(
border: OutlineInputBorder(),
labelText: 'Car id',
),
),
),
ElevatedButton(
child: const Text('Delete'),
onPressed: () {
int id = int.parse(idDeleteController.text);
_delete(id);
},
),
],
),
),
],
),
),
);
}
void _insert(name, miles) async {
// row to insert
Map<String, dynamic> row = {
DatabaseHelper.columnName: name,
DatabaseHelper.columnMiles: miles
};
Car car = Car.fromMap(row);
final id = await dbHelper.insert(car);
_showMessageInScaffold('inserted row id: $id');
}
void _queryAll() async {
final allRows = await dbHelper.queryAllRows();
cars.clear();
for (var row in allRows) {
cars.add(Car.fromMap(row));
}
_showMessageInScaffold('Query done.');
setState(() {});
}
void _query(name) async {
final allRows = await dbHelper.queryRows(name);
carsByName.clear();
allRows.forEach((row) => carsByName.add(Car.fromMap(row)));
}
void _update(id, name, miles) async {
// row to update
Car car = Car(id, name, miles);
final rowsAffected = await dbHelper.update(car);
_showMessageInScaffold('updated $rowsAffected row(s)');
}
void _delete(id) async {
// Assuming that the number of rows is the id for the last row.
final rowsDeleted = await dbHelper.delete(id);
_showMessageInScaffold('deleted $rowsDeleted row(s): row $id');
}
}
Conclusion
In conclusion, we learned basic SQLite procedures by constructing our own Flutter application. We also made a simple car class with an id, a name, and the number of miles driven for each car. The SQLite in Flutter project we made also includes a TabBar for the Insert screen, View screen, Query screen, Update screen, and Delete screen along with its actions on the car table.