SQLite with sqflite package in Flutter

Introduction

SQLite lets your app save data on the device. The sqflite package helps Flutter apps use SQLite easily.

You want to save user notes or settings locally on the phone.

You need to keep a list of items that the user can add or remove.

Your app should work offline and store data persistently.

You want to organize data in tables with rows and columns.

You need fast access to structured data without internet.

Syntax

Flutter

import 'package:sqflite/sqflite.dart';

// Open or create a database
final database = await openDatabase(
  'my_db.db',
  version: 1,
  onCreate: (db, version) async {
    await db.execute('CREATE TABLE items(id INTEGER PRIMARY KEY, name TEXT)');
  },
);

// Insert data
await database.insert('items', {'name': 'Item 1'});

// Query data
final List<Map<String, dynamic>> items = await database.query('items');

Use openDatabase to create or open a database file.

Use SQL commands inside execute to create tables.

Examples

Open a database file named 'app.db'. If it doesn't exist, it will be created.

Flutter

final db = await openDatabase('app.db');

Create a table called 'users' with columns for id and name.

Flutter

await db.execute('CREATE TABLE users(id INTEGER PRIMARY KEY, name TEXT)');

Add a new user named Alice to the 'users' table.

Flutter

await db.insert('users', {'name': 'Alice'});

Get all rows from the 'users' table as a list of maps.

Flutter

final users = await db.query('users');

Sample App

This Flutter app creates a local SQLite database using sqflite. It makes a table called 'items', inserts one item, and shows the list on screen.

Flutter

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

void main() {
  runApp(const MyApp());
}

class MyApp extends StatefulWidget {
  const MyApp({super.key});

  @override
  State<MyApp> createState() => _MyAppState();
}

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

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

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

  Future<void> insertItem(String name) async {
    await database.insert('items', {'name': name});
  }

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

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

Important Notes

Always close the database when done to free resources (not shown here for simplicity).

Use getDatabasesPath() and join() from path package to get a safe file path.

Database operations are async, so use await and async properly.

Summary

sqflite lets Flutter apps use SQLite databases easily.

Open or create a database, then create tables with SQL commands.

Insert, query, update, and delete data using provided methods.