Creating a Local Database with Sqflite in Flutter: Step-by-Step Guide
08-Nov-2023
|
|
Hello developers in this tutorial we will discuss about how to change language of all screens in flutter by using Getx package
Table of Contents
| S.no | Contents-topics |
|---|---|
| 1 | Import packages |
| 2 | Configure main.dart |
| 3 | Creating Database file |
| 4 | Performing CRUD |
| 5 | Run the code |
1:Import packages
In pubspec.yaml we need to import some packages (version of packages may differ so import latest pacakges from Pub Dev)
sqflite: ^2.2.8 - For using SqfLite database
path: ^1.8.2 - For providing path to our DB able
path_provider: ^2.0.14 - For providing path to all queries of db
Dependecies code Copy
sqflite: ^2.2.8
path: ^1.8.2
path_provider: ^2.0.14
2:Configure main.dart
In main.dart we import or all packages
main.dart code Copy
import 'package:flutter/material.dart';
import 'package:flutter_application_1/services/db_helper.dart';
// Here we are using a global variable. You can use something like
// get_it in a production app.
final dbHelper = DatabaseHelper();
Future<void> main() async {
WidgetsFlutterBinding.ensureInitialized();
// initialize the database
await dbHelper.init();
runApp(const MyApp());
}
class MyApp extends StatefulWidget {
const MyApp({super.key});
@override
State<MyApp> createState() => _MyAppState();
}
class _MyAppState extends State<MyApp> {
@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> {
List data = [];
@override
Widget build(BuildContext context) {
return Scaffold(
appBar: AppBar(
title: const Text('sqfliteby DeveloperCodez'),
),
body: Center(
child: Column(
mainAxisAlignment: MainAxisAlignment.center,
children: <Widget>[
Column(
crossAxisAlignment: CrossAxisAlignment.center,
children: List.generate(
data.length,
(index) => Row(
crossAxisAlignment: CrossAxisAlignment.center,
mainAxisAlignment: MainAxisAlignment.spaceEvenly,
children: [
Text(data[index]['_id'].toString()),
Text(data[index]['name']),
Text(data[index]['written_by'])
],
)),
),
ElevatedButton(
onPressed: _insert,
child: const Text('insert'),
),
const SizedBox(height: 10),
ElevatedButton(
onPressed: _query,
child: const Text('query'),
),
const SizedBox(height: 10),
ElevatedButton(
onPressed: _update,
child: const Text('update'),
),
const SizedBox(height: 10),
ElevatedButton(
onPressed: _delete,
child: const Text('delete'),
),
],
),
),
);
}
void _insert() async {
// row to insert
Map<String, dynamic> row = {
DatabaseHelper.columnName: 'DeveloprCodez',
DatabaseHelper.columnAge: 24,
DatabaseHelper.coulumnWrittenBy: 'Flutter Tutorial'
};
final id = await dbHelper.insert(row);
debugPrint('inserted row id: $id');
_query();
}
void _query() async {
final allRows = await dbHelper.queryAllRows();
debugPrint('query all rows:');
// for (final row in allRows) {
// debugPrint(row.toString());
// }
setState(() {
data = allRows;
// print(data[0]['_id']);
});
}
void _update() async {
// row to update
Map<String, dynamic> row = {
DatabaseHelper.columnId: 1,
DatabaseHelper.columnName: 'developerCodez',
DatabaseHelper.columnAge: 30,
DatabaseHelper.coulumnWrittenBy: 'Flutter Tutorial for sqLite'
};
final rowsAffected = await dbHelper.update(row);
debugPrint('updated $rowsAffected row(s)');
_query();
}
void _delete() async {
// Assuming that the number of rows is the id for the last row.
final id = await dbHelper.queryRowCount();
final rowsDeleted = await dbHelper.delete(id);
debugPrint('deleted $rowsDeleted row(s): row $id');
_query();
}
}
3:Creating services/db_helper.dart file
Under the lib folder create a folder name services and inside services create a file services/db_helper.dart , in this file we define our database and queries for create , read , update and delete paste the below code
code for db_helper.dart: Copy
import 'package:path/path.dart';
import 'package:sqflite/sqflite.dart';
import 'package:path_provider/path_provider.dart';
class DatabaseHelper {
static const _databaseName = "MyDatabase.db";
static const _databaseVersion = 1;
static const table = 'my_table';
static const columnId = '_id';
static const columnName = 'name';
static const columnAge = 'age';
static const coulumnWrittenBy = 'written_by';
late Database _db;
// this opens the database (and creates it if it doesn't exist)
Future<void> init() async {
final documentsDirectory = await getApplicationDocumentsDirectory();
final path = join(documentsDirectory.path, _databaseName);
_db = 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,
$coulumnWrittenBy TEXT 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(Map<String, dynamic> row) async {
return await _db.insert(table, row);
}
// 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 {
return await _db.query(table);
}
// 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 {
final results = await _db.rawQuery('SELECT COUNT(*) FROM $table');
return Sqflite.firstIntValue(results) ?? 0;
}
// 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(Map<String, dynamic> row) async {
int id = row[columnId];
return await _db.update(
table,
row,
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 {
return await _db.delete(
table,
where: '$columnId = ?',
whereArgs: [id],
);
}
}
4: Performing CRUD
CRUD includes create , read , update and delete that we define in our main.dart file
code Copy
void _insert() async {
// row to insert
Map<String, dynamic> row = {
DatabaseHelper.columnName: 'DeveloprCodez',
DatabaseHelper.columnAge: 24,
DatabaseHelper.coulumnWrittenBy: 'Flutter Tutorial'
};
final id = await dbHelper.insert(row);
debugPrint('inserted row id: $id');
_query();
}
void _query() async {
final allRows = await dbHelper.queryAllRows();
debugPrint('query all rows:');
// for (final row in allRows) {
// debugPrint(row.toString());
// }
setState(() {
data = allRows;
// print(data[0]['_id']);
});
}
void _update() async {
// row to update
Map<String, dynamic> row = {
DatabaseHelper.columnId: 1,
DatabaseHelper.columnName: 'developerCodez',
DatabaseHelper.columnAge: 30,
DatabaseHelper.coulumnWrittenBy: 'Flutter Tutorial for sqLite'
};
final rowsAffected = await dbHelper.update(row);
debugPrint('updated $rowsAffected row(s)');
_query();
}
void _delete() async {
// Assuming that the number of rows is the id for the last row.
final id = await dbHelper.queryRowCount();
final rowsDeleted = await dbHelper.delete(id);
debugPrint('deleted $rowsDeleted row(s): row $id');
_query();
}
5: Run the Code
We are Vineet Maanas we are working as professional developers from the last 8 years and always looking to discover something amazing getting followed by market trends and always push new developers to learn something new with our tutorials Thankyou.
0 Comments (Please let us know your query)