In this tutorial, I’ll assume you have some basic knowledge and you’re just looking for a sample code to use as a reference. So here we have a Database Helper class to help manage an access the database, and a Main class to interact with the database and see the results in your screen.
Importing the packages
Before coding, don’t for get to import the path_provider package into your project by typing and running the following command on Terminal:
flutter pub add path_providerNow import the sqflite package by typing and running the following line on Terminal:
flutter pub add sqfliteDatabase Helper
You should create new file in your project’s lib folder and name it databaseHelper.dart
The following script should be included in this file, it contains all the functions that are necessary to create a database and edit its contents.
import 'dart:io';
import 'package:path/path.dart';
import 'package:sqflite/sqflite.dart';
import 'package:path_provider/path_provider.dart';
class DatabaseHelper {
static const _databaseName = "DBsample.db";
static const _databaseVersion = 1;
static const table = 'contact';
static const columnId = '_id';
static const nameCol = 'name';
static const ageCol = 'age';
// makes this class a singleton
DatabaseHelper._privateConstructor();
static final DatabaseHelper instance = DatabaseHelper._privateConstructor();
// reference to one database
static Database? _database;
Future<Database> get database async =>
_database ??= await _initDatabase();
// opens the database or creates it if it wasn't created yet
_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 and the table
Future _onCreate(Database db, int version) async {
await db.execute('''
CREATE TABLE $table (
$columnId INTEGER PRIMARY KEY,
$nameCol TEXT NOT NULL,
$ageCol INTEGER NOT NULL
)
''');
}
// Helper methods
//----------------------------------------------------
// Inserts a line in the database where each Map key
// is a column name and each value is the column value.
// the returned value is the line's ID
Future<int> insert(Map<String, dynamic> row) async {
Database db = await instance.database;
return await db.insert(table, row);
}
// All lines are returned as a maps List where each map
// is a list of key-values from the columns.
Future<List<Map<String, dynamic>>> queryAllRows() async {
Database db = await instance.database;
return await db.query(table);
}
// All methods: insert, check, update and delete,
// can also be done using raw SQL commands.
// This method uses a raw check to provide the line count.
Future<int?> queryRowCount() async {
Database db = await instance.database;
return Sqflite.firstIntValue(await db.rawQuery('SELECT COUNT(*) FROM $table'));
}
// Assuming that the map's ID column is set.
// The other column values will be used to update the line.
Future<int> update(Map<String, dynamic> row) async {
Database db = await instance.database;
int id = row[columnId];
return await db.update(table, row, where: '$columnId = ?', whereArgs: [id]);
}
// Deletes a line that was specified by its id.
// The number of affected lines will be returned as 1,
// as long as there's a line to be deleted.
Future<int> delete(int id) async {
Database db = await instance.database;
return await db.delete(table, where: '$columnId = ?', whereArgs: [id]);
}
}Main Page
In the main.dart file we’re gonna code the UI from where you can test the SQLite database.
import 'package:flutter/material.dart';
import 'databaseHelper.dart';
void main() => runApp(const MyApp());
class MyApp extends StatelessWidget {
const MyApp({super.key});
@override
Widget build(BuildContext context) {
return MaterialApp(
title: 'SQFlite Demo',
theme: ThemeData(
primarySwatch: Colors.blue,
),
home: const MyHomePage(),
);
}
}
class MyHomePage extends StatefulWidget {
const MyHomePage({super.key});
@override
State<MyHomePage> createState() => _MyHomePageState();
}
class _MyHomePageState extends State<MyHomePage> {
// references the database helper singleton
final dbHelper = DatabaseHelper.instance;
String dbLog = '';
// page layout
@override
Widget build(BuildContext context) {
return Scaffold(
appBar: AppBar(
title: const Text('Basic CRUD sample'),
),
body: Center(
child: ListView(
children: <Widget>[
Text(dbLog),
ElevatedButton(
child: const Text(
'Insert Data',
style: TextStyle(fontSize: 20),
),
onPressed: () {
_insert();
},
),
ElevatedButton(
child: const Text(
'Check Data',
style: TextStyle(fontSize: 20),
),
onPressed: () {
_check();
},
),
ElevatedButton(
child: const Text(
'Update Data',
style: TextStyle(fontSize: 20),
),
onPressed: () {
_update();
},
),
ElevatedButton(
child: const Text(
'Delete Data',
style: TextStyle(fontSize: 20),
),
onPressed: () {
_delete();
},
),
],
),
),
);
}
// button methods
void _insert() async {
// line to be inserted
Map<String, dynamic> row = {
DatabaseHelper.nameCol: 'Username',
DatabaseHelper.ageCol: 34
};
final id = await dbHelper.insert(row);
setState(() {
dbLog += '\nInserted line: $id';
});
}
void _check() async {
final todasLinhas = await dbHelper.queryAllRows();
dbLog = 'Saved data...';
setState(() {
for (var row in todasLinhas) {
dbLog += '\n$row';
}
});
}
void _update() async {
// line to be updated
Map<String, dynamic> row = {
DatabaseHelper.columnId: 1,
DatabaseHelper.nameCol: 'Updated Username',
DatabaseHelper.ageCol: 29
};
final updatedLines = await dbHelper.update(row);
setState(() {
dbLog += '\nUpdated $updatedLines lines';
});
}
void _delete() async {
// assuming the number of lines is the last line's id
final id = await dbHelper.queryRowCount();
final deletedLine = await dbHelper.delete(id!);
setState(() {
dbLog += '\nDeleted id: $id';
});
}
}