Best SQFLite database example | Flutter SQlite Tutorial
Last updated Mar 15, 2021
In Mobile applications to persist the data into local we use the local database like SQlite. We have number of tutorials to setup Sqlite database in flutter application with SQFlite plugin. In this Sqflite tutorial i have given best example to do CRUD operations with Sqlite Query operations. Here we have implementing CRUD operations with user registration page and login pages.
In this flutter example we can use the SQLite database via SQFLite.
Now Let's start implement the SQFlite in flutter.
This example will cover below features
Flutter Navigation with Navigator Routes.
CRUD operations with SQLite data base.
Create, Insert, Delete, Read data with SQFlite.
First add below dependencies in pubspec.yaml file under flutter sdk with same indentation.
Step 1:
sqflite:
path:
path_provider:
Then in terminal run flutter packages get command to load the dependencies.
Step 2:
Create a Model class User with user.dart file and put below code.
class User{
int id;
String name;
String email;
String pasword;
String mobile;
User(this.name,this.email,this.pasword,this.mobile);
MaptoUserMap(){
return {
'name':name,
'email':email,
'password':pasword,
'mobile':mobile,
};
}
static fromMap(Map c) {
return User(c['name'],c['email'],c['passowrd'],c['mobile']);
}
}
Step 3:
Create a class UserDatabase and put below code
import 'dart:io';
import 'package:flutter_firebase_app/models/user.dart';
import 'package:path/path.dart';
import 'package:sqflite/sqflite.dart';
import 'package:path_provider/path_provider.dart';
class UserDatabase{
static String path;
static final _databaseName = "mydb.db";
static final _databaseVersion = 1;
static final _table_user = 'users';
static final _table_logins = 'logins';
UserDatabase._privateConstructor();
static final UserDatabase instance = UserDatabase._privateConstructor();
// only have a single app-wide reference to the database
static Database _database;
Future 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 users(id INTEGER PRIMARY KEY autoincrement, name TEXT, email TEXT, password TEXT, mobile TEXT)",
);
await db.execute(
"CREATE TABLE logins(name TEXT, email TEXT, mobile TEXT,password TEXT)",
);
}
static Future getFileData() async {
return getDatabasesPath().then((s){
return path=s;
});
}
Future insertUser(User user) async{
Database db = await instance.database;
var users=await db.rawQuery("select * from users where mobile = "+user.mobile);
if(users.length>0)
{
return -1;
}
return await db.insert("users",user.toUserMap(),conflictAlgorithm: ConflictAlgorithm.ignore
);
}
Future checkUserLogin(String mobile, String password) async
{
Database db = await instance.database;
var res=await db.rawQuery("select * from users where mobile = '$mobile' and password = '$password'");
if(res.length>0)
{
List list =
res.toList().map((c) => User.fromMap(c)).toList() ;
print("Data "+list.toString());
await db.insert("logins",list[0].toUserMap());
return list[0];
}
return null;
}
Future getUser() async{
Database db = await instance.database;
var logins=await db.rawQuery("select * from logins");
if(logins==null)
return 0;
return logins.length;
}
Future getUserData() async{
Database db = await instance.database;
var res=await db.rawQuery("select * from logins");
print("result user data $res");
print("result user data "+res.toString());
List list =
res.toList().map((c) => User.fromMap(c)).toList() ;
return list[0];
}
Future deleteUser(String mobile) async{
Database db = await instance.database;
var logins= db.delete(_table_logins, where: "mobile = ?", whereArgs: [mobile]);
return logins;
}
}
Step 4:
Create RouteSettngsPage with route_settings.dart and put below code
import 'package:flutter/material.dart';
import 'package:flutter_firebase_app/signup_login/SignupPage.dart';
import 'databases/UserDatabase.dart';
import 'home/home.dart';
import 'signup_login/LoginPage.dart';
import 'splashpage.dart';
class RouteSettngsPage extends RouteSettings{
static RoutegenerateRoute(RouteSettings settings)
{
switch(settings.name)
{
case "/":
return MaterialPageRoute(builder: (_)=>SplashPage());
break;
case "/splash":
return MaterialPageRoute(builder: (_)=>SplashPage());
break;
case "/login":
return MaterialPageRoute(builder: (_)=>LoginPage());
break;
case "/signup":
return MaterialPageRoute(builder: (_)=>SignupPage());
break;
case "/home":
return MaterialPageRoute(builder: (_)=>Homepage());
break;
}
}
}
Here we are created NamedRoutes for SplashScreen,Signup,Login and Home Pages. Now we are going to create these pages with below code.
SplashScreen
import 'package:flutter/material.dart';
import 'databases/UserDatabase.dart';
import 'home/home.dart';
import 'main.dart';
import 'models/user.dart';
import 'signup_login/LoginPage.dart';
class SplashPage extends StatefulWidget{
@override
State createState() {
// TODO: implement createState
return SplashState();
}
}
class SplashState extends State
{
int login=101;
int loginData;
@override
void initState() {
// TODO: implement initState
super.initState();
loginData=login;
new Future.delayed(const Duration(seconds: 1), () {
UserDatabase.instance.getUser().then((result){
setState(() {
loginData=result;
if(loginData==0)
Navigator.pushReplacementNamed(context, "/login");
else Navigator.pushReplacementNamed(context, "/home");
print("Called Return value on state $loginData");
});
});
});
}
@override
Widget build(BuildContext context) {
// TODO: implement build
return MaterialApp(
debugShowCheckedModeBanner: false,
home: Container(
child: Image.asset("splash_img.png",fit: BoxFit.cover,),
));
}
}
Conclusion: Thats for the SQflite database implemention, now let's run the application and you can register user with minimal required fileds like name, email, phonenumber. After registration you can also authenticate your login with database stored values. You can also delete user upon logout.
