Let’s say you’re creating this amazing app… (pause and think of your amazing app) While developing your app you come to the conclusion that you will be working with some type of data, perhaps user-generated data or data retrieved from an API, and that this data needs to persist even after the app has been closed. You come across the Provider package which looks promising. The only problem is it doesn’t quite fit your needs, because while it does provide app-level state, once the app is closed the data is gone. After more research, you come across on-device databases, which as it turns out, are the perfect solution to this problem.

Before going into detail about how to use SQLite in Flutter I want to mention a few alternatives. Each can accomplish what you want, it really comes down to the use-case and your personal preference. I don’t know too much about these, as I’ve only used SQLite, however they may be worth looking into if you haven’t yet committed to one.

Alternatives:

Come back to this before uploading

Alight, so let’s jump into it. The first thing we’ll need to do is add the sqflite package. You can find the exact install line and documentation for this package here: https://pub.dev/packages/sqflite

The next thing we want to do is create a Database.dart file which will serve as a central location to hold all of our database operations. We’ll refer to this file as the provider.

The first thing we want to add is this:

import 'package:sqflite/sqflite.dart';
import 'package:sqflite/sqlite_api.dart';
import 'package:path/path.dart';
import 'package:youtube_demo/models/user.dart';

class DBProvider {
  DBProvider._();
  static final DBProvider db = DBProvider._();
  static Database _database;

}

All we’ve done so far is created a new file and added a class that has some variables, nothing too complicated going here just yet.

The next thing we want to do is create a getter which gives us access to an instance of our class. In our case, we’ll also design this as a singleton. Add this right under the static Database _database; line (if you get lost which the piece by piece breakdown don’t worry, the complete code is at the bottom of this post).

Future<Database> get database async {
    if(_database != null)
    return _database;

    _database = await initDB();
    return _database;
}

This method checks to see if an instance of the data has already been created, if it does good, return and use it, if it doesn’t that’s fine too, create a new one and return that.

Now that we’re connected to our database we need to set it up, after all, it’s currently a completely empty database. To do that we’ll create an initDB method that sets everything up. In our case all we have to do is create one table, however, you may need to create multiple tables, constraints, or other things.

initDB() async {
    return await openDatabase(
      join(await getDatabasesPath(), 'youtube_demo.db'),
      onCreate: (db, version) async {
       await db.execute('''
        CREATE TABLE users (
          username TEXT PRIMARY KEY, password TEXT
        )
       ''');
      },
      version: 1
    );
}

Now that our database is setup, we’ll create our CRUD methods, or in our case the C and R part of CRUD.

newUser(User newUser) async {
    final db = await database;

    var res = await db.rawInsert('''
      INSERT INTO users(
        username, password
      ) VALUES (?, ?)
    ''', [newUser.username, newUser.password]);

    return res;
}


  Future<dynamic> getUser() async {
    final db = await database;
    var res = await db.query("users");
    if(res.length == 0) {
      return null;
    } else {
      var resMap = res[0];
      return resMap.isNotEmpty ? resMap : Null;
    }
}

The methods above simple provide an insert statement to allow us to add users, and a means to get a user out of the database. Here is the full code for Database.dart:

import 'package:sqflite/sqflite.dart';
import 'package:sqflite/sqlite_api.dart';
import 'package:path/path.dart';
import 'package:youtube_demo/models/user.dart';

class DBProvider {
  DBProvider._();
  static final DBProvider db = DBProvider._();
  static Database _database;


  Future<Database> get database async {
    if(_database != null)
    return _database;

    _database = await initDB();
    return _database;
  }


  initDB() async {
    return await openDatabase(
      join(await getDatabasesPath(), 'youtube_demo.db'),
      onCreate: (db, version) async {
       await db.execute('''
        CREATE TABLE users (
          username TEXT PRIMARY KEY, password TEXT
        )
       ''');
      },
      version: 1
    );
  }


  newUser(User newUser) async {
    final db = await database;

    var res = await db.rawInsert('''
      INSERT INTO users(
        username, password
      ) VALUES (?, ?)
    ''', [newUser.username, newUser.password]);

    return res;
  }


  Future<dynamic> getUser() async {
    final db = await database;
    var res = await db.query("users");
    if(res.length == 0) {
      return null;
    } else {
      var resMap = res[0];
      return resMap.isNotEmpty ? resMap : Null;
    }
  }
}

Believe it or not, that’s it for the database. In other words, the hard part is done. All we have to do now is use the DBProvider to work with our newly created database.

First, we need to add a User model. To do this, create a new folder and file lib/models/user.dart and add the following code to it.

import 'dart:convert';

User userFromJson(String str) => User.fromJson(json.decode(str));

String userToJson(User data) => json.encode(data.toJson());

class User {
    String username;
    String password;

    User({
        this.username,
        this.password,
    });

    factory User.fromJson(Map<String, dynamic> json) => User(
        username: json["username"],
        password: json["password"],
    );

    Map<String, dynamic> toJson() => {
        "username": username,
        "password": password,
    };
}

Since this post isn’t about models I won’t go into detail about what this does, but it basically allows us to define what user data should look like.

Now to more familiar territory. We’ll be making some changes to our MyHomePage.dart and MyOtherPage.dart files. The important things we need to add that allow us to interact with the database are:

MyHomePage.dart:

var newDBUser = User(username: username, password: password);
DBProvider.db.newUser(newDBUser);

MyOtherPage.dart:

final _userData = await DBProvider.db.getUser();

If they look somewhat familiar that’s a good thing. These lines call methods we created in the DBProvider file and use the User model to define that data. Here is the full code for both of these files. After adding this, everything will be complete and it should all work.

MyHomePage.dart:

import 'package:flutter/material.dart';
import 'package:youtube_demo/MyBottomNavBar.dart';
import 'package:youtube_demo/models/user.dart';
import 'package:youtube_demo/utils/Database.dart';

class MyHomePage extends StatefulWidget {
  MyHomePage({Key key}) : super(key: key);

  @override
  _MyHomePageState createState() {
    return _MyHomePageState();
  }
}

class _MyHomePageState extends State<MyHomePage> {
  var username;
  var password;

  @override
  Widget build(BuildContext context) {
    return Scaffold(
      appBar: AppBar(
        title: Text('Home Page'),
      ),
      body: Center(
        child: Container(
          padding: EdgeInsets.all(25),
          child: Column(
            mainAxisAlignment: MainAxisAlignment.center,
            children: <Widget>[
              Container(
                padding: EdgeInsets.only(bottom: 50),
                child: Column(children: <Widget>[
                  Text('Username'),
                  TextField(
                    onChanged: (value) {
                      setState(() {
                        username = value;
                      });
                    },
                  ),
                ]),
              ),
              Container(
                padding: EdgeInsets.only(bottom: 50),
                child: Column(children: <Widget>[
                  Text('Password'),
                  TextField(
                    onChanged: (value) {
                      setState(() {
                        password = value;
                      });
                    },
                  ),
                ]),
              ),
              Container(
                padding: EdgeInsets.only(bottom: 50),
                child: FlatButton(
                  child: Text('Save'),
                  color: Colors.black,
                  textColor: Colors.white,
                  onPressed: () {
                    var newDBUser = User(username: username, password: password);
                    DBProvider.db.newUser(newDBUser);
                  },
                ),
              ),
            ],
          ),
        ),
      ),
      bottomNavigationBar: MyBottomNavBar(),
    );
  }
}

MyOtherPage.dart:

import 'package:flutter/material.dart';
import 'package:youtube_demo/MyBottomNavBar.dart';
import 'package:youtube_demo/utils/Database.dart';

class MyOtherPage extends StatefulWidget {
  MyOtherPage({Key key}) : super(key: key);

  @override
  _MyOtherPageState createState() {
    return _MyOtherPageState();
  }
}

class _MyOtherPageState extends State<MyOtherPage> {
  Map<String, String> newUser = {};
  Future _userFuture;

  @override
  void initState() {
    super.initState();
    _userFuture = getUser();
  }

  getUser() async {
    final _userData = await DBProvider.db.getUser();
    return _userData;
  }

  @override
  Widget build(BuildContext context) {
    return Scaffold(
      appBar: AppBar(
        title: Text('Other Page'),
      ),
      body: FutureBuilder(
        future: _userFuture,
        builder: (_, userData) {
          switch (userData.connectionState) {
            case ConnectionState.none:
              return Container();
            case ConnectionState.waiting:
              return Container();
            case ConnectionState.active:
            case ConnectionState.done:
              if (!newUser.containsKey('username')) {
                newUser = Map<String, String>.from(userData.data);
              }

              return Column(children: <Widget>[
                Text(
                  newUser['username'],
                ),
                Text(
                  newUser['password'],
                )
              ]);
          }

          return Container();
        },
      ),
      bottomNavigationBar: MyBottomNavBar(),
    );
  }
}

If you’d like to see a video version of this post, checkout the YouTube video a posted on it here:

I hope everything works, let me know if it doesn’t.

If you’d like to notified when I create new content and have cool things to talk about you can sign up for my newsletter here: http://garrettlove.com/newsletter/