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

  1. Flutter Navigation with Navigator Routes.
  2. CRUD operations with SQLite data base.
  3. 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 :
https://www.rrtutors.com/site_assets/profile/assets/img/avataaars.svg

11458 Views