How to Use sqflite in Flutter: Simple Guide with Example

To use sqflite in Flutter, add it as a dependency, import it, and open a database with openDatabase. Then create tables and perform CRUD operations using methods like insert, query, update, and delete.

Syntax

The main steps to use sqflite are:

• Import the package.
• Open or create a database with openDatabase.
• Create tables using SQL commands in onCreate.
• Use methods like insert, query, update, and delete to manage data.

dart

import 'package:sqflite/sqflite.dart';

Future<Database> openMyDatabase() async {
  return openDatabase(
    'my_db.db',
    version: 1,
    onCreate: (db, version) async {
      await db.execute('CREATE TABLE items(id INTEGER PRIMARY KEY, name TEXT)');
    },
  );
}

Future<void> insertItem(Database db, Map<String, dynamic> item) async {
  await db.insert('items', item);
}

Future<List<Map<String, dynamic>>> getItems(Database db) async {
  return db.query('items');
}

Example

This example shows a simple Flutter app that opens a database, creates a table, inserts an item, and fetches all items to display in a list.

dart

import 'package:flutter/material.dart';
import 'package:sqflite/sqflite.dart';
import 'package:path/path.dart';

void main() => runApp(MyApp());

class MyApp extends StatefulWidget {
  @override
  State<MyApp> createState() => _MyAppState();
}

class _MyAppState extends State<MyApp> {
  late Database db;
  List<Map<String, dynamic>> items = [];

  @override
  void initState() {
    super.initState();
    initDb();
  }

  Future<void> initDb() async {
    db = await openDatabase(
      join(await getDatabasesPath(), 'demo.db'),
      version: 1,
      onCreate: (db, version) async {
        await db.execute('CREATE TABLE items(id INTEGER PRIMARY KEY, name TEXT)');
      },
    );
    await db.insert('items', {'name': 'First Item'});
    await fetchItems();
  }

  Future<void> fetchItems() async {
    final data = await db.query('items');
    setState(() {
      items = data;
    });
  }

  @override
  Widget build(BuildContext context) {
    return MaterialApp(
      home: Scaffold(
        appBar: AppBar(title: Text('sqflite Example')),
        body: ListView.builder(
          itemCount: items.length,
          itemBuilder: (context, index) {
            return ListTile(
              title: Text(items[index]['name'] ?? ''),
            );
          },
        ),
      ),
    );
  }
}

Output

A Flutter app screen with an app bar titled 'sqflite Example' and a list showing one item: 'First Item'.

Common Pitfalls

Common mistakes when using sqflite include:

• Not awaiting async database operations, causing unexpected behavior.
• Forgetting to open the database before queries.
• Not handling database version upgrades properly.
• Using raw SQL without parameter binding, risking SQL injection.

dart

/* Wrong: Not awaiting insert */
void addItem(Database db) {
  db.insert('items', {'name': 'New Item'}); // Missing await
}

/* Right: Await insert */
Future<void> addItem(Database db) async {
  await db.insert('items', {'name': 'New Item'});
}

Quick Reference

Here is a quick reference for common sqflite methods:

Method	Description
openDatabase(path, ...)	Open or create a database at the given path.
db.execute(sql)	Run a SQL command (e.g., create table).
db.insert(table, values)	Insert a row into a table.
db.query(table)	Get rows from a table.
db.update(table, values, where)	Update rows matching condition.
db.delete(table, where)	Delete rows matching condition.

Key Takeaways

Add sqflite to pubspec.yaml and import it to use SQLite in Flutter.

Open the database with openDatabase and create tables in onCreate callback.

Use async/await for all database operations to avoid bugs.

Use insert, query, update, and delete methods to manage data safely.

Handle database version upgrades carefully to avoid data loss.