Best SQFLite database example | Flutter SQlite Tutorial
Last updated Mar 15, 2021In 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,),
));
}
}
|
Signup page
import 'package:flutter/material.dart';
import 'package:flutter_firebase_app/databases/UserDatabase.dart';
import 'package:flutter_firebase_app/models/user.dart';
class SignupPage extends StatefulWidget{
@override
State createState() {
// TODO: implement createState
return SignUpState();
}
}
class SignUpState extends State{
final _formKey = GlobalKey();
final _scafoldKey = GlobalKey();
final _nameEditController=TextEditingController();
final _emailEditController=TextEditingController();
final _mobileEditController=TextEditingController();
final _passwordEditController=TextEditingController();
String email_pattern = r'^(([^<>()[\]\\.,;:\s@\"]+(\.[^<>()[\]\\.,;:\s@\"]+)*)|(\".+\"))@((\[[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\])|(([a-zA-Z\-0-9]+\.)+[a-zA-Z]{2,}))$';
String password_pattern = r'^[a-zA-Z0-9]{6,}$';
String mobile_pattern = r'^\(?([0-9]{3})\)?[-. ]?([0-9]{3})[-. ]?([0-9]{4})$';
Size size;
@override
Widget build(BuildContext context) {
size=MediaQuery.of(context).size;
return new Scaffold(
key: _scafoldKey,
body: Stack(
children:[
Image.asset("splash_img.png",fit: BoxFit.cover, width: size.width,height: size.height,),
Container(color: const Color(0x99FFFFFF),),
Container(
height: 120,
decoration: new BoxDecoration(
border: Border.all(color: Colors.teal),
borderRadius: BorderRadius.only(bottomLeft: Radius.circular(size.width/2),topRight: Radius.circular(size.width/2)),
color: Colors.teal,
),
),
Center(
child: SingleChildScrollView(
child: Padding(
padding: EdgeInsets.only(left: 20,right: 20),
child: Form(
key: _formKey,
child:
Column(
mainAxisSize: MainAxisSize.max,
mainAxisAlignment: MainAxisAlignment.center,
children: [
SizedBox(height: 20,),
Container(
decoration: new BoxDecoration(
border: new Border.all(color: Colors.teal),
borderRadius: BorderRadius.circular(10),
color: Colors.teal,
),
child: Padding(
padding: const EdgeInsets.all(8.0),
child: Text("Registration Form",style: TextStyle(color: Colors.white,
fontSize: 22
),),
),
),
SizedBox(height: 40,),
//--------------Name FormFiled------------------------------------------
TextFormField(
controller: _nameEditController,
textInputAction: TextInputAction.next,
validator: (value){
if(value.isEmpty)
{
return "Enter Name";
}
return null;
},
style: getTextStyle(),
decoration: customInputDecoration("Enter Name"),
),
SizedBox(height: 20,),
//--------------Email FormFiled------------------------------------------
TextFormField(
controller: _emailEditController,
textInputAction: TextInputAction.next,
validator: (value){
RegExp regex =RegExp(email_pattern);
if (!regex.hasMatch(value))
return 'Enter Valid Email';
else
return null;
},
keyboardType: TextInputType.emailAddress,
style: getTextStyle(),
decoration: customInputDecoration("Enter email id"),
),
SizedBox(height: 20,),
//--------------Mobile FormFiled------------------------------------------
TextFormField(
controller: _mobileEditController,
textInputAction: TextInputAction.next,
validator: (value){
RegExp regex =RegExp(mobile_pattern);
if (!regex.hasMatch(value))
return 'Enter valid mobile number';
else
return null;
return null;
},
keyboardType: TextInputType.number,
maxLength: 10,
style: getTextStyle(),
decoration: customInputDecoration("Enter mobile number"),
),
SizedBox(height: 20,),
//--------------Password FormFiled------------------------------------------
TextFormField(
controller: _passwordEditController,
textInputAction: TextInputAction.done,
validator: (value){
RegExp regex =RegExp(password_pattern);
if (!regex.hasMatch(value))
return 'Password should be in alphanumaric with 6 characters';
else
return null;
},
obscureText: true,
style: getTextStyle(),
decoration: customInputDecoration("Enter password"),
),
SizedBox(height: 20,),
RaisedButton(onPressed: (){
if(_formKey.currentState.validate())
{
UserDatabase.instance.insertUser(User(_nameEditController.text,_emailEditController.text,_passwordEditController.text,_mobileEditController.text)).then((result){
if(result==-1)
{
_scafoldKey.currentState
.showSnackBar(SnackBar(content: Text('User with same number already existed $result')));
}else
{
_scafoldKey.currentState
.showSnackBar(SnackBar(content: Text('User Registered Succesfully $result')));
Navigator.pushReplacementNamed(context, "/login");
}
}) ;
}
}, shape: RoundedRectangleBorder(
borderRadius: BorderRadius.circular(18),
),
color: Colors.pink,
child: Text("Signup", style: TextStyle(color: Colors.white,fontSize: 20),),
),
FlatButton(
child: Text("Already have account, Sign In?"),
onPressed: (){
Navigator.pushReplacementNamed(context, "/login");
},
)
],
)
),
),
),
)
],
),
);;
}
TextStyle getTextStyle(){
return TextStyle(
fontSize: 18,
color: Colors.pink
);
}
InputDecoration customInputDecoration(String hint)
{
return InputDecoration(
hintText: hint,
hintStyle: TextStyle(
color: Colors.teal
),
contentPadding: EdgeInsets.all(10),
enabledBorder: OutlineInputBorder(
borderRadius: BorderRadius.circular(12),
borderSide: BorderSide(
color: Colors.pink
)
),
focusedBorder: OutlineInputBorder(
borderRadius: BorderRadius.circular(8),
borderSide: BorderSide(
color: Colors.pink
)
),
);
}
}
|
Here We are validating the TextFiled data with FormFiled validator.
For Email, Mobile and Password validation done bu Regular expressions.
To focus the next TextFormFiled we used Focuse property.
Login page
import 'package:flutter/material.dart';
import 'package:flutter_firebase_app/databases/UserDatabase.dart';
class LoginPage extends StatefulWidget{
@override
State createState() {
// TODO: implement createState
return LoginState();
}
}
class LoginState extends State
{
final _formKey = GlobalKey();
final _scaffoldKey = GlobalKey();
final _mobileController=TextEditingController();
final _passwordController=TextEditingController();
final FocusNode _mobileFocus = FocusNode();
final FocusNode _passwordFocus = FocusNode();
Size size;
@override
Widget build(BuildContext context) {
size = MediaQuery.of(context).size;
return new Scaffold(
key: _scaffoldKey,
body: Stack(
children:[
Image.asset("splash_img.png",fit: BoxFit.cover,
width: size.width,
height: size.height,
),
Padding(
padding: EdgeInsets.only(left: 20,right: 20),
child: Form(
key: _formKey,
child:
Column(
mainAxisAlignment: MainAxisAlignment.center,
children: [
SizedBox(height: 20,),
TextFormField(
controller: _mobileController,
keyboardType: TextInputType.number,
textInputAction: TextInputAction.next,
focusNode: _mobileFocus,
onFieldSubmitted: (term){
FocusScope.of(context).requestFocus(_passwordFocus);
},
validator: (value){
if(value.isEmpty)
{
return "Enter mobile number";
}
return null;
},
style: getTextStyle(),
decoration: customInputDecoration("Enter mobile number"),
),
SizedBox(height: 20,),
TextFormField(
textInputAction: TextInputAction.done,
controller: _passwordController,
keyboardType: TextInputType.text,
obscureText: true,
focusNode: _passwordFocus,
validator: (value){
if(value.isEmpty)
{
return "Enter Password";
}
return null;
},
style: getTextStyle(),
decoration: customInputDecoration("Enter password"),
),
SizedBox(height: 20,),
RaisedButton(onPressed: (){
if(_formKey.currentState.validate())
{
UserDatabase.instance.checkUserLogin(_mobileController.text,_passwordController.text).then((result){
if(result==null)
{
_scaffoldKey.currentState.showSnackBar(SnackBar(content: Text("Please enter valid details")));
}
else
{
Navigator.pushReplacementNamed(context, "/home");
}
});
}
}, shape: RoundedRectangleBorder(
borderRadius: BorderRadius.circular(18),
),
color: Colors.pink,
child: Text("Login", style: TextStyle(color: Colors.white,fontSize: 20),),
),
FlatButton(
child: Text("Don't have account, Signup?"),
onPressed: (){
Navigator.pushReplacementNamed(context, "/signup");
},
)
],
)
),
)
] ,
),
);
}
TextStyle getTextStyle(){
return TextStyle(
fontSize: 18,
color: Colors.pink
);
}
InputDecoration customInputDecoration(String hint)
{
return InputDecoration(
hintText: hint,
hintStyle: TextStyle(
color: Colors.teal
),
contentPadding: EdgeInsets.all(10),
enabledBorder: OutlineInputBorder(
borderRadius: BorderRadius.circular(12),
borderSide: BorderSide(
color: Colors.pink
)
),
focusedBorder: OutlineInputBorder(
borderRadius: BorderRadius.circular(8),
borderSide: BorderSide(
color: Colors.pink
)
),
);
}
}
|
Home page
import 'package:flutter/material.dart';
import 'package:flutter_firebase_app/databases/UserDatabase.dart';
import 'package:flutter_firebase_app/models/user.dart';
class Homepage extends StatefulWidget{
@override
State createState() {
// TODO: implement createState
return HomeState();
}
}
class HomeState extends State{
Size size;
User user;
@override
void initState() {
// TODO: implement initState
super.initState();
UserDatabase.instance.getUserData().then((result){
setState(() {
user=result;
});
});
}
@override
Widget build(BuildContext context) {
size=MediaQuery.of(context).size;
return Scaffold(
appBar: AppBar(
title: Text("Home"),
),
body: Column(
mainAxisSize: MainAxisSize.max,
children: [
Row(
mainAxisAlignment: MainAxisAlignment.end,
children: [
Padding(
padding: const EdgeInsets.all(12.0),
child: RaisedButton(
onPressed: (){
UserDatabase.instance.deleteUser(user.mobile).then((res){
if(res==1)
{
Navigator.pushReplacementNamed(context, "/login");
}
});
},
shape: RoundedRectangleBorder(
borderRadius: BorderRadius.circular(15),
),
color:Colors.pink,
child: Text("Logout", style: TextStyle(color: Colors.white
),)
),
)
],
),
Container(
height:size.height-200 ,
child: Center(
child: (user==null)?null:Text("Welcome User "+user.name),
),
),
],
)
);
}
}
|
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.
Further reading
Flutter Form Validation Example
TAGS - Flutter, Flutter SDK, Flutter - SQLite Database, Flutter - SQFLite Database, Flutter - CRUD Operations
Flutter - Navigation,
|
Article Contributed By :
|
|
|
|
|
11811 Views |
|
Projects By category
Categories