Flutter SQFLite Database Tutorial: Implement SQLite database

To Persist the data into local we use the local database like SQlite.

In Flutter we can use the SQLite database via SQFLite.

If you are new to SQLite, go with SQLite  tutorial to understand.

Now Let's start implement the SQFlite in flutter.

You can find the complete code at Flutter-SQFLite-Database   github

In this tutorial i have implement the below functionalities.

Flutter Navigation with Navigator Routes.

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: ^0.4.1

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),
            ),
          ),
        ],
      )
    );
  }

}

 

 

TAGS - Flutter, Flutter SDK, Flutter - SQLite Database, Flutter - SQFLite Database, Flutter - CRUD Operations

Flutter - Navigation,


Subscribe For Daily Updates