SQLite is a relational database used in mobile apps. Unlike other commonly used relational database systems like MySQL, PostgreSQL, or MS SQL, SQLite does not have a separate server process. SQLite processes like reads or writes are processed on an ordinary file with SQLite extension. This makes it useful in mobile app development to store, query, and retrieve data locally even without the internet. Another advantage of SQLite is that it does not require any configuration and you can start right away.
In this tutorial, we will teach you how to use the SQLite database in Flutter using the sqflite package. We will take you through how to create a database when the user loads the app for the first time, how to create tables, put data, read or query data, update values in the database, how to delete a data from a table or the entire table and also how to perform batch operations in SQLite.
Install Package
The first step is to add sqflite and path_provider packages to pubspec.yaml as shown below. Run flutter pub get to install the package. There are no other configurations required for the plugin both on iOS and Android operating systems.
Initialize Sqlite Database
To initialize an SQLite database in Flutter, you will have to create an SQLiteDB class as shown below. Here countTable() function basically counts the number of tables in the database.
At first, you will have to call countTable() from the main() function. Inside countTable() function var dbClient = await db; gets executed. This calls get db async{...}. The function returns an instance of Database if it exists or calls initDB() if database doesn’t exist. Inside initDB(), a new database is initialized with the name you provide. In this case our databse will be myDatabase.db
SqLiteDB class
(sqlite.dart)
Main Function
Create a Table
The below example shows how to create a new table inside our previously created Database. Import sqlite.dart with the SqliteDB class we have created at the top. await SqliteDB().db returns an instance of our database and saves it to a dbClient variable. dbClient.execute() executes any SQL query. Here we run a Create Table query to create a new table User
Write data to database
dbClient.insert() can be used to insert data to an Sqlite table. Here we create and insert a user data with id, name, email and age.
Query database
The below example shows how to run queries on database using dbClient.rawQuery() where you can run normal sql queries as well as with sqflite helper dbClient.query(). Here in the example, getAll() and getAllUsingHelper() function returns all the users on the table and getAdults() and getAdultsUsingHelper() returns the name and id of users above the age of 18 on the table.
Update data
The below example shows how to update data using dbClient.rawQuery() where you can run normal sql queries as well as with sqflite helper dbClient.update().
Delete data
The below example shows how to delete data using dbClient.rawQuery() where you can run normal sql queries as well as with sqflite helper dbClient.delete().
Batch operation
The below example shows how to do batch insert using sqflite package. The data is written only after you commit changes using batch.commit()
Transaction
A transaction is committed if the all our queries does not throw back an error. If an error is thrown, the entire transaction is cancelled. Here we show an example of a transaction by deleting multiple tables. Here we use txn.execute(). You can also use rawQuery, update, delete etc with transactions.